How to do cohort retention analysis from customer data
Create retention cohorts by assigning each customer to a stable start period, defining an observable activity or paid status, and measuring the share retained at equal cohort ages. Exclude incomplete future cells, show cohort size, separate logo from gross and net revenue retention, and investigate acquisition, plan, or market differences only after the base calculation reconciles.
Clues in the source data
- The newest cohort shows many zeroes in future months.
- Retention exceeds 100% without being labeled net revenue retention.
- Customers move between cohorts after data refreshes.
- Percentages hide cohorts with only a few customers.
Why this gets messy
Retention tables become misleading when start dates shift, activity is undefined, revenue expansion is mixed with survival, or newer cohorts are compared at ages they have not reached.
From the field · Independent gym owner
Measure gym membership retention across joins, freezes, and cancellations
Members can freeze accounts, cancel and rejoin, or attend during a trial before paid membership begins. A simple monthly count cannot distinguish true retention from reactivation or incomplete observation.
Columns used in the example
member_id · membership_start · membership_end · freeze_start · freeze_end · visit_date · plan
Follow it with your own file
- Choose paid membership start as the cohort anchor and document how reactivations are handled.
- Define active status for freezes, grace periods, and cancellations.
- Calculate month-since-start for each eligible member and retain cohort denominators.
- Mark recent unobservable periods rather than filling them as zero retention.
Check: Spot-check individual timelines, reconcile each cohort's starting denominator, and confirm that a member appears at most once per cohort-period under the approved definition.
A method you can follow
- Define cohort and retention event: Choose first purchase, activation, or paid start, plus the event that proves retention and the inactivity grace period.
- Create customer-period status: remove duplicates from activity to one status per entity and period, then calculate integer periods since cohort start.
- Build the triangular matrix: Divide retained entities by original cohort size and leave periods not yet observable blank.
- Compare and explain: Add weighted overall curves, revenue measures, and selected segments with adequate sample size.
Retention metrics are not interchangeable
| Metric | Numerator | Can exceed 100%? |
|---|---|---|
| Logo retention | Retained customers | No |
| Gross revenue retention | Retained recurring revenue excluding expansion | No |
| Net revenue retention | Retained revenue including expansion | Yes |
Define what retained means
A login, transaction, active subscription, and positive monthly revenue describe different behaviors. Choose the event that matches the decision and state the allowed inactivity gap.
Reactivation also needs a rule. Decide whether a returning customer resumes the original cohort or begins a new lifecycle.
Read the heatmap diagonally and horizontally
Rows compare acquisition cohorts; columns compare equal ages. Calendar shocks appear diagonally because several cohorts experience them at the same calendar time. Onboarding problems often appear vertically at the same early age.
Pair the heatmap with cohort sizes and a weighted retention curve so color does not exaggerate tiny groups.
The short version
- Cohort age is different from calendar month.
- Show cohort size because small cohorts create unstable percentages.
- Keep logo, gross revenue, and net revenue retention separate.
- Never treat incomplete right-edge cells as churn.
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Calculate cohort retention with the correct cohort date, active definition, and observation window. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Cohort heatmaps · Time analysis · Segment comparison · Report generation
Final checks before using the result
- Cohort assignment stable
- Retention event explicit
- Reactivation rule defined
- Future cells blank
- Cohort sizes visible
- Revenue metric labeled
A retention view that reveals when customers disengage, whether cohort quality is changing, and which conclusions are supported by sufficient history.
Questions readers usually ask next
Should cohort retention use users or accounts?
Use the entity that buys or adopts the product and keep it consistent with the business decision.
How should partial months be handled?
Exclude or clearly label them; do not compare partial observation periods with complete periods.
What is a good retention rate?
There is no universal threshold. Compare with prior cohorts, product economics, segment mix, and the same retention definition.