10.9. UNION (with Full Outer Join)
A UNION operation appends two tables with the same schema together. It's like taking the second table and appending it right at the bottom of the first table. The fields don't need to have the same name, but their types should match. Since Looker Studio does not have a built-in UNION operation, we can use a full outer join to recreate a similar result.
How to Achieve Union Using Full Outer Join
Imagine you have two tables containing landing page and session data from two different Google Analytics websites. Your goal is to combine these tables so that you can see the total sessions for each landing page.
To achieve this, start with a full outer join. The resulting table will have all values from both the left and right tables, and then you can work around it to create a similar result to a UNION operation.
Combining Landing Pages
Use the coalesce function to combine the landing page columns. This will create a single column containing the actual landing page from either the left or right table.
Combining Sessions
When working with numbers, you can use the IFNULL function to handle null values. This function checks if a value is null, and if it is, it replaces it with a specified value (usually zero). By using IFNULL on the sessions columns from both tables, you can safely add them together without encountering issues with null values.
Example: In Facebook ads data, if you don't have a cost for a day, it returns null. When you sum five values together, if one of them is null, the result will be null as well. Using the IFNULL function, you can replace null values with zeros and avoid this problem.
Now you have a table that is similar to the result of a UNION operation, where you can see the combined landing page data and total sessions for each page, without losing any information from the original tables.
Additional real-life example: You might have two separate e-commerce websites for different markets, both with the same structure and similar products. By performing a UNION-like operation using a full outer join, you can analyze the combined data to get insights into the performance of specific landing pages across both markets.
📩 Receive my weekly Looker Studio tips
🎥 Subscribe to my YouTube channel
🖇 Connect with me on LinkedIn