MeasureSchool logo
Search
Close this search box.
Search
Close this search box.

Looker Studio Calculated Fields – The Ultimate Guide

Last Modified on January 4, 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?

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 fieldsChart-specific calculated fields
Require Data Source editor rights?✔️
Work on blended data?✔️
Can include other calculated fields?✔️
Can be used in other reports & charts?✔️
Data source vs. Chart-specific calculated fields

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)

create-new-field-looker-studio

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

create-custom-calculated-field

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.

another-way-to-create-looker-studio-fields

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

create-new-looker-studio-field-one-table

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.

calculate-conversion-rate-formula

Apply the New Field to Your Visualization

Click Save to apply the new field to a chart.

add-custom-metric-to-table

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.

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

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.

conversions-per-impression-calculated-field-example

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.

compare-mobile-traffic-share

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

blend-data-looker-studio

Click Blend data.

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

select-metric-percent-type

Change type from Number to a Percent

compare-calculated-fields-on-a-single-chart

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.

calculate-funnel-drop-off-rate

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

1-(Funnel Stage 2 (Goal 2 Completions))/Funnel Stage 1 (Goal 1 Completions)
conversion-drop-off-rate-percent-type

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

Can I create a calculated field from two different sources?

Yes, you can. However, this does only work in a specific setup. Firstly, you will need to join your data sources to a blended data source. You can then create a chart-level calculated field (with its limitations) on top of your new blended data source.

Unfortunately, you cannot create data source calculated fields on blended data.

Why does my CASE function not work?

One reason I have encountered this in the past is when I included fields of the type date in my CASE formula (i.e. Day of Week). Those fields are internally represented with a number instead of the name of the day (i.e. 1 instead of Monday).

To solve this, you can either use the number in your condition statement or use the field Day of Week Name instead.

Why does my REGEX function not match?

We have all been there and the troubleshooting can be tedious. If it is a Google Analytics use case you can test your regex with the advanced filters in GA. Alternatively, you can use other free regex testers.

Why is my calculated field blank?

Most likely your calculated field function does not return any value for the time period you have selected. By default, Looker Studio displays null in tables if this is the case. However, it can be changed to nothing (blank) as well.

Why are there limitations as to which metrics/dimensions I can use together in a Looker Studio calculated field?

You can either use metrics or dimensions in your function. Trying to mix the two will result in an error. However, there is no inbuilt sense-checking when it comes to calculated fields: You can sum up users and sessions, subtract pageviews and divide by exits if you fancy.

Data source calculated fields can also be used in other calculated fields of the same type (dimension vs. metric), however, this does not work with chart level calculated fields.

💡 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.

MeasureMasters

REOPENED!

Master Data & Analytics with MeasureMasters

Exclusive Courses & Workshops | Ongoing Troubleshooting | Support Resources, Tools & much more

Related Posts

Subscribe
Notify of
guest
19 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Lee Hurst
Lee Hurst
3 years ago

Hi Jochen! Very nice post on calculated fields – you covered a lot! One extra tip for those fields returning null values. You can change nulls to display zero in the display settings as mentioned but it only changes the display and not the actual field value. If you need to change nulls to an actual number 0, possibly for further calculations, you can do this by wrapping the field with the Nary_Max() function – Nary_Max( field, 0) – This will automatically return either the field value or a zero if the field is null. This is particularly handy with… Read more »

Tom F.
Tom F.
3 years ago

Thanks Jochen. Great help!

Guneev Kaur
Guneev Kaur
3 years ago

Hi,
Is there a way to use calculated field in the text box?

Julian Juenemann
Admin
Julian Juenemann
3 years ago
Reply to  Guneev Kaur

no, I’m afraid that’s not possible by default.

Han
Han
3 years ago

where is my formula in chart Calculated fields, i can’t see it

Monica Maure Mortensen
Monica Maure Mortensen
3 years ago

Hello, great article, however I am struggling with a particular problem involving calculated field in blended data and limitations of data source level vs chaar level. do you have an article on this type of issue in datastudio?

Julian Juenemann
Admin
Julian Juenemann
3 years ago

no, sorry I don’t. You have probably a very individual case.

Richard
Richard
3 years ago

Great post! I have a question though. I have two groups of keywords defined, with clicks, impressions, positions, etc. How can use calculated fields to add a new metric that divides the clicks of the first group, by the total clicks, of even clicks of group 2? To get a ‘share’ of the clicks.

Julian Juenemann
Admin
Julian Juenemann
3 years ago
Reply to  Richard

yes, it should be possible with calculated fields, but it depends on how your data sources are structured. Without knowing exactly how your data set looks like it’s hard to make suggestions. A safe bet is always to prepare the data first in Sheets and then use that as data source.

Rachel K
Rachel K
3 years ago

Is it possible to use a chart specific calculated field as a drop down list control for my viewers? Hoping there is a workaround available to make this happen as 2 of my chart specific calc fields are critical to the essence of my dashboard.
Thanks in advance!

Julian Juenemann
Admin
Julian Juenemann
3 years ago
Reply to  Rachel K

I don’t think that’s possible (yet). But let me know if you figure out a workaround

Ziz
Ziz
3 years ago

Hello everyone. Good day and hope you’re doing well. May I ask what is the formula for counting a specific word from google sheet as data source in Data Studio? Thank you so much!

Erik
Erik
3 years ago

Hi Julian,
I wonder if it is possible to create different revenue ranges in data studio?
I want to look at orders that include a specific revenue range. And build it in data studio. Is this possible and how can I do it?

/Erik

Julian Juenemann
Admin
Julian Juenemann
3 years ago
Reply to  Erik

yes, it’s possible. How to do it mainly depends on your data set. You could add another dimension to your data.

Christopher M
Christopher M
3 years ago

Hi Jochen, Great post! I’ve got an issue that I’m hoping you’ve seen before (and might be able to help me with). I’ve have some messy Page Titles that I’ve cleaned up using a regex replace in a data source field (I have full admin rights to the GA account BTW). I can refer to this field in a chart, and it works perfectly, aggregating all the pageviews against the cleaned titles. When I go to use it as a data control (set to report level) though, while the correct numbers show up in the selecting dropdown, when I try… Read more »

Julian Juenemann
Admin
Julian Juenemann
3 years ago
Reply to  Christopher M

no idea, maybe a bug in GDS, maybe one in your data

imran
imran
2 years ago

Hi how can i add filed for GA4 Event , like how can i calculate begin_checkout Event value in Field

marzi
marzi
2 years ago

hi
I have a lot of banner ads on my site
I set two events for each of these banners (impression and click)
Now I want to get the click-through impression ratio separately for each banner.
The problem is that I have an event action dimension that has the impression and click parameter of all banners and a total event as a metric
Do I have to calculate this ratio for each banner separately with Data Blending and create Field, or is there an easier way?

Muhammad Jamshed
Muhammad Jamshed
1 year ago

Hi dear,i want to save the spent of particular facebook campaign in datastudio field,how can we do,appreciate your output.

MeasureSchool Locker

Unlock our Free Tools, Templates and Resources

now it's time to

Start measuring like a master

Itching to jump into the world of MeasureMasters? This is what you have to look forward to.

Ready to take your digital marketing to the next level?

Subscribe to our newsletter and stay ahead with our latest tips and strategies.