Overview
Every SaaS dashboard shows MRR going up. The interesting question is what is churning underneath, and that only shows up when you compute the metrics from raw invoices instead of asserting them.
So I built the whole thing as a warehouse-native pipeline: two raw datasets, B2B order transactions and a subscription lifecycle table, flowing through a dbt staging to intermediate to marts architecture into a four-page dashboard whose centrepiece is a cohort retention heatmap.
$520K
MRR at June 2026, growing 5.1% month on month.
44
dbt data tests guarding sources, grains and metric logic.
4×
Faster churn on the Basic tier than Enterprise, trailing 12 months.
The data
12.5K invoices and 1,147 subscriptions land as dbt sources with tested contracts, and every mart is guarded by a grain test: 44 data tests, 52 of 52 building green, every column documented.
The metrics are computed rather than asserted. The MRR bridge decomposes each month into new, expansion, contraction and churned per customer, and the identity holds exactly: this month equals last month plus net new. NRR uses the 12-month cohort definition, and CLV ships both realized and predictive variants. Models compile on DuckDB locally and on BigQuery unchanged, via cross-database macros.
What the data says
Retention stops being one number once you have a customer by month revenue spine. It becomes a surface, and the surface disagrees with the headline chart.
Net revenue retention by plan tier
% of year-ago revenue retainedPro’s seat expansion outpaces its churn, which is what NRR above 100% means. Basic keeps only 68% of its year-ago revenue and churns about four times faster than Enterprise, at 6.2% a month against 1.4%.
The discount promo filled the funnel with customers who left.
The May to July 2024 discount-promo cohorts kept just 37 to 49% of customers at month six, against roughly 71 to 79% for the cohorts either side. Cheap signups, expensive churn.
Customers still active at month six, by signup cohort
% of cohort retainedThe promo quarter’s cohorts fall off a cliff by month six while every neighbouring cohort holds. On the full heatmap it reads as a dark horizontal band straight across the promo months.
The cheapest customers turned out to be the most expensive. The discount promo filled the funnel, and the cohort view watched it drain.
What is next
- 01Deploy the documented cloud path: the same dbt models on BigQuery, surfaced in Looker Studio.
- 02Swap the seeded generator for the real Kaggle datasets. The source tests already enforce the schema contract either way.
- 03Build the activity spine from coverage windows instead of invoices, so annual-prepay customers are handled correctly.