Retail POS data analysis: sales, returns and basket value

Retail POS data analysis starts by reconciling merchandise sales and returns, then comparing stores on net sales, purchase receipts and basket value. Count completed purchases rather than exported lines. A report that mixes receipts, item lines and refunds can make the busiest store look less productive simply because it sells more items per visit.

Start with the transaction export

Export a closed reporting period from the point-of-sale system. Keep receipt ID, store, business date, product group, transaction type and merchandise amount after discounts. Preserve the source line ID if a receipt has several products.

Choose the sales boundary before calculating anything. In this example, merchandise sales exclude sales tax, gift-card issuance and payment movements. Returns use their posting date, even when the original purchase happened earlier. This measures the period's recorded activity, rather than the eventual return rate of a purchase cohort.

Separate completed purchases, returns, voids and canceled transactions. A return amount appears once with a negative sign. If the source already supplies net sales, do not subtract its returns again. The broader retail POS analytics guide covers how these measures support recurring review; this method establishes the figures underneath it.

Work the same rows before comparing stores

Consider a US retailer's closed business day. Each completed purchase in this small example contains one product group, so one sale row equals one purchase receipt. Columns A through E contain the following export rows.

For a store summary, put North in H2 and South in H3. In I2, sum sales; in J2, turn the signed return total into a positive deduction:

=SUMIFS($E$2:$E$8,$B$2:$B$8,H2,$D$2:$D$8,"Sale")
=-SUMIFS($E$2:$E$8,$B$2:$B$8,H2,$D$2:$D$8,"Return")

Microsoft documents SUMIFS as summing values that meet multiple criteria. Here those criteria are store and transaction type. Fill both formulas down, keeping the source ranges fixed.

Net sales in K2, purchase receipt count in L2 and return-adjusted basket value in M2 are:

=I2-J2
=COUNTIFS($B$2:$B$8,H2,$D$2:$D$8,"Sale")
=IF(L2=0,"",K2/L2)

COUNTIFS counts rows satisfying every specified condition. That makes the count valid for this receipt-level example. For an item-level export, first create a purchase-receipt summary with one row per store and receipt ID; otherwise the formula counts items. Covirage is not affiliated with Microsoft.

Distinguish the two basket measures

The sales-only basket is sales before returns divided by purchase receipts. The return-adjusted basket uses net sales over the same denominator. Both can help, provided their labels make the treatment clear.

The sales-only basket formula is =IF(L2=0,"",I2/L2). Round displayed dollars to two decimals, but retain full precision in calculations. The total return-adjusted basket is 380 / 5 = $76.00; it is not the average of the two store results.

North has both higher purchase basket value and higher net sales. That does not establish better profitability: these rows contain no inventory cost or labor expense. Return deductions also include purchases from earlier dates, so the adjusted basket is a period reporting measure rather than the value of a typical current purchase.

Prove the totals, then look at products

The first check is the signed export total: 80 + 120 + 100 - 40 + 60 + 90 - 30 = $380. It equals $450 of sales less $70 of returns, and it equals the sum of the stores' net sales. Check that against the POS merchandise control total on the same exclusions and posting dates.

Regrouping the same rows by product must preserve that result:

Replace the store criteria range with column C in the SUMIFS formulas to reproduce this view. Sales receipt counts by product are generally not additive because one basket may contain several groups. A product sales dip also needs an availability check; sell-out gaps by store address that separate question.

Where it goes wrong

• Counting item rows as visits. Deduplicate completed purchase receipts before comparing basket value across stores.
• Subtracting refunds twice. Establish whether the exported merchandise amount is already net. Keep credit notes and returns consistent with that convention.
• Treating tender as revenue. Split payments and gift-card redemption describe settlement; they do not create extra merchandise sales.
• Comparing unlike trading periods. Store hours, closures and reporting cutoffs can change totals. Retail location analysis considers the wider store context separately.

Do this on your own POS file in Covirage

Bring your transaction export and its merchandise control totals to Covirage. Covirage maps the columns, its tools compute and check sales, returns and receipt-based measures, 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 Excel analysis for the supporting file-based workflow.