Amazon marketing analytics: reconcile sales and spend

Amazon marketing analytics compares advertising spend with sales and the contribution left after refunds, product costs and seller charges. Start with a defined sales population, then reconcile the exports before deciding which campaign deserves more budget. Advertising-attributed revenue and recorded seller revenue need separate labels because they answer different questions.

Start with a sales population you can defend

Save the campaign spend export, order-line export, refund adjustments, seller charges and product-cost file. Record the marketplace, currency, reporting dates and extraction cutoff. Use USD throughout this example; exclude sales tax collected for others from the merchandise-sales base.

The campaign report tells you what advertising receives credit for. The seller records tell you what was sold, refunded and charged. Amazon Ads defines advertising cost of sales and ROAS using advertising spend and advertising revenue. Those ratios do not establish the contribution on accounting transactions. The broader distinction belongs in marketing ROI versus ROAS.

Do not assume a campaign report contains order identifiers. Use an existing, documented mapping when available. Otherwise, compare campaign attribution separately and calculate contribution at a defensible SKU-period level. An allocation across campaigns is an allocation, even when its totals reconcile.

For the worked example, an illustrative US home-goods seller supplies a mutually exclusive mapping of product sales groups to campaigns. Each group appears once. The mapping organizes the comparison; it does not prove advertising caused the purchases.

Build the sales and cost table

The seller compares an order cohort from September 1 through September 30, 2026, with known adjustments through October 23, 2026. The following grouped rows summarize the included order lines. Refunds and costs are positive deductions; fees are net of any fee credits. Product cost already reflects the seller's treatment of returned inventory.

In a worksheet named Lines, put these inputs in columns A through G:

Use actual posted charges instead of applying a blanket fee percentage. Amazon's selling-fee guide distinguishes referral fees from selling-plan charges and explains that referral fees vary by category. Keep fixed selling-plan costs outside this variable contribution comparison, or show a separate, clearly labeled overhead allocation.

Add net sales in H and contribution before advertising in I. Enter these Excel formulas in row 2 and fill through row 5:

H2 = C2-D2
I2 = H2-SUM(E2:G2)

The first Kitchen row therefore leaves $5,700 of net sales and $2,100 before advertising. The next section groups those calculated rows without repeating any charge.

Add spend once per campaign

The campaign export records $2,000 of advertising spend for Kitchen and $2,000 for Storage for the same comparison period. Keep this spend in a separate campaign table. Copying the full campaign expense onto every SKU row would duplicate it.

Create a summary worksheet with the layout below. Percentages are rounded to one decimal place; sales multiples are rounded to two decimals. Calculations retain unrounded values.

With campaign names in column A and spend entered in D, use:

B2 = SUMIFS(Lines!$H$2:$H$5,Lines!$A$2:$A$5,A2)
C2 = SUMIFS(Lines!$I$2:$I$5,Lines!$A$2:$A$5,A2)
E2 = C2-D2
F2 = IF(B2=0,"",E2/B2)
G2 = IF(D2=0,"",B2/D2)

Fill through the Storage row. Microsoft documents SUMIFS as adding values that meet specified criteria. Here, campaign is the criterion, and the summed ranges contain already reconciled net sales and costs.

The last column is a matched-net-sales multiple, not native advertising ROAS. Both campaigns have gross merchandise sales of $10,000 against $2,000 of spend, a gross-sales multiple of 5.00. Their refunds and variable costs differ enough to leave opposite contribution results. For the distinctions between cost layers, use gross margin, contribution margin and net margin.

Prove the total before changing the budget

The source bridge must close:

Gross sales = Refunds + Seller fees + Fulfillment + Product cost + Advertising + After-ad contribution.

Here, $20,000 = $2,000 + $3,000 + $2,500 + $7,500 + $4,000 + $1,000. Net sales also reconcile independently: $20,000 less $2,000 equals $18,000.

On the summary sheet, the total margin is =SUM(E2:E3)/SUM(B2:B3), giving 5.6%. Compare each input-column total with its filtered source export. Retain an exclusion list for unrelated sales, non-order charges and adjustments outside the cutoff. Do not compare the included merchandise subtotal directly with a bank deposit; settlement timing and other charges can differ.

If an additional $500 refund belongs to this cohort, with other costs unchanged, net sales fall to $17,500 and contribution falls to $500. The revised margin is $500 / $17,500 = 2.9%. Refresh the cohort through the same adjustment age before treating its result as final.

Use contribution to choose the next investigation

Storage needs a review of returns, product economics and fulfillment costs before a budget increase. Kitchen leaves positive contribution under the stated cost scope, but that alone does not establish that additional spend will produce the same result. Check inventory availability and whether campaign assignments remain comparable.

Keep new-customer measurement separate: customer acquisition cost needs an identified new-customer denominator, which this sales table does not provide. If the business also buys app traffic, mobile marketing analytics compares acquisition cohorts rather than seller SKU economics.

Where it goes wrong

• An unsupported match. Similar dates or product names do not establish that an order belongs to a campaign. Label SKU-level allocations and retain unmatched amounts.
• Repeated costs. A fulfillment charge included in the seller-fee field must not be deducted again in the fulfillment column.
• Unequal refund maturity. A recently closed campaign has had less time to accumulate returns. Fix the cutoff and disclose pending adjustments.
• Changing the denominator. Attributed sales, gross merchandise sales and net sales are different bases. Name the one used for every ratio.
• Contribution called profit. Corporate overhead, taxes and other excluded expenses still exist. Keep the measure's scope visible beside the result.

Covirage is not affiliated with Amazon or Microsoft.

Reconcile your own seller exports in Covirage

Bring a sample of your own seller exports and campaign-spend file to Covirage, with the mapping and reporting window documented. Covirage maps the columns, its tools compute and check the contribution comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account; account creation takes about a minute, after which you set up your business and bring your data.

See customer profitability analysis for the supporting business context.