How to Blend Data in Looker Studio

Updated 10/19/22 with new information.

Are you creating charts on Looker Studio (formerly known as Google Data Studio) using multiple data sources and running into problems with data blending?

In this blog post, we’ll introduce you to exactly how data blending works in Looker Studio, note the considerations that limit our blending abilities, and share some tactics we’ve successfully implemented to resolve data blending issues. If you’re new to Looker Studio and would like to learn more, please check out our blog on getting started with Google Data Studio.

What Is Data Blending in Looker Studio?

Looker Studio’s data blending feature allows users to combine data and calculate metrics across data sources without writing code. It is a powerful function and yet requires some understanding of fundamental SQL join types.

You can use data blending to create any visualization offered in Looker Studio for a complete view of performance across different sources. You can also blend data sources to combine metrics across multiple properties like cost, revenue, and transactions, by joining matching dimensions like campaigns, keywords, and device types.

Looker Studio allows users to easily create a Data Blend in a report by simply clicking multiple scorecards or tables, right-clicking, and selecting ‘Blend Data.’ This method creates the Data Blend for you with the selected metrics and sets the first data source you selected as your left-most source.

In the screenshot below, click the Cost scorecard first and press the command key on mac or the control key on pc. Then, click the Clicks scorecard and right-click to select Blend data. That’s how you blend the Cost/Clicks scorecard, but you need to adjust the metric type from percentage to currency.

Why Use Data Blending in Looker Studio?

Combine Metrics Across Platforms

I use and build automated reports extensively for client work, and data blending is undeniably the most useful feature of Looker Studio. More often than not, I run into clients who have multiple Google Analytics properties or Google Ads accounts, are running Paid Social ads on multiple social platforms, or offer their services or products within a website and across applications. 

Clients and analysts should look at combined performance metrics for efforts with the same goals or budgets, regardless of how the data collection is disjointed. Check out my blog post on the marketing metrics you must track to determine if you’re tracking the right ones.

Better Performance Reporting

Blending data in Looker Studio allows you to combine metrics reported on different platforms that should be viewed as a total (ex., app conversions in Firebase and website conversions in Google Analytics). You can provide performance for an overall budget spread across more than one source or medium (ex., Facebook Ads and LinkedIn Ads). Ultimately, data blending provides holistic reporting on performance.

When to Use Data Blending in Looker Studio

Data blending should be used when you need to view combined data for analysis or reporting purposes. Below are some example use cases for data blending in Looker Studio, just in case this sounds overly general or doesn’t seem like it will ever apply to you—which it will.

• You’re running Google Ads across multiple accounts with the same overall budget.
• You’re running Bing Ads and Google Ads and need to report on combined Paid Search performance.
• You need to report on Bing Ads Ads cost metrics by campaign but report on goal completions out of Google Analytics.
• You want to report on Paid Social campaign metrics and goal completions from Google Analytics.
• You’re running Paid Social ads across multiple platforms from the same Paid Social budget.
• You manage multiple subdomains or sister sites for the same account.

What Can You Blend in Looker Studio?

Conceptually, you can blend just about anything in Looker Studio as long as the data source is in an available Looker Studio connector and there’s at least one dimension in common (i.e., a Join Key) among your data sources. Your data can live in a Google Sheet, SQL database, or even be a simple file upload to connect to Looker Studio, and custom fields can be added to create matching Join Keys.

Unfortunately, it’s not as easy as we’d like to blend any data with data blending in Looker Studio with these liberties. There are limitations outside the two main requirements (data source available in Looker Studio and Join Key) to return accurate metrics in your reports.

How to Blend Data in Looker Studio

Step 1: When in Edit mode, select the chart you need to work on and click the BLEND DATA button under Data source to add more data sources to the blend.

Step 2: Click Join another table, search and select the data source.

Step 3: Select the dimensions and metrics or create calculated metrics as needed from each data source with or without filters.

Step 4: Configure join by selecting the join operator and matching dimension(s) for the join conditions, then save.

Step 5: Rename the Data Blend and click save.

Different Types of Join Operators

Left Outer Join was only initially available on Looker Studio for data blending. In early 2022, Looker Studio released other advanced join types. There are now five join configurations available for data blending in Looker Studio. If you have experience with SQL, the following join operators should be fairly straightforward.

Inner Join (New)

It will return the matching rows from the left and right tables. In the screenshot below, only Campaign 1 and Campaign 2 match in Data Source 1 and Data Source 2.

It will return everything from the left table plus the matching rows from the right table. The screenshot below shows all campaigns from Data Source 1 plus the matching Campaign 1 and Campaign 2 from Data Source 2. This is a default join type when you start data blending.

Right Outer Join (New)

It will return everything from the right table plus the matching rows from the left table. The screenshot below shows all campaigns from Data Source 2 plus the matching Campaign 1 and Campaign 2 from Data Source 1. 

If you shift the right table to the left, you’ll get Left Outer Join.

Full Outer Join (New)

It will return all rows from the left or right table, resulting in potentially very large datasets. The screenshot below shows all rows from Data Source 1 and Data Source 2.

Cross Join (New)

It will return all possible paired combinations of each row in Data Source 1 with each row in Data Source 2.

Challenges of Data Blending in Looker Studio

Limit of Five Data Sources

Five data sources sound like plenty, but there are many situations when you need more than that to blend together. I’ve run into it quite a few times, and the only solutions have been to either forgo some metrics or combine data sources into one through Google Sheets or by importing data from one platform to the other.

Some examples where I’ve run into problems because of this limitation:

• Combining Bing Ads Cost data (Data Source 1) with Google Analytics conversions (Data Source 2) across more than two accounts or properties.
• Combining Google Analytics metrics across more than five different properties.
• Combining Assisted Conversions data with Google Analytics last-click conversions across more than two accounts or properties.

Common Data Blending Issues and How to Resolve Them

The issues I often run into in Looker Studio are easily resolved with a simple workaround. It seems Looker Studio is still ironing out logistical kinks for functionalities that should work seamlessly on their own. The following solutions and hacks have solved my blending issues so far.

Data Returning “Null” Values

Looker Studio will return “null” (-) values when any row of a dimension has no value associated with it. In the example below, the ‘Leads’ column returns null values and misses conversions from the second data source in the data blend due to the Left Outer Join operation.

The ‘Leads’ column is missing a conversion because the First Interaction Assisted Conversion (which is from the second data source) occurred for a keyword that did not return any values under the first data source. This results in inaccurate reporting! You can see that the total number of Leads under the ‘Leads’ column does not add up to what Looker Studio is reporting as the ‘Grand total.’

This issue can be resolved by creating custom metrics in the table that tell Looker Studio to either return the max metric for that field or return a 0 (instead of null). Use the formula below to create this metric:

NARY_MAX([FIELD],0)

Replace [FIELD] with the metric and use the SUM([FIELD]) calculation if you need an aggregated metric. This is the formula used in the above example:

NARY_MAX(SUM(Goal Completions),0)+NARY_MAX(SUM(First Interaction Assisted Conversions),0))

Returning 0s instead of nulls allows Looker Studio to combine the metrics and return a value, even if there are no values for one of your combined metrics. It’s a silly workaround, but it’s what makes sense with Looker Studio’s current configuration.

Another solution I’ve seen for this is creating a custom metric using a CASE WHEN formula, telling Looker Studio to either return 0 when the value returns null or to return the field:

CASE WHEN [FIELD] IS NULL THEN 0 ELSE [FIELD] END

Data Returning Incorrectly Calculated Metrics

I highly recommend always viewing your combined metrics by the Join Key in a table format before putting it into a scorecard. Viewing it in a table allows you to check that the correct metrics are being pulled in from each source before combining them.

When you’ve noticed that your report is calculating metrics incorrectly, comb through the following checkpoints:

1. Make sure the naming is the same for your Join Keys. I’ve tried using Device Category as a Join Key between Bing Ads and Google Analytics before realizing that the naming conventions are different. Bing Ads uses Computer, Tablet, and Smartphone instead of Desktop, Tablet, and Mobile, which are used in Google Analytics. Use a CASE WHEN formula to return the same dimension names in one data source to match the other. For example, you can create this custom field in your Google Ads data source:
2. Make sure the formatting is the same for your Join Keys. This is specific to dimensions like Date. Although the Date dimension may be called the same and look the same in their platforms, they may be formatted differently in Looker Studio. Pull your data into a table with the date set as the dimension and check the formatting of your dates. You may find that one is YYYYMMDD, while the other is MMDDYYYY. If reporting on a monthly basis, you should attempt changing the ‘Show As’ under each date in every data source to ‘Year Month.’
   

Wrap Up

I hope these tips and tricks are helpful when you need to do data blending on Looker Studio for reporting. Since Looker Studio keeps rolling out new features, there will likely be new issues, and hopefully, some existing ones will get resolved.