Blended data in Google Data Studio – Tips & Tricks

In the previous two chapters we have talked in detail about what is blended data and about each type of join in particular. Here is the Google Data Studio dashboard that we observe in the trilogy. You can find the links to the previous articles here – part 1, part 2.

Today I would like to share some tricks and tips about Blended Data in Google Data Studio. 

But before we start, check out the video tutorials for this article. The previous video tutorials you can find by the links – Video 1, Video 2.

Trick 1 – Null in dimension

In some cases null can appear in metrics or dimensions. As we know from the previous articles, null doesn’t mean zero. So we are able to change it. In the picture you can see that there is a null in date and null in costs. If we build a chart based on this data, we have null here. We have 9th, 6th, 5th, 4th and null and we have costs split by Google and Facebook but we cannot see the total cost. 

So, I copied a chart and a Full Join table. I would like to add an additional dimension. I will create a field with a title “Date” and I will use the formula:

IFNULL (Google Date + Facebook Date)

Right after applying we can see the changes. We’ve got a new column called Date. Instead of the null from Facebook Date we have 9th (that comes from our Google Date), and as we have null in Google Date, in our new column we see the 7th from our Facebook Date.

Now let’s copy the formula and add it to the chart as well. The scheme is the same – Create a field, name it Date, paste the formula and apply. 

As you can see now, we have all the dates and it doesn’t matter if it’s null for Google Date or Facebook Date. Here we have all the dates. 

Trick 2 – Calculated metric

We’ve successfully fixed the null in data and null in cost. Now I want to calculate the total cost.

Firstly, I select the table and create a field “Total Cost”. Here I can use: 

Cost Google + Cost Facebook

As you can see, 36 euro for 9th of August, and if we see null in Google or in Facebook costs, in total we will have null. It means that the null is not the same as a zero. 

So we need to avoid it, we need to change null to zero. I actually have two options on how to do it. Let’s use this one. We can create a new field and I will use the formula:

CASE
WHEN Cost Google is null then 0
else Cost Google
end

What does it mean? If Google Cost is null, then apply 0. If it is not, let’s use Google cost. 

Then I can do this: 

SUM(CASE
WHEN Cost Google is null then 0
else Cost Google
end)

Then I copy this formula and apply the same formula for Facebook. 

SUM(CASE
WHEN Cost Facebook is null then 0
else Cost Facebook
end)

Then we have to change the type from number to currency. How did these formulas influence our charts?

After changing the null to zero we can get a correct number. That lets us calculate the summary for this metric – Summary Cost. 

I delete this field and I leave the other three. We can add Total Cost to the chart. Let’s change the type of the chart from the Stacked Column Chart to Stacked Combo Chart. You can do it in the same way: Select chart – Create metric – Create a field, (name it Total Cost) – Change the number to currency – Apply. Series Nº1 will be the bar, Series Nº2 will be the bar as well and Series Nº3 will be a curve. One more detail here that I would like to do is to switch the chart from descending to ascending.

Difference between data sources / blends 

Data sources and blends may seem to be similar but in fact they are really different. 

1. If a data source depends on one data source, the blend depends on all the data sources you use for this blend. If you have any problems with your Data Studio, you need to know which data source is damaged. 
2. When you copy a report, you have to create blends in the new report. For instance, if you go to Manage Added Data sources – Data sources, you can click the Make Reusable button and use the same data source in another report, but for blends you cannot do it, so you will have to create the blends in your new report again, or you just copy this report and in this case it will work. 
3. Credentials. To create this dashboard I have used a Google Sheet connector. If somebody else wants to use my blends, he needs to have the credentials, my gmail and password that I logged in with or access to this Google Sheets from his gmail.
4. Data refreshing. If we click Manage Added Data Sources – Edit, you can see the data refreshes every 15 minutes, you can change it for Every Hour or Every 4 or 12 hours, and it means that your blended data updates when your original data source updates.

***

Hope you liked the last article about Data Blending! Leave your thoughts and questions in the comments below. 

Two previous parts: part 1, part 2.