On February 17th Google released a Looker Studio update which, in my humble opinion, will cause sooo many beginners to have gray hairs before their time. This Is Google’s Update Docs
We are going to dive into the practical applications behind the new Blending modes and situations they might be helpful!
What is blending?
Blending In Looker Studio takes two to five Data Sources and combines them using a field or fields to link them together. How you would like to deal with “Missing” data is how you will determine your Blending mode. Now including: Inner join, left outer join, right outer join, full outer join, cross join. I am already confused and I know how to do this.
For reference, this is Google’s Description
- Inner join – Returns only matching rows from the left and right tables.
- Left outer join – Returns matching rows from the right table, plus non-matching rows from the left tables.
- Right outer join – Returns matching rows from the left tables, plus non-matching rows from the right table.
- Full outer join – Returns all matching rows from the left tables or the right table.
- Cross join
My prediction is 95% of the time people will still use Left_Outer_Join. But we will hop into the why now!
JUMP STRAIGHT TO REAL WORD EXAMPLES
Important Words You Will Encounter
Blending = Combining two or more tables within Looker Studio.
Join – Same thing as Blending just a different word
SQL – This is a coding language which the Join/Blend came from.
Join Key = The dimension in your Data Source you will use to combine them together.
Data Source = The data being referenced within GDS.
Dimensions = The breaking down of your metrics – Read more here
Metrics – The numbers you are trying to pull – Read more here
Left Join – Uses the left most table in your Join (it’s the basic blend that used to occur)
What? How? I am Already Confused!
Don’t worry, this is a beginners guide so we are going to take it slow, and give you the groundwork you need to hop on your next blending project with gusto!
The goal is that you can be blending like the best of them without being overwhelmed with options.
Step 1: Everything is a Table
First we need to understand that every single piece of information GDS can reference is a SINGLE TABLE. If you want to have two two tables referenced at the same time you will need to use a blend.
Example: Imagine you have:
- Table 1: Date & Facebook Ad Spend
- Table 2: Date & Google Ad Spend
But you want to know Total Ad spend over each date…. Blending to the rescue!
You will want to combine those two tables using “Blend” and “Date” will be the join key as both tables have the same.
Left Outer Join – Default & Most Commonly Used
The left outer join does the following:
- Loads the left most table
- Then matches the right table to the left tables key.
This Means: If your left table does not have the key, it will return Null from the right table.
If your left table only has Mon, Wed, but your right table has Mon, Tues, Wed… Say Goodbye to Tuesday’s data because there is nothing to match it to on the left hand side.
In this image by GoLinuxCloud you can see that left table is the priority, then the right is sort of added on.
Right Outer Join – Same thing but reversed
Following the naming convention, this is the exact same as “Left Outer Join”….
I have 0 idea why someone would need to use this within GDS as its just flipped and using the right most data source.
Remember, in GDS you can drag and drop your data sources to be on the left or right!
If you know a valid reason to a right outer join (other than because you can) please email me at jj(at)lookerstudio(dot)vip
Inner Join – ONLY THE MATCHING
Returns only the matching records from both of the tables you are referencing.
So if there is no Tuesday on the right table, neither one is returned to your new master table.
Example: You have a list of Customers who purchased online & a list of customers who purchased from your POS system in the store.
You want to just see the average cart value of the customers who have purchased both online.
BOOM! Use Inner Join & you have a new table with just the customers who are on both lists! Easy peasy.
Full Outer Join:
This will be your go to when blending less consistent datasources. Like Ad Spend on all platforms.
The full outer join returns all the rows from the left table and the right table. NO MATTER WHAT.
Things to be careful about with Full Outer Joins in GDS :
- If your join key is formatted differently, you might double the number of “join Key” dimensions you have.
- If keys get switched up, you might actually end up over reporting.
Cross Join
Returns every single combination of rows you can make by smashing two tables together…. I have never used this. But it now exists in GDS.
Microsoft gives a great explanations [here](https://docs.microsoft.com/en-us/power-query/cross-join#:~:text=A cross join is a,row from the second table.)
But here is what we need to know.
List 1:
- Blue
- Red
- Yellow
List 2:
- Shirt
- Pants
New Output
- Blue
- shirt
- Blue
- pants
- red
- shirt
- red
- pants
- Yellow
- shirt
- Yellow
- pants
Real Life Use Cases for Looker Studio Blending
So here is what you really want to know. How Can I apply this to my real life?! I got you!
Facebook Ad Spend & Google Ad Spend
Imagine you are spending money on multiple channels, Facebook and Google. You want to show TOTAL AD SPEND in a single Score Card.
Here you would want to use a Full Outer Join.
Why?
Because you want the Value of that spend even if 1 platform isn’t spending anything. If you were to use a left join and you stopped spending money on Google (the left most join) then you wouldn’t return 0 even if you spent $100k on Facebook.
CRM to Transaction Time In GDS
Scenario: You want to calculate the number of days between Custom Creation in your CRM & Stripe Transaction time in Looker Studio.
Here you would want to use Left Outer Join & place your CRM List on the far left.
Why?
Because you know that your CRM users are on the Stripe list You will always be able to Join the user to a transaction, If you were to put it the other way (stipe left), you would not know the number of customers who did not buy.
Filters With Blending
My Final Thoughts
This is going to get confusing for the vast majority of GDS users. Joining is complicated and a great way to F things up. But it’s also a great way to keep things moving forward without having to undo your entire pipeline to just redo the same functions within a Database like Big Query.
