Joining ad spend to orders with no common key (three grains that actually work)
Chinmay Raibagkar · has debugged a 10x revenue miracle that was a join🔗 with the fan-out bug live, not a lecturePublished August 28, 2026
The 60-second version
- Joining orders to spend on a shared date multiplies revenue by your campaign count. Each order matches every campaign row.
- Aggregate both sides to the same grain first — a date spine for MER, a channel spine for channel ROAS, always.
- The only real key is one you create: capture click IDs at landing, persist them, write them onto the order.
You have a spend table: one row per campaign, per day, with a cost. You have an orders table: one row per order, with a customer, a timestamp and a value. Nothing in either table points at the other.
There is no campaign_id on an order. There is no order_id on a campaign. The join key you need does not exist, and the query someone will inevitably write — join them on date — produces a number that is wrong by a factor equal to the number of campaigns you ran.
This post covers the three join grains that actually work, in increasing order of both fidelity and effort, and the fan-out bug that ruins the naive attempt.
Grain 1: the date spine
One row per day per side, then join. Needs nothing, disputes nothing, gives MER and blended cost per order — but no channel dimension.
Grain 2: the channel spine
Join on date plus channel from captured UTMs. Gives channel ROAS, but only for orders whose source survived — keep the unattributed row visible.
Grain 3: the click id
Capture gclid and fbclid at landing, write them onto the order. The only genuine key — campaign and ad-group ROAS for the covered share.
First, the bug
Never join a row-level table to an aggregate table on a shared date. It is the most expensive mistake in marketing SQL, it produces a number that looks plausible, and nothing errors.
Here is what happens. Ten campaigns ran on 12 August. Forty orders were placed on 12 August. You join on date:
Each of the 40 orders matches each of the 10 campaign rows. The join emits 400 rows. SUM(o.order_value) now counts every order ten times, and every campaign appears to have generated the entire day's revenue. Total "revenue" across campaigns is 10x your actual revenue, and each individual campaign's ROAS is 10x too high — but each campaign's relative ROAS looks sensible, so the error survives review.
The rule: aggregate both sides to the same grain before joining. Always. tape this to your monitor
Every order is counted once per campaign. Reported revenue exceeds reality by exactly the campaign count — and relative ROAS still looks sensible.
Fan-Out, Illustrated
Data JourneyBefore the join
10 campaign-day spend rows on one side. 40 order rows on the other. Different grains, one shared date column.
After joining on date
Every campaign row matches every order row for that date. The join emits the product, not the union.
The reported number
SUM over the fanned-out rows counts each order once per campaign. Revenue appears ten times larger than it was.
Grain 1: The date spine
The simplest correct answer, and the right one if you have no attribution data at all. Aggregate both sides to one row per day, then join.
What you get: MER and blended cost per order, with no attribution assumptions and nothing that can be double-counted.
What you do not get: any channel dimension. This cannot tell you where to move budget.
The date spine matters more than it looks. Without it, a day with spend and zero orders vanishes from an inner join — which is precisely the day you would want to investigate.
Grain 2: The channel spine
One step up. If your orders carry a channel — because you captured a UTM at landing and wrote it to the order — you can join on (date, channel) instead of date alone.
This is where a clean UTM taxonomy stops being hygiene and starts being load-bearing: the join only works if utm_source in your orders table uses the same vocabulary as the platform name in your spend table.
Watch the unattributed row. It is the most useful output of this query and the one people filter out first. It is every order whose source you failed to capture — direct traffic, stripped UTMs, cross-device journeys, ad blockers. If it is 40% of your revenue, your channel ROAS numbers are computed on a minority of your business, and you should know that before acting on them.
Grain 3: the only real join key
Grains 1 and 2 are aggregations that avoid needing a key. Grain 3 creates one.
The mechanism is straightforward and entirely within your control: capture the click identifier at landing, persist it, write it onto the order at checkout.
Building the Key You Do Not Have
Process FlowCapture at landing
On page load, read gclid, fbclid, wbraid, gbraid and all utm_* parameters from the URL. Do this on every page, not just the homepage — people land deep.
Persist first-touch and last-touch separately
Write to a first-party cookie or localStorage. Keep first-touch (write once, never overwrite) and last-touch (overwrite each visit) as separate values — they answer different questions.
Attach at checkout
Read both values and write them to hidden order fields, or as order metadata via your commerce platform's API. This is the moment the key comes into existence.
Join on the click id
Your orders table now carries a genuine foreign key into the ad platform's click-level data. No aggregation tricks required.
Note the structure: the click join is at order grain, and spend is pre-aggregated in a subquery before joining. Even with a real key, mixing grains reintroduces fan-out — MAX(s.spend) rather than SUM(s.spend) is a second belt-and-braces guard against it.
Click-ID capture checklist in this order, before you need it →
- Capture at landing, on every page — read gclid, fbclid, wbraid, gbraid and all utm parameters from the URL, not just the homepage.
- Persist first-touch and last-touch separately — write-once versus overwrite-each-visit answer different questions; two columns cost nothing.
- Attach at checkout — read both values into hidden order fields or order metadata, the moment the key comes into existence.
- Join on the click id, then publish the coverage ratio — attributed revenue over total revenue, next to every attributed number.
What each grain can and cannot tell you
The coverage ratio
Whichever grain you land on, compute this and put it next to every attributed number:
A click coverage of 35% means your beautifully precise campaign-level ROAS describes about a third of your revenue. That is still useful — it is far better than nothing for relative decisions between campaigns — but it must never be presented as though it described the business.
Frequently asked questions
Why not just use the platform's reported revenue?
You can, for bidding. You cannot for anything that has to tie to your books: platform revenue is gross, back-dated to the click, double-counted across platforms, and includes modeled conversions that correspond to no order at all. The point of joining spend to your own orders is to get a number computed entirely from systems you control.
First-touch or last-touch click id?
Store both. Last-touch matches what platforms report and is the right comparison when you are reconciling against a dashboard. First-touch is more honest about what created the demand. Storing both costs two columns and lets you answer either question later; storing one forces you to pick now, in ignorance.
What about wbraid and gbraid?
Google's iOS-era click identifiers, used when gclid is unavailable due to app-to-web privacy restrictions. Capture all four — gclid, wbraid, gbraid, fbclid — into separate columns. Missing them means losing iOS attribution entirely, which is a large share of most consumer businesses.
My orders have no UTMs at all. Where do I start?
Grain 1, today. It requires nothing but the two tables you already have and gives you MER and blended cost per order — the two numbers that actually gate budget decisions. Then implement click-id capture, which is a few hours of front-end work, and grain 3 becomes available in a month once orders start carrying it. grain 1 today beats grain 3 someday
You join 40 order rows to 10 campaign-day spend rows on date, then SUM(order_value). What does the total report?
Should I do this in the warehouse or in a BI tool?
🧩 In a BI tool
Blends at query time with join logic you cannot inspect. The fan-out bug is easier to create here than in SQL — because in SQL you can at least read the join.
- Join semantics invisible
- Grain mismatches silent
- Fan-out one drag away
🏛️ In the warehouse
Aggregate first, join second, in version-controlled SQL. Each grain is a reviewable query with a coverage ratio beside it.
- Join grain explicit per query
- Dedup and mapping in views
- Coverage ratio published alongside
The summary
- Joining a row-level orders table to an aggregate spend table on
datemultiplies your revenue by the number of campaigns. Aggregate both sides to the same grain first, always. - Grain 1 (date spine) needs nothing, disputes nothing, and gives you MER and blended cost per order. Start here.
- Grain 2 (channel spine) needs a clean UTM taxonomy and an explicit platform-to-channel mapping. Keep the
unattributedrow visible. - Grain 3 (click id) is the only real key, and you have to create it — capture at landing, persist, attach at checkout.
- Always publish the coverage ratio next to any attributed number, so nobody mistakes a third of the business for all of it.
Takeaways for your next report
- Joining row-level orders to aggregate spend on date multiplies revenue by the campaign count — aggregate both sides to the same grain first, always.
- Grain 1 (date spine) needs nothing and gives MER; grain 2 (channel spine) needs clean UTMs; grain 3 (click id) is the only real key and you must create it.
- Keep the unattributed row visible — it tells you how much of the business your attribution actually sees.
- Always publish the coverage ratio next to any attributed number, and do the joining in the warehouse where joins are readable.
Blended CAC Calculator
Total spend across every channel, divided by total new customers — the acquisition cost number that reconciles with what you actually spent.
Chinmay Raibagkar
About author →Founder of DataLens AI. He helps non-technical teams read their ad and database numbers with confidence — which number to trust, what to do next, and what to ignore.