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

July 20, 2024

In this article, we will clearly explain how to use the ENDS_WITH function in Looker Studio and provide specific examples of its application. Utilize the functions available in Looker Studio to process and visualize data.

Regarding data processing for GA4 (Google Analytics 4) and Search Console, it is often possible to handle it using Looker Studio's calculated fields without relying on Big Query. The ENDS_WITH function is used to determine whether a given string ends with a specified substring. We will explain specific examples based on GA4 data using connectors, so please make use of it.

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 ENDS_WITH Function?

The ENDS_WITH function determines if a field ends with a specified string. If the field ends with the specified string, it returns true; otherwise, it returns false. The function is case-sensitive, so you need to specify the string correctly.

There are similar functions like CONTAINS_TEXT and REGEXP_CONTAINS. While the ENDS_WITH function checks if the search string is at the end of the field string, the CONTAINS_TEXT function checks if the search string is anywhere within the field string. The REGEXP_CONTAINS function uses regular expressions to determine if the target string is within the field. Using regular expressions allows you to specify not just a fixed number of characters but also patterns within the string. There are several other functions in Looker Studio that utilize regular expressions, as well as multiple functions that serve as alternatives to the ENDS_WITH function. Since using regular expressions requires some knowledge, consider whether it is absolutely necessary to use regular expressions for what you want to achieve. If not, consider using functions that do not require regular expressions.

For a list of functions that use regular expressions, their alternatives, and detailed instructions on how to use regular expressions, please refer to the official help documentation below.

※Official Help for Looker Studio: Regular Expressions in Looker Studio

Syntax

The syntax for the ENDS_WITH function is as follows:

ENDS_WITH("search_target", "string_to_search")

For "search_target," specify the field that you want to check if it ends with a specific string.
For "string_to_search," specify the string that serves as the criterion for the check. It will

How to Use the ENDS_WITH Function

To use the ENDS_WITH 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.

Use case: Categorizing GA4 page paths that end with a specific string in Looker Studio

We will categorize based on the pattern in which the GA4 page path string ends. For example, if the page path ends with the string "report," it will be categorized as "Report." If it ends with the string "template," it will be categorized as "Template." Anything else will be categorized as "Other."

First, set the calculated fields as follows.

(Quote:Looker Studio)

① Field Name: Please enter any field name.

② Formula:

CASE
WHEN ENDS_WITH(Page path,"report") THEN "Report"
WHEN ENDS_WITH(Page path,"template") THEN "Template"
ELSE "Other"
END

Using the CASE function, we are creating classifications based on the last string of the page path. We have created three types: reports, templates, and others, but more classifications can be created.

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

A field called the Page path classification has been created as follows.

(Quote:Looker Studio)

Next, we'll add the previously created fields to the report so that they can be utilized. Add the "Page path classification" to the dimensions and "Views" to the metrics, as shown below.