What is a CASE Statement?
Life is one big CASE statement! We are living in a series of conditions where if x = true then y happens. If I eat right and exercise, I will lose weight. If I drive to work, I will get there quicker than walking (unless you work from home). If you spend any time inside a database or a BI tool, the CASE statement will become one of your best friends.
By definition, a CASE statement is a function in most databases and BI tools that evaluates data against a set of conditions and produces a value based on the results of those conditions being true or false. The CASE statement is akin to the IF statement in spreadsheet tools like Excel and Google Sheets.
CASE statement syntax
The syntax of a CASE statement is below. The statement must begin with the word CASE and terminate with the word END. Each condition begins with the word WHEN and is followed by the condition to be evaluated. Following the condition you have the word THEN and the result if that condition is true. Rinse and repeat as many times as necessary. Use ELSE to provide a catch-all or default value if no conditions are true.
CASE
WHEN [CONDITION 1]
THEN {RESULT 1}
WHEN [CONDITION 2]
THEN {RESULT 2}
ELSE {RESULT 3}
END
Some examples of the syntax in action are below.
Metric example
CASE
WHEN `Clicks` = 0
THEN 'No Clicks'
WHEN `Clicks` >=1 AND `Clicks` <=10
THEN '1 to 10 Clicks1
ELSE '11+ Clicks'
END
Dimension Example
CASE
WHEN `Month` = 'June' OR `Month` = 'July' OR `Month` = 'August'
THEN 'Summer'
WHEN `Month` = 'March' OR `Month` = 'April' OR `Month` = 'May'
THEN 'Spring'
WHEN `Month` = 'September' OR `Month` = 'October' OR `Month` = 'November'
THEN 'Autumn'
WHEN `Month` = 'December' OR `Month` = 'January' OR `Month` = 'February'
THEN ' Winter'
ELSE 'Month Unknown'
END
Practical Applications of a CASE statement
If you're pulling data from Google Analytics into a Google Data Studio dashboard, you may want to create your own channel groupings if you haven't done so in Google Analytics. For simplicity's sake, let's say you want to create four channels + an "Other" channel for catching all unclassified traffic:
Search
Social
Email
Direct
Other
You could have a CASE statement that evaluates the source/medium in your Google Analytics data to produce a new channel field
CASE
WHEN `source/medium` = 'google/cpc' OR `source/medium` = 'bing/cpc' OR `source/medium` = 'google/organic' OR `source/medium` = 'bing/organic'
THEN 'Search'
WHEN `source/medium` = 'facebook/cpc' OR `source/medium` = 'instagram/cpc' OR `source/medium` = 'twitter/cpc'
THEN 'Social'
WHEN `source/medium` = '%Hubspot%'
THEN 'Email'
WHEN `source` = 'direct'
THEN ' Direct'
ELSE 'Other'
END
The CASE statement is your friend
CASE statements are not new, but if they're new to you, definitely try creating one in Google Data Studio, BigQuery, Domo, Tableau, or whatever platform you’re using. Do pay attention to the limitations and rules for whatever platform you happen to be using at the time. For example, Domo requires field names to be wrapped in tick marks (` … `), but BigQuery will let you get away with not placing the tick marks around the field names.
As always, if you have data, simple or complex, that requires CASE statements, don't hesitate to contact us for help.
