How to Blend Data in Google Data Studio [Guide 2023]

How to Blend Data in Google Data Studio 30

Let’s talk about blending data sources. In this article, we will learn how to blend data in Google Data Studio. Blend means what it suggests in general. Blending data means mixing data, and combining the data from more than one data source. In Google Data Studio, we need this feature frequently, when we are creating a report that has data from so many sources like social media data, Google ads data, Google analytics data, and maybe our custom google sheets file data. Data Studio gives us the power to blend these data sources and make a combination of data and metrics in various ways. So, today this is our topic, we will learn data blending of more than one source, and we will also learn how to perform operations on blended data.

Why use Blending Data in Google Data Studio?

Blend data is one of the features that make us transition from a basic marketing reporting tool to an advanced business intelligence solution. Features like blend data allow us to answer the questions that are most important in business intelligence, such as you are working for a client as a digital marketer, and your client asks you, what is my total ad expense? Now you are managing multiple ad campaigns and platforms like Google, Facebook, LinkedIn, and so on.

Now here, you need the blend data to feature to answer these questions, there can be so many questions like that from your client, from your boss, and maybe from your customers as well, so knowing that this is something very intelligent to use when working with Data Studio, you just need to learn how to blend data in Google Data Studio.

See also  How to Create Field in Google Data Studio [Complete Guide]

How to Blend Data in Google Data Studio?

So firstly, to use blend data we need to have multiple data sources, so here, I will not use any sample data sources, because they are not editable and we can face some type issues, instead, I have a custom google sheets file, in which I have three sheets, I have added dummy data for Facebook ads, Instagram ads, and LinkedIn ads on it. I will add this one sheet into Data Studio, a single sheet each time, and then we will blend this data. We will use two charts for visualizing the blended data.

Blend Data in Google Data Studio – Add Custom Sheets File to Blend Data Manually

In this first section, we will first learn how to add a custom sheets file, I have only one file with multiple sheets in it having ads data for different platforms, so you add the same file again and again, we can do that easily. Simply follow the below steps.

Step 1

Open Data Studio

How to Blend Data in Google Data Studio 1

Step 2

Create a blank report

How to Blend Data in Google Data Studio 2

Step 3

Select Google Sheets as a data source

How to Blend Data in Google Data Studio 3

Step 4

Select your file name

How to Blend Data in Google Data Studio 4

Step 5

Select the sheet name

How to Blend Data in Google Data Studio 5

Step 6

Click on Add

How to Blend Data in Google Data Studio 6

Now you have added your first sheet of that file, other sheets cannot be used inside Data Studio, so we need to add other sheets separately

Step 7

Go to Resource > Manage added data sources

How to Blend Data in Google Data Studio 7

Step 8

Click on “Add a data source”

How to Blend Data in Google Data Studio 8

Step 9

Again, select Google Sheets > then the second sheet of your file

How to Blend Data in Google Data Studio 9

How to Blend Data in Google Data Studio 10

Step 10

Repeat steps 7-9, and this time select the third and last sheet of your file.

How to Blend Data in Google Data Studio 11

Step 11

Now all three sheets have been added, you can verify here.

How to Blend Data in Google Data Studio 12

Step 12

Go to Edit, and set data types, date column to date (which is most probably already set by default), and cost, revenue, and spending as currency > any currency to all three sheets of the sample file

How to Blend Data in Google Data Studio 13

Step 13

Create a table chart for each sheet with the dimension date and metric as cost/expense/spending

How to Blend Data in Google Data Studio 14

Step 14

Select all charts

How to Blend Data in Google Data Studio 15

Step 15

Right click > scroll down > click on Blend data

How to Blend Data in Google Data Studio 16

Tip: Doing this will take some load time and create a blended table chart from all three tables.

Step 16

You can see your data columns and values in this chart

How to Blend Data in Google Data Studio 17

Step 17

Now let’s calculate total ad spending; for this select the blended chart > go to metric > create a field

How to Blend Data in Google Data Studio 18

How to Blend Data in Google Data Studio 19

Step 18

Name your column (such as total spending, or overall cost) anything that seems meaningful

How to Blend Data in Google Data Studio 20

Step 19

Below, creates a custom formula that simply adds cost, expense, and spending (cost+expense+spending), and click on apply

How to Blend Data in Google Data Studio 21

How to Blend Data in Google Data Studio 22

Step 20

Now this custom field is added to your chart, and you can see the sum of all three spending values here

How to Blend Data in Google Data Studio 23

This is how blended data works. You can perform operations on your different data sources using this method even if they have different column names.

I hope you have understood this use case which is pretty common and useful.

See also  How to Create a Funnel in Google Data Studio [2 Methods]

Blend Data in Google Data Studio – Blend using Joins

In this section, we will learn how to blend data in Google Data Studio using the blend button, now this method can be a little complicated, in the previous method we used an easy and simple way to blend data, but here in this method, we have more options, we can determine the type of join we want to create between two or maximum fiver data sets.

Inside blend, we have five types of joins

  • Left outer join: Returns matching rows from the right table, plus non-matching rows from the left tables
  • Right outer join: Returns matching rows from the left tables, plus non-matching rows from the right table
  • Inner join: Returns only matching rows from the left and right tables
  • Full outer join: Returns all rows from the left tables and right tables, whether they match or not
  • Cross join: Returns every possible combination of rows from the left and right tables

Note: Cross joins don’t require any conditions.

We can create any kind of join among our data sources, here we can add up to 5 data sources and a minimum of two, we can also make blends and save them, and only use them when we need them. Blends can be saved just like data sources and can be managed from Resources > Manage Blends

Let’s see how to create one.

I have a Google sheets file that has three sheets:

  • Facebook ads
  • Instagram ads
  • LinkedIn ads

Note: note that my sheet’s data has the same column names, because most of the joins require to have the same column names.

There can be many methods to initiate making a blend data set, let’s see the simplest one

Step 1

Open an empty canvas (having data source already connected)

How to Blend Data in Google Data Studio 24

Step 2

Go to Resource > Manage blends

How to Blend Data in Google Data Studio 25

Step 3

Click on “Add a blend”

How to Blend Data in Google Data Studio 26

Step 4

Select your data source

How to Blend Data in Google Data Studio 27

Step 5

Click on join another table

How to Blend Data in Google Data Studio 28

Step 6

Select another data source to create a join

How to Blend Data in Google Data Studio 29

Step 7

Two data sources have been selected; you can add up to 5 by repeating step 5

How to Blend Data in Google Data Studio 30

Step 8

Click on “configure join”

How to Blend Data in Google Data Studio 31

Step 9

Select a type of join

How to Blend Data in Google Data Studio 32

Step 10

Add fields

How to Blend Data in Google Data Studio 33

Step 11

Click on the Save button, again click on the Save button

How to Blend Data in Google Data Studio 34

How to Blend Data in Google Data Studio 35

Step 12

Click on Close

How to Blend Data in Google Data Studio 36

Step 13

Your blend has been created; you can use it on data sources just like charts

How to Blend Data in Google Data Studio 37

This is how you can save these blends and use them whenever you need them.

You can also create a blend using charts, selecting any chart will give a blend option in the Chart box you can click on it and add more data sources with it to make a blend.

See also  How to Make CRM Report with Google Data Studio & Sheets

Important Notes

  • You can add up to 5 data sources by blend feature (custom or sample)
  • Blend data works on all charts and tables
  • Blends can be edited or deleted from Resource > Manage blends
  • Most of the joins don’t allow different column names, so the first method by right-clicking and blending is pretty useful in that case when you don’t have the same column names.
  • If you have different column names, and you want to use join on it, you can simply create a metric by creating a field, and then in the formula you can simply write the formula as a previous column name and assign its value to a newly created field, doing this will do nothing, but you can have a different column name without making a change in original data

See also  How to Use CASE WHEN Statement in Google Data Studio

Frequently Asked Questions

What is a blend in Google Data Studio?

Blend simply means to mix your data, you may have multiple data sources, and they may have some similar data points, for instance, you have Facebook ads data, and Instagram, ads data, in both data sets you must have a metric that is defining cost per conversion.

So, using blend you can group this data and can get insights of total ad spending regardless of the platform. The blend can also be used for various purposes, and they can be saved internally in your file and can be used only if you need them, there can be an endless number of blends in your one data studio report and they are on report level, no viewers can see them.

What are joins in data blending in Google Data Studio?

Joins are like general database joins, in SQL or MC Access we have some joins to join data, Data Studio Blends have some similar nature of joins, they are of five types and can be used to blend data more precisely and more specifically.

Joins have some requirements, which means that you cannot join two data sets that have distinct column names or metric names, they will not identify a different column name or metric name when you try to create a join between them. I have told you a trick above in the notes section for doing this without even changing your original data.

See also  How to Create Charts in Google Data Studio [Scorecard, Time Series & Pie Chart]

Conclusion

So, this was how to blend data in Google Data Studio. I hope you have learned about blends, and now you don’t have any confusion regarding this topic in Google Data studio. I will see you soon next time with another helpful tutorial. Keep learning Google Workspace with Office Demy.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general