# skiprate-cea

Spotify skip rate computation for tracked playlists across FI, BENELUX, and FR markets.

## Pipeline

Incremental daily pipeline that:

1. Determines the date range (max date in main table + 1 day through max date in source tables)
2. For each day, builds a session table — stratified sample of consumers per playlist, detects listening sessions via 30-minute gap threshold, filters to sessions between 2–120 minutes
3. For each day, computes skip rates — aggregates streams and skips per (date, isrc, playlist), joins playlist positions, appends to main table

## Usage

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

No arguments required — the pipeline is fully automatic (picks up from the last processed date).

Logging is written to stderr and optionally to a rotating file in `LOGDIR`.

## Project Structure

```
skiprate-cea/
├── pyproject.toml
├── sql/
│   ├── _create/
│   │   ├── init-id-table.sql
│   │   └── init-main-table.sql
│   ├── append-skiprate-table.sql
│   ├── get-dates.sql
│   └── make-session-table.sql
└── src/
    ├── __main__.py
    ├── config/
    │   └── __init__.py
    ├── db/
    │   ├── __init__.py
    │   └── queries.py
    └── services/
        ├── __init__.py
        └── skiprate.py
```

## Database

All tables are on **Redshift**.

| Key                  | Table                                              |
| -------------------- | -------------------------------------------------- |
| `playlist_ids`       | `prod_eu_analytics.skiprate_playlist_ids`           |
| `playlist_positions` | `prod_eu_analytics.liveplaylisting_spotify_histo`   |
| `session`            | `prod_eu_analytics.skiprate_session`                |
| `temp`               | `prod_eu_analytics.skiprate_temp`                   |
| `main`               | `prod_eu_analytics.skiprate_main`                   |

Source tables: `spotify.fact_streams`, `spotify.fact_streams_l30`, `spotify.dim_partner_info`, `spotify.dim_access_types`, `spotify.dim_playlists`.

## Session Parameters

| Parameter                        | Value   |
| -------------------------------- | ------- |
| Consumers per playlist (sample)  | 5,000   |
| Session break threshold          | 30 min  |
| Minimum session length           | 2 min   |
| Maximum session length           | 120 min |

These are configured in `SESSION_PARAMS` in `src/config/__init__.py`.

## SQL Templating

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

`get-dates.sql` returns both `last_processed` and `last_available` in a single query — the main table's max date and the conservative max across the two source stream tables.

## Environment

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