Hourly ad reporting: today's numbers, not yesterday's

A 6-brand advertiser running Google Ads, Meta, and X with daily budgets in the tens of thousands was making spend decisions on data up to 30 hours old. We closed that gap to about an hour.

The problem

All reporting was a day behind. Connectors delivered data once a day, so a decision to kill a losing campaign could be delayed up to 30 hours. The ad platforms showed cost but not backend revenue; the warehouse knew revenue but not today's cost. Across Google Ads, Meta, and X with daily budgets in the tens of thousands, that lag was expensive.

What we built

An hourly chain of four independent stages, staggered by minute within the hour:

• :05 — incremental load of backend order data from the API into staging.
• :10 — our own pullers for ad cost, straight from the Google Ads, Meta Graph, and X Ads APIs — bypassing the daily-only connector entirely.
• :35 — a dbt run limited to the "live" models over a one-day window (insert_overwrite, history untouched).

The metric mapping wasn't assumed — it was reconciled against the daily connector to the cent, on a closed day, before anything went live.

The result

Every brand now sees today's cost, sales, and CAC with an hourly lag instead of a daily one. 45 live models run in about 6 minutes on 10.9 GiB per run — roughly $1.6/day. We also shipped hourly offline-conversion uploads back to Google Ads, reconciled daily against the nightly model with a hard fail on any mismatch — and along the way fixed a conversion-value bug where every conversion was stamped with a customer's entire purchase history (up to $97 instead of $1.27). After the fix, average conversion value dropped 10.4%, and the platform started bidding on the truth.