A warehouse-native cost model that loads every order with its real variable costs, so CAC, payback, and contribution margin are finally true by channel, campaign, and cohort.
The client is a DTC brand scaling paid acquisition across Meta, Google, and TikTok. Leadership steered by blended ROAS and platform-reported numbers, which ignore cost of goods, fulfillment, payment fees, and returns, and count the same conversion more than once. Nobody could say which channels were actually profitable. With investors now pushing on profit, not just growth, the guessing had to stop.
01 The Challenge
Platform-reported ROAS counts the same purchase across Meta, Google, and TikTok, and it ignores the cost of goods, the 3PL pick-and-pack, the shipping, the payment processor fees, and the returns. Finance ran a monthly contribution-margin spreadsheet that was stale before it was finished and never matched what Growth saw in the ad managers.
The trigger: the board wanted a credible path to profitability, and the team was about to push more budget into channels without knowing which ones paid back.
You can't scale what you can't cost.
02 The Approach
The governing rule: every dollar of revenue carries its true variable costs (COGS, fulfillment, shipping, payment fees, returns, and reconciled ad spend) before any margin is computed. Everything is modeled at the order, SKU, channel, and cohort grain, so any slice is one query away and every chart agrees.
A core decision was to reconcile platform-reported spend against billed spend, because the platforms over- and under-report, and CAC is a lie until that gap is closed. The margin, CAC, and payback definitions live once in the warehouse, owned and testable, instead of in per-platform dashboards and spreadsheets that never reconcile.
What we deliberately did not do: this is not a multi-touch attribution or identity resolution build (that is a separate engagement). We kept it to a trustworthy, reconciled margin model the finance team can stand behind, and we did not replace their BI.
03 The Build
Python connectors pull spend and campaign metadata from the Google Ads, Meta Marketing, and TikTok Marketing APIs; orders, refunds, and discounts from Shopify; landed COGS from NetSuite; carrier and 3PL fees; and processor fees from Stripe and Shopify Payments. Raw data lands in BigQuery. dbt builds the costed model: order-level contribution margin, and channel-level CAC and payback on reconciled spend, sliceable by day, channel, campaign, SKU, and acquisition cohort, with a single metrics definition so every number ties out. Dagster orchestrates the daily run, and dbt tests guard data quality and the spend reconciliation. Lightdash serves the finance cockpit on top of the model.
Connectors run as Cloud Run jobs and the whole pipeline is defined in Terraform on GCP, so the daily refresh is reproducible and the spend reconciliation is guarded by tests.
04 The Results
Contribution margin is now costed to every order and refreshed daily instead of monthly. Reconciliation surfaced a real gap between platform-reported and billed spend, so CAC and payback are finally accurate. With true payback by channel, the team moved budget off channels that looked fine on ROAS but lost money after costs, lifting blended contribution margin by about 6 points. Finance and Growth now argue from one number, and month-end margin reporting is a dashboard rather than a fire drill.
05 What's Next
The reconciled, costed model is the foundation for the rest of the brand's revenue analytics. It is the same spend, revenue, and cost spine a multi-touch attribution build would feed into, and the same margin definitions that pricing and inventory decisions can reuse. The hard part, trustworthy costed data, is done once.
One idempotent pipeline that turns retail purchase orders into shipped, invoiced, reconciled revenue across multiple 3PLs, with humans touching only the exceptions.
A governed AI agent that resolves the common tickets end to end using facts pulled from Shopify, takes only the actions your policy allows, escalates everything else, and states zero facts it did not verify in code.
Numbers you can trust, operations that run themselves, all built into your stack and owned by you. Tell us where you want the edge, and we'll build it.
You'll hear from an engineer the same working day, not a sales sequence.
A short call, then a plan, and we start with Proof Week or a full build.
A fixed fee agreed before anything starts, for Proof Week or the full build.