# FanChants Dashboard — Discovery Report > Generated 2026-05-12 as Phase 1 of the live KPI dashboard build. > Inventory of every data source in the repo that surfaces a business metric. > The dashboard in `dashboard/index.html` consumes the sources flagged **Used by dashboard** below. --- ## 1. The canonical analytics store ### `FanChants_KPI_Master.xlsx` (repo root, 248 KB) **Used by dashboard.** This is the single source of truth and the file every other Routenote-derived metric in the dashboard derives from. It was built 2026-04-21 from 16 monthly Routenote sales files and a 4.37M-row raw workbook. **Read only.** Never written to from the dashboard. | Sheet | Rows | Columns | What it contains | Dashboard use | |---|---:|---:|---|---| | `README` | 26 | 1 | Build metadata, period coverage, totals. `Built at (UTC) 2026-04-21T00:32:28Z`, `Files ingested 16`, `Periods 2022-11 to 2026-02 (16 months)`, `Unique albums (non-null UPC) 610`, `Total net earnings $76,991.33`. | Footer "last refreshed from KPI workbook built at …" | | `Raw Index` | 17 | 5 | Map of `Raw ...` sheets in the gitignored 500 MB `FanChants_KPI_Raw.xlsx`. Row counts per period. | Not used (the heavy raw is gitignored). | | `Monthly Summary` | 18 (16 data + header + TOTAL) | 12 | The headline trend table. `Period, MonthTag (WC/EURO/NORMAL), SourceFile, Rows, Net_Earnings_USD, Streams, Downloads, Creations, Unique_Albums, Unique_Tracks, Unique_Retailers, Unique_Territories`. | **Drives Section A** — monthly revenue trend, streams trend, downloads trend, KPI cards. | | `Albums Master` | 611 | 10 | All distinct albums with lifetime totals. `UPC_EAN, Album_Title, Album_Artist, First_Seen, Last_Seen, Months_Active, Lifetime_Net_USD, Lifetime_Streams, Lifetime_Downloads, Lifetime_Creations`. | Top 10 albums by revenue, by streams; catalogue health KPI cards. | | `Per-Album 12mo` | 611 | 15 | Trailing-12 earnings per album, tournament-adjusted. `Raw_12mo_Net_USD, Normalized_12mo_Net_USD, Normal_Sum_USD, Normal_Months_With_Activity, Baseline_Per_Month_USD, Lifetime_Mean_Normal_Per_Month, Months_Active_In_Window, Streams_12mo, Downloads_12mo, Window_Start (2025-03), Window_End (2026-02), Tournament_Months_In_Window`. | Premium Tracker view; short-tracks risk overlay (annualised earnings × short-track ratio). | | `Store Mix` | 59 | 18 | Retailer × period pivot in USD. Top retailers: Spotify ($51.5K lifetime), Amazon Music Unlimited, YouTube Music, Apple Music. Last column is `Total_USD`. | **Drives Section A platform pie + stacked-bar over time.** | | `Country Mix` | 245 | 19 | Country × period pivot. `Territory Code, Customer Territory, 2022-11 … 2026-02, Total_USD`. Germany #1 at $21.7K lifetime, UK #2. | Section D regional view + Section A revenue-by-country choropleth-ish breakdown. | | `Top Tracks` | 201 | 8 | Top 200 tracks by lifetime net USD. `ISRC, Track_Title, Track_Artist, Album_Title, UPC_EAN, Lifetime_Net_USD, Lifetime_Streams, Lifetime_Downloads`. 1 row has null ISRC (catch-all). | Section A — Top 10 tracks. | | `Premium Upgrade Analysis` | 611 | 10 | Per-album upgrade verdict to Routenote $25/yr All Access. `Rank_By_Normalized_12mo, Normalized_12mo_Net_USD, Projected_3yr_Net_Free_USD, Premium_3yr_Uplift_Est_USD, Premium_3yr_Cost_USD, Premium_3yr_Net_Benefit_USD, Verdict ∈ {Strong, …}`. | Section F — Premium upgrade ROI tile + filter table. | | `Tournament Adjustments` | 121 | 9 | Per-album per-tournament uplift. `Tournament (WC/EURO), Period, UPC_EAN, Tournament_Month_USD, Baseline_Normal_Per_Month_USD, Uplift_USD, Uplift_Pct`. | Section F — tournament uplift heatmap and "biggest tournament beneficiaries" table. | | `Data Quality` | 8 | 4 | Severity + category notes from ingest. Flags 24 missing months between 2022-11 and 2026-02 (coverage gap), the 6.4 MB June 2025 file (likely partial), 2,113 NULL-ISRC rows, 1,531 duplicate rows, 265 negative-earnings rows summing $-5.58. | Section E — data quality banner; coverage gap chip on monthly trend. | ### `FanChants_KPI_Raw.xlsx` (repo root, 518 MB, **gitignored**) The full row-level raw — 4.37M rows across 16 parquet parts. Too large to load in the browser. Dashboard does **not** read this; the Master workbook is the right grain. ### `FanChants_Report_Backfill.docx` (repo root, 26 KB) Auto-generated narrative report. Not consumed by the dashboard — it's prose, not structured data. --- ## 2. Routenote raw monthly imports ### `Routenote Reports/RNSales_*.xlsx` (gitignored, 16 files, ~22 MB each) The source files the KPI Master is built from. **Not loaded by the dashboard.** Loading 16 × 22 MB into the browser would be unacceptable. The Master workbook already aggregates these. **Filename pattern:** `RNSales__Fanchants.xlsx`. Schema documented in `docs/operations/routenote-monthly.md` §2 — 19 columns, USD via `Earnings($)` column. Tournament months live in `Routenote Reports/tournaments/`. **Coverage gap:** 24 months are missing between 2022-11 and 2026-02 (2023-01 through 2023-12, 2024-01 through 2024-05, 2024-08 through 2025-02). This is a known Routenote restatement artefact, surfaced in the Data Quality sheet. --- ## 3. Spotify URL + artist mapping ### `scripts/spotify-urls/all_albums_with_urls.xlsx` (Albums sheet, 590 rows × 21 cols) **Used by dashboard.** The Routenote → Spotify URL mapper output. Per album: `Album Title, Album Artist, Earnings, Streams, Tracks, UPC, Spotify URL, Spotify ID, Spotify Match %, Spotify Matched Title, Spotify Matched Artist, Apple URL, Apple ID, Apple Match %, Notes, Spotify Artist URL, Spotify Artist ID, Spotify Artist Name, All Artists on Album`. 580/589 mapped (98.5%), covering 99.9% of revenue. The 9 unmapped albums are flagged for manual review. **Refreshed monthly** by the `monthly-routenote-update` Cowork task. ### `scripts/spotify-urls/all_artists.xlsx` (Artists 477 rows, Summary 9 rows) **Used by dashboard.** 476 unique Spotify artist profiles. Summary tells us total earnings covered = $4,677.34, total streams covered = 63M, merge candidates = 2. Artists sheet: `Spotify Artist Name, Spotify Artist URL, Spotify Artist ID, Total Albums, Total Earnings, Total Streams, RouteNote Artist Variants, Variant Count`. Used to power Section B's artist-profile breakdown + the merge-candidate flag. ### `scripts/spotify-urls/albums_master.csv` (589 rows, 7 cols) **Used by dashboard.** Lightweight catalog snapshot. `Album Title, Album Artist, UPC/EAN, earnings, streams, track_count, sample_isrc`. Joined to `all_albums_with_urls.xlsx` by UPC. Auto-updated monthly. ### `scripts/spotify-urls/FanChants_Top_Albums_URLs_Starter.xlsx` Legacy starter file — superseded by `all_albums_with_urls.xlsx`. **Not used.** --- ## 4. Catalogue snapshot (the song-level ground truth) ### `scripts/enrichment/chants_by_originaltune_snapshot.csv` (22,649 rows × 21 cols) **Used by dashboard.** Pulled from the FanChants Songs API, regenerated by `scripts/enrichment/build_chant_snapshot.py`. Per chant: `originaltune_id, originaltune_title, chant_id, chant_title_en, chant_title_native, team_id, team_name, league_id, league_name, country_name, has_audio, flag_reject, flag_webapponly, type ∈ {Anthems, Classic, Hall-of-fame, Avin-a-go, Funny, Vintage}, explicit, last_modified, composer, lyrics, blurb, in_copyright, license_notes`. This is the **canonical composer/lyricist source** — the standing rule says never web-research these. Drives Section B treemap (chants by country/league/team), the copyright breakdown card, the explicit-content card, and the "tracks by type" card. ### `scripts/enrichment/Original_Tunes_Data_-_Sheet1.csv` (1,428 rows × 19 cols) **Used by dashboard.** Master source for original-tune assignments. The hard-rule says only Giles modifies these. ### `scripts/enrichment/Original_Tunes_Enriched.csv` (1,428 rows × 35 cols) **Used by dashboard.** Same shape + 16 enrichment columns (`original_story, composer_background, chant_story, ...`) populated by the Sunday Cowork cron. Progress tracked in `scripts/enrichment/enrichment-rows/` (50 per-row JSONs as of this build). ### `scripts/enrichment/qa_report.csv`, `original_tunes_malformed_rows.csv` QA byproducts. Reflected in the Pipelines section as a "row health" chip. --- ## 5. Spotify playlist pipeline state ### `data/playlists/wc2026/playlist_specs.json` (54 playlists × ~26 fields) **Used by dashboard.** The WC2026 playlist build plan. `version 1, build_date 2026-05-03, flagship_url, accounts {official, giles_dev, personal_a/b/c/d}, playlist_count 54, playlists[]`. Drives Section C's playlist programme tile. ### `reports/wc2026/build_log.jsonl` (append-only) **Used by dashboard.** Per-publish line. Latest: 2026-05-12. 54 distinct spec IDs run; 18 actually published `ok`, 36 still in `dry_run_ok`. Total tracks added so far: 265. Drives Section C's publish-progress widget + last-run timestamp for the LaunchAgent. ### `data/playlists/wc2026/audit/official_playlists_audit.{json,md,xlsx}` **Used by dashboard.** Audit of the 197 owned + public `@FanChants official` playlists. Status buckets: `archive_candidate 0`, `personal_review 165`, `review 7`, `keep 25`. Surfaces the "Spotify 200-playlist profile cap" risk in Section C. ### `docs/strategy/playlists/FanChants_First_53_Playlists_Tracker_v1.3.xlsx` **Used by dashboard.** 14 sheets (README, Accounts, Invite Email, Team Data, Matrix, Descriptions, Football Calendar, World Cup Plan, Log, Baseline, KPIs, Decisions, Account Strategy, Trademark Ref). The Matrix sheet (73 rows) is the 53-playlist publish plan; the Log sheet (65 rows) is the live publish tracker. Drives Section C's "Track 2 — curator programme" cumulative count. ### `reports/wc2026/launchagent.official.{log,stderr,stdout}` LaunchAgent fire log. Latest entry 2026-05-12T13:32:46+0700. Drives Section E's "wc2026-daily-publish" pipeline health row. --- ## 6. Operational pipelines + cron state | Pipeline | Cron / trigger | Owner output | Used by dashboard | |---|---|---|---| | `monthly-routenote-update` | Cowork, 25th 08:00 ICT | `FanChants_KPI_Master.xlsx`, `all_albums_with_urls.xlsx`, `all_artists.xlsx` | Pipeline-health row + KPI workbook freshness chip. Last successful build inferred from KPI workbook README cell (2026-04-21). | | `daily-digest` | Cowork, 07:00 ICT daily | Email; no file artefact | Pipeline-health row only (chip = "active, last fire inferred from git log"). | | `original-tunes-enrichment` | Cowork, Sundays 22:00 ICT | `scripts/enrichment/enrichment-rows/row-{ID}.json` + assembled CSV | Progress bar (50/1,428 rows = 3.5%) + per-row JSON count. | | `wc2026-daily-publish` | macOS LaunchAgents (NOT Cowork — sandbox blocks Spotify HTTPS, see `docs/decisions/2026-05-11-wc2026-launchagent.md`) | `reports/wc2026/build_log.jsonl` + launchagent log | Last publish timestamp + per-account split. | | `video-vision-tagging` | Proposed Cowork cron 22:30 ICT nightly | `scripts/pictures/workspace/video-rows/*.json` + `catalog.csv` updates | Tagging-progress bar (20/652 videos = 3.1%). | | `league-update` (annual) | Manual (`--apply` disabled) | `reports/-global/anomalies.xlsx`, `reports//changes.csv` | Last-run sweep + applied-moves count. | --- ## 7. Pipeline output files ### `reports/2026-04-global/anomalies.xlsx` (2026-04 league sweep) **Used by dashboard.** Sheets: `Summary 30`, `Applied moves 2094`, `Low confidence 214`, `New teams 630`, `Out of scope 2885`, `Possible renames 54`, `League anomalies 143`, `Name hygiene 1340`. Drives Section E "last league sweep" tile. ### `reports/league-map.json` (297 entries) **Used by dashboard.** FC league → Highlightly map. Avg/min/max confidence score 106.6 / 50 / 120. Drives the "league coverage" chip in Section E. ### `reports/2026-04/changes.csv` (37 rows) **Used by dashboard.** English-pyramid promotion/relegation moves from April 2026. Sample row: `MOVE, Leicester City, premiership, championship, …`. Sectional tile under E. ### `reports/wc2026/{argentina_sandbox, full_dryrun, cleanup_log}.jsonl` Historical dry-run + cleanup state. Not surfaced unless someone clicks the WC2026 publish log tile. ### `reports/highlightly-cache/` (180 cached league files) Just an HTTP cache for the Highlightly fetcher — not a metric source. ### `reports/test-hl/`, `reports/test-hl-global/` Synthetic-universe test outputs. Not surfaced. --- ## 8. Fan photo + video pipeline (Canvas content) ### `scripts/pictures/workspace/catalog.csv` (5,511 assets) **Used by dashboard.** Catalogue of every fan-submitted photo + video. 4,859 photos (4,116 tagged, 743 untagged), 652 videos (20 vision-tagged, 602 in `pending_video_review`, 30 missing). Across 294 distinct team slugs, 42 countries. Production-status distribution: all `not_in_pool` so far (the production pool itself hasn't been opened yet). Treatment-required distribution: heavy_blur 2,179, excluded 815, light_blur 683, silhouette_only 300, none 172, unspecified 1,562. Vision-canvas-score distribution: 5 → 554 atoms, 4 → 972, 3 → 1,082, 2 → 641, 1 → 279, 0 → 1,223. Drives Section F's Canvas-content pipeline tile. ### `scripts/pictures/workspace/video_windows.csv` (header only — empty) The shippable-video-windows output. **Not yet populated.** Surfaced as "0 shippable windows yet" in Section F. ### `scripts/pictures/workspace/missing_videos.csv` 5 entries — 4 malformed paths, 1 truly missing. --- ## 9. Reference + brand assets ### `docs/reference/trademarks.md` + `docs/reference/trademarks.xlsx` **Used by dashboard.** Drives the team-name display layer — anywhere a club name shows in the UI, we use the "Preferred Naming" alternative (Gooners not Arsenal etc.). Already covered by the standing rules — the dashboard imports a slim JSON-encoded subset of the safe-name map. ### `docs/brand/fanchants-brand-guide.md` (v1.0.0 locked) **Used by dashboard styling.** Drives the palette and typography. **Palette:** - Black `#000000` (primary backdrop) - White `#FFFFFF` (primary type on dark) - Graphite `#1A1A1A` (dark UI / cards) - Fog `#F4F2EC` (light UI) - Grey-200 `#B4B2A9` (muted text) - Grey-600 `#5F5E5A` (captions) - Electric `#5000FF` (primary accent — play, CTA) - Hi-vis orange `#FF5A1F` (secondary — release energy) - Volt `#E6FF1A` (tertiary — tags, "now playing") - Bubblegum `#FF6FB1` (quaternary) **Typography:** IBM Plex Sans Condensed (Bold 700 — display), IBM Plex Sans (400/500/600 — body), IBM Plex Mono (400/700 — stats/dates). Loaded via fontsource CDN. No italic anywhere. **Tagline:** "The sound of the crowd" (sentence case, no period). ### `design/downloads/fc_logo_var.svg` (140-bar soundwave logo) **Used by dashboard.** Inline-embedded in the header, three-fill colour system honoured via `--fc-a` / `--fc-b` CSS variables. Renders at 28 px height in the header. ### `docs/reference/Cashflow 2025 - Account overview spread.csv`, `Cashflow 2026 - GB_MD Pay_DL.csv` Bookkeeping cashflow exports. **Not surfaced** — they're personal finance / partner-payment splits, not business KPIs. --- ## 10. Strategy + decision logs ### `docs/strategy/spotify-playlist-strategy.md` (993 lines, 76.5 KB) Long-form strategy. Not surfaced as a data source, but Section C's headline numbers (target 3,000–8,000 playlists / 700-by-Q2 / 2,500-by-year-end) come from here. ### `docs/decisions/` (5 dated decisions) The append-only "we decided X because Y" log. The dashboard pulls the filenames + first-line summary into Section E's pending-decisions tile. --- ## 11. Google Analytics 4 — web traffic **No credentials found at `~/.fanchants/ga-credentials.json`.** GA is **not configured** in this repo. Section D will render in degraded mode with a "Configure GA" panel that walks Mikey through: 1. Create a GCP service account 2. Grant the service account `Viewer` access on the GA4 property at `analytics.google.com → Admin → Property Access Management` 3. Download the JSON key to `~/.fanchants/ga-credentials.json` 4. Set the property ID in `dashboard/config.js` 5. Refresh Once configured, the dashboard will call the GA4 Data API via a tiny Python helper in `serve.py` (kept server-side so the service-account key never touches the browser), cache responses for 1 hour, and populate the GA section. Full setup walkthrough in `dashboard/README.md`. --- ## 11b. CRM dashboard snapshot ### `data/crm/snapshot.json` (generated, gitignored) JSON snapshot of the Django CRM (`webapp/crm/`), produced by the management command: ```bash cd webapp && python manage.py export_dashboard_snapshot ``` Top-level keys: `generated_at_utc`, `totals`, `deal_pipeline`, `campaigns`, `activity_7d`, `activity_28d`, `hot_leads_needing_action`, `gmail_credentials`, `email_verification_health`, `category_breakdown`, `recent_activity`. Full schema in `data/crm/README.md`. The dashboard reads this static JSON — it never queries the CRM database directly. The file is gitignored (contains contact + organization names); only the schema doc is tracked. The morning cron should run `export_dashboard_snapshot` immediately before `dashboard/build.py` so the Cloudflare deploy gets a fresh snapshot each day. --- ## 12. What we deliberately did NOT load - `FanChants_KPI_Raw.xlsx` (518 MB) — Master workbook already aggregates this. - `Routenote Reports/*.xlsx` (16 × ~22 MB) — same reason. - `docs/strategy/playlists/analysis/` — exploratory analyses, not live metrics. - `incoming/` — staging area for triage; nothing canonical yet. - `Downloads- could be anything/` — name says it all. Personal pile. - `reports/highlightly-cache/` — HTTP cache, not a metric. - `data/enrichment-pilot/` — May 2026 pilot run, superseded by the cron + the per-row JSONs in `scripts/enrichment/enrichment-rows/`. --- ## 13. Summary — sources by dashboard section | Section | Primary source(s) | |---|---| | A — Revenue & streams | KPI Master → Monthly Summary, Store Mix, Country Mix, Albums Master, Top Tracks | | B — Catalogue health | Catalogue snapshot CSV (22,649 chants); all_albums_with_urls.xlsx; all_artists.xlsx; Per-Album 12mo (short-tracks); Albums Master | | C — Spotify playlists | playlist_specs.json (54); build_log.jsonl; official_playlists_audit.json (197); FanChants_First_53_Playlists_Tracker_v1.3.xlsx | | D — Web analytics | GA4 Data API (graceful degrade until configured) | | E — Operational pipelines | KPI Master README + Data Quality; build_log.jsonl; launchagent log; enrichment-rows count; reports/2026-04-global/anomalies.xlsx; league-map.json; decisions log | | F — Anything else | Premium Upgrade Analysis; Tournament Adjustments; catalog.csv (pictures); short-tracks risk overlay (Per-Album 12mo); merge-candidate artists | | G — Social reach | data/social/social-metrics.csv (manual monthly entry); dashboard/youtube-snapshot.json (built) | | H — Finance | data/finance/financial-summary.csv (gitignored real rows, *-template.csv tracked); KPI Master streaming feeds | | I — Licensing pipeline | data/licensing/inquiries.csv (gitignored real rows, *-template.csv tracked) | | J — CRM (B2B outreach) | data/crm/snapshot.json (generated by `python manage.py export_dashboard_snapshot`, gitignored) | All chart-driving source paths are relative to the repo root, so `dashboard/serve.py` serves from there. --- *Updated by `dashboard/index.html` on every refresh — discovery is static, but the freshness chips on each tile read from the underlying source's modification time and value coverage.*