Core user metrics, made reliable

The problem

Spotify's EPIC team owned user-activity metrics for the core mobile product. The figures mattered across the business, from executive and product reporting to external/public-market reporting.

The underlying SQL pipelines were unstable, unwieldy and not idempotent. Behavioural telemetry also arrived late, duplicated itself and required fraud adjustment before it could support trusted reporting.

Rebuilding the data flow

USQ's Principal Engineer lifted and shifted the reporting pipelines into organised, idempotent dbt DAGs. The work covered DAU, WAU, MAU, ad impressions, crashes, engagement sessions, content consumption, saves, and artist or show connections.

They also built marts and analytics layers in Scio and Apache Beam on Google Dataflow, turning large-scale behavioural telemetry into cleansed, aggregated and fraud-adjusted datasets.

Data correctness

The reporting accounted for late-arriving facts, duplicates, replays and out-of-order delivery. Fraud analytics excluded bot traffic, synthetic users, spam interactions and invalid ad engagement.

Operating the pipelines

Streaming and batch-derived datasets were reconciled, with testing, lineage, runbooks, orchestration, alerting and incident response for the pipelines behind the reporting.