cohort churn cohort definitions differ

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

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.

Not specified by the original author.

TL;DR

Do not rebuild the churn-risk cohort first. A 400 invalid cohort id from Mixpanel almost always means the id in your request does not resolve inside the project you are actually calling, because the cohort was deleted, the id was copied from another project or workspace, or the service account lost access. List the cohorts in the target project, match by name, swap in the live id, and re-run the export before you touch the scoring job downstream.

Mixpanel cohort export failed with 400 invalid cohort id when pulling the churn-risk cohort

Use this when

• The export job or API call returns 400 and names an invalid cohort id
• The same cohort is visible in the Mixpanel UI under a project you own
• Churn scoring is stale because the cohort pull stopped, not because scoring failed

Not for this skill when

• You are creating a brand new cohort and have never exported it successfully
• Mixpanel itself is returning 5xx errors for every project, which is a vendor incident

Steps

1. Open the failing request and record the exact project id, organisation, and cohort id it sent, straight from the job configuration or code rather than from a dashboard URL you opened later. Project mix-ups are the top cause, and a URL from a second browser tab can silently point at a staging project with a similar name. Verify: the recorded project and cohort ids match what the job actually sent at runtime
2. In Mixpanel, open Lexicon and the Cohorts list for that exact project and search for the churn-risk cohort by name, then compare its live id with the id in the request. If the cohort was deleted and recreated during a cleanup, the recreated cohort carries a new id even though the name looks identical. Verify: the live cohort id is either confirmed identical or identified as different from the configured id
3. Check the cohort state and definition. A cohort still computing, archived, or built on an event that was hidden in Lexicon can fail to export cleanly, and a cohort whose underlying event was renamed returns an error that looks like a bad id. Re-save the definition once so Mixpanel revalidates every event and property it references. Verify: the cohort shows a completed computation with a non-zero user count, or the broken reference inside it is named
4. Confirm the calling identity can read that cohort. Service accounts in Mixpanel are scoped per project, and a consumer or analyst role in one project has no standing in another. Test the same export call with a project admin credential in a safe test run to separate a permissions failure from a missing cohort. Verify: the export succeeds with the admin test or still fails identically, which tells you whether access is the cause
5. Update the job to use the live cohort id, and better, make it resolve the cohort by name at startup through the cohort list endpoint so a recreated cohort never hard-breaks the pipeline again. Keep the resolved id in the run log so the next failure is diagnosable from logs alone. Verify: the export completes and the run log shows the cohort name, resolved id, and exported row count
6. Backfill the missed churn scoring runs from the successful export, dedupe accounts on the Mixpanel distinct id joined to your account id, and re-run health scoring for the gap window so CSMs are not working from a stale at-risk list. Verify: the at-risk list is refreshed for every missed day with no duplicate accounts

Why it happens

Cohorts are project-scoped objects with opaque ids, and pipelines tend to store the id as a magic constant copied once from a URL. Any project migration, cohort cleanup, or delete-and-recreate quietly orphans that constant. Mixpanel then correctly reports that the id is invalid in the project being queried, even though a cohort with the right name is sitting visible in the UI, often in a different project or under a new id.

Compatibility: Mixpanel cohorts, cohort export and list endpoints, and service accounts in current Mixpanel project settings, with downstream warehouse or Gainsight style scoring.

Variants and edge cases

Mixpanel export cohort not found 400

['The lookup phrasing teams use when the cohort was deleted or recreated. Treat it as an identity problem first, project and id, before suspecting the export format or the destination.']

cohort id invalid after project migration

['The migration variant. Moving a pipeline to a new Mixpanel project invalidates every stored cohort id, so audit all saved ids in the job, not only the one that failed first.']

churn cohort export empty or rejected in scheduled job

['The scheduled-job variant. Compare the interactive export, which uses your user permissions, with the scheduled export, which uses the service account, because the difference between them is usually the whole diagnosis.']

Edge cases to work through before you close the incident:

• A cohort built on a user profile property can export far fewer rows than the UI count suggests if profiles were merged. Reconcile counts on distinct id before declaring the export short.
• A cohort that updates on a schedule may still be computing when the export fires. Add a state check or a small delay rather than retrying instantly in a tight loop.
• Export destinations can reject a good export. If the API call returns 200 in a manual test, inspect the warehouse or S3 style destination step next.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_2R-ja7VYLrMc30dZCu36kA

Resolved from the public thread: https://vectle.com/posts/pst_2R-ja7VYLrMc30dZCu36kA

Thread query: Mixpanel cohort export failed with 400 invalid cohort id when pulling the churn-risk cohort

TL;DR: Do not rebuild the churn-risk cohort first. A 400 invalid cohort id from Mixpanel almost always means the id in your request does not resolve inside the project you are actually calling, because the cohort was deleted, the id was copied from another project or workspace, or the service account lost access. List the cohorts in the target project, match by name, swap in the live id, and re-run the export before you touch the scoring job downstream.