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.
Top Looker Studio connectors we love and use on a daily basis (all with free trials): PMA - Windsor - Supermetrics - Catchr - Funnel - Dataslayer. Reviews here and there.
Not sure which one to pick? Have a question? Need a pro to get a project done? Contact us on LinkedIn or by e-mail, and we’ll clear up any doubt you might have.
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
Top Looker Studio connectors we love and use on a daily basis (all with free trials): PMA - Windsor - Supermetrics - Catchr - Funnel - Dataslayer. Reviews here and there.
Not sure which one to pick? Have a question? Need a pro to get a project done? Contact us on LinkedIn or by e-mail, and we’ll clear up any doubt you might have.
Communicate and browse privately. Check Proton Mail and Proton VPN
Website hosted by Tropical Server