5 Data Studio Functions for Calculated Fields you should know

By | August 28, 2019


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 sub domains 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 a 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 a 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 afterwards 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 reg ex, 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
reg ex match option. Let’s try it out. We go over to create 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 reg ex 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.

9 thoughts on “5 Data Studio Functions for Calculated Fields you should know

  1. Mohit Harjai Post author

    Hey Julian,

    First of all, I am a big fan of you, I have one situation that is related to data studio blending feature. Hope you will solve it.

    The situation is that I have a blog that has so many posts and I want to see metrics of one category blog posts. For that I have list out post URLs in Google sheets and connect with data studio.

    All going good, but I don't able to see metrics of dynamic URLs that contains ? mark. Because Data studio only matches non dynamic URLs with Google sheets URLs and showing data.

    Hope you understand my problem. If not, so I will share screenshots with you through mail.

    Reply
  2. MeasureSchool Post author

    You might notice me using the term Formula a lot…. I only later on noticed it's actually called Functions in Data Studio… oh well! Enjoy the video and let me know which functions you use often

    Reply
  3. Ryoko-San Post author

    Hi Julian,
    Thanks for this video and all previous ones, so interestings.

    I'm actually trying to analyse datas from multiple choice question survey with Google forms and Google Data Studio.
    The data obtain are words separate with coma depending how many choices the person has choosen.
    ex :
    Construction, Design, Innovation
    Design
    Design, Innovation
    Construction, Design

    I'm using the REGEXP_MATCH formula to extract the word "Design" and count how many times it appears in the results.
    After that, I'm using the words count with the formula as data sources for graph.
    But I'm not able to have the correct regular expression to extract all the data because the word can be located anywhere (beginning, middle or ending)

    Here is the formula I use, can you help me with the regular expression I should use ?

    SUM(CASE

    WHEN REGEXP_MATCH(Affaires et industries, ' .*Design.*' ) THEN 1

    ELSE 0 END)

    Thanks for your help …

    Reply
  4. Bejinariu Catalin Post author

    Hey Julian I have a question! Do you know how to extract Image Organic Traffic with Google Data Studio?Is there a way to do this? You know that in Google Search Console there is a section which allows you to see the Image Organic Clicks which comes from Image Google Results! And Google Data Studio doesn't have Search Type as dimension.

    Reply
  5. Get Found Madison Post author

    This is great. Thanks for creating and posting it. One use case comes to mind for us: calculate branded searches out of Google Search Console data. Maybe even filter and separate them to show the power of SEO on non-branded searches.

    Reply
  6. pulijala siddhartha Post author

    Hi, if we want to pull only parts of data from the String how do we use the extract function ? Like for example I have data like:

    Tier1|data1
    Tier2|data1
    Tier1|data2

    From the above I want to extract only tiers and copy them to a new calculated dimension .

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *