---
title: "Cohort Retention Analysis Without a Data Team: Spreadsheet Method"
description: "Cohort retention analysis in a spreadsheet: the six columns you need, how to read the grid down the diagonal, and the four mistakes that ruin it."
topic: "Metrics"
author: "Jay Bheda, Co-founder, GainTrace"
audience: "Head of Customer Success, CS Operations"
published: 2026-09-18
modified: 2026-09-18
source: https://gaintrace.com/explore/metrics/cohort-retention-analysis-without-a-data-team
---

# How Do I Run a Cohort Retention Analysis Without a Data Team?

*Six columns, one pivot table, an afternoon*

**Short answer:** Cohort retention analysis needs six columns and a pivot table, not a data team. Export one row per customer carrying a start month, an end month and an ARR figure, group the customers by start month, then count how many survive at month 1, 3, 6 and 12. Read the finished grid down its diagonals as well as across its rows.

**Key takeaways**

- A cohort retention analysis needs six columns: account, start month, end month, starting ARR, current ARR and segment. Every other field is optional and most of them slow you down.
- Read the grid three ways. Across a row is one cohort ageing, down a column is retention at the same age, and down a diagonal is one calendar month landing on every cohort at once.
- A dip that runs across a row belongs to that cohort. A dip that runs down a diagonal was something you did to everybody in that month, usually a price change, an outage or a support reorganisation.
- Cohorts under about 30 customers move ten points on a single cancellation, so group by quarter instead of by month on a small account base and say which you used.
- Run the logo grid first and the revenue grid second. Logo cohorts show who stayed; revenue cohorts show whether the survivors grew, and the two often point in opposite directions.

You have been asked for a cohort retention analysis by an investor, a new CEO or your own curiosity, and the honest answer is that nobody in the building writes SQL for customer data. The company has a billing system, a CRM, and an analyst who is booked until November. Meanwhile the aggregate retention number keeps saying everything is fine, which you do not believe, because three of the customers who signed in the spring have already gone.
This page is for the Head of CS or CS Ops lead who has a spreadsheet and no analyst. It gives the six columns you need, the six steps that turn them into a grid, the three directions you read that grid in, and the four mistakes that make a cohort chart say something untrue. Everything here runs in Excel or Google Sheets with one pivot table and no scripting.

## How do I run a cohort retention analysis without a data team?

A cohort retention analysis without a data team starts with one export, one row per customer, six columns wide. Grouping customers by the month they started and counting survivors at each age is a pivot table, and the analysis stops being hard the moment you accept a monthly grain instead of a daily one. The work that looks like data science is mostly deciding what a start date means and what counts as still a customer.

> **The diagonal read:** The diagonal read is the habit of scanning a cohort grid down its diagonals as well as across its rows. Each row is one cohort ageing; each diagonal is one calendar month landing on every cohort at once. A dip that runs across a row is something about that cohort. A dip that runs down a diagonal is something you did, in that month, to everybody.

| Column | Source | What breaks without it |
| --- | --- | --- |
| Account ID | Billing system, not the CRM. Billing knows who paid. | Duplicate names and renamed accounts split one customer across two cohorts. |
| Start month | First paid invoice date, truncated to month. | Using the contract signature date puts customers in a cohort a month before they existed. |
| End month | Last paid invoice, or the scheduled end date for a customer still active. | Without an end month you cannot tell a live customer from one that stopped paying in March. |
| Starting ARR | Contract value at first invoice, annualised. | The revenue grid is impossible, and small and large customers count the same. |
| Current ARR | Contract value today, or zero if churned. | Expansion and contraction disappear, so a shrinking account base can read as a healthy one. |
| Segment | Plan, ACV band or ICP flag from the CRM. | One curve averages a self-serve tail and an enterprise core into a line that describes nobody. |

> "We have all the ingredients: usage data, ticket history, NPS, billing. What we don't have is anyone who can cook."
>
> — r/CustomerSuccess, 2026

> "We were previously relying on Salesforce and spreadsheets for tracking, which did not allow us to do any real forecasting or projections."
>
> — Director of Customer Success, mid-market SaaS, public G2 review

> "No health scoring, no playbooks, no standardized QBRs, and retention tracking is mostly anecdotal."
>
> — r/CustomerSuccess, 2025

## How do I build the cohort grid in a spreadsheet, step by step?

Building the cohort grid takes six steps and about two hours the first time, most of it spent arguing with the export, not with the maths. Work in one sheet. Add one derived column, build one pivot table, then convert the counts to percentages by hand so you can see what the percentages are made of.

1. **Export one row per customer from billing.** Account ID, start month, end month, starting ARR, current ARR, segment. Filter out test accounts, internal accounts and anything with zero starting ARR before you do anything else.
2. **Add a lifetime-in-months column.** Months between start month and end month for churned customers, and months between start month and today for live ones. A single DATEDIF or a subtraction of two month numbers does it.
3. **Pivot cohort against age.** Rows are start month. Values are a count of account IDs. Then add one column per age n that counts customers whose lifetime is greater than or equal to n. A COUNTIFS against the two columns is enough; no array formulas.
4. **Convert each row to a percentage of its own month 0.** Divide every cell by the cohort size in the same row. Keep the raw counts in a sheet beside it, because a percentage with no denominator is the main way cohort charts mislead people.
5. **Grey out the cells that have not happened yet.** A cohort that started four months ago has no month 6. Leaving those cells blank prevents the most common reading error, which is treating an incomplete diagonal as a collapse in retention.
6. **Copy the grid and swap counts for ARR.** The same pivot with current ARR as the value gives the revenue view. Run both. The logo grid and the revenue grid disagreeing is information, not a mistake.

**Cohort logo retention at month n**

```
Cohort retention rate at month n = Customers from the cohort still active at month n ÷ Customers in the cohort at month 0 × 100
```

Where:
- Still active at month n: paid an invoice in that month, or held a live contract that had not reached its end date. Pick one definition and apply it to every cohort
- Month 0: the month of the first paid invoice, which is always 100% by construction. A cohort chart that does not start at 100% has a definition problem in the first column
- What good looks like: a curve that flattens. Early months always fall; a curve still falling in a straight line at month 12 means the product has no durable job to do

## How do I read a cohort retention analysis once the grid is built?

A cohort retention analysis is read in three directions, and most people only use one. Across a row is a single cohort ageing, which tells you about that intake. Down a column is retention at the same age across every cohort, which tells you whether your onboarding is improving. Down a diagonal is one calendar month hitting every cohort at once, which is where price changes, outages and reorganisations show up.

| Cohort | Size | M1 | M2 | M3 | M4 | M5 | M6 |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Jan 2025 | 42 | 100% | 98% | 95% | 86% | 83% | 81% |
| Feb 2025 | 51 | 98% | 96% | 86% | 84% | 82% | 80% |
| Mar 2025 | 38 | 97% | 87% | 84% | 82% | 79% | 79% |
| Apr 2025 | 45 | 87% | 84% | 82% | 80% | 78% | 78% |
| May 2025 | 40 | 95% | 93% | 90% | 88% | 87% | 85% |
| Jun 2025 | 47 | 96% | 94% | 91% | 89% | 88% | 87% |

> **Reading the grid above:** Follow the cells Jan M4, Feb M3, Mar M2 and Apr M1. Every one of them is May 2025, and every one of them is a step down of nine to thirteen points against the preceding month, counting month 0 as 100%. Nothing about those four cohorts is alike except the calendar. Something happened in May 2025 to everyone who was a customer, and the April cohort, which was still in onboarding when it landed, took the worst of it at 13 points. The May and June cohorts never see it, because they arrived afterwards. Read row by row, that pattern looks like four unrelated bad months. Read down the diagonal, it is one event with a date, which you can go and find in a release log or a pricing announcement. These figures are illustrative; build the grid on your own accounts.

> "I ran a cohort analysis on customer outcomes last quarter and the predictor of renewal at month 12 was not which account manager owned the customer, not the size of the customer, not how clean the integration was. It was whether the week-four check-in happened as a dedicated meeting in week four. Customers where it happened renewed at 91%. Customers where it didn't renewed at 67%."
>
> — r/CustomerSuccess, 2026

That practitioner result is the reason to run the analysis at all. A cohort grid split by one process variable turns an argument about whether a ritual matters into a gap you can measure. [Why customers churn after onboarding when time to value is long](https://gaintrace.com/explore/onboarding/time-to-value-onboarding-complete-but-churned) covers what to do once an early-life dip is confirmed.

## Which cohort works when our contracts are annual?

Annual contracts need a cohort grid measured in renewal events, not in months of survival. A customer on a twelve-month term cannot leave in month 5, so months 1 through 11 of every row will read 100% and the grid will show a cliff at month 12 that tells you nothing about behaviour. Switch the age axis to renewal number: first renewal, second renewal, third. The row still ages, the diagonal still works, and the numbers mean something.

| Contract type | Age axis | Error it prevents |
| --- | --- | --- |
| Monthly, self-serve | Months since first invoice, 0 to 24. | None needed. This is the shape every cohort tutorial assumes. |
| Annual, single term | Renewal number, 1 to 4, plus a mid-term contraction column. | Stops eleven months of contractual 100% from reading as excellent retention. |
| Multi-year | Renewal number, with the cohort defined by renewal date rather than start date. | Stops a three-year contract from appearing as a healthy account for 35 months and a disaster in one. |
| Mixed contract types | Two grids, one per contract type, never blended. | A blended grid hides monthly churn behind annual customers who have not had a chance to leave. |

The same discipline applies to the churn number you quote beside the grid, because a monthly rate and a renewal-window rate on the same annual account base differ by an order of magnitude. [Why monthly and annual churn rate formulas disagree](https://gaintrace.com/explore/metrics/churn-rate-formula-monthly-vs-annual) sets out the four definitions and which one belongs on a cohort chart.

## How do I add revenue to a logo cohort retention analysis?

Adding revenue to a cohort retention analysis means running the same pivot with ARR as the value instead of a count, which produces a grid that can go above 100%. A logo grid answers who stayed. A revenue grid answers whether the ones who stayed grew, and in most B2B companies the two curves separate hard: logos fall while dollars hold, because the customers who leave are small and the ones who stay expand.

**Cohort net dollar retention at month n**

```
Cohort net dollar retention at month n = ARR from the cohort in month n ÷ ARR from the same cohort in month 0 × 100
```

Where:
- ARR from the cohort: the sum of current ARR for every customer in that cohort, including the zeroes for those who left. Dropping the zeroes is the single most common way this chart is faked
- Above 100%: expansion from survivors has outrun everything lost. Normal in enterprise cohorts from month 12 onward, rare before month 6
- What good looks like: the survey median for net revenue retention was 101% in the SaaS Capital study fielded in 2025, so a mature cohort sitting well under 100% is losing more than it grows

Two published reference points are worth holding beside your own grid, with their caveats stated. SaaS Capital, surveying more than 1,000 private B2B SaaS companies in 2025, reported a median net revenue retention of 101% and a median gross revenue retention of 91%; the sample is self-selected and skews larger. High Alpha's 2025 survey of 800 or more respondents put median gross revenue retention between 88% and 92% depending on the ARR band. Both are aggregate figures, so neither can tell you whether your 2025 intake behaves like your 2024 one. That is what your own grid is for. Run the survivors through the [NRR calculator](https://gaintrace.com/tools/nrr-calculator) if you want the aggregate number to compare against.

> "I still find myself extracting data from SFDC and manipulating in spreadsheets instead."
>
> — Mid-Market reviewer, public G2 review

## Which four mistakes ruin a cohort retention analysis?

Four mistakes account for almost every cohort grid that gets quietly abandoned: tiny cohorts, incomplete periods shown as real, a start date that is not the start, and blending segments into one curve. All four produce a chart that looks authoritative and moves for reasons that have nothing to do with customers.

| Mistake | How it shows up | Fix |
| --- | --- | --- |
| Cohorts too small | A cohort of 12 customers swings 8 points every time one leaves, so the chart looks violent and means nothing. | Group by quarter until each cohort holds 30 or more, and print the cohort size in the row label. |
| Incomplete periods shown as zero | The newest cohorts appear to collapse because month 6 has not happened yet and the cell reads as a loss. | Leave future cells blank. Never fill an unreached period with a number of any kind. |
| Wrong start date | Cohorts defined by contract signature include customers who onboarded two months later, so month 1 retention reads under 100%. | Anchor on first paid invoice. If onboarding is long, run a second grid anchored on go-live and compare the two. |
| Segments blended | One curve averages a self-serve tail with an enterprise core, and the shape describes neither population. | One grid per segment, and only compare grids with the same age axis and the same definition of active. |

**Before the cohort chart leaves your laptop**
- [ ] Every cohort shows its size, in customers, next to the percentages.
- [ ] Cells for periods that have not happened are blank, not zero.
- [ ] The start date is the first paid invoice, and the sheet says so.
- [ ] Churned customers appear as zeroes in the revenue grid, not as missing rows.
- [ ] The grid is split by segment, or the title says it is deliberately blended.
- [ ] A logo grid and a revenue grid are shown together, never one alone.
- [ ] The definition of active is written on the sheet in one sentence.
- [ ] A colleague who has never seen it can name the worst diagonal without being told.

> "There are great work arounds and solutions today but those are areas where my team still suplements with spreadsheets."
>
> — Senior Manager, Customer Success, mid-market SaaS, public G2 review

## How does GainTrace keep a cohort retention analysis current?

GainTrace builds the cohort grid from billing events as they arrive, so the start month, the end month and the ARR movement come from the system that took the money and the grid does not need rebuilding every quarter. Logo and revenue views sit side by side with cohort sizes visible, and future periods stay blank. [Customer health](https://gaintrace.com/platform/customer-health) then shows which accounts inside a deteriorating cohort are moving, and [renewal forecasting](https://gaintrace.com/solutions/renewal-forecasting) shows what that cohort is worth at its next renewal date.

## Frequently asked questions

### How do I build a retention cohort chart in a spreadsheet?

Export one row per customer with account ID, start month, end month, starting ARR, current ARR and segment. Add a column for lifetime in months. Pivot start month against that lifetime, counting accounts that survived to each age. Divide each row by its own month 0 to get percentages, and leave future periods blank. One pivot table and one COUNTIFS is the whole build.

### How many customers do I need before a cohort chart means anything?

About 30 per cohort as a working floor. Below that, one cancellation moves the line several points and you will read noise as a trend. With a few hundred customers total, group by quarter instead of month, print the cohort size beside every row, and treat any move smaller than one over the cohort size as unreadable.

### What is the difference between logo retention and dollar retention by cohort?

Logo retention counts customers who stayed and treats every account the same. Dollar retention sums the ARR those survivors now pay, so expansion can push it above 100%. In most B2B companies logos fall faster than dollars, because the accounts that leave are small. Run both grids: logo retention tells you whether the product fits, dollar retention tells you whether it grows.

### How do I do cohort retention analysis when all our contracts are annual?

Change the age axis from months to renewal events. An annual contract cannot cancel in month 5, so a month-based grid shows eleven months of artificial 100% and a cliff at month 12. Use first renewal, second renewal and third renewal as the columns, and add a separate column for mid-term contraction so downgrades are visible between renewal dates.

### Why does our retention look fine in aggregate but bad by cohort?

Because growth hides decay. An aggregate rate mixes a large base of older, stable customers with a small number of recent arrivals who are leaving quickly, and the older base dominates the average. A cohort grid separates them, which is why a company adding customers fast can show acceptable blended retention while every recent intake performs worse than the one before it.

### Can I run cohort analysis without SQL or an analyst?

Yes. Everything on this page runs in Excel or Google Sheets with one pivot table, one derived column and COUNTIFS. The hard parts are definitional rather than technical: what counts as a start date, what counts as still being a customer, and which segments belong on the same chart. Settle those three in writing before you build, and the build takes an afternoon.

## How this was researched

The six-column export, the six-step build, the diagonal read, the annual-contract age axis and the four-mistake taxonomy are our own analysis, developed against practitioner accounts, not from a published framework. The cohort grid and the reading example use illustrative figures. Practitioner evidence comes from 33,600 posts collected from r/CustomerSuccess, r/SaaS, r/sales and r/startups between May 2024 and September 2026, of which 33 mention cohort work, and from 4,978 public G2 reviews of customer success platforms, of which 107 mention spreadsheets. Benchmark figures are quoted from SaaS Capital (September 2025, more than 1,000 private B2B SaaS respondents) and High Alpha (2025, 800 or more respondents); both are self-selected surveys and both report medians.

## Sources

- [r/CustomerSuccess: The onboarding step we always skipped that turned out to predict renewals](https://reddit.com/r/CustomerSuccess/comments/1tr0b69/)
- [r/CustomerSuccess: How do you predict churn without a data scientist?](https://reddit.com/r/CustomerSuccess/comments/1vhis9n/)
- [r/CustomerSuccess: NEW Head of Customer Success at a Startup](https://reddit.com/r/CustomerSuccess/comments/1oouu84/)
- [SaaS Capital Research Brief 32: 2025 B2B SaaS Retention Benchmarks](https://www.saas-capital.com/wp-content/uploads/2025/09/RB32WS1-2025-B2B-SaaS-Retention-Benchmarks.pdf)
- [High Alpha 2025 SaaS Benchmarks Report](https://www.highalpha.com/saas-benchmarks)

## Next steps

Build the logo grid this week, then the revenue grid, and take the worst diagonal to whoever owns the change that caused it. [Start free](https://app.gaintrace.com/auth/login) or [book a demo](https://gaintrace.com/booking).