why is this not just a table, but a live file
Most cohort analysis templates on the Internet are a beautiful picture with hardwired numbers. You cannot replace them with your own: there is nothing to recalculate, formulas no inside.
It's the other way around here. Both triangles - retention and revenue - are entirely assembled on formulas COUNTIFS and SUMIFSwho look at the sheet "Data". You insert your upload and get your cohorts. Nothing no need to redraw.
- Data. Customer, first purchase date, event date, amount. Age in months and cohort key are considered formulas, they are already extended by 400 lines.
- Hold. Twelve cohorts for twelve months of life. The share of cohort clients who completed an event in the Nth month.
- Revenue. The same triangle in money - accumulated revenue per cohort client.
- How to read. Row, column and diagonal answer three different questions. The sheet explains which one.
- Report. Five lines up, no triangles.
about the cohort key in the YYYYMM format
A small thing that causes other people's templates to break when opened. Usually a cohort sign with text like “2026-01”, and get it using the TEXT function with a mask format. Format masks depend on the Excel locale: in the Russian version it is YYYY-MM, in English yyyy-mm, and the file collected in one gives an error in the other.
Therefore, here the cohort key is number: =YEAR(date)*100+MONTH(date), that is, 202601. It is the same is considered in any locale and in any version, including online tables.
what to do with the result
The first thing they look at in the finished triangle is the cliff. If retention drops between M0 and M1 almost to zero, the problem is not in the product, but in onboarding: people they leave before they receive what they were promised. If the failure occurs on M3–M4 - usually the first noticeable result ends, and the product has nothing to hold on to person further.
The second is a comparison of columns. If each subsequent cohort has retention on M1 higher, it means changes in product or attraction are working. This is the only one an honest way to prove that things have improved, because the base averages are growing simply from the influx of new clients.
Further numbers go from here to unit economics calculator and in LTV to CAC calculation: revenue per client to M3 from this file - this is the part of LTV that can already be defend with numbers, not forecasts.
lies nearby
A detailed analysis of the methodology is in the article about cohort analysis and typical counting errors. General economics of the client - in analysis of unit economics. If we need a view on retention from the side of the subscription model - subscription and MRR.
Frequently asked questions
- How to count cohorts correctly - from the month of attraction or from month to month?
- From the month of attraction. A cohort is a group of clients who came in one month, and its age is calculated from this month: M0, M1, M2 and so on. A month-to-month comparison answers a different question—how things were going on the calendar—and mixes old customers with new ones. In the template, age is considered a formula from the date of the first purchase, you cannot make a mistake.
- What is cohort analysis in simple words?
- A way to look at clients not in a group, but in groups based on the month they arrived, and monitor how each group behaves further. It answers the question that the average base hides: does the product get better over time or are new customers simply masking the churn of old ones.
- How is cohort analysis different from funnel analysis?
- The funnel shows the path to the first purchase and works within one session or transaction. Cohorts show what happens after it, on the horizon of months. The funnel answers “where do we lose at the entrance”, the cohorts answer “how long does the client live and when does he leave”.
- What data is needed for cohort analysis?
- At least four fields: customer, date of first purchase, date of event and amount. Everything else is derivative. An additional acquisition channel or tariff will come in handy later, when you want to compare cohorts by source.
- How to read the cohort triangle?
- Three directions. Line by line - how one cohort lives over time and in what month clients drop off. The column shows whether different cohorts behave the same at the same age, that is, whether retention has improved. Diagonally - what happened in a specific calendar month with all cohorts at once.