How To Create Calculated Fields In Data Studio

Are you in the middle of creating calculated fields in Google Data Studio but don’t know how?

Are you asked to calculate different metrics of your social media accounts, including engagement rates, CTRs, and conversion rates? Then we have got you covered because there is nothing to worry about. 

Or maybe you want to improve your ROAS? We are constantly generating high-quality, step-by-step guides for you, and you can check out our article on how to calculate and improve your Return on Ad Spend.

Calculated fields in Google Data Studio are a handy tool that you should use for whatever you do on the platform.

You will need calculated fields at almost every step of the way, so if you want to learn how to complete this critical process, sit closer. 

In this article, we will break down the process of creating calculated fields in Google Data Studio.

When you reach the end of the article, you will have a comprehensive view of the overall Google Data Studio and how you can work with it. 

Porter Metrics allows you to create your Google Data Studio report in less than a minute.

What Are Calculated Fields?

Calculated fields are “a formula that performs some action on one or more other fields in your data source,” according to Google’s definition.

This indicates that you can apply calculations or functions to the data to generate new metrics and dimensions thanks to the capabilities of the data studio.

You should know that you can create calculated fields on two platforms, as highlighted in the image below.

In the first instance, the calculated field will only apply to the chart, which means if and when you delete the chart, you will not be able to access the fields you just created.

In the second instance, you’ll be able to create fields directly that you can access as part of the other fields linked to the data source.

It’s almost always permanent when you use the second add field link to create calculated fields.

Let’s now focus on the second instance. Once you click on Add a field, it brings you directly to the interface displayed below.

On this interface, you will be able to create any form of calculation as well as REGEX. You’d love to get your report in shape and your desired results on your dashboard.

On this interface, you can add arithmetic and mathematical formulas to divide, multiply, add or subtract two or more fields, manipulate text, date, and geographic information, and use branching logic to evaluate your data and deliver a variety of results.

Calculated metrics are quantitative values ​​based on Google Analytics data using user-defined formulas.

Thanks to this function, the benefit and convenience are that you can quickly bring the metrics into a form convenient for analysis. 

For example, one of the tasks often encountered in the work of an Internet marketer is to find the sum of the achievements of several necessary goals, calculate the cost of achieving this overall goal, and the conversion rate for this purpose.

This can be done using a simple formula in a calculated measure. You can check Porter’s Google Data Studio Templates to see the metrics mentioned.

Why Should We Use Calculated Fields in Data Studio?

Calculated fields are your best friend whenever you wish to show data not currently available in your source data. They permit you to format your data:

• Better looking (i.e., by cleaning up your URL report)
• More informative (i.e., by concatenating the hostname and landing page path).
• More practical (i.e., by relating results to set targets).

If you want your stakeholders to accept your suggestions and take the required action, you must provide your data in the most user-friendly and straightforward way possible.

There are two types of calculated fields, depending on where they are created:

1. Specific report charts. 
2. Data source 

Each type of computed field has distinct benefits over the others.

Data Source Schema-Specific Calculated Fields

Data Source Schema-Specific Calculated Fields are computed fields produced by Google Studio and stored in a Data Source schema. 

To access the data source schema-specific calculated fields tab, locate the “add field” button on the right side of the Google Data Studio Screen under the Data pane.

Once you find it, click the “Add a field” button, and the image below will appear.

You can name your field in this window and add your formulas and several combinations.

When you add calculated fields to a Data Source schema, those fields are made available in every report that employs that particular Data Source Model.

This occurs whenever calculated fields are added to a Data Source schema.

Specific to the Data Source Schema Calculated Fields in Google Data Studio can be further subdivided into the following categories:

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

Limitations of Data Source Calculated Fields

The following restrictions apply to calculated fields contained within data sources:

• When working with blended data, you cannot use a calculated field associated with a data source.
• If you want to be able to create or update computed fields at the data source, you need to have editing access.

Chart Level Calculated Fields

These Google Data Studio Calculated Fields are generated within a particular chart within a report.

When a calculated field of any kind is added to a chart, that field will only be accessible in that particular chart after it has been added.

On the other hand, a Chart Specific Calculated Field that utilizes blended data is an option.

Particular to, the Chart Calculated Fields in Google Data Studio could be further subdivided into the following categories:

• Chart Level Calculated Metrics
• Chart Level Calculated Dimensions

Chart Level Calculated Metrics

To access these chart-level calculated metrics, you’ll need to highlight your chart, go to metrics under the setup pane and click on add metric.

Once you click on add metric, a drop-down menu appears. At the bottom of the drop-down menu, you’ll see “Create Fields”.

Once you click “Create Field,” “an interface will appear and should look like this.

Chart Level Calculated Dimensions

To access this chart-level calculated dimensions, you’ll need to highlight your chart, go under dimensions under the setup pane and click on add dimension.

Once you click on add dimension, a drop-down menu appears. At the bottom of the drop-down menu, you’ll see “Create Fields”.

Once you click on “Create Field,” “an interface will appear and should look like this

If you want to create a formula to calculate the effectiveness of your conversions in comparison to the users in Google Analytics, there are a few steps you should complete: 

1. Sign in to Google Data Studio
2. Click “Add a page.”
3. Click Insert, and select a “Scorecard.”
4. Place your Metric on the blank space
5. From the Metrics on the right side, choose “Users.”
6. Choose the second Metric as “Goal Completions.”
7. To calculate your conversions, choose a “New Field” from Metrics
8. Name this new field
9. In the “Formula” window, insert your formula, which will be Goal Completions / Users in this case.
10. Keep it as a number or preferably as a percentage as you wish

The final screen should be looking like this: 

If you need a step-by-step video guide about calculating this field, you can watch our Youtube video about creating calculated fields in Data Studio.

Calculated fields that are chart-specific offer several benefits over data source calculated fields, including the following:

• You won’t even need access to the data source to be able to add fields rapidly and without difficulty.
• You can generate calculated fields that are unique to charts based on blended data.
• Calculated fields derived from the data source can be incorporated into chart-specific calculated fields.

Limitations of Chart-Level Calculated Fields

These are the main limitations of Chart Level Calculated Fields:

• Calculated chart-specific fields can only be found in the chart in which they were initially created. If you create a field in the chart, it will not automatically be created in the data source for the chart.
• In your formula, you cannot refer to any other chart-specific computed fields, even if those fields are defined in the same chart as your formula. (You should make use of a data source calculated field if you want to be able to reference additional calculated fields.)
• You have to be an editor of the report to have the ability to generate calculated fields that are unique to charts.
• The data source must have Field Editing enabled before it can be used in Reports.

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

There are several steps to add Data Source Calculated Fields:

To construct a calculated field:

1. Modify the source of the data.

2. To add a new field, on the downright side of your Google Data Studio screen, click the “Add a Field” button.

3. You should provide a Name in this space and enter a formula:

This will be the name appearing in all your reports by default. Editing the calculated field name will allow you to make this modification for the field you want to add to your report.

It is required that the field name be distinct. Also, you won’t be able to re-use the same calculated field name after you must have added it at first.

Now!

Enter a Formula:

Start typing the name of the dimension, measure, or function you want to use.

If you have numerous fields with names that are similar to one another or fields that duplicate part of the name of a function, such as Date and Date2, for example, type part of the name, then browse through the list until you find the field you want to use and pick it.

In this example, we want the event name column displayed in the Upper case. 

You can add a field to the formula editor by clicking a field in the Available Fields list shown on the left.

• Click the SAVE button in the bottom right corner (or UPDATE if you are editing an existing field).
• Adjust the Aggregation, Type, and Show As settings to correspond with your needs.

You’ll be able to find the new field you just created by searching for the field name – Uppercase Event Name under the data source search field on the righthand corner of the GDS screen.

To add the new calculated field to your chart, under dimension, click on add dimension.

Now search for the new calculated field you just created using the field name. In this case, we used – Uppercase Event Name.

Click on the new calculated field you created once you find it and see your results on your chart.

In this case, I wanted my event name column to be displayed in capital letters.

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

Calculated chart-specific fields can only be found in the chart in which they were initially created.

To generate a calculated field that is unique to a chart:

1. Modify the report as needed.
2. Choose one of the charts.

3. Click the +Add dimension or +Add metric button in the properties panel located on the right of the screen, depending on the type of field you wish to create.

4. Click the CREATE FIELD button that is located at the very bottom of the field list.

5. Enter a name for the field and your formula

You are free to use the same operators and functions with data source calculated fields. You are not permitted to make references to any additional computed fields that are chart-specific, though.

When working with blended data, you can employ chart-specific calculated fields.

1. Adjust the Aggregation, Type, and Show As settings to correspond with your needs.
2. Hit the APPLY button.

How To Identify Calculated Fields In Google Data Studio?

A formula that does something to one or more fields in your data source might be a calculated field in this context.

You can identify calculated fields by their ability to conduct arithmetic and math operations and change text, date, and geographic information. 

Additionally, calculated fields can use branching logic to evaluate your data and offer a variety of responses.

The results of a computed field can therefore be presented in charts alongside each row of data when that field is included. How this new data is shown will be determined by its use.

How To Create Calculated Fields In Google Data Studio?

You have the option of appending a variety of arithmetic functions to your data.

For instance, if you want to identify the unique terms that are ranked on the results page of your search engine, you can use the formula that is provided here:

COUNT DISTINCT(Query)

To accomplish this, you need to complete these steps in order:

1. Begin by making a brand new field
2. Enter your equation into the newly created field.

Make sure that the checker at the bottom of the formula will show a green checkmark, indicating that your formula’s syntax is valid (otherwise, you cannot create the new field).

After you have finished creating the field, the “Unique Keywords” output should be presented in your data source as a new row in its own right.

3. Select the appropriate formula for the calculated field.

In this particular scenario, the data type is also quite essential.

For example, if you want to utilize mathematical operations such as SUM, COUNT, etc., you can only add them to numeric data, and they will create numeric output.

This is because those operations produce numeric output. On the other side, string methods like CONCAT should be included in the text.

How To Edit Calculated Fields In Google Data Studio

In Google Data Studio, you must first access your data source and then click on the gray area labeled “fx” to change an existing calculated field. An example of how to do this is shown below.

This will allow you to alter the formula used for your calculation, but the computed fields also provide you with many other options to play with.

This can be learned with experience and takes a fundamental comprehension of regular expressions in addition to some fundamental computer programming knowledge.

On the other hand, if you are starting, you can use our pre-made templates that already come with calculated fields to help simplify the process for you.

Calculated Fields Data Types

The types of data that can be stored in a computed field are determined by the types of functions that are used in the formula:

• Numbers are generated using formulas that use arithmetic or aggregation functions, such as SUM, COUNT, or MAX.
• Text is generated via formulas such as CONCAT, SUBSTR, and LOWER, which are text-related function names.
• Formulas that employ date and time functions can create types of the form Number, Date, or Date & Time, depending on the date and time function used.

Utilizing the Type drop-down option found under the data source editor will allow you to modify the data type of any calculated fields you have.

How To Use Calculated Fields In Data Studio?

You can get answers to queries that couldn’t be addressed with the data in its current state by using calculated fields to answer those questions.

This encompasses anything from developing new custom metrics to changing several dimensions to doing various types of analyses on the data.

Making New Metrics

To make new metrics in Data Studio, you must add the Metric formula to the calculated field. 

There are two ways to do that: 

• You can either combine your goals in a way like a Goal 1 + Goal 2
• Or you can find the conversion rates stage by stage by finding the difference between them. All you have to do is divide the stage numbers like Stage 2 / Stage 1 

Tracking your progress

You can keep track of your progress toward goals with a scorecard by figuring out:

 Goal Metric / Goal Value

Combining your dimensions

CONCAT function will help you combine more than one dimension. For example, you could combine Hostname and Landing Page Path to make your URLs clickable.

Erasing Trailing Slashers

This tip is only a short-term fix if your site’s URLs have a mix of trailing and non-trailing slashes, which causes your data to be split when you report on it.

You should first fix the problem by requiring URLs to have either a trailing or non-trailing slash to stop duplicate content. If you don’t, you’re just covering up the problem.

You can use REGEX REPLACE to eliminate the last slash from pages that end with one.

Keeping Consistent Case

The best way to fix this is to keep the case of your URLs the same, which is similar to the way to fix the problem of trailing slashes in URL strings.

But if you want to combine dimensions by making them all lowercase, there is an easy formula:

LOWER (Landing Page)

Top Google Data Studio Formulas for Calculated Fields

Let’s examine the best Data Studio calculated fields for use in your reports, based on the nature of your company:

1. Content Grouping 

If you are analyzing website traffic data, you can add similar pages into one group and examine their traffic as a single dimension in your report using custom page grouping.

Using CASE WHEN, you can include all blog pages as a Blog group, for instance.

2. Paid Channel Grouping

To evaluate marketing effectiveness, you can use the custom grouping and the CASE WHEN computed field in Data Studio to determine the performance of paid and organic channels.

3. Checkout Funnel 

It is crucial for e-commerce websites to monitor client behavior and the point at which they abandon a purchase. Thus, the checkout funnel comes into play.

Using the CASE WHEN computed field, you may determine how many consumers purchase the products added to the shopping cart.

4. Keyword Ranking 

To measure the performance of your branded keywords, you may use the calculated fields in Data Studio to determine how your users discover you via search engines.

Use Case for Google Data Studio Calculated Fields

Comparing Mobile Data Traffic Share in Various Countries

Do you wish to demonstrate how the mobile traffic share fluctuates over time and how it varies throughout your target markets?

A combination of computed fields and blended data enables the comparison of segments within a single Data Studio chart.

The Solution:

Create segmentation in Google Analytics (GA). In Data Studio, combine these GA segments into a single data source.

Apply your segments to charts displaying the required Metric (s) for your calculated field that are otherwise identical.

• Rename your metrics to reflect the segment being measured (i.e., Sessions AU, Mobile Sessions AU)
• Click Blend data.
• Since blended data does not support data source-level calculated fields, create chart-level calculated fields.
• Change from Number to Percent type.
• Apply these calculated fields to your chart as metrics.

Conclusion

In this article, you have learned the purpose of calculating fields in Google Data Studio. You also had a chance to learn how to create a data source, and Google Data Studio Calculated fields at the chart level. 

Porter Metrics, a No-code Data Pipeline, provides a consistent and dependable way to handle data transfer between various sources and many Desired Destinations with only a few clicks.

FAQ

How do I add a calculated field in blended data Google Data Studio?

There are a few steps to complete the process: 

1. You should edit your data source
2. Next, you will have to Add a Field
3. Include a name for the field
4. Include a formula

Is Google Data Studio better than Tableau for creating Calculated Fields?

As Google Data Studio is connected to the Google ecosystem, you will likely encounter fewer difficulties in creating calculated fields and editing them. However, Tableau is also an excellent tool for this.