Cohort retention analysis for subscriptions, step by step

A monthly churn rate mixes customers who joined last week with customers who have been around for three years. When it moves, you cannot tell whether the product changed or the customer mix did. A cohort table fixes that: it groups customers by when they started and follows each group over time.

This guide builds one from scratch, from definitions to SQL to reading the result. All numbers are hypothetical.

Step 1: define the cohort

A cohort is a group of customers who share a start date. For subscriptions, use the first paid period, not the signup date:

• Free trials and free plans are not revenue. If you start the clock at signup, trial drop-off gets mixed into paid retention.
• Track trial-to-paid conversion separately (how to measure it).

Group by calendar month for most businesses. Weekly cohorts help if you have hundreds of new customers a week and want to see the effect of a change quickly; quarterly cohorts help if you have few.

Customers who leave and come back

Decide whether a returning customer joins a new cohort or stays in their original one. Stripe’s Billing analytics keep them in the original cohort (Stripe analytics docs, accessed November 2026). That is a reasonable default: it shows the cohort’s revenue honestly over time. Write the choice down either way.

Step 2: define “active”

For each cohort and each month after the start, you need a yes or no per customer. Typical rule: the customer is active in month N if they have a subscription that is paid (or in a paid status) at the end of that month.

Edge cases to settle before writing any query:

Case	Common choice
Past due, still retrying	Active until the subscription ends, or until a fixed number of days after the last retry
Paused	Not active, but flagged separately so you can see pause returns
Cancelled, still in the paid period	Active until the period ends
Downgraded to a free plan	Not active for paid retention
Annual plan, mid-term	Active; annual customers can only really churn at renewal

The last line matters: mixing monthly and annual plans in one table makes month 1 to 11 look artificially flat. Build separate tables per billing interval.

Step 3: build the table

Assume a table subscription_months with one row per customer per month they were active (customer_id, month). You can derive it from your billing data or from webhook events.

with first_paid as (
  select customer_id, min(month) as cohort_month
  from subscription_months
  group by customer_id
),
activity as (
  select
    f.cohort_month,
    (extract(year from age(s.month, f.cohort_month)) * 12
      + extract(month from age(s.month, f.cohort_month)))::int as month_number,
    s.customer_id
  from subscription_months s
  join first_paid f using (customer_id)
)
select
  cohort_month,
  month_number,
  count(distinct customer_id) as active_customers
from activity
group by cohort_month, month_number
order by cohort_month, month_number;

Divide each row by the cohort’s size at month 0 to get the retention percentage. Pivot month_number into columns for the familiar triangle.

Example (hypothetical numbers)

Cohort	Size	M1	M2	M3	M4	M5
January	120	86%	79%	75%	73%	72%
February	140	84%	77%	74%	72%
March	110	88%	82%	79%
April	150	91%	85%
May	130	90%

The empty cells are not zero. They are months that have not happened yet. This is called right censoring, and averaging across a column that includes empty cells as zeros is a classic mistake.

Step 4: read the table

Read across a row

A row is one cohort’s life. Look for where the curve flattens. In the example, January loses 14 points in month 1, then 7, 4, 2 and 1. Most loss is early, then the curve levels off. A curve that keeps falling at the same pace month after month means the product does not have a stable core of users at that price.

Read down a column

A column compares cohorts at the same age. In the example, month-1 retention rises from 84 to 91% between February and April. If you changed onboarding in March, this is where you would see it. Check that the change holds in later columns before celebrating; sometimes better early retention only delays churn.

Read the diagonal

A diagonal is one calendar month across all cohorts. If every cohort drops in the same calendar month, look for an external cause: a price change, an outage, a payment processor issue, a seasonal pattern.

Step 5: add revenue retention

Logo retention counts customers. Revenue retention follows the cohort’s MRR, which can rise above 100% when customers expand.

revenue retention (cohort, month N) =
  MRR in month N from customers in the cohort
  ÷ MRR in month 0 from the cohort

Show both. A cohort with 70% logo retention and 105% revenue retention lost many small customers and grew the rest. That is a different business from one where both numbers sit at 85%. See NRR vs GRR for the revenue-side definitions.

Step 6: summarize across cohorts

For a single “month N retention” figure, weight by cohort size and only include cohorts that have reached month N:

weighted month-3 retention =
  sum of active customers at M3 across cohorts that reached M3
  ÷ sum of their starting sizes

In the example, cohorts January to March reached month 3:

active at M3: 120 × 0.75 + 140 × 0.74 + 110 × 0.79 = 90 + 103.6 + 86.9 ≈ 280.5
starting size: 120 + 140 + 110 = 370
weighted month-3 retention ≈ 280.5 ÷ 370 ≈ 75.8%

Use the actual counts rather than rounded percentages in practice; the decimals here come from rounding.

Step 7: split by segment

The most useful cohort tables split by something you can act on:

• Billing interval (monthly vs annual)
• Plan or price point
• Acquisition channel or signup source
• Payment method type
• Whether the customer reached an activation milestone (see activation and early churn)

Each split shrinks the cohorts. Keep the next rule in mind.

Traps

1. Small cohorts. With 20 customers, one cancellation moves retention by 5 points. Show cohort size next to every row, or merge into quarters.
2. Recent cohorts look better. Month-1 numbers for last month’s cohort may not include failed payments still in retry. Wait until the retry window has closed before reading the latest column.
3. Mixed intervals. Annual plans make monthly cohorts look flat. Separate them.
4. Survivorship in segments. If you segment by a behavior that takes time (for example, “used feature X”), customers who left early never had the chance to do it. Measure the behavior within a fixed early window, such as the first 14 days, for everyone.
5. Correlation is not cause. A segment that retains better may differ in many ways at once. Test changes with a holdout when you can.

Doing this with NoChurn

NoChurn reads subscription events from your billing tool (Stripe in one click, many others by connector or REST API) and lets you send your own customer attributes by API, such as signup source, plan or activation, to see which go together with churn (correlation, not causation). Each customer gets a churn score from 0 to 100 at signup and on every change. To act on what a cohort table shows, it runs failed payment recovery, cancel flows with an offer per reason and one-click cancel always visible, and win-back emails at day 30, 60 and 90. Every feature is on every plan, priced by MRR only (pricing).