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

June 14, 2024

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

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

For data processing in GA4 (Google Analytics 4) or Search Console, many tasks can be performed using calculated fields without the need for Big Query. We will provide specific examples 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 REPLACE Function?

The REPLACE function is used to substitute a specific substring within a text field with another substring. A similar function is the REGEXP_REPLACE function, which utilizes regular expressions to specify string patterns, allowing for bulk replacement of multiple substrings. For simple, fixed-string substitutions, use the REPLACE function. If you need to replace multiple substrings that match a certain pattern, use the REGEXP_REPLACE function.

For example, if you need to replace a specific substring like "https://", you would use the REPLACE function. However, if you need to replace multiple substrings that fit a fixed pattern, such as "4007", "1003", and "4009", all being four-digit numbers, you should use the REGEXP_REPLACE function.

Syntax

The syntax of the REPLACE function is as follows:

REPLACE("Field containing the string to be replaced", "String to be replaced", "String to replace with")

In "Field containing the string to be replaced" specify the field that contains the specific string you want to replace, such as a page URL or page title. In "String to be replaced" specify the string you want to replace. In "String to replace with" specify the string to replace it with. If "String to be replaced" exists, it will be replaced with "String to replace with" You can also specify a blank for "String to replace with" in which case "String to be replaced" will be removed.

How to Use the REPLACE Function

To use the REPLACE 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:Removing Unwanted Strings from Item IDs in GA4 with Looker Studio

Let's assume a scenario where the item ID contains unwanted strings, and you want to display the item ID without these strings in your Looker Studio report. If no functions are used, the item ID appears as follows.

(Quote)Looker Studio

Of course, it can be displayed as is, but we will process it to make the report more readable. We will use the REPLACE function to remove the common "shopify_US_" prefix from all item IDs.

Set the calculated field as follows.