How to run a subscription cohort analysis in Excel

What to take away

Three columns carry the analysiscustomer ID, signup date and monthly MRR. Everything else is calculated in Excel.
Build the count grid with COUNTIFS, then divide each cell by its own month 0 cell to get retention.
A SUMIFS twin of the same grid gives MRR churnone grid counts subscribers, the other counts pounds.
LTV is ARPU multiplied by the sum of a cohort's retention row, not a blended average.
Chart the retention rows as a line chart with markers to see which cohort stops flattening.

Cohort analysis groups customers by the month they joined, then tracks each group on its own clock. The Wikipedia entry on cohort analysis sets out the method. A blended churn rate mixes every intake, so one strong month hides three weak ones.

UK seasonality sharpens the effect. Gifting inflates November and December sign-ups, and the same intake carries the January cancellations. ONS retail industry data shows how far retail sales swing around the festive period.

The columns your export needs

Column / Contents

A: Customer ID · One row per customer per active month
B: Signup date · A real date, not text
C: Cohort month · =EOMONTH(B2,-1)+1, formatted mmm yyyy
D: Activity month · First day of the month being billed
E: Month index · =DATEDIF($B2,D2,"m")
F: MRR · Recurring value for that month
G: Active flag · 1 if active, 0 if not

DATEDIF returns whole months since signup and works in Excel and Google Sheets. Keep one row per customer per active month, or the counts double.

Build the cohort grid with COUNTIFS

Export one row per customer per active month, with all seven columns above.
On a new sheet, list cohort months down column A from A4.
Put month indices 0 to 12 across row 3 from B3.
In B4 enter =COUNTIFS(Data!$C:$C,$A4,Data!$E:$E,B$3,Data!$G:$G,1).
Fill the formula right and down to cover every cohort and month.
On a second grid use =SUMIFS(Data!$F:$F,Data!$C:$C,$A4,Data!$E:$E,B$3) for MRR.
On a third sheet turn counts into retention with =Grid!B4/Grid!$B4, formatted as a percentage.

Chart the retention curve

Select one cohort's retention row, such as B4:N4, then insert a line chart with markers. Set the horizontal axis to the month index row 3.

Healthy curves fall hardest between month 0 and month 1, then flatten by month 6. A row that keeps sliding has not found its base, which is usually an onboarding problem rather than a pricing one. Our guide to subscription lifecycle email automation covers the triggers that fix it.

Label each line with its cohort month. Three lines on one chart beat three rows of a table when you present to a founder or a finance lead.

Calculate MRR churn from the grid

MRR churn for a month is the fall against the month before: =(B4-C4)/B4 on the MRR grid, formatted as a percentage. Strip out new and expansion revenue first, or you will understate churn.

Stripe's subscription analytics documentation splits recurring revenue into new, expansion, contraction and churn. Those movements map onto the grid columns, so a churn column can be built from the SUMIFS grid alone.

Refresh the churn grid monthly. The formula does not change as new cohorts appear.

Read the churn column down the sheet, not across a row, when you compare intakes. Compare 4% subscriber churn with 9% MRR churn and your highest-value customers are leaving first. That is a pricing conversation, and part of it is involuntary card failure. Read failed payment recovery before you touch price.

Example: LTV from the same grid

ARPU is month 0 MRR divided by month 0 subscribers: =MRRgrid!B4/Grid!B4. LTV multiplies that by the retention row total: =ARPU*SUM(Retention!B4:N4). Multiply by gross margin for a contribution figure.

The table below is illustrative arithmetic for a coffee box at £24 a month, not a benchmark.

LTV from the same grid

Cohort	Subscribers at month 0	Month 3 retention	Month 6 retention	Month 12 retention	MRR at month 0	MRR at month 12
Nov 2024	500	62%	48%	41%	£12,000	£4,920
Jan 2025	300	71%	58%	52%	£7,200	£3,744
Mar 2025	220	74%	61%	not yet due	£5,280	not yet due

The January cohort has an ARPU of £24, from £7,200 divided by 300. If its retention row sums to 6.4 month-equivalents, LTV is £153.60. Set that against acquisition cost per subscriber.

Read the table across a row to age one cohort. Read down a column to compare intakes at the same month, which is where decisions change. Our subscription churn reduction guide covers the retention levers that lift the sum without a price rise.

Common questions

How many cohorts do I need before the numbers mean anything?

Six complete cohorts is a working minimum and twelve is better. Fewer than six and one unusual month drives the whole read.

Should I use calendar months or rolling 30-day periods?

Calendar months line up with VAT quarters and the Christmas trading period, which suits UK reporting. Rolling 30-day periods suit weekly trading reviews.

What if subscribers pause rather than cancel?

Count pauses as active but flag them in a separate column. A pause still carries packaging and courier cost, so it is not a clean save.

Is there a benchmark for month-one retention?

No single figure covers every category. Compare cohorts with each other: one holding ten percentage points more than the previous intake is the signal worth chasing.