Build an AI agent that reconciles ad spend with margin
Your ad platform says ROAS is 4.2. Your bank account says otherwise. Somewhere between the ad dashboard and your P&L, spend gets disconnected from what you actually keep after COGS, shipping, discounts, and processing fees. Most brands solve this with a spreadsheet someone updates once a week, badly. An AI agent can do it daily, at the SKU and campaign level, without the manual grind. Here's how to actually build one.
Why blended ROAS isn't enough
Ad platforms report revenue, not profit. A campaign can post a 5x ROAS and still lose money if it's driving sales on your lowest-margin SKUs, or if returns and discounts eat the difference. Contribution margin — revenue minus COGS, shipping, payment processing, and variable fulfillment costs — is the number that actually tells you whether a campaign is worth scaling.
The problem is structural, not analytical. Ad spend lives in Meta, Google, TikTok. Order and cost data lives in Shopify, your 3PL, and your accounting system. Nobody has built the plumbing to connect them in near real time. That's the actual job here — not a smarter dashboard, but a working pipeline with a reasoning layer on top of it.
The data foundation before any agent logic
An agent is only as good as the data it can query. Before you write a single line of agent logic, you need a warehouse where these live side by side:
- Ad platform data: spend, impressions, clicks, and reported conversions pulled via API at the campaign and ad-set level, not just account-level totals.
- Order data: line-item detail from Shopify or your OMS, including SKU, discount applied, and refund status — not just order totals.
- Cost data: landed COGS per SKU, shipping cost per order (actual, not estimated), and payment processing fees.
- Attribution signal: UTM parameters, post-purchase survey data, or a multi-touch model — whatever you're using to connect an order back to a campaign.
Get this into a warehouse like BigQuery or Snowflake with dbt models that join order-level data to cost data cleanly. This is unglamorous work and it's 80% of the project. If your join logic is wrong, the agent will confidently reconcile garbage.
What the agent actually does
Once the data foundation exists, the agent's job is narrow and specific: pull daily spend by campaign, pull matched contribution margin by campaign, compute the delta between reported ROAS and true contribution margin ROAS, and flag anything that's drifted outside normal range.
Concretely, that means a scheduled job that:
- Queries yesterday's spend from each ad platform API.
- Queries matched orders and their contribution margin from the warehouse, using your attribution join.
- Calculates contribution margin ROAS per campaign: (revenue - COGS - shipping - fees - discounts) / spend.
- Compares that to platform-reported ROAS and computes the gap in percentage points.
- Checks the gap against a threshold you set — say, anything where true CM-ROAS is more than 30% below reported ROAS gets flagged.
- Writes a summary to Slack or a dashboard, with the specific campaigns and SKUs driving the gap.
Notice this isn't a chatbot you ask questions to. It's a scheduled agent with a defined loop: pull, compute, compare, flag, notify. The "AI" part is in how it summarizes the discrepancy and, if you want to go further, drafts a recommendation — pause this campaign, shift budget from this ad set, this SKU's margin is too thin to support paid acquisition at current CPA.
Where the reasoning layer earns its keep
The math above doesn't need an LLM. A dbt model and a cron job can do arithmetic. Where an agent adds real value is in pattern recognition across noisy, messy signals that don't fit clean rules:
- Root cause summarization: instead of just flagging "campaign X is off by 40%," the agent can query further — is it a discount code being overused, a spike in returns, a shift in product mix toward low-margin bundles — and write that up in plain language.
- Natural language queries: letting a marketer ask "why did contribution margin drop on the retargeting campaign last week" and having the agent trace it through the joined data rather than someone building a new dashboard filter.
- Anomaly triage: when ten things look slightly off, the agent can rank them by dollar impact instead of surfacing every minor variance, which is where alert fatigue kills these systems in month two.
Use a lightweight framework — something that can call your warehouse via SQL, call ad platform APIs, and pass structured output to a model for summarization. You don't need a complex multi-agent orchestration system for this. One agent with three tools (warehouse query, API query, notification) covers most of the use case. Complexity here is a liability, not a feature.
Guardrails that keep it trustworthy
The fastest way to kill trust in this system is to have it be wrong once in a way that gets noticed. A few things matter:
- Show the math, always. Every flagged discrepancy should come with the underlying numbers — spend, revenue, COGS, shipping, fees — not just a conclusion. If someone can't audit it in ten seconds, they'll stop trusting it.
- Handle attribution honestly. If your attribution model has known blind spots — under-crediting retargeting, over-crediting brand search — say so in the output. An agent that pretends its attribution is perfect will eventually get caught out and lose credibility for the whole system.
- Keep humans in the loop on action. Have the agent recommend budget shifts, not execute them automatically, at least for the first few months. Once you've validated it against manual reconciliation for a full season including returns lag, you can start automating smaller actions.
- Account for return and refund lag. Contribution margin isn't final on the day of purchase — returns can take weeks. Decide whether your agent uses a rolling window (e.g., margin as of day 30) or a live number that gets revised, and label it clearly so nobody mistakes a preliminary number for a final one.
None of this requires exotic infrastructure. It requires the unglamorous discipline of getting ad spend, order data, and cost data into one place, joined correctly, refreshed daily. The agent layer on top is genuinely useful, but it's a thin layer over solid plumbing — not a replacement for it. Brands that skip the data engineering and go straight to "build me an AI agent" end up automating bad numbers faster. Build the pipeline first. Let the agent do what it's actually good at: watching the numbers every day so a person doesn't have to, and telling you in plain language when the story your ad platform is telling doesn't match what's landing in your bank account.