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

In this article, we will clearly explain how to use the LEFT_TEXT function in Looker Studio, along with specific practical examples. By leveraging the functions available in Looker Studio, you can process and visualize your data effectively.

For processing GA4 (Google Analytics 4) and Search Console data, many tasks can be accomplished using calculated fields without needing Big Query. We will illustrate specific use cases of the LEFT_TEXT function 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 LEFT_TEXT Function?

The LEFT_TEXT function is a formula that extracts a specified number of characters from the left side of a string field. A similar function is the REGEXP_EXTRACT function. This function uses regular expressions to specify and extract multiple strings at once based on specific string patterns. When you need to extract a fixed number of characters from the beginning of a string, use the LEFT_TEXT function. On the other hand, if the string you want to specify is not at the beginning and the character patterns vary, use the REGEXP_EXTRACT function.

For example, if you want to extract the first five characters of a page title, use the LEFT_TEXT function. If you need to extract a string where the second level of a directory is composed of two letters followed by three to five digits, use the REGEXP_EXTRACT function. The latter allows for more complex conditions to be specified for string extraction but requires knowledge of regular expressions. For detailed instructions on how to write regular expressions, please refer to the official help documentation below.

Syntax

The syntax for the LEFT_TEXT function is as follows:

LEFT_TEXT("Field to extract text from", "Number of characters to extract from the start")

In the "Field to extract text from," specify the field from which you want to extract the text. In the "Number of characters to extract from the start," specify the number of characters to extract as a numerical value.

How to Use the LEFT_TEXT Function

To use the LEFT_TEXT 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: Extracting the First 4 Characters of a Page Title from GA4 in Looker Studio

Assume a scenario where the first 6 characters of a page title on a site contain the product code, and you want to categorize page titles by product code.

First, set up a calculated field as follows.

(Quote)Looker Studio

1. Field Name: Please enter any field name.

2. Formula: LEFT_TEXT(Page title,4)

3. Save: Once you have entered the information, click Save.

A field named item_code_page_title has now been created as shown below.

 (Quote)Looker Studio

We will actually use the created fields in the report.

 (Quote)Looker Studio

By utilizing the LEFT_TEXT function in this way, the product code page titles correctly display the first 6 digits of the product code. Additionally, by adding metrics such as the number of pages and the number of views, we were able to visualize the number of pages and views for each product code.

Furthermore, I will introduce an advanced technique to format the data without using regular expressions, making it more readable. At this point, we have extracted the first 6 digits as [4007]. To make this more readable by removing the brackets and reducing it to just the 4 digits, we will use the REPLACE function. The steps are as follows:

1. Create a calculated field to remove the "[" from the previously created product code page title using the REPLACE function, resulting in Calculated Field A.
2. Apply the REPLACE function to Calculated Field A to remove the "]", resulting in Calculated Field B.
3. Use Calculated Field B in the report.

Although this process involves several steps, it shows how to combine functions to process data. On the other hand, using the REGEXP_EXTRACT function with regular expressions allows for extracting the 4-digit number in a single step.

Choose the appropriate function based on the data you want to display.

Relevant Looker Studio Official Documentation

Looker Studio Official Help : About calculated fields

Looker Studio Official Help : Function list