10.2. Left Outer Join in Looker Studio
In this section, we will explore the left outer join in Looker Studio with a real-life example.
Left Outer Join Recap
A Looker Studio Left Outer Join returns all rows from the left table and the matching rows from the right table.
If there is no match in the right table, NULL values will be returned for the right table columns. The primary goal of a left outer join is to enrich, widen, or add to the left table.
Example: Sales Performance by Sales Manager
Let's consider a company with sales managers, each responsible for a specific city. The company wants to analyze the sales performance of its sales managers per million population in their cities. To do this, we need to blend two tables:
- The left table contains sales data, including the city, sales manager, and sales amount.
- The right table contains population data for each city and state.
Our goal is to calculate the sales per million population for each sales manager by dividing the sales amount by the city population.
Blending the Tables in Looker Studio
- In Looker Studio, use data blending to create a blended data source by configuring the left table (sales data) and the right table (population data). Ensure that the left outer join is selected and that the join condition is based on the city value in both tables.
- Once the blend data source is configured, the resulting table will include columns for the city, state, sales manager, sales amount, and population.
- Create a calculated field called "Sales per Million Population" using the formula:
SUM(sales) / (SUM(population) / 1,000,000). This field is now available in the blended data source.
- With the blended data source prepared, you can visualize the sales performance of each sales manager per million population. Remove unnecessary columns (city, state, sales amount, and population) to focus on the sales manager and sales per million population.
- Customize the table visualization by displaying the sales per million population as a bar chart with compact numbers and a target value representing the desired sales performance for each sales manager.
Now, the company can easily assess the sales performance of its sales managers, taking into account the population of their respective cities.