Blog · Board and management reporting
Customer life cycle analysis compares the same accounts between dated purchase states. Build a reproducible monthly transition table from order exports, reconcile new and existing customers, and connect each movement to revenue.
Customer life cycle analysis compares the same accounts across dated purchase states to show first purchases, repeat buying, lapses, and returns. Build it from an order export with stable account IDs, then reconcile every movement to the opening and closing populations. Revenue growth alone cannot show whether established buyers stayed active.
Use June 30 and July 31, 2026 as the cutoffs for this illustrative US distributor. Its monthly buying pattern makes a calendar-month policy useful. These states are management definitions, not claims that a quiet month permanently ends a customer relationship.
| State | Definition for a reporting month |
|---|---|
| Not yet | No qualifying purchase through the cutoff |
| First | Purchased this month and had no earlier qualifying purchase |
| Repeat | Purchased this month and in the immediately preceding month |
| Returned | Purchased this month, skipped the preceding month, and has earlier history |
| Lapsed | Has purchase history but made no purchase this month |
Apply the rules in that order of eligibility: determine whether there is a current purchase, then distinguish first, repeat, and returned using earlier history. Each account receives exactly one state at each cutoff.
Lapsed is an inactivity label here. It does not mean a canceled contract or confirmed lost account. See customer survival analysis for the separate question of relationship duration. Keep sales life cycle, which follows opportunities toward a sale, separate from post-purchase account movement.
Keep one row per completed order in a sheet named Orders. Put account ID in A, order ID in B, order date in C, and net sales in USD in D. Use a consistent purchase-date definition and exclude canceled orders. If the export contains order lines, aggregate them to unique orders before counting purchases.
For this example, a qualifying purchase is a completed positive-value order after same-order adjustments. There are no stand-alone credits in the worked rows. In a live file, reconcile credits separately; a credit memo must not create a new purchase or turn a returning buyer into a first purchaser.
Use the union of accounts with purchase history through July. Keep accounts that bought before the visible months: D and G would disappear if the file contained only recent purchases. An account with no purchase before July is legitimately "Not yet" in the opening snapshot.
Make a sheet with these columns: A account ID, B orders before June, C May orders, D June orders, E July orders, F July net sales, G June state, and H July state. Enter the following formulas in row 2 and copy them down each account.
Orders before June, including older history:
=COUNTIFS(Orders!$A$2:$A$1000,$A2,Orders!$C$2:$C$1000,"<"&DATE(2026,6,1))
May orders:
=COUNTIFS(Orders!$A$2:$A$1000,$A2,Orders!$C$2:$C$1000,">="&DATE(2026,5,1),Orders!$C$2:$C$1000,"<"&DATE(2026,6,1))
For D2, change the May bounds to June 1 and July 1. For E2, change them to July 1 and August 1. Using an exclusive upper bound also handles order dates containing a time. Microsoft documents that COUNTIFS counts records meeting every supplied condition.
July net sales in F2:
=SUMIFS(Orders!$D$2:$D$1000,Orders!$A$2:$A$1000,$A2,Orders!$C$2:$C$1000,">="&DATE(2026,7,1),Orders!$C$2:$C$1000,"<"&DATE(2026,8,1))
The SUMIFS function sums amounts meeting multiple criteria, so the account and date filters stay together. Extend every range to cover the full export. Covirage is not affiliated with Microsoft.
June state in G2:
=IF(D2=0,IF(B2>0,"Lapsed","Not yet"),IF(B2=0,"First",IF(C2>0,"Repeat","Returned")))
July state in H2:
=IF(E2=0,IF(B2+D2>0,"Lapsed","Not yet"),IF(B2+D2=0,"First",IF(D2>0,"Repeat","Returned")))
Do not substitute a recent export for complete earlier history. Without earlier purchases, the formulas cannot distinguish a first buyer from a returning buyer.
These account-level rows are the complete calculation inputs. Orders before June include May orders; those columns overlap deliberately and must not be added together.
| Account | Orders before June | May orders | June orders | July orders | July net sales (USD) |
|---|---|---|---|---|---|
| A | 1 | 1 | 2 | 1 | 1,200 |
| B | 0 | 0 | 1 | 1 | 700 |
| C | 0 | 0 | 1 | 0 | 0 |
| D | 2 | 0 | 0 | 1 | 900 |
| E | 3 | 0 | 1 | 0 | 0 |
| F | 0 | 0 | 0 | 1 | 500 |
| G | 4 | 1 | 0 | 0 | 0 |
| H | 1 | 1 | 1 | 1 | 1,500 |
| Total July sales | 4,800 |
A and H move from Repeat to Repeat. B moves from First to Repeat, while C moves from First to Lapsed. D moves from Lapsed to Returned. E moves from Returned to Lapsed, F moves from Not yet to First, and G stays Lapsed.
That gives the transition matrix below. Every cell is a count of accounts, not orders.
| June state | July First | July Repeat | July Returned | July Lapsed | Opening rows |
|---|---|---|---|---|---|
| Not yet | 1 | 0 | 0 | 0 | 1 |
| First | 0 | 1 | 0 | 1 | 2 |
| Repeat | 0 | 2 | 0 | 0 | 2 |
| Returned | 0 | 0 | 0 | 1 | 1 |
| Lapsed | 0 | 0 | 1 | 1 | 2 |
| Closing rows | 1 | 3 | 1 | 3 | 8 |
To reproduce any cell, count the paired states. For the June First to July Repeat cell:
=COUNTIFS($G$2:$G$9,"First",$H$2:$H$9,"Repeat")
Use customer value analysis when the next question concerns economic differences between these accounts; do not assume all returning accounts have equal value.
Two reconciliations answer different questions.
Closing customers with purchase history = Opening customers with purchase history + First purchasers
Closing monthly purchasers = Continuing monthly purchasers + Returned purchasers + First purchasers
Seven accounts had purchased by June 30: every account except F. Add F's first purchase and the closing historical population is 7 + 1 = 8. Lapsed accounts remain in that historical population.
June purchasers were A, B, C, E, and H. Three continued purchasing, C and E lapsed, D returned, and F purchased for the first time. The active-buyer reconciliation is 5 - 2 + 1 + 1 = 5, also equal to 3 + 1 + 1 = 5.
Purchase retention is 3 / 5 = 60.0%. First-to-repeat conversion is 1 / 2 = 50.0% for June's first purchasers B and C. Reactivation is 1 / 2 = 50.0% for June's lapsed accounts D and G. Percentages are rounded to one decimal place; each denominator is its eligible opening group.
| July state | Accounts | Source accounts | July revenue (USD) |
|---|---|---|---|
| First | 1 | F | 500 |
| Repeat | 3 | A, B, H | 3,400 |
| Returned | 1 | D | 900 |
| Lapsed | 3 | C, E, G | 0 |
| Total | 8 | 4,800 |
Repeat revenue is 1,200 + 700 + 1,500 = $3,400. Adding $900 returned revenue and $500 first-purchase revenue reproduces the $4,800 export total. Use customer revenue analysis for the broader revenue comparison after this movement check passes.
Bring your dated order export and account file; Covirage maps the columns, and its tools compute and check state counts, transition totals, and associated revenue. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for sales insights for the supporting business context.
Use a stable account ID, a unique order ID, purchase date, and net sales amount. Keep enough earlier purchase history to distinguish a first purchase from a return. Add account ownership after the purchase states reconcile, so duplicate account records do not multiply orders.
Choose an explicit inactivity rule that matches the business. This example labels a previously purchasing account lapsed when it makes no qualifying purchase in the reporting month. A longer buying cycle needs a different threshold, applied consistently to both snapshots.
A cohort keeps customers grouped by a shared starting event, such as their first purchase month. Lifecycle analysis follows changing states across cutoffs. The same customer remains in its original cohort while moving through first purchase, repeat buying, lapse, and return.
No. A returned customer has purchase history before the inactive interval. Count that customer as an existing account becoming active again. Treating the return as a first purchase overstates acquisition and prevents the opening and closing customer populations from reconciling.