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

July 17, 2024

In this article, we will clearly explain how to use the CASE (Simple) function in Looker Studio and provide specific examples of its application. By utilizing functions available in Looker Studio, you can process and visualize data effectively.

Many aspects of data processing for GA4 (Google Analytics 4) can be accomplished using calculated fields without the need for BigQuery. We will explain concrete 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(Simple) Function?

The CASE (simple) function is used to return different values based on specified conditions for a single dimension or metric. It is very useful for categorizing data by conditions or converting notations. For example, using this function, GA4 data in English notation can be converted to Japanese.

Similar functions include the CASE function and the IF function. While the same results can be obtained regardless of which function is used, the recommended use cases from the official guidelines are as follows:

Use the IF function when specifying conditions for one or more dimensions or metrics and the result options are two (e.g., dividing device types into two categories: PC and others).
Use the CASE (simple) function when specifying conditions for one dimension or metric and the result options are three or more (e.g., dividing device types into three or more categories: PC, mobile, tablet, and others).
Use the CASE function when specifying conditions for multiple dimensions or metrics and the result options are three or more (e.g., combining conditions of device type and OS type to divide into three or more categories such as mobile-safari, mobile-chrome, PC-chrome, etc.).

Syntax

The syntax for the CASE(Simple) function is as follows

CASE "Dimension"

WHEN "Condition 1" THEN "Result 1"

WHEN "Condition 2" THEN "Result 2"

WHEN "Condition 3" THEN "Result 3"

ELSE "Other Result"

END

In "Dimension," specify the dimension or metric you want to target with the conditions.

For "Condition 1," specify the string or numerical condition. This could be a specific string or a number greater than a certain value. The same applies to "Condition 2" and "Condition 3." In "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."

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

Precautions for Using the CASE (Simple) Function

There are a few points to keep in mind when using the CASE (simple) function.

Ensuring uniform result types

The results can be specified as either strings or numbers, but within the same CASE (simple) function, you must unify the type. For instance, you cannot have Result 1 as a string and Result 2 as a number.

When multiple conditions are met

If a dimension or metric matches multiple conditions, the result of the first matching condition 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(Simple) Function

To use the CASE(Simple) 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 IF function in detail, using GA4 (Google Analytics 4) data as an example.

Use Case: Grouping Ages in Looker Studio

We will create custom age groups in GA4. To do this, define the groups by setting up calculated fields as follows.

(Quote:Looker Studio)

① Field Name: Please enter any field name.

② Formula:

CASE age
WHEN "18-24" THEN "18-44"
WHEN "25-34" THEN "18-44"
WHEN "35-44" THEN "18-44"
ELSE age
END

③ Save: Once you have completed entering the information, click save.

A field called the age_group has been created as follows.

(Quote:Looker Studio)

Next, let's add the dimension we created earlier to make it available in the report. Once added, you will see that it is correctly reflected in the table.

(Quote:Looker Studio)

In this way, the CASE (simple) function is a convenient tool that can be used to transform data and categorize it. Please make sure to take advantage of it.