Cohort Analysis for Normal Humans: One Table, One Truth
Retention dashboards are often a mess of intersecting lines. We will build a simple cohort matrix in Excel to see exactly when customers fade.
The Spaghetti Dashboard
Come, look at this screen. It is a SaaS analytics dashboard tracking customer retention over the last twelve months. Let us try to follow the curve. The problem is, there is not one curve; there are a dozen. January is a blue line, February is a red line, March is a green dotted line. They cross over each other, dip, rise, and tangle together into a dense, unreadable knot in the center of the screen.
This visualization is a disaster. It is a chart with too much ink, screaming data at the viewer while communicating absolutely zero insight. When the board looks at this, their eyes glaze over, and they revert to asking for the “average churn rate.” But the average hides the shape. If we rely on a single blended number because our charts are too noisy to read, we are willfully blinding ourselves to the physical mechanics of our business. We must remove the gridlines. Delete the background. Clean up the noise. What remains is the truth, and the truth is best viewed in a simple grid.
The Noise: Complex line charts for cohort retention fail because they ask the human eye to do too much spatial translation. They treat distinct generations of customers as if they are running a race on the same track. But a customer who joined during a Black Friday sale has entirely different physics than a customer who joined via an enterprise webinar in March. They are different species. When we visualize them all stacked on top of each other, we create noise. This noise allows churn to sneak up on us, hiding in the messy intersections of the graph until it is too late to act.
The Pattern: We do not guess the future; we project the trend. And to see the trend of human behavior over time, we must build a Cohort Matrix. It is the most elegant, ruthlessly honest lie-detector test you can apply to your product.
Let us open Excel and build it. We are making a grid.
A worked teaching example, not an observed company dataset. Month 1 is the joining month, normalized to 100%. Compare each cohort at the same age.
| Joined | Month 1 | Month 2 | Month 3 |
|---|---|---|---|
| January | 100% | — | 60% |
| June | 100% | 60% | 30% |
| July | 100% | — | Not yet |
| August | 100% | Not yet | Not yet |
“—” means no figure is supplied. “Not yet” means that cohort has not reached that age in this illustrative August view; neither means zero retention. The June row illustrates the early 40% loss discussed below.
Down the left side, we list the groups by the month they joined. The sample shows January, June, July and August so we can compare an older cohort with newer ones. Across the top, we list the time elapsed: Month 1, Month 2, Month 3.
Inside the cells, we put the percentage of that original group that is still paying us. The written values carry the meaning; a spreadsheet color scale can be an optional aid.
Now, step back and look at the shape of the data. You do not need to decipher intersecting lines. You compare the percentages across each row and down each column.
Read the June row horizontally: 100% at Month 1 falls to 60% at Month 2. In this example, forty percent of the original cohort has left. That is the point where we investigate onboarding.
Now read the ‘Month 3’ column vertically. January retains 60%, while June retains 30% at the same age. That is a 30-percentage-point gap. If pricing changed in June, it is a useful hypothesis to investigate; the table alone does not establish the cause.
One table. One truth. By formatting the data as a physical space—a matrix of comparable percentages—we separate the Whales from the Barnacles with total clarity. We stop fighting with confusing dashboards and start fixing the exact moments where our customers decide to leave.
FAQs
Why are standard retention line charts so confusing?
Because they overlay too many time periods on a single axis. A chart with too much ink is hiding a weak signal. Your brain cannot process twelve overlapping lines of churn simultaneously.
What is a cohort matrix?
It is a triangle of data. The rows are the months people joined. The columns are the months that have passed. The cells show the percentage of people still paying you.
Do I need expensive software to build this?
No. You need a raw export of your billing data, a pivot table, and conditional formatting. The CSV cannot hide, and Excel handles the grid perfectly.