5 Looker Studio Functions for Calculated Fields (Data Studio)

Last Modified on March 25, 2024

Looker Studio (Data Studio) has a lot of features and it keeps adding new ones into the mix.

I’m going to show you my top five favorite Looker Studio Functions for Calculated Fields to customize your data:

1. LOWER/UPPER Function
2. CONCAT Function
3. REGEX_EXTRACT Function
4. REGEX_MATCH Function
5. CASE Function

In this article, we want to talk about Looker Studio. Looker Studio is really interesting because it’s always evolving. With Looker Studio Functions, you can upgrade your game when you build a Calculated Field.

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 Looker Studio, you can now do so much with calculations in Calculated Fields and utilize Functions. 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 Looker Studio, then check out our Looker Studio Essentials Training inside of our MeasureMasters membership.

Where to Create Calculated Fields in Looker Studio

All right, let’s start in Looker Studio, looking at what Functions 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 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.

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.

🚨 Note: A great way to pull data from third-party data sources is Looker Studio connectors

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.

Tally screw up our numbers, as we know that all of these shall be on Facebook.com.

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 it 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 capitalize string text and rewrite them in a new dimension.

2. 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 splits it up and shows us that, for example, the homepage belongs to different subdomains right here.

What if we wanted to 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, 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.

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 Looker 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 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 it 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, for our regular expression, you would need to know a bit of Regex 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=

Using a capture group to pull out anything that is in the back of this capture group 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 some more hidden key phrases reveal more about what the users are up to.

If you go here into the view and look down here, maybe you can find one of those How are you was something you need to 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, and where are oftentimes more telling about what the user 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 filter them out. There are different methods of doing so. But a Function that you can use here is the REGEX_MATCH option.

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 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 is 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 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, and where equals true, let’s save this.

And voila, we get our list filtered down to these terms like this a bit larger. So we’ll be able to see this. But we don’t 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 a bit more sophisticated filtering, which brings us to our last Function, which is probably the most used one the case Function.

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 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 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.

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 the who question mark. We can do this with all the different w questions.

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 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, and 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 Looker Studio that 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.

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 a powerful tool within Looker Studio to have available for your data visualization.

FAQ

Summary

All right, so there you have it, my favorite Looker Studio Functions to use for Calculated Fields.

Also, a great way to add extra information to your GDS reports is to add tooltip annotations to your graphs, charts, or reports.

Did I forget any that you often use in your Dashboards? If so, then please let me know in the comments down below. And if you have any more questions, I’d love to hear from you in the comments below.