How to properly build calculated fields (sum, ratio, …) with blended data in Looker Studio?
So, this is a common issue we’ve seen. How to make sum of blended data work in Looker Studio? No worries, we’ve got a solution for you!
So, this is a common issue we’ve seen many questions about on the Looker Studio Google Cloud Community, Reddit & Stackoverflow lately. People trying to make calculations with blended data, but the result is not what they were expecting. No worries, we’ve got your back. In this article, we’ll show you how to properly build calculated fields (sum, ratio, …) with blended data in Looker Studio.
Looker Studio connectors we use on a daily basis and are happy with (w/ free trials): Supermetrics - Windsor - Catchr - PMA - Funnel
Not sure which one to pick? Reviews here and there. Still unsure?! Contact us with your tech stack and budget, and we’ll guide you in the right direction:
Make sure your blended data is correctly set-up
So, let’s say you’ve got 2 data sources. One with Google Ads data, one with Meta Ads data, and you want to show the daily spends. Ie adding Google Cost to Meta Ad Spent.
In order to do so, you will blend your data. What should be the right set-up? In a world where you may spend one day on both platforms, one day only on one, and one day only the other, you don’t want to miss information. So you need to go with a FULL OUTER JOIN to be sure not to miss any data:
Indeed, if you only did a left join here, and for a given date you’ve got Google Cost but no Meta Amount Spent, the calculated field would return nothing. By doing a full outer, you ensure you capture the full information. In our example, we’re focusing on date. You might have more dimensions to shime in. Not a problem, just make sure they’re used in the blend as well to prevent double counting (all dimensions present should be used in the join conditions).
Create the right data dimension
Now, we’ll be creating a table. What should be the date dimension? You need to take both fields into consideration by using the COALESCE function:
COALESCE(Date (Meta Ads),Date (Google Ads))This way, we make sure that there is no null returned. Looker Studio will know which date it should attribute both costs to. Same goes for any dimension you’ll want to use in a breakdown. And if you’re joining Google Ads + Meta Ads + TikTok Ads for example, and want to breakdown by Channel (and don’t forget to join on them on the blend set-up), it’ll go as this, no limitation to the number of dimensions you cant put in a COALESCE:
COALESCE(Channel (Meta Ads),Channel (Google Ads),Channel (TikTok Ads))And now, the magic formula
You probably tried SUM(Meta Amount Spent + Goole Cost), but it didn’t work right? It’s because when you’ve got null value, the sum (or any calculated field really) just breaks. Instead, use the following formula that will take care of these and fix the issue for you:
SUM(IFNULL(Meta Amount Spent,0)+IFNULL(Google Cost,0))By doing so, you ensure null values don’t break your calculated field as they’re replaced by a proper number, 0, which is what they really are. Simple, but you had to think about it! Why it’s REALLY important? Looker Studio won’t throw you any error message, just incorrect numbers you might have not spotted. So be careful!
And that’s all folks :)
In this article, we’ve reviewed out to properly build calculated fields (sum, ratio, …) with blended data in Looker Studio.
PROBLEM SOLVED
Tools we use and we like:
Todoist: for tasks management
Monday: for projects management, when tasks fall short
Tropical Server: our friends in charge of the hosting of this very website!
Tools we’d like to use (if we had the budget…):