[Looker Studio] How to Use the CASE Function and Practical Examples | Calculated Fields

June 12, 2024

[Looker Studio] How to Use the CASE Function and Practical Examples | Calculated Fields

This article provides a clear explanation of how to use the CASE function in Looker Studio and its specific use cases. By leveraging the functions available in Looker Studio, you can process and visualize data effectively.

When it comes to processing GA4 (Google Analytics 4) data, you can often achieve this using calculated fields without the need for BigQuery. We will explain specific use cases based on GA4 data using connectors, so please make use of this information.

What are Calculated Fields in Looker Studio?

Calculated Fields in Looker Studio are a handy feature that allows you to create custom fields by using operators (such as addition, subtraction, multiplication, division), functions, and regular expressions based on existing items for use in reports.

Also, for basic usage of Looker Studio, please refer to the "How to Use Looker Studio" guide. Looker Studio is a very convenient tool that is free to use and allows for the creation of easy-to-understand reports by connecting to various data sources, so let's make active use of it.

What is the CASE Function?

The CASE function is used to return different values based on specified conditions for multiple dimensions or metrics. It is very useful for classifying data based on conditions or transforming notations.

Similar functions include the simple CASE function and the IF function. While any of these functions can yield the same result, the recommended usage from official sources is as follows:

Use the IF function when specifying conditions for one or more dimensions or metrics and the result has two options. (Example: Dividing device types into PC and others)
Use the simple CASE function when specifying conditions for one dimension or metric and the result has three or more options. (Example: Dividing device types into PC, mobile, tablet, and others)
Use the CASE function when specifying conditions for multiple dimensions or metrics and the result has three or more options. (Example: Combining conditions for device type and OS type to classify into mobile-safari, mobile-chrome, PC-chrome, etc.)

Syntax

The syntax of the CASE function is as follows:

CASE
WHEN "Condition 1" THEN "Result 1"
WHEN "Condition 2" THEN "Result 2"
WHEN "Condition 3" THEN "Result 3"
ELSE "Other Result"
END

For "Condition 1" specify the string or numeric condition. For example, if it matches a specific string or is greater than a certain number. The same applies to "Condition 2" and "Condition 3"

For "Result 1" specify the value you want to output when the specified dimension or metric meets Condition 1. The same applies to "Result 2" and "Result 3"

For "Other Result" specify the value you want to output when none of the specified conditions are met.

Points to Note When Using the CASE Function

There are several points to be aware of when using the CASE function.

Unifying the Result Type

Although you can specify either strings or numbers as results, you must unify the result type within the same CASE function. For instance, you cannot have one result as a string and another as a number within the same CASE function.

When Multiple Conditions are Met

If dimensions or metrics meet multiple conditions, the first matching result will be returned. For example, if a value matches both condition 1 and condition 2, the result for condition 1, which is listed first, will be output.

How to Use the CASE Function

To use the CASE function, you need to create a calculated field in Looker Studio. There are two types of calculated fields: data source calculated fields and chart-specific calculated fields. This article will focus on creating a data source calculated field, but for more detailed differences between the two, please see the differences between data source calculated fields and chart-specific calculated fields.

I will explain how to use the CASE function with an example using GA4 (Google Analytics 4) data.

Use case:Ranking GA4 Pages in Looker Studio

Rank the GA4 pages based on the number of sessions on each page. Define the rank by setting up the calculated fields as follows.

(Quote)Looker Studio

① Field Name: Enter any field name.

② Calculation Formula:

CASE
WHEN Sessions > 5000 THEN "A"
WHEN Sessions BETWEEN 2500 AND 5000 THEN "B"
WHEN Sessions < 2500 THEN "C"
END

We have defined the session counts as follows: more than 5,000 sessions as A, between 2,500 and 5,000 sessions as B, and less than 2,500 sessions as C.

③ Save: Click save once you have completed the input.

This creates a new dimension called Page Rank.

(Quote)Looker Studio

Now, let's add the dimension we just created so that it can be used in the report. You will see that it is correctly reflected in the table.

(Quote)Looker Studio

Use case:Ranking GA4 Pages in Looker Studio Using Multiple Criteria

Next, we will rank the pages based on multiple criteria including not just the number of sessions but also the average session duration and the number of views. Define the rank by setting the calculated fields as follows.

(Quote)Looker Studio

① Field Name: Enter any field name.

② Calculation Formula:

CASE
WHEN average session duration > 120 AND impressions > 10000 THEN "A"
WHEN average session duration > 120 AND impressions > 1000 THEN "B"
WHEN average session duration between 20 and 120 AND impressions > 1000 THEN "C"
ELSE "D"
END

We classify as follows: if the average session duration is more than 120 seconds and views are more than 1,0000, it is classified as A; if the average session duration is more than 120 seconds and views are more than 1,000, it is classified as B.

③ Save: Click save once you have completed the input.

This creates a new dimension called Multi-criteria PageRank.