Custom dimensions are one of the most powerful features in Google Data Studio. It allows you to manipulate and visualize data in tables or charts based on new groups or values you define. Google offers a variety of functions or formulas that can be used to create dimensions from any source and extract new definitions that can be applied to your data sets.
In this post, we will be discussing 6 formulas to define new dimensions that you can use in your charts and reports. The formulas can be copied and pasted in the calculated field editor in you Google Data Studio Dashboard, as you can see below:
CASE is pretty much the same as IF function in excel, it basically checks if a condition is met and returns one value if TRUE or another value if FALSE. And it can be combined with most Google Data Studio Functions.
It consists of WHEN clause, two parameters; the conditional argument (X1, X2) and the value to return (Y1,Y2) when the conditions are met. And finally, the END clause.
CASE WHEN X1 THEN Y1 WHEN X2 THEN Y2 ELSE Y END
In the example below, we want to clean up the AdWords device default value and return simple values like Mobile instead of the long phrase “mobile devices with full browsers”.
CASE WHEN (Device = "mobile devices with full browsers") THEN "Mobile"
WHEN (Device = "computers") THEN "Desktop"
ELSE "Tablets" END
It returns specific value if X matches Y and false otherwise (using regular expressions). This function can only be used in WHEN clause of CASE statements; not a standalone function.
REGEXP_MATCH(field,"text to match")
Let’s say you want to create a channel grouping of your analytics sources to group any medium equals to CPC or PPC under “Paid” and anything else under “Organic”. If you’re familiar with regex:
^ means text begins with.
| means OR.
$ means text ends with.
WHEN REGEXP_MATCH(meduim,"^(cpc|ppc)$") THEN "Paid"
Upper converts any text to uppercase and Lower converts to lowercase.
Google Analytics is case sensitive, this means if you tag one of your campaigns with the source “facebook” all lowercase and another campaign with the source “FACEBOOK” all uppercase. These sources will appear separately in GA.
To overcome this issue in your Google Data Studio report you can use the formula Upper/Lower and Google Data Studio will aggregate the data under one source.
This is exactly the same as Concatenate function in excel, it allows to combine text from different sources.
Let’s say you want to generate a report of browsers plus their operating systems, it can be done as below:
CONCAT(Browser, Operating System)
It creates dimension values by extracting them from a source dimension using regular expressions.
Let’s say you do include the categories name of your website in your AdWords campaigns and you want to create a report of the same. Using REGEXP_EXTRACT(), you can extract the category names easily.
If you’re familiar with regex, the dot-asterisk (.*) means, extract all the characters after “Category:”. So, if your campaign name is “SN-EN-Category:Cars” , the results or the extracted value would be “Cars”.
It replaces all occurrences of text which matches the regex in X with the replacement string.
REGEXP_EXTRACT(field,text to replace, text to fill)
Let’s say you have a campaign that has a misspelled word “Shoex” and you want it to replace it in your reports with right word “Shoes”
REGEXP_REPLACE(Campaign, 'Shoex', 'Shoes')