Create Advanced Dimensions in Google Data Studio via these 6 Formulas

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:

custom dimensions in google data studio

1. CASE

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.

Syntax
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

Example
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

devices google data studio

2. REGEXP_MATCH()

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.

Syntax

REGEXP_MATCH(field,"text to match")

Example

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.

CASE WHEN REGEXP_MATCH(meduim,"^(cpc|ppc)$") THEN "Paid"  ELSE "Organic" End

3- Upper()/Lower()

Upper converts any text to uppercase and Lower converts to lowercase.

Syntax

Upper(text)
Lower(text)

Example

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.

Upper(Source)

google data studio upper lower

4- CONCAT()

This is exactly the same as Concatenate function in excel, it allows to combine text from different sources.
Syntax

Concat(x,y)

Example
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)

5. REGEXP_EXTRACT()

It creates dimension values by extracting them from a source dimension using regular expressions.

Syntax

REGEXP_EXTRACT(field_expression, regexp)

Example

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”.

REGEXP_EXTRACT(Campaign,'Category:(.*)')

6. REGEXP_REPLACE

It replaces all occurrences of text which matches the regex in X with the replacement string.

Syntax

REGEXP_EXTRACT(field,text to replace, text to fill)

Example

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')

 

mm
Ahmed launched his first digital campaign in 2013 through participating in Google Online Marketing Challenge. He competed with over 12,000 students from 80 countries to win the first place in Africa & MENA region and one of the best five campaigns in the world. Today, he works as an Associate Digital Director at Keyade Middle East.

5 Comments

  1. I tried using your CASE formula and pasting in the Formula field. I received a “could not parse formula” error. i want to group the Page Depth the same way Google Analytics shows it (the default in Data Studio is to show each page rather than 1, 2-4, 5-7,etc.). I’d like to do the same for the time on site as well.

Leave a Reply

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