Retail customer churn analysis without cancellations

Retail customer churn analysis starts with a buying window because customers usually stop purchasing without sending a cancellation. Flag customers whose last qualifying purchase is older than the window appropriate to their buying pattern. Then compare repeat buying and historical purchase value without calling every quiet customer permanently lost.

Fix the population and the calendar

Choose a snapshot date and freeze the customers eligible at the start of the observation period. Keep new customers separate so acquisition does not dilute the lapse rate. A customer needs enough history to have had a genuine opportunity to buy again.

Customer lapse rate = Eligible customers beyond their buying window / Eligible opening customers

Repeat-purchase rate = Opening customers making a qualifying purchase during the observation period / Eligible opening customers

The shared cadence principle appears in B2B churn without a subscription. Retail adds a strong need to distinguish seasonal purchases from replenishment. These groups should use an equally clear observation date, even when their allowed purchase gaps differ.

Document whether the window is an elapsed-day rule or a seasonal deadline. An annual holiday purchase may be better reviewed after the relevant selling season than against an arbitrary short gap. Keep the chosen definition visible beside every result.

Prepare the order export

Start with order ID, customer ID, completed purchase date, net merchandise sales and a qualifying-purchase flag. Net linked returns against the original order first. Exclude canceled orders and return-only events from purchase activity; keep their monetary effects in the appropriate sales reconciliation.

Use a stable customer identifier across channels. Where you cannot reliably join store and online identities, report the known population and its coverage limitation. Anonymous transactions cannot support a claim about individual customer retention.

Create one customer-summary row for each opening customer. The following illustration uses a US retailer's July 31, 2026 snapshot. Prior-year sales cover July 1, 2025 through June 30, 2026. The 30-day and 365-day windows are illustrative business rules, not recommended retail benchmarks.

The lapse test is strictly greater than the allowed gap. A customer exactly at the threshold is still within the window. That boundary choice must stay constant when the review is repeated.

Build the spreadsheet flags

Put the summary columns above in A:H, with actual dates in column C and the snapshot date in J1. Enter these formulas in F2, G2 and H2, then fill down:

=$J$1-C2
=IF(F2>D2,"Yes","No")
=IF(AND(C2>=DATE(2026,7,1),C2<DATE(2026,8,1)),"Yes","No")

Column H works because this snapshot is at the observation month's end and column C is the latest qualifying purchase. For another historical month, count purchases within that month's date boundaries from the order export instead of using the current latest purchase.

To obtain an opening group's size and lapsed count, put its label in L2:

=COUNTIFS(B2:B7,L2)
=COUNTIFS(B2:B7,L2,G2:G7,"Yes")

The Google Sheets COUNTIFS documentation confirms that the function counts rows satisfying multiple criteria. Use the corresponding conditional sum for the group's lapsed historical value:

=SUMIFS(E2:E7,B2:B7,L2,G2:G7,"Yes")

The SUMIFS documentation explains the same multiple-criteria approach for sums. Covirage is not affiliated with Google. For a neighboring workbook method, see customer churn analysis in Excel.

Compare buying groups without losing the denominator

The customer rows produce this review, with percentages rounded to one decimal place:

The overall repeat rate is 2 / 6 = 33.3%, rather than the simple average of the group percentages. The lapse rate is 3 / 6 = 50.0%. Historical value tied to lapsed customers is 480 + 220 + 600 = $1,300, or 1,300 / 2,800 = 46.4% of the opening cohort's prior-year sales.

This lapse rate describes status at July 31 among the fixed opening cohort. It does not count only customers who newly lapsed during July. For that movement measure, retain each customer's opening lapse status and distinguish new lapses from customers already beyond their window.

The seasonal group's lack of July buying is not sufficient evidence of poor retention. R03 remains within its stated window. Applying the replenishment rule to that customer would add a lapse purely through a definition change.

Reconcile the result and give it an owner

Opening customers must split into 3 lapsed + 3 within window = 6. Historical sales must split into 1,300 lapsed + 1,500 within window = 2,800. Tie the underlying order totals to the chosen sales report, and retain an exception list for missing customer IDs or purchase dates.

Merchandising should review whether the buying groups still reflect the category calendar. The retention owner can review lapse reasons and contact eligibility using the company's existing process. Finance should confirm the sales basis before the team ranks the dollar values. Broader retail customer analytics can put the review alongside basket and purchase patterns.

Where it goes wrong

• One cutoff for every category. Seasonal and replenishment customers have different opportunities to repeat.
• Short histories. Recently acquired customers may not yet have completed a buying cycle.
• Returns counted as purchases. Refund activity can falsely reset the clock.
• Historical sales called future losses. The table measures past value associated with today's lapse status.
• Identity gaps ignored. Unmatched purchases can make known customers look inactive.

Review your retail cohorts in Covirage

Upload a sample of your own order export and customer buying-window file, or bring a Google Sheet. Covirage maps the columns, and its tools compute and check the cohort figures; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring the files behind your lapse review.

See renewal watch for the supporting business context.