Before you start
- Sound data sources. Each source should already be reusable, correctly typed and named, with its credential mode decided. The data sources guide covers that; blending a badly typed source multiplies its problems.
- A naming convention that both platforms share. Blends join on values, so the campaign name in the ad platform has to equal the campaign value recorded by your UTMURL tags identifying traffic source. tags or your CRM. If the convention is loose, plan to normalise with a calculated field before joining.
- Each source's own totals for the period. Note the cost, sessions, leads or revenue each source reports alone for the date range you will test with. Validation in the final steps compares the blend against these.
- Edit rights on the report and view rights on every source. Blends are built in the report, and the blend editor can only see sources you can read.
- A clear owner for the metric definitions. If "lead" or "revenue" is contested, settle it in the metrics layer first; a blend cannot reconcile two definitions, only two tables.
Define the question and its grain
Write the question as one sentence that names the metric, the dimension and the period: cost per lead by campaign by day, or revenue per sessionRevenue divided by the number of visitors in the same period. by landing page by week. The dimension and period together are the grain, and every table in the blend must be aggregated to exactly that grain before it is joined. A blend without a stated grain is where fan-out begins: one campaign row on the left matches several rows on the right, and the left-hand cost is counted once for each match. If the question needs two grains — campaign totals and daily trend — plan two blends rather than one that tries to serve both.
Choose the sources and check each alone
Pick the smallest set of sources that answers the question; each extra table adds a join, a query and a place for the grain to slip. Before opening the blend editor, drop a table chart on the page for each source with the dimensions and metrics you intend to blend, set the date range you will validate against, and look at the values. Odd rows show up here: campaign names with trailing spaces, a date field typed as text, an "(not set)" bucket that will never join to anything. Fix what you can at the source, since source edits reach every report; note the rest so you can account for it when totals are compared later.
Pick the join key
The join key is one or more fields present in every table with the same meaning, the same type and the same grain. Campaign name plus date is the usual pair for cost-versus-outcome questions; landing page URL plus date works for search and analytics. Both sides must be text joined to text or date joined to date; a numeric ID in one source and a text ID in another will not match until one is cast. Case and whitespace differences break matches too, so where the convention is not enforced, create a normalised key at the source with a lower-case, trimmed version of the field and join on that. Keys are set in the blend editor as join conditions between adjacent tables.
Choose the join type
Looker Studio offers left outer, right outer, inner, full outer and cross joins, set per join between tables. Decide which table is the spine — the one whose rows must all appear — and left-join the others to it. Cost-led questions usually make the ad platform the spine, so every campaign with spend shows even when the CRM recorded no leads. Use an inner join only when a row is meaningless without a match on both sides. Use a full outer join while debugging, because it reveals unmatched rows on either side. A cross join matches every row with every row and belongs only to tiny lookup tables such as a target per month. The table after the steps summarises the options.
Align dates and dimensions
Each table in a blend carries its own date range dimension; set it in every table, and make sure the field types match, because a date on one side and a date-and-time on the other do not align by day. Then add the same dimensions to each table that the grain requires — if the question is by campaign by day, every table gets campaign and date, nothing more. Looker Studio aggregates each table by its own dimensions before joining, so an extra dimension on one side changes that side's grain and produces duplicates on the other. Dimensions needed only for display, such as a campaign type, come from one table only, usually the spine.
Add the metrics, named by their source
Bring in the metrics each table contributes and rename them so their origin is visible: "Ads cost", "CRM leads", "Analytics sessions". A blended chart with two fields both called "Conversions" is a question waiting to be asked. Do not create ratios inside the blend tables; a per-row ratio would be averaged later and would be wrong. Ratios such as cost per lead or ROASRevenue generated per unit of ad spend. are added afterwards as chart-level calculated fields that divide a summed metric by another summed metric, following the pattern in the calculated fields tutorial. Keep the metric list short; every metric is another column the join has to carry.
Validate totals against each source
Build a scorecard for every blended metric and set the same date range you noted before starting. With a left join, the spine's metrics must equal the spine's own totals exactly; if they are higher, the right-hand table has duplicate key values and the grain is wrong. Metrics from the joined tables will be lower than their own totals by exactly the rows whose keys found no match. To see those rows, switch the join to full outer temporarily and filter for an empty spine key; the list is your mismatch report, and each entry is a naming fix. Only when the blended totals are explained row by row should the chart go on a page that other people read.
Name the blend and decide where it lives
Give the blend a name that states its tables and grain, such as ads_crm_campaign_day, in the manage blends panel, and record it alongside the report's definitions so the next editor knows what it joins. Then ask whether the join should live here at all. Blends are report-specific, run one query per table every time a chart loads, and cannot be shared. If the same join is wanted in a second report, or the page is slow, move the join upstream: a scheduled query into a warehouse table or a sheet that both platforms export into, connected as one plain data source. The performance guide covers when that move pays for itself.
Join types
| Join type | Keeps | Use it when | Watch for |
|---|---|---|---|
| Left outer | Every row of the left table, matched rows of the right | The left table is the spine: all campaigns with spend, whether or not they produced leads | Unmatched right-hand rows vanish silently |
| Right outer | Every row of the right table, matched rows of the left | The same as left outer, with the spine on the other side; usually clearer to reorder the tables | Easy to misread which side is complete |
| Inner | Only rows matched on both sides | A row means nothing without both halves, such as cost and revenue for the same order | Totals drop on both sides; mismatches are invisible |
| Full outer | Every row from both tables, matched where possible | Debugging: find keys that exist on one side only | Empty keys need handling in filters and charts |
| Cross | Every combination of rows from both tables | Attaching a small lookup, such as a monthly target, to every row | Row counts multiply; never use it on two large tables |
Cost per lead across Google Ads and a CRM
A software company wants cost per lead by campaign for March. Google Ads reports £12,400 of spend across 14 campaigns; the CRM reports 310 leads with a campaign field copied from the landing-page UTM. A left join from Ads to the CRM on campaign name and date returns the full £12,400 but only 290 leads. A full outer join with a filter for an empty Ads campaign shows the missing 20: the CRM holds them under "spring_sale" while Ads names the campaign "Spring_Sale". A lower-case, trimmed key at each source fixes the match; the blend now returns 310 leads, and a chart-level field dividing summed cost by summed leads reports £40.00 per lead. The team records the key normalisation in its definitions document and schedules a nightly warehouse join to replace the blend once a second report needs the same figure.
Common mistakes
- Joining on a field that is not at the same grain on both sides. Campaign on the left, campaign plus ad group on the right; the cost is multiplied by the number of ad groups.
- Trusting an inner join because the numbers look tidy. The rows it drops are exactly the ones that would have exposed a naming problem.
- Averaging a ratio metric across the blend. A cost-per-lead field averaged across campaigns weights every campaign equally; divide sums instead.
- Mismatched date types. A date on one side and a date-and-time on the other never align, and the chart shows a line that stops.
- Blending to fix a definition problem. If two systems disagree about what a lead is, the blend only lets them disagree in one chart; settle it upstream.
- Rebuilding the same blend in every report. The second time a join is needed is the moment to move it into a sheet or a warehouse table.
- Forgetting that a KPIA metric tied directly to an objective. built on a blend inherits every source's freshnessWhether a table, feed or report holds data as recent as it should; a freshness check fails when the latest date is older than the source's expected arrival.. The slowest, least frequent source sets the age of the whole number.
The Looker Studio hub links the rest of this series; the natural next read is the calculated fields tutorial, which turns blended sums into the ratios people actually ask for.
