# Data audit report: RavenStack synthetic SaaS dataset

Demo · synthetic data. The source dataset is fully synthetic; its author describes it as created with Python and ChatGPT.
Source: https://www.kaggle.com/datasets/rivalytics/saas-subscription-and-churn-analytics-dataset (River @ Rivalytics, MIT).

## Scorecard

| Check | Dimension | What was tested | Rows tested | Rows failed | Status | Rule | Examples |
|---|---|---|---:|---:|---|---|---|
| A01 | Completeness | Subscription has no end_date (never closed) | 5,000 | 4,514 | fail | R2 | S-001561, S-0027d3, S-003647 |
| A02 | Completeness | Ticket has no satisfaction score | 2,000 | 825 | info | R13 | T-000c31, T-0024de, T-00644f |
| A03 | Completeness | Churn event has no feedback text | 600 | 148 | info | R13 | C-02a354, C-033d73, C-043665 |
| R01 | Referential integrity | Every foreign key resolves to a parent row | 32,600 | 0 | pass | - |  |
| T01 | Time logic | Paid subscription overlaps another paid subscription of the same account | 4,222 | 4,210 | fail | R2 | S-001561, S-0027d3, S-003647 |
| T02 | Time logic | Two paid subscriptions for one account start on the same day | 4,222 | 125 | fail | R3 | S-0380a3, S-050a91, S-09c1df |
| T03 | Time logic | end_date is before start_date | 5,000 | 0 | pass | - |  |
| T04 | Time logic | Usage is dated outside the window of the subscription it points to | 25,000 | 19,434 | fail | R7 | U-0000eb, U-000529, U-000844 |
| T05 | Time logic | Ticket submitted before the account signed up | 2,000 | 1,077 | fail | R8 | T-0016ab, T-00183b, T-0024de |
| T06 | Time logic | Churn recorded before the account's first subscription started | 600 | 53 | fail | R5 | C-049f10, C-05bc56, C-0c829f |
| T07 | Time logic | resolution_time_hours = closed_at - submitted_at | 2,000 | 0 | pass | - |  |
| T08 | Time logic | Row ends on the extract cut-off date itself (24 rows, against 5.6 a day for the rest of the month) | 486 | 24 | fail | R14 | S-1131ff, S-1e15d1, S-1e1853 |
| U01 | Uniqueness | Primary keys are unique | 8,100 | 0 | pass | - |  |
| U02 | Uniqueness | usage_id reused by two different usage rows | 25,000 | 42 | fail | R9 | U-0c9318, U-13ce5b, U-2103bb |
| U03 | Uniqueness | Same account churned twice on the same date | 600 | 2 | fail | R12 | C-ce47da, C-e20cc6 |
| V01 | Value logic | MRR = seats x list price (paid) and 0 (trial) | 5,000 | 0 | pass | - |  |
| V02 | Value logic | ARR = 12 x MRR | 5,000 | 0 | pass | - |  |
| V03 | Value logic | Row is flagged as both an upgrade and a downgrade | 5,000 | 23 | fail | R10 | S-11034a, S-149409, S-193d88 |
| V04 | Value logic | Upgrade/downgrade flag disagrees with the actual price change vs the prior row | 529 | 271 | fail | R10 | S-006908, S-01b2dc, S-0334aa |
| V05 | Value logic | Refund does not tie to any billed amount (memo only) | 600 | 142 | info | R11 | C-02719d, C-02a354, C-07ec1f |
| V06 | Value logic | Consecutive paid rows of one account: seat count more than doubles or falls by more than half (seat correlation between consecutive rows r = 0.49) | 3,722 | 1,209 | fail | - | S-003647, S-00837c, S-008cab |
| X01 | Cross-table consistency | Account plan_tier differs from its first subscription | 500 | 336 | fail | R6 | A-00cac8, A-019782, A-029f69 |
| X02 | Cross-table consistency | Account seats differ from its first subscription | 500 | 223 | fail | R6 | A-00bed1, A-0158bb, A-016043 |
| X03 | Cross-table consistency | Account churn_flag disagrees with whether it has any churn event | 500 | 312 | fail | R5 | A-00bed1, A-016043, A-029f69 |
| X04 | Cross-table consistency | Marked as a reactivation but the account has no earlier churn | 61 | 26 | fail | R5 | C-05bc56, C-1124ed, C-2447b3 |
| X05 | Cross-table consistency | Account flagged churned but billing is still live at the cut-off | 110 | 100 | fail | R5 | A-038089, A-08e34e, A-098560 |
| X06 | Cross-table consistency | Account flagged as "currently trialing" but paying at the cut-off | 97 | 88 | fail | R6 | A-00bed1, A-00cac8, A-016043 |
| X07 | Cross-table consistency | CRM churn event with billing still live 31 days later, after excluding events dated before the first subscription and events matched to a billing churn (550 have live billing 31 days later before those exclusions) | 599 | 505 | fail | R5 | C-020446, C-025044, C-02719d |
| X08 | Cross-table consistency | Paying customer changes tier from one month-end to the next, 2024 (December: 161 of 457 customers; expansion plus contraction = 53% of opening MRR) | 3,719 | 1,193 | fail | - | A-00cac8, A-0158bb, A-016043 |
| X09 | Cross-table consistency | Accounts table tier x seats differs from the plan version live at the cut-off under R2 | 458 | 387 | fail | R6 | A-00bed1, A-00cac8, A-0158bb |

Snapshot that shows the overlap problem: on 30 Jun 2024, 298 of 333 paying accounts had more than one live paid subscription row (up to 11).

## Raw to clean: rows and December 2024 MRR

| Step | Rows after | Row change | MRR at 31 Dec 2024 | MRR change |
|---|---:|---:|---:|---:|
| Raw subscriptions table, every row that has not ended | 5,000 | +5,000 | $10,159,608 | $10,159,608 |
| Remove trial rows (R1) | 4,222 | -778 | $10,159,608 | $0 |
| Remove same-day duplicates (R3) | 4,097 | -125 | $10,007,427 | -$152,181 |
| Remove zero-day rows (R4) | 4,087 | -10 | $10,007,427 | $0 |
| Rows that end on the cut-off date stay live (R14) | 4,087 | +0 | $10,078,435 | $71,008 |
| Close each version when the next one starts (R2) | 4,087 | +0 | $1,323,529 | -$8,754,906 |

## How much rides on rule R2

Which subscription row is live is a business rule; the data cannot prove it.
The two readings that treat rows as plan versions agree to about 6% on December MRR ($1,323,529 and $1,407,624) and on GRR (43% and 46%). They disagree on churn: under fall-back all 500 customers stay live, because 3,725 of the 4,087 kept rows never end, so every customer always has an open row to fall back to; each of R2's 33 December cancellations has one (33 of 33). Whether those rows are still billed is the question for the billing owner. R2 is the more conservative of the two readings: it books the 33 cancellations that fall-back would keep, the same prudence that keeps December in the forecast drivers.
Taking the highest open row would mean almost no customer's MRR ever fell (GRR 99.7%), and the accounts table matches neither the first subscription (336 of 500) nor the live version (387 of 458), so neither is used.

| Reading | Dec 2024 MRR | Dec churn, % of opening MRR | TTM NRR | TTM GRR | What this reading implies |
|---|---:|---:|---:|---:|---|
| R2: the newest paid row replaces the earlier one (used) | $1,323,529 | 7.2% | 109.3% | 43.4% | 33 customers cancelled in December; used. |
| Fall back to an earlier row still open when the newest ends | $1,407,624 | 0.0% | 118.0% | 45.7% | No customer churns in December: 3,725 of 4,087 kept rows never end, so all 500 customers stay live. |
| Highest-MRR row still open | $3,657,907 | 0.0% | 175.9% | 99.7% | Almost no customer's MRR ever falls (GRR 99.7%); not used. |
| Accounts table: current tier x seats | $566,721 | n/a | n/a | n/a | Matches neither the first subscription (336 of 500 tiers differ, X01) nor the live version (387 of 458, X09); not used. |

TTM = trailing twelve months, company level: customers paying in December 2023 and what they pay in December 2024. Dec churn is 0.0% under the fall-back and highest-row readings because every customer keeps an open row; the accounts table is one snapshot, so it has no churn or retention (n/a).

## Rule R14 on and off

| | Dec 2024 MRR | Dec churn, % of opening MRR | TTM NRR | TTM GRR |
|---|---:|---:|---:|---:|
| With R14 (used) | $1,323,529 | 7.2% | 109.3% | 43.4% |
| Without R14 | $1,307,280 | 8.3% | 107.1% | 43.1% |

## Mid-year reforecast against actuals (opening 2024-06, drivers 2024-01 to 2024-05)

| Line, Jul-Dec 2024 | Forecast | Actual | Variance |
|---|---:|---:|---:|
| new | $435,487 | $510,184 | $74,697 |
| reactivation | $22,076 | $46,526 | $24,450 |
| expansion | $343,126 | $194,557 | -$148,569 |
| contraction | $356,550 | $205,881 | -$150,669 |
| net migration | $226,897 | -$1,472 | -$228,369 |
| churn | $44,513 | $147,022 | $102,509 |
| Closing MRR, Dec 2024 | $1,553,161 | $1,323,529 | -$229,632 |

## Rows before and after cleaning, by table

| Table | Rows before | Rows after | Rules | Treatment |
|---|---:|---:|---|---|
| accounts | 500 | 500 | R6 | Kept every account; tier, seats, trial and churn status re-derived from the ledger |
| subscriptions | 5,000 | 4,087 | R1-R4 | Paid plan versions in the ledger; trials moved to a trial table, same-day duplicates and zero-day rows dropped |
| feature_usage | 25,000 | 25,000 | R7, R9 | All rows kept; re-used ids re-keyed; attributed to account and month |
| support_tickets | 2,000 | 923 | R8 | Tickets dated before signup left out of behaviour analysis |
| churn_events | 600 | 599 | R5, R12 | Same-day duplicate removed; reconciled against billing, not used to move revenue |

## Cleaning rules

- **R1** Trials carry no MRR and do not replace a paid plan. They stay in a separate trial table.
- **R2** Each new paid row for an account is the next version of that account's plan and replaces the prior version from its start date. A version ends at the earlier of its own end date or the next version's start.
- **R3** Two paid rows of one account starting the same day: keep the open one, then the higher MRR, then the lower subscription_id.
- **R4** A paid row that ends on the day it starts is dropped.
- **R5** The subscriptions table is treated as the billing system of record; the raw data has no invoices and no cash. CRM churn events and account flags do not move revenue; disagreements are listed as exceptions.
- **R6** Account tier, seats, trial status and churn status are re-derived from the ledger. Raw values are kept as 'recorded'.
- **R7** Usage is attributed to the account and month it happened in, not to the subscription row it points to.
- **R8** Tickets dated before the account signed up are left out of behaviour analysis.
- **R9** Re-used usage ids are re-keyed (second occurrence gets '-b'); both rows are kept because their contents differ.
- **R10** Upgrade and downgrade flags are ignored. Expansion and contraction come from the actual MRR change.
- **R11** Refunds on churn events are one-time credits: left out of MRR and reported as a memo line.
- **R12** Duplicate churn events (same account, same date) count once.
- **R13** Missing satisfaction scores and feedback stay missing. Nothing is imputed.
- **R14** A row whose end date is the extract date itself is treated as still live at the cut-off: the date looks stamped by the extract, not by a cancellation (check T08).