Blended data, and the joins that quietly break
A blend is not a SQL join. It aggregates first, joins second, and that order is where the numbers go wrong.
Data blending is the most used feature in Looker Studio and the one that produces the most quietly wrong reports. The reason is structural: each table in a blend is grouped and aggregated before any join happens, so identical rows collapse and the blend can return fewer rows than the same join run in SQL. Add a default left outer operator, metrics that arrive as unaggregated dimensions, and blends that cannot be reused outside their own report, and you have four failure modes that all look like a client typo. This names each one and gives the check that catches it.
What a blend actually is
A blend joins fields from up to five tables into one resource that charts can read. Google's documentation is clear that it is a distinct kind of object, not a data source: blends are always embedded in the report they are created in and cannot be made reusable.
Three consequences follow immediately, and each one surprises somebody every month. A blend cannot be shared to another report, so a portfolio of thirty reports with the same blend has thirty copies. A blend has no freshness or credential settings of its own, inheriting both from the sources underneath. And metrics from the underlying source arrive in the blend as unaggregated numeric dimensions, which changes what your totals mean.
The headline problem is none of those. It is the order of operations. Looker Studio groups and aggregates each table first, then joins. SQL joins first, then aggregates. Same data, same join keys, different answer.
Failure one: rows collapse before the join
Google states the mechanism plainly. Before tables are joined, rows in each table are grouped by the dimensions you included, and if those dimensions do not include a unique identifier, identical rows collapse, producing a lower row count than the same SQL join.
A worked example
Work it through. You have 3 campaigns across 30 days, so 90 rows of daily spend. You build a blend table containing Campaign and Cost but not Date, because the chart only shows campaign totals. Those 90 rows collapse to 3 before anything joins. Now join to a conversions table that still carries Date, and the join fans the 3 collapsed rows back out across 30 dates. Spend is now counted 30 times.
The symptom is a clean multiple
The symptom is a number that is a clean multiple of something. Cost that is exactly 30 times too high, or conversions that match the platform exactly while revenue does not. When a client says the number looks like it has been multiplied, it usually has been.
Add a unique identifier to every table
The fix Google gives is the one to use: add a unique identifier to the dimension list of every table in the blend, even if no chart displays it. A primary key, a row id, or the concatenation of the fields that make a row unique. It costs nothing and it makes the blend behave like the SQL you had in your head.
Failure two: the default operator and the table order
When Looker Studio creates a blend for you from selected charts, it picks the join operator and the table order for you. The order of tables matches the order in which you selected the charts, and every join configuration defaults to left outer.
That default is defensible and it is frequently wrong. A left outer join keeps every row from the left side, so if the left table is Google Ads and the right is a CRM extract, every campaign with no matching CRM record still appears with a null revenue. Sum those nulls into a return-on-spend calculation and you get a ratio that is too low, consistently, for a reason nobody can see on the chart.
Which operators are available
Looker Studio supports inner, left outer, right outer, full outer and cross joins, matching the operators BigQuery documents in its query syntax reference. It also only supports equality in a join condition, so no range joins, no inequality, no fuzzy matching.
Table order changes the answer
Table order compounds it. Joins evaluate left to right, and the result of each join feeds the next one. A three table blend is two sequential joins, and reordering the tables changes the answer even when every operator stays the same.
Failure three: metrics stop being metrics
Any metric you pull into a blend becomes an unaggregated numeric dimension inside it. Google's guidance on reaggregation is specific: reaggregation only happens when the blend contains a subset of the underlying fields, and blends should therefore contain only the fields you actually intend to chart.
When reaggregation helps
This cuts both ways. It is genuinely useful when you need an average of averages or a percentage of a group total, which is why blending a source against itself is a legitimate technique for producing a denominator that does not exist in the data.
When it errors outright
It is destructive when the metric was already aggregated upstream. Google Analytics and Google Ads metrics are pre-aggregated, and applying an aggregation function to them fails outright: re-aggregating metrics is not supported, and a formula such as SUM(Sessions) will error because the limitation comes from the underlying dataset.
One rule for both cases
The rule I use: if the metric came from an advertising or analytics API, treat it as already summed and never wrap it in an aggregation function. If it came from a warehouse table of raw rows, aggregate it explicitly and stop relying on the default.
Failure four: the blend is invisible in review
A blend lives inside its report and nowhere else. There is no asset list to audit, no version to pin, no way to see from outside the report that a chart is reading a five-table join with a cross product in the middle of it.
Copy the report and the blends copy with it, which sounds helpful and means a bad join now exists twice. Thirty client reports built from one master copy carry thirty independent copies of the same defect, and fixing one fixes one.
This is the strongest practical argument for moving the join upstream once the same blend appears in more than two reports. A view or a table in the warehouse is a named object with a definition somebody can read, which is the whole point of documenting lineage so a number is defensible.
Symptom to cause
| What you see | Likely cause | The check |
|---|---|---|
| A metric is an exact multiple of the correct value | Rows collapsed on one side, then fanned out on the join | Add a unique row identifier to every table's dimension list |
| Totals are lower than the source platform | Inner join dropping unmatched rows | Switch the operator to left outer and compare row counts |
| Rows appear with blank metrics | Left outer keeping unmatched rows from the left table | Decide whether the unmatched rows belong in the denominator |
| A formula refuses to save | Aggregating an already aggregated platform metric | Remove the aggregation function and let the source aggregation stand |
| The blend is slow and expensive | Fields in the blend that no chart uses | Strip the blend to the fields the charts actually reference |
| Two reports disagree on the same client | Two independent copies of the blend, edited separately | Move the join into the warehouse and point both at it |
Run the first check before any of the others. In my experience the collapsed-row problem accounts for more wrong blends than the other five combined, and it is the only one that produces a plausible-looking number rather than an obvious error.
When to stop blending
Blending is the correct tool for a question you are asking once. A campaign name that exists in two systems, a quick check of whether spend and pipeline move together, a one-off comparison for a pitch. It is fast, it needs no engineering, and if it is wrong you find out in the same session.
It is the wrong tool for a number a client sees every month. Recurring numbers need a definition that survives a staff change, and a blend embedded in a report is the opposite of that. Once a join is load-bearing, it belongs in a modelled layer, which is the decision covered in marketing data warehouse or dashboard and, when the answer is yes, delivered as a marketing data warehouse rather than a deeper blend.
Move the join, keep the report simple
There is a middle position worth naming. Move the join upstream but keep the report simple: one clean table per client, joined and deduplicated before Looker Studio sees it. Deduplication in particular is not a blending problem at all, it is a pipeline problem, and it is solved where the rows arrive rather than where they are charted. See deduplicating rows from a retrying connector.
For a clinic group reporting across more than fifteen locations, that is exactly the shape that made per-location quarterly reporting safe to send: the joins resolved before the dashboard, so every location's numbers came from one definition rather than one blend per report.
Test the output before a client does
Whichever side you land on, test the output before a client does. A blend that returns 87 rows where the source has 90 is not a rounding difference, it is a join defect, and testing data before a dashboard sees it is how you catch it in the build rather than on the call.