×

5 Looker Studio Functions for Calculated Fields (Data Studio)

Julian Juenemann

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:

Looker Studio For Beginners

Subscribe & Master the Basics with our FREE Looker Studio Course

  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.

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-looker-studio

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

lower-function-looker-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 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.

concat-function-looker-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 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=

regex-extract-function-looker-studio

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.

regex-match-function-looker-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 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.

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

case-function-looker-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 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.

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

FAQ

How can I create Calculated Fields using Looker Studio Functions?

There are two ways to create Calculated Fields in Looker Studio:
1. Chart Level Calculated Fields: You can add Functions directly to a specific chart by creating a field within the chart and entering the desired Function.
2. Data-Source Level Calculated Fields: To make the Calculated Field available throughout the data source, you need to create a new field within the data source and enter the Function there.

What is the purpose of the REGEX_EXTRACT Function in Looker Studio?

The REGEX_EXTRACT Function is used to extract specific patterns or values from a text dimension using regular expressions. For example, you can use this function to pull the search query from a page path or URL. By creating a Calculated Field with the REGEX_EXTRACT Function, you can extract the desired information and present it in a separate field.

How can I use the REGEX_MATCH Function in Looker Studio?

The REGEX_MATCH Function helps identify whether a text dimension contains certain keywords or patterns using regular expressions. It returns a Boolean value (true/false) indicating the presence of the specified pattern. You can create a Calculated Field with the REGEX_MATCH Function to classify and filter data based on specific keywords or patterns.

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.

Sign up to the FREE GTM for Beginners Course...

[wpforms id="9076262" title="false" description="false"]

Julian Juenemann

Julian is the web analytics instructor and the owner of MeasureSchool. He has 10+ years of experience in teaching analytics tools such as Google Analytics, Google Tag Manager and Looker Studio. He founded MeasureSchool to help marketers with the data-driven way of digital marketing.

Related Posts

The Best Google Tag Manager Extensions Tools

Top 11 Google Tag Manager Extensions

Usman Qureshi

First-Party Mode for Hosting Tags on Your Domain blog featured image

Google Tag Manager First-Party Mode Guide

Eric Huguenin

Google Analytics 4 Email Tracking blog featured image

Google Analytics 4 Email Tracking [Ultimate Guide]

Eric Huguenin

Subscribe
Notify of
guest
6 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jérôme Lacroix
Jérôme Lacroix
5 years ago

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

andre
andre
4 years ago

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

Bashar Ayyash
Bashar Ayyash
4 years ago

Hi Julian,

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

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,

Last edited 2 years ago by Aleksa Filipovic
Anais
Anais
3 years 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!

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.

Ready to take your digital marketing to the next level?

Subscribe to our newsletter and stay ahead with our latest tips and strategies.