Table of Contents
Google Data Studio calculated fields and functions are the core of any data visualization platform such as Data Studio. They allow you to analyze your data and add new metrics to your reports. Calculated fields and functions can help you transform the dimensions and metrics that are coming from your existing data sources.
If you are new to Google Data Studio, you might need some assistance in learning to use the calculated fields and functions. We have created a detailed article to help you get started. You will learn what the calculated fields are and how you can use them to add new dimensions to your reports.
What are the Google Data Studio Calculated Fields?
According to Google, a calculated field is “a formula that can perform some action on one or more than one field in your data source”
In easy terms, Google Data Studio calculated fields and functions enable you to perform arithmetic or statistical operations on data to add new metrics or dimensions.
Google Data Studio calculated fields can perform the following actions on data:
- Arithmetic or mathematic
- Manipulation of text and other information
- Add logical comparisons to evaluate data
Through Google Data Studio calculated fields, you can perform an in-depth analysis of data gathered from various sources.
What are the Google Data Studio Functions?
Google Data Studio Functions allow easy manipulation of data. They are more complex than simple arithmetic operations. Data Studio calculated functions include aggregation functions, manipulation of geographical information and date, and transformation of text data. Google Data Studio provides over 50 functions that can be used on the given data.
How to Create and Edit Calculated Fields in Google Data Studio?
Data Studio calculated fields can be quite useful for in-depth analysis of your data. You can use these fields to create new arithmetic and logical functions.
Google Data Studio calculated fields can be used in any report that uses at least one data source. Once you have connected your data source with Data Studio, you can use the calculated fields to add different formulas.
Create a Google Data Studio Calculated Field Example
You can add different arithmetic functions to your data. For instance, if you want to find the unique keywords that are ranked on your search engine results, you can use the following formula:
Here are the steps you should follow to do it:
Step 1) Create a new field
Step 2) Type your formula for the new field
Make sure that the formula checker will be green at the bottom, meaning that the syntax in your formula is correct (otherwise you cannot create the new field).
Once you create the field, the output “Unique Keywords” should be displayed as a separate row in your data source.
Step 3) Apply the type of the calculated field
The data type is also important in this case. For instance, if you want to use arithmetic operations such as SUM, COUNT, etc, you can add them only to numeric data and they produce numeric output. On the other hand, string functions such as CONCAT should be added as text.
Edit Calculated Fields in Google Data Studio
In order to edit an existing calculated field in Google Data Studio, you should open your data source and then click on the grey area “fx” as shown below.
This will allow you to edit the formula for your calculation but there is also much more you can do with the calculated fields. This is something that you can learn with practice, and it also requires a basic understanding of regular expressions and some basic computer programming. However, if you are a beginner, you can use our ready-to-use templates that come with calculated fields to make things easier for you.
Data Studio Functions to Use for Your Reports
Let’s take a look at some of the functions you can use in your reports;
- Conditional Case: It is used to add a branching logic to your data. You can apply several conditions that can let you sort data. For example, the CASE WHEN function can let you add a condition with two possible outputs, and only when this condition is applied, the data after THEN would be displayed as output. Otherwise, it would add the data after ELSE.
Data Studio Case When Example -> CASE WHEN (REGEXP_MATCH(Query,Brand Terms)) THEN “Brand Keywords” ELSE “Generic Keywords” END
- Conditional If: Similar to the above mentioned function, If function also adds a branching logic to your data. For example, if the bounce rate is above 0.90, the result would be Pass, otherwise it would be Fail.
Data Studio Conditional If Example-> IF(Bounce Rate > 0.90, Pass, Fail)
- Aggregation: These arithmetic functions are used for numeric data, to add, subtract or count the values. For instance, you can use COUNT_DISTINCT to find the number of unique values of a specific dimension.
Data Studio Aggregation Example-> COUNT_DISTINCT(Query)
- Date: Data Studio calculated fields also allow you to manipulate the dates collected from data sources. For example, you can try the DATE_DIFF function to find the difference between starting date and ending date.
Data Studio Date Example-> DATE_DIFF(End Date, Start Date)
- Text: You can use the text data types in calculated fields as well. There are several functions available to manipulate textual data. For example, with CONCAT you can join multiple strings together.
Data Studio Text Example-> CONCAT(Age,” – “,Gender)
If all this stuff sounds too complicated for you, you can try one of our templates, that can help you get the hang of using the calculated fields that are essential for your reports. Data Bloo is the #1 contributor of Data Studio Gallery as the majority of our ready-to-use templates have been featured by Google. You can select the template that fits your requirements and improve your decision-making about your business.
Top 5 Google Data Studio Formulas for Calculated Fields
Let’s take a look at some of the best Data Studio calculated fields that you can use in your reports, based on your business.
1. Content Grouping (for Content Marketing)
If you are trying to analyze website traffic data, you can use custom page grouping to add similar pages under one group and analyze their traffic as a single dimension in your report. For example, you can add all the blog pages like a Blog group using CASE WHEN.
CASE WHEN (Page=”/”) THEN “Home” WHEN (REGEXP_MATCH(Page, ‘.about-us.’)) THEN “About us” WHEN (REGEXP_MATCH(Page, ‘.blog.’)) THEN “Blog Pages” WHEN (REGEXP_MATCH(Page, ‘.product.’)) THEN “Product Pages” WHEN (Page=”/contact”) THEN “Contact Us” ELSE “Other” END
This is calculated field is featured in our Content Performance Template.
2. Paid & Owned Channel Grouping (for Performance Marketing)
To evaluate marketing performance, you can use the custom grouping to identify the performance of paid and organic channels using the CASE WHEN calculated field in Data Studio.
CASE WHEN REGEXP_MATCH(Default Channel Grouping, ‘(.Paid.|.Aggregator.|.Paid Social.|.Paid Search.|.Display.|.Affiliate.)’) THEN ‘Paid Channels’ ELSE ‘Owned Channels’ END
This is calculated field is featured in our Ecommerce Master Template.
3. Checkout Funnel (for Ecommerce)
For e-commerce sites, it is essential to check customer behavior and the stage at which they drop off. That is where the checkout funnel comes in. You can identify how many customers actually purchase the items added to the cart using the CASE WHEN calculated field.
Case WHEN Event name=”view_item” THEN “Product View” WHEN Event name=”add_to_cart” THEN “Add To Cart” WHEN Event name=”begin_checkout” THEN “Checkout” WHEN Event name=”purchase” THEN “Transaction” END
This is calculated field is featured in our Google Analytics 4 Template.
4. Branded & Generic Organic Keywords (for SEO)
To identify the performance of your branded keywords, you can use the Data Studio calculated fields to determine how your users find you on search engines.
CASE WHEN (REGEXP_MATCH(Query,'(.*brand term 1.*|.*brand term 2.*)’) THEN “Brand Keywords” ELSE “Generic Keywords” END
This is calculated field is featured in our Keyword Analysis Template.
5. Keyword Ranking Position (For SEO)
You can use the Data Studio calculated fields, particularly CASE WHEN to find out which of the keywords are ranked in the first top 3 positions on search engines. This can allow you to analyze your keyword’s ranking distribution.
CASE WHEN Average Position < 4 THEN “Top 3” WHEN Average Position < 11 THEN “Top 10” WHEN Average Position < 21 THEN “Top 20” ELSE “Rest” END
There is so much more you can do with the calculated fields. Try our ready-to-use templates to get started with the calculated fields.