# spotify-summer-2025-cea

Daily playlist position tracking for a curated set of priority tracks during the summer 2025 campaign.

## Pipeline

1. Queries the latest loaded date and the latest available date in the source view
2. For each new day, inserts playlist positions for the tracked ISRCs into the main table
3. Rebuilds the hyper (denormalized) table joining positions with track metadata, distributor info, and playlist classification
4. Refreshes the Tableau extract

## Usage

```
uv run python -m src
```

Options:

- `--log-file`, `-l` — Log filename (default: `spotify-summer-2025-cea.log`). Written to `LOGDIR` if set.

## Project Structure

```
spotify-summer-2025-cea/
├── pyproject.toml
├── sql/
│   ├── _create/
│   │   ├── init-isrcs.sql
│   │   └── init-main.sql
│   ├── date-query.sql
│   ├── fill-main.sql
│   └── make-hyper.sql
└── src/
    ├── __main__.py
    ├── config/
    │   └── __init__.py
    ├── db/
    │   ├── __init__.py
    │   └── queries.py
    └── services/
        ├── __init__.py
        └── summer.py
```

## Database

All tables are on **Redshift**.

| Key     | Table                                          |
| ------- | ---------------------------------------------- |
| `isrcs` | `prod_eu_analytics.spotify_summer_2025_isrcs`  |
| `main`  | `prod_eu_analytics.spotify_summer_2025_main`   |
| `hyper` | `prod_eu_analytics.spotify_summer_2025_hyper`  |

Source views/tables: `spotify.vw_spotify_playlist_history`, `spotify.dim_playlists`, `spotify.vw_ezpass_streams`, `spotify.vw_ezpass_streams_orchard`, `common.dim_countries`.

## Hyper Table

The hyper table denormalizes playlist positions with:

- **Track metadata** from the ISRCs reference table (artist, track name)
- **Distributor** — Sony / Orchard classification based on European streaming presence (>=25 countries = full, otherwise "in some mkts")
- **Playlist type** — classified as Chart, Hot Hits/Exitos, Summer, Radio, Mix, Daily Mix, Release Radar, Discover Weekly, or Other Editorial

## Tableau

Site: `int_mktng`

| Extract                | Datasource ID                          |
| ---------------------- | -------------------------------------- |
| `spotify_summer_2025`  | `fcf54de5-d64a-48cb-a863-bb925d1a0199` |

## Environment

- `INFAMOUSDIR` — Base path override (optional; defaults to project root)
- `LOGDIR` — Directory for rotating log files (optional)
- Redshift credentials are resolved by `djagitit` (`ReportingDB` / `ReportingDBSession`)