# summer-playlists-cea

Tracking performance of curated summer playlists across European markets on Spotify. Five pipelines feed Tableau dashboards covering skip rates, discovery metrics, streaming stats, aggregated country-level stats, and live playlist positions.

## Pipelines

| Mode | What it does | Source tables |
|---|---|---|
| `skiprate` | Stratified consumer sampling (5 000 per playlist), session computation, skip rate calculation. Day-by-day. | `spotify.fact_streams`, `spotify.fact_streams_l30` |
| `discovery` | Rebuilds discovery hyper table when new data appears in the source. | `prod_eu_analytics.discoverydash_playlist_main` |
| `stats` | Appends per-track streaming stats with demographic breakdowns. Day-by-day. | `spotify.fact_streams` |
| `stats-agg` | Appends country-level aggregated playlist streams. Day-by-day. | `spotify.agg_playlist_history` |
| `live` | Rebuilds live playlist positions hyper table + refreshes live Tableau extract. | `prod_eu_analytics.liveplaylisting_spotify` |
| `all` | Runs skiprate → discovery → stats → stats-agg, then refreshes all regular Tableau extracts. | |

## Usage

```bash
uv run python -m src <mode>
uv run python -m src all
uv run python -m src live
```

## Project structure

```
summer-playlists-cea/
  pyproject.toml
  sql/
    _create/
      init-id-table.sql          # Playlist ID seed table
    get-dates.sql                # Shared date check (last_processed + last_available)
    make-session-table.sql       # Stratified consumer session computation
    append-skiprate-table.sql    # Skiprate daily append
    make-skiprate-hyper-table.sql
    make-discovery-hyper-table.sql
    append-stats-table.sql       # Stats daily append
    make-stats-hyper-table.sql
    append-agg-table.sql         # Stats-agg daily append
    make-agg-hyper-table.sql
    make-live-hyper-table.sql
  src/
    __main__.py                  # CLI entry point
    config/__init__.py           # Tables, paths, session params, Tableau config
    db/__init__.py               # ReportingDB singleton
    db/queries.py                # Jinja2 QueryLoader
    services/
      skiprate.py
      discovery.py
      stats.py
      stats_agg.py
      live.py
```

## Database tables

| Table | Description |
|---|---|
| `prod_eu_analytics.summer_playlists_ids` | Seed table of tracked playlist IDs by country |
| `prod_eu_analytics.summer_playlists_session` | Session table (rebuilt daily per skiprate run) |
| `prod_eu_analytics.summer_playlists_skiprate_main` | Skip rate data (appended daily) |
| `prod_eu_analytics.summer_playlists_skiprate_hyper` | Skip rate hyper table for Tableau |
| `prod_eu_analytics.summer_playlists_discovery_hyper` | Discovery hyper table for Tableau |
| `prod_eu_analytics.summer_playlists_stats_main` | Per-track streaming stats (appended daily) |
| `prod_eu_analytics.summer_playlists_stats_hyper` | Stats hyper table for Tableau |
| `prod_eu_analytics.summer_playlists_stats_agg_main` | Country-level aggregated stats (appended daily) |
| `prod_eu_analytics.summer_playlists_stats_agg_hyper` | Agg stats hyper table for Tableau |
| `prod_eu_analytics.summer_playlists_live_hyper` | Live playlist positions hyper table for Tableau |

## Tableau

Site: `int_mktng`

| Datasource | ID |
|---|---|
| SP_DISCOVERY | `9bbe75fe-f02d-4aaa-830c-cbd6d43e76d4` |
| SP_SKIPRATE | `556a419a-e36a-4152-9072-01b5a5855773` |
| SP_STATS | `6122f97e-ab0a-433c-918d-13ef2b908b26` |
| SP_AGGSTATS | `5f3a2db2-b54b-41d5-b81d-4cb107f13739` |
| SP_LIVE | `1ad26240-234c-4a26-932a-4f4bdfafd831` |