How to build a control that filters blended data in Looker Studio?
It took us time to figure it out, but we finally did it. Check out how we created a control that filters blended data in Looker Studio !
Yet again, we’ll be talking about blended data. These are powerful, but sometimes frustating. You’ve been playing with SUM(IFNULL, with COALESCE to build custom fields and still, you didn’t find a way to build a control that filters blended data in Looker Studio / Data Studio?
Good news, after months of research we couldn’t find a solution, but Thomas Langnau shared one with us, thanks for that buddy!
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.
Controls usually don’t work with blended data
In this example, we’ll be using a sample Google Ads data source blended to a sample Facebook ads data source, looking at impressions by date (full outer join on date):
Now, you want to have a nice table that gives you total impressions by date.
At this stage if you’ve landed here we think you know how to do it:
Dimension: COALESCE(Date (Facebook),Date (Google))
Metric: SUM(IFNULL(Impressions (Facebook),0)+IFNULL(Impressions (Google),0))
Once you’ve done this, you’ve got a nice table that gives you total impressions facebook + google by date. Excellent.
But what if you wanted to filter only on facebook or google? You’ve been looking for ways, and none worked. Now, it’s time to show how we did to build one.
The key: adding fields in both data sources that share the same ID
Please Edit both your data source, and add a new field:
For the facebook data source, we’ll add a field called source, ID sourceID, and just input ‘facebook’
For the google data source, we’ll add the same field, this time inputting ‘google’
It’s very important that both fields share the same ID
Now, this is where the magic happens:
Modify your blended data to insert both sourceID from both tables as dimensions, and join on them
Create a control with the following formula: COALESCE(source (Facebook),source (Google))
It looks good, but it doesn’t work right? We know that, no worries, we’re going to fix it for you :)
Add two new fields to each data sources
Go back to your two data sources, and hide the two field source (by clicking on the 3 dots right to the name, and then Hide).
The control should break, and this is exactly what we want it to do :)
Now, look at the formula in your control, and copy the two weird names (the IDs in reality) you’ll see:
From there, that’s pretty simple:
Go back again to your two data sources, and un-hide the source fields
Create two new fields in each data source, the first one called source0 with ID sourceID_dv0, and value: Source
Similarly for the second one, call it source1 with ID sourceID_dv1 and value: Source
And that’s it, it just works, mind blowing right?
Remember, the key here is to create two additional fields with the parameter IDs we’ve seen in our control when we’ve hidden the original fields. Weird, but it does the job!
In this article, we’ve reviewed how to build a control that filters 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