We're eager to please as this is the first task we've been trusted with. Once we get the data we'll calculate the KPIs, Key Performance Indicators, that our boss would expect: CPC, CTR, CPA and others. In the meantime, let's learn about Pivot Tables so we're ready. 

Here's the data I promised from the PPC account. 
You should take this and calculate the main KPIs like CTR and Conversion rate.
Let me know if you get stuck!

The main weapon in every data-driven marketer's arsenal is the mighty Pivot Table! It's the workhorse of marketing analytics, because it allows you to quickly summarize the data, run calculations and segment it any way you see fit. The best thing about it: it's drag and drop! Anybody can learn to use Pivot Tables, even if you aren't technical or don't know how to code.

Excel was initially used in Marketing to manage media plans, but really came into its own when Search Engine Marketing rose to prominence. Because Google allows marketing to be bought self-service rather than by negotiation over the phone, Marketing became more analytical and being able to calculate marketing KPIs in Pivot Tables became an essential skill.

Calculating KPIs

0:00 Okay, let's figure out how to do the calculations for KPIs. These are the standard KPIs that you'll want to calculate for any advertising campaign.
0:10 So over here we have the raw data this is the spend, impressions, clicks, etc. And we want to be able to calculate the final amounts right here.
0:25 So this is the cost per thousand impressions, the click-through rate, cost per click and conversion rate. Alright, so I'm doing this with the pivot table.
0:34 The reason why is because you could calculate it here or just kind of, you know, average these conversion rates, but it's going to turn out to be wrong.
0:45 If you you know, select all of these then you can see the sum is obviously wrong 110%. The average is 0.82%.
0:56 But if you look in the pivot table that's wrong. It's it's actually one point. One, three percent. It's because you can't average an average.
1:04 The, the conversion rate is already an average conversion rate for this line item. And therefore you actually kind of have to sum up all.
1:16 The clicks sum up all of the leads and then get the total conversion rate for that for that section. And that's what a pivot table is doing for you.
1:26 Okay. So let me just show you this works. I'm going to press command A. Control a. I'm going to go to a concert and then pivot table in a new sheet.
1:38 And then on, on this side, we want to kind of add the keyword which is what we're breaking it down by.
1:45 And then into the value. So we're going to add all the different things that we need. So spend impressions, clicks, and leads, conversions.
1:59 So this is just the raw data, right? Like we're not. Including any of the calculated metrics because we're going to calculate them ourselves.
2:06 And one way we can do this is if we wanted to, you know, calculate the CPM, up for a thousand impressions, we could say, spend you know, divided by.
2:19 The impressions divided by 1000 in brackets, and we get the CPM. But the problem is if we went and then change this, so say we wanted to break this down by device.
2:34 Yeah, then we're going to run into problems because now I have this like divided by zero error because there's nothing here.
2:41 And now there's you know, like it's not kind of calculating it properly. So the way that you want to do this is have this built into part of the pivot table itself.
2:53 And in order to do that, you need to add it as a calculated field. So click on add and then calculated field.
2:59 And then the way it works here is you can just put spend divided by corrections and that is divided by 1000.
3:13 And there we go. We just need to change the name here. So we're going to collect impressions and now oh sorry, CPM, that was for 1000 impressions.
3:24 And now whenever we change this, so say if we get rid of that and we put you know, by ad group in here or we could change it to campaign.
3:35 Now the CPM is always with us. So that's really helpful. The other calculations here are the CPM is the CTR.
3:49 So the CTR is. The clicks divided by fractions. Then we have the cost per click. So that's the cost divided by.
4:09 Enter. Oh, so here we go. I've put the wrong thing in here. I put some costs. So there we go.
4:17 Now it's, now it's fixed. What are the other ones? Yeah. CVR. That's the other one here. Then CVR. Click add calculated field.
4:29 And then this is the convergent. Oh, sorry. Leads. Dotted by declared. Cool. One thing you can do here is format it like this.
4:47 So we got a percentage but. The one thing I would say just to kind of let you know, is if this was Culturally different.
4:59 So say, you know, if it wasn't leads, it was leads. And then you wouldn't be able to use the method that we looked about here, right?
5:14 We'd, we wouldn't just type leads. We have to put it into quotations like this. So here we go. It's automatically changed it for us to leads dash website.
5:24 And then it will change back, which is kind of nice because it's referencing that. So I'm just going to change that back So that's how you create calculated field.
5:34 You know, you can label them now as well. CPC and that's and now that you have these calculated fields, you can basically kind of drag in and out the different dimensions.
5:47 And see, you know, how does that change the cost per click, the conversion rate, etc. I am hoping that was helpful.

What is the purpose of using a pivot table to calculate KPIs?

How do you add a calculated field to a pivot table?

How can you tell if a pivot table is accurately calculating the KPIs?

What is the formula for calculating the cost per thousand impressions (CPM)?

What is the difference between calculating a KPI in the raw data versus a pivot table?

The number one reason I would fail someone in an interview when running my agency, was simple. They couldn’t do basic calculations in GSheets / Excel for the main KPIs.

Vexpower Learning Certificate of Completion

Congratulations Zawadi Irungu 💪