Calculated fields in Google Data Studio (Looker Studio)

What are Calculated fields in Google Data Studio?

Calculated fields in Google Data Studio are custom formulas that you can create using mathematical or logical operators, functions, and fields from your data source. These fields enable you to convert your data into a more useful format and perform intricate calculations that might not be achievable with your raw data.

Calculated fields are fashioned within the Data Source Editor and can be employed in any report that uses that data source. They can also be shared with other team members who possess access to the same data source. Calculated fields are a potent tool for creating custom metrics, aggregations, and dimensions, which can assist you in gaining profound insights into your data.

Why use calculated fields in google data studio?

Calculated fields can help you do the following operations with your data:

• Basic arithmetic operations: addition, subtraction, division and multiplication.
• Data Modification: Use a text function like TRIM to remove leading and trailing spaces or a function like UPPER to standardise text. The most common inconsistency is usually the use of different case, e.g. “email”, “Email”, and “EMAIL”. This is easily fixed by creating a new dimension, e.g. Medium (Lower) and then applying the formula Medium (Lower).
• Mathematical calculations on your data: Use functions like MAX  – MAX() – to return the maximum value from a set of numbers or SUM to return the sum of all values in a set of numbers. Another good example of an aggregating function will be the STDDEV function that returns the standard deviation for data that represents a sample. 
• Logical operation: use the CASE statement to crate custom groups using conditional logic. There is no IF statement in Google DataStudio. However, by using CASE/WHEN should allow you to do exactly the same operations as the IF statement in Excel. 

Here’s an overview of what we’ll cover:

How to create calculated fields in Google Looker Studio (Data Studio)?

When your data doesn’t give you enough information, it’s time to create a calculated field.

• Navigate to ‘Resources” and then click on ‘Manage added data sources”.
• Select the Data source you’re using for your report. In this example, as I am using the Google Analytics sample dataset the Data Source is called ‘Master View’ so I click ‘Edit’.
• Under ‘Actions’ click ‘Edit’ (if you have more than one Data source you have to edit them separately).
•  Click “ADD A FIELD’ and insert your formula in the provided window. Google will also give you a random Field ID but don’t worry too much about it as the actual ID number is not important. If there is an error in the calculated field formula, Data Studio will show you a warning message, and you won’t be able to save the field. 

Note: Always name your custom fields with something descriptive so that you know which is which in your reports.

Top 10 Most Useful Calculated Fields and Functions in Looker Studio (Data Studio)

Now that we know what Calculated Fileds are in Google Looker Studio (Data Studio) and how to create one let’s talk about some of the most common use cases. 

Find all the examples here (click on the picture or the link below the picture). Copy the Dashboard (it’s free) – that I’ve built and use the different sections for your projects.

REGEXP_MATCH Function in Data Studio

I find the REGEXP_MATCH Function to be very practical when aggregating data based on existing dimensions, including custom groupings of pages, geographic regions, or traffic sources. 

Let’s say you are part of a sales team for an international company and you are in charge of Europe. And then imagine that the sales territories of your company are organized by West Europe and Eastern Europe.

Using the REGEXP_MATCH Function you can create a new dimension – let’s call it ‘Europe Sales Territories’.

CASE 
WHEN REGEXP_MATCH(Country, "Germany|United Kingdom|Switzerland|Spain|France|Ireland|Italy|Sweden|Portugal|Denmark|Austria|Norway|Finland|Malta|Iceland|Luxembourg|Jersey|San Marino|Guernsey|Andorra|Liechtenstein|Faroe Islands|Gibraltar|Monaco|Åland Islands") THEN "West"
WHEN REGEXP_MATCH(Country, "Ukraine|Bulgaria|Greece|Romania|Belarus|Sweden|Slovakia|Croatia|Serbia|Russia|Poland|Czechia|Estonia|Latvia|North Macedonia|Moldova|Albania|Kosovo|Montenegro") THEN "Eastern Europe"
ELSE "Other" 
END 

Using the formula above you will be able to see how these 2 parts of Europe are performing against each other. 

CONCAT function in Google Data Studio

The CONCAT function allows you to join various strings from your data together so you can end up with a more compact and more informative ‘new’ dimension. You can add as many dimensions as you want but make sure that the ‘new’ dimension is useful to you.

A good example of using the CONCAT function would be when you have multiple domains or sub-domains within your website and you want to see the full URL of the page in your Google Data Studio Dashboards. 

I will call this new dimension “Combined Dimensions 1” and the formula is:

CONCAT(Hostname, Landing Page)

And the result is:

Another good usage of the CONCAT function would be to build a new segment separating our visitors into segments based on their city and how the channels they used to find our pages.

I will call this new dimension “Combined Dimensions 2” and the formula is:

CONCAT(City," (",Country,")"," - ",Default Channel Grouping)

And the result is:

My third example – and the one that nobody talks about – is using the CONCAT function together with the IMAGE function in order to access the image data and display images in our reports.

I will call this new dimension “Combined Dimensions 3” and the formula is:

IMAGE(CONCAT('https://shop.googlemerchandisestore.com/store/20160512512/assets/items/images/', Product SKU, '.jpg'), Product)

And the result is:

Note: The IMAGE dimension is only supported in tables and no other chart can display images. 

REGEXP_REPLACE function in Data Studio

The REGEXP_REPLACE function is often used when we want to strip the parameters of a landing page and only want to see the traffic to that page. 

In the example below I used the following formula to strip everything after the ‘?”. 

REGEXP_REPLACE(Landing Page, "(\\?).*", "") 

I named the calculated field ‘Removing Query Parameters”.

And the result is:

As you can see in the second table by using the REGEXP_REPLACE function I can combine the traffic to the landing page (I removed the dimension “Landing Page”).

Note: To learn the basics of Regex go to this website https://regexone.com/. 

Another good use of the REGEXP_REPLACE function would be consolidating a value when there is fragmented data due to inconsistent naming conventions.

In the following example, I used the following formula to combine all Facebook referral sources.

REGEXP_REPLACE(Source, '(?i)((l|m|lm)\\.)?facebook\\.com', 'facebook')

As the previous example if I only use our ‘new’ dimension I can have a very ‘clean’ view (Table 2) of all visitors coming to our website where Facebook is the source.

CASE Function in Google Data Studio 

Last but not least is my favourite way of creating ‘custom fields’ in Google Data Studio: Case Statements.

Before giving you some examples let’s talk a little bit about the rules and limitations of using the Case Statements in Looker Studio (Data Studio).

The syntax of the CASE statement is pretty simple:

CASE
    WHEN condition THEN result
    WHEN condition THEN result
    ELSE result 
END

Rules and limitations of the CASE statement in Data Studio

• You can only add CASE statements to your Data Studio if you are able to modify the data source on which it will be added.
• When using Data Studio in different languages you may have to adapt some of the fields, e.g. COUNTRY will be PAYS in French. 
• You can’t compare dimensions to dimensions or metrics to metrics. You can compare a dimension or metric only with a literal value.
• calculated fields can’t mix metrics (aggregated values) and dimensions (non-aggregated values)

Now that we know what the CASE Statement is and it’s limitations let me show you some of my favourites CASE use cases (pun intended).

CASE Function in Data Studio to understand and compare sales

Let say you want to see how your sales during the weekend compare with sales during the weekdays. By using a simple Case statement you can easily separate the days into weekdays and weekend:

Case
When Day of Week Name = "Monday" Then "Weekday"
When Day of Week Name = "Tuesday" Then "Weekday"
When Day of Week Name = "Wednesday" Then "Weekday"
When Day of Week Name = "Thursday" Then "Weekday"
When Day of Week Name = "Friday" Then "Weekday"
When Day of Week Name = "Saturday" Then "Weekend"
When Day of Week Name = "Sunday" Then "Weekend"
End 

And the result is a clear separation between weekdays and weekend. Notice that to make things very clear in the second table I removed the standard “Day Of Week Name” dimension so we are left with a table clearly showing us our sales during the week and weekend.

Annotations in Google Data Studio using the CASE statement

If you ever wanted to annotate your charts so you can explain shifts (important events) in your data as of today the only way to do that in Google Data Studio is to use the CASE function.

Start by creating a calculated field and naming it after the even you want to annotate. In my example, I named the event ‘New Product Launch’.
Use the following formula

CASE 
when Date='20200220' then 1 
else 0
END 

In simple words what we are doing here is specifying the date we want to annotate and giving it a value of 1. After creating the new metric go to the list of fields in your data and make sure the aggregation type is set to ‘Sum’.

Finally before using the new metric go to the ‘LAYOUT’ setting and set the Series #2 data to display on the right axis, and display as a bar.

If you want to add more than one annotation simply duplicate this metric, adjust the dates and give it a new name.

Custom Content Grouping in Data Studio

Custom content grouping allows you to allocate a set of pages to a logical grouping and then report on them as a collective. For example, all pages where the path contains /shop can be grouped as Shop and all pages that contain /article can be grouped as Articles. 

Segmenting your URLs by type will allow you to:

• group landing pages together by subfolder or URL, e.g. Product Pages, Category Pages, Blog Pages, etc.
• Clearly see which segments are performing the best or the worst.
• Display clicks, impressions and CTR by segment so you can make accurate segment over segment comparison.

Custom content grouping is very useful when assigning custom content segments to historical data (after website migration, category renaming etc.) as you can assign any past and current category to a custom segment, e.g. you can assign both old /red-shoes/ and new /leather-red-shoes/ categories to the same segment ‘Leather Red Shoes’.

CASE
WHEN REGEXP_MATCH(Landing Page, "/red-shoes/") then "Leather Red Shoes"
WHEN REGEXP_MATCH(Landing Page, “.*/leather-red-shoes/.*”) then "Leather Red Shoes"
ELSE "Other"
End

In my example, I used the following formula to apply custom content segmentation the Google Shop.

CASE
When REGEXP_MATCH(Landing Page, ".*/.*apparel.*|") then "Apparel"
When REGEXP_MATCH(Landing Page, ".*/.*lifestyle.*|") then "Lifestyle"
When REGEXP_MATCH(Landing Page, ".*/.*drinkware.*|") then "Drinkware"
When REGEXP_MATCH(Landing Page, ".*/.*brand.*|") then "Brand"
When REGEXP_MATCH(Landing Page, ".*/.*home.*") then "Home"
else "Others"
End

And the result is:

Note:

• .* is used in the REGEX to include everything before and after
• | means “or” and is used to include more words to take into account

Revenue Targets using the Case Statement

As you can see from the previous example knowing the revenue by category is a good thing but what if I wanted to know whether I am meeting my revenue targets or not. Or in other words, how to report against targets in Google Data Studio.

From what we’ve already learnt about the CASE Function in Google Data Studio completing this task is actually very easy. All we have to do is assign a value (expected revenue targets) to a previously made custom segment – in our case Apparel, Lifestyle, Brand etc. – using the following formula.

CASE
WHEN URL by Type="Apparel" then "5000"
WHEN URL by Type="Lifestyle" then "400"
WHEN URL by Type="Drinkware" then "600"
WHEN URL by Type="Brand" then "3000"
WHEN URL by Type="Home" then "8000"
ELSE "0"
END

After creating our new field using a CASE statement, the default Type will be “Text”. We need to change this to Currency.

We also have to change the ‘Default Aggregation’ to “Sum”.

Using the newly created field in our Google Data Studio reports will give us the following table.

To make things a bit more visual I also applied Conditional Formatting to the table in order to highlight which categories achieved their targets, and which did not. 

In the “STYLE’ tab of this table under “Conditional formatting’ I added two rules.

The first rule sets any value in the ‘Revenue’ column greater than its corresponding value in the ‘Revenue Targets’ column as Green.

The second rule sets any value in the ‘Revenue’ column lower than its corresponding value in the ‘Revenue Targets’ column as Red.

Now we can clearly see how our categories are performing against our revenue projection.

Monthly Revenue Targets using the Case Statement

When it comes to reporting, context matters.

I don’t know about you but I normally do my reporting on a monthly basis. So applying the same logic I can assign revenue targets to every month and then compare them to the actual monthly revenue.

Or in simple words, we want to write a CASE statement that says when the month = x the target = y.

CASE
when Month of the year="01" then 5000
when Month of the year="02" then 4300
when Month of the year="03" then 7500
when Month of the year="04" then 9000
when Month of the year="05" then 4000
when Month of the year="06" then 10000
when Month of the year="07" then 3000
when Month of the year="08" then 4500
when Month of the year="09" then 2400
when Month of the year="10" then 3800
when Month of the year="11" then 6800
when Month of the year="12" then 7900
End 

Again I’ve applied Conditional Formatting so my stakeholders can spot ‘good’ and ‘bad’ moths at first glance.

---

Add-on 18.02.2002

Operators

Arithmetic operators

Addition: +
Subtraction: -
Division: /
Multiplication: *

Comparison operators

Addition: +
Subtraction: –
Division: /
Multiplication: *

---

Phew.
If you’re still with me, congratulations. I know the above steps weren’t easy. But if you followed my advice – and hopefully recreated all the tables above – you just made it very easy to create visual reports from your data for months and years to come.