Convenience store customer segmentation from transactions

Convenience store customer segmentation groups identified shoppers by visit frequency, basket value, and category mix over a fixed period. Start with stable shopper IDs in your POS export; anonymous receipts describe baskets but cannot prove repeat customer behavior.

For a store manager, the useful result is a clear comparison: which identified shoppers return frequently, what they buy, and whether a category has a meaningful place in their spending. Those observations can guide a merchandising test without claiming to know the shopper's personal circumstances or reasons for visiting.

Choose the receipt and shopper definitions

Use a completed purchase receipt as the visit unit for this analysis. If a shopper makes separate purchases during one physical stop, the worksheet counts separate purchase occasions. State that limitation instead of describing receipt frequency as exact foot traffic.

The export needs a store identifier, receipt ID, business date, stable shopper ID when present, merchandise category, net sales, and transaction status. A receipt ID may only be unique within its store and business date, so confirm the full key before removing duplicates.

Prepare one row per receipt, summing item lines first. Leave out voids. Apply a documented treatment to returns, sales tax, fuel, and nonmerchandise amounts. This example covers completed in-store merchandise purchases after discounts, excluding tax, fuel, and return-only receipts.

Use an approved internal shopper identifier rather than exporting payment card numbers or unnecessary contact details. The FTC's guide to protecting business information recommends keeping sensitive information only for a legitimate business need. A stable ID is still information to handle under your organization's controls.

For the broader retail setting, see retail customer segmentation. This method focuses on the repeat visits and category mix visible in a convenience-store receipt file.

Summarize each identified shopper

An illustrative US store examines September 2026. Its identified receipts occupy rows 2:45 on a sheet named Raw, with shopper ID in A, receipt ID in B, merchandise sales in C, and prepared-food sales in D. Every row is already one completed purchase receipt.

Create a Summary sheet with the unique shopper IDs in A2:A7. In columns B, C, and D calculate receipt count, merchandise sales, and prepared-food sales:

B2 =COUNTIF(Raw!$A$2:$A$45,A2)
C2 =SUMIFS(Raw!$C$2:$C$45,Raw!$A$2:$A$45,A2)
D2 =SUMIFS(Raw!$D$2:$D$45,Raw!$A$2:$A$45,A2)

Fill down. Microsoft's SUMIFS documentation explains how matching criteria select the values to add; here the criterion is the stable shopper ID. Keep blank IDs in a separate anonymous-receipt summary.

Covirage is not affiliated with Microsoft or Google.

Average basket in E and prepared-food share in F are:

E2 =IF(B2=0,"",C2/B2)
F2 =IF(C2<=0,"",D2/C2)

The resulting shopper totals are the inputs for every group calculation below. Dollar amounts are in USD; shares are rounded to one decimal place.

Total average basket is 407 / 44 = $9.25. Prepared-food share is 162 / 407 = 39.8%. Neither total is the simple average of the six shopper ratios.

Apply explicit rules in a fixed order

For this store's test, frequent means at least eight purchase receipts during the month. Among frequent shoppers, food-led means prepared food accounts for at least half of merchandise spending. These are illustrative operating thresholds, not industry benchmarks.

Put the group in G2 and fill down:

=IF(B2<8,"Occasional",IF(F2>=50%,"Frequent food-led","Frequent other"))

The rule puts an occasional shopper with a high food share in Occasional. That is intentional: frequency is the first split. If the business needs both views, preserve frequency and category flags as separate descriptive columns while keeping one primary group for additive reporting.

The distinction between observed purchasing and inferred motivation matters. Behavioral segmentation covers that principle. The label food-led means a measured category share here; it does not prove a shopper came specifically for lunch.

Compare the groups using totals

Place the group names in I2:I4. Use J for shoppers, K for receipts, L for merchandise sales, and M for prepared-food sales:

J2 =COUNTIF($G$2:$G$7,I2)
K2 =SUMIFS($B$2:$B$7,$G$2:$G$7,I2)
L2 =SUMIFS($C$2:$C$7,$G$2:$G$7,I2)
M2 =SUMIFS($D$2:$D$7,$G$2:$G$7,I2)
N2 =K2/J2
O2 =L2/K2
P2 =M2/L2

N is receipts per shopper, O is average basket, and P is prepared-food share. Fill down to reproduce this comparison, rounding average baskets to cents and shares to one decimal place.

Occasional shoppers have the largest baskets, but the food-led group produces more merchandise sales through repeat purchases. A manager might test prepared-food availability for the frequent food-led group, then measure results against comparable periods. Sales alone does not establish category profit; product costs and waste would be needed for that question.

Reconcile identified and anonymous receipts

The store also has anonymous purchases. Keep them in the total-store check without inventing shopper identities:

The identified customer analysis covers 44 / 52 = 84.6% of receipts and 407 / 479 = 85.0% of sales, rounded to one decimal place. These are identification coverage measures, not loyalty or customer retention rates. The group sales must sum to $407, while identified plus anonymous sales must tie to $479 in the scoped POS report.

Where it goes wrong

• Item lines counted as visits. A multi-item purchase must count once after receipt aggregation.
• Shared or changing IDs. Household sharing or replaced IDs can distort individual frequency. Document what the identifier represents.
• Anonymous purchases ignored. A changing identification rate can shift group results without any change in shopper behavior.
• Threshold changes hidden. Preserve old rules and examine segment migration when comparing periods.

Compare your POS groups in Covirage

Upload an approved POS workbook or Google Sheet with stable shopper IDs, receipt totals, category totals, and your group definitions. Covirage maps the columns; its tools compute and check the comparisons, 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 export.

See sales insights for the supporting commercial context.