Spotify Listening Analytics — Seven Years of My Own Data

An end-to-end analytics engineering project that turns seven years of my personal Spotify streaming history into a tested data pipeline and an interactive dashboard. Built with PostgreSQL + dbt + Python, visualized in Tableau.

🔗 Live dashboard on Tableau Public

---

What this is

Spotify lets you export your complete streaming history. Mine covers June 2019 to July 2026 — every track I played, when, on what device, and how it ended. This project ingests that raw JSON, models it into a clean dimensional schema with automated data-quality tests, and answers questions about how my listening changed over seven years.

Raw data lands untouched, transformations live in version-controlled SQL, every model is tested, and the dashboard reads from a curated reporting layer rather than raw tables.

By the numbers

Streams analyzed	173,320
Hours listened	7,326
Distinct artists	6,342
Distinct tracks	20,673
Date range	Jun 2019 – Jul 2026
"Counted plays" (≥30s)	83.5%
Automated data tests	75

---

What I found

• Longest listening streak: 428 consecutive days, from 2022-08-31 to 2023-11-01. My current active streak as of 2026-07-13 is 98.
• I'm a pretty heavy evening listener. Listening intensity climbs through the day and peaks in the 8pm–midnight window, especially Sunday through Wednesday. The 2am–5am band is effectively dead. See the dashboard.
• My taste has been pretty constant recently but wasn't in the past. From 2020 to 2022, my top 20 artist retention was never higher than 15% (higher = more similar). From 2023 to 2026 retention ranged from 40% to 55%.

---

Architecture

Raw JSON flows through five layers, each with a single responsibility. This is the standard ELT pattern (extract, load, transform) — land the data first, transform it in SQL where the logic is versioned and testable.

Spotify export (15 JSON files)
        │
        ▼
  scripts/load_streams.py ──────►  raw.streams          (untouched landing zone)
                                        │
                                        ▼
                                  stg_streams            (cleaned, cast, typed)
                                        │
                    ┌───────────────────┼───────────────────┐
                    ▼                   ▼                   ▼
          int_streams_sessionized  int_daily_listening  int_artist_year_rank
                    │                   │                   │
                    ▼                   ▼                   ▼
   dim_artist  dim_track  dim_date  fct_streams  fct_sessions  fct_streaks  fct_taste_turnover
                                        │
                                        ▼
                          public_reporting.*  (aggregate-only views for the dashboard)
                                        │
                                        ▼
                                  Tableau dashboard

Layers:

• raw — the export loaded verbatim. Timestamps stay as strings, names keep their whitespace, nothing is cleaned. This makes the pipeline fully reproducible from the source files.
• staging — one cleaned row per stream: timestamps cast and localized, names trimmed, content split into music / podcast / audiobook, engagement flags derived.
• intermediate — reusable mid-step logic (sessionization, daily rollups, per-year artist ranks).
• marts — a dimensional star schema: fct_streams at the center, surrounded by dim_artist, dim_track, dim_date, plus derived fact tables for sessions, streaks, and taste turnover.
• reporting — thin, aggregate-only views the dashboard reads from. No raw timestamps, no IP addresses, no location data — safe to publish publicly.

---

Engineering decisions worth calling out

The interesting part of a data project isn't the SQL syntax — it's the judgment calls. A few from this build:

"A play" is grounded in a real rule, not an arbitrary cutoff. Spotify counts a stream for royalties at ≥30 seconds, so I use ms_played >= 30000 as the definition of a counted play throughout. Defensible and consistent.

Skips are derived from behavior, not trusted from the source. The export includes a skipped flag, but it's unreliable — in older exports it's frequently null, and even when populated it disagrees with actual behavior (tracks that played past 30s still sometimes carry a "skipped" flag). I derive skips from reason_end + play duration instead.

Artist and track names are deduplicated by frequency, not by title-casing. The same artist appears under multiple spellings across seven years ("SZA", "sza", " SZA "). I resolve each to its most frequently observed spelling with MODE() rather than INITCAP() — which would have mangled "SZA" into "Sza" and "channel ORANGE" into "Channel Orange".

A date spine prevents a silent, invisible bug. dim_date includes every calendar day, including days with zero listening. Without it, rolling averages compute over "days I listened" instead of "days that existed," silently overstating activity after any break. This is the kind of error that never raises an exception — it just quietly reports wrong numbers.

Timezone handling survives both export formats. Spotify's documentation shows naive timestamps, but real exports use ISO-8601 with a Z suffix. Casting via ts::timestamp AT TIME ZONE 'UTC' is correct for both — a bare ::timestamptz cast would silently shift the entire history by the server's local offset on any non-UTC Postgres instance.

Sessionization measures true idle time. Listening sessions are built with a gaps-and-islands pattern (LAG + running sum), but the gap is measured from the previous track's end to the current track's start — not end-to-end. Measuring end-to-end would treat every long podcast episode as a session break.

---

Testing

75 automated dbt tests run on every build — uniqueness, not-null, accepted values, referential integrity between facts and dimensions, and custom assertions (e.g. "no session overlaps another in time," "a skip is never also a counted play").

They were pretty useful in my testing — they caught real bugs such as:

• A uniqueness failure on dim_track surfaced 28 tracks where the same Spotify URI appeared under two different artist spellings, which would have double-counted those tracks in every downstream query. The test stopped the build before the bad data propagated.
• A taste-turnover set-logic bug where a FULL OUTER JOIN silently undercounted "dropped" artists (reporting zero every year). Caught by sanity-checking output against expectations, then fixed with an explicit union.

This is the whole point of the testing pipeline: the failures showed up at build time as specific, fixable errors

---

Tech stack

Layer	Tool
Database	PostgreSQL 16
Transformation	dbt-core 1.11 (+ dbt_utils)
Ingestion	Python (psycopg2)
Visualization	Tableau Public
Version control	Git

---

Project structure

spotify-analytics/
├── scripts/
│   ├── make_sample.py           # generates realistic fake data for dev
│   ├── inspect_export.py        # validates a real export before loading
│   ├── load_streams.py          # loads JSON → raw.streams
│   └── export_for_tableau.py    # dumps reporting views → CSV for Tableau
├── data/
│   ├── sample/                  # fake data (committed)
│   └── raw/                     # real export (gitignored — personal)
├── spotify_dbt/
│   ├── models/
│   │   ├── staging/
│   │   ├── intermediate/
│   │   ├── marts/
│   │   └── reporting/
│   └── tests/
├── requirements.txt
└── README.md

---

How to run it

Requires Python 3.12 and PostgreSQL.

# 1. Environment
python3.12 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

# 2. Point dbt at your database (~/.dbt/profiles.yml) and confirm the connection
cd spotify_dbt
dbt debug

# 3. Try it on realistic fake data first — no Spotify export needed
python ../scripts/make_sample.py
python ../scripts/load_streams.py --path ../data/sample
dbt build            # builds all models + runs all 75 tests

# 4. When you have your real export, inspect then load it
python ../scripts/inspect_export.py --path ../data/raw
python ../scripts/load_streams.py --path ../data/raw --no-pii
dbt build

# 5. Export the reporting views for Tableau
python ../scripts/export_for_tableau.py --out ../tableau_data

If you want to try: request "Extended streaming history" from your Spotify Privacy Settings. It takes a few days to a few weeks to arrive. Unzip the Streaming_History_Audio_*.json files into data/raw/.

A note on privacy: data/raw/ is gitignored — the export contains my IP addresses and location data. The --no-pii flag nulls those columns at load time, and the reporting layer exposes only aggregates, so the published dashboard and any committed CSVs are safe.

---

What I want to build next

• Query optimization case study — profile the heaviest analytical query with EXPLAIN ANALYZE, add indexes, and document the before/after (a ~87s naive rolling-average query is the obvious candidate).
• Genre enrichment via the Spotify Web API artist endpoint, to analyze genre mix over time.
• Discovery cohorts — group artists by the month I discovered them and measure how many I still listen to a year later.