Retention analysis compares customer behavior at the same tenure across cohorts and segments. Use customer and transaction exports to calculate return flags, reconcile the counts and identify the next investigation.
Retention analysis finds where customer return behavior changes across acquisition cohorts, tenure and segments. Start with complete customer and transaction exports, then compare the same activity window for every account. A falling overall rate should lead to a specific customer group and a checkable explanation.
This example follows a US business-services supplier's customers after their first purchase. A customer is active in a window if it contains a qualifying net positive purchase. The two windows are days 31 through 60 and days 61 through 90 after first purchase; day zero is the first-purchase date.
This is period activity, so a customer may return after an inactive window. It is not a claim of uninterrupted contractual retention. Use the renewal, retention and churn definitions when the question concerns contracts or the opening customer base instead.
Set the complete-through date to May 31, 2026, in cell H1. The date means all eligible transactions through that day have been supplied, rather than simply being the latest date visible in an incomplete file.
Keep customer IDs and first-purchase dates in a customer summary. Establish first purchase from sufficient history, not merely the earliest invoice in a short export. Freeze the segment at acquisition for this comparison; changing segments later would move customers between groups.
For the underlying cohort-table construction, follow the invoice-export cohort guide. Here the focus is diagnosis after the rows have been prepared.
The worked summary occupies A1:F9. Activity flags are 1 for a qualifying purchase and 0 for none in a completed window.
| A: Customer | B: First purchase | C: Cohort | D: Segment | E: Active days 31-60 | F: Active days 61-90 |
|---|---|---|---|---|---|
| A | January 5, 2026 | January | Standard | 1 | 0 |
| B | January 12, 2026 | January | Standard | 0 | 0 |
| C | January 16, 2026 | January | Managed | 1 | 1 |
| D | January 25, 2026 | January | Managed | 1 | 1 |
| E | February 3, 2026 | February | Standard | 1 | 0 |
| F | February 10, 2026 | February | Standard | 1 | 0 |
| G | February 18, 2026 | February | Managed | 1 | 1 |
| H | February 25, 2026 | February | Managed | 1 | 1 |
All windows have completed by the report date. The summary preserves customer B, whose lack of repeat activity is part of the answer.
On Invoices, use A for customer ID, B for purchase date and C for qualifying net purchase amount. Net returns against their original purchases first, and use dates without time components. The ranges below assume all records fit through row 10000; extend them to include the entire export.
In summary E2, count whether the first completed window contains a purchase:
=IF($B2+60>$H$1,"",--(COUNTIFS(Invoices!$A$2:$A$10000,$A2,Invoices!$B$2:$B$10000,">="&($B2+31),Invoices!$B$2:$B$10000,"<"&($B2+61),Invoices!$C$2:$C$10000,">0")>0))
In F2, calculate the later window:
=IF($B2+90>$H$1,"",--(COUNTIFS(Invoices!$A$2:$A$10000,$A2,Invoices!$B$2:$B$10000,">="&($B2+61),Invoices!$B$2:$B$10000,"<"&($B2+91),Invoices!$C$2:$C$10000,">0")>0))
Fill both down. COUNTIFS applies the customer, date and amount conditions together, as Microsoft's function documentation explains. The final comparison produces one flag even if an account has several qualifying purchases. Covirage is not affiliated with Microsoft.
For a segment label in K2, the later-window retention formula is:
=IFERROR(COUNTIFS($D$2:$D$9,K2,$F$2:$F$9,1)/COUNTIFS($D$2:$D$9,K2,$B$2:$B$9,"<="&($H$1-90)),"")
The denominator includes only customers old enough to complete the window. A missing first-purchase date must be resolved before this formula is used.
| Segment | Original customers | Active days 31-60 | Earlier rate | Active days 61-90 | Later rate |
|---|---|---|---|---|---|
| Standard | 4 | 3 | 75.0% | 0 | 0.0% |
| Managed | 4 | 4 | 100.0% | 4 | 100.0% |
| Total | 8 | 7 | 87.5% | 4 | 50.0% |
Each rate divides its active count by original customers; percentages are displayed to one decimal place. The total falls from 7 / 8 = 87.5% to 4 / 8 = 50.0%. The Standard group accounts for the decline; Managed activity is unchanged.
January's earlier-window count is 3 of 4, or 75.0%; February's is 4 of 4, or 100.0%. Both later-window counts are 2 of 4, or 50.0%. The early improvement has not carried through to later tenure.
For a broader view of rows, columns and calendar effects, use how to read a cohort table. For campaign-specific interpretation, see retention rate in marketing.
The checks are direct: 4 Standard plus 4 Managed customers equals 8; earlier activity is 3 + 4 = 7; later activity is 0 + 4 = 4. Later active customers plus inactive customers must equal 8: four of each. No active count may exceed its original customer count.
Customer operations should inspect the Standard accounts' ordering needs, service records and onboarding history. Finance should compare contribution before recommending extra retention spending. These eight illustrative customers locate a question; they do not establish that managed service causes stronger retention. NIST's randomized-design guidance describes random assignment, which is different from observing customers who already received different service.
Upload your customer summary and transaction files; Covirage maps the columns, and its tools compute retention comparisons and check the customer counts. The external AI model explains the findings with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See renewal watch for the supporting customer-retention use case.
You need stable customer IDs, first-purchase or start dates, agreed segment labels and dated activity records. Include customers with no later activity. Establish that the records cover each observation window before labeling a customer inactive. Missing periods should remain unavailable.
A retention rate summarizes a particular customer set and observation window. Retention analysis compares several such rates to locate a change by cohort, tenure or segment. It also checks definitions, missing records and customer mix before interpreting the difference.
Review it when another meaningful customer window has completed. Match the window to the purchasing or renewal cycle, and keep the definition stable. A frequent report is useful only if customers have had enough time to show the behavior being measured.