Direct marketing analytics: response, conversion and cost

Direct marketing analytics compares a defined group of contacted recipients with their responses, orders, and campaign cost. Use a consistent response window and count people separately from repeated activities. Then check the recipient, order, and spending totals before deciding which campaign deserves another test.

Define a contact and an outcome

Start with the campaign's recipient export, not a report of everybody who responded. Otherwise, people who did nothing disappear from the denominator. Retain recipient ID, campaign ID, contact date, and delivery or mailing status.

Choose what qualifies as a response: a recorded inquiry, reply, or identified landing-page submission, for example. A sale can be tracked separately even when no earlier response was recorded. Document those definitions before comparing campaigns, and preserve unmatched responses for review.

This example compares mailed letters with postcards for a US commercial maintenance supplier. Recipients appear in only one cohort, and the denominator is distinct mailed recipients, including any later undeliverable pieces. Outcomes cover 30 calendar dates beginning with the contact date. Both cohorts have completed that window.

The example counts the first qualifying, noncanceled order per recipient. Additional orders belong in a separate repeat-purchase measure. Its sales amounts are net of recorded returns and discounts on those first orders, measured at the same reporting cutoff.

Prepare recipient rows before calculating rates

Use one row per recipient in the analysis sheet. The starting layout is:

Use actual spreadsheet dates, including timestamps when supplied. For row 2, the response flag and order flag are:

=IF(D2="",0,--AND(D2>=C2,D2<C2+30))
=IF(E2="",0,--AND(E2>=C2,E2<C2+30))

The lower bound includes the contact date; the upper bound excludes the beginning of the next window. Fill down, then filter unexpected sequences such as an order dated before contact. Do not quietly change the date to make a record fit.

If the order export has several line items per order, total those items once before bringing the amount into the recipient sheet. Keep order ID in the supporting file so the sales total remains traceable. Separate unmatched orders from confirmed recipients with no order.

Summarize the cohorts and campaign cost

Suppose recipient rows occupy 2:2501. Put campaign names in J2:J3. Count recipients, responses, and first orders in K2:M2:

=COUNTIFS($B$2:$B$2501,J2)
=SUMIFS($G$2:$G$2501,$B$2:$B$2501,J2)
=SUMIFS($H$2:$H$2501,$B$2:$B$2501,J2)

The Google Sheets COUNTIFS reference documents counting records against criteria. The SUMIFS reference documents summing values that meet all supplied criteria; the ranges must correspond to the same recipient rows. Covirage is not affiliated with Google.

Sum qualifying first-order sales in N2:

=SUMIFS($F$2:$F$2501,$B$2:$B$2501,J2,$H$2:$H$2501,1)

Bring campaign cost into O2 from the reconciled spending schedule. Include list rental, printing, postage, and campaign-specific production under the same policy for both cohorts. Keep the schedule separate from recipient rows so joining recipients does not multiply a campaign invoice.

For this example, every first-order recipient is also a recorded responder. That relationship is an observed property of these rows, rather than an assumption to impose on every campaign.

Work the rates and prove the comparison

Response rate is responders divided by recipients. First-order conversion is first orders divided by recipients. Cost per response and cost per first order divide campaign cost by the corresponding outcome count.

For the first summary row, use:

=IF(K2=0,"",L2/K2)
=IF(K2=0,"",M2/K2)
=IF(L2=0,"",O2/L2)
=IF(M2=0,"",O2/M2)

Percentages and unit costs are rounded to two decimal places. The letter response rate is 80 / 1,000 = 8.00%; postcard cost per response is 4,500 / 90 = $50.00. Combined cost per order is 9,500 / 38 = $250.00.

The recipient counts must reconcile to the mailing file, the 38 first orders to distinct qualifying order IDs, and $17,200 to their net amounts. The $9,500 must reconcile to the spending schedule. Compute combined rates from summed counts, rather than averaging the campaign percentages.

Postcards produced cheaper responses, but both campaigns cost the same per first order. Investigate order margin before shifting budget. Marketing ROI versus ROAS explains the margin test; direct marketing ROI covers the return calculation. Neither replaces this recipient reconciliation.

Where it goes wrong

• Counting activities as people. Several replies from one recipient still represent one responding recipient.
• Mixing denominator policies. Mailed contacts and delivered contacts answer different questions. Label and retain the chosen basis.
• Giving cohorts different time. Incomplete windows make recent campaigns look weaker. Compare only mature windows or label provisional results.
• Confusing orders with new customers. Existing customers can place qualifying orders. Customer acquisition cost requires a separate new-customer definition.
• Calling observed orders incremental lift. Some recipients would have purchased anyway. A credible control is needed to estimate the additional effect; B2B marketing analytics gives the wider commercial context.

Do this on your own file in Covirage

Bring the recipient export, matched response and order records, and campaign spending schedule. Covirage maps the columns; its tools compute and check cohort comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.

See sales insights for the supporting sales analysis context.