Apr 11, 2017

How to create Data Studio reports with calculated metrics

By Supermetrics

Looker Studio (Google Data Studio)Marketing Analytics

[ Updated Nov 7, 2023 ]

6-MINUTE READ · By Tina Arnoldi

By now, we hope you have become familiar with Google Data Studio. In this post, I’ll show you an advanced feature of Data Studio – using calculated metrics – and provide another example of reporting with a non-Google data source, this time including Twitter data.

Initially, the free version of Data Studio had a five-report limit per account compared to the number of reports allowed on the enterprise version. In February, Google removed that limit to allow unlimited reporting in the free version so you do not have to re-use reporting slots.

The easiest way to get started with Data Studio is to visit the Google Data Studio Gallery and use an existing report template. This way you can drill into the different features in the report to learn how they were created. And once you make a copy and add it to your account, you are able to make edits. The two screenshots below are from the Google Data Studio Gallery.

Also, you can take a look at the reports other companies create to get some design inspiration.

First steps in Google Data Studio

To create a new report, log into Data Studio with a Google account. You can see the connectors available in the below screen, such as Google Analytics and applicable demo accounts, such as the Google Merchandise Store. Later I will show you how to use Google Sheets to pull in data from Twitter.

Once you are familiar with some of the reports from the gallery, you are ready to start creating reports for data driven decisions in your organization. When you talk to your team about the type of data you want, draw it out first. It can be a simple sketch with pen and paper.

In the start, identify the audience. Is it members of your marketing team who need to drill into detail or executives who are looking for a high-level overview? What is the purpose of your Data Studio report? Is there a clear action or decision to be made by recipients when they receive the report?

As you work on your reports and learn how to use the different fields and formulas available in Data Studio, spend some time in Google’s support documentation to learn about the different options. In this post, I want to introduce you to calculated fields and then show you how to apply that advanced feature using a Twitter data source.

Calculated fields

Calculated fields allow you to combine and group data into metrics and dimensions and are added to your data source. An example of a calculated metric shown in this video by Google Analytics is Profit Margin. You do this by first creating a field calling Profit. In this example, it is assumed that we have fields in our data source called revenue and a field called expenses so the new Profit field is calculated as Revenue – Expenses. 

After you’ve calculated profit, you can create Profit Margin and set it to be displayed as a percentage value. You can find a good list of equations for main metrics from this blogpost.

The video also shows you how you can create a custom dimension, such as grouping country values in your data source as a new calculated field you can call sales region. This is done with the CASE statement which “lets you add logic-based flow control to a calculated field” and the regular expression match function.  Do not worry if you have not use RegEx (regular expressions) or CASE statements in the past. You can still follow along with this example so you are aware of the capabilities of Data Studio for calculating your own fields.

Supermetrics for Google Sheets and Data Studio

Let’s now look at how you might use Supermetrics for Google Sheets and Google Data Studio to use data outside of Google which will include a calculated field. With this Add-on, you extend the capabilities of what Google offers in Data Studio by using non-Google data sources, such as Twitter. Google Sheets will pull in Twitter data by using the Supermetrics Add-on. From the Data Studio side, Google Sheets is then selected as the data source.

You start in Google Sheets with the Supermetrics Add-on installed. Select the Add-on and choose a Data Source of Twitter data with the fields you want to pull into sheets. In the below example, you can see the Twitter field names on the right and partial results on the left hand side in resulting Google Sheet:

Next, in Data Studio, I create a new Data Source and choose the Google Sheet that has my Twitter data (which in this case is Sheet2).

Next, I create a simple table in Data Studio to display the Twitter fields that are in Google Sheet2.

Once I had the existing Twitter dimensions and metrics in my Data Studio table, I created an additional field to measure popularity, which of course is definitely subjectively. To make this simple, my new calculated field is called Popular and I will measure Twitter popularity by adding the Tweet retweet count to the Tweet like count as seen below in the calculated field.

The below table shows the Tweet retweet count and Tweet like count fields which were automatically pulled from the Google Sheet that had my Twitter data. The third field is one I calculated from this data for a metric of popularity.

This of course is a very simple example of a calculated field, but shows you the capabilities of Data Studio and the ability to extend those capabilities to pull in non-Google data, such as Twitter, with the Supermetrics Add-on.

I am sure there is a lot more to come with Data Studio which you can follow in Google’s Data Studio forum and can learn more from Google’s support files. Supermetrics is planning to release more Google Data Studio Templates, so stay tuned up!

About Tina Arnoldi

TinaArnoldi

Tina Arnoldi is Analytics and AdWords Qualified and one of the few people in the United States recognized as a Google Developer Expert (GDE) for marketing. Her agency, 360 Internet Strategy, is also a Google Partner. You can learn more about her on LinkedIn

Turn your marketing data into opportunity

We streamline your marketing data so you can focus on the insights.

Book Demo