Retail marketing ROI: returns, discounts and margin

Retail marketing ROI compares the contribution retained from campaign-linked orders with the cost of the campaign. Deduct discounts, refunds, product cost and agreed variable fulfillment costs before calculating the return. A store with more campaign sales can still produce a lower return when its promotion gives away more margin.

Start with the marketing ROI versus ROAS formulas for the general distinction. The retail job is to make the sales and cost inputs trustworthy across stores, online orders and later returns.

Set the campaign boundary before combining exports

Choose a campaign cohort and an observation cutoff. In this example, a fictional Ohio retailer reviews orders placed from July 1 through July 31, 2026, including refunds recorded against those orders through October 23, 2026. This is a cohort analysis rather than a July income statement: later refunds are connected to their original sales.

Use the same campaign identifier in the spend register and the order assignment file. Write down how a sale qualifies: a supplied promotion code, a recorded campaign interaction within the approved window, or an allocation already approved by the business. Preserve orders without a match as unmatched. A missing code is not permission to assign a sale to the largest campaign.

The export should contain order ID, original order date, store or channel, campaign credit, gross merchandise value, discounts, refunds and product cost. Add payment and fulfillment costs from the corresponding cost export. Keep taxes collected for government agencies outside the merchandise sales comparison.

Use one reporting location for each order. If a customer purchases online and picks up in Columbus, decide which location owns that order in this analysis. A separate operational pickup report can use another view, but adding the two views together would count the same receipt twice. The marketing attribution rules explain shared credit; this calculation starts after those rules have been selected.

Build the store comparison in a spreadsheet

The input table below represents deduplicated campaign orders aggregated by reporting location. Refunds are recorded after discounts, so they reverse the amount actually charged. Product cost is net of inventory value recovered from returned goods; fulfillment includes the agreed variable costs that remain after those returns. Marketing is allocated once from the campaign cost register.

For the net sales boundary, the IRS discussion of sales returns and allowances explains that customer credits for returned merchandise and other sales allowances reduce gross sales. This example is a management comparison, so finance should also reconcile its sales definition with the company's reporting policy. Follow credit notes and returns when adjustments appear in a later reporting period.

Place the table headers in row 1 and the three location rows in rows 2 through 4. Columns A through G correspond to the input columns above. Add H for net sales, I for contribution before marketing, J for contribution after marketing and K for ROI:

H2 = B2-C2-D2
I2 = H2-E2-F2
J2 = I2-G2
K2 = IFERROR(J2/G2,"")

Fill down through row 4 and format K as a percentage. Microsoft documents IFERROR as returning the specified value when a formula produces an error. Here the blank prevents a zero spend denominator from becoming a displayed return; review missing and invalid inputs separately so blanks do not conceal data problems.

Percentages are rounded to one decimal place; calculations use unrounded dollars. Dayton contributes more after marketing than Columbus despite having lower net sales. That is a reason to inspect promotion mix, costs and customer behavior before allocating the next budget, rather than automatically scaling the highest-sales location.

Prove the totals against the source files

The revenue bridge is $160,000 minus $16,000 minus $23,000, which equals $121,000. Deducting $70,000 of product cost and $8,000 of fulfillment leaves $43,000. Marketing of $25,000 leaves $18,000, and $18,000 divided by $25,000 gives 72.0%.

The total ROI cell is therefore:

=SUM(J2:J4)/SUM(G2:G4)

Do not average the location percentages. Tie each input total to its source report, including the refunds matched back to the cohort. Keep a reconciliation of all retailer orders split between campaign-assigned and unassigned orders so the campaign report does not quietly become the whole company's revenue.

For a larger prepared export, Microsoft's SUMIFS function adds amounts meeting multiple conditions. If a sheet named Orders has campaign in A, reporting store in B and net sales in C, sum an already reconciled campaign and store combination with:

=SUMIFS(Orders!C:C,Orders!A:A,$M$1,Orders!B:B,A2)

M1 holds the selected campaign. This formula groups supplied assignments; it does not resolve overlapping claims or create missing campaign identifiers. Covirage is not affiliated with Microsoft.

Test how later refunds change the answer

Suppose an additional $5,000 of refunds arrives for the same orders. The retailer recovers $2,000 of saleable inventory cost, while fulfillment and marketing costs remain unchanged. The updated bridge is:

Updated ROI is $15,000 divided by $25,000, or 60.0%. Keep both cutoffs visible. Comparing a recently completed campaign with one whose returns have fully arrived rewards the less mature campaign for incomplete information. Use the next marketing ROI improvement review to investigate the underlying driver rather than changing the attribution rule to improve the percentage.

Where it goes wrong

• Discounts appear twice. An export already containing net merchandise sales should not have promotion discounts subtracted again. Keep a documented gross-to-net bridge.
• A refund reverses all product cost automatically. Damaged or nonreturnable goods may recover no inventory value. Use the actual recovery recorded by finance, and retain return handling costs.
• Shared creative costs repeat across stores. Allocate the campaign total once and prove that store allocations sum to it. State the allocation basis alongside the result.
• Overlapping channels claim one purchase. Resolve order-level duplicates before summarizing. A code redemption and a paid campaign interaction can describe the same transaction.
• Attributed return becomes an expansion promise. These calculations describe assigned historical contribution. They do not establish how much additional demand the campaign caused or what the next budget increase will earn.

Check your retail campaign file in Covirage

Bring a sample of your own order, refund and campaign cost exports, with the credit rules and cost boundary stated. Covirage maps the columns, and its tools compute and check the contribution comparison; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and set up your business to bring your own file.

See customer profitability for the contribution behind campaign revenue.