Loyalty segmentation: rules and customer actions

Loyalty segmentation groups customers by explicit rules for repeat purchasing and continued activity. Use a completed-order export to assign each customer a status, then compare orders, revenue and contribution within those statuses. The label should describe observable behavior and lead to a specific customer action.

Set the population and status rules first

A US office-supply retailer reviews customers as of September 30, 2026, using orders from July 1 through September 30. Its customer list includes everyone in the review population, including established customers who placed no orders during the quarter.

The retailer chooses these rules for its own replenishment pattern:

These are working definitions, not industry benchmarks. A customer without a known purchase history belongs in a review or prospect group, outside this purchased-customer example. Do not convert a missing purchase date into a long period of inactivity.

Purchase-based status also differs from stated preference or program enrollment. Keep membership, tenure and account attributes in separate columns. The broader behavioral segmentation method explains other purchase patterns; this workflow uses a small set of explicit loyalty statuses.

Build the customer worksheet from an order export

Use an Orders sheet with one row per completed order: customer ID in A, order date in B, net revenue in C and variable cost in D. Remove canceled orders and consolidate order lines before counting. Include returns and credits consistently in revenue and cost, without counting a credit memo as another purchase.

The variable-cost definition here includes goods, fulfillment, payment fees and order incentives. It excludes fixed overhead. Contribution therefore measures the amount available to cover fixed costs, rather than net profit.

Set Params!B1 to =DATE(2026,9,30) and Params!B2 to =DATE(2026,7,1). On Customers, use columns A:H for customer ID, latest completed purchase date through the cutoff, quarterly orders, days since purchase, revenue, variable cost, contribution and status.

For row 2, the order-count formula is:

=COUNTIFS(Orders!$A$2:$A$50000,A2,Orders!$B$2:$B$50000,">="&Params!$B$2,Orders!$B$2:$B$50000,"<"&Params!$B$1+1)

The final condition includes timestamps throughout the closing date. Google's COUNTIFS documentation describes counting rows that satisfy multiple criteria and requires equally sized criteria ranges. Extend every range together if the export is larger.

Calculate days since purchase in D2, using the latest purchase date supplied from the complete history in B2:

=IF(B2="","",INT(Params!$B$1)-INT(B2))

Sum revenue in E2 and variable cost in F2:

=SUMIFS(Orders!$C$2:$C$50000,Orders!$A$2:$A$50000,A2,Orders!$B$2:$B$50000,">="&Params!$B$2,Orders!$B$2:$B$50000,"<"&Params!$B$1+1)
=SUMIFS(Orders!$D$2:$D$50000,Orders!$A$2:$A$50000,A2,Orders!$B$2:$B$50000,">="&Params!$B$2,Orders!$B$2:$B$50000,"<"&Params!$B$1+1)

Google documents SUMIFS as a conditional sum across multiple criteria. The same customer and date conditions must govern orders, revenue and costs.

Finally, calculate G2 and assign H2, then fill all formulas down:

=E2-F2
=IF(B2="","Review",IF(D2>60,"Lapsed",IF(C2>=3,"Loyal",IF(C2=2,"Repeat","Occasional"))))

The lapse rule comes first. A customer can have a purchase inside the quarter but still be past the retailer's inactivity limit at quarter-end.

A worked customer table

The following rows are the customer-level results. Dates in column B are actual spreadsheet dates, displayed in US format.

L8 illustrates rule priority: the customer placed an order in the quarter but is lapsed at the observation date. L6 and L7 retain their customer rows even though quarterly revenue is zero.

Summarize the groups and prove the result

Put the status names in J2:J5. Use K for customers, L for orders, M for revenue, N for contribution and O for contribution margin:

=COUNTIFS($H$2:$H$9,J2)
=SUMIFS($C$2:$C$9,$H$2:$H$9,J2)
=SUMIFS($E$2:$E$9,$H$2:$H$9,J2)
=SUMIFS($G$2:$G$9,$H$2:$H$9,J2)
=IF(M2=0,"",N2/M2)

Margins divide each group's contribution by its revenue and are rounded to one decimal place. The total is 1,620 / 5,500 = 29.5%; do not average the displayed group percentages. Customer counts sum to 8, orders to 17 and revenue to $5,500. Contribution also checks independently: 5,500 - 3,880 = $1,620.

The loyal group provides the largest contribution amount, while L4's repeat purchases leave a thinner margin. Review that account's discounts and fulfillment costs before offering another incentive. Lapsed customers need a separate review of timing and relationship history; a quarterly margin does not establish that a win-back offer will pay.

Use RFM analysis for a more detailed purchase-ranking method. Use lifecycle segmentation when the question concerns progression from acquisition to activation and repeat buying. The B2B ledger segmentation guide covers commercial account grouping more broadly.

Where it goes wrong

• Counting invoice lines as orders. Consolidate a completed order before measuring purchase frequency.
• Excluding customers with no recent orders. Start from the customer population and retain zero-activity rows.
• Using the same labels with changing rules. Save the cutoff, window and rule version with each result.
• Calling frequent buying profitable. Contribution depends on costs and discounts as well as order count.
• Reading a label as proof of preference. A purchase-based status records behavior under a rule; it does not reveal a customer's motivation.

Covirage is not affiliated with Google.

Compare loyalty statuses on your own file in Covirage

Bring your customer list, supplied loyalty rules and completed-order export with net revenue and the relevant costs. Covirage maps the columns, its tools compute and check the group figures, and 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 sales insights for the supporting commercial analysis use case.