cohort retention curves do not match billing data URIs churn rate off by 8 points after the warehouse migration

TL;DR

Do not average the two numbers and move on. An 8 point gap after a migration means one side changed what it counts, when it counts it, or which history it kept, and billing is the harder system to fool. Rebuild both calculations from raw records for one cohort month, find the definitional or migration difference line by line, fix the retention model, and restate the affected curves with the reason attached.

cohort retention curves do not match billing data URIs churn rate off by 8 points after the warehouse migration

Use this when

• Cohort retention and billing churn disagree by several points, here about 8, since the warehouse migration
• The gap appeared at the migration date or affects migrated history more than new data
• Finance and CS are quoting different churn numbers for the same cohorts

Not for this skill when

• The two reports always used different definitions by design and are now being compared for the first time
• The difference is within normal timing noise of a day or two at period edges

Steps

1. Freeze one test case. Choose a single cohort month and a single later month, and pull the raw account list each system counts as starting, retained, and churned for that cell, with account IDs, from billing and from the warehouse model. Aggregate agreement hides everything, account level lists expose it immediately. Verify: the two account lists for the test cell are exported and comparable by ID
2. Diff the lists and classify every mismatched account. The usual classes after a migration are accounts missing from migrated history, accounts duplicated under a second ID, cancellations recorded on the invoice date in one system and the subscription end date in the other, and trial or free accounts included in one denominator only. Count each class so you fix causes in size order. Verify: every mismatched account belongs to a named class with a count
3. Audit the migration itself for the biggest classes. Check row counts per source table before and after migration, any filters the migration applied such as dropping pre-2023 history, timezone conversion on timestamps, and whether subscription records that were already cancelled at migration time were loaded as active with their cancellation applied later or never. Verify: migration defects matching the mismatch classes are confirmed or cleared
4. Agree the single churn definition in writing with finance and CS, covering the denominator, what counts as churned and when, and how trials, pauses, and reactivations are treated. Most long running disagreements survive because each system quietly encodes a different answer to one of those questions. Verify: the definition is written, owned, and signed off by both teams
5. Fix the retention model or the migrated data according to that definition, rebuild the curves, and validate three cohort cells by hand from raw billing records, including one pre-migration and one post-migration cohort, because a fix that only works on new data leaves history wrong. Verify: three hand built cells match the rebuilt model exactly
6. Restate and annotate. Publish the corrected curves with a note on what changed and which months moved, keep the old series visible for one cycle, and add a monthly reconciliation query comparing model churn counts to billing counts with an alert beyond a small tolerance so drift is caught in weeks, not quarters. Verify: the restated curves are published and the monthly reconciliation alert is active

Why it happens

Retention curves are ratios built from identity, timing, and inclusion rules. A warehouse migration can change any of the three silently, history truncation changes denominators, ID re-keying splits accounts, and timestamp shifts move churn across month boundaries. Billing, which moves money on its dates, stays comparatively stable, so the gap is the warehouse telling you its picture of the past changed during the move.

Compatibility: Cohort retention models in Snowflake or BigQuery style warehouses, billing systems such as Stripe or Chargebee as the record source, and migration validation, current versions.

Variants and edge cases

cohort retention does not match billing churn

['The reconciliation phrasing. Account level diffing for one cell is the method, arguing over definitions in the abstract never converges.']

churn rate off after warehouse migration

['The migration variant. Suspect history filters, ID re-keying, and timestamp conversion in the migration before suspecting the new model logic.']

warehouse churn vs finance churn 8 points

['The stakeholder variant. Bring finance the classified mismatch list, it turns a credibility fight into a shared defect list with owners.']

Edge cases to work through before you close the incident:

• A cohort cell with small numbers swings wildly on a few accounts. Reconcile on a large mature cohort first, then apply the lessons to small ones.
• Reactivations are the definition question teams forget. An account that churns and returns inside the window must have one agreed treatment or the gap reappears.
• Keep the migration snapshots. Without the pre-migration data you cannot prove which side changed for historical months.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstiJRfxSTcPXXEBFoi_kzMg

Resolved from the public thread: https://vectle.com/posts/pstiJRfxSTcPXXEBFoi_kzMg

Thread query: cohort retention curves do not match billing data URIs churn rate off by 8 points after the warehouse migration

TL;DR: Do not average the two numbers and move on. An 8 point gap after a migration means one side changed what it counts, when it counts it, or which history it kept, and billing is the harder system to fool. Rebuild both calculations from raw records for one cohort month, find the definitional or migration difference line by line, fix the retention model, and restate the affected curves with the reason attached.