Scratch your skipping itch by jumping to this section “
We’ll cover calculated fields in data studio and their capabilities especially
when it comes to creating or customizing metrics relevant to your company,
creating calculated dimensions to present understandable and clean data
(common Google Analytics data issues will be addressed).
- Create Custom Metrics using Calculated fields within a Chart or within
a Data Source
- Create a Custom Dimension using the CASE Formula
An advantage in GDS (Google Data Studio) is that any metrics in it can be
calculated in a manner that fits your business model.
Let’s use an example with Google Analytics since we use it often for digital analytics.
Traditionally, conversion rate in GA is obtained by dividing Goal Completions by
the total number of Sessions. This works for the overall conversion rate of your
website but may not be specific enough for some of your website’s goals.
Using all Sessions can lead to inaccurate goal conversions.
If your company only sells in one location then you don’t need sessions from all over
the world for example.
The conversion rate of a business is more accurate once divided by one specific
In Google Analytics you’ll have a list of goals you’ve set called
(people who downloaded a document, those who signed up to your websites,
Maybe not everyone is eligible for your service except those who sign up and
satisfy your eligibility criteria (address etc.). In this case, all Sessions which are
all the visits to your sites wouldn’t make sense. You’d want to know how many
of those eligible visitors who signed up ended up converting. Your calculation
would be Goal Completions / Goal 2- Signups.
Calculated fields are useful in this context to create metrics relevant to your
Calculations in Data Studio vary from basic (addition, subtraction, average,
count, conversion rate, price, etc.) to complex ones (regular expression, floor,
rounding off of functions, differences between dates, etc.).
There are also miscellaneous calculations like the CASE formula which we will
cover because they’re known to come in useful.
Common Functions That Are Good to Know:
Aggregate (Sum, Avg, Count…)
Arithmetic (Floor, Round, Sqrt…)
Date (Date_Diff, Year, Day…)
Geo (To Country, To Region…)
Miscellaneous (CASE, CAST) Text (Reg Ex, Replace, Upper…
Here’s the full list of available functions…🥱😴
Where To Create Calculated Fields (Pros & Cons )
You can create calculated fields:
- in the data source
- in a chart (within your report)
The difference is that calculated fields created in the data source are
re-usable everywhere, whereas the others are only available in the chart
you’re editing. If you created a chart that had a custom metric using a
calculated field for a chart, creating another chart would mean you’ll have
to re-do the whole process (re-create your calculated field and name it for
the new chart). So by creating them in the data sources, they are created
once only and then always available also for any chart for example.
Reasons Why You Might Create Calculated Metrics With Charts
- You do not have access to the data source
- When you create Blends
That being said, let’s see how to create them both ways.
Creating A Calculated Meric Within a Data Source
Creating A Calculated Metric Within A Chart
Pros: don’t need edit access to the data sources (just report access)
Cons: must be recreated constantly to reuse
can’t use the metric within the calculation of another calculated field.
Go to your Google Data Studio Report and choose a chart you want.
Then select any available metrics and click “Create Field” in the pop-up:
Name your metric and create your calculation.
I’m interested to know the percentage of people who entered the checkout and
ended up really buying (the checkout abandonment ratio).
Note: we’re using the Google Merchandise Store.
So the calculation would be :
Purchase Completed / entered checkout
Here’s how it’ll look in Data Studio:
Results To Watch out for. Especially with crazy numbers
This is a common issue encountered with other viz programs like Tableau. It’s an
issue related to aggregation.
Let’s assume a business like a large supermarket sells cheese. This formula to
calculate the profit for cheese is wrong. Why? Because under the “Cheese”
category their products, different brands of cheese. Each brand requires its own
accounting and profit report that is not combined with other brands. So remember
this when you sum or average data.
Creating Calculated Dimensions
Let’s create a custom dimension. We will create a new regional grouping and will
explain the formula.
The process is similar to creating a metric.
Go to your data sources and “Add Field“.
To go back to your data sources, you can just click on the Google Data Studio logo
at the top left.
Rename your Field Name and add this :
So what have we done here:
Geneva and Lausanne are 2 important cities in the french-speaking area of
Switzerland. And I’d like to group both in a group called “French-speaking Switzerland”.
So that’s one custom dimension.
Same for the German-speaking side of Switzerland, I’d like to group cities like Zurich,
Baden and Bern in one group called “German-speaking Switzerland“. This is my
second custom dimension.
It’s a programming convention to add “other” for the groups that don’t fit in the
groups (dimensions) I’ve created. “Other” will be my 3rd custom dimension.
You use CASE statement to start creating your custom dimension and end it with the
WHEN describes your condition.
Our condition here is that WHEN a city (when you type city, Google Data Studio will
automatically suggest you the city dimension) is for example “Zurich”, “Baden”, “Bern”
THEN put them in the group called “German-speaking Switzerland”.
You can have multiple conditions since we created 2 different city groups.
Think of the ELSE statement as saying “otherwise”.
Each line executes in order.
If I created only 1 dimension, this is how it would look like :
Note: You cannot mix custom dimensions and metrics with calculated fields.
2 things happen automatically when you create or add a new field. You get the:
- Field Name
- Field ID
Field Name you can always change at any time.
However, you cannot do so for the Field ID.
There is only one time when you can change the Field ID and that’s when you create
a new Field.
So make sure to name your Field ID as soon as you add a new Field.
So name your Field Name but also your Field ID at the same time.
Why is this important?
The Field ID as you see comes with a string that would be very hard to remember:
Now If you were to create a custom dimension like the city-grouping we did but from
multiple data sources. If you wanted to apply a filter control that would work for each
source, you’d want each (data sources) to have the same Field ID.
Otherwise, you will encounter problems since they will have each different automated ID’s.