Cohort Analysis: The Report That Explains Retention

Every aggregate metric you look at is a blend. Monthly active users blends the people who joined yesterday with the people who joined three years ago. Churn rate blends a cohort that had a great onboarding experience with one that hit a broken signup flow. Cohort analysis is the technique that unblends them: you group users by when they started, then watch each group age separately. It is the single most useful report for answering the question that aggregates cannot answer, which is whether the product is actually getting better or just getting bigger.

This guide covers how cohorts are constructed, the specific mistakes that make a cohort table wrong in ways that look right, how to read a retention curve without fooling yourself, and how to convert a cohort finding into a decision someone can act on.

What cohort analysis actually measures

A cohort is a set of users who share a starting event within a shared time window. The classic version is acquisition cohorts: everyone who signed up in March 2026. The report has two axes. Down the rows, the cohorts, ordered by start date. Across the columns, elapsed time since that start, usually labeled Month 0, Month 1, Month 2 and so on. Each cell holds the share of that cohort still doing something in that period.

That layout gives you two independent readings from one table:

• Read across a row and you see the lifecycle of a single group. How fast does a cohort decay, and does the decay stop?
• Read down a column and you see whether the product is improving. If Month 1 retention has climbed across the last six cohorts, something you shipped is working. If it is flat while your headline user count grows, you are running on acquisition, not on product.

That second reading is why cohort analysis survives every analytics fashion cycle. Total user count can rise for a year while every individual cohort performs worse than the one before it. The aggregate hides it. The column does not.

Cohorts do not have to be time based. You can cohort by acquisition channel, by plan tier, by whether the account completed a setup step, by first feature used. Time is just the most common starting axis because it is the one that lets you measure change.

The three decisions that define a cohort analysis

Most bad cohort tables are not calculation errors. They are definition errors made before anyone wrote a query.

The entry event

Signup date is the default, and it is often the wrong choice. If your product has a setup lag, say a data import or an admin connecting a source system, then a signup-anchored cohort measures your onboarding queue as much as your product. A user who signed up on the 30th and finished setup on the 5th of the next month gets counted as having a near-zero first month.

The alternative is an activation-anchored cohort: Day 0 is the first time the user did the thing the product exists to do. This produces flatter, more flattering curves, which is exactly why you should be explicit about it. Both are legitimate. Publishing one while people assume the other is not.

The cohort grain

Weekly, monthly, or daily. The rule of thumb is to match the grain to your product's natural usage rhythm. A tool people open every workday supports weekly cohorts. A tool people use at month end, like a billing or reporting product, will show false churn in weekly buckets because normal users genuinely skip weeks. Monthly cohorts smooth that, at the cost of needing many months before the table says anything.

Bigger grains also mean bigger cohorts, which means less noise. If your weekly cohorts contain 40 people, you are reading sampling variance and calling it a trend.

The retention definition

This is the decision that changes the answer the most, and the one most often left undocumented.

Pick one, name it in the chart title, and do not change it silently. A retention number without its definition attached is not a number.

Building the cohort table in SQL

The mechanics are two building blocks: a table of each user's cohort assignment, and a table of activity periods. Then you count the intersection.

with first_seen as (
  select
    user_id,
    date_trunc('month', min(activated_at)) as cohort_month
  from users
  where activated_at is not null
  group by 1
),
activity as (
  select distinct
    user_id,
    date_trunc('month', occurred_at) as active_month
  from events
  where event_name = 'export_completed'
),
joined as (
  select
    f.cohort_month,
    f.user_id,
    (extract(year from a.active_month) - extract(year from f.cohort_month)) * 12
      + (extract(month from a.active_month) - extract(month from f.cohort_month)) as month_index
  from first_seen f
  left join activity a on a.user_id = f.user_id
)
select
  cohort_month,
  count(distinct user_id) as cohort_size,
  count(distinct case when month_index = 1 then user_id end) as m1,
  count(distinct case when month_index = 2 then user_id end) as m2,
  count(distinct case when month_index = 3 then user_id end) as m3
from joined
group by 1
order by 1;

Two things to notice. First, the left join is what keeps never-returning users in the denominator, and dropping it is one of the most common ways a cohort table silently lies. Second, computing a numeric month_index before pivoting is far more robust than interval arithmetic in every column, and it makes the query easy to extend. If you are hand-writing a dozen of those pivot columns, that is a signal worth reading; our guide to SQL CASE WHEN patterns and better alternatives covers when to reach for filter, crosstab functions, or a pivot in the BI layer instead.

If the same cohort logic is going to be used by more than one report, define it once in a model rather than pasting the CTE into every query. That is a modeling problem more than an analytics problem, and picking a data modelling tool that matches how your team works is the difference between one cohort definition and six subtly different ones.

A worked cohort analysis, read line by line

Here is an illustrative table for a hypothetical B2B tool. Monthly acquisition cohorts, retention defined as at least one completed export in the period. Blank cells are periods that have not finished yet.

Read down the M1 column first: 41, 43, 29, 45, 47, 46. Ignore March for a second and the trend is a steady climb from 41% to the mid-forties. Something shipped between January and May is working.

Now March. The cohort is more than twice the size of its neighbors and retains at roughly two thirds the rate. That is the fingerprint of a one-time acquisition spike, a launch, a discount, a large paid push, or a viral moment. Those users arrived with weaker intent. The important consequence is what March does to your blended numbers: if you compute "retention" across all users in the first half of the year, March's 2,900 low-intent users drag the average down enough to erase the real improvement visible in every other row. The aggregate says flat. The cohorts say improving plus one bad batch.

Then read across January, the only row old enough to have a shape: 41, 33, 30, 29, 28. The drop from M1 to M2 is 8 points. From M2 to M3 it is 3. After that, roughly a point per month. The curve is bending toward a plateau somewhere near 27%. That plateau is the most valuable number in the table, because it is the fraction of each cohort that becomes a durable customer, and it is what makes acquisition spend either rational or not.

Five mistakes that make a cohort analysis lie

1. Counting an incomplete period as complete

If today is the 12th of the month, this month's cell is not a retention rate, it is a partial count that will keep rising until the month closes. Plotted without care, it produces a terrifying cliff at the right edge of every chart, and someone will react to it. Leave incomplete periods blank, or shade them and label them clearly. The same applies to the newest cohort, which has had less time to do anything at all.

2. Letting acquisition mix move without saying so

March in the table above is the whole lesson. A cohort is only comparable to the cohort above it if the population is comparable. When you change channels, run a promotion, enter a new geography, or launch on an aggregator site, you have changed what a cohort is. The fix is to segment: split cohorts by acquisition source and read each series separately. If organic M1 retention is stable and paid M1 retention is falling, that is an entirely different conversation than "retention is falling."

3. Choosing an activity event that does not mean anything

If "retained" means logged in, then a browser tab left open, a background sync, an emailed link, or a mobile app checking for notifications can all keep a dead account alive in your numbers forever. Choose an event that requires deliberate effort and delivers the product's actual value. The resulting curve will be lower and much more honest, and it will move when you improve the product, which is the entire point.

4. Shrinking the denominator

Retention at Month 3 is Month 3 actives divided by the original cohort size, always. If you divide by the users who were still active in Month 2, you are computing period-over-period survival, which is a different and legitimate metric, but it will look dramatically better and it is not what anyone means by retention. Mixing the two inside one table, which happens easily when different analysts write different columns, produces a curve that bends upward for no reason.

5. Reading noise in small cohorts

A 40-person cohort moving from 45% to 52% is three people. Before you present a column trend, sanity check the cohort sizes and consider whether the movement exceeds what random variation would produce. For small B2B populations, widen the grain to quarterly cohorts, or switch from percentages to raw counts so the reader can see the sample size themselves.

Reading a retention curve honestly

Plot a cohort as a line with elapsed period on the x-axis and retention on the y-axis, and you get a decay curve with three regions.

The cliff. The drop between Period 0 and Period 1 is usually the largest movement in the entire dataset. It is dominated by onboarding, expectation mismatch, and people who were never going to be customers. Work here changes the height of every subsequent point.

The bend. The middle periods, where decay slows. Improvements here usually come from habit formation, recurring value, and reasons to come back.

The plateau. Whether the curve flattens at some positive value or keeps sliding toward zero is the most consequential question in the whole report. A curve that flattens means you have a core of users for whom the product genuinely stuck, and lifetime value is finite but real. A curve with no plateau means every user eventually leaves and growth has to be bought forever.

Three practical habits make this reading more reliable. Plot cohorts on the same axes rather than one chart each, so the column comparison is visual. Use a log scale on the y-axis when you want to compare decay rates rather than absolute levels, because constant proportional decay becomes a straight line. And resist fitting a smooth exponential and extrapolating twelve months out; retention curves are mixtures of populations with different behaviors, and a fitted curve will usually understate the plateau.

The heatmap version, the triangle of colored cells, is good for spotting anomalies at a glance and bad for reading trends precisely, because color is a weak encoding for small differences. Use the heatmap to find the odd row, then a line chart to actually measure it. If you are deciding how to present this to a wider audience, examples of charts and how to choose the right one and different types of charts and when each one works both cover the tradeoff between a dense grid and a readable line.

Turning a cohort finding into a decision

A cohort table that nobody acts on is expensive wallpaper. The move from observation to decision usually follows the same path: identify which region of the curve moved, form a cause hypothesis that is specific enough to be wrong, and pick the smallest test that would distinguish it.

The discipline that matters most: write the hypothesis before you slice. Cohort tables have enough cells that you will always find something interesting if you keep cutting, and most of it will not replicate in the next cohort.

Where cohort analysis fits in your tooling

Dedicated product analytics tools compute cohorts as a first-class feature. Amplitude, Mixpanel, and PostHog all ship retention and cohort reports out of the box as of 2026, and pricing and packaging change often enough that you should check their current plans directly rather than trusting any article, including this one. If your events already flow into one of them, use it; hand-rolling cohort SQL to reproduce a report you already have is not a good use of a week.

If your source of truth is a warehouse or a production database, the SQL above is the whole mechanism, and the real work is modeling and scheduling rather than analysis.

Skopx sits in a narrower and more specific slot, and it is worth being exact about it. Skopx is not a BI tool. It does not build dashboards, draw heatmaps, or produce visualizations. What it does is let you ask questions of connected systems in chat, including direct queries against PostgreSQL, MySQL, and MongoDB, and get answers as tables and text with their sources cited. For cohort work, that covers the two least glamorous parts of the job: getting the number without opening a SQL client, and making sure it lands in front of someone every week.

A concrete example. In Skopx chat, you would type:

Every Monday at 9am, run this against the production Postgres: monthly signup cohorts for the last 12 months with the share of each cohort that completed at least one export in each following month, then post the table in Slack to #growth with a note on which cohort moved most since last week.

That describes a scheduled workflow rather than a dashboard. Skopx builds it from the description, no drag-and-drop canvas involved, and you can inspect each run step by step to see the exact query that executed and what came back. Workflow triggers are manual, scheduled with a 15 minute minimum, or webhook driven, and workflows are capped at 20 steps with no custom code steps, so this is reporting plumbing rather than a data pipeline. The workflows page covers what the steps can and cannot do.

The daily morning brief is the other half: it surfaces what changed across connected tools, so a retention number moving out of its normal range shows up without anyone remembering to look. Skopx catches what falls between your tools, and a cohort report nobody opened is a very common thing to fall between them.

Skopx is a paid product with no free tier and no trial. Solo is $5 per month and Team is $16 per seat per month with no seat cap, billed from day one; see pricing for the full detail. AI steps run on your own provider key, and Skopx does not mark up AI costs. Connections to the source systems come from a catalog of nearly 1,000 integrations. Data is encrypted with AES-256 at rest and TLS 1.3 in transit, isolated per organization at the row level, never used to train a model, and Skopx has SOC 2 controls in place.

Frequently asked questions

How many users does a cohort need before the numbers mean anything?

There is no universal threshold, but the useful test is to ask how many individual users one percentage point represents. If a cohort has 50 users, one point is half a person and the table is unreadable at percentage precision. Widen the grain to monthly or quarterly cohorts, show raw counts alongside percentages, and treat single-cohort movements as questions rather than findings until a second cohort agrees.

Should I use weekly or monthly cohorts?

Match the grain to how often a healthy user would naturally use the product. Daily-use tools support weekly cohorts and give you feedback faster. Products used on a monthly cycle, like reporting or billing tools, generate false churn in weekly buckets. When in doubt, build monthly for reporting and weekly for experiment readouts, and never compare a weekly number to a monthly one.

What counts as a good retention rate?

Any specific benchmark you see quoted is close to meaningless without knowing the retention definition, the entry event, the product category, and the grain behind it, and those four things vary enormously between the sources that publish benchmarks. The honest comparison is against your own prior cohorts. The shape matters more than the level: a curve that plateaus at a modest number is a healthier business than a higher curve that never stops falling.

How is cohort analysis different from churn rate?

Churn rate is a single blended number for a period, so it mixes users at every stage of their lifecycle. Because new users churn far faster than old ones, blended churn moves whenever your growth rate moves, even if nothing about the product changed. Cohort analysis holds tenure constant, which is what makes it possible to attribute a change to something you did rather than to your acquisition mix.

Can I run cohort analysis without a data warehouse?

Yes, if your production database retains the events you need and the query volume will not disturb the application. Run it against a read replica, not the primary. The reasons to move to a warehouse are joining across multiple systems, keeping history that the application deletes, and not having analysts run heavy aggregate queries against a live transactional database.

Does cohort analysis work for B2B products with few accounts?

It works, with adjustments. Cohort by account rather than by user, widen the grain to quarterly or even half-yearly, and show counts rather than percentages so the reader can see how thin the sample is. With very small populations, a named list of which accounts sit in which cell is often more actionable than any curve, because someone can pick up the phone.