Google Data Studio: 5 Advanced Techniques to Boosts Your Visualisation ( Part II)
Moving from basics to intermediate features in Data Studio, this article will explore two major advanced features every marketer should not miss.
Please check out here if you missed out on the previous article about the filters, controls, and segment. This article will continue diving into the rest of the powerful features of calculated fields.
What to expect for :
We will go through how to use the calculated field in Data Studio to create customised metrics for what matters to your business. You can see how to create calculated dimensions to more clearly and cleanly convey your data. You'll see specific examples of practical calculated dimensions you can use to clean up common Google Analytics data.
Calculated Field
In Google Data Studio, you can calculate any metrics off each other. For example, in Google Analytics, "Goal Conversion Rate" is typically calculated by Goal Completions/ total number of Sessions.
But, what if your business model might have a reason that calculation won't work and the conversion is better defined by Goal 2 Completions/ Goal 1 Completions? There is always an exception and unique goal to achieve.
And here you can have various calculations to play around with:
- Basic calculations (addition, subtraction, division, multiplication) that include: Sales minus Returns, Conversion Rate, Price * Quantity
- More complicated functions (SUM, ROUND, REGEXP), for example, REGEXP_EXTRACT(Field,’^([a-zA-Z_]*)(\\| )’) C
There are also some typical functions in DS include:
- Aggregate (Sum, Avg, Count…)
- Arithmetic (Floor, Round, Sqrt…)
- Date (Date_Diff, Year, Day…)
- Geo (To Country, To Region…)
- Miscellaneous (CASE, CAST) —handy functions!
- Text (Reg Ex, Replace, Upper…)
I would recommend you look at the DS support section, where you can find a long list available.
There are two ways to create the calculated field:
- In the Data Source
You go into your data source, select ADD A FIELD on your top right, and added the formula as you wish. YES, simple as it is.
Create in Data Source:
- Pros: Reusable for many different widgets (without having to manually recreate every time).
- Cons: Need edit access to the data source
2. In a specific chart
You would first need to go into your report. Insert a chart, go to your metrics and click CREATE FIELD. Here you can set the metrics a name, formula, type etc.
Create for just that widget:
- Pros: Don't need edit access to the data (you do need edit access to the report, though)
- Cons: Have to recreate over and over to reuse, and you can't "stack" (refer to a calculated field in another)
One common issues to keep an eye out is that when you perform some calculations for example, Profit/Revenue, which giving you crazy wacky numbers. Instead, you need to try SUM ( Profit)/ SUM ( Revenue). Plant that in the back of your head that you would always look at the SUM rather than just adding the metric name as it is.
Calculated metrics are handy to clean up data, rewrite values and group values. There are also a lot of functions you can use here; see below. We will explain Regex, Concat, Case and Cast more in detail.
To give you a quick example of how to use CASE Formula using countries. When the country filed in the US, Canada, or Mexico, then we will call that North America, England or France, then we are going to call that Europe, and let the rest fall in Other and END. Remember, the rules will execute in order.
Some other helpful text functions that you might also want to look at are below. I would suggest you take a look at this pdf in order to understand ReGEX more.
Here is another little hack for Funnel visualisations that you can use CASE Formula to work creatively in pretty much any field.
Another of the favourite uses of most marketers is being able to recreate channel groupings in Data Studio. Channel groupings in Google Analytics are not relatively flexible and retroactive, so it requires a bit of complex work. But now, you can create a rule set in DS that will work exactly the same as your channel groupings in GA.
Using the CASE formula ( like what we showcase below) will be handy to build your own rules that make sense for you as an organisation, how you tag your campaigns, and what field you would like to call. And again, the rules execute in order.
Another helpful thing about using the CASE formula is removing the locale or the country from the URL. If you want to analyse behaviour but have many pages that are all identical, you can strip the country, locale, language code, etc., out of there.
By doing so, you can follow the formula below to curate a cleaner list of the page names. You might need to tweak a little bit for how your URLs works, but this function is good to go.
Stacking Calculated Fields
If you created in the Data Sources, calculated fields could refer to ( and therefore, build upon) other calculated fields. So even if something isn't possible in one field, it might be possible in multiple, which give you a lot more control of how you display the data.
A little caveat for Calculated Field is that you can't mix dimensions and metrics as it is not possible in Data Studio at the moment. If you are creating a calculated dimension, you can refer to what's in dimensions. Likewise, the same theory applies to the metrics.
A little hack for the calculated field name is that you only have one chance to name a field ( in the actual Field iD) and that's when you create it. And you can make the field ID the same across multiple Data Sources, so the filters work.
And that's it. In our next ( last) article, we will wrap up Google Data Studio with blend feature and I hope you stay tuned with it. If you would like to learn more about digital marketing courses, check out CXL, as they have various online courses curated for marketers.
