Google Data Studio

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, then 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 to Google Data Studio, or our alumni’s post on what GDS is 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 in Data Studio?

In Data Studio, CASE returns dimensions and/or metrics based on a set of conditional expressions.

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

CASE works by evaluating boolean values, and returning the value you specify.

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.

See ‘Social Source’ and more CASE examples below:

Copy/Paste CASE WHEN Examples

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 Data Studio 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’,’’)

READ ALL GDS POSTS


Sign up for our newsletter for more posts like this in your inbox: