Most ad-scaling mistakes are not bidding mistakes. They happen because three different datasets are treated as if they describe the same unit of profit:
- an order that has been placed;
- a settlement that has not finished;
- a refund that may arrive after the reporting period has closed.
For a single product, those events can produce very different conclusions. A campaign can look profitable in yesterday's order report, look weaker in today's settlement report, and fall below break-even once late refunds and creator commissions are reconciled.
The fix is not a more complicated dashboard. It is a stable SKU-level contribution-margin model with an explicit as-of timestamp.
Define the three numbers first
Before writing SQL or adding another chart, separate these concepts:
| Metric | What it measures | Typical mistake |
|---|---|---|
| GMV or net merchandise sales | Demand and order value | Treating sales as cash |
| Settled proceeds | Cash available or scheduled | Treating unsettled proceeds as usable cash |
| Contribution profit | What remains after variable operating costs | Applying a fixed margin percentage |
For a seller, a useful contribution-profit definition is:
Contribution profit
= net merchandise sales
- product cost
- outbound fulfilment
- platform and payment fees
- affiliate or creator commission
- ads allocated to the SKU
- expected refunds and return costs
This is deliberately a decision metric, not an accounting profit metric. Its purpose is to answer a narrower question: should this SKU receive another unit of ad budget?
Use an event timeline, not one flat order row
The first structural improvement is to keep order, settlement, refund, and cost events separate. A flattened table encourages teams to overwrite a value “when the number becomes more accurate.” That destroys the audit trail and makes late adjustments impossible to explain.
For example:
| Timestamp | Event | Financial effect |
|---|---|---|
| 2026-11-01 10:00 | Order created | Recognize a sale candidate |
| 2026-11-02 18:00 | Payment settled | Recognize proceeds and fees |
| 2026-11-08 09:00 | Creator commission finalized | Adjust acquisition cost |
| 2026-11-17 14:00 | Refund approved | Reverse sale and recoverable fees |
| 2026-11-20 11:00 | Refund paid | Reduce available cash |
A daily report should be a snapshot “as of” a timestamp. It should not silently replace prior facts. When the same day is recalculated later, the system can append a revision and preserve the reason.
A practical pseudocode model
The exact schema depends on the source system, but the calculation boundary can stay consistent:
with orders as (
select
order_id,
sku_id,
store_id,
created_at,
net_merchandise_sales
from order_events
where created_at >= :period_start
and created_at < :period_end
),
settlements as (
select
order_id,
sum(settled_amount) as settled_amount,
sum(platform_fee) as platform_fee,
sum(affiliate_commission) as affiliate_commission
from settlement_events
group by order_id
),
refunds as (
select
order_id,
sum(refund_amount) as refund_amount,
sum(return_cost) as return_cost
from refund_events
group by order_id
),
ads as (
select
sku_id,
sum(allocated_spend) as allocated_spend
from ad_allocations
where event_date >= :period_start
and event_date < :period_end
group by sku_id
)
select
o.store_id,
o.sku_id,
sum(o.net_merchandise_sales) as net_sales,
sum(coalesce(r.refund_amount, 0)) as refunds,
sum(coalesce(s.platform_fee, 0)) as platform_fees,
sum(coalesce(s.affiliate_commission, 0)) as affiliate_commission,
sum(coalesce(r.return_cost, 0)) as return_cost,
sum(coalesce(a.allocated_spend, 0)) as ad_spend
from orders o
left join settlements s using (order_id)
left join refunds r using (order_id)
left join ads a using (sku_id)
group by o.store_id, o.sku_id;
The final margin calculation should be implemented as one reviewed function, not copied into several dashboards:
pre_ad_margin = net_sales - product_cost - fulfilment - fees - affiliate - refunds
contribution_margin_pct = pre_ad_margin / net_sales
break_even_roas = 1 / contribution_margin_pct
contribution_profit = pre_ad_margin - ad_spend
Guard against division by zero and clearly label negative or not-yet-settled rows. A useful report shows both the raw value and the coverage state.
Handle ad allocation explicitly
Platform-level ad reports rarely map perfectly to SKU-level profit. A campaign may contain several products, and a product may receive traffic from several campaigns.
Choose one policy and document it:
- Direct allocation when the campaign contains one SKU.
- Revenue-weighted allocation for mixed campaigns.
- Contribution-weighted allocation when products have very different margins.
- Unallocated bucket when evidence is insufficient.
Do not hide an unallocated amount in a catch-all SKU. It should be visible, reviewed, and assigned by an accountable operator.
The same principle applies to free samples, creator commissions, and content production. If these costs influence the ad decision, give them a home in the model.
Reconcile four totals before trusting the chart
A SKU table is only useful if it can be reconciled back to source systems. A compact daily or weekly check can catch most silent failures:
| Check | Expected relationship |
|---|---|
| Orders report to event table | Row count and sales total within documented tolerance |
| Settlements to order IDs | Every settled amount maps to one order or an explainable adjustment |
| Refund events to original order | No orphan refunds; late refunds remain in the correct cohort |
| Ad spend to platform invoice | Allocated plus unallocated spend equals the invoice total |
Store the timestamp and source file or API batch with each check. “The dashboard is right” is not evidence; a reproducible reconciliation is.
A worked example
Suppose one SKU records net sales of 10,000 in a seven-day cohort.
- Product cost: 3,200
- Fulfilment: 800
- Platform and payment fees: 600
- Affiliate commission: 900
- Expected refunds and returns: 500
- Ads allocated to the SKU: 3,000
Pre-ad margin is 4,000, or 40% of net sales. Break-even ROAS is therefore 2.5. Actual ROAS is 3.33, and contribution profit is 1,000.
This does not automatically mean the SKU should scale. Check whether the refund reserve is complete, whether the settlement has finished, and whether the next tranche of traffic is likely to preserve the same marginal contribution. The threshold is a guardrail, not a promise.
The operating rule
Use a statement that makes time and cost treatment explicit:
For each SKU, calculate contribution profit for orders created in the selected cohort, using all costs and refunds known as of the report timestamp, and disclose any settlement or refund coverage gap.
That sentence prevents most false precision. It also makes two teams less likely to argue about different numbers that are both technically correct under different definitions.
For a quick manual check, this free no-login profit calculator follows the same cost categories:
The broader worked guide is here:
The goal is not to create a perfect accounting system inside an ad dashboard. It is to make the ad-scaling decision reproducible, explainable, and honest about what has not settled yet.
Disclosure: this article is published by Starway Global and links to our free seller tools and guide. No paid placement is included.
Top comments (0)