Weekly report: headline WoW delta uses a variable focus set, making weeks non-comparable

[sweetmantech]

Problem

The weekly performance report's headline "streams gained this week" is computed over a
focus set of ~25–30 tracks, not the full captured catalog. The composition of that set
varies week to week, so the headline number is not comparable across weeks — which defeats
the purpose of a weekly report.

LA EQUIS (Xcelencia) is the live example. Two consecutive emails:

Sent	Window	Headline in email	Full catalog (verified)	Full catalog / day
Mon Jul 20	Jul 13 → Jul 20	154,374	193,394 (8d)	24,174
Mon Jul 27	Jul 21 → Jul 27	52,224	121,465 (6d)	20,244

From the customer's side that reads as a ~60% collapse. His catalog actually eased −16%
per day, uniformly across every track (Candela −20%/day, Fds −20%, Astrokid −22%).

The divergence is entirely focus-set coverage: 43% of real catalog growth this week vs 80%
last week.

This matters more than usual because Xcelencia's own feedback was specifically that he wants
week-over-week deltas.

Verification

Direct query against song_measurements (platform='spotify',
metric='platform_displayed_play_count') for the 79 tracks in his Jul 27 13:01:29 capture batch.
Zero tracks missing at Jul 13, Jul 21 or Jul 27 — the capture pipeline is healthy. This is a
reporting-layer bug, not a data bug.

Secondary: "not captured" is misleading

Six El Niño Maravilla tracks rendered as "not captured". They are captured — they store 0, and
the prompt correctly says to treat 0 as missing rather than a real zero.

Root cause is ISRC duplication: QZTVM2635xxx are the album versions of tracks that also
exist as single releases. Spotify attributes the plays to the single, which is stored under its
own album row with the real count. So Candela, Fds, Brisa and Chamakito all appear twice —
once at 0, once with real numbers.

Effect: the album's per-project total is understated, and the customer sees "not captured" next
to songs that are actually performing.

Proposed fix

1. Compute the headline delta over the full captured catalog, not the focus set. Keep the
   focus set for the per-track table only.
2. Normalise to per-day (or always use a fixed 7-day window) so windows of different lengths
   are comparable. This week's window was 6 days, last week's was 8.
3. De-duplicate album-vs-single ISRCs before totalling, preferring the row with a non-zero
   count.
4. When a track's only ISRC reads 0, say "no plays recorded" rather than "not captured" —
   the current wording implies our pipeline failed.

Acceptance

• Headline delta covers the full captured catalog
• Deltas are per-day normalised or fixed-window, and the window length is stated
• Duplicate album/single ISRCs collapse to one row
• Two consecutive weekly emails for the same artist produce comparable headline numbers
• Backfill note to Xcelencia if we correct the record

Found while sourcing verified numbers for a marketing film. Not customer-reported — but he
received both emails.