What You Can Do with Looker Studio's Calculated Fields

The capabilities of using calculated fields can be broadly categorized into the following three:

1. Creating new metrics using numbers and standard operators (addition, subtraction, multiplication, division)
2. Processing data using functions
3. Creating branching logic using CASE statements

Understanding these may seem abstract, so in the following sections, we will introduce specific ways to use calculated fields and practical examples using handy functions. Please consider them as references.

How to Use Looker Studio's calculated field

Calculated fields can be easily created in three steps. Here, we will try to create a "new user rate," which is not a default metric, using the GA4 data source.

Select "Add calculated field" from the data source

Open the Looker Studio data source, click "Add Field" at the top right, and then you will see two options. Choose "Add calculated field."

  Enter "Field Name" and "Formula," then save

Set it as follows and save. Note that these settings can be changed later.

• Field Name: Enter a name that is easy to understand later
• Formula: For the new user rate, enter "number of new users / number of active users"

(Optional) Change the type

After saving, close the settings screen, and the list of fields will be displayed. If you enter the "new user rate" that you just set in the search box at the top right, the item you created will be displayed as follows.

The default type is numerical (e.g., 0.5), but since you want to display the new user rate as a percentage (50%), change the type to "%" using the dropdown menu.

This completes the setup for the calculated field. Once the setup is complete, the item can be used in reports immediately as shown below.

List of Useful Functions for Calculated Fields in Looker Studio

Here, we introduce a list of useful functions for creating calculated fields.

The "HYPERLINK" function that can generate a link URL from text data

The HYPERLINK function converts text into a clickable hyperlink. The basic syntax is as follows:

HYPERLINK("URL", "Link Text")

• "URL" specifies the address of the hyperlink destination. This is the webpage URL that opens when clicked.
• "Link Text" is the text displayed on the link. This is visible to the user, and clicking on it will take them to the specified URL.

Example of use: Creating a list of page titles with links in GA4 in Looker Studio

Set up the calculated field as follows:

HYPERLINK(Full page URL, Page title)

Using this in an actual report creates a list of clickable page titles, and clicking on one opens the actual URL in a new window.

The 'REGEXP_REPLACE' function that can transform or delete text matching certain conditions into another string

The REGEXP_REPLACE function replaces text within a string that matches a regular expression pattern with another string. The basic syntax is as follows.

REGEXP_REPLACE(target_string, 'pattern_to_search', 'replacement_string')

• target_string: The original text you want to modify.
• 'pattern_to_search': Parts matching this pattern within the target string are searched for.
• 'replacement_string': The text that matches is replaced with this string. 

Example use: Excluding parameters from the full URL of a page in Looker Studio

Set up a calculated field as follows. This function removes all characters following the '?' symbol from the full URL of the page.

REGEXP_REPLACE(full page URL, '\?.+', '')

This makes the 'full page URL' dimension, with parameters excluded, available for use.

  The "CASE" function that can transform data based on specific conditions

The CASE function can be utilized in various cases, such as data classification, labeling, and transforming data based on specific conditions. For instance, when categorizing products by price range, you would set it up as follows:

In this example, if the price is less than 100, it is labeled "Inexpensive", the range between 100 and 500 is "Standard", and above 500 is "Expensive". If none of the conditions are met, it displays as "Price Undefined".

  Use case: Converting GA4 Device Categories in Looker Studio

The device categories in GA4 are, by default, classified as follows:

• desktop
• mobile
• tablet

By setting up calculated fields, we can convert each category as follows:

With this, you now have access to your own custom device categories.

  Related Looker Studio Official Documentation

Looker Studio Official Help: About calculated fields

Looker Studio Official Help: Function List

We also recommend this article. 

[Looker Studio]How to download the report as PDF

The function to download reports as PDFs in Looker Studio (formerly Data Studio) is very useful for sharing reports internally and externally as reporting materials, or printing them on paper for use as presentation materials. This article provides an easy-to-understand explanation of how to use and what to expect from the PDF download function.

How to represent GA4 content groups in Looker Studio

GA4 content group is a grouping of specific content within a site. For example, you can group content by blog category, site menu, etc. This allows you to analyze visitor preferences and parts of your site. Normally, content groups are set up using Google tag manager, but Looker Studio offers a similar setup, so if you cannot use Google tag manager but want to group pages, try this.

[Looker Studio] How to export data from a graph to a Google Spreadsheet or CSV (Excel)

Looker Studio (formerly Data Studio) allows you to export the graphs you create in a variety of formats. This article provides a clear explanation of the benefits, usage, and caveats of the export function.