Data blending is among the most versatile functionalities added by Looker studio. Previously, combining data sources required exporting it to an Excel sheet (not there is anything wrong with this method), where you can do all sorts of analysis like comparing the spending between Google Ads and Facebook ads based on ROI.
The data blending functionality in Looker studio is more flexible, and will help you explore new relationships between separate data sources.
What is data blending in Looker Studio?
When we talk about data blending, we simply refer to combining two or more data sources, to create a new data source. Looker Studio has five Data blending options, but most of the time you will work mainly with left-outer join, which we will explain later in this article.
Data Blending is a powerful functionality in Looker studio (and other BI tools). It is especially useful for manipulating data from multiple sources (up to 5), without leaving your dashboard.
Blending strategies in Looker Studio:
Creating a blended data source:
Instead of just rephrasing the official documentation (which you can check here), We will use a simple real world example. We will be working with two data sources, the first will be our standard GA4 (Google Analytics 4) property. The second source is a Google Sheet, where we have data of the number of users who created an account by date from our CRM.
In this report, I have two data sources, each represented in a table, the orange one is from a GA4 property, the other is from a Google Sheet. We will blend the data by either selecting the two tables and selecting the blend option, or selecting a table and clicking on blend data under the Data source field.
Before diving into the blend types, there are a few concepts you need to become familiar with:
- Join key: the join key allows you to combine two or more data sources based on a common element across all data sources. In the example for this blog post, we will use the date as our join key.
- Table: everything we do in data blending is based on tables. For instance, we have two tables in our current example, each of them contains dimensions, metrics, and filters from a data source.
Choosing a data blending method:
What methods you will choose will dictate how much data gets to be shown on your dashboard. For instance, If you pick a full-outer join, you will have all the data from your available data sources. This can be useful, let’s say, if you want to know the total ad spend from your Google Ads and Facebook ads accounts.
We will explore each data blending method with a concrete example. So don’t worry if you have a hard time grasping the concepts.
The left-inner join
Using this join type, you will get all the data from the Google Analytics account, plus, matching data from the right table.
Since we used the date as our join key, we will get all the data from the Google Analytics account, plus matching data from the CRM data source.
Notice that even if we have subscribers on the 7th, we can’t see it on the blended data source because in the left join. We only have the data from the Google Analytics, which doesn’t have any data from the 7th October.
Right-outer join:
The right-outer joint works the same way as the first type discussed above. Only this time the roles are reversed. We will have all the data from the right source (CRM DATA), plus the matching data from the left source (Google Analytics)
Inner join:
In the inner join, you will get matching data from both data sources. For example, we removed dates from 1st to 5th October from our CRM sheet. Notice in the blended table the data from Google Analytics is not showing even though sessions were recorded from 1st to 6th October.
Similarly, if, there are no sessions on a certain date, there will be no available data for the number of subscription on that date, even though we have subscriptions were recorded.
Full outer join:
You get all the data from both sources, regardless of whether they match or not. In our example, even when we delete the data from 1st to 5th October, you will be able to see sessions in GA from that time period.
Cross-join
For this joint type, Looker Studio will generate every possible of combination from the two data sources. Here is a basic overview of how it works:
Table 1:
- A
- B
Table 2:
- 1
- 2
- 3
When we blend the two data sources, we get the following result:
- A 1
- A 2
- A 3
- B 1
- B 2
- B 3
