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.
CASE WHEN statements evaluate a list of conditions and return the first result where the condition is true. The basic syntax is:
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.
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:
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:
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.
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:
This categorizes customers from APAC/EMEA regions or wholesale channels as Priority. Retail customers are labeled Standard and all others are assigned Other.
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:
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.
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:
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.
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:
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.
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:
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.
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.
Become a Looker Studio expert
for free
Learn all the secrets of data analysis and create beautiful and effective dashboards thanks to our 30-second video courses! Join Catchr's community on YouTube.
Get more video lessons
All your data on Looker Studio
Build Looker Studio dashboard easily with your marketing data from all platforms and track your essential KPIs! No-code integration.
Start free trial now!
Optimize your data analysis
Get free Looker Studio dashboard template among a large collection of +50 stunning template! Elevate your data visualization.
Get free templates!.png)
