The CASE WHEN function allows you to conditionally transform, categorize and shape your data in powerful ways within Looker Studio (formerly Google Studio).We’ll walk through a diverse range of real-world examples that demonstrate how to effectively use CASE WHEN to solve business problems and create dynamic, interactive reports.
What is CASE WHEN?
CASE WHEN statements evaluate a list of conditions and return the first result where the condition is true. The basic syntax is:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
[ELSE else_result]
END
It starts with CASE, followed by one or more WHEN clauses containing a condition and result. An optional ELSE clause at the end specifies a default result if none of the WHEN conditions are met.
CASE WHEN evaluates the conditions sequentially and returns the first result where a condition is true. Once a condition is met, Looker Studio stops evaluating.
A basic starter example
Let’s Categorize Stores by Sales Volume. A common use of CASE WHEN is to categorize dimension values into tiers. Here’s an example of segmenting stores into tiers based on their annual sales volume:
CASE
WHEN Annual_Sales < 500000 THEN "Tier 1"
WHEN Annual_Sales >= 500000 AND Annual_Sales < 1000000 THEN "Tier 2"
WHEN Annual_Sales >= 1000000 AND Annual_Sales < 5000000 THEN "Tier 3"
WHEN Annual_Sales >= 5000000 THEN "Tier 4"
END
This checks the Annual_Sales measure and assigns each store to a tier from 1 to 4 based on sales brackets. In your code you could identify Tier 1 as small stores and Tier 4 as very large volume stores.
By breaking stores into tiers instead of looking at individual stores, we can more easily analyze performance trends across segments. For example, we can create a bar chart showing Profit by Store Tier and see if larger stores tend to have higher profit margins.
We can use the same method to Classify Customers by Purchase Frequency. In addition to numeric values, we can also categorize dimension fields like customer IDs into tiers. Here's an example classifying customers based on their annual purchase frequency:
CASE
WHEN Orders_Past_Year >= 10 THEN "Top"
WHEN Orders_Past_Year >= 5 AND Orders_Past_Year < 10 THEN "Active"
WHEN Orders_Past_Year > 0 AND Orders_Past_Year < 5 THEN "New"
WHEN Orders_Past_Year = 0 THEN "Inactive"
END
This segments customers into Top, Active, New and Inactive groups. We can then analyze metrics like Average Order Value by this Customer Category to gain insights into our best customers and how to optimize spend per segment. This type of simple numerical classification is recurrent in daily business processes.
Evaluate Conditions with OR Logic
CASE WHEN statements can evaluate complex logical conditions using OR and AND operators. This allows more complex analysis. Here's an example using OR to check multiple criteria:
CASE
WHEN Region IN ("APAC", "EMEA") OR Customer_Type = "Wholesale" THEN "Priority"
WHEN Customer_Type = "Retail" THEN "Standard"
ELSE "Other"
END
This categorizes customers from APAC/EMEA regions or wholesale channels as Priority. Retail customers are labeled Standard and all others are assigned Other.
Use Filter Views with Parameters
Parameters provide interactivity within reports. We can use CASE WHEN to filter the view dynamically based on a parameter selection. For example, if we have a Category parameter allowing users to select multiple categories, we could write:
CASE
WHEN Category IN (@{Filter Categories}) THEN Category
ELSE NULL
END
This would display only the selected categories, filtering out any unselected categories by returning NULL. As users update the parameter, the view updates. Remember that you have to create your parameters prior to access them from your code.
How to display Key Metrics with Parameters
In addition to filtering, we can use parameters to toggle the displayed metric. It can be viewed as a switch to go from one metric to another:
CASE
WHEN @{View} = "Units" THEN Total_Units
WHEN @{View} = "Revenue" THEN Total_Revenue
END
Here we allow users to switch between Total Units and Total Revenue metrics in the chart. The view updates based on the parameter selection. This proves really unvaluable to provide different views for the same dataset. For instance, 360° views of data are often use to provide intricate insights from business data.
You can concatenate Fields Conditionally
CASE WHEN can concatenate text fields conditionally to create new values. Let's look at an example building full names from first and last name fields:
CASE
WHEN First_Name IS NULL AND Last_Name IS NOT NULL THEN Last_Name
WHEN Last_Name IS NULL AND First_Name IS NOT NULL THEN First_Name
WHEN First_Name IS NOT NULL AND Last_Name IS NOT NULL THEN CONCAT(First_Name, " ", Last_Name)
ELSE "Name Unknown"
END
This checks if either name field is NULL. If so, it returns the non-NULL value. If both are populated, it combines them into a full name. Any records with completely blank values get labeled "Name Unknown". Try to use this method to create full email or postal addresses for example.
One more use case: calculate Dynamic Shipping Costs
You can use CASE WHEN to categorize based on a calculation. This is about submitting a calculation when a precise condition is met. Here's an example assigning shipping cost tiers based on order total:
CASE
WHEN Order_Total < 50 THEN 5
WHEN Order_Total >= 50 AND Order_Total < 100 THEN Order_Total * 0.1
WHEN Order_Total >= 100 AND Order_Total < 500 THEN Order_Total * 0.05
ELSE 25
END
For small orders under $50, this applies a flat $5 shipping rate. For mid-size orders, it calculates 10% of the order total. Orders over $500 cap at a $25 max shipping cost. Tis type of process if frequently used to apply sales discounts as well.
Summary
CASE WHEN is a function much more powerful than its IF conditional cousin. It opens up endless possibilities for shaping, filtering, and transforming your data within Looker Studio. With conditional logic, you can build dynamic reports that adapt on the fly based on user inputs. The key is structuring your CASE WHEN statements clearly, so the results are understandable and useful.
