ARCHITECTURAL PLAYBOOK // QUESTION 01
Why does ad-platform reported ROAS diverge from true CM3 contribution margin—and how do you calculate CM3 in SQL?
Direct Architectural Answer:
Ad-platform ROAS (Meta, Google, TikTok) measures gross top-line revenue on overlapping attribution windows while ignoring promo code stacking, landed unit COGS, oversized freight costs, payment processing fees, and post-purchase refund rates. True Contribution Margin 3 (CM3) deducts every variable product, fulfillment, and marketing cost at the order-line grain—frequently revealing that a campaign with a 4.4x reported ROAS is actually losing -$14.20 in net cash per order.
Production Engineering Rules & Implementation Standards:
- ✓CM1 (Gross Product Margin) = Net Sales (Gross Sales - Discounts - Returns) minus Landed Unit COGS.
- ✓CM2 (Fulfilled Operating Margin) = CM1 minus 3PL Pick/Pack Fees, Actual Shipping Cost Net of Shipping Charged, and Payment Gateway Fees (e.g., Stripe/Shopify Payments/Affirm).
- ✓CM3 (Net Contribution Margin) = CM2 minus Fully Loaded Direct Paid Acquisition Spend attributed to the cohort or campaign.
models/marts/fct_order_cm3_waterfall.sqlsql // Production Pattern
WITH line_item_economics AS (
SELECT
li.order_id,
SUM(li.quantity * li.unit_price) AS gross_item_sales,
SUM(li.quantity * li.unit_cogs) AS total_landed_cogs,
SUM(li.fulfillment_fee) AS total_fulfillment_fees
FROM `dbmarketer_ecommerce_sample.raw_shopify.order_line_items` li
GROUP BY 1
),
refund_deductions AS (
SELECT
r.order_id,
SUM(r.refund_amount) AS refunded_revenue,
COUNT(DISTINCT r.refund_id) AS refund_count
FROM `dbmarketer_ecommerce_sample.raw_shopify.refunds` r
GROUP BY 1
)
SELECT
o.order_id,
o.customer_id,
DATE(o.created_at) AS order_date,
o.Attributed_Campaign_ID AS campaign_id,
-- Top-Line & Net Sales
o.gross_sales,
o.discount_amount,
COALESCE(ref.refunded_revenue, 0) AS refunded_revenue,
(o.gross_sales - o.discount_amount - COALESCE(ref.refunded_revenue, 0)) AS net_sales,
-- CM1: Net Sales minus Landed Unit COGS
(o.gross_sales - o.discount_amount - COALESCE(ref.refunded_revenue, 0))
- li.total_landed_cogs AS cm1_margin_usd,
-- CM2: CM1 minus Fulfillment, Net Shipping Cost & Payment Gateway Fee (2.9% + $0.30)
(o.gross_sales - o.discount_amount - COALESCE(ref.refunded_revenue, 0))
- li.total_landed_cogs
- li.total_fulfillment_fees
- (o.shipping_cost_actual - o.shipping_revenue)
- ((o.gross_sales * 0.029) + 0.30) AS cm2_margin_usd,
-- CM3: CM2 minus Allocated Customer Acquisition Cost (CAC)
((o.gross_sales - o.discount_amount - COALESCE(ref.refunded_revenue, 0))
- li.total_landed_cogs
- li.total_fulfillment_fees
- (o.shipping_cost_actual - o.shipping_revenue)
- ((o.gross_sales * 0.029) + 0.30)
- o.allocated_ad_spend_usd) AS cm3_contribution_margin_usd
FROM `dbmarketer_ecommerce_sample.raw_shopify.orders` o
JOIN line_item_economics li USING (order_id)
LEFT JOIN refund_deductions ref USING (order_id)Order- and SKU-level CM1 → CM2 → CM3 Contribution Margin Waterfall SQL (as deployed in the PeakGear Outdoors diagnostic engine).
Attribution & Profitability Comparison: Vanity Platform ROAS vs. CM3 Warehouse Attribution
| Metric / Scenario | Platform-Reported ROAS View | CM3 Warehouse Attribution View |
|---|---|---|
| PeakGear 'Apex Ultralight Tent' Campaign | Reports 4.4x ROAS — recommends doubling budget | Reveals -$14.20 CM3 loss/order due to 25% promo stacking + 31% zipper-defect returns |
| Klaviyo Welcome Popup Attribution | Klaviyo claims 100% last-click credit on 10%-off popup | Timestamp join proves paid TikTok click occurred 4 minutes prior (Paid nCAC -22% lower) |
| Offline Call Center & B2B Orders | Invisible to browser pixels (0% credit to top-of-funnel search/social) | Invoca / CRM phone & practitioner joins recover 100% of omnichannel LTV:CAC |
| Executive Budget Reallocation | Chases high-return, heavily discounted SKUs that burn cash | Shifts capital to high-CM3 cohorts, driving +20.4% verified ROI lift |