Data blending in Looker Studio and how to overcome its limitations

Data blending is a great way to explore and make the most of your data in Looker Studio (formerly Data Studio). However, this feature also comes with some limitations that could slow your report down at best and affect your data accuracy at worst.

In this post, we’ll look at two workarounds using Google Sheets and Supermetrics.

Skip ahead >>

• Different types of join in Looker Studio
• How to blend data in Looker Studio
• The limitations of data blending
• How to overcome data blending limitations using Google Sheets
• How to overcome data blending limitations using the Supermetrics Hub
• Watch how data blending works in the Supermetrics

Different types of join in Looker Studio

Data blending is the process of merging different data sources into one single dataset. Data blending works when your joined data sources share at least one common dimension or a ‘join key’.

To blend data in Looker Studio, pick a join operator and join key. Your blended table shows different results depending on the join operator you pick. Currently, Looker Studio supports five join operators:

• Inner join means combining data from both sources — matching it where the join keys are the same and dropping the data that doesn’t match.
• Outer join means taking all the data from both sources — matching it where the join keys are the same. And finally, padding the non-matching columns with empty values in the joined table.
• Left join means taking all the data from the left table and the matching data from the right table where the join keys are the same.
• Right join means taking all the data from the right table and the matching data from the left table where the join keys are the same.
• Cross join is a special operation that results in a table with all possible row combinations from both tables. It doesn’t require join keys. Each row from table A is joined with every row from table B, resulting in a table with the number of rows A*B.

How to blend data in Looker Studio

Click ‘Resource’ > ‘Manage blends’ > ‘Add a blend’.

A configuration window will pop up, and you can choose the data sources you want to blend. For each data source, select metrics and dimensions. Remember, your sources have to have a common dimension, aka, your join key. To make it simple, you can use ‘Date’. Give your blended source a name.

Once you’re done, click ‘Configure join’. Choose the suitable join operation and make sure your join key is selected in the ‘Join conditions.’ Then, click ‘Save’.

Return to your Looker Studio dashboard and create a table with your blended source.

Watch this tutorial where Anna Shutko, Marketing Analyst Consultant at Supermetrics, explains how data blending in Looker Studio works.

The limitations of data blending in Looker Studio

While data blending is a great feature, it has many limitations that could slow down your reports or affect your results.

• You have no control over what’s happening under the hood: Unlike spreadsheets, you can’t see the details of how data is combined in Looker Studio, which can make it hard to find and fix errors.
• Slow processing and loading speeds: Data blending can significantly affect Looker Studio’s performance, with multiple blended data sources leading to slower loading times. This is because the blending process requires multiple API calls to retrieve data from different sources. The more blended data sources you add, your dashboard will be slower.
• A limited number of blended sources: Another frustrating limitation is that you can blend a maximum of five data sources. While this number sounds like a lot, it isn’t. You’ll easily cross the limit if you want to create a very detailed table with many columns.

That’s why if you have a lot of data, it’s better to blend it before feeding it into Looker Studio. Next, we’ll walk you through two ways to blend data using Google Sheets and Supermetrics.

How to overcome data blending limitations in Looker Studio using Google Sheets

When data blending in Looker Studio becomes a bit of a hassle, you can blend your data in Google Sheets and bring it back together in Looker Studio for reporting. 

This approach gives you more flexibility with your data. You can take advantage of the Google Sheets formulas to enrich your data. Additionally, it’s much faster to load blended data from a Google Sheet than from several sources.

Bringing data into Google Sheets

If you want to import data from Google platforms like Google Analytics 4, Google Ads, Google Search Console, etc. You can use the native connector. They’re free.

However, in case you want to bring data from other marketing sources besides Google, such as Facebook Ads, LinkedIn Ads, etc., it’s better to use a third-party tool like Supermetrics. This way, you can:

• Move data from +150 marketing sources
• Set up automatic refreshes and emailing so you get notifications whenever a certain metric changes
• Access to free, premade marketing reporting templates.

Managing your data in Google Sheets

It can get messy quickly when you bring data from different sources to Google Sheets for blending. Dividing them into separate tabs is a good way to stay organized with your data.

The ‘raw data’ tab is where you store all your unformatted raw data from your data sources. In this example report, we use Supermetrics to pull data from Facebook Ads, Microsoft Ads, and Google Ads into three separate tabs.

The ‘blended data’ tab is where the magic happens. You can match your data together and perform some calculations to get more insights from your data.

The ‘reporting data’ tab is where you put the last piece of the puzzle. When you’re done enriching and transforming the data, you can present them in a separate tab where it’s easier to monitor.

Additionally, you can connect the ‘reporting data’ tab to Looker Studio to bring the final results to your dashboard. You can find the Google Sheets connector in the connector gallery.

Three useful functions for joining data in Google Sheets

VLOOKUP

VLOOKUP is one of the most used functions for data joining. It lets you search for a value in one table and use it in another table.

The syntax for VLOOKUP is:

VLOOKUP (search_key, range, index, [is_sort])

• search_key: the value you want to look up.
• range: the range that contains the value you want to look up. Note that VLOOKUP will search from the first column in your range.
• index: the column number (within your chosen range) that contains the returning value.
• is_sort: this parameter is optional. Here, you can specify if you want to receive an exact match (FALSE) or the nearest match value (TRUE). In the case of data joining, you’ll want to set it to an exact match.

You’re telling Google Sheets what value you want to search for, where you want to search for it, the column number in the range that has the value to return, and finally, if you want to receive an exact match (FALSE) or the nearest match (TRUE).

Let’s say you have two tables:

• A marketing table with data about date, source, medium, campaign, impressions, cost, and clicks
• A conversion table with data about date, source, medium, transactions, and revenue.

There are two steps to connecting the puzzles.

First, you need to create composite keys for two tables using the TEXTJOIN function. Each composite key can be used to uniquely identify each row of the table. Without the composite keys, you’re likely to run into one-to-many relationships. Additionally, you can use them as join keys for VLOOKUP.

Your composite keys will include the campaigns’ date, source, medium, and campaign (which means campaign name in this case). It’ll look something like this.

Next, use VLOOKUP to join two tables. For example, the formula for combining transaction data with the marketing table is:

VLOOKUP($A4,$A$22:$J$33,6,0)

IF + REGEXPMATCH

The first step is to remap the campaign name to new values with an IF function (columns F and N).

That new cleaned-up name is then used as a join-key to generate the metrics table on the right side of the sheet, where metrics from two sources are aggregated together where the previously remapped campaign name matches.

The function we’re looking at next is a nested function — IF + REGEXPMATCH, where

• IF starts a conditional evaluation.
• REGEXPMATCH checks the target for a text match

Let’s take a look at the table below. As you can see, it has different naming conventions, for example, ‘Google Data Studio’ and ‘googledatastudio’, or ‘Enterprise’ and ‘enterprise’. 

You can put all your Google Data Studio campaigns in one basket and Enterprise campaigns in one basket using this formula

=IF(

REGEXMATCH(A7,"Data Studio|datastudio"),"Data Studio Campaigns",

IF(

REGEXMATCH(A7,"Enterprise|enterprise"),"Enterprise campaigns"

)

In simpler terms, your function searches in column A7 for ‘Data Studio’ or ‘datastudio’ and returns ‘Data Studio Campaigns’. If there is no such value, it will search for ‘Enterprise’ or ‘enterprise’ and return ‘Enterprise campaigns’. 

You can remap campaign names from different sources and use them as your join key.

Conditional aggregation

In Google Sheets, you can use different aggregation functions to summarize your data — calculating the sum, average, or counting the number of data points. But, in reality, you may not want to aggregate all your data. In that case, you can use conditional aggregation to specify which data you want to aggregate.

Conditional aggregation is a function that tells Google to perform data aggregation over a set of data when it meets certain criteria. We’ll take a look at some common conditional aggregation functions.

The SUMIF function tells Google to calculate the sum of the data that meets a predefined condition in a range. The syntax for the SUMIF function is:

SUMIF (range, criterion, [sum_range])

• range: you want to specify the data range you want to apply the condition to.
• criterion: you should specify the condition that defines which cells will be summed.
• sum_range: you should specify the range to be summed if different from ‘range’. This is optional.

Take the table below as an example. Let’s say you want to calculate the impressions from the US. You can do so by using SUMIF (B3:J12, “US”, D3:D12).

The AVERAGEIF function returns the average value of data that meets certain criteria in a range. The syntax for the AVERAGEIF function is:

The AVERAGEIF function returns the average value of data that meets certain criteria in a range. The syntax for the AVERAGEIF function is:

AVERAGEIF (criteria_range, criterion, [average_range])

• criteria_range: you should choose the data range you want to apply the condition to.
• criterion: specify the condition that defines which cells will be averaged.
• average_range: you should specify the range to be averaged if different from ‘criteria_range’. This is optional.

For example, if you want to calculate the average cost from the US, you can use AVERAGEIF(B3:J12, “US”, E3:E12).

Similarly, the COUNTIF function performs a conditional count over your data. The syntax for COUNTIF is:

COUNTIF (range, criterion)

• range: the range you want to count
• criterion: the condition you want to apply

For example, you want to count how many countries have CPC greater than 1. You can do so by using COUNTIF(H3:H12, “>1”)

An easier way to blend data in Looker Studio: Using the Supermetrics Marketing Intelligence Cloud

The Supermetrics Marketing Intelligence Cloud is a centralized platform that allows you to consolidate, transform, and move data to any reporting and analytics destination. It’s also easier and quicker way to blend your data.

Data blending in Supermetrics

Whether you’re an analyst looking for a more effective way to blend your data or a non-technical marketer who isn’t familiar with all join logic, you can easily blend your data and bring it to Looker Studio. With the Supermetrics Marketing Intelligence Cloud, you can:

• Blend more than 150 data sources—including paid, social media, web analytics, etc.
• Review your blended fields so you can verify how they look before using them in your report
• Modify the blended sources and configure the field mapping to fit your needs
• Use the created blends in all your reporting tools

Additional resources:  Learn 7 ways to transform your data using Supermetrics.

Getting started with data blending in Supermetrics

Here’s how you can use the data blending feature in Supermetrics.

1. Log in to the Supermetrics.
2. Choose ‘Transform’ → ‘Data blending’ on the left side of the screen.
3. Choose ‘Create new blend’.
4. Select the data sources you want to combine, then click ‘Continue to configuration’ and follow the instructions to configure the data sources.
5. After that, name your blended source, then click ‘Create blend’.

You’ll see your blended fields.

After that, your blended source will be available in your analytics and reporting destination. Let’s say you’re using Looker Studio for reporting.

1. Click ‘Back to blend list’.
2. Select ‘Use your blends in destinations’.
3. Once the window opens, choose ‘Looker Studio’ → ‘Go to connector’.
4. Follow the instructions to authenticate your data sources.

After that, you can easily build reports with your blended sources. To learn more about how data blending works, check out our support center.

Watch how data blending works in the Supermetrics

Over to you

Despite its limitations, Looker Studio is still a great tool for visualizing and sharing your reports. If you’re looking for an easy way to do data blending, contact us.