KPIs Studio Library

CASE WHEN Data Studio Library

Step up the game with this CASE WHEN formulas collection

Are you familiar with that feeling of intense hunger, when you’re out of your hometown, you’re in a hurry and you don’t have enough time to have a proper meal in a restaurant because you’re missing your flight or bus? 

What’s the first thing you do? I, for one, enter a supermarket and grab a ready-to-eat snack box or sandwich. 

Well, in case you’re wondering what this has to do with the topic, I’ll tell you: we have prepared a CASE WHEN Data Studio library with ready-to-use formulas. This library is meant to come in handy for any busy data visualization specialist, data analyst or marketer. Don’t forget to step in more often, cause we’ll keep adding fresh formulas.

What Is The CASE WHEN Data Studio Function?

CASE statements use conditional expressions to determine the value of a calculated field.

It returns dimensions and metrics based on conditional logic. Using the “if this then that” branching logic, the CASE function is one of the most powerful and advanced formulas you can learn in Data Studio. 

One of the most common uses for CASE formula Data Studio is to create new categories or groupings of data.

CASE Formula Data Studio - The Syntax

The CASE statement syntax is simple. It begins with CASE, it ends with END, and in between you have a series of clauses.

CASE

    WHEN condition THEN result

    [WHEN condition THEN result]

    […]

    [ELSE result]

END

Like this:

After the August 27th 2020 Data Studio update CASE statements are now simplified, meaning that you need to declare the field to test once and then write the values:

CASE field

  WHEN value1 THEN result1

  WHEN value2 THEN result2

  WHEN value3 THEN result3

END

CASE WHEN Data Studio Examples

Here are the CASE WHEN examples we have gathered so far. 

Keep in mind that you can customize them according to your needs. If you need help with how to do that, ask us.

CASE WHEN for grouping your traffic sources

Source: KPIs Studio library

CASE

WHEN REGEXP_MATCH(Source, ‘.*facebook.*’) THEN ‘Facebook’

WHEN Source = ‘Facebook’ AND Medium = ‘(not set)’ THEN ‘Facebook’

WHEN REGEXP_MATCH(Source, ‘.*instagram.*’) THEN ‘Instagram’

WHEN Source = ‘t.co’ THEN ‘Twitter’

WHEN Source = ‘google’ AND Medium = ‘cpc’ THEN ‘Google Ads’

WHEN Source = ‘google’ AND Medium = ‘organic’ THEN ‘Google Organic’

WHEN Source = ‘(direct)’ THEN ‘Direct’

ELSE Source / Medium

END

CASE WHEN for grouping your campaigns by the stage in the sales funnel

Source: KPIs Studio library

CASE 

WHEN REGEXP_MATCH(Campaign name, “((?i).*tofu).*”) THEN “TOFU”

WHEN REGEXP_MATCH(Campaign name, “((?i).*mofu).*”) THEN “MOFU”

WHEN REGEXP_MATCH(Campaign name, “((?i).*bofu).*”) THEN “BOFU”

ELSE “Other campaign types” 

END

When City - or any other dimension - is (not set) but you want to show it in the totals

Source: KPIs Studio library

You can replace City with any other dimension that you want to see in the totals.

CASE 

WHEN City = ‘(not set)’ then ‘Other cities’

ELSE City

END

CASE WHEN to group countries within different regions

Source: KPIs Studio library

CASE 

WHEN Country IN (“USA”,”Canada”,”Mexico”) THEN “North America” 

WHEN Country IN (“England”,”France”) THEN “Europe” 

ELSE “Other” 

END

CASE WHEN for Click to Call and Contact Us form completions

CASE 

WHEN Contact Us Form (Goal 1 Completions) IS NULL THEN 0 

ELSE Contact Us Form (Goal 1 Completions)

END +

CASE 

WHEN Click to Call (Goal 2 Completions) IS NULL THEN 0

ELSE Click to Call (Goal 2 Completions) 

END

CASE WHEN for age breakdown in Google Ads

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

CASE WHEN for organic versus paid searches

CASE

WHEN REGEXP_MATCH(medium,”^(cpc|ppc|cpm|paid)$”) THEN “Paid”

WHEN REGEXP_MATCH(medium,”organic” THEN “Organic”

ELSE “_Other”

END

Data Studio CASE WHEN for combining multiple events into a larger category

CASE

WHEN REGEXP_MATCH(Event Action,”Video play|Downloaded File|Click|”) THEN “Interactions”

ELSE “Other”

END

When you want to compare your Google Analytics metrics during weekdays versus weekends

Source: Siavak

CASE

WHEN Day of Week Name IN(‘Monday’, ’Tuesday’, ’Wednesday’, ’Thursday’, ’Friday’) THEN ‘Weekday’ 

WHEN REGEXP_MATCH(Day of Week Name, ‘Saturday|Sunday’) THEN ‘Weekend’

END

When you want a different kind of conditional formatting - using reaction GIFs to indicate performance

Source: Michael Howe-Ely

Yeah, that’s some creative way of using the CASE calculated fields!

CASE

WHEN Page Views >=1000 THEN “https://i.imgur.com/b6fyd3w.gif”

WHEN Page Views >=900 THEN “https://i.imgur.com/hd7rC6V.gif”

WHEN Page Views >=800 THEN “https://i.imgur.com/6zdseqS.gif”

WHEN Page Views >=700 THEN “https://i.imgur.com/4WeMI66.gif”

WHEN Page Views >=600 THEN “https://i.imgur.com/3ZPFFuh.gif” WHEN Page Views >=500 THEN “https://i.imgur.com/t9bScLe.gif”

WHEN Page Views >=400 THEN “https://i.imgur.com/QQeZcZL.gif”

WHEN Page Views <400 THEN “https://i.imgur.com/NFEkXz5.gif”

ELSE “Other”

END

Jump to the formula you need

We’ll be adding as many useful CASE WHEN Data Studio formulas to this list as we find. If you have any questions, ask us. If you have any suggestions of formulas to add to the list, let us know.

Share this library:

Share on linkedin
Share on facebook
Share on twitter
Share on pocket
Share on reddit

Take control.
Bring data driven decisions at you fingertips