Google Data Studio: How to create calculated fields and metrics? | Lachezar Arabadzhiev | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Google Data Studio: How to create calculated fields and metrics?

teacher avatar Lachezar Arabadzhiev, Founder and CEO @SkildLabs

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

3 Lessons (14m)
    • 1. Introduction

      0:14
    • 2. Creating your first calculated field

      7:14
    • 3. Configuring sophisticated custom metrics

      6:49
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

8

Students

--

Projects

About This Class

Calculated fields let you create new metrics and dimensions derived from your data. They also let you extend and transform the information flowing from your data sources and see the results in reports. In this class, you will learn how to create calculated fields and sophisticated custom metrics in both the data source and dashboard interface.

41558ba8.png

Meet Your Teacher

Teacher Profile Image

Lachezar Arabadzhiev

Founder and CEO @SkildLabs

Teacher

Hey there! I'm Lachezar Arabadzhiev and for the past five years, I have helped companies harness the power of data in a variety of ways to drive business growth and innovation. I began my career as a digital marketer at Microsoft but soon transitioned to the audience and analytics world, where I had the opportunity to work with major brands including Air Canada, RBC, Walmart, Kimberly-Clark, Nintendo, Mazda, and HSBC. In addition, I am the owner of the Data Studio Canada educational website. 

Throughout all that time, I always loved teaching and developing learning programs for my colleagues and friends. That passion materialized in early 2021 in the form of a brand new company that I was luckily enough to start.

I am currently the Founder and CEO of SkildLabs, where ... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: Hey there and welcome to another video of data visualization would Google Data Studio. And this class, we're going to talk about custom metrics and how to create them at a data source level and at the local level of your dashboard. Now let's get started. 2. Creating your first calculated field: In the previous module, we talked about data sources and what's behind the curtain. But right now we're actually going to focus on custom metrics and custom dimensions. And we're going to start with custom metrics and in particular our calculated field. So you would use custom metrics and dimensions when you want to create a calculation or something new that's not really available in Google Data Studio by default. So let's see what actually meets what we're gonna do first is add a new chart. And we're going to add our familiar table here. And I put it here, extended for little bit. Now, the next thing we're gonna do is add two specific metrics here on the right side. One will be sessions. And we're going to find that here on the bottom. And the other one is going to be page views. Now if you're familiar with Google Analytics, you know that the sessions are recorded every time a user lands on your website and the page views or the actual number of pages that user visits throughout one session. Let's imagine that we want to see how many pages a user visited per session. And we want to create a ratiometric for that. And let's, for a second, imagine again that, that's not available in Google Analytics. So what we would have to do first is before we create the calculated field, I'm just going to change this dimension here to country because I would want to see this by from different countries. Is, is there any correlation, for example, between the page views and the sessions? So we're going to click on Add metric here. And instead of selecting a metric that we normally would do, we would click Create field. The first thing that pops up here is really this tab that allows us to write a formula, create our new fields. So you can see that it's not even named, so we will have to put a name. So we're gonna put page views per, we'll just put a slash session. Now we've given it a name. Now let's think about the logic. And Google Data Studio uses a very, very simple syntax that over the years have become more complex and there's a lot more functionalities. In the resource section of this course, you will see a list of all. You will see a link to all the different formulas available for the formula that we're just going to type page views divided by sessions. And so what you can see, what's happening here is actually Google Data Studio suggesting different options for you as you type. And this is pretty helpful because you can just select it from here. And I will select sessions in this case. And here the little mark when it starts loading and really shows you if the formula in the syntax is correct or not. So if I did not complete the metric or put the wrong metrics, So for example, if I just it's sash and I didn't really pick anything, it shows me that There's an unknown dimension here. So I won't be able to save the actual calculated field or this metric. So I'm going to click sessions here and it will show up perfectly fine. Now, the type, obviously you can select, whether it's a currency, it's number. If it's anything specific, in our case is just the number for the comparison calculation and running calculation. Remember going to focus on these right now because we just want to create a simple calculated field. So I'm going to click Apply here. So you can see here that the aggregation that we talked about in the last module, because it's set up on auto. This is all taken care of. And we're going to see also an example of when you can actually specify your own aggregation. So I'm going to go back and there's the page views here, the new metric. So I'm going to click, right-click here and use our useful trick, distribute evenly. And in this case I can see the metric. It's perfect. It divides the page views per session. And this is a dynamic metrics. So this is a calculation that will always occur. And by doing so, it's fairly easy to spot an interesting insight. So for example, from the United States, a lot more pages are viewed per session compared to India or compared to the United Kingdom. So that might be an interesting insight for whether a company should enter that market or should create maybe more content since that specific language so we can reach our target audience. I'm going to close out this lesson by introducing you to something fairly interesting when it comes to creating these custom metrics or dimensions. So we've created page views here, procession, and that's our new metric. Great. I'm going to click back here on this formula. And I'm just going to select this as test so it's more distinctive and you'll see why in a second. So I'll click apply and we'll close it off. So I have this created. That's great. Let's say that it's a perfect metric. That's exactly what we're looking for. Old create another table by adding a chart table, maybe what bars. And I'll extend that. Now let's say that I would like to add the same metric here. And I'll try selling the metric here. So I'm going to click Add metric and run this test. And let's see what happens. Well, we're not seeing the metric here. And why is that? Well, because Data Studio has this concept of local and global scope. And what that means is if you're within a chart and you create a specific metric or dimension, that metric or dimension is specific to that chart and only works in that context. But it's not created for the entirety of your report. In order to do that, you would have to go to Resources. Click, Manage added data sources. Go to Edit and go back to our familiar data schema. And they're click on Add field. Here you can add test page views per session. We'll open it up. And here we're going to just ride page views divided by sessions. And we're going to click Done. I'm going to close this, and now I'm going to click on this table. So we have the test1 here. But what about if we go with the bottom and click Add metric and write tests now. Well, default group test page views per session. We've created our metric in the data schema, which means that it's now accessible for any new chart that we create. And it's tied to that data source. So it will simply be added. And we're all set. 3. Configuring sophisticated custom metrics: Now, if you fold the instructions correctly, you should have access to a Google Sheets with the data that we're going to use for this exercise. And we're using a separate Google Sheets because that data is not necessarily a connector with Google Analytics itself, but it's a static file that I've generated with some dummy data and a couple of different metrics. So just to show you what it looks like and you might have seen it already. I'll click here on database raw. And I have a couple of things. I have start and end dates, strategy, name impressions and clicks. And it's pretty simple what, what's available. And once you configure this file, you'll have to go back, click Add Data, then go to Google Sheets. And within Google Sheets, that was going to be normally the first one. So you'll click Allison data is raw. Alison Davis Rog in and click, Add. None of the other changes or those take boxes, you don't need to really adjust them. Click Add to Report again from this prompt. And you will have this generated. Now, what normally is going to happen is you're going to see no data because the data that I have here is actually 2016. So to correct that, we'll have to do a couple of things. The first one being includes start date as a dimension here. So what we basically did is we told you to studio, you know what, we don't want to put a date range for our data. So whenever you are mentioning here that we're going to see the data in the last 28 days, which is the default. You don't see that anymore because we've removed the date qualifier. Now the more important part here is we just have a couple of metrics here and we're going to add them. So I'm going to take this impressions, drag it here. And I'm gonna do this one as well. And I have clicks. Now, from what you can see, this looks like a paid media campaign because it has impressions. And that's how many time the ad was served, angles as clicks, how many times the ad was clicked? And we're going to keep it simple as that. So I'm going to click distributed evenly again, and we're going to create a new metric. But look closely what happens here. And now to demonstrate it a bit better, I'm going to tick this box that says Show summary row. And this is a really important one because it shows us the total of impressions here and the total of clicks. So nothing complex, we just have a grand total. Now, these grand totals are in fact the sum of all these metrics. But imagine that we want to do something different. We don't want to have the sum. We actually want to change the aggregation type. So if I click some here, because this is a separate data source and the aggregation as Mount automatic we can actually pick. So I can click on average here. And let's see what happens within the cliques. Well, here I'm getting the average of all click, so not the total, but the actual aggregation has completely changed. In this case, it doesn't make much sense. So it's not really useful, but there's also a couple of other things. So they count distinct and count. We can use count to see how many records they are. So if you spot it, normally Google Data Studio is going to create as a record count. And that is, it counts the records automatically because that used to be an issue before. You didn't have you haven't dimension in if you just wanted to see what is the total amount of records you had to go and create a brand new metric. But here it's fairly easy. You can add this one. We're going to remove it for now. And then just go back here to explore the different aggregation types. Or you have count distinct, so that'll count just the distinct value. So if you have repetition, it's not going to take it into consideration. We have minimum and maximum. So if I click on maximum, what I'm going to get is the maximum and the highest number, and the minimum will be the lowest number. And then for the more complex computations you have medium, standard deviation and variance. And that's fairly useful because I can just click on the standard deviation and I'll get, get it right away here. And I can get the standard deviation for each one, which is fantastic and saves us a lot of time. Now by clicking here, of course, you can change the entire aggregation and that's super useful. But there's another way to do it where you have a little bit more flexibility. Let's create another metric here. And we're going to click on Add metric, Create field. And we're gonna do something that's fairly, fairly easy. So CTR is what we're going to create and that's click-through rate, pretty common metric in any type of advertising, any type of paid media or advertising campaign. But the differentiator here is going to be, we're going to add some additional elements due to a formula. So let's just do clicks and then divide it by precious. This is really what the CTR is. So this is what the normal formula is, but we could specify the aggregation right here instead of doing an after. So essentially, I've said here I wanted to sum of all clicks first, sum them up, then divide by the sum of every single impression. And if I close brackets, I will get the green check here. And that means the formula and the syntax is good, and I'll click Apply. Now it shows zeros here as you can see, because this is a ratio. So we're looking for a percentage here, not necessarily a number. For the first time. We're going to actually change this to a percent and then click Apply. And right now we're getting 0.90.8 and we're getting this specific click-through rate from what you can see on the bottom is 0.08%. What this percentage means is it's really the sum of every single impression and the sum of every single click. So it's not the average of the column, It's actually the division here between those two. And that makes it a little bit more accurate. Sometimes that'll be done by default, by your connector. But just, just to let you know, you can actually specified here. If I wanted to do something else, if I wanted to say, I don't want this sum, so I want the average of all clicks. And for some reason that might be something that you're looking for and the average of all impressions. And then the division of those. I can click Apply. And not much is going to change here because these impressions and clicks are fairly close. Now that we know how to create custom metrics and calculated fields. In the next video, we're going to explore custom dimensions.