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!
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:
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 !
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…):