5 Functions for Calculated Fields in Data Studio (2020 Updated)

I’m going to show you my top five favorite Functions for Calculated Fields in Data Studio to customize your data in Google Data Studio.

In this article, we want to talk about Data Studio again. Data Studio is really interesting because it’s always evolving, it’s a pretty new tool. There are always new features. And recently, they’ve added Functions to the mix. 

With Functions, you can actually upgrade your game when you build a Calculated Field in Data Studio. This is something that we all know from Google Sheets. We probably use the SUM Function or the AVERAGE Function often to add up fields or a row of data. 

In Data Studio, you now do so much now with calculations in Calculated Fields and utilize Functions. And today, I want to show you five of these Functions that you should utilize in your Calculated Fields.

Before we get started if you’re looking for a more comprehensive step-by-step course on Data Studio, then check out our Data Studio Essentials Training.

Where to Create Calculated Fields in Data Studio

All right, let’s start out in Google Data Studio, looking at what Functions actually are. So if you have a chart, you can use Functions and input Functions in two ways. 

1. Chart Level Calculated Fields

One is on a chart basis itself by adding a metric or dimension right here, then creating a field. Here you can see you can enter Functions to calculate that particular field. But this is then only available within this chart. 

chart-level-calculated-fields

2. Data-Source Level Calculated Fields

If you want to make it available within the data source, you need to create a new field down here. It will open up the Create new field editor where we can also enter our Function. 

calculated-field-data-studio

#1 LOWER/UPPER Function

Change Text Dimensions to Lowercase or Uppercase

So let’s get started with our first Function. If you have an example just like this one where you have sources that are kind of the same, but as you can see, we have different uses of Facebook and Google here with capitalization, without capitalization, and so on. These really screw up our numbers, as we know that all of these should actually be Facebook.com.

lower-function-data-studio

We can utilize a Function in a Calculated Field to calculate a new field that simply takes this dimension and makes a new one with all lowercase sources. So let’s create this. We go to create a new field, and this will all be our lowercase source. As a Function, we’ll use our LOWER Function to simply use our source field as an input field. Once we get a green checkmark down here, everything should be good to go. Save this and edit to our chart right here. You see the input was taken from the source field, and then rewritten in lowercase. This is the case for every variation.

If you get rid of the source field have a much smaller, much more readable data set that we can now visualize appropriately. The LOWER or even the UPPER Function can be used to lowercase or capitalized strings text and rewrite them in a new dimension. 

#2 CONCAT Function

Let’s move on to the second Function, the CONCAT Function.

Use Case: Combine Host Name & Page Path to Create a FUll URL Dimension

For example, you might be familiar with a case like this, where you have your page path, especially when you have multiple domains attached to your Google Analytics account, it might be a bit deceiving. So, let’s add here, our hostname to this dimension. As we see it actually splits it up and shows us that, for example, the homepage belongs to different subdomains right here. 

What if we wanted to actually combine both of these so we have a more understandable data set. Or we can do this with text as well, by creating a new field. Using the Function of concatenate a call this full URL and will simply use the concatenate Function which has CONCAT as an input field, it takes the hostname and the page path. 

Here we go. Let’s save this and look at the result. We have our full URL here, pull that in. 

We can see what’s in between right now let’s put this down here. This plus this equals this new full-page path.

concat-function-data-studio

And again, we can get rid of our other dimensions and just have the full path for our understandable data. So concatenate is another Function you can use in your Calculated Fields in Data Studio to manipulate text and string together different text dimension values.

#3 REGEX_EXTRACT Function 

Use Case: Pull Search Query From Page Path

Let’s move on to the next example, which is the REGEX_EXTRACT function. An example here would be that you have a page path, which is clearly the search of your website. At the end of the URL, you have this q= parameter and afterward, the search string that was used on your website. 

This clearly shows the search. But to make it all more readable, why not pull out and extract the search query and put into a separate report. Again, here, we can use a Function. 

Let’s go ahead and create a new field, which is our search term. Here we’re going to use the REGEX_EXTRACT Function, which takes a Field as an input, which is our page path. Then our regular expression, you would need to know a bit of Regex in order to be able to use this effectively. But it’s well worth learning. Check out our tutorial on how to use Regex with our video right here.

The Regex that we will use here is simply looking for the query parameter, q=

regex-extract-function-data-studio

Using a capture group to pull out anything that is in the back of this capture group that should do it.

Let’s save this and have a look at how this works.

It has pulled out these search terms from our page path. We can get rid of our page path variable to have a nice search term report.

#4 REGEX_MATCH Function

Identify which values contain certain keywords

Next up is the REGEX_MATCH Function.

In our example here, we have a search query report for a language learning website. As you might expect, users are often searching for the target language that they want to learn. But there are some more hidden key phrases that reveal that more about what the users up to. 

If you go here into the view, and look down here, maybe you can find one of those How are you was actually something you need to really dig through.

But here, for example, why French is… Why French is not as hard as you think, interesting. These search terms that start with why, what, when, where are oftentimes more telling about what the user actually wants, and might give us some ideas on future content generation, or keywords that you might want to book in Google Ads.

Let’s try to pull them out and actually filter them out. There are different methods of doing so. But a Function that you can use here is the REGEX_MATCH option.

regex-match-function-data-studio

Let’s try it out in a Calculated Field. We go over to create a new field. We’ll try to pull out our what, when, where and so on questions. We’ll utilize the REGEX_MATCH Function.

As an input, we take our query string and type in the regular expression to detect whether there is what when aware inside of the text.

You do this by separating it with the all functionality, which is a | (pipe) symbol so why, how, when, where, who or which. Now, since these words can appear in any order at the beginning or the end or in the middle, I’m gonna also prefix them with a .*

This means that as long as they couldn’t, in the sentence, we should be able to pick this up.

Let’s save this and add this to our sheet. What this REGEX_MATCH option does it spits out a false or true. In this case, all of these are false. But we should be able to find one or two of these trues. Let’s see here we have a true for how to learn French. This field can actually be used for a filter. 

Use Case: Create a Filter to Include or Exclude Search Terms Containing these Keywords

There are a lot of different ways of filtering data. But now that we have that new column, let’s use it for filter, simply filter out our w questions. We only want to include when, what, where equals true, let’s save this.

And voila, we get our list filter down to these terms like this a bit larger. So we’ll be able to see this. But we don’t actually need to have this column right here, we could also take it out and only look at this data and call this the one where report. The REGEX_MATCH option can be used to classify and search through strings of data. Then it could be used for example to create a filter. Or, as we can see in the next example, for bit more sophisticated filtering, which brings us to our last Function, which is probably the most used one is the case Function.

calculated-field-as-filter

#5 CASE Function – An If This, Then That Type Function

This is a very popular and also very powerful Formula. Here we have the same report as before. But this time, we’ll use the case Formula to build a more dynamic filter that we can utilize with a dynamic chart, let me show you what I mean.

The case Formula is really an if this, then that Formula, we will call this w class. And our case Formula always starts with a very succinct syntax. It starts with a case keyword and follows with when clauses. So when x happens, then do y. You can have multiple of these conditions in here. But at the end, you might want to have an else clause, which would then say, if none of the above are true, then just put this one in place. And all everything is ended with the end clause. This is the syntax of a case Formula. Let’s fill this out. 

What do we want to happen? 

Well, on the first classification, we would like to use our REGEX_MATCH option again, to filter by our query. And this time, we only put in as a Regex our who question for example, and when the who is found in the string, then I would like to classify it as who question mark. We can do this with all the different w questions.

case-function-data-studio

So we would have why, and so on. In the end, if none of them are true, we just put this as others. Let me fill this out really quickly here. Here we have all our questions in here now, why, how, when, what, where, and it shows green.

Let’s update this. 

Put our new dimension in the report. We should see if we put this in the right order. We now have the how questions, the why questions, all classified in different categories. It’s kind of the same as we have a true or false but it’s more categories. 

Use Case: Create a Filter to Show Only Query Terms

How can this be useful? Well, if we duplicate this, and get rid of our query right here, we are only left with the query type terms that we imported into our Custom Dimension. 

There is a feature within Data Studio that actually lets us use this table as a filter.

How can you classify this as a filter?

Well, there’s an option down here on the interactions apply filter.

case-function-calculated-field-filter

If you have this in your report, you can click on any of these. Then it will filter the whole report page based on this data point if it’s within the same data set. 

Our report on the left side is filtered based on what we have clicked on. So if you go to why, which is great for the exploration of these when, why, how and so on questions that oftentimes appear in search things. 

There are many other use cases for the CASE Function. But in general, if you want to classify something and aggregate it together, you might want to use the CASE Function for this purpose.

Use Cases might include general grouping or content grouping. Importantly, you can’t do this retroactively. Even if your data set doesn’t provide the channel grouping, by default, you can go in and edit it later. The CASE Function is really a powerful tool within Data Studio to have available for your data visualization.

Summary

All right, so there you have it, my favorite Functions to use for Calculated Fields in Data Studio. Did I forget any that you often use in your Dashboards? If so, then please let me know in the comments down below. 

Don’t forget to check out our complete Data Studio Essentials Training to learn more about how to present and visualize your data.

And if you have any more questions, I’d love to hear from you in the comments below.

JOIN US!

Master Data & Analytics with Measuremasters

Exclusive Courses & Workshops | Ongoing Troubleshooting | Support Resources, Tools & much more
guest
6 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jérôme Lacroix
Jérôme Lacroix
2 years ago

Thanks Julian, I learned a few valuable tricks here! I will apply them right after lunch 🙂

andre
andre
1 year ago

Thank you, i didnt know how to use calculated fields, now im willing to try

Bashar Ayyash
Bashar Ayyash
1 year ago

Hi Julian,

I connected to google sheet which will update daily, each day will append the date with integer values as attached:

https://drive.google.com/file/d/1BBYMgTR1jXkdAEhTz6zV3P1YDRLWge4n/view?usp=sharing

how can I make a calucluated field which will the total value of each row, and the total value of each column as the date columns will be dynamic?

Please advice,

Anais
Anais
5 months ago

Thank Julian, this was very helpful! I do have a question. I’ve extracted the name of users for a report, and I’d like to split name and surname by adding a formula where you have a space in front of an uppercase.
It shows like this on the report JulianJuenemann
in the formula, I’d like to manage to have it look like this Julian Juenemann

Is it possible? Could you help?

Thanks!

Blog Categories

Join 30,000+ marketers getting exclusive resources and our latest content!

now it's time to

Start measuring like a master

Itching to jump into the world of MeasureMasters? This is what you have to look forward to.