Read retention without comparing incomplete cohorts
Build retention tables with explicit eligibility, blank unobserved cells and matched-age comparisons, then aggregate rates from compatible counts.
Who could have produced this retention outcome?
A retention cell needs an eligible starting population and a completed observation period. If a September cohort has only reached month one, its month-three result is unavailable. Filling that cell with zero creates artificial deterioration. Keep incomplete periods distinct from completed periods with no retained customers.
Microsoft documents SUMPRODUCT as summing products of corresponding arrays. That can help with weighted calculations, but the correct weighting still depends on compatible populations and valid observations. A spreadsheet formula cannot decide whether an immature cohort belongs in the denominator. [SUMPRODUCT function]
An illustrative cohort triangle
| Cohort | Start | Month 1 | Month 2 | Month 3 |
|---|---|---|---|---|
| May | 100 | 70 / 100 = 70% | 60 / 100 = 60% | 55 / 100 = 55% |
| June | 80 | 60 / 80 = 75% | 48 / 80 = 60% | Not yet observed |
| July | 120 | 84 / 120 = 70% | Not yet observed | Not yet observed |
This example uses a fixed starting denominator, not conditional survival from one month to the next. A conditional definition would divide by a different eligible population and answer a different question. Name the method in the caption so the table can be interpreted without guessing.
At month one, all three cohorts are mature. Combined retention is (70 + 60 + 84) ÷ (100 + 80 + 120) = 214 ÷ 300 ≈ 71.33%. The simple average of 70%, 75% and 70% is approximately 71.67%, which gives each cohort equal weight despite different sizes.
What can be compared fairly?
Compare May, June and July at month one. Compare only May and June at month two. At month three, this table contains only May, so it cannot show whether later cohorts improved at that age. A blank region is evidence of observation limits, not a reason to extrapolate a trend.
The combined month-two rate for eligible cohorts is (60 + 48) ÷ (100 + 80) = 60%. Do not add July’s starting 120 customers to that denominator while leaving its unobserved numerator blank or zero. That would mix observed and unobserved opportunity.
Keep a retention-definition log
- Starting event: first paid purchase, activation or another explicit cohort entry.
- Retained event: the specific meaningful activity or paid state being measured.
- Period: calendar month, rolling interval or another declared age rule.
- Eligibility: exclusions, cancellations, refunds and incomplete identities.
- Timezone and cutoff: the boundary used to consider a period complete.
- Method changes: version and backfill decision when the definition changes.
Avoid comparing a purchase-based cohort with a login-based cohort under the same retention label. Both can be useful, but their numerators answer different questions. A lower purchase frequency does not automatically mean lower product engagement, and a login does not prove recurring revenue.
Test the table before drawing a trend
Use a fixture with one mature cohort, one partial cohort and one completed period with genuinely zero outcomes. Verify that aggregation excludes unobserved cells, exports preserve the distinction and charts do not connect unknown future values to zero. Keep original counts available for reconciliation.
I would review the eligibility rule before interpreting a colorful heat map. Once the table respects maturity and denominator boundaries, it can support a meaningful next question: which same-age cohort differs, and what evidence might explain that difference?
Sources
AI-assisted writing and an AI-generated editorial cover. Source links and checked dates are listed below; illustrative examples are labeled in the text.
- SUMPRODUCT function — checked 2026-10-18