Retail customer analytics: buyers, baskets and repeat visits

Retail customer analytics connects identifiable buyers with their purchase frequency, basket mix and net spend. Start with the transaction export, count purchase receipts separately from returns, and compare customers over the same dates. The result explains who returns to buy and what spending remains after refunds.

This customer view complements retail analytics, which covers the wider business. A store can grow total sales while depending on fewer repeat buyers; a customer table makes that change visible.

Start with receipts and a defined customer base

Export customer ID, receipt ID, date, transaction type, category and amount after discounts. Exclude sales tax and state whether shipping is included. Use an existing stable customer reference; this workflow does not require cameras or biometric identification.

Set the dates before counting. The example below is an illustrative US retailer's identified transactions for October 1 through October 31, 2026. Returns are recognized on their posting dates, including their category and original receipt reference. A purchase-cohort report would instead match later refunds to the original purchases and state its refund cutoff.

Each row below represents a complete purchase basket or a refund event. If your export has one row per item, first summarize purchase lines by receipt and customer. Otherwise, a basket containing several products will look like several visits. Even a correctly counted receipt measures a purchase occasion; separate receipts do not prove separate physical visits.

Keep unidentified transactions in an explicit unmatched bucket when reconciling the full register. Do not treat that bucket as one customer or assume identified shoppers represent everyone. The choice of eligible buyers follows the same principle as defining the norm from your own customer base.

Build the transaction table

Put these headers in A1:H1 and the eight events in rows 2:9. Return rows reference the receipt being refunded. All amounts are USD, with discounts already applied.

The net amount in H2 is:

=SUM(F2:G2)

Fill down through H9. Purchase amounts total $575; refunds total $50; net spend is $525. The two refund events reduce spend but add no purchase receipts.

Calculate buyers, frequency and category mix

List each distinct customer once in J2:J5. Put purchase receipts in K, apparel net spend in L, home net spend in M, total net spend in N and a repeat flag in O.

In K2, count only the customer's sale rows. Microsoft's COUNTIFS documentation confirms that every specified condition must match:

=COUNTIFS($B$2:$B$9,J2,$D$2:$D$9,"Sale")

In L2, M2 and N2 respectively, sum the customer's amounts. SUMIFS adds values meeting the stated criteria:

=SUMIFS($F$2:$F$9,$B$2:$B$9,J2)
=SUMIFS($G$2:$G$9,$B$2:$B$9,J2)
=SUMIFS($H$2:$H$9,$B$2:$B$9,J2)

In O2, flag customers with at least two purchase receipts, then fill every formula down through row 5:

=IF(K2>=2,1,0)

Covirage is not affiliated with Microsoft.

The summary produces these measures. Percentages are rounded to one decimal place; money is shown to two decimals where needed.

For the first four measures, the exact formulas are:

=SUM(K2:K5)/COUNTA(J2:J5)
=SUM(O2:O5)/COUNTA(J2:J5)
=SUM(N2:N5)/SUM(K2:K5)
=SUM(N2:N5)/COUNTA(J2:J5)

The repeat share describes this month's observed behavior. It does not establish retention or prove that a promotion caused another purchase. Use retail customer segmentation when turning these measures into consistently defined groups.

Prove the result before choosing an action

Run three checks. Category totals must give net spend: $250 + $275 = $525. Customer net spend must equal transaction net spend: $140 + $220 + $90 + $75 = $525. Finally, frequency times net spend per basket must equal net spend per buyer: 1.5 × $87.50 = $131.25.

The first reconciliation in a spreadsheet should return zero:

=SUM(N2:N5)-SUM(H2:H9)

C03 bought both categories but returned the entire home portion. A merchandising review should therefore distinguish initial category purchase from retained category spend. C01 and C02 are both repeat buyers, yet their category mixes differ. That supports a more specific assortment question than sending the same offer to everyone.

Retail operations should own receipt completeness, finance the net-sales reconciliation, and merchandising the category interpretation. If return-related contacts are increasing, move that separate question into customer care analytics.

Where it goes wrong

• Item rows counted as baskets. Count complete purchase receipts after item lines have been summarized.
• Refunds counted as purchases. Keep return events in net spend while excluding them from purchase frequency.
• Unequal observation windows. A buyer identified near month-end has had less opportunity to repeat. Compare acquisition cohorts over equal follow-up periods.
• Shared or missing IDs. A household account can contain several shoppers; anonymous receipts cannot establish customer frequency. Label the population you can actually observe.
• Spend called profit. Category mix and net spend omit product cost and service cost. They support merchandising questions, not a profitability conclusion.

Do this on your own file in Covirage

Bring a transaction export with customer IDs, receipts, categories and refunds. Covirage maps the columns, and its tools compute the customer comparisons and check the totals; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own data.

See sales insights for the supporting business context.