06-07 Order of WHEN Clauses in CASE Function

6.7. Order of WHEN Clauses in CASE Function in Looker Studio

Have you ever encountered issues with your CASE custom fields not categorizing data accurately?

It might be because of the succession rule when implementing CASE function with WHEN clauses in Looker Studio.

CASE function evaluates criteria of each successive WHEN clause and returns the first result where the condition is true. Any remaining WHEN and ELSE clauses are not evaluated.

We will see in this lesson how we can workaround this rule so we can categorize dimension fields accurately.

In the CASE function, the order of WHEN clauses is crucial, as the function stops evaluating once it encounters the first matching condition. To ensure the correct results, it's essential to order your WHEN clauses from the most specific to the most generic.

Example

Consider this example where you want to extract the product brand by using CONTAINS_TEXT() function in Looker Studio based on the product title. You have the following criteria:

If the product title contains the word "Google," the brand is "Google."
If the product title contains "Google Cloud," the brand is "Google Cloud."
If the product title contains "YouTube," the brand is "YouTube."
If the product title contains "Android," the brand is "Android."

Here's the initial formula:

CASE
  WHEN CONTAINS_TEXT(product_title, 'Google') THEN 'Google'
  WHEN CONTAINS_TEXT(product_title, 'Google Cloud') THEN 'Google Cloud'
  WHEN CONTAINS_TEXT(product_title, 'YouTube') THEN 'YouTube'
  WHEN CONTAINS_TEXT(product_title, 'Android') THEN 'Android'
END

In this example, products with "Google Cloud" in their title are incorrectly classified as "Google" because the function stops evaluating once it encounters the first matching condition.

To fix this, you need to reorder the WHEN clauses, placing the more specific condition for "Google Cloud" before the more general condition for "Google":

CASE
  WHEN CONTAINS_TEXT(product_title, 'Google Cloud') THEN 'Google Cloud'
  WHEN CONTAINS_TEXT(product_title, 'Google') THEN 'Google'
  WHEN CONTAINS_TEXT(product_title, 'YouTube') THEN 'YouTube'
  WHEN CONTAINS_TEXT(product_title, 'Android') THEN 'Android'
END

By reordering the WHEN clauses, the function now correctly classifies products with "Google Cloud" in their title.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn