How to create calculated fields in Google Data Studio

Today I am going to speak about calculated fields. If you have worked with Google Data Studio, you may have faced the need to create additional fields. 

There are two ways how we can do it. It may be at the data source or at the chart level.

If you are more comfortable with video format, I have recorded a tutorial for you:

Now we are going to take a look at creating a calculated field at the chart level.

Calculated fields on the chart level

There is a table with 3 columns – Page title, Total users and New users. We want to see the difference between total users and new users, in other words, returned users.To do it, you click on the chart – Add metric – click to Create field. Let’s name the field Return users and we can create a custom formula: Total Users – New users

After applying changes, we can notice that there is a new column in our table. That is the difference between total users and new users. 

How can we do it in another way?

Calculated fields on the report level

We can go to Resources – Manage Added Data Sources and click Edit, or you can click the pencil = Edit data source. Here we see all the fields this data source has. We have an “Add a field” button. We can click it and see a field with settings. Let’s rename the field and call it “Return users – report level” in order to differentiate it. Here we just insert the same formula: 

Total Users – New users

Saving changes. Now, when we’ve created a new field at the data source level, you can add “Return users – report level” as a metric, default group. The result is the same, numbers are the same. 

What is the difference? 

Case 1

It can be applicable in cases when you need to create a lot of elements with the same metric. 

Let me explain everything by example. I need to create a chart. It already contains the information that we have previously had, except return users. As the matter of fact, we can not add the metric that was created on chart level. Among the possible metrics we can find only “Return users – report level”. That is what I meant. It can really save your time, if you create the metrics at the report level from the very start. You will just need to find your metrics in the list instead of creating a new one. 

Case 2 

We need to copy the report or we need to connect it to another data source or connect to another Google Analytics account. Let’s see how it is going to work. 

After making a fresh copy, we will need to set up report level metrics again because it won’t appear automatically. If we connect another Google Analytics account, we will have to recreate all the calculated metrics from scratch. However, chart level metrics remain the same, they are working. 

Summing up

Resuming everything said before, when you have a report with many elements with the same metric, you’d better use report level metrics. If you know that you will copy the report and attach new different accounts, you’d rather leave the chart level metrics. 

Calculated fields in Dimensions

We still have the same table. 

Let’s imagine we need to find some categories from Page titles. We can click Add dimensions – Create a field – naming is Category. The formula in our case will be:

CASE WHEN CONTAINS_TEXT (Page title, “Sale”) then “Sale” 

WHEN CONTAINS_TEXT (Page title, “Lifestyle”) then “Lifestyle”

WHEN CONTAINS_TEXT (Page title, “Apparel”) then “Apparel”

ELSE NULL
END

We have applied the formula and see how it has influenced our table. We have a new column called Category, full with data. 

***

I wanted to show you that calculated fields are not only applicable with metrics, but with dimensions as well. We have written some formulas that can find matches in a table and use data from there.

I hope you liked the article! Share your cases of using calculated fields in the comments section!