How to Create CASE Statements in Google Data Studio

This post was originally published in 2019, and last updated December 2021.

If you’re used to creating Segments and Advanced Filters in Google Analytics, and you’re using Google Data Studio to visualize your data, this post is for you.

There are serious limitations to Google Data Studio’s ability to understand, interpret, and apply your filters and segments from Google Analytics. Of course these features are helpful for basic users of GDS, but if you want to take your analysis and visualization a step further, keep reading.

I’ll be walking through the CASE statement in Data Studio, and how it can be used to create new fields for better analysis of your data from the 75+ data sources GDS currently connects to.

If you’re new to Data Studio, this post is more advanced than we’d recommend starting out with. Instead, read through my Beginner’s Guide before diving deeper into specific ways of manipulating the data you bring into the platform using the following:

1. A ‘FIELD’  is a dimension or a metric which is used in your report. After you’ve added a data source, all of the fields within it will be available to use in your charts. Google Data Studio highlights dimensions in green and metrics in blue.
2. CALCULATED FIELDS (or custom fields) allow you to create new metrics and dimensions based on existing metrics and dimensions available from within your data source.

What is CASE?

Purpose

CASE lets you create new fields that use conditional logic to determine the field values. CASE is most often used to create new categories or groupings of data.

Learn more in this Help article from Google.

Syntax

CASE statements consist of the 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.

How CASE Statements Work

For example, if you’re as sick of Google Analytics limited social attribution as we are, I'd recommend using Data Studio to create a CASE statement that groups common social sources into single fields.

CASE WHEN Examples

See copy/paste CASE examples below:

Social Source

CASE 
WHEN REGEXP_MATCH(Source / Medium, "((?i).*facebook|fb|f\\.b).*") THEN "Facebook" 
WHEN REGEXP_MATCH(Source / Medium, "((?i).*twitter|t\\.co|tweet|feed).*") THEN "Twitter" 
WHEN REGEXP_MATCH(Source / Medium, "((?i).*pinterest|pin).*") THEN "Pinterest" 
WHEN REGEXP_MATCH(Source / Medium, "((?i).*youtube|yt|video).*") THEN "YouTube" 
WHEN REGEXP_MATCH(Source / Medium, "((?i).*instagram|ig|insta).*") THEN "Instagram" 
WHEN REGEXP_MATCH(Source / Medium, "((?i).*linkedin|lnkd\\.in).*") THEN "LinkedIn" 
WHEN REGEXP_MATCH(Source / Medium, "((?i).*reddit).*") THEN "Reddit" 
ELSE "All Other Sources" 
END

Channel Type

CASE 
WHEN Default Channel Grouping = "Direct" THEN "Direct & Referral" 
WHEN Default Channel Grouping = "Referral" THEN "Direct & Referral" 
WHEN Default Channel Grouping = "Email" THEN "Social & Email" 
WHEN Default Channel Grouping = "Social" THEN "Social & Email" 
WHEN Default Channel Grouping = "Display" THEN "Paid Media" 
WHEN Default Channel Grouping = "Paid Social" THEN "Paid Media" 
WHEN Default Channel Grouping = "Paid Search" THEN "Paid Media" 
ELSE Default Channel Grouping 
END

Page Engagement

CASE 
WHEN Page Depth in ("1","2","3") THEN "Less than 4" 
WHEN Page Depth in ("7","4") THEN "4-7" 
WHEN Page Depth in ("11") THEN "10 or more" 
ELSE Page Depth 
END

Content Type

CASE 
WHEN REGEXP_MATCH(Page, "((?i).*^/$|^/\\?.*).*") THEN "Homepage" 
WHEN REGEXP_MATCH(Page, "((?i).*.*/about.*).*") THEN "About" 
WHEN REGEXP_MATCH(Page, "((?i).*^/services$).*") THEN "Services" 
WHEN REGEXP_MATCH(Page, "((?i).*^/products/.*).*") THEN "Products" 
WHEN REGEXP_MATCH(Page, "((?i).*^/solutions/.*).*") THEN "Solutions" 
WHEN REGEXP_MATCH(Page, "((?i).*^/blog$).*") THEN "Blog" 
WHEN REGEXP_MATCH(Page, "((?i).*.*/blog/.*).*") THEN "Blog Posts" 
WHEN REGEXP_MATCH(Page, "((?i).*.*/contact$).*") THEN "Contact" 
WHEN REGEXP_MATCH(Page, "((?i).*^/contact/.*).*") THEN "Thank You" 
ELSE "All Other Pages" 
END

DIY Template

Use our handy Google Sheets template below to start automating and scaling the process of creating new CASE statements in Data Studio:

Copy GSHEETS Template

Other Helpful Functions

1. Clean up your inconsistent campaign tagging with LOWER (Campaign)
2. Group leads and other high value users you’re tracking with COUNT (User ID)
3. Combine fields for more granular data with CONCAT:
   • (User Type , Device Category)
   • (Landing Page , Count of Sessions)
   • (Channel , Count of Sessions)
   • ((Event Category , Event Action , Event Label)(“ | ” , Page))
4. Replace full URL page names with page paths using REGEXP_REPLACE (Page,’https://www.seerinteractive.com’,’’)

Want more Google Data Studio posts in your inbox?