How to run a cohort retention analysis

A retention curve answers a question an average can't: not "what's our churn rate this month," but "of the people who joined in a given period, how many are still active N periods later, and does that number stabilize or keep sliding toward zero." cohort analysis groups users by when they started rather than mixing everyone together, because a business growing quickly can have a flattering overall active-user number while every individual cohort is retaining worse than the last — the growth masks the decay until it doesn't.

The two shapes of retention

Classic (N-day) retention asks: of users active on day 0, what fraction are active again on day N? This is the standard product-analytics definition and what most tools compute by default.

Unbounded (return) retention asks: of users active on day 0, what fraction are active on day N or any day after? It's more forgiving and trends higher, because a user who comes back on day 40 after a gap still counts. Neither definition is wrong, but comparing an unbounded number from one source against a classic number from another will make one look better than it is for no real reason — confirm which one you're looking at before you compare tools or teams.

Building the cohort table in SQL

The underlying logic is the same regardless of warehouse: assign each user to a cohort based on their first tracked event, then for each subsequent period check whether they had any activity. In generic ANSI SQL, against an events table with user_id, event_time, and event_name:

with first_seen as (
  select
    user_id,
    date_trunc('week', min(event_time)) as cohort_week
  from events
  group by user_id
),
activity as (
  select
    e.user_id,
    f.cohort_week,
    date_trunc('week', e.event_time) as activity_week
  from events e
  join first_seen f on f.user_id = e.user_id
),
weeks_since as (
  select
    user_id,
    cohort_week,
    (extract(epoch from activity_week - cohort_week) / 604800)::int as week_number
  from activity
  group by user_id, cohort_week, activity_week
)
select
  cohort_week,
  week_number,
  count(distinct user_id) as active_users
from weeks_since
group by cohort_week, week_number
order by cohort_week, week_number;

Divide each active_users value by the cohort's week-0 count (its total size) to turn raw counts into a retention percentage, and pivot week_number into columns to get the familiar triangular cohort table. The date_trunc grain (week here) should match your product's natural usage cycle — daily for something used every day, monthly for something used less often; forcing a weekly grain onto a monthly-habit product will make retention look worse than it is simply because most weeks legitimately have no activity.

Reading the curve

A healthy retention curve declines and then flattens — the flattening point is your resilient core: the users who stick around are unlikely to churn much further, and that plateau, not the peak, is the number worth tracking over time. A curve that keeps sliding toward zero with no flattening means the product hasn't found the group of users it durably works for yet, regardless of how good the day-1 number looks. Comparing the shape of the curve — where it flattens, and at what level — across cohorts over time tells you whether recent product changes made retention better or worse, which a single blended churn rate number cannot show you, because it's dominated by whichever cohort is currently largest.

For subscription businesses specifically, subscriber churn is often reported as a period-over-period rate rather than a cohort curve; the cohort view is more diagnostic because it isolates whether a specific change (a pricing update, an onboarding redesign) shifted retention for the cohorts that experienced it, versus older cohorts that didn't.

Where funnels and retention meet

funnel analysis and retention analysis are often built in the same tool because they answer connected questions: a funnel tells you whether people reach a value moment at all; retention tells you whether they keep coming back after they do. A common, useful cut is retention conditioned on funnel completion — do users who reach a specific milestone in week one retain meaningfully better than those who don't? If so, that milestone is a strong candidate for an activation or north-star metric worth optimizing directly.

Tools built for this without hand-written SQL

All four of the major product analytics platforms compute retention cohorts natively, which is usually faster for exploratory cuts than querying the warehouse directly, though the SQL above is worth knowing for definitions the tool doesn't expose or for validating what the tool is showing you.

• General-purpose product analytics with strong built-in retention and cohort tooling. Amplitude and Mixpanel both build retention curves and behavioral cohorts directly from tracked events, with experimentation layered on top.
• Open-source, want to run cohort SQL directly against your own warehouse. PostHog's built-in data warehouse and SQL querying make it straightforward to validate or extend the built-in retention charts with a custom query like the one above.
• Retroactive analysis on events nobody thought to instrument in advance. Heap's autocapture records interactions before you define the event, which matters if the milestone that turns out to predict retention wasn't something you tagged from day one.

Common mistakes

• Blending classic and unbounded retention definitions across dashboards or teams without labeling which is which.
• Choosing a daily or weekly grain that doesn't match the product's natural usage cycle, making retention look artificially poor.
• Looking at a single blended retention number instead of the cohort table, which hides whether recent cohorts are doing better or worse than old ones.
• Declaring a curve "flattened" after only two or three periods — some products take longer to reveal their stable core.
• Comparing retention across cohorts that experienced different acquisition channels or promotions as if they were the same population.

For the metric this retention behavior should roll up to, see how to define a north-star metric, and for the funnel work that usually precedes it, see how to analyze a conversion funnel. Browse product analytics tools for the full field.