Data Blending in Google Data Studio Explained with Picture
On the 17th of February, the Google Data Studio team introduced new ways of blending data, including Inner Join, Left outer join, Right outer join, Full outer join, and Cross join.
With data blending in Google Data Studio, you can create charts, tables, and filters based on multiple data sources. For example, you can blend data from Google and Facebook Ads for a unified view of your marketing campaign performance—and visualize that information in a single Data Studio table.
It takes time to grasp the idea of each join operator and I have tried to phrase it in many ways which could provide a better understanding of each operator below.
Data blending join operators
Left outer join: returns all records from the left table, plus matching rows from the right table.
Right outer join: returns all records from the right table, plus matching rows from the left table.
Inner join: returns only matching rows from the left and right table.
Full outer join: returns all matching rows from the left tables or the right table.
Cross join: returns every possible combination of rows from the left and right tables.
What to consider before creating blended data sources in Data Studio?
It can create a large data set, leading to a slower chart load in the data studio. In addition, using a paid data warehouse like BigQuery results in higher query costs.
- 💡 You can limit the number of rows by applying a filter or fixed date range to a blended data source. It will filter the data before blending takes effect.
Data Studio will automatically create a default Left outer join type for each table when you create a data blending.
Blends may contain more rows than the original data, and Cross joins will always have more rows than the sum of the rows in the individual data sources.
Limitations:
Up to 5 data sources
Joining them using from 0 to 10 join keys
A large blended source charts will load slow
1. Left outer join: returns all records from the left table, plus matching rows from the right table.
Phrase differently
Select all records from the "left table" and any records in the "right table" that match the joined keys.
Left join retains all rows of the "left table", regardless of whether there is a row that matches the "right table".
Left join returns all the rows from the "left table", even if there are no matches in the "right table".
Returns the result set of all the values from the "left table" and only rows that match the condition in the "right table". Likewise, if there is no matching row, the right side columns will show nulls.
Matched rows in the "right table" and all rows in the "left table."
A left join returns any rows from the "right table" that match the join condition. Rows from the "right table" that don't match the join condition are returned as nulls.
In the above table, the Left join configuration is applied and takes all the data from the left table and the matching data from the right table.
All data, including A, B, and C, form the left table (1) and matching A and B form the right table (2), and Row D which is not available in table 1 returns "null." in Blended table.
Best use case:
Aggregate products from different tables or data sources.
To identify the same data labels between the different tables (data sources)
If you want to get all unique rows in columns that exist in two tables
Now let's put into practice with real-world data. Let's assume we have Leads (Potential Customer) data in table 1 and Customers (Made a purchase) in table 2. With the Left Join, we can combine data in a table to find customers who made a purchase and customers who didn't make a purchase yet will have a null value in the Blended table.
💡
Tablet orders (e.g. below: Data Blending Menu Explained) are important, and please make sure you have the correct order according to the data extraction objective.
If you switch the table order, the above left join configuration will have the same result as the Right Join table below
2. Right outer join: returns all records from the right table, plus matching rows from the left table.
Phrase differently
Right outer join — exactly opposite of left join
The right join works exactly like the left join, except the table order is reversed.
All rows from the "right table" are returned in a right join. The "left table" is conditionally returned based on the join condition.
Select all records from the "right table" and any records in the "left table" that match the joined keys.
Returns the result set of all the values from the "right table" and only rows that match the condition in the "left table".
Matched rows in the "left table" and all rows in the "right table."
A right join returns any rows from the "left table" that match the join condition.
Right join retains all rows of the "right table", regardless of whether there is a row that matches the "left table".
A right join returns all the rows from the "right table", even if there are no matches in the "left table".
A right join shows rows for each record on the right-hand side, even if there are no matching rows on the left side. Likewise, if there is no matching row, the left side columns will show nulls.
Rows from the "left table" that don't match the join condition are returned as nulls.
In the above table, the Right Join configuration is applied and takes all the data from the right table and the matching data from the left table.
All data, including A, B, and D, form the right table (2) and matching A and B form the left table (1), and Row C which is not available in table 2 returns "null." in Blended table.
Best use case:
Aggregate products from different tables or data sources.
To identify the same data labels between the different tables (data sources)
If you want to get all unique rows in columns that exist in two tables
Let's assume we have Customers (all subscribers) data in table 1 and Lost Customers (or simply didn't renew their subscriptions) in table 2. With the Right Join, we can find all the lost customers from all subscribers in table 1, and we can simply filter out the null value to exclude all the customers with active subscriptions.
💡
Tablet orders (e.g. below: Data Blending Menu Explained) are important, and please make sure you have the correct order according to the data extraction objective.
If you switch the table order, the above Right Join configuration will have the same result as the first example Left Join table.
There are chances you never see the Right Join in practice, not because there's something wrong with a Right Join. We are inherently designed to read and write from left to right; only the two most well-known right to left languages are Arabic and Hebrew. When there is a chance to use Right join, you will simply switch the table order, which will produce the same result. E.g. in the above table, if we switch the table order Left Join will produce the same result.
3. Inner join: returns only matching rows from the left and right tables.
Phrase differently
Inner join — most commonly used join types
Inner join produces only the records that match in Table 1 and Table 2.
An inner join retrieves the matched rows only or returns matched records from both tables.
An inner join only returns rows where the join condition is true.
Returns the resultset that matches only the condition in both the tables.
An inner join will return only the rows that match based on the join key.
You use Inner join to return all rows from both tables where there is a match. i.e. In the resulting table, all the rows and columns will have values.
Inner join requires there is at least a match in comparing the two tables.
An inner join attempts to match up the two tables based on the join key you specify and only returns the rows that match. So, for example, if a row from the first table in the join matches two rows in the second table, two rows will be returned in the results. However, if there’s a row in the first table that doesn’t match a row in the second, it’s not returned; likewise, if there’s a row in the second table that doesn’t match a row in the first, it’s not returned.
In the above table, the Inner Join configuration is applied and returns the data if it matches in both table 1 and table 2.
A and B are present in both tables, and the blended table only returns these two values, and the rest is ignored.
Best use case:
Stats on only matching products from different tables
To identify only matching data labels between different tables (data sources)
Let's assume we have Leads (Potential Customer) data in table 1 and Customers (Made a purchase) in table 2. With Inner Join, we can extract paid customers (matching from the right table) and exclude all customers who have not made a purchase yet.
💡
Since an Inner join only includes rows that match the join condition, the order of the two tables in the join doesn't matter.
If we reverse the order of the tables in this example, we will get the same result
4. Full outer join: returns all matching rows from the left tables or the right table.
Phrase differently
A full outer join returns all the rows present in both the left and right tables, regardless of whether or not the other table has a matching value.
A full outer join returns the resultset of all the values from both the tables, even if there is a condition match or not.
A full outer join returns matched and unmatched records from both tables with null for unmatched records from both tables.
Full outer joins combine all the values from both the tables and preserve the non-matching rows from both left and right tables.
A full outer join will give the union of table 1 and table 2, i.e. all the rows in table 1 and all the rows in table 2. If something in table 1 doesn't have a corresponding data in table 2, then the table 2 portion is null, and vice versa.
You should use a full outer join when you also want to have all rows from both tables in your results.
In the above table, the Outer Join configuration is applied and returns all the rows present in both the left and right tables, regardless of whether or not the other table has a matching value.
Best use case:
Stats on the all products from different tables
To identify all data labels between the different tables (data sources)
Let's assume we have Leads (Potential Customer) data in table 1 and Customers (Made a purchase) in table 2. With Outer Join, we can combine both tables, and unmatched records from both tables will return as a null value, serving as a reference point for customers missing from one table to another or vice versa.
5. Cross join: returns every possible combination of rows from the left and right tables.
Phrase differently
Cross join — simplest of all but also very dangerous against large tables.
Cross join returns every possible combination of rows from the left and right tables. In other words, it will produce rows that combine each row from the first table with each row from the second table.
A Cross join would give you each row of the first table joined with each second table row.
Cross join will return the resultset in the number of rows in the first table multiplied by the number of rows in the second table.
Cross join "everything x everything", resulting in 3 x 3 = 9 rows. If you do the math, you can see why this is a very dangerous join to run against large tables. Similarly, if Table 1 has 100 rows and Table 2 has 100 rows will result in 10000 records.
In the above table, the Cross Join configuration is applied and returns every possible cross match from both table 1 and table 2.
Best use case:
Create a larger dataset for testing purposes
Let's assume you have two tables, Customer and Order. Customers have many Orders, and you want to create a view that gives details about customers and the most recent order they've made. This is how SQL Query would look like
SELECT *
FROM Customer
CROSS JOIN ( SELECT TOP 10 * FROM Order
WHERE Order.CustomerId = Customer.CustomerId
ORDER BY OrderDate DESC)
Data Blending Menu Explained: Google Data Studio
Join Configuration Conditions: Google Data Studio
How to create blended data sources (tables) in Google Data Studio?
There is two primary way to access and create blended data sources in the Google Data Studio interface:
Resource > Manage blended data > ADD A BLEND.
Select two or more charts, right-click & select “blend data”. This will create a blended data source with a default name plus a new chart. Rename the default name to keep track of blended data sources and the configuration applied to each blended source. E.g. "Left Join Leads & Customer Table"