automations.help
Book a teardown

Reporting & dashboards

Automate a daily ad spend vs revenue report

To know if yesterday's ad spend paid off, someone opens Ads Manager for the spend, opens Shopify for the revenue, and does the division in their head — across two timezones and two definitions of a 'sale.' This does it for you. Once a day it pulls yesterday's spend from Meta, yesterday's revenue from Shopify, lines the two day-windows up so they're actually comparable, computes blended ROAS (and shows Meta's own attributed ROAS next to it so the gap is visible), and posts one clean line to Slack. Build it yourself from the steps below, or we build it on your own accounts, reconcile the windows and the currency, and hand you the keys so you own it with no monthly fee. Free teardown of how you check whether ads are working first.

Difficulty
Intermediate
Time to build
3-5 hrs
Updated
  • Meta Ads
  • Shopify
  • Slack

The manual way today

Every morning someone — usually whoever owns growth — opens Meta Ads Manager, reads yesterday's spend, then opens Shopify to read yesterday's revenue, and divides one by the other to get a rough ROAS. Then comes the part that quietly eats the time: the two numbers don't agree on what 'yesterday' even was, because the ad account and the store are often on different timezones, and Meta's own ROAS column says one thing while the Shopify total says another. So the real ritual is two logins, a mental adjustment for the timezone, a squint at why Meta claims a 3.1 when the bank account feels like a 1.8, and a number typed into Slack that nobody fully trusts.

It looks like a five-minute check, but it's the single number that decides whether you spend more or pull back today — and it's being eyeballed from two tabs that measure different things. Meta's attributed ROAS counts view-through conversions and credits sales inside its attribution window, so it almost always reads higher than the cash; lean on it alone and you scale a campaign that the bank account says is underwater. The deeper cost is that the comparison is fragile and personal: it lives in one person's head each morning, it isn't logged anywhere, and on the day they're out, nobody can say whether yesterday's spend actually worked. The most important growth number in the business gets the least reliable reporting.

What running looks like

Every morning, before anyone logs in, one line lands in Slack: yesterday's total ad spend, yesterday's revenue, and the blended ROAS between them — with Meta's own attributed ROAS shown beside it so the gap between 'what Meta claims' and 'what the store actually made' is right there. Both sides use the same calendar day in the same timezone and the same currency, so the numbers are genuinely comparable. It posts once a day, reconciles with what Ads Manager and Shopify Analytics each show on their own, and posts even on a zero-spend day or an API hiccup so a silent channel is never mistaken for a profitable one.

01The build

How to build it

  1. Decide which ROAS you're reporting — blended, not just Meta's

    Needs a human

    Pin this down on paper before touching an API, because it's the whole point of the report. Blended ROAS (also called MER, marketing efficiency ratio) is total store revenue ÷ total ad spend — the top-line truth the bank account agrees with. Meta's attributed ROAS (the purchase_roas column) is sales Meta credits to its own ads inside its attribution window, including view-through, so it reads higher and double-counts against other channels. The honest move is to report blended ROAS as the headline and show Meta's attributed number beside it as context, not as the answer. Also decide the revenue basis now — Shopify net sales (gross minus discounts and returns) is the figure that reconciles — and write down that true MER needs every dollar of ad spend, so if you also run Google or TikTok this report is 'Meta-only ROAS' until you add them.

  2. Schedule the run and build yesterday's window in BOTH timezones

    Runs itself

    Use an n8n Schedule Trigger (cron) to fire once early — say 08:00 — and build 'yesterday' as a precise calendar day. The trap that ruins this report specifically: Meta reports in the ad account's timezone (its timezone_name) and Shopify in the store's timezone, and these are frequently different. If they're the same, build one window (yesterday 00:00:00–23:59:59 with the correct offset) and use it for both calls. If they differ, you must decide which day you're reporting and convert: pick the store's calendar day as the source of truth, then translate that window into the ad account's timezone for the Meta call. Skip this and you'll be dividing Tuesday's spend by Monday-night-into-Tuesday revenue and never understand why the ROAS jumps around.

  3. Pull yesterday's spend from the Meta Marketing API

    Runs itself

    Call the Insights endpoint with an HTTP Request node: GET /v<version>/act_<AD_ACCOUNT_ID>/insights with fields=spend,impressions,clicks,purchase_roas,action_values, level=account, time_increment=1, and time_range={'since':'2026-06-28','until':'2026-06-28'} in the account's timezone. spend is your real cash out (already in the account currency); purchase_roas is Meta's attributed ROAS, and action_values with action_type omni_purchase is Meta's attributed conversion value — grab both so the digest can show the gap. Authenticate with a long-lived system-user token that has ads_read; a personal token will expire and break the job at the worst time. If you run more than one ad account, loop over each act_<id> and sum the spend — a brand with a separate prospecting and retargeting account undercounts badly otherwise.

  4. Pull yesterday's revenue from Shopify — all of it

    Runs itself

    Call the Admin API: GET /orders.json?status=any&created_at_min=<start>&created_at_max=<end>&limit=250 (or the GraphQL orders connection with the same created_at filter), using the store-timezone window from step 2. The part everyone forgets: paginate — a good day blows past 250 orders and Shopify hands the rest back behind the Link header's page_info cursor, so loop until there's no next page or your revenue silently caps and overstates ROAS on exactly your busiest days. Sum net sales the way Shopify Analytics headlines it (total_price minus refunds, or subtotal logic matched to your dashboard), exclude test:true orders, and pull customer.orders_count too so you can optionally compute acquisition ROAS (new-customer revenue ÷ spend) — often the number that actually matters for paid prospecting.

  5. Normalize currency before you divide

    Needs a human

    ROAS is a ratio of two money figures, so they have to be in the same currency or the number is fiction. Meta returns spend in the ad account's currency; Shopify returns revenue in the store's currency. If they match, divide directly. If they don't — a US store running a EUR ad account, say — convert one side with a single agreed daily rate (a fixed rate you set, or a rate node), and label the currency in the message so no one reads a converted number as native. Don't let two currencies silently divide into a meaningless ratio; this is a quiet, one-line bug that makes every morning's number subtly wrong.

  6. Compute the line: blended ROAS, with Meta's number beside it

    Runs itself

    Now the math that makes the report worth reading. Blended ROAS = Shopify revenue ÷ total ad spend — the headline. Show Meta's attributed ROAS (purchase_roas, or Meta conversion value ÷ spend) right next to it, plus the delta, so the over-attribution is visible at a glance instead of being a mystery: '1.9 blended vs 3.1 Meta-reported.' Add spend and revenue in plain numbers so the ratio is grounded, and optionally acquisition ROAS (new-customer revenue ÷ spend) for the paid-prospecting view. Compare blended ROAS to the same weekday last week — paid performance is weekly-seasonal like everything else in DTC — so a normal Monday dip doesn't read as a fire.

  7. Build and post the Slack digest

    Runs itself

    Assemble a Block Kit message and post it to a dedicated channel like #daily-roas — via a Slack incoming webhook for the simple case, or chat.postMessage if you want threading or @-mentions. Lead with the headline: yesterday's date, blended ROAS with its ▲/▼ versus last week, then a compact row (spend · revenue · Meta-reported ROAS), and the new-vs-repeat or acquisition-ROAS line underneath. Put the currency and the words 'blended (MER)' right in the message so nobody confuses it with the Ads Manager column, and add buttons linking to Ads Manager and the Shopify dashboard for the day someone wants to dig. Keep it to a five-second read — the goal is a daily gut-check, not a rebuilt dashboard inside Slack.

  8. Make it reliable — idempotent, and loud on $0 and errors

    Runs itself

    A scheduled report that fails silently is worse than none, because an empty channel looks exactly like a day you forgot to run ads. Store the last date you posted (n8n static workflow data or a Sheet cell) and skip if it already ran, so a retry or restart can't double-post. Handle the honest edge cases explicitly: $0 spend yesterday means ROAS is undefined, not zero — post 'no spend yesterday' rather than dividing by zero; $0 revenue with real spend is a genuine alarm worth flagging loudly. Wrap both API calls so a Meta token expiry or a Shopify hiccup posts 'couldn't pull yesterday's ad numbers' to the channel instead of dying quietly. The entire reason you built this is to stop checking two tabs by hand — so the absence of a message must never be allowed to mean 'we were profitable.'

02The stack

What it’s built on

Meta Ads
The spend side, and the source of the attribution gap. The Marketing API's Insights endpoint serves yesterday's spend (real cash) plus purchase_roas and action_values (Meta's attributed ROAS and conversion value), which the digest shows beside the blended number so the over-credit is visible rather than hidden.The Marketing API is free to call — there's no ad-spend upcharge for reading your own insights. Use a long-lived system-user token with ads_read so the job doesn't break when a personal token expires, and respect the per-account rate limits (a once-daily account-level pull sits comfortably under them).
Shopify
The revenue side and the system of record. The Admin API serves yesterday's orders so you can sum real net sales (and split new vs repeat for acquisition ROAS), and Shopify Analytics is the figure your revenue has to reconcile against so the team trusts the ratio.
Slack
Where the one line lands so the whole team sees whether yesterday's spend worked without anyone opening Ads Manager. A free incoming webhook covers the daily post; Block Kit gives you the skimmable card with blended-vs-Meta ROAS and the week-over-week delta.Incoming Webhooks and the Slack API are free on every plan, including the free tier — no upcharge to receive this post.
n8n
The glue that does the work: a Schedule node fires it each morning, HTTP Request nodes pull Meta spend and (paginated) Shopify orders, a Code/Set node aligns the timezone windows, normalizes currency, and computes blended ROAS, and a Slack node posts the card — plus the idempotency guard and the $0/error handling.n8n is open-source and self-hostable for free, which covers this end to end; their cloud tier just saves you running the server yourself. It's a once-a-day, low-volume workflow, so it fits the smallest plan either way.

03Questions

Before you build

Why not just trust Meta's ROAS column in Ads Manager?

Because Meta marks its own homework. Its attributed ROAS counts view-through conversions and credits sales inside its attribution window, so it routinely reads higher than the cash actually landing in Shopify — and it'll credit a sale that another channel or an email also touched. That's not Meta lying; it's just measuring 'sales we can associate with our ads,' which isn't the same as 'sales the business made per dollar spent.' Blended ROAS — total revenue ÷ total spend — is the number the bank account agrees with. This report shows both side by side so you can see the gap instead of guessing at it.

What's the difference between blended ROAS and MER?

They're the same idea from two directions. Blended ROAS is total revenue ÷ total ad spend; MER (marketing efficiency ratio) is usually written the same way, total revenue ÷ total marketing spend. The word 'blended' is the important part: it means you're dividing all your revenue by all your ad spend, not isolating one platform's attributed slice. A clean blended ROAS needs every channel's spend in the denominator — so if you run Google or TikTok alongside Meta, fold their spend in too. Until then, label the report honestly as Meta-only so nobody reads a partial denominator as the full picture.

Why do the two numbers disagree on what 'yesterday' was?

Timezones. Meta reports in the ad account's timezone and Shopify in the store's timezone, and they're often set differently — so 'yesterday' in Ads Manager and 'yesterday' in Shopify can be two slightly different 24-hour windows. Divide one by the other without aligning them and your ROAS wobbles for no real reason. The build picks one calendar day (the store's) as the source of truth and translates that window into the ad account's timezone for the Meta call, so both sides are measuring the exact same day before anything gets divided.

Will the spend and revenue match Ads Manager and Shopify exactly?

They will, if you align two things. The spend matches Ads Manager when you pull account-level spend for the same timezone day and sum across every ad account you run. The revenue matches Shopify Analytics when you sum the same net-sales field it headlines and exclude test orders. The blended ROAS itself won't match Meta's ROAS column — and it's not supposed to, because they measure different things. Reconcile each side against its own source once, label the currency and the basis in the message, and it lines up from then on.

What happens on a day with no spend, or if an API call fails?

It still posts, and it's careful about it. $0 spend makes ROAS undefined, not zero, so the build posts 'no spend yesterday' instead of dividing by zero. $0 revenue against real spend is a genuine alarm and gets flagged loudly. And both API calls are wrapped so a Meta token expiry or a Shopify hiccup posts a clear 'couldn't pull yesterday's ad numbers' rather than failing in silence — because a quiet channel must never be mistaken for a profitable morning. The absence of a message is never the signal.

Can I build this myself, or should you build it?

Every endpoint, field, window, and edge case is written out above on purpose — if you've got a few hours and some API comfort, build it and own it outright. If you'd rather not own the timezone alignment, the currency normalization, the multi-account spend sum, and the blended-vs-attributed math, we'll build it on the Meta, Shopify, Slack, and n8n accounts you already pay for, reconcile both sides against their own dashboards, document every piece, and hand you the keys. You own the workflow — no monthly retainer, and nothing stops working if you walk away. It starts with a free 20-minute teardown of how you check whether ads are working, not a contract.

Reporting & dashboards

Send a daily revenue digest to Slack

Every morning starts the same way: open Shopify, wait for the dashboard to load, read yesterday's number, close the tab. This does it for you. Once a day it pulls yesterday's orders from Shopify, totals net sales, order count, AOV, and units, compares it to the same weekday last week so the number actually means something, and posts a clean digest to a Slack channel before you've finished your coffee. Build it yourself with the steps below, or we build it on your own accounts, wire the math and the comparison, and hand you the keys so you own it with no monthly fee. Free teardown of what you check by hand every morning first.

  • Shopify
  • Slack

Intermediate2-4 hrs

Reporting & dashboards

Post a daily Stripe payouts and fees summary to Slack

Stripe deposits money to your bank on a rolling schedule, nets out refunds and disputes, skims its fee, and tells you almost none of it unless you log in and dig. This closes that gap. When a payout lands, it pulls the exact transactions that make up the deposit, totals the gross, the fees, the refunds, and the net, works out your effective fee rate, and posts a clean summary to Slack — so the number in your bank account finally has a story attached. Build it yourself with the steps below, or we build it on your own Stripe and Slack accounts, reconcile it to the cent against your dashboard, and hand you the keys so you own it with no monthly fee. Free teardown of what you reconcile by hand first.

  • Stripe
  • Slack

Intermediate3-5 hrs

Reporting & dashboards

Auto-alert on a sudden drop in sales

When checkout breaks or an ad set gets paused, orders don't error out — they just stop, and the store looks fine until the next morning's numbers come in low. This watches the order flow in near-real-time, learns what a normal Tuesday 2pm looks like for your store, and pings a Slack channel within the hour when sales fall off a cliff against that baseline — with a triage checklist so someone can go look before the lost revenue stacks up. Build it yourself with the steps below, or we build it on your own accounts — the baseline, the sustained-breach logic, the cooldown so it never spams you — and hand you the keys so you own it with no monthly fee. Free teardown of where your store actually goes quiet first.

  • Shopify
  • Slack

Advanced4-8 hrs

Reporting & dashboards

Build a real-time sales dashboard in Google Sheets

Every week you export a Shopify CSV, paste it into the same spreadsheet, fix the columns, and rebuild the same pivot — to land on a number you could have had automatically. This wires it up instead: each paid order appends itself to a raw log tab in Google Sheets within a minute or two, a scheduled reconcile job catches anything the webhook missed, and a dashboard tab built on QUERY formulas recalculates the moment a row lands. Build it yourself with the steps below, or we build it on your own Shopify and Google accounts, reconcile it against your Shopify Analytics, and hand you the keys so you own it with no monthly fee. Free teardown of the report you rebuild by hand first.

  • Shopify
  • Google Sheets
  • n8n

Intermediate3-5 hrs