Joining Ad Spend to Orders When There's No Common Key

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.

First, the bug

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.

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.

Grain 3: Click-id capture — 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.

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.

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.

Should I do this in the warehouse or in a BI tool?

The warehouse. BI tools blend at query time with join semantics you cannot inspect, and the fan-out bug at the top of this post is dramatically easier to create in a drag-and-drop interface than in SQL — because in SQL you can at least read the join.

The summary

• Joining a row-level orders table to an aggregate spend table on date multiplies 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 unattributed row 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.