Ahmad is back with another Data Studio tutorial. Today, he will take us through how we can calculate the conversion rate between two Google Analytics events within Data Studio. In this video, let’s learn the trick of using filters and subsets to blend data sources and create custom calculated metrics.

? Links:

Ahmads Siavak – siavak.com

Google Data Studio Calculated Fields: https://support.google.com/datastudio/answer/7569962

More Videos on Google Data Studio: https://www.youtube.com/watch?v=u05EXm0GeTg&list=PLgr_8Hk8l4ZGijmrFPjYSeKwLbnsqwq1e

In this video, Ahmad is going to show you how you can calculate the conversion rate of two Google Analytics events inside of Data studio. All and more coming up.

This video is brought to you by our premium membership MeasureMasters. If you truly want to master the art of analytics and data, then become a member today. And get access to over 70 hours of premium video content, exclusive tools, and mingle with other fellow MeasureMasters in our community. And if you ever get stuck, I’m there for you to answer your questions and support you along the way to measure mastery. Join today and become a MeasureMasters.

Thanks, Julian. This is Ahmad again with another tutorial for Google Data Studio. And in this tutorial, I’m going to show you how to calculate the conversion rate between two different events and report it using Google Data Studio. Let’s start by creating a new report in Data Studio and connecting it to Google Analytics data for Google merchandise store. And now, let’s first take a look at the events that are existing in this data set. So what I want to see is events category, events action, and events label. These are my dimensions and then for the metric, I want to see the total number of events. Quick tip, you can double click on any of these edges to quickly adjust the width of these columns of any table in Google latest video. Now, let’s review and take a look at what kind of events that we have. So most of them seem to be quick, we use clicks. But what I’m interested in for the purpose of this tutorial is enhanced ecommerce Add To Cart, enhanced ecommerce product click. Now when people come to this website and they click on products. And then they might add Add it to the cart. Okay, what we want to calculate right now is the ratio between product add to cart and product clicks. So it is kind of product Add To Cart conversion rates, as to say. So let’s go back to edit mode and see how can we define such kind of calculated metric in Google Data Studio. So normally when we have a metric here and we want to create a calculated metric, we can click on here in pageviews and create a field. Now we can name it anything we want, like products, click to ATC for Add To Cart conversion rate. So from each hundred product links that we get coming out to constantly get up. Here, we cannot use a normal formula. Because in normal formula, for example, we can divide page views. Okay, a page. page views, okay, by the number of sessions for example.

We can do that. We can divide a metric by another metric, we can add a metric to another metric. Okay, we can do arithmetic calculations with any two metrics that we want. But we cannot do it with with a subset of each metric. So the metric that we are looking at right now is total events. Okay. You can see, we can put total events in here, but we cannot divide it by anything else. Like, we cannot divide total events by total events. We cannot say that, OK, Google Data Studio, please divide just those events that match the event action Add To Cart by those events that match the event action of product link, we cannot say that directly in the formula, it is a single metric. And we cannot use subsets of a single metric in our calculations. We cannot say just some events, divide by some other events. Okay, the total of some events divided made a total of some other events, we cannot say that directly. So what should we do? Let’s see the trick that makes this happen.

So first of all, what we need to do is to create a scorecard for our main metric which is total events. Okay, so total events, we put it here. And we can see. So the total number of events. And if we add a summary row to this table, we can add the you can see that the numbers match. So this is the number of total events that these Google Analytics accounts had in the timeframe of this report. But how can we get the total number of events with event action product click? So how can we get this number? To do this, we need to add a filter. So a scorecard events. Go on and add a filter, I’m going to call it I usually called the name of a filter, exact criteria for that filter. So for example, for this one, I say event action equals product click. That way, when I’m looking at the list of filters in my Google Data Studio reports at a future date, I can quickly see what is this filter doing. Okay, I just need to include events, actions, actions are equal to product click exactly as it appears here. Ok. So now, it should filter this scorecard and show me the number of product clicks. Right now, it is not total events anymore. It is filtered version of total events. That’s why I need to click here and rename it to product clicks. Because I like the title of the metric to be descriptive to show what the metrics is about. And this is how we do it, we can click here and change the name of the metric temporarily not on the data source level, just for this scorecard to whatever we want. Okay, now, what I’m going to do is I’m going to hit Control C and control V to copy and paste this scorecard and create another one. And for this one, I’m going to apply another filter. So let’s again, click on add a filter and click a filter. And this time, guess what, event action equals Add to Cart. Okay, this is the event action that I want. So let me copy this to save myself from typing it again. And we want to include event actions that’s are equal to add to cart. And now we should have this number 7,373. Okay. Now, again, this is our product clicks. This is add to cart, or add to cart. Let’s keep it just like this. Okay, so I have the number of cards, and I have the number of products. These are basically the same metric. These are both total events. But these two scorecards have been filtered differently. So it is a total events. One filter applied this total event, another filter, filter, applied.

Now here’s the trick. When you create your metrics and filters in the way that you want and you’re already getting the numbers that you want, certain number of add to cart and product clicks, you select both of those, right click, and click blend data. And a newest scorecard appears. Okay, this is the new scorecard. Here’s the difference. If you check any of these two scorecards, these are connected to Google Analytics Google merchandise store as their data source. But this one, however, is connected to blended data. What is this blender data? This is a combination of two data sources and in this case is one data source is product clicks, total events filter to show product clicks only. And the other data source is total events filtered to show add to carts only. And right here, we can see that we have only two available fields, product click and Add to Cart. If you click on the metric, only two metrics are available in this blended data source.

Okay, now how to calculate the conversion rates between add to cart and product click. how to divide them together. You can click here on the metric only here, you can click on creative field, name it. So I’d usually in this cases, I use this naming convention CR for conversion rate between Add To Cart from between product, click and add to cart. So conversion rate between product links and add to Cart. It should be somewhere close to 65, 70%. Because it’s 7000 over close to 12,000. Okay, but here, we don’t have any metric name total events in this blended data set anymore. The only thing is that we have our add to Cart and we can divide it by product clicks, okay. And these are the names that we’ve given to these two metric, this could be my name, this could be Julian’s name, you know, put any name that you want on the metrics that you create. But you know, let’s be clear and descriptive. Right now, if I divide these two metrics together, it will give me a number less than one. But that’s why I need to choose here, instead of number. I want a percentage that converts that ratio to a percentage. And then I can click Apply. Because it’s a percentage, I tend to choose average, because if it’s you know reported in a tables and you have the summary row available, it will add percentages together for. For example, if you have all 1% 1% 1%, it will add them together to create a 3% summary row, which is not true. So I put average whenever I create a percentage metric or a conventionally, okay, so let’s click Apply. See, what do we have? Okay, so conversion rate between product link and Add to Cart is 61%. This is conversion rate between a subset of events, those who match our filter here, the way we created and the other subset of events.

You can use this same technique to blend and combine up to five different metrics of this type together. In other words, any blended data source can have up to five different data sources blended together in itself. Okay. Now, this creates a whole lot of possibilities for you. So for example, let’s say we also have events for product impression product checkouts, product removes from cards and product purchases. So impression, click, add to cart, checkout and purchase five different metrics. Okay. And if you have all these five metrics together in a single blended data, then we can divide them together and come up with calculated metrics like product, CTR, or click through rates, which would be the number of click divided by the number of impressions. Or we can have the checkout conversion rate which is the number of purchases divided by the number of Checkout. Or maybe even checkout abandonment, which is one minus the ratio between product purchases, and product checkouts. So the sky’s the limit and then you are not limited to events at all. So it is not that we can only do this with events, we can do the same technique, we can apply it to page views of the set and page titles or page URLs that can apply to sessions. Any metric in Google Analytics and in your data source that you can filter and create a subset of, you can combine them together, select them, all right click and blend them together and use the blended data source to create custom calculated metrics. You can also use the same technique on page views. And for example, divide the number of page views of your cart page by the number of page views of your product page and category page and homepage. Or if you have a funnel from the landing page. to the order page. to the thank you page, you can have three different metrics of page views to these pages, blend them together, and calculate the conversion rate between each step to the next step. Now that you learn this technique it’s your turn to put it to use.

All right, so there you have it. This is how you can calculate the conversion rate between two Google Analytics events within Data Studio. Did you know about this technique beforehand? I actually didn’t know I always used another trick but which was a little bit more complicated. So thank you Ahmad for making this video for us. Definitely, we’ll appreciate it. If you liked this video, then please leave us a comment down below how you found it and give us a thumbs up. And also if you haven’t yet, maybe consider subscribing to our channel right over there because we bring you new videos just like this one every week. Now my name is Julian and on behalf of Ahmad, til next time

## Leave a Comment