Build an ad-spend decision engine on warehouse data
Every DTC brand has a marketer staring at three different ROAS numbers before 9am: one from Meta, one from Google, one from the "blended" spreadsheet that never matches either. Nobody trusts any of them enough to actually cut a campaign. So budgets drift on inertia, not evidence. That's the gap a decision engine closes — not another dashboard, but a system that tells you what to do with the next dollar, built entirely on your warehouse data instead of platform self-reporting.
The problem with platform-reported ROAS
Meta and Google grade their own homework. Both platforms take credit for the same conversion through last-click or inflated view-through windows, so add up their reported revenue and you'll often "prove" more sales than your Shopify store actually processed. That's not a rounding error — on accounts running five or six channels, double-counted revenue can inflate blended ROAS by 20-40%.
The fix isn't a better dashboard connector. It's refusing to let ad platforms be the source of truth for revenue at all. Revenue lives in your order data. Spend lives in your ad platform billing data. A decision engine keeps those two facts separate and joins them yourself, on your terms, in your warehouse.
What a decision engine actually does
A dashboard shows you numbers. A decision engine tells you an action: shift budget, pause a campaign, raise a bid cap, hold steady. The difference is a rules layer sitting on top of clean data that translates "CAC is up 18% week over week on channel X" into "cut daily budget on channel X by 15% until payback period drops below 60 days."
This matters because the real bottleneck in most ad accounts isn't insight — teams already sense something is off. It's the lag between noticing and acting, and the lack of confidence to act because the underlying numbers are shaky. A decision engine removes both. It runs on a schedule, checks the same math every time, and only surfaces the calls that need a human.
Lay the data foundation first
None of this works without a warehouse that holds the raw ingredients, modeled consistently. At minimum you need:
- Order-level revenue from your commerce platform, net of refunds and discounts, at the order and line-item grain.
- Ad spend by campaign and day pulled from each platform's cost API — not the revenue or conversion numbers they report, just spend.
- Session and click data with UTM parameters or click IDs, so you can tie a purchase back to the ad that plausibly drove it.
- Cost of goods and shipping so you can convert revenue into contribution margin, not just top-line sales.
- Customer identity stitched across sessions, so repeat purchases and LTV feed back into the model instead of every order looking like a new customer.
Build these as dbt models with clear grain and tested keys — a spend table at the campaign-day level, an orders table at the order level, a bridge table connecting sessions to orders. Skip this step and every decision downstream is built on a guess.
Once that's in place, define one metric everyone agrees to argue about: usually contribution margin per dollar of spend, or a blended MER (marketing efficiency ratio — total revenue divided by total spend) calculated from warehouse numbers, not platform dashboards. Pick your attribution approach deliberately — full last-touch, a data-driven model, or a simple time-decay window — and apply it consistently across every channel so campaigns are compared on the same terms.
Build the decision logic layer
With trustworthy numbers flowing daily, the next layer is rules that turn thresholds into actions. This is where most teams either over-engineer with a black-box ML model nobody trusts, or under-engineer with nothing beyond a static spreadsheet. The middle ground works best:
- Set guardrails by channel and campaign type — a maximum acceptable CAC, a minimum contribution margin, a target payback period.
- Compare rolling windows, not single days. A 7-day trailing CAC against a 28-day baseline filters out noise from one bad Tuesday.
- Weight decisions by spend level. A campaign burning $50/day breaching a threshold is not the same emergency as one burning $5,000/day.
- Separate acquisition and retention spend in the logic, since a new-customer campaign and a retargeting campaign should never share the same CAC ceiling.
Write these rules in SQL or a lightweight Python job that reads from your warehouse tables and outputs a ranked list: which campaigns are outperforming their guardrail, which are underperforming, and by how much. That output is the actual product — a ranked action list, refreshed daily, not a chart.
Layer in incrementality where you can. Blended metrics tell you what happened; they don't tell you what would have happened without the spend. Even simple geo holdouts or brand-search suppression tests, logged back into the warehouse, sharpen the model over time and stop it from over-crediting channels that were riding organic demand anyway.
Automate the action, not just alerts
A list of flagged campaigns that a human reviews every morning is a good v1. But the real value shows up when the engine closes the loop. That can mean:
- Slack or email alerts with the specific recommendation and the number behind it — not "check this campaign" but "CAC on Campaign B hit $62 against a $45 ceiling, recommend cutting budget 20%."
- API calls to the ad platforms that execute budget changes automatically within pre-approved bands, with humans only stepping in for moves above a certain dollar threshold.
- A weekly reallocation model that shifts a fixed pool of budget from underperforming to overperforming campaigns based on marginal CAC, not gut feel.
Start manual, log every recommendation and whether a human overrode it, and use that log to tune the rules. Full automation only earns trust after the engine has been right enough times that overriding it feels like the risky move.
The brands that win on paid media in the next few years won't be the ones with the biggest budgets or the fanciest attribution vendor. They'll be the ones who stopped trusting platform dashboards, built a warehouse that tells the truth about revenue and spend, and turned that truth into a system that acts on it daily instead of debating it monthly. That's not a marketing project — it's a data engineering one, and it pays for itself the first month it stops a losing campaign from running for three more weeks.