Data blending: combining sources in a report
Data blending is joining two or more data sources on a field they share, so figures that live in different tools end up on one row.
Ad spend from two platforms and sales from your store, lined up by day, is a blend.
Why it matters
The questions worth asking usually span tools. Whether advertising pays for itself needs spend from the ad platforms and revenue from the store. Whether a campaign's clicks turned into engaged visits needs the ad platform and analytics. No single tool holds both halves, so the comparison only exists once you blend.
How it works
Every blend needs a join key: a field present in each source that means the same thing in each. Date is the commonest. Campaign name is the next, and the most troublesome.
Looker Studio does this in a blend editor, joining up to five tables and letting you choose how unmatched rows are treated (Looker Studio Help, How blends work). In a spreadsheet you do the same job with SUMIFS: one row per value of the key, and a SUMIFS per source that adds up everything matching that row.
Here is an illustration with made-up numbers. Three days of spend from Google Ads and Meta Ads, sales from Shopify, joined on the date:
| Day | Google Ads cost | Meta Ads amount spent | Shopify net payment | Blended ROAS |
|---|---|---|---|---|
| 1 October | £400.00 | £250.00 | £2,600.00 | 4.00 |
| 2 October | £380.00 | £270.00 | £2,275.00 | 3.50 |
| 3 October | £420.00 | £230.00 | £2,925.00 | 4.50 |
| Total | £1,200.00 | £750.00 | £7,800.00 | 4.00 |
Each day's blended ROAS is sales over the two platforms' spend together: 2,600 ÷ 650 = 4.00 on the first. The total is 7,800 ÷ 1,950 = 4.00, and it is worked out from the totals, not by averaging the three days. Blended ROAS and MER covers what the figure is for.
What it can't tell you
A blend lines numbers up; it doesn't make them agree. If Google Ads credits itself with a sale that TikTok also claims, a blend of their conversion columns counts it twice. Blend spend with revenue from your own store, as above, and leave each platform's conversions in its own column. Which channel caused which sale is an attribution question, and a blend can't answer it.
Where the keys go wrong
Dates in different time zones. Each ad account reports days in its own time zone, and Shopify reports in the store's. A store in London and an ad account set to Los Angeles put the same evening on different dates. Set the accounts to one zone where you can, and expect small edge differences where you can't.
Names that nearly match. Joining Google Ads campaigns to Google Analytics 4 by name fails on a capital letter or a trailing space. "Autumn Sale" and "autumn sale" are different keys. Consistent UTM naming fixes the analytics side.
Mixed grain. Blending a daily table with a weekly one repeats or drops figures. Bring both to the same grain first.
Tracking it in a spreadsheet
Scheduled Fresh Sheets reports can keep each source current in a tab of its own: Google Ads Cost by Date in one, Meta Ads Amount spent by Day in another, and Shopify Net payment by Day in a third. Fresh Sheets replaces each tab's contents on every refresh, so build the blend on a separate tab with one row per date in column A.
This formula, in B2, adds up Google Ads cost for the date in A2, finding both columns by their headings so it still works if a field is added and the columns move:
=SUMIFS(INDEX('Google Ads'!A:Z, 0, MATCH("Cost", 'Google Ads'!1:1, 0)), INDEX('Google Ads'!A:Z, 0, MATCH("Date", 'Google Ads'!1:1, 0)), A2)
Copy it across for the other two tabs, changing the tab name and the two headings, and fill it down the dates.
Related guides
How to export Shopify orders to Google Sheets
From a one-off export to a report that keeps itself current.
ROAS in Google Sheets
The formula, the averaging mistake, and why platform ROAS never matches your store.
Blended ROAS and MER in Google Sheets
Total revenue over total ad spend, with Shopify, Google Ads and TikTok Ads as the worked example.