Omnichannel analytics: reconcile sales across channels

Omnichannel analytics starts with a sales total that counts every order once across stores and online. Use consistent order identifiers, put returns against the originating sale, and compare repeat buying only where the supplied customer identifiers already match across channels.

The purpose is a commercial reconciliation: where net sales happened and how identified customers bought. Campaign attribution and contact-center journeys answer different questions.

Set the channel and period before combining files

Choose an originating sales channel for each order. The Census Bureau's retail definitions classify ecommerce by how the order is placed or its terms negotiated; payment need not happen online. That supports separating order channel from payment method and pickup location.

Choose the reporting period and a consistent treatment of canceled orders, sales tax, shipping, discounts, and refunds. For the example below, merchandise sales exclude sales tax and shipping. Refunds are posted within the same period as the orders they reverse. For a real monthly close, a refund from an older order still needs its original channel, even when its posting date belongs to the current month.

Finance should own the definition of net sales. Store and ecommerce managers should approve channel assignments. The customer-data owner should confirm which existing tokens are comparable. Do this before ranking channels.

Build one row per completed order

Start with the order exports and the return register. Keep order ID, order date, originating channel, an approved deidentified customer token, gross merchandise sales, discounts, and linked refunds. Include the source file and source row in your working records so exceptions can be traced.

Roll item lines into orders before counting orders. Retain distinct return IDs while summing refunds against the original order; repeated refund exports must not create repeated deductions. A pickup confirmation or delivery event is not another order.

A fictional US home-goods retailer has these October 2026 orders. Columns A through H follow the table, with headings in row 1 and orders in rows 2 through 7. Discounts and refunds are positive deductions.

Check order uniqueness in a spare column, filling down from row 2:

=COUNTIF($A$2:$A$7,A2)

Every result must be 1 after the item lines have been rolled into orders. Investigate repeated keys before calculating either sales or repeat buying.

Calculate net sales and prove the channel totals

Net sales = Gross merchandise sales − Discounts − Refunds

In Excel, enter this in H2 and fill down:

=E2-F2-G2

For store and online net sales respectively, use:

=SUMIFS($H$2:$H$7,$C$2:$C$7,"Store")
=SUMIFS($H$2:$H$7,$C$2:$C$7,"Online")

Microsoft's SUMIFS documentation describes the matching-criteria sum and requires equally sized sum and criteria ranges.

The check is 290 + 190 = 480, also equal to 500 − 0 − 20. The $20 refund on O103 belongs to Store even if another channel processed it. Keep that handling location available for a separate service-cost review. See credit notes and returns in revenue measures for the broader revenue treatment.

Tie the gross-sales and refund totals separately to their source reports. A correct net total can conceal an omitted sale and an omitted refund of the same amount.

Count repeat shoppers across the combined orders

List each distinct nonblank customer token once in J2:J5. Count its orders in K2, then flag repeat shoppers in L2, filling both formulas down:

=COUNTIF($D$2:$D$7,J2)
=IF(K2>=2,1,0)

Microsoft documents COUNTIF as counting cells that match a criterion. Here the criterion is the token, and the underlying file already has one row per qualifying order.

The repeat purchase rate is:

=SUM(L2:L5)/COUNTA(J2:J5)

That is 2 / 4 = 50.0%, rounded to one decimal place. It describes repeat buying within this window, not lifetime loyalty. Define whether fully refunded orders qualify; this example has no fully refunded orders.

Store has three identified shoppers and Online has three, but the business has four. C01 and C02 appear in both channels, so adding channel customer counts would double count them. For basket spending, use the separate average order value measure; shopper analytics covers basket composition.

Where it goes wrong

• Different order identifiers. A receipt number can repeat across stores. Use the source's complete order key, including location where needed.
• Refunds without original orders. Report unmatched refunds as exceptions rather than guessing a channel.
• Blank customer tokens. Keep their sales in the reconciliation, but exclude them from identified-customer rates and disclose the coverage gap.
• Unequal observation windows. A new channel has had less time to show repeats. Compare equivalent periods before explaining a difference.

Covirage is not affiliated with Microsoft.

Reconcile your own channel exports in Covirage

Bring a sample of your own order and refund files with the channel rules and existing identifiers. Covirage maps the columns, and its tools compute and check the channel totals and repeat-purchase measures; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and set up your business to start with your own files.

See revenue driver analysis for the supporting commercial review.