Benard Kiplangat May 28, 2022
Understand the limitations of data blending in Looker Studio and learn the tips to get past those limitations. You will make a remarkable impact and get the most valuable insights from your data sources.
Looker Studio is undoubtedly the best free tool to visualize your data from different sources. It helps you unlock invaluable insights and possibilities with its powerful tools and functionality, like the data blending feature. Furthermore, data blending in Looker Studio gives you an unmatched competitive advantage for your company, thus taking your business to the next level.
However, like many other Google tools, it’s easy to get by — but rock hard to master. Features like data blending in Looker Studio are easy to get their advantages or capabilities, but only the pros understand their common pitfalls.
Thankfully, we will talk about the seven limitations of data blending in Looker Studio and how you can overcome them. Are you ready? Let’s get started.
But first, the basics:
What is Data Blending in Looker Studio?
Data blending in Looker Studio is the process of merging multiple data sources into a single dataset for you to create charts, tables, and controls for your data visualization. For you to join several data sources into a blend, you need to understand the concept of data joining. So, what is it?
Understanding data joining in Looker Studio
Because this is a big concept of data blending in Looker Studio, it is often a challenge that many marketers find confusing. Blending two tables in Looker Studio requires a join configuration with a join operator and join condition. This is how the join configuration window looks like in the Looker Studio:
Data joining is the process of merging two data sets, side by side, with a shared data column from each dataset. Before 17th Feb 2022, the join configuration was limited to the Left Outer join configuration.
The new join configurations in Looker Studio now include four other join conditions:
- Inner join – returns only matching rows from both the left and right table.
- Left outer join – returns the matching rows on the right table with the non-matching rows on the left table.
- Right outer join – returns the matching rows on the left tables with the non-matching rows on the right table.
- Full outer join – returns all the matching rows on either the left or right table.
- Cross join – returns every possible combination of rows from the left and the right table.
The Six Main Limitations of Data Blending in Looker Studio
Data blending in Looker Studio is limited to five data sources
Another limitation of data blending in Looker Studio is that you are limited to five sources. If you have over five sources you want to merge, you’ll not get a chance with Looker Studio.
Therefore, you may miss out on advanced or in-depth reports that require more sources to create a detailed table with too many columns. If you need more than this, consider using Google Sheets.
- Pro Tip: Use Dataslayer for Looker Studio to unlock over 40 data sources for blending
Looker Studio does not handle any missing or miscalculated values automatically
While Looker Studio will show you when your blend has a configuration error, it will not fix these errors automatically. Such errors may result from a broken calculation or a missing field in the join keys.
For example, the missing fields will become ‘null.’ GDS will not replace them with zeros ‘0’ or any other custom values for you if you don’t do so before data blending in Looker Studio.
In case of such configuration errors, you will see a pink ‘missing’ field in the join condition or a red box around the join configuration for mistakes in your tables.
Consider using pre-blend filters and calculate your fields accurately, or apply the fix manually to ensure all the join keys are valid and matching for data blending in Looker Studio.
Blending can create unnecessarily large data sets
In Looker Studio, without strict control on what data to include in your blend, your blend can quickly generate a large dataset with too many fields. These unnecessary datasets often slow performance and reduce your productivity. Also, you could incur considerate costs if you query more and more data through your paid connections or API services.
Therefore, your blends should include only the fields you want to visualize, which may be just a subset of your data. Also, be wary of the cross-join operations, as it may cause too much data in a blend.
Blend charts calculate all rows even if they’re unused
When you work with blends, you will often encounter the error ‘This chart requested too much data.’ As we’ve mentioned, if you generated too much data in your blend, you may have an issue visualizing them into charts. But why?
This limitation happens because a chart in a blend will calculate all the rows available even if it will never use it. That means, Looker Studio will calculate all the fields in your data and use only those you selected.
Therefore, think more before using join operators like cross join and include only the fields you need for your data blending in Looker Studio to avoid these errors.
Blend data may be wrong
It is common to find that the results of your blend data are not those you expected. This inaccuracy can be very hard to notice if you work with complex data blending in Looker Studio. This issue often results from Looker Studio’s way of creating the tables for your blend.
Data Studio will query the data for each table’s data before joining them into a final blend with your preferred join condition. If you applied the date ranges, filters, or calculated fields on your tables before joining them can explain the different output for your data blending in Looker Studio.
That could typically be the reason. Otherwise, Looker Studio does this in the background, so you are limited to seeing the logic of what could’ve probably gone wrong.
You can try small pieces of data before data blending in Looker Studio of your complex datasets. Also, ensure that all your pre-calculated tables and filters are accurate.
Connector usability depends on internal database schema
While Looker Studio offers several connectors for you to use for data blending, each connection often provides a single link for each of your properties. Therefore, your preferred data sources may not offer more connection property choices for different data segments.
You can integrate your data sources directly with the APIs instead of the connectors to get more flexibility and avoid these limitations. That way, you can customize the structure of your data sources the way you like.
How to Overcome Limitations of Data Blending in Looker Studio
Google Data Studio is powerful for data visualization. While blending in Looker Studio has some drawbacks, they are easy to workaround. And basically, there are tips to work some of them in Looker Studio. Other complex workarounds require you to use Google Sheets, an excellent tool for data manipulation.
So, Google Sheets will be our go-to tool to handle all the limitations of Looker Studio. Let’s start with the tips.
General tips to follow while blending data on Looker Studio:
- Pick well-understood sampled data to test and strengthen your understanding of blending.
- Ensure that all your tables are in the exact order you want them to be.
- Be sure that the number of dimensions in each of your tables is minimal, when possible.
- Ensure that you’re using the correct filters and calculated fields.
- Start with fewer tables, like two, then keep adding others while watching the results.
- Don’t use a complex join condition at first, especially one that results in as few fields as possible.
How to use Google Sheets to overcome the limitations of data blending in Looker Studio
Most, if not all, of the limitations of data blending in Looker studio, can be easily fixed with Google sheets. It can be a complex process, but it is worth it.
That way, it would be helpful for you to blend your data in Google sheets, then take it back to Looker Studio for reporting. In Google Sheets, you’ll get more flexibility and control over your data than it would have been in Looker Studio.
Furthermore, loading data is easier, faster, and more manageable in Google Sheets. Google Sheets offer great formulas to manipulate your data automatically without too many hassles.
So let’s roll our sleeves and get to know some useful functions for data joining in Google Sheets. Now, you can analyze your data in a single table and get meaningful insights.
Read more: Why Google Data Studio is So Slow and How to Fix it
Understanding Data Blending functions in Google Sheets
After pulling your data from your favorite sources with Dataslayer, what you have to do next is to work on your data. Here are some of the powerful functions to help you in Google Sheets:
VLOOKUP
Scouring for information from a large data set can be daunting. Thankfully, the Google Sheets VLOOKUP function is available to help you look for your data. This function is particularly useful when dealing with multiple data sources — it is more effective no matter the number of lines in your data.
VLOOKUP is a vertical lookup function, meaning it looks up for specific values in vertical columns. To utilize this function efficiently, use columns as your fields. Additionally, VLOOKUP is for exact matches only, so don’t think about using it for approximates.
The syntax for VLOOKUP is:
VLOOKUP(search_key, range, index, [is_sort])
Here, the searck_key is the value that you want to search for. The range is where you want to search it from, and the index is the column index of the value that will be returned. Then, the last parameter is if you want the exact match (set to false or ‘0’) or a loose match (set to true or omit).
- Pro Tip: Use absolute reference to search for the value and drag the formula across other cells to reuse the function.
Here’s the best article to help you learn How to use VLOOKUP.
INDEX AND MATCH
Use the MATCH function to find cells with approximate values, and the INDEX function to get the value of a cell with a specified index. Therefore, you can both to give you better results where the VLOOKUP could not effectively look up for approximate values.
The syntax for INDEX is:
INDEX(reference, [row], [column])
The syntax for MATCH is:
MATCH(search_key, range, [search_type])
Syntax for using both INDEX and MATCH is:
INDEX(reference, MATCH(search_key, range, [search_type]), [column])
Here’s the best article to help you learn How to use INDEX and MATCH
Macros
Automation is king nowadays. Macros can help you define custom functions to help you automate your repetitive reporting actions. This functionality will save you hours of doing repetitive tasks, especially on larger, everyday tasks. You’ll save time and energy while minimizing mistakes when working on your data.
Setting up macros is easy; you can write them or record your actions to get more control and flexibility in your data analysis.
If you need a comprehensive guide to setting up macros in Google Sheets, check out this article: Google Sheets Macros.
- Tip: After blending your data on Google Sheets, you can now connect it to Looker Studio for reporting.
Next steps
Data blending is an advanced topic. While it is a challenging concept, we hope this article has been resourceful for you. There are countless ways you can use the blended data for your reporting. Hopefully, data blending in Looker Studio’s limitations did not stop you from getting the most out of it.
We have also shared some tips and a few Google Sheets functions to help you overcome those limitations of data blending in Looker Studio.
The list of functions that we shared is, however, not exhaustive — but they’re the core functions that can meet most of your data blending in Looker Studio needs. So be sure to keep growing your skills for handling your marketing data creatively.
Also, check out how you can use Dataslayer to help you pull all your marketing data from different sources to your favourite go-to reporting platforms, be it Google Sheets, BigQuery, or Looker Studio.
