Dashboards | Automated Reporting | Marketing Technology | Web Analytics - Fusion75

View Original

How to write CASE Statements

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.

See this content in the original post

 

Some examples of the syntax in action are below.

Metric example

See this content in the original post

Dimension Example

See this content in the original post

 

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

 

See this content in the original post

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.