Blog · Territory, capacity and quota planning
Build retail shopper groups from identified purchases across stores and online orders. Compare frequency, revenue, and gross margin while reconciling customer counts and sales across locations.
Retail customer segmentation groups identified shoppers across stores and channels using recorded purchases, frequency, and economics. Combine each shopper's history under one stable ID, then compare group revenue and margin over the same dates. A company customer count must deduplicate shoppers who buy in several places.
The practical question is whether the groups behave differently enough to warrant a merchandising, service, or commercial review. Start with evidence from completed orders. Keep store context and channel context visible without counting the same order twice.
Choose the reporting period, included locations, and selling-channel definitions before aggregating. Use the order's selling channel, with pickup or fulfillment location recorded separately when needed. A store collecting an online order should not create another purchase in the history.
Use a company-wide customer ID that survives purchases at different stores and online. If the same identifier means different people in different systems, obtain a verified account mapping. Do not match shoppers merely because their names resemble each other. Unmatched and anonymous transactions belong in separate sales totals.
This method focuses on cross-store and cross-channel comparisons. The broader retail customer analytics page covers other customer measures, while ecommerce customer segmentation covers the online-specific workflow.
Use net revenue after discounts and refunds. The IRS describes returns and allowances as deductions in calculating net receipts. Apply finance's consistent sales and COGS definitions; do not compare one channel before returns with another after returns.
An illustrative US retailer has these September 2026 orders. Each row is one completed order, all customers are identified, and there are no returns in this example. Web is the online selling pool, not a physical store. Put the table on a Sales sheet in columns A:F, with headers in row 1.
| Order ID | Shopper ID | Channel | Selling location | Net revenue (USD) | COGS (USD) |
|---|---|---|---|---|---|
| S1 | C01 | Store | North | 100 | 60 |
| S2 | C01 | Online | Web | 80 | 48 |
| S3 | C02 | Store | North | 120 | 72 |
| S4 | C02 | Store | South | 60 | 36 |
| S5 | C03 | Online | Web | 200 | 140 |
| S6 | C04 | Store | South | 90 | 63 |
| S7 | C04 | Online | Web | 50 | 35 |
| S8 | C05 | Store | North | 100 | 70 |
| Total | 800 | 524 |
Keep order dates in the real export and filter them to the agreed period before using these ranges. For a line-item export, first consolidate items to unique order IDs; counting item rows would inflate frequency.
On a Shoppers sheet, list C01 through C05 once in A2:A6. Use B for order count, C for store orders, D for online orders, E for physical-store count, F for revenue, G for COGS, H for gross profit, and I for the primary segment.
In Google Sheets, COUNTIFS counts matching records across criteria. Count store and online purchases separately:
B2 =COUNTIFS(Sales!$B$2:$B$9,A2)
C2 =COUNTIFS(Sales!$B$2:$B$9,A2,Sales!$C$2:$C$9,"Store")
D2 =COUNTIFS(Sales!$B$2:$B$9,A2,Sales!$C$2:$C$9,"Online")
E2 =IF(COUNTIFS(Sales!$B$2:$B$9,A2,Sales!$D$2:$D$9,"North")>0,1,0)+IF(COUNTIFS(Sales!$B$2:$B$9,A2,Sales!$D$2:$D$9,"South")>0,1,0)
The physical-store formula explicitly covers this example's North and South locations. Expand the location list for your own file. SUMIFS adds the revenue and costs matching each shopper:
F2 =SUMIFS(Sales!$E$2:$E$9,Sales!$B$2:$B$9,A2)
G2 =SUMIFS(Sales!$F$2:$F$9,Sales!$B$2:$B$9,A2)
H2 =F2-G2
I2 =IF(AND(C2>0,D2>0),"Cross-channel",IF(B2>=2,"Repeat single-channel","Single purchase"))
Fill down. Covirage is not affiliated with Google. The classification prioritizes cross-channel membership, so each shopper appears in one primary group.
| Shopper ID | Orders | Physical stores | Revenue (USD) | Gross profit (USD) | Primary segment |
|---|---|---|---|---|---|
| C01 | 2 | 1 | 180 | 72 | Cross-channel |
| C02 | 2 | 2 | 180 | 72 | Repeat single-channel |
| C03 | 1 | 0 | 200 | 60 | Single purchase |
| C04 | 2 | 1 | 140 | 42 | Cross-channel |
| C05 | 1 | 1 | 100 | 30 | Single purchase |
C02 shops across stores but within one selling channel. C01 and C04 buy through both channels. Those are different patterns, even though all three shoppers placed repeat orders.
On a Groups sheet, list the three segment labels in A2:A4. Use B for shoppers, C for orders, D for revenue, E for gross profit, and F for gross margin:
B2 =COUNTIFS(Shoppers!$I$2:$I$6,A2)
C2 =SUMIFS(Shoppers!$B$2:$B$6,Shoppers!$I$2:$I$6,A2)
D2 =SUMIFS(Shoppers!$F$2:$F$6,Shoppers!$I$2:$I$6,A2)
E2 =SUMIFS(Shoppers!$H$2:$H$6,Shoppers!$I$2:$I$6,A2)
F2 =IF(D2=0,"",E2/D2)
| Segment | Shoppers | Orders | Revenue (USD) | Gross profit (USD) | Gross margin |
|---|---|---|---|---|---|
| Cross-channel | 2 | 4 | 320 | 114 | 35.6% |
| Repeat single-channel | 1 | 2 | 180 | 72 | 40.0% |
| Single purchase | 2 | 2 | 300 | 90 | 30.0% |
| Total | 5 | 8 | 800 | 276 | 34.5% |
Margins use total group gross profit divided by total group revenue, rounded to one decimal. Cross-channel margin is 114 / 320 = 35.6%. Company gross profit is both 114 + 72 + 90 = $276 and 800 - 524 = $276. Company margin is 276 / 800 = 34.5%.
Now check the selling-location view against the same order rows:
| Selling location | Distinct shoppers | Orders | Revenue (USD) | COGS (USD) |
|---|---|---|---|---|
| North | 3 | 3 | 320 | 202 |
| South | 2 | 2 | 150 | 99 |
| Web | 3 | 3 | 330 | 223 |
| Company | 5 | 8 | 800 | 524 |
Revenue reconciles as 320 + 150 + 330 = $800; COGS reconciles as 202 + 99 + 223 = $524. Location shopper counts sum to 8 appearances, not 5 distinct people. The company denominator comes from the deduplicated Shoppers sheet.
Three of the five shoppers placed multiple orders, so the within-period repeat-purchase rate is 3 / 5 = 60.0%. That is not a later-period retention rate. Keep new shoppers' observation time visible before comparing frequency across cohorts.
The example gives the retail team a reason to inspect product mix and cost differences across the groups. It does not prove that promoting another channel would improve margins. Gross profit also excludes delivery and service costs that may matter to the decision.
Keep the shopper membership list beside the quantitative customer profile. Add recency only when the review needs it; RFM analysis explains that separate measure.
Bring a sample of your own order and cost exports with stable shopper IDs, dates, stores, and selling channels. Covirage maps the columns, its tools compute and check group revenue and margin, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring your own file.
See sales insights for the supporting commercial context.
Anonymous purchases can support store, basket, and channel comparisons, but they cannot establish a shopper's repeat history. Keep those sales in a separate reconciliation bucket. Do not combine all blank IDs into a single customer or assume unidentified receipts represent different people.
Record the order once under a documented selling-channel rule and retain pickup location as a separate attribute. A pickup event does not create another sale. Keep the same treatment across stores and periods so fulfillment changes do not appear as customer growth.
That depends on the recorded sales and costs. Compare group gross profit divided by group net revenue, then review shipping, returns, and service costs separately where available. A channel label by itself does not establish profitability or prove that moving customers between channels would improve it.