Cohort analysis 2026: how to calculate retention and what to show to the manager
How to show inflow, outflow and retention of customers by month on one table. Four fields from CRM, two formulas, a cohort triangle and three ways to read it. Plus an analysis of the main accounting error - counting from the calendar instead of the month of attraction - and the median interval as the outflow boundary.
The question that usually leads to this topic is: how to show the manager the inflow, outflow and retention of clients by month in one picture. The answer is the cohort triangle. It is built over the evening from four unloading fields, and it is the only one that shows what the average ones in the database are hiding.
I’ll go through it in order: how to calculate a client’s age, where almost everyone makes mistakes with the starting point, how to read a finished triangle in three directions and what to take out of it.
Total revenue grows from influx. Cohorts show what happens to those who have already arrived. These are different questions, and the answer to the second one is usually more unpleasant.
1. Why do this if there is revenue and average bill
A classic situation: revenue is growing for the third quarter in a row, the average bill is stable, the report is green. At the same time, clients are leaving faster than six months ago - it’s just that the inflow is still blocking the outflow. Both metrics do not show this, because they add new clients and old ones into one number.
The cohort divides them by month of arrival. Then you can see what was previously blurred: whether the client lives longer than before, and in what month he leaves. The moment when the inflow ceases to cover the outflow is visible in cohorts several months before it appears in total revenue.
2. Main mistake: starting point
The most common question on the topic is whether to count from the first month of attraction or from month to month. The answer is clear: from month of attraction. And this is not a matter of taste, but the difference between two different tables.
| Countdown | Which question does it answer? | What is hidden |
|---|---|---|
| From the month of attraction (M0, M1, M2...) | How does the client live after arrival? | Nothing - this is the cohort look |
| Month to month (January, February...) | How things were going on the calendar | Mixes new with old, outflow is invisible |
Both cuts are needed, but they solve different things. The calendar one answers “what happened in March”, the cohort one answers “is it any better”. If we build only a calendar one and call it cohort, the conclusion will be the opposite of reality: the influx will mask the deterioration in retention.
3. Four fields and two formulas
The data that is needed as input is available in any CRM or billing system:
- client ID;
- the date of its first purchase;
- date of a specific event - order, payment, renewal;
- amount.
One line - one event. Next are two derived columns.
Client age in months at the time of the event: (YEAR(event)-YEAR(first)*12+(MONTH(event)-MONTH(first)). This is the horizontal coordinate: 0 is the month of attraction, 1 is the next month, and so on.
Cohort Key: YEAR(first)*100+MONTH(first), that is, 202601. There is a subtlety here that causes other people’s templates to break: a cohort is usually signed with text through the TEXT function with a format mask, and the masks depend on the locale of the table - in the Russian version YYYY-MM, in the English yyyy-mm. A file collected in one produces an error in the other. The number does not have such a problem.
Next, the triangle is assembled using two functions: COUNTIFS to hold and SUMIFS for revenue. Ready file with all formulas - cohort analysis template, there is also a demo download there so you can see how it is calculated.
4. How to read a triangle
One table answers three different questions, depending on how you move your eye across it.
| Direction | Question | What will you see |
|---|---|---|
| Line by line, left to right | How one cohort lives | In what month do clients drop off? |
| By column, top to bottom | Are cohorts the same at the same age? | Has retention improved over time? |
| Diagonally | What happened in a particular calendar month | An external event that hits everyone at once |
The column is the most valuable and most underutilized area. It is the comparison of cohorts at the same age that proves that changes in product or acquisition have worked. The average ones in the database will never prove this: they simply grow from new clients.
The diagonal is useful less often, but it helps in analyzing failures. If the drawdown occurs on one diagonal - that is, on one calendar month for all cohorts at once - the reason is external: season, failure, price change. If the drawdown occurs in rows at the same age, the reason is inside the product.
5. What to look for in a finished triangle
Open circuit between M0 and M1. If retention drops to almost zero immediately after a month of acquisition, the problem is not in the product, but in onboarding: people leave without achieving the promised result. It is treated not by discounts, but by the first touch after purchase.
Failure on M3–M4. A typical moment when the first noticeable effect of a product ends, and there is no next reason to return. What works here is not advertising, but the script of the second cycle.
Flat plateau after M6. A good sign: you have a core that won't go away. Further calculating LTV makes sense, because the curve has reached a predictable part.
Incomplete cohorts. The last lines of the triangle are shorter than the others - they simply did not reach the right columns. You can’t compare them with older cohorts; this is the most common mistake in presentations: a fresh cohort always looks worse because it has less time.
6. Median interval: where the pause ends and the outflow begins
For subscriptions, the outflow is obvious - the person did not renew. There is no limit for one-time sales, and you have to set it yourself. Tool - median order interval: the typical time between two purchases for the same customer.
The logic is simple. If half of repeat purchases happen within forty days, then silence at ninety is no longer a pause, but a lost customer. And if the median is four months, then it’s too early to worry in the second, and sending out “we miss you” after three weeks will only irritate.
It is the median that is taken, not the average: several clients, with an interval of one and a half years, skew the average so that it cannot be used. The same figure then sets the horizon of the cohort triangle - there is no point in constructing twelve months if the median is twenty days.
7. What to bring upstairs
The triangle is a working tool, not a slide. The manager is shown five lines:
| Indicator | What does he say? |
|---|---|
| New Cohort Size | Inflow: how many clients came |
| Hold on M1 | Onboarding quality |
| Hold on M3 | Does the product have a second cycle? |
| Revenue per customer to M3 | Part of LTV that can already be protected by fact |
| Median interval between purchases | Where is the outflow boundary? |
Each line is compared with the previous period, and one sentence is written for each about what to do about it. Without the last column, the report turns into an archive of numbers.
8. Where do these numbers go next?
Revenue per customer to M3 is not an abstract metric, but an input into the other two models. She is substituted in unit economics calculator instead of predictive LTV and makes the calculation honest: this is money that has already happened, and not an expectation. Then she also participates in LTV to CAC ratio, which is used to decide how much to pay for a client.
For subscription models, cohorts are linked to MRR: retention by cohort explains where the gap between new and net revenue growth comes from. This is discussed in the material about subscription and MRR.
9. Summary
Cohort analysis is not heavy analytics and not a separate system. Four fields from CRM, two formulas for age and cohort key, two triangles and five lines to the top. Assembled in the evening.
The only thing that really needs to be done correctly is to count the age from the month of attraction, and not from the calendar. Everything else is technique, but this error reverses the output.
Ready file with formulas: cohort analysis template. Related materials: unit economics, marketing analytics, LTV/CAC benchmarks in SaaS.
If the upload does not fit into the template or the triangle comes out full of holes, write to Telegram or through form, I'll see what's wrong with the data.