Revenue data and reconciliation
A public SaaS dataset that looks clean but isn't. Added up as-is, its subscription table overstates monthly revenue about eightfold. The raw tables were audited first, every cleaning rule is spelled out in SQL, and the result is a revenue ledger that ties to the cent. One rule decides most of the answer: which subscription row counts as live.
A public SaaS dataset that looks clean overstates monthly revenue about eightfold if you take it at face value. I rebuilt it into a subscription ledger: auditing the raw tables first, applying 14 cleaning rules written out in SQL, and tying revenue, the MRR bridge and deferred revenue to the cent — 129,354 automated checks, zero exceptions.
Apply the rules one at a time and December MRR goes from $10.16M to $1.32M. The other tabs hold the audit, the SQL, the MRR bridge and the reconciliation.
Add up every subscription row that has not ended, the way the raw table invites you to. 500 accounts appear to pay $10,159,608 a month. Each rule below is spelled out in the SQL so it can be argued with.
How it was built. I built it with an AI agent system I run. It wrote the pipeline and the workbook to my brief, I went through it myself, and a separate check re-derives its figures from the source data.
Each part opens on click.
Written the way it would go to a finance team. The data is synthetic, so the anomalies it chases are the dataset's, not a company's; the method is the point.
In one line: December closed at $1,323,529 of MRR, down 2.2% on November. Without 33 cancellations that billing has not confirmed, and that look like a data problem, MRR would have grown; new business held its pace.
What moved: New customers added $80,748 (26 customers) and returning customers $11,790 (8). Plan changes netted -$25,016: $346,971 of expansion against $371,987 of contraction. Churn took $97,564 across 33 customers.
What is real and what is a data problem: New-customer volume is in line with the prior six months. The churn is not renewal churn: of 48 plan versions that ended in December, 26 were annual contracts and none ended on an anniversary; the median version had run 8 days. The raw table opened 953 rows in December against 245 in May, so short-lived rows pile up at the end of the window. The plan-change volume (161 of 457 customers changed tier) is too high to be real seat changes, so expansion and contraction are not decision-grade.
Against forecast: The June reforecast expected $1,553,161 for December; the ledger closed $229,632 (14.8%) below it. Churn ran at 2.30% of opening MRR a month against the 0.62% assumed, most of it in December, and the MRR that tier moves added in January to May did not continue.
What is being done: The 505 CRM churn records with live billing go to revenue operations as a worklist. The 33 December cancellations go to the billing owner. Each of them has an earlier plan row with no end date, so the question is whether those rows are still being billed. The closest alternative reading of the subscription table gives $1,407,624; what rule R2 really decides is these 33 cancellations.
Forecast impact: Until billing confirms otherwise, December stays in the forecast drivers (July to December 2024) as a prudent assumption, the same way it is in the opening MRR. The Base case reaches $1,995,592 by December 2025; if the cancellations prove to be a data artefact, Base rises by about $346,666 to $2,342,258. After the reforecast miss, the 2025 drivers use the latest six months, and tier moves carry only the net effect measured in that window, which is close to zero.
Source: ravenstack_rebuilt_ledger.xlsx and monthly_commentary.md, written from the rebuilt ledger under rule R2. Synthetic data; it describes no real company.
Summing every subscription row that has not ended gives $10,159,608 at 31 Dec 2024. The gap grows every month as rows that never close pile up: 1.7x in 2023-06, 7.7x in 2024-12.
Owner: billing. The rule for which row is live (R2) has to be agreed with whoever owns billing before any SaaS metric is published.
Of 599 CRM churn records, 505 have no matching billing churn and billing still live 31 days later. 34 matched a billing churn, 53 were dated before the account's first subscription and 7 had neither.
Owner: revenue operations. Each record is either revenue the CRM wrote off by mistake or a cancellation billing never processed, so the list goes to revenue operations to clear.
166 of the 185 customers paying in December 2023 still pay in December 2024, yet before expansion they keep only 43.4% of their MRR (net 109.3% with it). In December 161 of 457 customers changed tier and expansion plus contraction came to 53% of opening MRR.
Kept out of the board pack until billing confirms the rule behind it.
48 plan versions ended in December, a median 8 days after they started. 26 were annual contracts and none ended on its anniversary; across all of 2024, 0 of 59 annual versions that ended did so on an anniversary.
Forecast stays prudent. Until billing confirms otherwise, the forecast keeps December in its drivers as a prudent assumption, the same way it is in the opening MRR; if the cancellations prove to be an artefact, the 2025 Base case rises by about $346,666.
| Reading of the subscription table | Dec MRR | Customers | Dec churn | 12-month GRR |
|---|---|---|---|---|
| R2: the newest paid row replaces the earlier one (used)33 customers cancelled in December; used. | $1,323,529 | 458 | 7.2% | 43.4% |
| Fall back to an earlier row still open when the newest endsNo customer churns in December: 3,725 of 4,087 kept rows never end, so all 500 customers stay live. | $1,407,624 | 500 | 0.0% | 45.7% |
| Highest-MRR row still openAlmost no customer's MRR ever falls (GRR 99.7%); not used. | $3,657,907 | 500 | 0.0% | 99.7% |
| Accounts table: current tier x seatsMatches neither the first subscription (336 of 500 tiers differ, X01) nor the live version (387 of 458, X09); not used. | $566,721 | 318 | n/a | n/a |
| R2 without R14 | $1,307,280 | 453 | 8.3% | 43.1% |
Source: sql/07_sensitivity.sql; the ledger uses R2. Synthetic data.
The worklist above, by category. Every record is in crm_churn_vs_billing.csv (under Files) for revenue operations to clear.
| CRM churn record, 31 days later | Records |
|---|---|
| CRM says churned, billing still live 31 days later | 505 |
| No live billing and no billing churn nearby | 7 |
| Matches a billing churn within 31 days | 34 |
| CRM churn before the first subscription | 53 |
| All CRM churn records | 599 |
30 checks on completeness, keys, time logic, values and cross-table agreement; 21 fail.
14 written cleaning rules (R1 to R14); one live plan version per account; raw-to-clean bridge in rows and dollars.
MRR bridge, customer roll, tier bridge, cohorts, company-level trailing-12-month NRR/GRR, channels, behaviour signals, CRM vs billing.
The same December MRR, churn and TTM retention under three other readings of R2, and with and without R14.
Billed in advance like Stripe or Zuora: plan changes inside a term prorated into the same term, credits for early closes, daily revenue recognition, deferred revenue roll-forward.
2025 MRR by tier from Jul-Dec 2024 drivers, Bear / Base / Bull; a June 2024 reforecast against Jul-Dec actuals.
Recomputes the ledger, the MRR and customer bridges, billing and deferred revenue a second way in plain Python, re-applies R2/R3/R4/R14 to the raw rows; 0 exceptions or the run fails.
The clean ledger plus account-month MRR, bridge, tier bridge, cohorts, channels, deferred revenue and forecast as live formulas on it; numbers are typed only on data sheets; blue inputs; a Checks sheet.
reconcile.py proves that the code does what the rules say. It cannot prove the rules are right; the R2 sensitivity table is there for that. The invoices it checks are generated from the ledger, so they test the billing code, not an outside source.
Synthetic data with seat counts that look re-drawn on every plan change; month-end MRR misses customers who join and leave inside a month; spend, margin and scenarios are assumptions; invoices are generated from the ledger.
Next: send the CRM-churn-but-still-billing list to revenue operations; agree rule R2 with the billing owner; confirm December against billing; replace the assumed spend with ledger marketing lines.
Source: RavenStack: SaaS Subscription & Churn Analytics Dataset, by River @ Rivalytics, MIT license. Synthetic; it describes no real company.
