@danaditomaso who presented at Brighton SEO 2018 recently brought to my attention the very useful benefits of using calculated formulas within Google Data Studio during her talk on ‘The math behind effective reporting.
Calculated field formulas within Google Data Studio are a very powerful yet underused feature of Data Studio which can really help with presenting your data in your dashboards. What I really like is the ability to be able to retrospectively make changes to data in your reports, for example renaming and merging channels.
This is a great benefit if you need to tidy up your analytics reports or want to bring old reporting in line with new reporting. I for one have leaned heavily on making changes directly within Analytics but doing this in Data Studio provides far greater flexibility and speed efficiency.
Data Studio offers a variety of formulas that can be used to calculate ratios and rates, extract and reformat text, and apply logical comparisons.
Here are 5 examples of new dimensions and metrics you can create yourself in Google Data Studio:
To create a new dimension or metric within your dashboard (using calculated fields), click on an object such as table and within the right side data panel look for ‘Create New field’ with a plus symbol to get started.
1. Use Formulas to Combine Data
Using the CONCAT function in Data Studio (just like in Excel) will allow you to combine data together. For example if you have multiple domains or sub-domains within your website, it can be helpful to see the full URL of the page in your reports. A simple concatenation function will let you combine the Hostname and Page dimensions:
Use the CONCAT function to combine the Hostname and Page dimensions:
CONCAT(Hostname, Page)
OR maybe Browser or operating system..
CONCAT(Browser, Operating System)
2. Turn Text to Lowercase
The most common inconsistency is usually the use of different case, e.g. “email”, “Email”, and “EMAIL”. This is easily fixed by forcing all campaign dimensions to lowercase. Luckily this can be fixed by simply applying a lowercase filter.
To do this, create a new dimension eg Medium (Lower) then enter the formula LOWER(Medium).
Then hit save.
You can also do this for any other label such as source, campaign, search term etc. If you wish convert text to uppercase you can do this simply by specifying UPPER (enter dimension name).
3. Create New Custom Metrics
Create new custom metrics based on different calculations
Events Per user: {{Total Events}} / {{Users}} (select metric type float)
User Conversion Rate %: ( {{Goal Completions}} + {{Transactions}} ) / {{Users}} (select metric type percent)
Product Views per session: {{Product Detail Views}} / {{Sessions}} (select metric type float)
Engagement Rate: ( {{Sessions}} – {{Bounces}} ) / {{Sessions}} (select metric type percent)
New User Ratio = {{New Users}}/ {{Users}} (select metric type percent)
4. Reorganise and Group Content by Label
In instances where you might want to re-group and categorise content, we can use the built-in ‘CASE’ statement functionality to set rules for how to group the content. In the example below, we are grouping together countries into region names for easier reporting. You could also do this based on sales region to better suit your needs.
CASE WHEN Country IN (“France” , “Italy” , “Germany” , “United Kingdom”, “Netherlands” , “Denmark”) THEN “Europe” WHEN Country IN (“China” , “Singapore” , “Philippines” , “Japan” ,“Hong Kong”) THEN “Asia” WHEN Country IN (“United Arab Emirates” , “Bahrain” , “Saudi Arabia”) THEN “Middle East” WHEN Country IN (“United States”, “Canada”) THEN “North America” ELSE “Other Countries” END
5. Add in Hyperlinks
You can link elements in your report to take people to specific web pages. An example of this might be a landing page report as illustrated below in which the urls are a hyperlink.
To do this
- Create A New Dimension Field within Google Data Studio
- Give the new dimension a name eg Landing Page with Links (You can always rename afterwards)
- Enter the following formula replacing the first part of the url with your own hostname
In the table above, the landing pages exclude the first part of the URL (the protocol and the domain name), so a Concat function is required to combine the landing page path with the hostname in order to make the link work. In the example above, the Landing Page is used as the Link Label, so the table will look the same as above, but with links included. This was the formula used: HYPERLINK(CONCAT(X, Y), Link Label).
Other Useful Links
https://support.google.com/datastudio/answer/7431836
https://www.lunametrics.com/blog/2018/04/20/data-studio-calculated-fields/
https://www.clickinsight.ca/blog/5-calculated-fields-google-data-studio
https://optimizationup.com/custom-dimensions-google-data-studio-formulas/