Retail POS analytics: analyze sales, returns and baskets

Retail POS analytics starts by reconciling transaction lines into net sales, then measuring sale receipts, discounts and returns on clearly defined bases. A receipt with several SKUs is one basket, while a refund receipt is a separate event. Set those rules before comparing stores or interpreting a sales change.

Start with completed merchandise lines

Export one row per receipt line for a closed business period. Keep store, date, receipt ID, line ID, SKU, transaction type, status, units, merchandise sales, discount and refund. Use a store and receipt key when different stores reuse receipt numbers.

Remove voided lines and training transactions. Keep taxes, tips, gift-card issuance and tender movements outside merchandise sales. A gift-card purchase and its later redemption should not both become merchandise revenue. Check your export's definitions before changing any signs: some systems already deduct discounts or record refunds as negative sales.

For a US reporting reference, the Census Bureau's retail sales definition deducts refunds and allowances for returned merchandise and excludes sales taxes collected for tax agencies. Your operating report should state its own treatment just as clearly. The broader retail analytics guide explains how the reconciled sales file connects with inventory and other business records.

Rebuild net sales from the rows

Riverbend Home Goods uses these illustrative lines for its Atlanta store for one completed trading day. Refunds are positive deductions; return units are negative. List sales exclude tax and have not yet been reduced by discounts.

With headers in row 1 and the displayed columns in A:I, enter this in I2 and fill down through I7:

=F2-G2-H2

Net sales = List merchandise sales − Discounts − Refunds

The control is $110 − $8 − $18 = $84. Sum I2:I7 and compare it with the POS merchandise net-sales control for the same store and date. Investigate any difference before reading the basket measures. Tender settlements need a separate reconciliation because tax, gift cards, timing and payment fees can affect them.

For SKU totals, put Towel and Soap in K2:K3 and use this formula beside each:

=SUMIFS($I$2:$I$7,$D$2:$D$7,K2)

Microsoft documents SUMIFS as adding values that meet specified criteria. Towel contributes $56 and Soap $28; $56 + $28 = $84 proves the product view still ties to the store control. Covirage is not affiliated with Microsoft.

Count receipts before calculating baskets

Create a separate receipt summary containing each completed sale receipt once. Do not count line rows or include the refund receipt in the basket denominator. The same source lines give:

In a fresh summary area, put those receipt IDs in K2:K4 and calculate L2, filled down:

=SUMIFS($I$2:$I$7,$B$2:$B$7,K2,$C$2:$C$7,"Sale")

For this single-store example, receipt alone identifies the basket. Add a store criterion when summarizing a multi-store export. The purchase basket value is:

=SUM(L2:L4)/COUNT(L2:L4)

That gives $102 / 3 = $34. Period net sales per sale receipt is $84 / 3 = $28. Both are valid, but label them differently: the second includes a refund that may relate to an earlier day's purchase.

The sale rows contain 7 units, so units per basket are 7 / 3 = 2.33, rounded to two decimals. The negative return unit belongs in net unit movement, which is 6. Distinct customers are another denominator; retail customer analytics covers that separate job.

Turn the checked measures into a store review

Discounts are $8 / $110 = 7.27% of list sales. Refunds are $18 / $102 = 17.65% of sales after discounts, with both percentages rounded to two decimals. Call the latter a period refund burden. It is not the return rate of today's baskets without matching the refund to its original sale.

Compare these measures by store, SKU and trading period. A higher basket value with fewer sale receipts calls for a different review from rising receipts with deeper discounts. Use pricing analytics in retail for price and promotion decisions after the discount basis is reconciled.

Where it goes wrong

• Counting lines as baskets. Multi-item receipts inflate the denominator and depress basket value.
• Subtracting discounts twice. An export's sales column may already be net of discounts. Confirm it against a receipt before applying the formula.
• Treating period refunds as a purchase cohort. A current refund may reverse an earlier sale. Keep event reporting and original-sale analysis distinct.
• Mixing business dates. A late-closing store and a calendar-day export may allocate the same transaction to different periods.
• Adding tax or tender to merchandise sales. Those amounts require reconciliation, but they do not describe the merchandise basket.

Analyze your own POS file in Covirage

Bring a sample of your own completed POS lines and receipt control totals to Covirage. Covirage maps the columns, its tools compute and check the sales and basket figures, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your export.

See revenue driver analysis for the supporting business approach.