# spotify-contribution-cea

Spotify market share contribution and breakdown pipelines.

## Pipelines

### Contribution

Weekly pipeline computing SME and Orchard stream contribution per country. For each week:

1. Queries the latest available week from source tables (pro-rata, NC streams, orchard streams, charts)
2. For each week and country code, fills the main_v2 table with SME/Orchard streams, pro-rata scaled shares, and chart contribution (tracks with >40% SME/Orchard streams)
3. Rebuilds the hyper table with country names and week numbers
4. Refreshes the Tableau extract

### Market Share Breakdown

Weekly pipeline breaking down market share by source, license type, eligibility, local/international, track age, and stream volume. For each week:

1. Queries the latest available week from source tables (pro-rata, NC streams, playlist history)
2. Fills the breakdown table aggregated by all dimension combinations
3. Refreshes the Tableau extract

## Usage

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

| Mode             | Description                          |
| ---------------- | ------------------------------------ |
| `contribution`   | Run contribution pipeline            |
| `ms-breakdown`   | Run market share breakdown pipeline  |
| `all`            | Run both pipelines                   |

Options:

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

## Project Structure

```
spotify-contribution-cea/
├── pyproject.toml
├── sql/
│   ├── _create/
│   │   └── init-main-v2.sql
│   ├── date-query.sql
│   ├── date-query-ms-breakdown.sql
│   ├── fill-main.sql
│   ├── fill-ms-breakdown.sql
│   └── make-hyper.sql
├── src/
│   ├── __main__.py
│   ├── config/
│   │   └── __init__.py
│   ├── db/
│   │   ├── __init__.py
│   │   └── queries.py
│   └── services/
│       ├── __init__.py
│       ├── contribution.py
│       └── ms_breakdown.py
└── viz/
    └── Spotify Market Share Breakdown.twb
```

## Database

All tables are on **Redshift**.

| Key                  | Table                                                |
| -------------------- | ---------------------------------------------------- |
| `main_v2`            | `prod_eu_analytics.spotify_contribution_main_v2`     |
| `main_ms_breakdown`  | `prod_eu_analytics.spotify_market_share_breakdown`   |
| `hyper`              | `prod_eu_analytics.spotify_contribution_hyper`       |

Source tables: `stats.fact_nc_streams`, `stats.fact_nc_orchard_streams`, `spotify_reports.prorata_sony_weekly_v2`, `charts.vw_spotify_top200_daily`, `spotify.agg_playlist_history`, `common.dim_products`, `common_orchard.dim_products`, `common.dim_calendar`, `common.dim_countries`.

## Country Codes

**Contribution**: AT, BE, CH, CZ, DE, DK, FI, FR, GB, GR, IT, NL, NO, PL, SE, TR, US, ZA, NG

**Breakdown**: AT, BE, CH, CZ, DE, DK, EE, FI, FR, HU, IT, KE, LT, LV, NG, NL, NO, PL, SE, TR, ZA, US, GB

## Tableau

Site: `int_mktng`

| Extract                            | Datasource ID                          |
| ---------------------------------- | -------------------------------------- |
| `spotify_contribution`             | `e90da65a-a280-43df-a739-b85c1d05cf29` |
| `spotify_market_share_breakdown`   | `89a8299a-6ca1-4e3f-aae8-0c9cac7f6eb0` |

## SQL Templating

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

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