- GA4 BigQuery For Marketing Analytics
- CASE and IF Conditional Expressions Demystified
CASE and IF Conditional Expressions Demystified
Leveraging GA4 Data in BigQuery
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.
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.
WHEN conditon1 THEN result1
WHEN condition2 THEN result2
WHEN condition3 THEN result3
WHEN conditionN THEN resultN
[ ELSE else_result ]
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:
WHEN event_name = 'purchase' THEN "eCommerce"
WHEN event_name = 'view_item' THEN "eCommerce"
WHEN event_name = 'begin_checkout' THEN "eCommerce"
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:
WHEN match_value1 THEN result1
WHEN match_value2 THEN result2
WHEN match_value3 THEN result3
WHEN match_valueN THEN resultN
[ ELSE else_result ]
Using CASE expr syntax, we can transform the previous CASE statement to the following:
WHEN 'purchase' THEN "eCommerce"
WHEN 'view_item' THEN "eCommerce"
WHEN 'begin_checkout' THEN "eCommerce"
Note: event_name is now after the CASE 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)
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.
Anil Batra, Your Digital Marketing Analytics Coach