In this post, we are going to explore one of the most useful functions in Google Data Studio, the CASE. I personally think that knowing how to write a CASE is not only helpful in Data Studio, but also it allows you to understand how computer logic works. In addition, I have added a section on how to compose the new Simplified CASE statements. These are part of Google’s August 27, 2020 feature update.
What is the Data Studio CASE function?
The CASE function returns dimensions and metrics based on conditional expressions; it is often used to create new groupings of data and sort them in categories. Let’s imagine the following scenario. In the table below, you have a paid digital marketing campaign that includes “EN” and “FR” in the campaign name. However, you want to see the table below only per “Language”. This is where you can use the CASE function to write a specific statement.
The trick with the CASE function is that you often have to find a unique common denominator to parse the data correctly. In the statement below, I am opening the CASE with a WHEN, specifying the first condition. Then adding IN, which instructs Data Studio that I will be listing text strings. Finally, I go ahead and choose my campaign name(s) and specify with THEN what I want to name the result of my WHEN condition. I know…. this is a bit of a tongue twister.
The result is exactly what we were looking for. Now if we want to see the campaign simply filtered by the “Language” dimension, we can even exclude “Campaign”.
How to use REGEXP_MATCH function within CASE?
Now, what happens if things get more complicated. For example, in the “Complex Campaign” dimension, we have a lot of different and unique campaign iterations. If we were to use IN, then the list in the CASE function is going to grow substantially and we are more likely to make a mistake while writing it. This is why we are going to try out the REGEXP_MATCH function, also referred to as regex.
Quick background: Regular expressions are a notation for describing sets of character in a specific word or any text string. Google Data Studio (and most Google products) uses the RE2-style syntax.
We start the WHEN statement the same way, without much difference. However, the modified dimension i.e. “Campaign” is inside brackets, unlike IN where it is outside. All we are doing with the regular expression is telling Data Studio that every time the capital letters “EN” or “FR” are spotted in the “Complex Campaign” dimension, the text strings should be named “English” and “French”, respectively. In a little bit more detail:
How to write simplified CASE statements by declaring a field to test once? (NEW)
In recent feature updates, Google has made it even easier to write repetitive CASE statements by declaring the field at the beginning. For example, if you have the same “Campaign” name, but this time, you want to use the year as a matching criteria.
Following the same logic as our past examples, you would normally write this standard CASE statement.
However, with the simplified CASE statement, you can do the following and save a bit of time!
Data Studio CASE examples from the Media Agency world:
These are few of the most common CASE statements across the Google Marketing Platform (GMP), ready to be copy/pasted!
Google Campaign Manager (CM) – Language
CASE WHEN REGEXP_MATCH(Campaign, '.*(EN).*') THEN 'English' WHEN REGEXP_MATCH(Campaign, '.*(FR).*') THEN 'French' ELSE 'Other Language' END
Display and Video 360 – Language
CASE WHEN REGEXP_MATCH(Insertion Order, '.*(EN).*') THEN 'English' WHEN REGEXP_MATCH(Insertion Order, '.*(FR).*') THEN 'French' ELSE 'Other Language' END
Display and Video 360 – Standard Media Tactics
CASE WHEN REGEXP_MATCH(Line Item, '.*(Remarketing).*') THEN 'Remarketing' WHEN REGEXP_MATCH(Line Item, '.*(Lookalikes).*') THEN 'Lookalikes' WHEN REGEXP_MATCH(Line Item, '.*(Prospecting).*') THEN 'Prospecting' ELSE 'Other Tactic' END
Google Ads – Device Type
CASE WHEN REGEXP_MATCH(Campaign, '.*((M|m)obile).*') THEN 'Mobile' WHEN REGEXP_MATCH(Campaign, '.*((T|t)ablet).*') THEN 'Tablet' WHEN REGEXP_MATCH(Campaign, '.*((C|c)omputer).*') THEN 'Desktop' ELSE 'Other' END
Google Ads – Age Breakdown (this cleans up your default age dimension)
CASE WHEN Age IN ('Undetermined') THEN 'Unknown' WHEN Age IN ('18to24') THEN 'A18-24' WHEN Age IN ('25to34') THEN 'A25-34' WHEN Age IN ('35to44') THEN 'A35-44' WHEN Age IN ('45to54') THEN 'A45-54' WHEN Age IN ('55to64') THEN 'A55-64' WHEN Age IN ('gt64') THEN 'A65+' ELSE 'N/A' END
Dynamic Dashboards and Data Analysis with Google Data Studio
If you want to learn how to build powerful data visualizations and unlock insights that can help you drive business results for your clients or employers, take a look at my full course on Udemy.