Joining Shopify and Ad Exports

Two files at different grains, joined through an attribution nobody fully trusts. The keys that work, the ones that do not, and why the spend you cannot match belongs on screen rather than in the bin.

Every interesting store question — is this channel paying for itself, which categories survive their acquisition cost, what does a customer actually cost — needs two files in one table. This is the step that stalls, and it stalls for reasons that have nothing to do with the tooling.

There has to be a key, and it has to be on the order

The join runs from ad spend to orders through attribution. Common keys, in descending order of reliability:

1. A click identifier captured at checkout and stored on the order. Best available, and rarest.
2. A campaign name or id from a UTM, recorded on the order.
3. A source or medium only. Enough for channel-level questions, not for campaign-level ones.
4. Nothing. More common than anyone likes to admit.

If the order carries no attribution, no amount of analysis fixes it. The information was not recorded at the time and cannot be reconstructed from what the store holds. That is a tracking change, and it is worth knowing before planning a month of work around a file that cannot support it.

Two grains, and a mismatch that is not an error

Spend is per campaign per day. Orders are per order, each with lines, each line a product. There is no row-to-row correspondence anywhere, and forcing one is where most attempts go wrong.

The workable shape is to aggregate both sides to a common grain and join there:

Spend side   campaign × day        → campaign × month
Order side   order → campaign      → campaign × month

Then, if a product breakdown is needed, spend for a campaign is split across the categories that campaign orders actually bought from, in proportion to their revenue in the same period. That is an allocation, with all the caveats allocations carry — but it is at least an allocation you can state.

Bucket spend by the day it was spent

The tempting alternative is to attribute spend to the date of the orders it is credited with. It reads as more accurate and it is a trap: January spend lands in February because a customer took three weeks to convert, monthly totals stop matching the platform invoices, and nobody can reconstruct why six months later.

Spend on the spend date is boring, reconciles to what the platform billed, and does not move after the fact. Where a period genuinely has spend but no attributed orders, that period gets an unallocated figure — which is information, not a defect.

The unmatched row is the point

Three things fail to match, always:

• Spend with no orders. Campaigns that converted nothing, or whose UTM never reached the order.
• Orders with no spend. Organic, direct, email, returning customers. Correct, and not a problem.
• Names that nearly match. The worst kind, because they silently move money between campaigns rather than failing.

Only the first needs a decision. Dropping it makes every channel look more efficient than it is and makes the breakdown quietly disagree with your card statement. Spreading it across everything charges channels for spend that demonstrably reached none of them. Reporting it as its own Unallocated row is the version that keeps the table honest, and its size is a direct measure of how much of the rest to believe.

Doing it in Excel

Normalise the key on both sides first, because this is where most of the loss happens:

Key  =LOWER(TRIM(SUBSTITUTE([@Campaign],"_"," ")))

Then aggregate spend to campaign and month, and attributed revenue the same way:

Spend     =SUMIFS(Ads[Cost],Ads[Key],$A2,Ads[Month],$B2)
Revenue   =SUMIFS(Orders[Net sales],Orders[Key],$A2,Orders[Month],$B2)
ROAS      =IFERROR(D2/C2,"")

And measure what did not match, on money rather than on rows:

Unmatched spend  =SUMPRODUCT((COUNTIFS(Orders[Key],Ads[Key])=0)*Ads[Cost])
Match rate       =1-E2/SUM(Ads[Cost])

Put that match rate at the top of the sheet. It is the number that decides whether anything below it can be shown to anyone.

What the join unlocks

Once the two sides are in one table, the questions that were impossible become arithmetic: spend and attributed revenue per channel, ROAS per campaign, and — once cost of goods is joined too — contribution margin by category, which is the only one of the three that can tell you a well-performing campaign is losing money on the product it sells.

If ROAS by channel is the immediate need, there is a dedicated walkthrough in how to calculate ROAS by channel.

Doing it without maintaining the join

Quiriz for e-commerce takes the store and ad exports as separate uploads and holds the join as a definition rather than as a monthly VLOOKUP. Spend is bucketed by its own date, allocated within each period by the mix of orders it is attributed to, and anything unmatched comes back as an Unallocated row instead of vanishing. In Excel that is =QUIRIZ.ASK("contribution margin by channel last quarter", "table").

The honest boundary: we cannot supply attribution that was never recorded. If your orders carry no campaign, source or click id, there is no key, and the answer is a decline with the reason rather than a confident channel table built on nothing.

Frequently asked questions