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:
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')
Ex-Google, building MarTech and AdTech products, successfully shipped GA4 Auditor and GA4 SQL so far. If you’re interested in doing business together, Contact Me!
We’ve developed a Free Ecommerce Google Data Studio Template (https://goo.gl/O04k38) we would like to share. We would love to hear your opinion.
All the best,
Thanks for sharing this, Bruno 😉
Great internet site! It looks very professional! Sustain the excellent job!
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.
Please share your final formula to troubleshoot further.
I’m tracking product downloads for our marketing team. We have a quarter objective of 50 downloads. I have a simple spreadsheet formula set up that calculates whether or not we are on track to meet our goal. In Google Spreadsheets, when the number is 0 we are meeting our quarterly objective, when it’s >1 we are not meeting our quarterly objective.
Is there a way to set up my data pipeline and create a calculated field in Data Studio so that when the number is zero the text in the DS will say “YES” and when it is >1 it will say “NO”.
I’ve been trying to use CASE on a scorecard number but it’s not working:
CASE WHEN Q3 Completion == <1 THEN YES ELSE NO END
I've also played around with different versions of this to make sure that the sheet field is an aggregated number. I've also tried playing around with different "" or '' around YES and NO. I'm not sure I'm doing this right.
I am tracking scroll depth (25%, 50%, 75%, 90%) and want to create an aggregate view of total events per URL. I also want to create a % of the total events per URL at each scroll depth interval. For example, I want to know the % of the total events for a URL at each interval. Is this possible in GDS?
Do you know of a list of “Characters” (Not sure what to call them) like: .* & | & $ & ^
I am trying to create a Dimension that combines different ad groups. Each of these ad groups has a common word between them, but it varies in its “position” within the name. For Example:
Cheap Car Insurance
I guess I am specifically looking for the symbol i can use to say:
“Choose the Ad Groups with the word [Car] in them regardless of its position within the name”
Very useful, Ahmed!
But I’m trying to filtering/categorize with a custom field with an example like this:
WHEN REGEXP_MATCH(Campaign, “BLU”) THEN “Colour Blue”
WHEN REGEXP_MATCH(Campaign, “GRE”) THEN “Colour Green”
WHEN REGEXP_MATCH(Campaign, “CAR”) THEN “Product Car”
WHEN REGEXP_MATCH(Campaign, “MOT”) THEN “Product Motorbike”
And works fine for campaigns with only one “tag”, like MOT or GRE, for example.
I have also campaigns with more than one tag, for example CAR+GRE, and the REGEXP only returns the first true condition, in this case, CAR. But I’m not able to categorize both, CAR and GRE.
Do you know how to achieve this?
I tried something similar but it says the formula is invalid. This is to group into remarketing or display based on partial string match in the campaign name. Thoughts?
WHEN REGEX_MATCH(Campaign name,”Remarketing”) THEN “Remarketing”
WHEN REGEX_MATCH(Campaign name,”Display”) THEN “Display”
Can you combine multiple criteria? I just want to call all campaigns that contain “East” “East” and all that contain “West”, “West” etc. Here is what I’m going for:
CASE WHEN REGEXP_MATCH(campaign,”^(east|$”) THEN “East” or
case when REGEXP_MATCH(Campaign,””(west|$””)then “West”or
case when REGEXP_MATCH(Campaign,””(mid|$””)then “Mid” else “0”
CASE WHEN REGEXP_MATCH(Device Category, “desktop|mobile|tablet”) THEN “All Traffic”
WHEN REGEXP_MATCH(Device Category, “mobile|tablet”) THEN “Mobile Traffic”
WHEN REGEXP_MATCH(Device Category, “tablet”) THEN “Tablet traffic”
ELSE “Other” END
Using this case statement, I’m trying to build a bar graph with 3 bars (all traffic, mobile traffic and tablet traffic). But this doesn’t seem to work – All I see one bar graph with All traffic?
If you think case statement is not the ideal one to use , how to create a calculate field to be able to build that bar graph?
Please help! I look forward to hearing from you.
Clayton, use REGEXP_MATCH, not REGEX_MATCH