Direct marketing ROI: match responses to contribution

Direct marketing ROI compares the contribution from matched purchases with the cost of the campaign that generated the recorded responses. Match response logs to order and finance exports, subtract refunds and variable order costs, then deduct campaign spending. Label the result as attributed ROI unless a separate controlled comparison establishes incremental sales.

Fix the response and purchase rules first

A fictional US business supplies office products to company customers. It compares an email offer and a mailed offer sent during July 2026, recording qualifying responses through July 31 and purchases through September 30, 2026. Eligible purchases occur on or after the customer's qualifying response and through the purchase cutoff. Both campaigns receive the same observation window, and their customer audiences do not overlap in this example, so responder counts add across campaigns.

The response rule is a verified reply or offer-code redemption. The matching hierarchy uses a campaign-specific offer code first, then an existing response reference, then a reviewed customer identifier within the purchase window. A matching rule should be reproducible without guessing from customer names.

Keep one response row per customer and campaign for the unique-responder calculation. Preserve repeated interactions separately. Give each order one primary campaign assignment for this additive report, and keep ambiguous or unmatched orders outside the attributed result until reviewed.

For the general distinction between revenue return and profit return, see marketing ROI versus ROAS. Delivery and address controls for physical campaigns belong in direct-mail ROI.

Build the matched purchase file

Use these workbook tabs, filtered to the agreed windows before calculating:

Finance should confirm that refunds and product cost belong to the matched orders. The IRS explanation of gross profit starts with net receipts after returns and allowances and subtracts cost of goods sold. The calculation here also subtracts the agreed variable fulfillment costs to reach contribution before campaign spending.

On Orders, put the listed fields in A through F. Contribution in G2 is:

=C2-D2-E2-F2

Keep shipping or handling cost in only one cost column. A product cost export that already includes fulfillment must be adjusted before the separate fulfillment subtraction. Leave general overhead outside this contribution definition and label the resulting scope clearly.

The response register and matched order export produce these campaign totals. Refunds are shown as positive deductions. Dollar amounts are whole USD.

The email offer's contribution is $24,000 − $2,000 − $11,000 − $1,000 = $10,000. Total net revenue is $45,000 − $3,000 = $42,000, and total contribution is $42,000 − $21,000 − $2,000 = $19,000. A purchasing responder can place multiple orders; count that customer once in the responder columns while retaining every distinct order amount.

Include the campaign costs once

Campaign costs include the agreed expenses required to execute the offer, even when they were invoiced separately. Allocate shared creative or labor under a rule set before comparing returns. Keep the allocation visible so finance can reproduce it.

In Costs, campaign is A and the cost components occupy B through E. Enter =SUM(B2:E2) in F2. The costs are illustrative allocations from this business's own cost schedule, not market prices.

If campaign labor also appears in a general acquisition cost review, explain the overlap rather than summing both measures. Customer acquisition cost has a different denominator and can include a broader selling cost scope.

Calculate and prove ROI

Attributed direct marketing ROI = (Matched contribution − Campaign cost) / Campaign cost

Build Summary columns A through E for campaign, matched contribution, campaign cost, contribution after campaign cost, and ROI. Enter these formulas in row 2:

=SUMIF(Orders!$B$2:$B$10000,A2,Orders!$G$2:$G$10000)
=SUMIF(Costs!$A$2:$A$10000,A2,Costs!$F$2:$F$10000)
=B2-C2
=IF(C2=0,"",D2/C2)

Extend ranges to cover the full files. For a report that also filters a business unit or period, Google Sheets SUMIFS supports multiple criteria. A zero campaign cost leaves ROI blank and requires review; it does not establish an unlimited return.

Total ROI is ($19,000 − $10,000) / $10,000 = 90.0%, rounded to one decimal place. The proof is that contribution after campaign cost plus campaign cost returns matched contribution: $9,000 + $10,000 = $19,000. Divide the summed amounts, rather than averaging the campaign ROI percentages.

Matched and unmatched orders must together reconcile to the scoped order extract. Matched net revenue, refunds, and costs must tie to their finance records, and the campaign cost schedule must tie to the spending ledger. Keep any difference visible before discussing budget changes; improving marketing ROI starts with a dependable baseline.

Where it goes wrong

• Treating every response as a sale. Replies establish engagement; purchases require an order match.
• Crediting the same order twice. Competing offer codes or customer matches need one documented assignment or an explicitly nonadditive report.
• Ignoring later refunds. A campaign can look profitable before the return window has matured.
• Leaving out execution costs. Creative, list, distribution, and agreed labor costs belong in the campaign cost schedule.
• Calling matched purchases incremental. Existing demand may explain part of the result. A separate randomized holdout can test lift; NIST explains random assignment in controlled experimental design. It still requires an appropriate outcome definition and uncertainty assessment.

Measure your campaign cohorts in Covirage

Bring a sample of your own response log, matched order export, and campaign cost file to Covirage. Covirage maps the columns, and its tools compute and check the contribution comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring your files.

See revenue driver analysis for the supporting business context. Covirage is not affiliated with Google Sheets.