How to Use Calculated Fields in Google Data Studio

In this article, we are going to explain how to use calculated fields in Google Data Studio.

A ‘field’  is a specific metric used in a data visualisation report. A metric is a measure used to quantitatively track or assess progress, performance, quality and more.

In GDS, once a data source has been added, all of the fields within that data source are available to use in a report. By using ‘calculated fields’, it is possible to create new, custom metrics which derive from your data source. Calculated fields are essentially user-generated metrics.

So why would you want to create new custom metrics? Well, sometimes the pre-existing metrics are just not sufficient. Calculated fields are useful if you cannot answer certain questions with the available fields associated with your data. Some examples from Google Analytics could be Events per user which is total Events / Users or Product View per Session which is Product Detail Views / Sessions

In this article, we are going to explain the process of creating a new metric in 10 steps.

1) Calculated Fields

For this example, we are looking at two metrics, tax rates and product prices:

We want to be able to calculate the amount of tax that an individual customer would pay when they purchase a specific product. To work this out, the below calculation is used:

‘Tax paid by the customer’ is the new metric that we want to use in our data visualisations.

2) GDS supports basic math functions

Addition (+), Subtraction (-), Division (/) and Multiplication (*). The operators (and formulas) used in GDS are the same as Microsoft Excel or Google Sheets:

3) Creating a new Metric

We are going to create a new metric called ‘Tax paid by customer’ using the data from our example data source. To begin, it is necessary to export the spreadsheet into GDS. In order to connect Google Sheets to GDS, a new data source needs to be created by selecting the ‘+’ in the bottom right-hand corner of the page in GDS.

4) Add Data Connections

You can revisit our previous article on how to connect Google Sheets to Google Data Studio.

5) Editing your data

You have the choice to edit the data in your existing report before exporting it to GDS, or it is also possible to edit your data source in the GDS home screen after you have exported it.

In the GDS home screen you will see a list of your metrics in blue, and your dimensions in green. To create a new metric select the ‘+ Add Field’ on the right-hand side of the screen.

6) Creating a new metric

To start, enter a name for your metric. For this example we are using ‘Tax paid by customer’. GDS will then assign a Unique ID to your new metric – this does not need to be changed.

7) Calculating your metric using formulas

The next step is to input the formula for your new metric in the formula field:

When entering a new formula, GDS generates suggested metrics. Once you are finished inputting your formula, click save.

8) Check the new formula is saved

Your new metric will then display in your list highlighted in blue, with a small icon ‘fx’ indicating that it’s a calculating field.

9) Select the data format type

Next, select the data type for this new field. For this example, it is ‘currency’. Once complete, select ‘done’.

Your new metric will then be available to add to your data visualisation.

10) Duplicating a metric

Once you have created a new calculated metric, you have the ability to use it when creating additional ones. Below we have created another custom metric, ‘Average price + tax’, by referencing ‘Tax Paid by Customer’.

You can create custom metrics for any type of data source to assist you in enhancing your existing datasets. This is a great way to complement your dashboard.

We hope this article was helpful and has assisted you in improving your knowledge in Google data studio.

Using Regex Expressions in Google Data Studio Dashboards

Google Data Studio is a great data visualization tool. It can be used to visualize data from Google Analytics, YouTube, Search Console, raw data in spreadsheets and much more.

GDS does, however, have limitations. For example, say you wanted to analyze your search keywords separated into long-tail and short-tail keywords. There’s no inbuilt filter to separate by  the number of words in a phrase. Using simple regex formulas can overcome many of these limitations and make GDS a far more powerful analysis tool.

In this article, I’m going to show you how to use regex/regular expressions to filter between long-tail and short-tail keywords. This is not an exhaustive  tutorial on regex expressions. Rather, I want to provide a concrete example of how using regex can stretch the capabilities of Google Data Studio reporting.

Data Studio offers many more functions other than regex, and you can see them here.

What is a Regular Expression?

A regular expression, also known as regex or regexp, is a special text string that describes a search pattern. You may have seen and used wildcard characters like “*.txt” to find all files ending in “.txt”.

Think of regular expressions as wildcards on steroids. There are many great tutorials on Regex expressions online.

Worked Example: Separating Short-Tail Vs. Long-Tail Keywords

The focal point of this case study is two questions which address the performance of long-tail and short-tail keywords.. The first question in the dashboard shown above addresses the number of impressions and the cost of long-tail keywords. The second question addresses the impressions and the cost of short-tail keywords.

According to Vertical Response, a short-tail keyword contains one or two words, while a long-tail keyword has three or more words. This is the criteria that we are going to use to define the regex expressions in this article. Also, search keywords may contain numbers, so we will include those as well.

There are many regex testing tools which can help you create and test regex expressions. For this article, we have used: https://regex101.com/.

When using regex101, first, you need to write the regex expression in the field labelled “Regular Expression”. Next, you need to write the content that you want to test in the field labelled “Test String”.

Short Tail Keywords

The regex expression: ^([a-zA-Z\d]+\s?\b){1,2}$

Let’s dissect the regex expression above to see how it works:

  1. ^ – Tells the regex expression to start at the beginning of the line.
  2. [a-zA-Z\d] – Means that we want all lowercase letters (a-z), all uppercase letters (A-Z) and numbers (\d)
  3. +\s – Means that the characters must be followed by whitespace.
  4. ? – Indicates that there can be one or more words before the current space.
  5. \b – Word boundary means that after the whitespace there should be something.
  6. {1,2} – This pattern should repeat once or twice. In this case, we are simply saying that for this regex expression to pass, there must be one or two words and nothing more.
  7. $ – Asserts position at the end of the line. This means that there should be nothing after the second word if this regex expression is to pass.

Let’s test our Regex expression to see if it works.

When you enter a single word, the short-tail keyword regex expression receives a match. This is indicated by the now highlighted test word – “hello,” as well as the text above the “Regular Expression” field that reads “1 match, 10 steps.”

When a second word is added, the regex expression again receives a match. This is because we specified that the pattern should repeat one or two times for the regex expression to pass.

When a third word is entered in the text string, the regex expression returns false and therefore no highlighted words appear. You will also see that the field above “Regular Expression” reads “no match.”

Long Tail Keywords

The regex expression: ^([a-zA-Z\d]+\s?\b){3,}$

Again let’s dissect the regex expression above to see how it works:

  1. ^ – Tells the regex expression to start at the beginning of the line.
  2. [a-zA-Z\d] – Means that we want all lowercase letters (a-z), all uppercase letters (A-Z) and numbers (\d).
  3. +\s – Means that our characters must be followed by whitespace.
  4. ? – Indicates that there can be one or more words before the current space.
  5. \b – Word boundary. This means that after the whitespace there should be something.
  6. {3, } – This pattern should be repeated at least three times to get a match. If we try only one or two words, we won’t get a match.
  7. $ – Asserts position at the end of the line. Since we haven’t specified a maximum number as the second argument in {3, }, this expression returns true for any keyword with a length of at least 3 words.

Let’s test our Regex expression to see if it works.

As you can see, entering 3 words in the test string returns a match. If an indefinite number of words are added, we’ll still get a match. The only condition is that there are three or more words in the sequence.

You can see that if the test string is made up of only two words, the regex test fails and no highlighted words appear.  You can also see “no match” above the field labelled “Regular Expression.”

Inputting Short-Tail Regex in Data Studio

Now that we have a working regex expression, we need to use it in the Google Data Studio dashboard. Once you have opened the dashboard, select the preferred chart or table that you want to apply the short-tail keyword length filter to.

Once you have selected the correct chart, scroll down through the panel on the right-hand side of the page until you see the “Add a Filter” button.

Once you have selected “Add a Filter” you will be taken to a list of options to use on your dashboard. If there are existing filters in your dashboard, they will be displayed in the list. In order to create a new filter, which in this example we want to do, you need to select the button at the very bottom of the list – “Create a Filter”.

In order to create the regex expression, you need to use the pull-up dialogue box that appears. You first need to input the name of your regex expression in the “name” value box. It is important to note that the fields need to be set to “Include” and “Search Keyword”.

Next, a condition needs to be provided. From the list, select “RegExp Match” as shown below.

Finally, the short-tail regex expression needs to be added, as shown in the image below.

Inputting Long-Tail Regex Tail Regex in Data Studio

Now that we have a working long-tail keyword regex expression, we need to use it in Google Data Studio dashboard. Once you have opened the dashboard, select the preferred chart/table that you want to apply the keyword length filter to.

Once you have selected the correct chart, scroll down through the panel on the right-hand side of the page until you come across the “Add a Filter” button.

Once you have selected the “Add Filter” button, you will be taken to a list of options to use on your dashboard. If you already have existing filters in your dashboard, they will be displayed on the list. If you want to create a new filter, as we do in this example, you need to select the button at the very bottom of the list – “Create a Filter”.

In order to create the regex expression, you will need to use the pull up dialogue box. First, it is necessary to name your regex. You can enter “Long-Tail Filter” into the “name” value box. It is important to note that the fields need to be set to “include” and  “Search Keyword”.

Next, a condition needs to be provided. From the list, select “RegExp Match” as shown below.

Finally, the long-tail regex expression needs to be added, as shown in the image below.

The Result: Tables and Graphs Filtered by Word Length

As  shown in the above screenshot, with Google Data Studio we can use our regex expressions to correctly filter search keywords. Search keywords with only one or two words have been identified as short-tail keywords, and those with three or more words as long tail keywords.

Conclusion

Although Google Data Studio has limitations, with the correct knowledge and tools it is possible to add versatility to your reporting and use the program to gain a more in-depth insight into your data.

At https://datastudiotemplates.com/, we create custom dashboards designed to visualize all kinds of marketing data. We have everything from YouTube, Google Analytics, Facebook Ads, LinkedIn Ads, Google Ads and a combination of all these and more. Check out our templates here.

The Best Free Data Studio Templates of 2019

Datastudiotemplates.com is one of the only sites on the web dedicated to providing great templates for Google Data Studio. We devote time and resources to researching and building templates that we feel will help you understand, analyse and monitor your marketing campaigns more effectively.

In this article, we have reviewed seven of the best free templates that are available online.  The free dashboard templates that we have reviewed allow for a quick analysis of performance and enable you to see and interact with your data in ways that are most helpful to you.

Aside from some great free templates that we’ve found, we also provide a number of premium paid templates which cover SEO, SEM, Social Media, E-commerce and Traffic.

Connecting GDS’ Dashboards to Data Sources

It is possible to connect a GDS dashboard to a huge range of third-party data sources as well as other Google platforms. GDS provides free connectors, but other premium or community connectors have also been built because of certain limitations associated with the default connectors. The templates we have reviewed in this article use only GDS’ default connectors and analyse data from Google accounts, making them 100% free to use.

  • You can access a list of all the connectors GDS has on offer here
  • You can access a list of premium connectors here.

In no particular order, here is our pick of the best free Data Studio Templates available right now.

1) Paid Channel Mix

The Paid Channel Mix dashboard is simple to read and understand and makes it easy to compare the performance of all your paid marketing channels. This dashboard displays an overview of your top-level data from Adwords, Facebook, Twitter and Bing, enabling you to compare your results at a glance.

Within this dashboard, you can take full control of your date range as well as being able to select your metrics. This dashboard includes handy features like trending charts and scorecards.

View Template: The Paid Channel Mix  (Created by Supermetrics)

Developed By: https://supermetrics.com/product/data-studio

2) Adwords 1 Page Report

This 1-page report provides a useful overview of how your Google Ads have performed over a certain date range. It displays the clicks, cost, CPC and CTR of each campaign in clear, eye-catching scorecards. It also enables an insight into the demographics of your audience as well as the profitability of your keywords. Its uncomplicated design allows for a quick and easy analysis which makes this dashboard effective for companies looking to review their Google Ads performance on a daily basis.

View template: Adwords 1 page report

Developed by: datastudiotemplates.com

3) Blog Content Performance

This interactive dashboard provides an insight into the performance of your blog content. By revealing the blog content that has generated the most traffic to your website, it makes it easy to identify the topics and authors that resonate the most with your users and drive the most value. This single page report provides a clear overview of your content’s performance which helps when developing a content strategy.

The Blog Content Dashboard also enables you to understand more about your content cohorts by changing the date range and comparing different topics within the same time frame, or the same topic over a different period of time. You can also compare categories of content which have the same topic in the blog’s title.

View template: Blog Content template

Developed by Alberto Grande head of marketing at X-Team

4) Youtube Channel Report

This dashboard displays your Youtube metrics in a visual presentation which helps you understand how your videos are performing. The Youtube dashboard showcases your best performing content and highlights the total number of views, user’s average watching time, number of subscriptions and videos shared. This dashboard is easy to use and allows for a quick but effective analysis of your Youtube content and the videos that perform well with your users.

Use template: Youtube Google data studio template

View Template: Youtube

Developed by: Google Data Studio Team

5) Content Performance Report

This Data Studio template provides a 1-page report on the performance of your content. The uncomplicated top-level scorecards clearly display the following metrics; Users, Sessions, Page Views, Bounce Rate and Av. Session Duration. The report also breaks down your authors and categories of your content by traffic (sessions, bounce rate and av.session duration). This dashboard’s design is vibrant and coherent and it is simple to set up.

View template: Content performance

Developed by: datastudiotemplates.com

6) Website Performance Overview

This dashboard provides a very easy to read overview of your website’s performance, on one single page. It includes all the necessary Key Performance Indicators, as well as useful scorecards that display total sales, revenue, average order value and conversion rate. The design is simple and as well as providing an effective overview of performance and traffic sources, some aspects of this dashboard enable you to gain a more in-depth understanding of your data.

Use Template: Website performance overview

Developed by: Alligator Interactive

7) Dashboard for Non Profit Web Data

This comprehensive report provides all the tools to enable you to effectively analyse the performance of your not for profit campaigns. It is functional and clear and includes an overview, breakdown of your audience, information on your Adwords and more. It also provides helpful instructions for connecting your own data.  

Use template: Dashboard for non profit web data

Developed by: https://www.wholewhale.com/

Free templates are a great way to start using GDS to better understand your data and create professional level reports. We also offer a range or premium paid templates which will enable you to create custom, visually compelling views of your data. You can access our premium templates here.