All your customer data in one place

No-Code Data Pipeline for Google Data Studio

Integrate data from multiple sources and visualize it easily in Google Data Studio.

Using Google Data Studio Calculated Fields: Easy Steps 101

• July 13th, 2020

google data studio calculated fields

Google Data Studio Calculated fields are a powerful tool for its users. If you are frustrated by inconsistent naming conventions, custom organizational acronyms, or retroactive data, calculated fields may be the right solution for you. 

Sometimes when using Google Analytics, changes made to a dimension are not applied retroactively. After inheriting a website using Google Analytics, you may find that it has poorly formatted UTM (Urchin tracking module) parameters. Maybe, you want to get ratios rather than exact numeric values but you don’t know how to get them using Google Analytics. 

All of these challenges can be solved using the Google Data Studio calculated fields and formulas. In this article, you will understand how to use calculated fields and formulas in Google Data Studio. All you’ll need is a Google Data Studio account.

Table of Contents

What is Google Data Studio?

Google Data Studio:
Image Source

Google Data Studio is a tool developed by Google for data visualization. Data Studio lets you create different visualizations from your data to depict the relationships among different variables. It comes with different visualization tools that you can use to visualize your data. Examples include pie charts, line graphs, bar graphs, google maps etc. 

Data Studio comes with many connectors that you can use to extract your data from different data sources. You can also move your data into Google Sheets, then you pull it from there into Data Studio. You can then create reports using your data. Data Studio makes it easy for you to collaborate on reports and share them with other users. 

To access Data Studio, copy and paste the following URL on your web browser:

https://datastudio.google.com

You will be prompted to log into your Data Studio account. If you already have a Google account that you use to access services like YouTube and Gmail, you can use it to log into Data Studio. After a successful login, you can begin to use Data Studio for free. 

What are Google Data Studio Calculated Fields?

In Data Studio, a calculated field is a user-generated metric or dimension. Calculated fields become useful when you need to answer questions that cannot be answered using the available fields in the data set. So, creating a calculated field is just developing new metrics or dimensions from the existing data. With calculated fields, you can answer custom strategic questions that existing dimensions cannot answer. 

A good example is when you need to know the ratio of new users to old users. Maybe, you want to know how this ratio has changed from time to time by comparing the percentage of a year to that of the previous year. The available data fields may not be able to answer this, but calculated fields can. 

The benefit of using Google Data Studio calculated fields is that they can be inserted in visualizations just like the pre-populated metrics or dimensions. This means that in most cases, a calculated field can interact with a standard session metric, date dimension, or goal conversion completion. This will give you more control over how you report and present your data. 

A calculated field can also be used within another calculated field. Calculated fields can be a data source or a chart level. Data source calculated fields are normally added to the data source and they can be reused in other reports and charts. Chart level calculated fields can only be used on a particular chart and they work on blended data. Blended data is data that is obtained from multiple data sources. 

The data sources must have a common shared key that will be used as the join key. Data source calculated fields require data source editor rights but chart level calculated fields don’t. They can also include other calculated fields but chart level calculated fields can’t. However, data source calculated fields don’t work on blended data like chart level calculated fields. 

Simplify your ETL & Analysis with Hevo’s No-code Data Pipeline

A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ Data Sources (including 40+ Free Sources) to a Data Warehouse/Destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. In addition to the 100+ data sources, Hevo also allows integrating data from non-native sources using Hevo’s in-built Webhooks Connector.

Get Started with Hevo for Free

Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line. 

Check out some of the cool features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Connectors: Hevo supports 100+ Integrations to SaaS platforms, Files, Databases, BI tools such as Tableau, and Native REST API & Webhooks Connectors. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; Databricks (Connector Live Soon!); and MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.  
  • Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (Including 40+ Free Sources) such as Tableau that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

Why Use Calculated Fields in Google Data Studio?

Calculated fields are your best friends when you need to present data that isn’t already in your source data. They enable you to make your data:

  • More appealing (i.e. by cleaning up your URL report)
  • More in-depth (for example, by concatenating hostname + landing page path)
  • More practical & actionable (i.e. by relating results to set targets).

If you want your stakeholders to trust your recommendations and act on them, you must present your data in the most user-friendly and unambiguous way possible.

Types of Google Data Studio Calculated Fields

Google Data Studio Calculated Fields can be categorised into 2 types:

Google Data Studio Calculated Fields: Categories
Image Source

1) Data Source Schema Specific Calculated Fields

These are the Google Studio calculated Fields which are created in a Data Source schema. Whenever the calculated fields are created in a Data Source schema, then they are available in any report which uses that specific Data Source Schema.

Data Source Schema Specific Google Data Studio Calculated Fields can be further classified into:

  • Data Source Schema Calculated Dimensions
  • Data Source Schema Calculated Metrics

2) Chart Level/Chart Specific Calculated Fields

These Google Data Studio Calculated Fields are created in a specific chart of a report. When any calculated field is created in a chart, then it is only available in which you have created it. However, a Chart Specific Calculated Field with blended data can be used.

Chart Specific Google Data Studio Calculated Fields can be further classified into:

  • Chart Specific Calculated Dimensions
  • Chart Specific Calculated Metrics

How to Use Calculated Fields in Data Studio?

You can use calculated fields to answer questions that couldn’t be answered with data in its current state. That includes doing everything from creating new custom metrics to transforming multiple dimensions in order to analyze the data in different ways.

Some helpful ways to use Google Data Studio Calculated fields include:

1) Making New Metrics

You can create your own metrics by simply entering the metric’s formula into the calculated field.

A) Consolidate Goals

If you want to view two separate goals as a single metric, simply add them together:

Goal 1 + Goal 2

B) Find Conversion Rates from Funnel Stages

If you use Google Data Studio to track conversion funnel stages, you can calculate conversion rates from stage to stage by creating your own conversion rate metric:

Stage 2/Stage 1

2) Tracking Progress to Goals

You can make a scorecard that tracks your progress toward goals by calculating [Goal Metric]/Goal Value:

Sessions/10,000

3) Combining Dimensions

You can use the CONCAT function to combine multiple dimensions (for example, Hostname + Landing Page Path to make your URLs clickable):

CONCAT(Hostname, Landing Page)

4) Removing Trailing Slashes

If your site has a mix of trailing and non-trailing slashes on URLs, which is causing your data to split in reporting, this tip is only a temporary solution. You should first fix the problem by enforcing either trailing or non-trailing slashes on URLs to prevent duplicate content – otherwise, you’re just masking the problem.

To remove the trailing slash from any pages that end with one, you can use REGEX_REPLACE:

REGEXP_REPLACE(Landing Page,”/$”,””)

5) Keeping Case Consistent

The best solution here is to maintain consistent case in your URLs, which is similar to the solution for removing trailing slashes in URL strings. However, if you need to consolidate dimensions by forcing lowercase, there is a simple formula:

LOWER(Landing Page)

For further information on using Calculated Fields in Google Data Studio, you can visit here.

How to Add Data Source Calculated Fields in Google Data Studio?

Data source calculated fields are available in all reports that use that data source. 

To create it, follow the steps given below:

  • Step 1: Load your data into the reports page. 
  • Step 2: Click the “ADD A FIELD” button that is located at the bottom right corner of the properties window. 
  • Step 3: A new window will pop up from the bottom of the screen. Click the “ADD A FIELD” button located at the far right. 
  • Step 4: Type the name of the field in “Field Name”. Note that this is the name that will appear on your reports. However. The name can be changed for individual charts by editing it in the metric and dimension picker. The name of the field must be unique. Also, a reserved keyword cannot be used as a field name.
  • Step 5: Enter the formula in the “Formula” field. If you need to select a metric, dimension, or function, just begin to type it. If you have fields that share names or fields that duplicate part of a function name such as Names1 and Names2, begin to type the name, then scroll through the suggested list to choose the one that you want. 
  • Step 6: Click the SAVE button or the “UPDATE” button if you were editing an already existing field. 

If you go back to the report, you will see that the data source calculated field has been created. 

Just as we specified in the formula, it shows the product of the “Price” and “Quantity” columns per row. 

Limitations of Data Source Calculated Fields

Following are the limitations of data source calculated fields:

  • They cannot be used with blended data. 
  • You are required to have edit rights on the data source so as to create/edit calculated fields. 

How to Add a Chart Level Calculated Field in Google Data Studio?

Chart level calculated fields only exist within the chart in which you have created them. 

To add it, you can follow the steps given below:

  • Step 1: Add a chart to your report. 
  • Step 2: Select the chart. 
  • Step 3: Click the “+ Add dimension” or “+ Add metric” button from the properties window on the right depending on the type of field that you need to create. 
  • Step 4: A new window will pop up with the list of available fields. Click the “+ CREATE FIELD” button. 
  • Step 5: Enter the name of the field and the formula.
  • Step 6: Click the “APPLY” button. 

It will then be applied to the chart that you have selected. 

Limitations of Chart Level Calculated Fields

Following are the limitations of chart level calculated fields:

  • You cannot reference other chart-level calculated fields in your formula, even when the fields are defined in the same chart. 
  • To create chart-level calculated fields in a report, you must be added to the report as an editor. 
  • You must have enabled “Field Editing in Reports” in the data source.

Example Use Cases for Google Data Studio Calculated Fields

The example use cases for Google Data Studio Calculated Fields are as follows:

1) Custom Conversion Rate

Conversion rate is a common metric in Google Analytics and Google Ads data. However, those standard metrics do not always provide the necessary insights, and we must look for alternatives.

Example: When comparing two display campaign ad variants, the KPI Conversion Per Impression (CPI) may be more informative. 

The Solution:

The steps to be carried out are as follows:

  • Step 1: As the data source, select your Google Ads account. Make a new calculated field data source type. To find the Conversions Per Impression (CPI), for example, enter Conversions / Impressions into our field.
Google Data Studio Calculated Fields: conversions-per-impression-calculated-field-example
Image Source
  • Step 2: By clicking the “Save” button, you can add the new field to your chart.

2) Comparing Mobile Traffic Share Development Between Countries

Example: Do you want to demonstrate how mobile traffic share changes over time and how it differs in your target markets? Through the use of calculated fields and blended data, it is possible to compare segments in a single Data Studio chart.

The Solution:

The steps to be carried out are as follows:

  • Step 1: In Google Analytics, create the segments (GA).
  • Step 2: In Google Data Studio, create a blended data source of those GA segments.
  • Step 3: Apply your segments to otherwise identical charts that show the metric(s) required for your calculated field.
Google Data Studio Calculated Fields: compare-mobile-traffic-share
Image Source
  • Step 4: Rename your metrics to reflect the segment you’re using (i.e. Sessions AU, Mobile Sessions AU)
Google Data Studio Calculated Fields: blend-data-data-studio
Image Source
  • Step 5: Select the “Blend data” option.
  • Step 6: Since data source level calculated fields are not possible with blended data, create chart level calculated fields.
Google Data Studio Calculated Fields: select-metric-percent-type
Image Source
  • Step 7: Here, you can modify the type from “Number” to a “Percent“.
Google Data Studio Calculated Fields: compare-calculated-fields-on-a-single-chart
  • Step 8: Finally, add these calculated fields to your chart as metrics.

Conclusion

In this article, you have learnt the purpose of calculated fields in Google Data Studio. You have also learnt how to create a data source as well as chart level Google Data Studio Calculated fields. Moreover, you have also seen the limitations Google Data Studio calculated fields entail.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Visit our Website to Explore Hevo

Hevo Data with its strong integration with 100+ Data Sources (including 40+ Free Sources) allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows integrating data from non-native sources using Hevo’s in-built REST API & Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools. 

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding Google Data Studio Calculated Fields in the comment section below! We would love to hear your thoughts.

No-Code Data Pipeline for Google Data Studio

Try for free