Uncategorized

Data Studio Hack – Get a Filter Control to Apply on Multiple Data Sources

Suppose you have multiple data sources in one Google Data Studio report. Of course, you’d like to filter either on the date or on campaign sources, or input devices or whatever. And of course, you’d like to have one filter control to filter all your tables, hence the complete report. Currently, this is a little tricky in Google Data Studio and all hinges on the so-called “field ID” (This post an in-depth explanation of the steps outlined in Filter Controls and Data Sources.)

Let’s create a pretty useful kind of report for your marketing team. A newsletter report. You’d probably like to have two kinds of data in there.

  • Newsletter data from your mailing program like #mails sent, #bounced mails, #opening rate, the exact links clicked in the newsletter and more.
  • Google Analytics data, or any other clickstream tracking data, like #people landing on articles, #conversions and so on.

For the first kind of data, usually, a basic Google Sheet will do, as Google Data Studio currently doesn’t have that many APIs ready. Although since they opened up the Extensions, the number is growing pretty quickly.

The steps to get a filter control to apply to those two kinds of data sources are pretty simple: We first create the two data sources, make a “copy” of the field we want to filter on, in this case, the campaign, and then make their “field IDs” agree.

Step 1: Create the first data source from Google Sheets

I’ve saved some data in a Google Sheet which looks like this.

Now we head over to the Google Data Studio and create a new data source.

Hit “connect”, and go on to “create report”. We will edit the metrics later on, once we have the field ID in hand.

Step 2: Create the second data source from Google Analytics.

Head back to the data sources view and create a new one, this time with the Google Analytics connector. Select your account, property and view.

Now this time, we have some editing to do. Create a new field, in this

case

a dimension, by clicking on “+” (create calculated field). We have to create a new field, which is a copy of the old field “Campaign”. The only reason for this is that we cannot access the field ID for default fields. Thanks, Google.

  1. Create a new field by clicking “+”. Let’s call it “(cust.) Campaign” to distinguish it from the default one.
  2. Copy the field ID, in this case, “calc_XiYN14pi”.
  3. Insert the formula to copy the default field Campaign, which is
“CONCAT(Campaign,’')"

Once you hit enter/Create field, the new field should appear with the suffix “fx”.

Step 3: Creating a custom metric with the same

Now, armed with our field ID, we head back to the first data source from Google Sheets, to create a new copy calculated field as well.

  1. Create a new field by clicking “+”. Let’s call it “(cust.) Campaign” to distinguish it from the default one; Turns out you actually could give it a different name as far as I understand as long as the field ID matches the other one.
  2. Important: Change the field ID to the copied one, here it’s“calc_XiYN14pi”.
  3. Insert the formula to copy the default campaign field from Sheets.

 

And then create your metric.

Step 4: Creating the report with the filter control

Finally, we again open our report in edit mode, create any table and then CLICK ON THE DATA SOURCE, which at first should be the Google Sheets data source.

 

On clicking, you get a list of all your data sources and can thus add your new data source to this report. I don’t know why this is not possible in the “Manage added data sources” dialog.

Add one table by selecting the settings

  1. Our Google Analytics data source
  2. as dimension “(cust.) Campaign”
  3. as metric “Sessions”

Add a second table by selecting

  1. the Google Sheets data source
  2. as dimension “(cust.) Campaign” and all the other dimensions and metrics from the sheet.

 

Finally, add the filter control. Here you have to use the settings

  1. data source: choose any of the two, it doesn’t really matter as the filter is supposed to apply to BOTH although you can only select one.
  2. select our new calculated field as “Dimension”, and select any metric to be displayed alongside different campaigns.

 

 

And we are done! Now the filter control will filter both tables at the same time.

Leave a Reply

Your email address will not be published. Required fields are marked *