Google Data Studio Calculated Fields – The Ultimate Guide

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

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

Imagine waving good-bye 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 Google Data 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.

Introduction

What Are Google Data Studio Calculated Fields?

Calculated fields allow you to apply calculations and other functions on your data to create new metrics and dimensions. Calculated fields 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
  • Analyse funnel drop-off rate
  • Clean URLs & text strings
  • Grouping similar content together

Why Use Calculated Fields in Google Data 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.

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-google-data-studio

Click on Create New Field at the bottom right 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-data-studio-fields

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

create-new-data-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 Data 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.

Google Data Studio Functions

What Are Data Studio Functions?

Data 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, Data Studio functions each have a specific purpose and syntax.

 Google classifies it’s currently 64 Data 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 Data Studio Functions

To get you started we have put together a list of our favorite Data Studio Functions. See the full breakdown of our top 5 Data Studio Functions here.

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 Data 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 Data 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 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 Data 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 Data 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-data-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 Data Studio dashboard, we want to report all traffic under this new URL.

The Solution

  • Use a Data 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 Data 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 Data 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 Data Studio dashboards.

The Solution

  • Use a Data 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 Data 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 Google Data studio gives you those insights also in retrospect.

The Solution

  • Use a Data 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.

FAQs

Why are there limitations as to which metrics/dimensions I can use together in a google data 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.

Why does my CASE function not work?

One reason I have encountered 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.

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 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, Data Studio displays null in tables if this is the case. However, it can be changed to nothing (blank) as well.

Summary

Using Google Data 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 Data 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. Data Studio calculated fields can be an essential part in facilitating data-driven decision making.

Did you find out something new about Google Data 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 Google Data Studio here.

Jochen Setzer
About the author: Jochen Setzer

Jochen Setzer is working freelance as a Digital Marketing Consultant for international businesses. He has over 10 years of industry experience working from both the client and agency side. Jochen embraces the data-driven world of digital marketing and is an active member of the MeasureMasters community. When he is not sitting behind his screen creating fancy dashboards he is feeding his four chickens or is getting lost trail running through the beautiful Blue Mountains near Sydney.

2
Leave a Comment

avatar
2 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
Tom F.Lee Hurst Recent comment authors
newest oldest most voted
Lee Hurst
Guest
Lee Hurst

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.
Guest
Tom F.

Thanks Jochen. Great help!