Being one of the most convenient data visualization, reporting and dashboard tool due its easy integration with Google’s tools, Data Studio quickly became a go-to tool for majority of digital marketers. However, even though I don’t find myself using 100% of its potential, I did find range of limitations for which you either need to find a workaround within the tool, edit a source itself or even merge it prior to importing to Data Studio as you can’t combine multiple data sources in a single chart.
One of the last of such limitations I have came up with was inability to filter a calculated metric while creating it.
How to use calculated metrics in Data Studio
Let’s say you would like to create a chart with the traffic sources and sessions you have in Google Analytics. You’ll face several issues (if you choose to look at them as issues). If you choose the source dimension your chart won’t have a single number of sessions for, for instance, traffic from Facebook. It’ll rather have several including facebook.com, m.facebook.com, l.facebook.com and more. On the other hand, if you choose the Default Channel Grouping there you’ll face a problem with having a perhaps too generic overview of your traffic sources such as Social while you might want to separate Facebook, Twitter and LinkedIn. Also, you might not want to have Google AdWords and Facebook Ads both as Paid. Unfortunately, creating a Custom Channel Groupings in GA doesn’t help either as you won’t be able to see it in Data Studio.
This is where Calculated Metrics come in. By using a simple SQL-like expressions you can group your data or perform calculations to create custom metrics you can report on.
Therefore, if we’d like to create a chart that includes Organic, Facebook, Facebook Ads, LinkedIn, Twitter we’d have to create a calculated metric that contains the following:
CASE
WHEN Medium = 'organic' THEN 'Organic'
WHEN Source = 'google' AND Medium = 'cpc' THEN 'AdWords'
WHEN REGEXP_MATCH(Source, '.*facebook.*') AND REGEXP_MATCH(Medium, 'referral|social') THEN 'Facebook'
WHEN REGEXP_MATCH(Source, '.*facebook.*') AND Medium = 'ads' THEN 'Facebook Ads'
WHEN REGEXP_MATCH(Source, 'twitter.*|^t\\.co$') THEN 'Twitter'
WHEN REGEXP_MATCH(Source, 'linkedin.*|^lnkd\\.in$') THEN 'LinkedIn'
ELSE 'Other'
END
One thing to note here is that this particular example will only work if you if you use the exact same settings for the UTM codes. And, another one is that the formula is being executed in the order you write it and in our example above, if there weren’t for other potential google sources, after organic we could’ve easily written just WHEN Source = 'google' as organic would already be selected.
Limitations of Calculated Metrics
One major limitation that comes with using Calculated Metrics is that you can’t combine dimensions and metrics which means that you are also limited with the filtering options. So if you’d like to return the number of transactions where the traffic source was Adwords, you might find yourself stuck trying to search through Google forums ending up reading only questions such as ‘when will this problem be fixed?’. I haven’t found the exact solution of how to do this using calculated metric only, but this might work for you as well.
I was trying to build a Adwords reporting dashboard where I would have few custom metrics based on which we could see the actual impact of Adwords and have something to set targets for. One of these metrics was CRR (Cost Revenue Ratio) and it’s calculation is rather simple CRR = Adwords Revenue / Adwords Spend, however I was unable to create something similar to Revenue where Source = 'google' and Medium = 'cpc' – ‘sounds good – doesn’t work’.
Eventually, I came up with the following workaround. Even though you can’t filter within the calculated field, you can do it on different levels within the report itself – Chart, Page and Report level. Therefore, if I wanted create my Adwords report with metrics such as ROI, ROAS, CRR, and others I would first do it anyway, regardless of it not being filtered and then create a report level filter where source / medium = 'google / cpc' Same way if this would be an overall marketing performance report and on the other page we’d have Facebook Ads with the same metrics we could use the Page level filter.
To create a page level filter simply select Page > Current Page Settings > Data > Filters and enter your custom filter.
For a report level filter select File > Report Settings > Choose the data source you'd like to filter > Filters.
Needless to say, if you have more than one data source you will have to create additional filters on different levels, depending on your need.
Additionally, if you’d like to have several charts at which filters should not be applied, you select the chart and in the control panel on the right side pick the ‘data’ tab, scroll down to ‘filters’ and switch of the ‘Inherit Filters’. This is what makes this solution a substitute for filtering a calculated metric.