If you want to know whether customers actually stick around, a cohort retention table is the clearest view there is - and you don't need SQL or a BI tool to build one. Plain Excel will do it.
What a cohort retention table shows
You group customers by the month they first appeared (their cohort), then track what fraction of each cohort is still active in month +1, +2, +3 and so on. Read down a column to see how retention is trending across cohorts; read across a row to see how a single cohort decays over time.
Building it from a transactions sheet
- One row per customer per active month. From a transactions list, derive each customer's first-active month and their active months.
-
Compute the month offset.
offset = active_month - cohort_month(0, 1, 2, ...). - Pivot. Rows = cohort month, columns = offset, values = count of distinct customers. A PivotTable does this.
- Convert to percentages. Divide each cell by the cohort's month-0 size to get retention %.
- Colour it. Conditional formatting turns the grid into a heatmap so the decay pattern jumps out.
I wrote up the full step-by-step with the helper formulas here: Cohort analysis in Excel.
A few things that trip people up
- Count distinct customers, not transactions - a PivotTable counts rows by default, so de-duplicate to distinct customers per cohort/offset.
- Young cohorts look better than they are - the newest cohorts have only had a month or two to churn, so don't over-read their high early retention.
- Pair it with RFM - cohorts tell you when people churn; RFM segmentation tells you who is most valuable and most at risk.
If you'd rather not rebuild the grid by hand each month, I made a free browser tool that does cohorts (plus forecasts, segments and more) straight from a CSV, no signup: free tools.
Cohort retention looks advanced but it's really just careful bookkeeping. Build it once and you'll never trust a single headline "churn rate" again.
