Looker Studio Calculated Fields – The Ultimate Guide

Last Modified on June 6, 2024

Looker Studio calculated fields are a super powerful tool that you should be using if you do anything in Looker Studio (ex Google Data Studio).

Imagine solving the Rubik’s Cube of wrongly configured goals and filters, non-retroactive content groupings, and inconsistent UTM parameters in your Google Analytics account.

Imagine waving goodbye to manual and messy data manipulations in Google Sheets, broken VLOOKUP-functions, and easily broken reports.

Imagine turning your monthly analytics reporting from a hard martial arts session to a quick jog around the block.

Well, say hello to Looker Studio Calculated Fields.

This guide is meant to provide you with a solid foundation that you can build upon. 

If you are anything like us, applying calculated fields to optimize your reporting will put you closely in touch with your stakeholders’ individual needs.

Here’s an overview of what we’ll cover:

• What are Looker Studio calculated fields?
• Why use calculated fields in Looker Studio?
• Where can you use calculated fields?
• Step by Step: Your First Calculated Field
• Looker Studio Functions
• Example Use Cases for Looker Studio Calculated Fields
• FAQ

What Are Looker Studio Calculated Fields?

A Calculated field is a formula that performs an action on single or multiple other fields in a data source.

Calculated fields allow you to apply calculations and other functions to your data to create new metrics and dimensions. They can be used to extend and transform the information in your data sources.

A few things Calculated fields can help you do:

• Calculate the conversion rate between two metrics

• Analyze funnel drop-off rate

• Clean URLs & text strings

• Grouping similar content together

Why Use Calculated Fields in Looker Studio?

Whenever you want to present data that is not already available in your source data, calculated fields are your BFF. They allow you to make your data:

• Prettier (i.e. by cleaning up your URL report)

• More insightful (i.e. by concatenating hostname + landing page path)

• More actionable (i.e. by relating results to set targets). 

Presenting your data in the most user-friendly and unambiguous way is paramount if you want your stakeholders to trust your recommendations and take appropriate action on them.

Pivot tables can also prove useful when presenting data. They are perfect for summarizing trends and answering business questions!

Where Can I Use Them?

Calculated Fields can be used in any chart type. However, only Chart level calculated fields can be used on blended data sources.

What Are Data Source Calculated Fields?

Data source calculated fields are added to the data source and can be reused in other charts, and reports.

Chart Level Calculated Fields?

Chart level calculated fields can only be used on one particular chart however they work on blended data.

Step by Step: Your First Calculated Field

Plan Your Calculated Field

When you sketch your dashboard’s design, take inventory of all KPIs that are not directly available in a single data source. Define which data sources, dimensions, metrics, and functions you will need to create those.

Example: Your client’s lead generation website has three contact forms that are each tracked as separate goals in Google Analytics. However, in the dashboard they want you to report the Contact Form Conversion Rate for all three contact form goals combined.

To create a Calculated Field Goal Conversion Rate (All Contact Forms) you will need one Google Analytics view as a data source, your individual goal completion metrics, and your session metric. Some basic arithmetic in a calculated field will get us to the desired result.

Add a New Field to Your Data Source / Chart

Decide whether you want to create a Data Source or Chart Level Calculated Field with the help of our little comparison table.

	Data source calculated fields	Chart-specific calculated fields
Require Data Source editor rights?	✔️	❌
Work on blended data?	❌	✔️
Can include other calculated fields?	✔️	❌
Can be used in other reports & charts?	✔️	❌

For now, let’s go ahead and create a data source level calculated field.

Add any chart to your page that can take multiple dimensions and metrics as inputs (i.e. a table)

Click on Create New Field at the bottom right of your screen.

Create a chart specific Calculated Field, then add any chart to your page that can take multiple dimensions and metrics as inputs (i.e. a table)

Click on either Add Dimension or Add Metric in the chart editing pane.

Click Create Field at the bottom of the opening dimension/metric selection box.

Example: Since we want to use our calculated field Goal Conversion Rate (All Contact Forms) in other reports as well and we do have the Data Source editor rights, we opt for a Data Source Calculated Field.

Enter Your Function

In the formula field, you can define how your existing dimensions or metrics should be manipulated.

For your first calculated field, you might want to stick to the basics and just do a simple arithmetic calculation between metrics.

The green check indicates that your formula does not contain any syntax errors. When you hit Save Looker Studio performs another sense check but this time also considers actual data.

Example: Following our example we simply add our Google Analytics Goals and divide them by the metric Sessions. Don’t forget to apply the round braces.

Apply the New Field to Your Visualization

Click Save to apply the new field to a chart.

Now we get to see our newly created custom field in action. It appears alongside the standard metrics and dimensions of the data source and can be added in the same way.

To summarize, here are the steps for creating Looker Studio calculated fields:

1. Plan your calculated field. Define which data sources, dimensions, metrics, and functions you will need to create your calculated field.
2. Add a new field to your data source/chart. Decide whether you want to create a Data Source or Chart-Specific calculated field.
3. Enter your function. Define how your existing dimensions and metrics should be manipulated in the formula field.
4. Apply the new field to your visualization. Save the calculated field and apply it to a chart.

Looker Studio Functions

What Are Looker Studio Functions?

Looker Studio functions are formulas that can be used in calculated fields to manipulate and combine existing fields in more advanced and complex ways than basic arithmetics allow.

Similar to formulas in spreadsheet software like Excel or Google Sheets, Looker Studio functions each has a specific purpose and syntax.

Google classifies its currently 64 Looker Studio functions into six categories: 

• Aggregation (like AVG to compute the average)

• Arithmetic (like ROUND to round numbers)

• Date (like WEEKDAY to return the weekday of a date)

• Geo (like TOCOUNTRY to return country names for ISO codes)

• Text (like CONCAT to concatenate text)

• Miscellaneous (like CASE to return values based on conditional statements)

5 Important Looker Studio Functions

To get you started we have put together a list of our favorite Looker Studio Functions.

UPPER / LOWER

Do you need to sum up your data by a specific value but struggle with mixed upper- and lowercase spellings? While this can also be addressed with filters in GA they will not work retroactively. With the Looker Studio function UPPER / LOWER you can address this with ease. The function takes one dimension as input and converts it into its uppercase or lowercase equivalent.

Example formula (with Google Ads source):

UPPER(Campaign)

Input : US Brand Product 

Output: US BRAND PRODUCT

CONCAT

Do you need to join multiple text fields to make your data more understandable? Showing the Google Analytics dimensions ‘hostname’ and ‘page’ in one table column is a common use case for the CONCAT function. You can have multiple dimensions as inputs and the function returns the concatenation of all fields.

Example formula (with Google Analytics data source):

CONCAT(hostname, page)

Input: www.googlestore.com, /google+redesign/youtube

Output: www.googlestore.com/google+redesign/youtube

REGEX_EXTRACT

Do you need to pull out parts of a field’s values? A common use case for REGEX_EXTRACT is the extraction of query parameter values that are stored in your ‘page’ dimension. This function takes one dimension and a regular expression as inputs and it returns a text value.

Example formula (with Google Analytics data source):

REGEXP_EXTRACT(Page, 'thankyou=([^&]+)')

Input: /global/en/contact?thankyou=de

Output: de

REGEX_MATCH

Do you need to know if a field’s value meets specific conditions? While this can be achieved in a multitude of ways, using the REGEX_MATCH function makes the result reusable for other functions.

A common use case for this function is to classify search queries whether they contain one of your predefined brand terms.

Similarly to the REGEX_EXTRACT this function takes one dimension and a regular expression as inputs. However, unlike REGEX_EXTRACT, it just returns boolean values. 

Example formula (with Search Console data source):

REGEXP_MATCH(Query, '(apple|iphone)')

Input: iphone 11

Output: true

CASE

The CASE function is an if this, then that type formula and it can be used for more complex conditional expressions. As a digital marketer, this function can help in cleaning up messed up channel groupings in Google Analytics.

All those wrongly tagged email and paid search campaigns – now is the time to put them in the right traffic bucket. The CASE function takes one or more conditions as inputs and returns the dependent results.

Example formula (with Google Analytics data source):

CASE 
WHEN REGEXP_MATCH(Page,'.*utm_medium=e-mail.*') THEN "Email"
WHEN REGEXP_MATCH(Page,'.*utm_medium=paid-search.*') THEN "Paid Search" 
ELSE Default Channel Grouping 
END

Input: /landingpage?utm_source=newsletter&utm_medium=e-mail&utm_campaign=discount-coupon

Output: Email

Example Use Cases for Looker Studio Calculated Fields

Here are a couple of use cases for using calculated fields in Looker Studio:

• Custom Conversion Rate
• Comparing Mobile Traffic Share Development Between Countries
• Funnel Drop-Off Rate
• Cleaning URLs: Moved Pages
• Cleaning URLs: Duplicate URLs
• Case Inconsistencies
• URL Word Separators
• Removing Query Parameters
• Grouping Content

Let’s check them out!

Custom Conversion Rate

Conversion rate is a standard metric in the Google Analytics and Google Ads data sources. However, sometimes those standard metrics don’t provide the required insights and we need to look for alternatives.

Example: When comparing two ad variants of a display campaign the KPI Conversion Per Impression (CPI) might be more insightful. 

The Solution

Select your Google Ads Account as a data source. Create a new data source type calculated field.

For example, to find the Conversions Per Impression (CPI) we would input Conversions / Impressions into our field.

Apply the new field to your chart by clicking Save.

Comparing Mobile Traffic Share Development Between Countries

Do you want to show how the mobile traffic share is changing over time and its differences in your target markets? The ability to compare segments in one Looker Studio chart is possible through a conjunction of calculated fields and blended data.

The Solution

Create the segments in Google Analytics (GA). Create a blended data source of those GA segments in Looker Studio. Apply your segments to otherwise identical charts showing the metric(s) you’ll need for your calculated field.

Rename your metrics to reflect the applied segment (i.e. Sessions AU, Mobile Sessions AU)

Click Blend data.

Create chart level calculated fields since data source level calculated fields are not possible with blended data.

Change type from Number to a Percent.

Finally, apply these calculated fields as metrics to your chart.

Funnel Drop-Off Rate

Do you want to show the conversion dropoff through the different funnel stages? While Google Analytics goal funnels can provide some insight, they only work if all your funnel stages equal a pageview. They fall short when your funnel steps are event-based. In our example, we assume that you have created a Google Analytics goal for each of your funnel stages.

The Solution

Create calculated fields calculating the conversion drop-off rate for all your funnel steps.

Conversion drop-off rate formula for step 1 in our funnel

1-(Funnel Stage 2 (Goal 2 Completions))/Funnel Stage 1 (Goal 1 Completions)

Change the metric type from Number to Percent.

You can display the Conversion drop-off calculated fields as scorecards alongside a horizontal bar chart showing the absolute numbers of funnel stage goal completions

Cleaning URLs

We have probably all come across reports that distracted our attention from the main conclusion and saw us focus on benign mistakes. Inconsistent URLs in the report let stakeholders doubt our conclusions, even if the skewing effect is minimal.

Cleaning your URLs with calculated fields is a simple exercise. We are addressing fixes for three common scenarios of page dimension pollution.

Moved Pages 

For one reason or another, a page’s URL has changed over time. However, in terms of reporting both URLs should be folded into one. Let’s assume for this exercise that the YouTube Marshall Ballpen was moved from the stationary category to the YouTube brand category.

Previous URL:

https://www.googlemerchandisestore.com/Stationery/Writing/GGL1346_YouTube-Marshall-Ballpen

New URL:

https://www.googlemerchandisestore.com/Brands/YouTube/GGL1346_YouTube-Marshall-Ballpen

In our Looker Studio dashboard, we want to report all traffic under this new URL.

The Solution

• Use a Looker Studio function to replace the old URL with the new one in a calculated field

• Create a data source calculated field by using the REGEXP_REPLACE function

Example formula:

REGEXP_REPLACE(Page, '/Stationery/Writing/GGL1346_YouTube-Marshall-Ballpen', '/Brands/YouTube/GGL1346_YouTube-Marshall-Ballpen')

Duplicate URLs

Are some pages reachable via different URLs? Depending on your CMS and server configuration it is not uncommon that pages can be reached both with and without trailing slashes.

Depending on how those pages are linked internally and externally will determine how much traffic is reported for each version. Fixing this retroactively for you reporting will ensure the consistency of your data.

The Solution

• Use a Looker Studio function to remove trailing slashes from URLs apart from the root /

• Create a data source calculated field by using the REGEXP_REPLACE function

Example formula:

REGEXP_REPLACE(Page,"./","")

Case Inconsistencies 

If your web server environment is case sensitive, URLs might appear with inconsistent cases in your reporting. Spending a few minutes to consolidate the data in your dashboards is time well spent. 

The Solution

• Use a Looker Studio function to change all your URL characters to lowercase

• Create a data source calculated field by using the LOWER function

Example formula:

Lower(page)

URL Word Separators

There has been lots of debate around the best URL word separators ( +, – or _ ) from an SEO perspective.

If you are dealing with an older website, chances are that the employed URL word separators have been changed over time. Time to consolidate them in your Looker Studio dashboards.

The Solution

• Use a Looker Studio function to replace all + and _ in URLs with –

• Create a data source calculated field by using the REGEXP_REPLACE function

Example formula:

REGEXP_REPLACE(Page,'(\\+|_)','-')

Removing Query Parameters

Removing query parameters from a list of URLs is a common use case when you are breaking down metrics by the page dimension.This can be done with Google Analytics filters for future data or new setups, however, it’s not a fix for the data already collected.

The Solution

• Use a Looker Studio function to remove query parameters 

• Create a calculated field by using the REGEXP_REPLACE function

Example formula:

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

Grouping Content

Do you need to group your metrics by specific content types to gain better insights? Setting up content groups in Google Analytics is the standard way of doing this for future data.

Grouping content with the help of calculated fields in Looker Studio gives you those insights also in retrospect.

The Solution

• Use a Looker Studio function to create custom content groups

• Create a data source calculated field by using the CASE function

• Example formula Custom Content Groups:

CASE 
WHEN (Page="/en" OR Page="/de" OR Page="/es") THEN "Home" 
WHEN (REGEXP_MATCH(Page, '^/../shop/.*')) THEN "Webshop"
WHEN (REGEXP_MATCH(Page, '^/../blog/.*')) THEN "Blog"
WHEN (REGEXP_MATCH(Page, '(^/../company/.*|.*career.*)')) THEN "Company"
WHEN (REGEXP_MATCH(Page, '^/../support/.*')) THEN "Support"
WHEN (REGEXP_MATCH(Page, '.*/contact$')) THEN "Forms"
ELSE "Other" 
END

We are using the CASE function in conjunction with the REGEX_MATCH function to define the content groups. The power of regex allows you to define more complex matching rules.

Be aware of overlapping conditions in the different WHEN statements: The CASE function will always execute the first clause it matches.

Example: The page /blog/top-tips-to-boost-your-career will be grouped in the category Blog although it is also matching the Company category. You can customize the above to be the best fit for your specific use case.

🚨 Note: Check out our handy guide on the top five Looker Studio Functions for calculated fields to customize your data.

FAQ

💡 Top Tip: Adding tooltip annotations is a great way to add extra information to your Looker Studio reports!

Summary

Using Looker Studio calculated fields has certainly helped us to bring our reporting to the next level.

Whether it’s by using it to substantially grow stakeholders’ interest in and demand for customized reports or to gain entirely new insights into your data, they are a must if you use Looker Studio. 

As a digital marketer, your success will always be dependent on your ability to present relevant data in the most intuitive and engaging form.

Looker Studio calculated fields can be an essential part of facilitating data-driven decision-making.

In case you want to pull data from third-party data sources in GDS, check out our handy guide on Looker Studio Connectors.

Did you find out something new about Looker Studio calculated fields? If so we would love to hear from you in the comments.

And if you want to increase your understanding of this awesome reporting tool further, then check out our full course on Looker Studio inside of our MeasureMasters membership.