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
Source: More Visibility
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
Source: DataStudio.ca
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
Source: Seer Interactive
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
Source: Michael Howe-Ely
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.