How to blend data in Looker Studio - part 1?

Blend data in Looker Studio (formerly known as Blend data in Data Studio!) is one the most powerful feature you'll face during your dashboarding journey. Indeed, it gives you the possibility to group dimensions & metrics from several data sources into a single one.

The most common use case? You've got a Shopify connector, a Google Ads connector, a Meta Ads connector, and you want to be able to join all of these to create a single master table including: Shopify revenue, Google ads spent, Meta ads spent, and from there calculate profit, ROI & ROAS. How can you do that? Well, I guess you got it at this point, blend data!


This article is the first part of a serie. To start with, we're going to focus on simple blends, and only using the operators INNER and FULL OUTER.


Need help ? We offer Looker Studio Support for companies.

Looking for a connector ? Check Supermetrics.


Blend data: set-up the data source

For this example, we're going to use GA4 and Google Ads sample data. We've got two tables at hands: the first ones shows the Google Ads impressions by country/territory, and the second one the GA4 sessions by country. In this case, you'll notice that both tables share the same dimension. Make sure that they are in the same type/format before moving to the next step.

Looker Studio blend data part 1 tables

So, we're going to blend these two tables into one so we can display Ads impressions next to Analytics sessions at the country level in the same table (and if we wanted, we could then create calculate fields).


To do so, please click in the top menu on Resource, Manage Blends, and then Add a Blend. For Table 1, I'll choose the GA4 connector (that I'll rename GA4 by clicking on (Table Name)). I'm going to choose Country as a dimension, and sessions a metric.

Then, click on Join another table, choose your second table (in my case, the Google Ads sample data that I'll rename GAds). I'll add again Country/Territory as a dimension and impressions a metric. At this stage I know that Country from first table and Country/Territory from second table are the same type as they've got the same little world icon left to their names. Often, blend breaks because dimension types mismatch.


If you followed my steps, you should see that:

Looker Studio blend data part 1 blend data

Let's stop a second. What are we doing here?

  • We're asking the first table to give us sessions by country
  • We're asking the second table to give use impressions by country/territory


Now, we're going to explain Looker Studio how to blend data, ie how to put each country from table 1 in front of the same country from table 2 so we've got both metrics on the same line in the same table.


Blend data configure join

Please click on Configure join, this is well all the magic happens. We're going to explain to Looker Studio how it should proceed to the join, so we make sure that newly created table is exactly what we wanted:


Looker Studio blend data part 1 join configuration

Blend data join conditions

Under Join conditions, we're going to specify to Looker Studio how to match lines. On the left Add field, click and select Country. On the right Add field, click on Missing that appeared and select Country/Territory. What have we just done?

We're asking the following: "If you find a matching value between these two fields, then join the data". So if you find sessions for "United States" and impressions for "United States", put them together.

Obviously, we joined on one dimension, but we could join on two or more. Like match by country & by date. The processus would be the same as we've just seen, one line per dimension.


Blend data join operators

In this article, we'll only going to review the INNER and FULL OUTER joins. What do they mean?

  • INNER: only return data if you find dimension in both tables.
  • In our example, we've got 1 sessions in Montserrat, North Korea, Palau, St. Martin, Tonga or Vatican City but no impressions -> the countries will not be returned in the blended table:
Looker Studio blend data part 1 inner join
  • FULL OUTER: always return data, even if only found in one table. So, in our example, the listed country above have 1 sessions but no impressions -> the countries will be returned in the blended table;
Looker Studio blend data part 1 full outer join

And that's it for the day! In this article, you've learned how to blend data by using two tables, with one dimension & one metric in each, focusing on INNER and FULL OUTER joins. In the next article of the serie, we'll go deeper into the topic.

PROBLEM SOLVED !


Previous article: How to build a field using a CASE WHEN statement in Looker Studio?

Next article: How to build calculated fields in Looker Studio and what are the most useful formulas?

Looking for something else? All our articles can be found on our blog page