cohort_retention_analysis v1.0.0

name: "cohort_retention_analysis" description: "Run cohort retention analysis end to end: define cohorts, pick the right retention variant, build the triangle matrix in SQL or Python, read curves, and check feature adoption by cohort without fooling yourself on confounders. Use when someone asks about retention, churn by cohort, D1/D7/D30, retention curves, net revenue retention, or whether newer users stick better than older ones."

Cohort Retention Analysis

Purpose

Answer three questions with numbers that hold up: do users come back, is that getting better or worse for newer cohorts, and which early behaviors or features line up with staying. Aggregate retention hides all three. A monthly retention rate mixes young and old cohorts, so it moves when growth speeds up even if the product did not change. The cohort view fixes the denominator, fixes the age, and makes the comparison fair.

This skill ships the full method: definitions first, then the matrix, then the read, then the confounder checks. A single prompt that spits out percentages without those steps will produce a chart that looks right and reads wrong.

Workflow

1. Fix three definitions before any query. Write them down and put them on the report.

• Cohort anchor: signup, first meaningful action, first purchase, or first activation. For products with delayed value, anchoring on signup mixes tourists into every cohort. Pick the anchor that marks the start of real use.
• Activity event: the event that means real use, not a login or an app open by itself. One event, named exactly as it appears in the data.
• Period and retention variant: see step 2. Mixing variants across charts is the most common silent error.

1. Pick the retention variant and say which one you used.

• Classic / N-day: active exactly on day N. Strict. Fits daily-use products and games. This is Amplitude Return On and Mixpanel On.
• Unbounded / rolling: active on day N or any day after. Fits low-frequency products where daily precision punishes normal gaps. This is Amplitude Return On or After. It restates as later data arrives, and it always runs higher than classic on the same data.
• Bracket / bounded interval: active inside period N, for example week N or month N. Fits subscription and B2B work, and most SQL cohort tables. Each bracket stands alone. Pick by cadence: daily products use days, weekly products use weeks, monthly and B2B products use months. If you change variant, you changed the metric. Do not trend across the change.

1. Choose granularity and check cohort size. Daily cohorts need thousands of signups per day or the percentages are noise. Weekly is the default for most consumer products. Monthly fits low volume or long sales cycles. Prefer cohorts with at least 100 users. Flag any cohort under 50 as noisy and do not draw conclusions from it alone. Pool small cohorts into a wider window instead of reading jitter.

1. Build the triangle matrix. Rows are cohorts, columns are age since anchor (period 0, 1, 2, and so on), cells are percent of the original cohort active in that period. Period 0 is 100 percent by definition for a signup anchor, or the anchor event rate for other anchors. Use the SQL in references/sql-patterns.md or the bundled CLI in bin/cohort.py for CSV data. Two hard rules:

• Denominator is the original cohort size in every cell. Never the survivors, never the active base that month.
• A cell the cohort has not lived long enough to reach is blank and labelled not mature. It is never zero. Plotting an immature cell as zero invents churn that has not happened yet.

1. Read the matrix in two directions, separately.

• Across a row: one cohort over time. This is the retention curve. Note where it drops, where it flattens, and at what level.
• Down a column: same age, different cohorts. This is the only fair way to ask if retention is improving. Compare January at month 3 only with February at month 3, never with February at month 1. Report both reads. A good D7 number with a curve that never flattens is still a leak.

1. Diagnose the curve shape. Use references/curve-diagnosis.md.

• Flattening to a non-zero plateau: a retained core exists. Work on moving more users into that core through activation.
• Steady decay toward zero with no flattening: no stable core for that cohort. Acquisition spend will leak out. Fix the core loop first.
• Sharp cliff at one age: look at what happens at that age. Trial end, paywall, first renewal, content running out, a forced upgrade.
• Curve lifts back up after a dip (smile): reactivation or a seasonal product. Check calendar effects before claiming a win.
• Newer cohorts sit above older cohorts at every age: something improved. Name the change that lines up with the cohort start, then test the claim in step 8.

1. Do feature adoption by cohort, the careful way.

• For each feature, compute adoption by cohort age: percent of the cohort who used the feature at least once by age N, percent who used it inside period N, and median time from anchor to first use.
• Split the retention matrix by adopters vs non-adopters using a fixed early window, for example used the feature in the first 7 days. Align both groups to the same window.
• State the bias in the report: users must survive long enough to adopt, so adopters start with an advantage (immortal time bias), and engaged users adopt more features anyway. An adopter gap is a lead for an experiment or a staggered rollout, not proof the feature causes retention. Full method in references/feature-adoption.md.

1. Run the confounder checks before you conclude anything. Full list in references/confounders.md.

• Mix shift: did channel, plan, geography, or platform mix change between the cohorts you compare? If yes, segment first, then compare inside each segment. An overall lift that reverses inside every segment is Simpson's paradox, not a product win.
• Seasonality and weekday: compare like periods, watch day-of-week for daily cohorts, and compare year over year when the product is seasonal.
• Calendar events: releases, pricing changes, outages, and campaigns that hit one cohort at a specific age. Annotate them on the cohort axis.
• Survivorship: confirm churned users are still in the denominator and that no filter dropped users who never returned.
• Maturity: confirm every compared cell is mature for every cohort in the comparison.

1. Add revenue cohorts when money matters.

• Logo retention counts accounts. Dollar retention weights them. Report both when they tell different stories.
• Net revenue retention (NRR) for a fixed starting cohort: (starting revenue + expansion - contraction - churned revenue) / starting revenue. New customers are excluded by construction. Above 100 percent means the starting base grew on its own.
• Gross revenue retention (GRR) is the same formula without expansion. It caps at 100 percent and isolates how much revenue simply stayed. Worked numbers in references/worked-studies.md.

1. Write the report and name the decision. Use the Output Contract below. Every finding gets evidence, a likely cause labelled as a hypothesis unless an experiment proved it, and a next action. If the data cannot support a conclusion, say so and say what data would.

Output Contract

A cohort report must contain:

• Definitions block: anchor, activity event, period, retention variant, timezone or day-boundary rule, and the data window.
• The matrix: cohort, cohort size, and retention percent by age, with immature cells blank or marked NM (not mature). Counts next to percents for any cohort under 500.
• Curve read: shape, flattening level if any, and the largest single-period drop with its age.
• Same-age comparison: at least two cohorts compared at the same ages, with the difference in percentage points.
• Segment cuts used, and the mix-shift check result.
• Feature adoption section when features were asked about: adoption by age, adopter vs non-adopter retention with the bias warning, and the experiment or rollout that would test causation.
• Revenue section when revenue data exists: logo retention, GRR, NRR, each with its starting cohort and window.
• Findings list: one sentence per finding, the numbers behind it, confidence (high, medium, low), and a concrete next action.
• Limits: small cohorts, immature periods, missing events, and any definition change inside the window.

Operating Rules

• Never compare cohorts at different ages. Same age or no comparison.
• Never fill an immature cell with zero, with the last value, or with a forecast presented as data.
• Never drop churned or inactive users from the denominator. The denominator is fixed at cohort birth.
• Never mix retention variants, day-boundary rules, or timezones in one trend. If a tool changed its default, rebuild the whole series under one rule.
• Day boundaries change results. Rolling 24-hour windows from each user start and strict calendar dates in a project timezone give different D1 numbers on the same raw events. State which rule the SQL uses.
• Behavioral cohort gaps are correlation. Claiming a feature causes retention needs an experiment, a staggered rollout, or a natural experiment. Say which one you have, or label it a hypothesis.
• Watch for the anchor event firing once per user. If the return event equals the birth event and can only fire once, period 1 retention will read zero as an artifact.
• Retention above 100 percent in a user matrix is a build error: double counting, a variant mismatch, or a denominator that moved. Stop and fix the query.
• Small numbers get counts, not just percents. 3 of 7 users is not 43 percent retention in any decision-grade sense.
• Annotate releases, pricing changes, and major campaigns on the cohort timeline in every report. Memory fades, the annotation does not.
• Prefer the team's own mature cohorts as the baseline. Published industry benchmarks use mixed definitions and rarely match your variant, cadence, or anchor.
• Privacy: work on aggregated cohorts. Do not export user-level rows into reports, and suppress any segment cell small enough to identify a person.