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

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

For processing data from GA4 (Google Analytics 4) or Search Console, many tasks can be accomplished using calculated fields without needing to use Big Query. The REGEXP_MATCH function utilizes regular expressions, and employing regular expressions in GA4 and Looker Studio can significantly enhance your data visualization and analysis capabilities. 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 REGEXP_MATCH Function?

The REGEXP_MATCH function is used to determine whether a string in a field exactly matches a pattern using regular expressions. If a pattern that fully matches the string is found, it returns true; if no match is found, it returns false. Since the determination is case-sensitive, it is necessary to specify the string correctly.

Syntax

The syntax for the REGEXP_MATCH function is as follows:

REGEXP_MATCH("target_string", "regular_expression_pattern")

In "target_string," specify the data field you want to determine if it matches exactly. In "regular_expression_pattern," specify the string that serves as the matching criteria using regular expressions.

What are Regular Expressions?

Regular expressions are special strings used to describe specific patterns within text strings. By using regular expressions, you can specify strings such as the following:

• A string that starts with "abc" and has five characters can be represented by the regular expression ^abc.{2}$
• A string containing a hyphen between a three-digit number and a four-digit number can be represented by the regular expression ^\d{3}-\d{4}$
• A string that ends with "abc" can be represented by the regular expression .*abc$

The key characteristic of regular expressions is their ability to specify not just specific strings but patterns of strings, which allows for a high degree of flexibility in the strings that can be specified. For instance, if there are two strings, "abc" and "abcde", a normal search specifying "abcde" would not include "abc". However, by using regular expressions, both can be included in the search results.

 For a detailed description of how to write regular expressions, please refer to the official Looker Studio Regular Expressions help.

Difference from the REGEXP_CONTAINS function

There is a similar function called REGEXP_CONTAINS. The difference is that the REGEXP_MATCH function determines a complete match of the entire string of the specified field, whereas the REGEXP_CONTAINS function determines a partial match within the string of the specified field.

How to Use the REGEXP_MATCH Function

To use the REGEXP_MATCH 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: Classifying Search Console Keywords in Looker Studio

Let's consider a scenario where you classify Search Console keywords by focus words. Keywords that start with "seo" are classified as "SEO," and those that start with "ga4" are classified as "GA4." First, set the calculated fields as follows.

(Quote)Looker Studio

 

① Field Name: Please enter any field name.

② Formula: 

CASE
WHEN REGEXP_MATCH(Query,"^seo.$|^SEO.$") THEN "SEO"
WHEN REGEXP_MATCH(Query,"^ga4.$|^GA4.$") THEN "GA4"
ELSE "OTHER"
END

Here, it is used in combination with the CASE function. This regular expression specifies that if the search keyword starts with "seo" or "SEO", it outputs "SEO"; if it starts with "ga4" or "GA4", it outputs "GA4"; otherwise, it outputs "OTHER".

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

 A field called the target_keyword has been created as follows. 

 (Quote)Looker Studio

Next, the fields created earlier are now available for use in the report, so let's add them to the table. Add "target_keyword" to the dimensions, and "Impressions" and "Clicks" to the metrics as shown below.

 (Quote)Looker Studio

 We were able to visualize the number of impressions and clicks for each focus keyword group set in this way.

Use Case: Determining if GA4 Page Titles Contain Specific String Patterns in Looker Studio

We will determine if page titles in GA4 contain specific string patterns using regular expressions. The goal is to detect string patterns that include a four-digit number within square brackets, as shown below:

$4007$ GA4 Monthly Report Template... $4003$ Google Ads Report... $1005$ GA4 Monthly Report Template... $4011$ SEO Monthly Report Template...

First, set up the calculated field as follows:

 (Quote)Looker Studio

 

① Field Name: Please enter any field name.

② Formula: 

REGEXP_MATCH(Page title, '^\\[\\d{4}\\].*')

The following regular expression can be explained as a string pattern that starts with a “[”, followed by a four-digit number, then a “]”, and is followed by any string of characters.

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

 A field called the product_pagehas been created as follows.

 (Quote)Looker Studio

 Next, we will add the fields we created earlier to the report so they can be utilized. Add the product page to the dimensions as shown below.

 (Quote)Looker Studio

This revealed that there are 284 pages with page titles that do not contain the specified string pattern. You can check if appropriate page titles are set.

Since the REGEXP_MATCH function determines whether a string matches a specific pattern, you can, for example, filter for true values only or count the number of true values to display only the page titles with the specific string pattern or to count their number.

Relevant Looker Studio Official Documentation

Looker Studio Official Help : About calculated fields

Looker Studio Official Help : Function list