# priority-tracks-cea

Priority tracks playlist monitoring and TTLO (Turn The Lights Off) chart/playlist pipelines.

## Pipelines

### Priority Tracks

Monitors Spotify playlist adds for priority artists. Runs daily to:

1. Query the latest available date from `priority_tracks_playlist_main`
2. Fill the main table day-by-day with new playlist history data
3. Rebuild the `priority_tracks_playlist_adds` hyper table (adds metadata, release dates, track status)
4. Refresh the Tableau extract

### TTLO

Chart and playlist tracking for artist KATO (product family key 1350666). Runs daily to:

1. Query the latest available date from both TTLO tables
2. Fill chart data (capped streams, chart thresholds, rankings) and playlist data (playlist streams) day-by-day
3. Rebuild both hyper tables with track/playlist metadata
4. Refresh the Tableau extracts

## Usage

```
uv run python -m src <mode>
```

| Mode              | Description                    |
| ----------------- | ------------------------------ |
| `priority-tracks` | Run priority tracks pipeline   |
| `ttlo`            | Run TTLO pipeline              |
| `all`             | Run both pipelines             |

Options:

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

## Project Structure

```
priority-tracks-cea/
├── pyproject.toml
├── sql/
│   ├── _create/
│   │   ├── init-main.sql
│   │   ├── init-ttlo-chart.sql
│   │   └── init-ttlo-playlist.sql
│   ├── date-query.sql
│   ├── date-query-ttlo.sql
│   ├── fill-main.sql
│   ├── fill-ttlo-chart.sql
│   ├── fill-ttlo-playlist.sql
│   ├── make-hyper.sql
│   ├── make-ttlo-chart-hyper.sql
│   └── make-ttlo-playlist-hyper.sql
└── src/
    ├── __main__.py
    ├── config/
    │   └── __init__.py
    ├── db/
    │   ├── __init__.py
    │   └── queries.py
    └── services/
        ├── __init__.py
        ├── priority_tracks.py
        └── ttlo.py
```

## Database

All tables are on **Redshift**.

| Key               | Table                                                  |
| ----------------- | ------------------------------------------------------ |
| `artist_ids`      | `sandbox.kbengi_prio_artists_int_dash`                 |
| `main_playlist`   | `prod_eu_analytics.priority_tracks_playlist_main`      |
| `hyper_playlist_adds` | `prod_eu_analytics.priority_tracks_playlist_adds`  |
| `ttlo_chart_main` | `prod_eu_analytics.turnthelightsoff_chart_main`        |
| `ttlo_chart_hyper`| `prod_eu_analytics.turnthelightsoff_chart_hyper`       |
| `ttlo_playlist_main` | `prod_eu_analytics.turnthelightsoff_playlist_main` |
| `ttlo_playlist_hyper`| `prod_eu_analytics.turnthelightsoff_playlist_hyper`|

Source tables: `spotify.agg_playlist_history`, `spotify.fact_streams`, `spotify.dim_playlists`, `common.dim_products`, `common.dim_calendar`, `common.dim_track_release_date`, `charts.vw_spotify_top200_daily`.

## SQL Templating

All SQL files use Jinja2 templates. Table names are injected via `QueryLoader` from the `TABLES` config dict. Runtime parameters (e.g. `date`) are passed as keyword arguments.

## Tableau

Site: `int_mktng`

| Extract                              | Datasource ID                          |
| ------------------------------------ | -------------------------------------- |
| `priority_tracks_playlist_adds`      | `4009e912-8fd8-46e1-8f42-fe2c59f5ab16`|
| `turnthelightsoff_chart_hyper`       | `2b348c40-694e-4dd4-a653-78cc292215db`|
| `turnthelightsoff_playlist_hyper`    | `defef367-9ed6-45cd-92ff-d3669c54fb6e`|

## 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`)