Uncategorized

Data Studio Hack – Filter Controls on Multiple Data Sources

I was ready to publish a short piece on copying filters & in general components from one report to another in Google Data Studio, the free reporting & dashboarding tool from Google, when I came across a curious phenomenon, which kind of explains how filters work when you copy them from one data source and apply it to the other.

So, here’s something about Filter Controls in Data Studio, what they are, how to copy them and how they work.

What’s a Filter Control

A Filter Control is an element which adds great variety to your dashboard and reports in Google Data Studio. It allows you to either drill deeper into certain dimensions or to combine a bunch of reports together into one large report with a filter control.

A Filter Control can be applied

  • to a group of charts
  • to the complete page
  • to the complete report.

For a given marketing report with lots of Google Analytics data, two examples would be a filter

  • to select different sources of traffic like mobile, desktop
  • to select different tagged campaigns (like “christmas_newsletter” or “easter_special_banner_ad”)

Here’s how the Filter Control looks like. It works just like the date selector, except that you can choose (in editing mode) to filter on any dimension, not just on the date.

Creating a Filter Control

When you create a filter control you have the following options.

  • “Data Source” (mandatory) You select one data source on which you want to “create” the filter. For instance, you might select your Google Analytics View if you want to have a filter on the dimension called Source. You can only select one data source.
  • “Dimension” (mandatory) You select the (one) dimension you want to be able to filter your data on.
  • “Metric” (optional) The metric is optional, the only use of the metric is to be displayed alongside with the dimension value in the filter drop down (see picture above).
  • …Sorting & Style options….
  • “Filter Control Filter” (optional) A filter on the filter control (yeah really) allows you to display fewer options. In the example above, if you have a filter control on the campaign, you might want to limit the options to only the ones which include the word “newsletter”. That’s what the “filter control filter” is for.

Now the important observation here is: A filter belongs in some way to one data source. It’s linked to one. If you’re familiar with databases for instance MySQL you’d immediately think “why can’t I simply copy this filter on the dimension Source to another database with the same dimension (like a different Google Analytics property)?”.

You can do this, but you have to watch out for the field ID.

Copying Filter Controls (and any other component)

The only problem when you want to copy any control/filter is, that you cannot look directly into the database Google runs in the background which stores your data. So you don’t really know whether the two dimensions you want to filter on, which might be in both cases the Source in two different Google Analytics views, actually have the same name in the database schemata.

This also means, the filter you’re copying, might work differently on a different database/underlying data.

But Google is so nice to give you at least some control over this. They give you the “Field ID”, which is supposedly something like the field name. Although I’m guessing for security reasons it’s only mapped in some way to the real one.

You can find the “Field ID” in your data source when you create a new custom dimension/metric:
Keep in mind,
  • you can view the ID for all custom dimensions.
  •  You currently cannot view it for default dimensions, like the standard Google Analytics dimensions.
  • And you can change it, only when you create a new custom dimension.

Steps to Copy a Filter Control

With all of this in mind, here is a quick list of steps to copy a filter control from one data source to another, or from one report to another (with a different data source).

  • First make sure your filter control is based on a custom dimension. Copy the field ID.
  • If it is not, then create a new custom dimension which copies the default dimension with the formula given above (CONCAT(your default dimension,’’)).
  • In your new data source, create a new custom dimension and make sure to change the ID right away (afterward you cannot change it).
  • Copy your control, and you’re done.

Fine Points in How the Filter Control Works

Remember the sentence I highlighted above? A filter control is tied to a data source. That makes for some weird behavior in some cases on the report if you don’t understand how this filter is evaluated on data sources not tied to it.

If you have the following values for your dimension: V1,V2,V3,V4,V5 then the selection works as follows:

  • On the data source it was created on: If you select a bunch of those options, for instance, “V1, V2, V5” then the filter works as you might expect, it selects All Data filtered for the dimensions equal to V1, V2, V5.
  • On a different data source with the same dimension, and field ID: If you select V1,V2,V5 this will be interpreted (as we have selected three of the five options, more than half) as “All Data filtered for the dimensions NOT equal to V3, V4”.

I don’t know how to fix this, I’m guessing it’s there for some database/performance reason. Here’s a list of what to expect when you copy a filter control and then apply it to the new data source:

  • “V1,V2,V3,V4,V5” intepreted as “select everything” => “All Data”
  • V1,V2,V3 interpreted as “select everything where the dimension is NOT equal to V4,V5 => All Data filtered for dimensions NOT equal to V4,V5. This of course is possibly much more than you have in the original data source. Imagine you had filtered the filter control, with a Filter Control Filter, for campaign tags containing “newsletter”, then, in this case, you’d suddenly get ALL campaign tags (not just the pretty Filter Control filtered ones).
  • “V1, V2” interpreted as “select everything where the dimension is equal to V1, V2” (as it’s only two options of five, less than half)

The Fix? Put more Data up

The fix is simply to put up more options, so the user usually selects less than half of the option. You can play with the number of options displayed, add some dummy options etc. to get your application working.

You might think: Well, then I’ll just create a new filter control. That’s true, but it’s only possible if you really want to apply the filter control to only one data source. The point of dashboarding is usually to have data from different sources combined in one board. So in my use cases, I run into this problem quite often.

Leave a Reply

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