Ecommerce customer segmentation from order exports

Ecommerce customer segmentation assigns shoppers to explicit groups using their order history, recency, and contribution. Start with one customer row at a fixed cutoff, then apply rules in a declared order. The result should reconcile to the same customer and financial totals as the source exports.

Build the customer table from qualifying orders

A US outdoor accessories seller reviews customers on September 30, 2026. Its recent-performance window is July 3 through September 30, inclusive: 90 days. It retains earlier orders to distinguish a new shopper from an existing shopper who happened to order once recently.

Use customer ID, order ID, paid date, order status, net revenue after refunds, item cost, and other variable costs. Keep one row per order after consolidating line items. Remove canceled and test orders. In this example a qualifying purchase is a paid order with positive revenue after refunds; preserve excluded refunds in the financial reconciliation.

Put the cutoff in M1 and window start in M2. In an Orders tab, use columns A:F for customer ID, original paid date, net revenue, item cost, other variable cost, and qualifying-purchase flag. The financial columns must include relevant refunds even when the purchase flag is zero.

On the customer tab, A contains the unique customer list. B is the first qualifying purchase date from the full history; C is the last. D counts qualifying orders in the recent window. E:G hold recent revenue and costs. For C2 and D2:

=MAXIFS(Orders!B:B,Orders!A:A,A2,Orders!F:F,1,Orders!B:B,"<="&$M$1)
=COUNTIFS(Orders!A:A,A2,Orders!F:F,1,Orders!B:B,">="&$M$2,Orders!B:B,"<="&$M$1)

Google Sheets MAXIFS returns the maximum value matching criteria, which finds the latest qualifying purchase date. Its COUNTIFS function applies multiple conditions, so the count shares the same dates and status rule. Format date cells as dates and put customers without qualifying history on an exception list. Covirage is not affiliated with Google.

For the first qualifying purchase date in B2, use:

=MINIFS(Orders!B:B,Orders!A:A,A2,Orders!F:F,1,Orders!B:B,"<="&$M$1)

For recent net revenue in E2, then analogous cost sums in F2 and G2:

=SUMIFS(Orders!C:C,Orders!A:A,A2,Orders!B:B,">="&$M$2,Orders!B:B,"<="&$M$1)

SUMIFS sums values meeting all stated conditions. Refunds need a documented assignment to purchase date or refund date; choose one basis and reconcile it rather than silently switching.

Calculate contribution and recency

The customer summary below supplies the inputs to the classification. First and last purchase dates come from full history; financial amounts and order counts cover only the recent window. Other variable costs include fulfillment and payment costs under the seller's written policy.

In H2 enter =E2-F2-G2 for contribution. In I2 enter =$M$1-C2 for days since purchase. Fill both down.

These are recorded behavioral groups, not assumed personalities. Behavioral segmentation explains that distinction. For a wider recency-frequency-monetary framework, see RFM analysis.

Apply exclusive rules in a fixed order

Here, lapsing means at least 60 days since the last qualifying order. Among the rest, high contribution means at least $150 in recent contribution. First-time means one recent order and a first-ever purchase within the window. Everyone remaining is repeat. Both thresholds are illustrative business choices.

In J2:

=IF(I2>=60,"Lapsing",IF(H2>=150,"High contribution",IF(AND(D2=1,B2>=$M$2),"First-time","Repeat")))

Order matters. A previously valuable customer can be lapsing; the first rule keeps that customer on the follow-up list. Store the rule version with the cutoff so later changes remain explainable. Customer journey segmentation instead groups customers by declared lifecycle stage.

Compare the segments and prove completeness

For a segment name in O2, use =COUNTIFS(J2:J7,O2) and =SUMIFS(H2:H7,J2:J7,O2). Divide its summed contribution by summed revenue for margin; percentages shown are rounded to one decimal.

The checks are 1 + 1 + 2 + 2 = 6 customers and $1,320 − $660 − $210 = $450 contribution. Segment contribution also totals $40 + $200 + $120 + $90 = $450. These identities catch missing or overlapping groups before anyone uses the list.

The lapsing group includes a customer with no recent revenue. Dropping zero-order customers would hide that case. Review its purchase cycle before outreach, and use ecommerce experience analysis to investigate recorded delivery or service friction.

Where it goes wrong

• Recent history treated as lifetime history. A returning customer becomes falsely new.
• Revenue called value. Discounts and service costs can reverse the ranking.
• Overlapping assignments. Independent tags need separate reporting; exclusive groups need precedence.
• Thresholds applied across every category. Seasonal purchasing can look like inactivity.

Compare your own customer groups in Covirage

Bring your order export, customer history, and written segment rules. Covirage maps the columns; its tools compute and check the segment figures, and the external AI model explains the result 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 business questions these comparisons support.