Measure campaign profitability after refunds and discounts

Gross sales divided by advertising spend can make a campaign look attractive even when refunds and fulfillment costs leave little contribution. Build a transparent value bridge before comparing campaigns, and give every cohort the same outcome horizon.

The resulting measure should be named precisely. Contribution after selected acquisition spending is not necessarily full accounting profit, lifetime value or the incremental return caused by advertising.

Define the cohort and cost scope

The original synthetic campaign orders belong to December 2025 acquisition cohorts. Their thirty-day refund outcomes are complete by January 31, 2026 and frozen at that horizon. The campaign spend is assigned to those same cohorts.

All amounts are paise. The example includes gross merchandise value, discounts, refunds, fulfillment cost, documented cost recovered from returns, payment fees and campaign acquisition spend. It excludes fixed overhead, taxes and later customer activity.

For each order, calculate:

gross − discount − refund − fulfillment cost + recovered cost − payment fee.

Recovered cost is a cost adjustment supported by the return outcome. It is not the cash refund and should not automatically equal the original fulfillment cost.

Aggregate order contribution before joining campaign spend

WITH orders AS (
 SELECT campaign,cohort,
 SUM(gross_paise) AS gross_paise,
 SUM(gross_paise-discount_paise-refund_paise-fulfilment_cost_paise
     +recovered_cost_paise-payment_fee_paise) AS contribution_paise
 FROM campaign_orders GROUP BY campaign,cohort
)
SELECT o.campaign,o.cohort,o.gross_paise,o.contribution_paise,s.spend_paise,
 o.contribution_paise-s.spend_paise AS contribution_after_spend_paise
FROM orders o JOIN campaign_spend s USING(campaign,cohort)
ORDER BY o.campaign;

Campaign A generates 6,960 paise of order contribution and spends 5,000, leaving 1,960. Campaign B generates 1,700 and spends 3,000, leaving negative 1,300.

The spend is subtracted once per campaign and cohort. Joining one spend row to every order and then summing it would charge campaign A's spending twice in this fixture.

Check the result independently

from collections import defaultdict
from datetime import date, timedelta
from build_and_verify import database

db = database()
totals = defaultdict(lambda: {'gross':0,'contribution':0})
for row in db.execute('SELECT * FROM campaign_orders'):
    order,campaign,cohort,ordered,gross,discount,refund,cost,recovered,fee = row
    assert date.fromisoformat(ordered)+timedelta(days=30) < date(2026,2,1)
    assert 0 <= discount <= gross and 0 <= refund <= gross-discount
    assert 0 <= recovered <= cost
    totals[(campaign,cohort)]['gross'] += gross
    totals[(campaign,cohort)]['contribution'] += gross-discount-refund-cost+recovered-fee
spend = {(campaign,cohort):amount for campaign,cohort,amount in db.execute('SELECT * FROM campaign_spend')}
assert totals.keys() == spend.keys()
result = {key:{'gross_sales_over_spend':value['gross']/spend[key],
               'contribution_after_spend':value['contribution']-spend[key]}
          for key,value in totals.items()}
db.close()
assert result[('A','2025-12')]['contribution_after_spend']==1960
assert result[('B','2025-12')]['contribution_after_spend']==-1300
assert result[('A','2025-12')]['gross_sales_over_spend']==3.6
assert result[('B','2025-12')]['gross_sales_over_spend']==4
print(result)

B has the higher gross-sales-to-spend ratio, 4.0 versus A's 3.6, but the lower contribution after included costs. Ranking only by gross sales would miss the refund and cost structure.

Match the observation window to the claim

A recent cohort with only two days of refund exposure is not comparable with one observed for thirty days. Either wait for a common horizon or label an explicit estimate for immature outcomes. Do not treat unobserved future refunds as observed zeros.

This fixture freezes thirty-day outcomes. Later refunds or repeat purchases can change a longer-horizon result, so the article's measure should not be called final lifetime profitability.

Payment fees may also behave differently after refunds depending on the provider and contract. Use actual fee records or a documented assumption rather than automatically reversing every fee when an order is refunded.

Keep attribution separate from incrementality

Assigning an order to a campaign does not establish that the campaign caused the order. The attribution-window example shows how credit can move without changing conversions.

A budget recommendation should consider incremental contribution where a suitable causal design can estimate it, along with capacity and uncertainty. The descriptive bridge here identifies recorded economics under the selected allocation; it does not establish the effect of increasing or removing spend.

Exercise: add a campaign with spend but no orders. Replace the inner join with a population that preserves all campaign/cohort spend, and verify that zero observed contribution does not make the campaign disappear. Explain why data completeness must be checked before calling that a true zero.

NeuraPath's Data Analytics with Generative AI course connects marketing analysis with reconciliation. A useful campaign report follows value beyond the headline sale and states exactly which costs and outcomes it includes.

Continue learning

This article is part of the Domain analytics and business cases sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Build a sales pipeline report with stage-history data.
• Continue with Analyze call-centre service levels with abandonment rules.