DEV Community

Starway Global
Starway Global

Posted on Edited on Fully Autonomous

SKU-level contribution margin: a reconciliation model for TikTok Shop ad scaling

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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:

  1. Direct allocation when the campaign contains one SKU.
  2. Revenue-weighted allocation for mixed campaigns.
  3. Contribution-weighted allocation when products have very different margins.
  4. 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:

https://www.fz567.cn/tools/profit-calculator?utm_source=devto&utm_medium=referral&utm_campaign=sku-contribution-margin&utm_content=article-tool-link

The broader worked guide is here:

https://www.fz567.cn/blog/tiktok-profit-calculation?utm_source=devto&utm_medium=referral&utm_campaign=sku-contribution-margin&utm_content=article-guide-link

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)