Cohort Analysis: How to Build a Retention Table, Read It, and Avoid the Churn Rate Trap
Cohort analysis groups customers by when they started and follows each group over time. It is the standard method for measuring customer retention, and it answers a question that a single churn rate cannot: are newer customers behaving better or worse than older ones? This guide covers how to build a cohort retention table in SQL, how to read a retention heatmap, which retention metric suits which product, why a blended churn rate can move in the wrong direction while retention holds steady, and how to test cohort differences in QuantumLayers.
What Cohort Analysis Measures
A cohort is a group of users who share a starting event in the same period: everyone who signed up in March, everyone whose first order was in week 14, everyone who joined through a given campaign. Cohort analysis aligns each group on its own timeline, so month 1 means the first month after that group started, regardless of the calendar date. Customers are then compared at the same age instead of at the same moment.
That alignment matters because customer behavior depends heavily on tenure. New users churn far more often than users who have stayed for a year. Any metric that pools users of different ages, such as total monthly active users or an overall churn rate, mixes those behaviors together, and the mix itself changes whenever acquisition speeds up or slows down. Cohort analysis removes tenure as a hidden variable.
Cohorts can be defined in three main ways, and most analyses use more than one.
| Cohort type | Grouped by | Question it answers | Example |
|---|---|---|---|
| Acquisition cohort | Signup or first-purchase period | Is retention improving for newer customers? | All users who signed up in April 2026 |
| Behavioral cohort | An action taken, usually early | Which early behaviors go with long-term retention? | Users who invited a teammate in their first week |
| Attribute cohort | A fixed property at signup | Do channels, plans, or regions retain differently? | Paid search signups vs referral signups |
Behavioral cohorts deserve a caution. Users who invited a teammate may retain better because inviting causes stickiness, or because already committed users are the ones who invite. The cohort comparison shows the association. Establishing the cause needs an experiment, as discussed in Correlation vs Causation and A/B Testing Statistics.
How to Build a Cohort Retention Table
A retention table needs only two fields per event: a user identifier and an activity date. The build has three steps. Assign each user to a cohort using their first activity date. Compute, for every later activity, how many periods have passed since that first date. Then count distinct active users per cohort and period, and divide by the cohort’s starting size.
In PostgreSQL, for monthly cohorts built from an events table:
WITH firsts AS (
SELECT user_id,
DATE_TRUNC('month', MIN(event_date)) AS cohort_month
FROM events
GROUP BY user_id
),
activity AS (
SELECT DISTINCT
e.user_id,
f.cohort_month,
(EXTRACT(YEAR FROM AGE(DATE_TRUNC('month', e.event_date), f.cohort_month)) * 12
+ EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', e.event_date), f.cohort_month))) AS month_number
FROM events e
JOIN firsts f USING (user_id)
)
SELECT cohort_month,
month_number,
COUNT(DISTINCT user_id) AS active_users,
ROUND(100.0 * COUNT(DISTINCT user_id)
/ FIRST_VALUE(COUNT(DISTINCT user_id))
OVER (PARTITION BY cohort_month ORDER BY month_number), 1) AS retention_pct
FROM activity
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;The output is in long format, one row per cohort and month. Pivoting month_number into columns gives the familiar triangle. Two definitions in this query carry most of the analytical weight and should be written down before anyone reads the result: what counts as the starting event, and what counts as activity. A login, a purchase, and a use of the core feature produce three very different retention curves for the same product.
How to Read a Retention Heatmap
A cohort table is usually shaded as a heatmap so that patterns are visible at a glance. Each row is a cohort, each column is an age, and the table forms a triangle because recent cohorts have not yet lived through later months.
The table can be read in three directions, and each one answers a different question.
Across a row is the retention curve of one cohort. The January cohort loses 28 percent of its users in the first month, another 16 points in the second, and then the losses shrink: 9, 6, and 3 points. A curve that flattens like this means the product holds on to a stable core of users. A curve that keeps falling at the same pace toward zero means it does not, and acquisition spending is filling a leaking bucket.
Down a column compares cohorts at the same age, which is the comparison that tells you whether things are getting better. In the example, month 1 retention drifts from 72 to 69 percent, and month 3 from 47 to 44 percent. Newer cohorts are slightly weaker. Whether that drift is real or noise is a statistical question, covered below.
Along a diagonal runs a single calendar month. In this table, May is the January cohort’s month 4, the February cohort’s month 3, and so on down to the May cohort’s month 0. If an outage, a price change, or a holiday affected everyone at once, it shows up as a darker or lighter diagonal stripe cutting across cohorts of different ages. Diagonal patterns point to events. Column patterns point to changes in who is being acquired or how they are onboarded.
Retention Rate, Churn Rate, and Which Metric to Use
“Retention” covers several distinct calculations. They are not interchangeable, and the right one depends on how often a healthy customer is expected to show up.
| Metric | Definition | Best suited to |
|---|---|---|
| N-period retention | Share of the cohort active in exactly period N | Products used daily or weekly: apps, SaaS tools, games |
| Unbounded (rolling) retention | Share active in period N or any later period | Infrequent use, where skipping a month is normal: travel, tax, e-commerce |
| Bracket retention | Share active at least once within a window, such as days 7 to 13 | Smoothing out day-of-week noise in daily data |
| Customer churn rate | Customers lost during a period divided by customers at the start of it | Subscriptions with an explicit cancellation event |
| Net revenue retention | Revenue from a cohort in period N divided by its starting revenue, including expansion | B2B and usage-based pricing, where it can exceed 100 percent |
Unbounded retention is always at least as high as N-period retention for the same data, sometimes much higher, so the two should never be compared against each other or against a benchmark that used the other definition. Published retention benchmarks are hard to use for the same reason: they rarely state which definition or which activity event they were built on.
The Churn Rate Trap: When the Blended Number Lies
The strongest argument for cohort analysis is what happens to an overall churn rate when acquisition changes. Because new users churn far more than established ones, the blended rate depends on the share of the customer base that is new. Grow acquisition and the blend shifts toward high-churn users. Slow it down and the blend shifts toward loyal ones. Neither movement says anything about whether customers are being retained better or worse.
To show the size of the effect, we simulated a subscription business in which every cohort follows exactly the same retention curve: 40 percent of users leave in their first month, 20 percent of the remainder in the second, 12 percent in the third, 8 percent in the fourth, and 5 percent per month after that. Signups run at 1,000 a month for a year. Then the scenarios split. In one, signups stay at 1,000. In the other, they grow by roughly 30 percent a month.
With steady signups, the blended churn rate falls from 14.5 to 12.5 percent over six months as the base matures. With growing signups, it climbs from 14.5 to 19.2 percent. A team watching only the headline would congratulate itself in the first case and start a retention crisis review in the second. In both, retention at every age is exactly what it was before.
The same mechanism works in reverse, which is the more dangerous case. If acquisition slows while newer cohorts retain worse, the shift toward older customers can hold the blended rate flat and hide the decline entirely. This is a form of Simpson’s paradox: an aggregate that moves differently from every group inside it. The fix is to report churn by tenure band or by cohort, and to treat the blended rate as a capacity figure for forecasting, not as a measure of retention quality.
Is That Cohort Really Different? Testing Retention Changes
Every cell in a cohort table is a proportion estimated from a finite group, so every cell carries sampling error. The smaller the cohort, the wider the error. The table below gives the 95 percent margin of error for a retention rate near 40 percent.
| Cohort size | 100 | 200 | 500 | 1,000 | 5,000 |
|---|---|---|---|---|---|
| Margin of error | ±9.6 pts | ±6.8 pts | ±4.3 pts | ±3.0 pts | ±1.4 pts |
A weekly cohort of 150 users can swing by several points from one week to the next with nothing changing in the product. When cohorts are small, use monthly or quarterly cohorts instead of weekly ones, or show the interval next to each cell.
To test whether two cohorts differ, treat retention as a yes or no outcome per user and run a chi-square test on the counts. In the heatmap, January retained 590 of 820 users at month 1 (72.0 percent) and May retained 932 of 1,350 (69.0 percent). The chi-square test gives p = 0.15, and Cramér’s V, which measures the strength of the association, is 0.03. A three-point gap between two cohorts of this size is well within what chance produces. The consistent drift across all five cohorts is a better signal than any single pair, and a test for trend across every cohort uses that information. Comparing revenue per user across cohorts calls for a different test, because revenue is skewed: Kruskal-Wallis or Mann-Whitney U handles it without letting a few large accounts decide the result.
Five Mistakes That Distort Cohort Analysis
Counting incomplete periods. The most recent cohort has not finished its current month, so its latest cell is understated. Users who signed up on the 28th have had three days to come back. Exclude the in-progress period, or define periods by days since each user’s own start date rather than by calendar month.
Averaging cohorts without weights. A simple average of month 3 retention across cohorts gives a cohort of 200 the same influence as a cohort of 5,000. Weight by cohort size, or compute the pooled rate directly from user counts. And only average cohorts that have all reached that month, or the average shifts as new cohorts enter the column.
Changing the definition of active mid-stream. If tracking changes so that a background sync now counts as activity, every cohort’s curve jumps on the same calendar date. That shows up as a diagonal stripe. Check diagonals before attributing a column change to product improvements.
Ignoring acquisition mix within cohorts. A cohort that looks weaker may simply contain more users from a low-intent channel, such as a giveaway campaign. Splitting each cohort by channel or plan before concluding that onboarding got worse avoids blaming the product for a marketing decision.
Reading revenue cohorts as user cohorts. Net revenue retention above 100 percent can sit alongside user retention of 60 percent if surviving accounts expand. Both numbers are correct. They answer different questions, and a report should say which one it shows.
Running Cohort Analysis in QuantumLayers
A cohort heatmap shows the pattern. Deciding which parts of it are real, and what drives them, takes the statistical tests described above, applied across every cohort, channel, and plan at once. That is the part QuantumLayers automates. The analysis works best on two shapes of data, both of which can be loaded from a CSV, a Google Sheet, or a direct connection to the database where your events live. For a database source, you can paste the cohort query above or describe the table you want in plain English. Setup for each engine is in Connecting a Database to QuantumLayers.
A user-level table: what differs between cohorts
One row per user, with a cohort label such as 2026-03, attributes such as acquisition channel and plan, retention flags such as retained_m1 and retained_m3, and a numeric outcome such as revenue in the first 90 days. Leave a retention flag empty for users who have not yet reached that month, so recent signups are not counted as churned.
The statistical pipeline treats the cohort label, channel, and plan as categorical columns and tests each against the retention flags with chi-square and Cramér’s V. An association is reported only when it is significant and has a V above 0.1, so a three-point wobble like the January to May gap above does not appear as a finding. Revenue is compared across the same groupings with ANOVA, eta-squared, and Cohen’s d for the most different pair, and Kruskal-Wallis and Mann-Whitney U run as rank-based fallbacks when skewed revenue hides the effect from ANOVA. Categorical columns qualify for these tests with between 2 and 20 distinct values, which fits up to 20 monthly cohorts or several years of quarterly ones. Every result passes through Benjamini-Hochberg correction before it is shown, which matters in cohort work because the number of cohort, channel, and plan comparisons grows quickly.
A time series: when retention changed
The second shape has one row per cohort start date, with metrics such as cohort size, week 1 retention, and month 1 retention. With a date column present, the pipeline analyzes each metric as a series. The CUSUM test locates a structural break and reports its date with the mean before and after, which turns “retention seems lower lately” into “month 1 retention dropped from one level to another for cohorts starting after a specific week”. STL decomposition separates seasonal swings, such as weaker holiday cohorts, from the underlying trend, and Holt-Winters and SARIMA forecasts are produced for the series with a pattern strong enough to project.
Keeping it current
Cohort tables change every time a new cohort ages by a month. A database-backed dataset can be synced on a schedule, a Scheduled Report can deliver the updated findings, and a Monitor reruns the pipeline on each sync and sends an alert by email, Slack, or webhook only when a statistically real change appears, such as a new break in month 1 retention or a channel whose retention gap became significant. AI agents can query the same findings through the QuantumLayers MCP server, so an agent asked about retention works from tested results.
A Cohort Analysis Checklist
| Stage | Check |
|---|---|
| Definitions | Starting event, activity event, period length, and retention metric written down and kept fixed |
| Building the table | Periods measured from each user’s own start; in-progress periods excluded; cohort sizes shown beside each row |
| Reading the table | Rows for curve shape, columns for improvement or decline, diagonals for calendar events |
| Before concluding | Differences tested rather than eyeballed; cohorts split by channel and plan; averages weighted by cohort size |
| Reporting | Churn shown by tenure band or cohort, not only as a blended rate; user and revenue retention labeled separately |
Compare Customers at the Same Age
A blended churn rate measures the customer base as it happens to be composed this month. A cohort table measures how customers behave at each stage of their life with the product, which is what retention work can actually change. Build the table from fixed definitions, read it in all three directions, test the differences before acting on them, and report churn in a form that growth and slowdowns cannot distort. The result is a retention number that moves only when retention does.
This post is part of the QuantumLayers blog series on making data-driven decisions you can trust. For forecasting churn once you understand it, see Predictive Analytics for Non-Data-Scientists. For tracing why a metric moved, read Diagnostic Analytics. Run cohort analysis on your own data at www.quantumlayers.com.