Blog · Board and management reporting
Choose a cohort heatmap or retention curve to compare customers at equal ages. Build the chart from dated customer exports, show cohort sizes and keep unfinished periods out of the comparison.
A cohort chart compares retention at the same customer age, using a heatmap or a set of retention curves. Put months since acquisition on the horizontal axis and keep each cohort's original customer count as its denominator. Show unfinished ages as blank so a young cohort does not look worse simply because less time has passed.
A cohort is a group sharing a defined starting event. For a billing analysis, that can be first paid service month. Calendar month answers when business happened; customer age answers how long customers had been with you when it happened.
| Chart | Best use | What to display | Main risk |
|---|---|---|---|
| Retention heatmap | Scan many acquisition periods | Cohort rows, age columns, retention percentages | Color hides small cohort sizes |
| Retention curves | Compare a few groups directly | Age on horizontal axis, retention on vertical axis | Too many overlapping lines |
| Count table beside either chart | Explain the scale of the change | Original and retained customer counts | Counts mistaken for comparable rates |
Start from one checked count table and produce either visual from its percentages. The guide to reading retention tables covers the interpretation of rows, columns and calendar diagonals in more detail.
Export customer ID, first paid date and activity month from your billing or invoice records. Decide what activity means: active paid service at month-end, a qualifying purchase during the month, or another documented business event. These are different measures, so label the chart precisely.
For this subscription example, activity means paid service active at month-end. The source is reduced to one row per customer per active month. Multiple invoices, credits and additional products must not create extra customer counts. The Excel retention-table tutorial explains the longer invoice-preparation workflow; the steps here focus on the chart that follows it.
On a worksheet named CustomerMonths, use A for customer ID, B for first paid date and C for the activity month as a real date. Calculate cohort month in D2:
=DATE(YEAR(B2),MONTH(B2),1)
Calculate customer age in whole calendar months in E2:
=12*(YEAR(C2)-YEAR(D2))+MONTH(C2)-MONTH(D2)
Fill down. Investigate negative ages and customer IDs assigned to multiple first paid dates. Confirm that the last included activity month is complete before counting it as an observed age.
The example closes on April 30, 2026. January customers have reached age three; February customers have reached age two; March customers have reached age one. Age zero is the acquisition month.
| Acquisition cohort | Original customers | Age 0 | Age 1 | Age 2 | Age 3 |
|---|---|---|---|---|---|
| January 2026 | 100 | 100 | 80 | 70 | 60 |
| February 2026 | 50 | 50 | 45 | 40 | — |
| March 2026 | 80 | 80 | 60 | — | — |
On a Counts worksheet, put cohort dates in A2:A4, original customer counts in B2:B4 and numeric age headers 0–3 in C1:F1. With the prepared source limited to rows 2–999, the observed count in C2 is:
=COUNTIFS(CustomerMonths!$D$2:$D$999,$A2,CustomerMonths!$E$2:$E$999,C$1)
Fill only cells for completed ages. Leave future cells truly empty; a count formula would otherwise return zero for a period that has not happened. Check original counts against the acquisition register rather than assuming the prepared activity file is complete.
Create a Retention worksheet with the same layout. Copy original counts to column B. In its observed C2 cell, use:
=Counts!C2/$B2
Fill across and down only the observed cells, and format as percentages. Every age keeps the original denominator.
| Acquisition cohort | Original customers | Age 0 | Age 1 | Age 2 | Age 3 |
|---|---|---|---|---|---|
| January 2026 | 100 | 100.0% | 80.0% | 70.0% | 60.0% |
| February 2026 | 50 | 100.0% | 90.0% | 80.0% | — |
| March 2026 | 80 | 100.0% | 75.0% | — | — |
Percentages are displayed to one decimal place. For example, February age two is 40 / 50 = 80.0%. The blank cells in this illustration represent empty worksheet cells, not typed dashes.
For a heatmap, select the observed percentage area and apply a single color scale. In Excel, use Home > Conditional Formatting > Color Scales, then edit the rule to use fixed numeric endpoints of 0 and 1. Microsoft documents color scales and their numeric bounds. Keep the original counts visible beside the colored cells and leave future cells uncolored.
For retention curves, select the cohort labels and percentage columns, excluding original customer counts. Insert a standard line chart with markers and use each cohort as a separate series. Switch rows and columns if necessary so the horizontal labels are ages 0–3. A standard line chart suits these equally spaced age categories; Microsoft explains the difference between line and scatter chart axes.
Set the vertical axis consistently from 0% to 100%. Label the example lines January, n=100; February, n=50; and March, n=80. In the chart's Hidden and Empty Cells settings, show empty cells as gaps, following Microsoft's empty-cell chart guidance. Confirm that each line stops at its last observed age.
At age one, the chart compares 80.0%, 90.0% and 75.0%. February is 10.0 percentage points above January: 90.0% − 80.0%. It also starts with fewer customers, which should remain visible during any discussion about changing acquisition spending.
An overall age-one rate is (80 + 45 + 60) / (100 + 50 + 80) = 185 / 230 = 80.4%. The simple average of the displayed cohort rates is 81.7%, which gives each cohort equal weight. Neither weighting should be left implicit.
At age two, only January and February are eligible. Their combined rate is (70 + 40) / (100 + 50) = 73.3%. Including March's original customers while excluding its future retained count would understate retention.
Keep customer retention separate from revenue retention by cohort. Price increases or expansion can raise revenue while the customer curve continues to fall.
Covirage is not affiliated with Microsoft.
Bring a sample of your own dated customer activity or prepared invoice export. Covirage maps the columns, and its tools compute and check retention counts and equal-age comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your file.
See Covirage for sales insights for the supporting business context.
A regular trend chart compares calendar periods, mixing new and established customers in each point. A cohort chart follows groups from their own starting dates. Its age axis lets you compare customers at the same stage of their relationship with the business.
Use a heatmap when the main job is scanning many cohorts for a weak period or a repeated age pattern. Use separate lines when a meeting needs to compare a few cohorts directly. Both charts should come from the same checked retention table.
It has not reached the older customer ages yet. Its curve should stop at the last complete observed period. Extending it with zeros creates false churn; carrying its latest rate forward creates an unsupported assumption about future retention.
It can if the measure counts customers active in each separate period and allows customers to return. A measure requiring continuous retention cannot regain a lost customer. Define the activity and return rules before interpreting an upward movement as an improvement.