How to use CASE statements in Google Data Studio? – 2021

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.

*NEW – By popular demand, I have decided to expand the comprehensive guide on CASE and added IF statements, as the Google Data Studio team has had some incredible updates over the past year. Enjoy!

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?

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!

How to create IF statements in Google Data Studio?

This has been a highly anticipated function and everyone was ecstatic when Google brought it to life. Similar to other platforms, you can use the IF function to return a dimension based on a given condition or rule. The basic syntax is as follow:

IF(condition, true_result, false_result)

Parameters

• Condition. This is the expression to evaluate. It consists of any valid boolean expression.
• True_result: This refers to the value that will be returned if the condition is true. It can be any valid expression.
• False_result: This refers to the value that will be returned if the condition is false. It can be any valid expression

Let’s look at an example. This simple condition will calculate if “Actual Sales” exceed the “Forecast Sales”. If they do, your new column will the “Bonus” by 1.4, which presumably is going to info you sales team. Then, bonuses will be distributed accordingly.

IF(Actual Sales > Forecast Sales, Bonus * 1.4, Bonus)

In addition, you can use a more complex condition with regular expression matching and logical AND. Although AND and OR allow you to test multiple conditions, simple CASE and CASE are the best to use if there are more than two possible results. Let’s see how that works.

IF (Event name = "purchase" AND (REGEXP_MATCH(Page path, ".*clothes.*") OR REGEXP_MATCH(Page path, ".*hoodies.*")), "hoodies Sales", Page title)

As you can see, the IF statement can be quite powerful.

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 (DV360) – 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 (DV360) – 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

GA360 or Google Analytics (Free) – Blog “Page” Sorting

CASE
WHEN REGEXP_MATCH(Page, ‘((?i).*.*/about.*).*’) THEN ‘About Us
WHEN REGEXP_MATCH(Page, ‘((?i).*.*/blog/.*).*’) THEN ‘Blog Posts’’
WHEN REGEXP_MATCH(Page, ‘((?i).*.*/contact$).*’) THEN ‘Contact Us’
WHEN REGEXP_MATCH(Page, ‘((?i).*^/blog$).*’) THEN ‘Blog Page’
WHEN REGEXP_MATCH(Page, ‘((?i).*^/solutions/.*).*’) THEN ‘Solutions Pages’
WHEN REGEXP_MATCH(Page, ‘((?i).*^/services$).*’) THEN ‘Services’
WHEN REGEXP_MATCH(Page, ‘((?i).*.*/blog/category/.*).*’) THEN ‘Blog Category Page’
WHEN REGEXP_MATCH(Page, ‘((?i).*^/products/.*).*’) THEN ‘Product Pages’
WHEN REGEXP_MATCH(Page, ‘((?i).*^/contact/.*).*’) THEN ‘Thank You – Contact’
CASE WHEN REGEXP_MATCH(Page, ‘((?i).*^/$|^/\\?.*).*’) THEN ‘Homepage’
ELSE ‘_Other’
END

I hope this was helpful! If you have any questions, feel free to DM me on LinkedIn or subscribe to my newsletter for more updates 🙂

---

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.