Creating filters for multiple data sources in Google Data Studio
How to filter data from more than one source. By Ahmed Masmoudi
After explaining the different types of filters Google Data Studio offers for your reports on the previous blog post of our GDS series. This time we are going to discuss the possibility of using filters from multiple data sources.
In many use cases, data analysts will have to put data from Google Sheets, Google Analytics, BigQuery, and many other third-party data sources altogether in one dashboard.
And filtering the different charts from their respective data sources all at once can be tricky, especially that the schema names can differ.
In this blog post, I will walk you through the different scenarios that can encounter you while using different data sources with one filter control for all of them.
We will work on the San Francisco Bike Share public dataset for that.
San Francisco Bay Area Bike Share Data
San Francisco Bike Share includes multiple data tables:
- Bikeshare station info
- Bikeshare station status
- Bikeshare trips
We will be focusing on Bikeshare trips data, which includes records of the trips with their start and end dates, stations, and customer information as the following:
The data is sorted by the most trips for every track (Start Station + End Station) and provides the latest date for each.
Import data from BigQuery and Google Sheets
After importing trips data from both the BigQuery table and the Google sheets, we tried to add a filter control for both tables to filter out rows by the start station name like the following:
Despite having the same field names, the filter control for the start station is only filtering out the data from the BigQuery table, since we used it as a data source for our filter.
Field ID functionality
For filters from different sources, the most important thing in Data Studio to have a unified filter for all our tables is the field ID. Having the same field ID for our Start Station Name field will enable us to filter both tables from BigQuery and Google Sheets at the same time.
But due to some limitations in Data Studio, we need to add a new field for both data tables so we can control the field ID ourselves.
You can find the ‘Add a field’ button at the bottom right of your Data Studio dashboard like this:
We will create a field named ‘start_station_name_global’ from the desired field ‘start_station_name’ for both data sources, and we will define the same field ID. We can create a formula to concatenate the Start Station Name to an empty string which is basically the same as the original field, like the following:
Afterwards, we will change the Start Station Name field in both our tables and the control field we defined into the global one which now has the same field ID across all our data sources like the following:
Now we can filter out the data for both BigQuery and Google Sheets with the same filter. This can apply to all the filters Data Studio is providing and for all chart types available too. So in conclusion, field ID is the essential criteria to get the job done.
To have all options available in your filter, you better link it to the data source with the most distinct values for that particular field.
In our case the BigQuery table has much more distinct values of the station names included compared to the number of distinct station names in the Google Sheets one, you can estimate that by trying both data sources control filed on your filter.
Other use cases
Using the field ID for a common filter across different data sources can also be for different columns and not necessarily only columns with the same name or having the same purpose regardless of the data source.
For instance, in our SF BikeShare data, we can use the station name as a filter for the Start Station Name for the BigQuery trips table and for the Station Name in the Bikeshare station info table.
That way we can have a filtered overview of the station infos and the station’s most recent trips on the same dashboard.
To do so. We need to create a new field from the Station Name field for the Bikeshare station info table for example and give it the same field ID as the one on the BigQuery Trips table like the following:
Conclusion
The most important thing for this blog post topic is learning how to make use of the field ID to link charts from your dashboard with different data sources and be able to filter them out all at once with a unified control field. You can also use chart filters for fields with the same field ID which is super helpful for many use cases too.
You can apply this concept to different columns as long as they share the same data type and purpose so that your filters can work properly for sure.
Further links
This post is part of our Google Data Studio series in the Data School of datadice. We will be sharing the best insights that we’ve learned through years of creating data solutions.
If you want to learn more about how to use Google Data Studio and take it to the next level in combination with BigQuery, check our Udemy course here.
If you are looking for help to set up a modern and cost-efficient data warehouse or analytical dashboards, send us an email to hello@datadice.io and we will schedule a call.
