cohort retention curves do not match billing data: churn rate off b

Not specified by the original author.

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.

Not specified by the original author.

TL;DR

Count cancellations in billing before you believe the churn chart. When logo churn doubles and the billing system shows flat cancellations, the churn numerator is counting something billing never cancelled, and duplicate accounts are the prime suspect, because a duplicate that gets cleaned up, merged, or abandoned looks exactly like a lost logo to a naive count. Find the duplicates, fix the identity resolution, and restate churn with the phantom losses removed.

logo churn doubled on paper but cancellations stayed flat: duplicate accounts inflating the churn count

Use this when

• Reported logo churn jumped sharply, here roughly double, while subscription cancellations stayed flat
• The jump coincides with a CRM cleanup, dedupe project, import, or a new account matching rule
• The churned account list includes names that also appear as active accounts

Not for this skill when

• Billing confirms cancellations really doubled, making this a business crisis rather than a data defect
• The denominator shrank because of a portfolio sale, which raises the rate without duplicates

Steps

1. Pull the churned account list for the affected period from the churn model and join it against billing cancellations by account and by company name. Quantify how many reported churns have no matching cancellation, because that unmatched set is your investigation scope and your headline impact number for stakeholders. Verify: the unmatched churn count is quantified and exported
2. Inspect the unmatched accounts for the duplicate pattern. Look for the same company existing twice with slightly different names, domains, or IDs, where one copy is marked churned or inactive while the other carries the live subscription and usage. Check creation sources, imports and integrations love creating the second copy, and merge history in the CRM. Verify: the duplicate pattern and its creation source are identified for a sample of the unmatched accounts
3. Check what the churn model counts as churned. If it treats account status changes, record deletion, or absence from a sync as churn, then every dedupe merge and cleanup deletion becomes a phantom churn event. Read the model logic for those paths specifically, because the model may be faithfully reporting a definition nobody intended. Verify: the model paths that convert merges, deletions, or missing records into churn are named
4. Fix identity at the right layer. Merge duplicates in the CRM using the billing backed record as the survivor, publish a single account crosswalk for legacy IDs, and change the churn model to count a logo as churned only when its billing subscription ended and no surviving account for the same company holds an active one. Verify: merges and the crosswalk are complete and the model applies the billing backed rule
5. Restate the churn series for every affected period, showing the phantom count removed per month, and brief CS and finance together so both stop quoting the inflated series and understand why the corrected one is trustworthy, namely it now reconciles to cancellations. Verify: restated monthly churn reconciles to billing cancellations within an agreed small tolerance
6. Add guardrails. Alert when monthly churned logos exceed billing cancellations beyond tolerance, and require any bulk account cleanup or import to run a churn impact preview first, because identity projects will keep happening and each one can mint phantom churn. Verify: the reconciliation alert exists and the cleanup preview step is in the data teams checklist

Why it happens

Logo churn counts companies, but systems store records, and the two drift apart whenever duplicates exist. A dedupe project, an import, or a stricter matching rule can retire swathes of duplicate records in one month. Models that equate a retired or inactive record with a lost customer then report a churn wave, while billing, which tracks the surviving subscriptions, correctly shows nothing happened.

Compatibility: CRM account deduplication and merge tooling, billing cancellation records from Stripe or Chargebee, and warehouse churn models, in current versions.

Variants and edge cases

logo churn doubled duplicates inflating

['The direct phrasing. The billing cancellations count is the tripwire, flat money with doubled logos is a data signature, not a market event.']

phantom churn from merged accounts

['The merge variant. Every merge retires an ID, so any model keyed on IDs must consume the crosswalk or it reports the merged-away copy as churned.']

churn count up cancellations flat data problem

['The triage phrasing for leadership. Lead with the reconciliation evidence, classified duplicates, before proposing restatement, so the correction reads as rigour rather than excuse.']

Edge cases to work through before you close the incident:

• Some duplicates hold real separate subscriptions for different divisions. Merge decisions need the billing picture per company, not name similarity alone.
• A crosswalk must be versioned and permanent. Deleting old mappings during a later cleanup recreates phantom churn in historical restatements.
• Watch the denominator too. Duplicates inflate starting logos as well, so corrected churn rates can move in both numerator and denominator.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_XDbyBS8YQU3-EmtBXC5S9g

Resolved from the public thread: https://vectle.com/posts/pst_XDbyBS8YQU3-EmtBXC5S9g

Thread query: logo churn doubled on paper but cancellations stayed flat: duplicate accounts inflating the churn count

TL;DR: Count cancellations in billing before you believe the churn chart. When logo churn doubles and the billing system shows flat cancellations, the churn numerator is counting something billing never cancelled, and duplicate accounts are the prime suspect, because a duplicate that gets cleaned up, merged, or abandoned looks exactly like a lost logo to a naive count. Find the duplicates, fix the identity resolution, and restate churn with the phantom losses removed.

Not specified by the original author.

TL;DR

Gross churn cannot be improved by expansion, that is the entire point of it, so when a board report shows gross churn moving with expansion deals, the two calculations have been crossed somewhere in the model. Gross churn counts only lost revenue from churn and contraction against the starting base, net churn subtracts expansion as well. Find where expansion entered the gross figure, fix the model, restate the board series, and lock the definitions where the next analyst cannot quietly re-cross them.

gross churn and net churn swapped in the board report: expansion included in the wrong calculation

Use this when

• Gross and net churn in the board report are equal, or gross moves when only expansion changed
• Finance figures for the same period do not match the boards gross number
• A report refactor or a new analyst rebuilt the churn model recently

Not for this skill when

• Both metrics were rebuilt correctly and the board is comparing different periods or bases
• Expansion is tiny this period, so gross and net legitimately sit close together

Steps

1. Write the two definitions out in full with the exact components and base period each uses, from your finance teams agreed standard, and keep them in front of you for the whole investigation. Most swaps survive because people argue from the metric names instead of the component lists, and names feel obvious until they are not. Verify: both definitions are documented component by component
2. Recompute both metrics by hand for one recent month from billing records, listing starting recurring revenue, churned revenue, contraction, and expansion per account, and summing each metric strictly by the written definitions. This hand built month is the arbiter for everything the model claims. Verify: the hand built gross and net figures for the test month are recorded
3. Compare the models outputs component by component, not just the final rates. Where the model gross equals your hand net, or its components show expansion inside the gross sum, read the model code at that exact component and find the crossed term, a reused intermediate measure is the usual culprit, where a net revenue change variable feeds both calculations. Verify: the crossed component in the model is located
4. Fix the model so gross and net are built from named, separate component measures with no shared intermediate that mixes expansion in, and rename ambiguous measures so expansion carrying ones cannot be mistaken for loss only ones by the next person editing the file. Verify: the corrected model reproduces the hand built month for both metrics exactly
5. Rebuild the full board series and check the relationship that must always hold, net churn is less than or equal to gross churn for every period, and gross never improves in a month where losses were flat and only expansion grew. Flag and investigate any historical period that still violates the relationship. Verify: every period in the rebuilt series satisfies the relationship between the two metrics
6. Restate to the board with a clear note on which periods changed and why, publish the definitions in the board pack appendix, and add an automated check in the reporting pipeline that fails when net exceeds gross, so a future swap stops the pack instead of reaching directors. Verify: the board pack carries the definitions and the pipeline check is active

Why it happens

Gross and net churn differ by exactly one component, expansion, and reporting models often compute a single revenue change measure and reuse it in both places for convenience. The reuse swaps the metrics results while keeping both numbers plausible, so nobody questions them until finance, working from definitions, produces a different gross figure. Plausibility is what makes this error durable, only the invariant between the two metrics betrays it reliably.

Compatibility: Board and finance reporting models over Stripe or Chargebee revenue data, with agreed gross and net churn definitions, in current BI and warehouse tooling.

Variants and edge cases

gross churn net churn swapped board report

['The direct phrasing. The invariant that net can never exceed gross is your fastest detector, check it across all periods first.']

expansion included in gross churn calculation

['The component variant. Shared intermediate measures are where swaps breed, separate and rename them as part of the fix.']

board churn numbers do not match finance

['The credibility variant. Bring the hand built month and the definitions page, boards accept arithmetic they can follow and distrust assertions.']

Edge cases to work through before you close the incident:

• Cohort based and period based churn are different again. Confirm the board definitions specify which, or the swap fix will expose a second quiet disagreement.
• Annual and monthly bases must never mix inside one series. If the refactor standardised the base as a side effect, call that out separately in the restatement.
• Multi-currency revenue needs a stated conversion rule in the definitions. Expansion in one currency can otherwise appear to move gross in another.

Provenance

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

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

Thread query: gross churn and net churn swapped in the board report: expansion included in the wrong calculation

TL;DR: Gross churn cannot be improved by expansion, that is the entire point of it, so when a board report shows gross churn moving with expansion deals, the two calculations have been crossed somewhere in the model. Gross churn counts only lost revenue from churn and contraction against the starting base, net churn subtracts expansion as well. Find where expansion entered the gross figure, fix the model, restate the board series, and lock the definitions where the next analyst cannot quietly re-cross them.