Calculated Fields in Google Data Studio let you create and add custom metrics or dimensions to your data set. You can utilize Formulas to calculate your fields just like in Google Sheets. Today I’m going to show you 5 Functions you should know.
All Functions: https://support.google.com/datastudio/table/6379764?hl=en
🎓 Learn more from Measureschool: https://measureschool.com/products
🚀Looking to kick-start your data journey? Hire us: https://measureschool.com/services/
📚 Recommended Measure Books: https://kit.com/Measureschool/recommended-measure-books
📷 Gear we used to produce this video: https://kit.com/Measureschool/measureschool-youtube-gear
In this video, I’m going to show you the five formulas you need to know in Data Studio to build your calculated fields. All and more coming up.
Hey, there measure geeks. Julian here back with another video teaching you the data-driven way of Digital Marketing. Today we want to talk about Data Studio again. And Data Studio is really interesting because it’s always evolving, it’s a pretty new tool. So there’s always new features. And recently, they’ve added formulas to the mix. Now with formulas, 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 sometimes to add up fields or a row of data. In Data Studio, you can actually add now to your data set with calculated fields and utilize formulas event. And today, I want to show you five of these formulas that you should utilize in your calculated fields. Now, we got lots to cover so let’s dive in.
All right, let’s start out in Google Data Studio, looking at what formulas actually are. So if you have a chart, you can use formulas and input formulas in two ways. One is on a chart basis itself by adding a metric or dimension right here, then creating a field. And here you can see you can enter a formula to calculate that particular field. But this is then only available within this chart. If you want to make it available within the data source, you need to create a new field down here. And it will open up the Create new field editor where we can also enter our formula. So let’s get started with our first formula. 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. So these really screw up our numbers, as we know that all of these should actually be Facebook.com.
Now, we can utilize a formula to calculate a new field that simply takes this dimension and makes a new one with all lower case sources. So let’s create this. We go to create new field, and this will all be our lower case source. And as a formula, we’ll use our lower formula to simply use our source field as an input field. And once we get a green checkmark down here, everything should be good to go. Save this and edit to our chart right here. And you see the input was taken from the source field, and then rewritten in lower case. And this is the case for every variation now here. So if you get rid of the source field have a much smaller, much more readable data set that we can now visualize appropriately. So the lower or even the upper formula can be used to lowercase or capitalized strings text and rewrite them in a new dimension. Let’s move on to the second formula. This is our concat formula.
You might be familiar with a case like this, where we have your page path, especially if you have multiple domains attached to your Google Analytics account, for example, it might be a bit deceiving. So let’s add here, our hostname to this dimension. And as we see it actually splits it up and shows us that, for example, the homepage belongs to different subdomains right here. So 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. And using the formula of concatenate a call this full URL and will simply use the concatenate formula 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 our result. We have our full URL here, pull that in. And now 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 formula you can use with text in order to string together different text dimension values into one. Let’s move on to the next example, which is regex extract. So an example here would be that you have a page path, which is clearly the search of your website. And in the back of the URL, you have this Q equals parameter, and afterward the search string that was used on your website. Now, 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 formula. Let’s go ahead and create a new field, which is our search term. And here we’re going to use the regex extract formula, which takes a field as an input, which is our page path. And then our regular expression, you would need to learn a little bit of regex in order to be able to use this correctly. But it’s well worth learning. I won’t get into it right now. But it has many use cases and data analysis. So definitely look into regex, we have a separate video on this as well on the channel, the regex that we will use here is simply looking for the Q equals.
And then 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. As we can see has now pulled out these search terms from our page path. And we can get rid of our page path variable to have a nice search term report. Moving on. Next up our regex match. And this is 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. So 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. So let’s try to pull them out and actually filter them out. There are different methods of doing so. But a formula that you can use here is the regex match option.
Let’s try it out. We go over to create a new field. And we’ll try to pull out our what, when, where and so on questions. We’ll utilize the regex match formula.
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.
And you do this by separating it with the all functionality, which is a pipe 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 dot star, which means if as long as they couldn’t, in the sentence, we should be able to pick this up.
There we go. Let’s save this and add this to our sheet. Now what this reg ex match option does it spits out a false or true. And 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. Now this field can actually be used for a filter. So 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. And 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. So the regex match option can be used to classify and search through strings of data. And then it could be used for example for filter. Or, as we can see in the next example, for bit more sophisticated filtering, which brings us to our last formula, which is probably the most used one is the case formula. 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.
Now the case formula is really a if this, then that formula, we will call this w class. And our case formula always starts with a very succinct syntax. And it starts with a case keyword and follows with when clauses. So when x happens, then do y. And 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. So this is the syntax of a case formula. And let’s fill this out right now. What do we want to happen? Well, on the first classification, we would like to use our reg ex match option again, to filter by our query. And this time, we only put in as a reg ex 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. Now, we can do this with all the different w questions.
So we would have why, and so on. And at the end, if none of them are true, we just put this as others. Now, let me fill this out really quickly here. So here we have all our questions in here now, why, how, when, what where, and it shows green. Let’s update this. And now put our new dimension in the report. And 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. So it’s kind of the same as we have a true or false but it’s more categories. Now, how can this be useful? Well, if we duplicate this, and now get rid of our query right here, we only left with the y which our categories that we imported in to our custom dimension. Now, 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.
And now if you have this in your report, you can click on any of these. And then it will filter the whole report page based on this data point if it’s within the same data set. So now our report on the left side is filtered based on what we have clicked on. So if you go to why, which great for exploration of these when, why, how and so on questions that oftentimes appear in search things. Now there are many other use cases for the case formula. But in general, if you want to classify something and aggregated together, you might want to use the case formula for this purpose. Use Cases might be general grouping or content grouping. And, importantly, you can do this retroactively. So even if your data set doesn’t provide the channel grouping, by default, you can go in and edit in later. So case formula is really a powerful tool within Data Studio to have available for your data visualization.
All right, so there you have it. This is how you can utilize the five formulas in your Data Studio calculated fields. Did I forget any of the formulas that you oftentimes use in your Data Studio dashboards? Then please let me know in the comments down below. And as always, if you liked this video, why not give us a thumbs up and also subscribe to the channel right over there because we bring you new videos just like this one every week. Now, my name is Julian till next time.