# discoverydash-cea

Processes Spotify streaming discovery data across three sources — **playlist**, **search**, and **radio** — to measure how consumers discover music tracks. Feeds a Tableau dashboard.

## Data flow

1. **Update** (`update` mode) — For each source, finds dates with new `fact_streams` data since the last processed date. For each date, runs a discovery query that:
   - Identifies discovery consumers from `spotify.fact_streams`
   - Filters suspicious streaming activity (>50 streams/consumer/track)
   - Pivots consumer/stream counts by source type (source, collection, other)
   - Applies quantile filter (radio: 0.95, search: 0.97) with override ISRCs
   - Appends results to the source's main table

2. **Hypers** — Rebuilds denormalized Tableau-ready tables by joining main data with track metadata (`dim_partner_info`) and playlist info (`dim_playlists`). Adds Insights deep links for each track. Triggers Tableau extract refresh.

3. **Email** — Sends an HTML status email summarizing which sources were updated.

4. **Backfill** — Reruns discovery queries for a single source from a given base date, scoped to specific country codes (search/radio) or playlist URIs.

5. **Backfill overrides** — Backfills radio and search discovery data for all ISRCs in the `discoverydash_isrc_override` table. Runs a targeted query per source (no quantile ranking) for each date, inserting only rows where `(report_date, isrc_cd, country_code)` doesn't already exist in the source's main table.

## Sources

Each source uses a different `source_type_key` from `spotify.fact_streams`:

| Source | Key | Quantile filter |
|--------|-----|-----------------|
| Playlist | 638 | None (all included) |
| Search | 792 | 0.97 |
| Radio | 2906 | 0.95 |

## Tables

All tables are in `prod_eu_analytics`:

| Table | Purpose |
|-------|---------|
| `discoverydash_{source}_main` | Daily discovery data per source |
| `discoverydash_{source}_temp` | Staging table per date (dropped/recreated) |
| `discoverydash_{source}_hyper` | Tableau-ready denormalized tables |
| `discoverydash_isrc_override` | ISRCs always included regardless of quantile |
| `discoverydash_playlist_uri` | Playlist URIs to track |

## Usage

```bash
# Full daily pipeline: update tables -> rebuild hypers -> send email
uv run -m src update

# Rebuild hyper tables and trigger Tableau refresh only
uv run -m src hypers

# Backfill search data for specific countries from a date
uv run -m src backfill search 2024-01-01 US GB DE

# Backfill playlist data for specific URIs from a date
uv run -m src backfill playlist 2024-01-01 spotify:playlist:abc123

# Source shorthand (p/s/r) also works
uv run -m src backfill r 2024-06-01 FI SE NO

# Backfill radio and search data for all override ISRCs (all dates)
uv run -m src backfill-overrides

# Backfill override ISRCs from a specific date onwards
uv run -m src backfill-overrides --from 2025-01-15
```

### Options

```
--log-file, -l    Log file name (default: discoverydash-cea.log, stored in LOGDIR)
```

## Project structure

```
discoverydash-cea/
  pyproject.toml              # Dependencies (uv)
  src/
    __main__.py               # CLI entry point (update | hypers | backfill | backfill-overrides)
    config/                   # Tables, paths, country codes, quantiles, Tableau/email config
    db/                       # Singleton ReportingDB connection
      queries.py              # Jinja2 SQL template loader
    services/
      tables.py               # Discovery query execution (update + backfill)
      hypers.py               # Hyper table creation + Tableau refresh
      email.py                # HTML status email
  sql/
    date-query.sql            # Get last processed date + max available date
    discovery-query-*.sql     # Discovery ETL per source (Jinja2 templates)
    backfill-override-radio.sql  # Targeted radio backfill for override ISRCs
    backfill-override-search.sql # Targeted search backfill for override ISRCs
    hyper-query-*.sql         # Hyper table creation per source
    _create/                  # DDL for table initialization
  viz/
    DISCOVERYDASH.twbx        # Tableau workbook
```

## Environment variables

| Variable | Purpose |
|----------|---------|
| `INFAMOUSDIR` | Base path override (defaults to project root) |
| `LOGDIR` | Directory for rotating log files |
| Redshift credentials | Managed by `djagitit` (`ReportingDB`) |