Optimization Up

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:

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

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)

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

Ahmed Ali

Entrepreneur focused on building MarTech products that bridge the gap between marketing and technology. Check out my latest product GA4 Auditor and discover all my products here.