CASE and IF Conditional Expressions Demystified

In BigQuery, conditional expressions are used to perform conditional logic within SQL queries.

Two conditional expressions commonly used are CASE and IF.

If you are already familiar with these statements, you can skip this post. However, if you are new to SQL then continue reading.

CASE Statement

In the CASE statement, you first specify an expression that needs to be evaluated. Then you specify one or more conditions that you want to match with the expression, if the condition is met then the corresponding result is returned.

Optionally, you can also specify a value to be returned in the ELSE clause. If none of the specified conditions is met then the value in the ELSE clause is returned. If the ELSE clause is not specified then a NULL value is returned.

Here is the SQL syntax for the CASE Statement.

SELECT 
CASE
  WHEN conditon1 THEN result1
  WHEN condition2 THEN result2
  WHEN condition3 THEN result3
  ...
  WHEN conditionN THEN resultN
  [ ELSE else_result ]
  END
FROM 'your_project_id.your_dataset_id.your_table_id'

For example, If I want to get a list of all the events but instead of the actual event name, I want to return the name as “eCommerce" or “General” depending on if the event is part of the eCommerce funnel or not. Here is the SQL I will use:

SELECT 
CASE 
WHEN event_name = 'purchase' THEN "eCommerce"
WHEN event_name = 'view_item' THEN "eCommerce"
WHEN event_name = 'begin_checkout' THEN "eCommerce"
ELSE "General"
END
FROM `optizent-test-project.analytics_253837362.events_20240105` 

When the above SQL is executed, the event_name column value for each record will be evaluated. If the event_name is purchase, view_item, or begin_checkout then “eCommerce” will be returned else “General” will be returned.

Note: In the above example, I only specified 3 eCommerce events but you can specify as many as you want.

CASE expr Statement

Similar to the CASE statement, there is also a CASE expr statement. In this statement, CASE is followed by a condition (expression), which is evaluated and then the result is compared with values in the WHEN statements.

Here is the sample SQL statement:

SELECT 
CASE expr
  WHEN match_value1 THEN result1
  WHEN match_value2 THEN result2
  WHEN match_value3 THEN result3
  ...
  WHEN match_valueN THEN resultN
  [ ELSE else_result ]
  END
FROM 'your_project_id.your_dataset_id.your_table_id'

Using CASE expr syntax, we can transform the previous CASE statement to the following:

SELECT 
CASE event_name
WHEN 'purchase' THEN "eCommerce"
WHEN 'view_item' THEN "eCommerce"
WHEN 'begin_checkout' THEN "eCommerce"
ELSE "General"
END
FROM `optizent-test-project.analytics_253837362.events_20240105` 

Note: event_name is now after the CASE statement.

IF Statement

IF statement is similar to the CASE statement but if it evaluates one condition only. The syntax for the IF statement is as shown below.

IF statement is followed by three values in the parenthesis. The first value is the expression or condition to be evaluated, the second is the value that needs to be returned if the condition matches. If the condition doesn’t match then the third value is returned.

SELECT IF (expr, true_result, else_result)
FROM 'your_project_id.your_dataset_id.your_table_id'

Here is an example SQL Query that returns ‘eCommerce’ if the event_name is ‘purchase’ and returns ‘General’ if the event_name is not equal to ‘purchase’

I will be showing more examples of these statements as I dive into various use cases in future posts.

For now, go ahead and practice these so that you are ready for the advanced examples.

If you want to fast-track your learning then check out the BigQuery with Marketers and Marketing Analysts Course. With the course, you also get my support to help you understand and master SQL and BigQuery.

If you are not ready for the course then check out the premium subscription of this newsletter. This is the least expensive option for you to learn and master these concepts.

Happy Learning!

Anil Batra, Your Digital Marketing Analytics Coach

P.S. If your organization needs BigQuery or GA4 help then reach out to me at [email protected] or fill out the form on Optizent.com