How Analysis Works

Technical documentation for all 10 analysis pages — SQL logic, thresholds, and use cases.

Analysis Pages

All analysis pages are available at dev-tm.ohmgp.com/analysis (and production). Each page has a country selector (where applicable) and displays data in card-based layouts powered by DuckDB queries against live CSV snapshots.

Pages Overview

Page	URL	Country Filter	Cards	Data Sources
Daily Movement	`/analysis/daily`	Yes	3	Spotify Daily (today + yesterday)
Trends	`/analysis/trends`	Yes	2	Spotify Daily (today + yesterday)
Top Lists	`/analysis/top`	Yes	3	Spotify Daily, YouTube Toplists, Spotify Artists
Countries	`/analysis/countries`	No	2	Spotify Daily, YouTube Countries
Artists	`/analysis/artists`	No	3	Artists Ranking, Spotify Listeners, YouTube Artists
YouTube	`/analysis/youtube`	No	3	YouTube Daily, YouTube Toplists, YouTube Trending
Metrics	`/analysis/metrics`	Yes	2	Spotify Daily, Spotify Daily Totals
Cross-Platform	`/analysis/cross`	Yes	2	Spotify Daily, Shazam, Radio
Predictive	`/analysis/predict`	Yes	2	Spotify Daily
Time-Based	`/analysis/time`	Yes	3	Spotify Daily

1. Daily Movement (`/analysis/daily`)

Purpose: Shows the most significant position changes happening right now — who is climbing, who just entered, and who fell off.

Country filter: Yes (defaults to India)

Data sources: Spotify Daily (today + yesterday via _prev_table())

Card 1: Biggest Climbers Today

Logic: Tracks where P+ (position change) is not = (i.e. something moved). Sorted by position ascending (top positions first). Limited to top 15.

SQL conditions: - country_code = '{code}' - "P+" != '=' - ORDER BY CAST(Pos AS INT) ASC - LIMIT 15

Columns: Track, Position, Streams, Streams+ (change amount), P+ (direction), YouTube

Insight: Identifies the songs making the biggest jumps today. A track at position 5 with P+ of +12 is climbing fast.

Card 2: New Chart Entries

Logic: Tracks that appear in today's CSV but NOT in yesterday's CSV. Uses a LEFT JOIN ... WHERE NULL pattern (anti-join) between today's table (tname) and yesterday's table (t0).

SQL conditions: - a.country_code = '{code}' - NOT EXISTS (SELECT 1 FROM {t0} b WHERE b.Artist_and_Title = a.Artist_and_Title AND b.country_code = '{code}') - ORDER BY CAST(a.Pos AS INT) ASC - LIMIT 15

Columns: Track, Days (should be 1), Streams, YouTube

Insight: Brand new songs on the chart. A new entry at position 12 is more significant than one at position 95.

Card 3: Tracks Leaving the Chart

Logic: Tracks that exist in yesterday's CSV but NOT in today's CSV. Same anti-join pattern, reversed.

SQL conditions: - b.country_code = '{code}' - NOT EXISTS (SELECT 1 FROM {tname} a WHERE a.Artist_and_Title = b.Artist_and_Title AND a.country_code = '{code}') - LIMIT 15

Columns: Track, Days (how long it charted), Last_Streams, YouTube

Insight: Shows what fell off. A track that charted for 60 days leaving is more notable than one that lasted 2 days.

2. Trends (`/analysis/trends`)

Purpose: Identifies momentum — tracks with the strongest upward movement and potential viral candidates based on stream surges.

Country filter: Yes (defaults to India)

Data sources: Spotify Daily (today + yesterday)

Card 1: Top Climbers vs Yesterday

Logic: JOINs today's table with yesterday's table on Artist_and_Title + country_code. Calculates position change as Today - Yesterday. A negative number means climbing (e.g. position 3 today vs position 10 yesterday = change of -7). Sorted ascending so the biggest climbers appear first.

SQL conditions: - a.country_code = '{code}' AND b.country_code = '{code}' - ORDER BY (CAST(a.Pos AS INT) - CAST(b.Pos AS INT)) ASC - LIMIT 15

Columns: Track, Today Position, Yesterday Position, Change, Streams, YouTube

Insight: The raw movement between two days. A change of -15 means the track jumped 15 spots.

Card 2: Viral Candidates (Big Stream Surges in Top 50)

Logic: Tracks in the top 50 where today's streams (Streams+) exceed 0. Sorted by Streams+ descending. This surfaces tracks getting unusually high daily streams relative to their position.

SQL conditions: - country_code = '{code}' - CAST(Pos AS INT) <= 50 - CAST({_n('"Streams+"')} AS BIGINT) > 0 - ORDER BY CAST({_n('"Streams+"')} AS BIGINT) DESC - LIMIT 15

Columns: Track, Position, Streams, 7-Day Average, Streams+ (today's surge), YouTube

Insight: A track at position 20 with 500K daily streams (vs 100K average) is surging. The ratio between Streams+ and 7Day indicates if this is a one-day spike or sustained growth.

3. Top Lists (`/analysis/top`)

Purpose: All-time highest-performing content across Spotify and YouTube — the biggest songs and artists ever.

Country filter: Yes (for Spotify Daily card only)

Data sources: Spotify Daily, YouTube Toplists, Spotify Artists

Card 1: All-Time Most Streamed

Logic: Sorts Spotify Daily by Total (all-time cumulative streams) descending. Country-filtered.

SQL conditions: - country_code = '{code}' - ORDER BY CAST({_n('Total')} AS BIGINT) DESC - LIMIT 15

Columns: Track, Position, Total (all-time streams), Daily (today's streams), YouTube

Insight: The most streamed song in a country's history. Comparing Total vs Daily shows if a song is still active or purely legacy.

Card 2: YouTube All-Time Most Viewed

Logic: Sorts YouTube Toplists by Views descending. Global (no country filter).

SQL conditions: - ORDER BY CAST({_n('Views')} AS BIGINT) DESC - LIMIT 15

Columns: Video, Artist, Views, Yesterday, Days, YouTube

Insight: The most viewed YouTube videos ever. Days shows how long it took to accumulate those views.

Card 3: Spotify Top Artists by Total Streams

Logic: Sorts Spotify Artists by total streams descending. Global.

SQL conditions: - ORDER BY CAST({_n('Streams')} AS BIGINT) DESC - LIMIT 15

Columns: Artist, Streams (total), Daily, Lead, Solo, Feature

Insight: Breaks down how artists accumulate streams — lead vs solo vs feature credits shows collaboration strategy.

4. Countries (`/analysis/countries`)

Purpose: Global snapshot — what's #1 everywhere and where platforms agree.

Country filter: No (shows all countries)

Data sources: Spotify Daily, YouTube Countries

Card 1: #1 Track in Every Country

Logic: Filters Spotify Daily for position = 1 across all countries. Sorted alphabetically by country name.

SQL conditions: - CAST(Pos AS INT) = 1 - ORDER BY country_name

Columns: Country, Code, #1 Track, Position, Streams, YouTube

Insight: Reveals regional taste. Same song at #1 in 20 countries = global phenomenon. Different #1s everywhere = fragmented market.

Card 2: Spotify vs YouTube Overlap (Top 10)

Logic: JOINs Spotify Daily with YouTube Countries on Artist_and_Title + country_code. Only includes tracks where BOTH platforms have the song in their top 10. This is a strong convergence signal.

SQL conditions: - CAST(a.Pos AS INT) <= 10 AND CAST(b.Pos AS INT) <= 10 - ORDER BY a.country_name, CAST(a.Pos AS INT) - LIMIT 30

Columns: Country, Track, Spotify Position, YouTube Position, YouTube

Insight: When a song is top 10 on both Spotify and YouTube in the same country, it indicates genuine cultural relevance beyond just streaming algorithm promotion.

5. Artists (`/analysis/artists`)

Purpose: Artist-level performance across platforms — who dominates globally.

Country filter: No (global data)

Data sources: Artists Ranking, Spotify Listeners, YouTube Artists

Card 1: Cross-Platform Artist Ranking

Logic: Sorts the Artists Ranking table by composite Points. Points are calculated from positions across Apple Music, Spotify, iTunes, YouTube, Shazam, and Deezer.

SQL conditions: - ORDER BY CAST({_n('Points')} AS BIGINT) DESC - LIMIT 20

Columns: Artist, Points, Apple_M, Spotify, iTunes, YouTube, Shazam, Deezer, Top_Country

Insight: The most comprehensive artist metric. An artist ranking high on Points but low on one platform is specialized. High on all = truly global.

Card 2: Spotify Top Artists by Monthly Listeners

Logic: Sorts Spotify Listeners by monthly listener count descending.

SQL conditions: - ORDER BY CAST({_n('Listeners')} AS BIGINT) DESC - LIMIT 20

Columns: Artist, Listeners, Daily +/-, Peak, Peak Listeners

Insight: Monthly listeners indicate reach. A high Daily +/- means the artist is currently growing or declining. Comparing current Listeners to Peak shows if they're at their height or past it.

Card 3: YouTube Top Artists by Total Views

Logic: Sorts YouTube Artists by total views descending.

SQL conditions: - ORDER BY CAST({_n('Total')} AS BIGINT) DESC - LIMIT 20

Columns: Artist, Total Views, Videos 100M+

Insight: YouTube views accumulate differently from Spotify streams. The "Videos 100M+" count shows consistency — an artist with 50 videos at 100M+ is more consistent than one with 1 viral hit.

6. YouTube (`/analysis/youtube`)

Purpose: YouTube-specific performance — daily charts, all-time records, and trending.

Country filter: No (global data)

Data sources: YouTube Daily, YouTube Toplists, YouTube Trending

Card 1: YouTube Daily Global Top 20

Logic: Sorts YouTube Daily by rank ascending. Shows today's most viewed videos globally.

SQL conditions: - ORDER BY CAST(Rank AS INT) ASC - LIMIT 20

Columns: Rank, Video, Views, Likes, Channel, YouTube

Insight: Daily YouTube chart movement. High views + high likes = genuine engagement. High views + low likes = potential clickbait or promoted content.

Card 2: YouTube All-Time Top 100 Most Viewed

Logic: Sorts YouTube Toplists by views descending.

SQL conditions: - ORDER BY CAST({_n('Views')} AS BIGINT) DESC - LIMIT 20

Columns: Video, Artist, Views, Yesterday, Days, Likes, YouTube

Insight: The all-time leaderboard. Yesterday column shows daily velocity — if Yesterday is high relative to total, the video is still actively accumulating views.

Card 3: YouTube Trending Now

Logic: Shows current trending videos from the trending page. Limited to first 20 entries.

SQL conditions: - LIMIT 20

Columns: Video, Position, Highlights, Type, YouTube

Insight: What YouTube considers trending right now. The chart_type column distinguishes between overall trending, music trending, etc.

7. Streaming Metrics (`/analysis/metrics`)

Purpose: Quantitative analysis of streaming performance — peak efficiency and velocity.

Country filter: Yes (defaults to India)

Data sources: Spotify Daily, Spotify Daily Totals

Card 1: Peak vs Total Streams

Logic: Joins Daily Totals data. Calculates Peak % = (Total / Peak_Streams) * 100. A high peak % means the song sustained near-peak performance. A low peak % means it spiked then faded.

SQL conditions: - country_code = '{code}' - CAST({_n('PkStreams')} AS BIGINT) > 0 - ORDER BY CAST({_n('Total')} AS BIGINT) DESC - LIMIT 15

Columns: Track, Days, Peak Streams, Total, Peak %, YouTube

Insight: Peak % is the key metric. A song with Peak % of 85% sustained most of its peak. One with 20% had a brief spike. Comparing Days to Peak % shows longevity vs burst.

Card 2: Stream Velocity (Today / 7-Day Average)

Logic: Calculates velocity ratio = Streams / 7Day. A ratio > 1.0 means the track is accelerating today. A ratio < 1.0 means it's decelerating.

SQL conditions: - country_code = '{code}' - CAST({_n('"7Day"')} AS BIGINT) > 0 - ORDER BY Velocity DESC - LIMIT 15

Columns: Track, Streams (today), 7-Day Average, Velocity (ratio), YouTube

Insight: Velocity > 2.0 is a strong viral signal. Velocity > 3.0 is explosive. Velocity < 0.5 means the song is fading fast. This is one of the most actionable metrics for trend prediction.

8. Cross-Platform (`/analysis/cross`)

Purpose: How tracks perform across different platforms — Shazam discovery pipeline, radio vs streaming.

Country filter: Yes (defaults to India)

Data sources: Spotify Daily, Shazam, Radio

Card 1: Shazam to Spotify Pipeline

Logic: JOINs Shazam with Spotify Daily on Artist_-_Title + country_code. Only includes tracks that are top 20 on Shazam AND top 50 on Spotify. This identifies the discovery pipeline — songs people are Shazaming that are already gaining traction on streaming.

SQL conditions: - sh.country_code = '{code}' - CAST(sh.Pos AS INT) <= 20 AND CAST(sp.Pos AS INT) <= 50 - ORDER BY CAST(sh.Pos AS INT) ASC - LIMIT 15

Columns: Track, Shazam Position, Spotify Position, Streams, YouTube

Insight: Shazam often leads Spotify by days or weeks. A track high on Shazam but low on Spotify is about to break. A track high on both has already broken through. The position gap between platforms indicates the pipeline stage.

Card 2: Radio vs Spotify

Logic: JOINs Radio with Spotify Daily on Artist_and_Title + country_code. Shows how radio airplay translates to streaming.

SQL conditions: - s.country_code = '{code}' - ORDER BY CAST(r.Pos AS INT) ASC - LIMIT 15

Columns: Track, Radio Position, Audience, Formats, Spotify Position, Streams, YouTube

Insight: Radio is a traditional discovery channel. A track high on radio but low on Spotify indicates older demographic appeal or upcoming release. High on both = mainstream crossover. Radio audience size indicates reach.

9. Predictive Signals (`/analysis/predict`)

Purpose: Forward-looking signals — songs about to break and songs about to fade.

Country filter: Yes (defaults to India)

Data sources: Spotify Daily

Card 1: Viral Breakout Candidates

Logic: Tracks in positions 100-500 that are young (<=14 days on chart) and already getting significant streams (today's streams > 50% of 7-day average). This catches songs early in their trajectory that are gaining momentum.

SQL conditions: - country_code = '{code}' - CAST(Pos AS INT) > 100 AND CAST(Pos AS INT) <= 500 - CAST({_n('"Streams+"')} AS BIGINT) > CAST({_n('"7Day"')} AS BIGINT) * 0.5 - CAST(Days AS INT) <= 14 - ORDER BY CAST({_n('"Streams+"')} AS BIGINT) DESC - LIMIT 15

Columns: Track, Position, Streams, 7-Day Average, Days, YouTube

Insight: A track at position 350 that's only 5 days old but already getting 100K daily streams is about to rocket. The Streams+ > 7Day * 0.5 threshold ensures the track has real momentum, not just a one-day blip.

Card 2: Fade Signals (Long-Runners Dropping)

Logic: Tracks that have been on the chart for 30+ days, are in the top 100, but have dropped more than 20 positions below their peak. The P+ column must not be = (indicating active movement, specifically downward).

SQL conditions: - country_code = '{code}' - CAST(Days AS INT) >= 30 - CAST(Pos AS INT) <= 100 - "P+" != '=' - CAST(Pos AS INT) > CAST(Pk AS INT) + 20 - ORDER BY CAST(Days AS INT) DESC - LIMIT 15

Columns: Track, Position, Streams, Days, Trend (P+), YouTube

Insight: A song that was at peak 5 and is now at position 30 after 45 days is fading. The gap between peak and current position (25 spots) combined with long charting duration confirms this is a natural decline, not a temporary dip.

10. Time-Based Analysis (`/analysis/time`)

Purpose: Temporal patterns — longevity, freshness, and decline trajectories.

Country filter: Yes (defaults to India)

Data sources: Spotify Daily

Card 1: Longest Charting in Top 100

Logic: Tracks that have been on the chart for 60+ days AND are still in the top 100. Sorted by days descending.

SQL conditions: - country_code = '{code}' - CAST(Days AS INT) >= 60 - CAST(Pos AS INT) <= 100 - ORDER BY CAST(Days AS INT) DESC - LIMIT 15

Columns: Track, Days, Position, Peak, Streams, YouTube

Insight: Longevity champions. A track at position 80 after 120 days is still relevant. Comparing Days to Peak shows the trajectory — did it peak early and sustain, or peak late?

Card 2: Fresh Fast-Risers (Published <30 Days, Top 50)

Logic: Tracks where the YouTube publish date was less than 30 days ago AND the track is already in the top 50. Uses days_since_published column (from YouTube metadata enrichment).

SQL conditions: - country_code = '{code}' - CAST(days_since_published AS INT) <= 30 - CAST(Pos AS INT) <= 50 - ORDER BY CAST(Pos AS INT) ASC - LIMIT 15

Columns: Track, Age (days since publish), Position, Streams, YouTube

Insight: Freshness indicator. A track published 5 days ago at position 12 is exploding. A track published 28 days ago at position 45 is steadily climbing. The age-to-position ratio is the key metric.

Card 3: Fading From Peak (Were Top 10, Dropped 20+)

Logic: Tracks that peaked in the top 10 but have since dropped more than 20 positions below peak. Sorted by the gap descending.

SQL conditions: - country_code = '{code}' - CAST(Pk AS INT) <= 10 - CAST(Pos AS INT) > CAST(Pk AS INT) + 20 - ORDER BY (CAST(Pos AS INT) - CAST(Pk AS INT)) DESC - LIMIT 15

Columns: Track, Position, Peak, Below Peak (gap), Days, YouTube

Insight: Decline trajectory. A song that peaked at #1 and is now at #45 after 80 days is fading. The gap (45 spots) combined with Days shows how fast the decline is. Songs with small gaps and low Days are still adjusting; large gaps with high Days are clearly declining.

Helper Functions Used

Function	Purpose	Used In
`_resolve_country(args)`	Gets country from query params, defaults to 'in'	All country-filtered pages
`_country_table(source_key)`	Returns latest DuckDB table name for a source	All pages
`_prev_table(source_key)`	Returns second-latest table for day-over-day comparison	Daily, Trends
`get_latest_file(source_key)`	Returns (table_name, latest_date)	All pages
`_q(sql)`	Executes SQL, returns (columns, rows) as string lists	All pages
`_n(col)`	Wraps column in REPLACE(CAST(...), ',', '') for safe numeric casting	Metrics, Trends, Top, Artists, YouTube, Cross, Predict, Time
`_country_list()`	Returns sorted list of country dicts for dropdown	All country-filtered pages

Key SQL Patterns

Anti-Join (New Entries / Exits)

-- New entries: in today but not yesterday
SELECT a.Track FROM today a
WHERE NOT EXISTS (SELECT 1 FROM yesterday b WHERE b.Track = a.Track)

-- Exits: in yesterday but not today
SELECT b.Track FROM yesterday b
WHERE NOT EXISTS (SELECT 1 FROM today a WHERE a.Track = b.Track)

Day-over-Day Comparison

-- Position change between two days
SELECT a.Track, a.Pos as Today, b.Pos as Yesterday,
       a.Pos - b.Pos as Change
FROM today a JOIN yesterday b ON a.Track = b.Track
ORDER BY Change ASC  -- negative = climbing

Velocity Calculation

-- Today's streams / 7-day average
ROUND(CAST(Streams AS DOUBLE) / GREATEST(CAST("7Day" AS DOUBLE), 1), 2) as Velocity

Peak Percentage

-- How much of peak performance is sustained
ROUND(CAST(Total AS DOUBLE) / GREATEST(CAST(PkStreams AS DOUBLE), 1) * 100, 1) as "Peak %"

Cross-Platform JOIN

-- Matches across platforms
SELECT sh.Track, sh.Pos as Shazam_Pos, sp.Pos as Spotify_Pos
FROM shazam sh JOIN spotify sp ON sh.Track = sp.Track AND sh.country_code = sp.country_code
WHERE sh.Pos <= 20 AND sp.Pos <= 50

How Analysis Works

Analysis Pages

Pages Overview

1. Daily Movement (/analysis/daily)

Card 1: Biggest Climbers Today

Card 2: New Chart Entries

Card 3: Tracks Leaving the Chart

2. Trends (/analysis/trends)

Card 1: Top Climbers vs Yesterday

Card 2: Viral Candidates (Big Stream Surges in Top 50)

3. Top Lists (/analysis/top)

Card 1: All-Time Most Streamed

Card 2: YouTube All-Time Most Viewed

Card 3: Spotify Top Artists by Total Streams

4. Countries (/analysis/countries)

Card 1: #1 Track in Every Country

Card 2: Spotify vs YouTube Overlap (Top 10)

5. Artists (/analysis/artists)

Card 1: Cross-Platform Artist Ranking

Card 2: Spotify Top Artists by Monthly Listeners

Card 3: YouTube Top Artists by Total Views

6. YouTube (/analysis/youtube)

Card 1: YouTube Daily Global Top 20

Card 2: YouTube All-Time Top 100 Most Viewed

Card 3: YouTube Trending Now

7. Streaming Metrics (/analysis/metrics)

Card 1: Peak vs Total Streams

Card 2: Stream Velocity (Today / 7-Day Average)

8. Cross-Platform (/analysis/cross)

Card 1: Shazam to Spotify Pipeline

Card 2: Radio vs Spotify

9. Predictive Signals (/analysis/predict)

Card 1: Viral Breakout Candidates

Card 2: Fade Signals (Long-Runners Dropping)

10. Time-Based Analysis (/analysis/time)

Card 1: Longest Charting in Top 100

Card 2: Fresh Fast-Risers (Published <30 Days, Top 50)

Card 3: Fading From Peak (Were Top 10, Dropped 20+)

Helper Functions Used

Key SQL Patterns

Anti-Join (New Entries / Exits)

Day-over-Day Comparison

Velocity Calculation

Peak Percentage

Cross-Platform JOIN

1. Daily Movement (`/analysis/daily`)

2. Trends (`/analysis/trends`)

3. Top Lists (`/analysis/top`)

4. Countries (`/analysis/countries`)

5. Artists (`/analysis/artists`)

6. YouTube (`/analysis/youtube`)

7. Streaming Metrics (`/analysis/metrics`)

8. Cross-Platform (`/analysis/cross`)

9. Predictive Signals (`/analysis/predict`)

10. Time-Based Analysis (`/analysis/time`)