Now time and time again, you probably would’ve heard that pivot tables are something you need to know if you work in digital marketing, particularly PPC. In this video, I’m going to walk you through how I use pivot tables to analyze large amounts of PPC data to visualize and contextualize data and spot trends. Let’s jump right in and I’ll show you how I do it.
Hi there guys, so let’s have a look at Google Ads. This is an old Google Ads account I inherited so the data in here is all very old legacy stuff that the client doesn’t mind sharing. First of all, what you need to do in the first instance is, download a list or a data set in order to analyze with a pivot table. For me to do that, the first thing I’m going to do is go to Reports and then once I am in here in Reports, I’m going to start building out the raw data that I’m going to use in order to create my Pivot table.
I’m going to create a custom report and I’m going to create a table because we want a tabular data in order to analyze it in a pivot table, so we pull that raw data in, in a table into Excel and then create a pivot table, off the back of that data. This is a blank report so I’m going to call this report, Pivot Table Test 2. I’ve already created one just to test it. Let’s hit OK. Now what you need to do is start building the report.
In order to build the report, you need to start dragging fields on to the report canvas, this is the canvas and these are the fields you can drag onto the report. You can actually search for fields as well, so that could be quite helpful for you if you want to find specific fields. Let’s get started. I think the first thing we need to look at is the month and we’ll also add the– Is there a date option, I’m sure there is. No, there isn’t a date option. Let’s also add the year. You’ll see as you drag them on the columns, it will populate.
Let’s add some campaign data as well. Campaign. You have to add keyword and then match type. As you see it will start building a report. You’ve got the month, you’ve got the year. In fact, with the month it’s got the year involved as well, so in actual fact, we don’t actually need the year, so I’m going to get rid of that. You’ll see the month and the year there. You’ve got the campaign, you’ve got the keyword, you’ve got the keyword match type. If you want, you can get the Add Group as well, there it is. And then we can start adding some numbers.
At the moment, this report doesn’t really tell us any data so we need to start adding some number data. In order for us to do that, we’ll get the current, I guess the most common performance matrix as well, we’ll get Impressions, we’ll take Clicks, the Cost, the Click-through Rates, the Average Position, the Average CPC as well. Now we’ve got a report that we can really get some data out of. We’ve got all of this data from all time in all the campaigns as a raw data file, so you’ll see how many rows there are here, so you’ll see 17,974 rows of data, so that’s a ton of data and this where pivot tables come in handy because it allows you to make sense of this data.
I’m going to go ahead and save this, just so I’ve got a copy of it ready to go. The great thing about this is, as the days go on in your campaign, this report automatically updates, so you can change the date range and you can literally just update a report as and when you see fit, for whatever time period you see fit as well. If you want to make it all time, I’ve saved this report now, so next time you come back to Google Ads, it will be all time data.
The next thing we need to do is download the data, so I’m going to go ahead and do that now. I’m going to download that as an Excel CSV file and there it is downloading now and I’m going to open that just now. Here’s our data download. I’ll just go back, here we go. Here’s our data download from Google Ads into Excel, so let’s rid of the top two rows because they’re going to stop us from having a proper table of data. Here’s the full table of data and what I’m going to do actually is open these columns up so you can see them better. This is all our data, you’ll see here there are the same 17,000 rows that we exported, 17975, so you’ll see that there. What we want to do is make sense of all of this data which is where pivot tables come in handy.
The first thing we need to do is create a pivot table. I’m going to go to the top cell A1, I’m going to hold down Ctrl and Shift and hit down, then hold down Ctrl and Shift keeping it held and hit right and this will enable us to select all the data. Now, what I’m going to do is go to Insert and then Pivot Table. Now, the options you have here are you can either open a pivot table up in an existing worksheet, so these are your sheets down here in the tabs or you can do it in a new worksheet.
Now, what I like to do is work off a fresh worksheet when I’m creating a pivot table, so you have one sheet for you raw data and another sheet for your actual reports. Let’s go ahead and do that in a new worksheet as you’ll see here. And this is the pivot table data builder, so this is where you actually go ahead and build out your pivot table data. In order for us to do that, we can start dragging some of these fields inside one of these four boxes. Whenever you drag something into these four boxes, the pivot table will start to be created. Let’s start off with looking at something quite basic.
Let’s look at all the campaigns, so I’m going to drag the campaigns into rows, so you’ll see all the campaigns will populate and they’re all there. The next thing you need to do is add your data so let’s say, for example, I want to see the whole time range, the whole time frame, how many impressions these campaigns got. I will just drag that here and go to values and you’ll see here the report builds automatically as the sum, so the sum of impressions, which is the total number of impressions across all of that data.
If I go back, all of this data, if I were to go ahead and count every single impression by campaign then it will be totaled here, so you’ll see that data update real-time. That’s really useful. It’s quite easy to do, in order to do something like that but say, for example, you want to start spotting trends, then we need to do something a bit different, so you might want to create a filter. Say, for example, I wanted to just see how many impressions my broad matched keywords are getting, so I would drag the search keyword match type into filters. Now by dragging it into filters, it allows me to have a filter which populates here and I can choose what data to show in this table.
If I click the dropdown here, you’ll see there’s three different types of keyword in the campaigns, there’s Broad, Exacts and Phrase. If I tick this box to select Multiple Items, I can go ahead and select a specific type of keyword match type is set to Phrase and you’ll see the impressions have changed total so it’s a lot less than obviously the total of all match types but you’ll see here it will populate and update automatically, so that’s an interesting one we could do as well. In addition to that, what we could do, in addition is we could look at, for example, the average CPC in addition to that.
At the moment, it’s not an average, so we’ve got the column average CPC but there’s something you need to remember about pivot tables whenever you’re dealing with an average, so fields like average CPC, Click-through rate and things like that whereas an average or average position those kind of averages need to be averaged out in the pivot table as well. At the moment, we’re doing a count, so we’re literally– Sorry, we’re doing a sum, so we’re adding every single, I guess every single value of a CPC for every single row we’re adding them together as a total, which doesn’t give us correct data because obviously, the average CPC isn’t £3,477 for this particular campaign.
In order for us to fix that we need to go into the values, so see here, it says sum of average CPC. If you click the little drop-down arrow, and go to value field settings, you can change that to a count or an average. In our case, we need to make it an average because it’s an average of the CPC. If I select average and then hit OK, then that will average it out and you’ll see the average CPCs are now showing. It’s quite a lot of decimal places with pivot tables, so you can go ahead and you can reduce those as well.
If you want to make it a proper pound amount, we’ll see the average CPCs here. Now, another thing you might want to do is you might want to see how have these campaigns performed over time so by using that month column, if we drag that into play as well into the rows section, you will see that it breaks that campaign out into different areas so you’ve got quarters, you’ve got months and you’ve got years. Now, quarters isn’t really used for us, so we’re going to get rid of that by dragging that away and you’ll see across the different campaigns by month, how the performance is going. What you can actually do is put the campaign at the top and you will see this particular campaign [unintelligible 00:09:44] how it performed at every single month in 2013, 2014, 2015 and you can start to see trends that way as well.
Again, we’re still on phrase match only, so I’m going to go ahead and change this field so all of the match types have keyword and you’ll see the data updates as well. When it comes to pivot tables, another thing you might want to do is create what’s called a pivot graph or pivot chart. In order for us to do that, we’re going to go ahead and go back to our data. We’re going to select all of our data, again holding Ctrl and Shift and pressing down, and then right to select all the data. We’re going to insert instead of a pivot table, we’re going to insert a pivot chart. What we’re going to do is, we’re going to do it in a new worksheet again.
Here’s the report builder but what you’ll see as well in addition to the report builder is this chart area here. This is actually where we’d be building the chart. I’m going to make it a little bit bigger so you can see the chart a bit better. As you would with a pivot table, you drag fields into the chart but the only main difference is with the pivot chart, you build the chart at the same time you build the table. If I go to campaigns and put campaigns down and then, if we decided to put in place, I don’t know, let’s look at the impressions by campaign.You will see here the chart builds automatically. You’ll see all the impressions are pretty much going to one campaign but the idea is you build out the reports to show your data in real time.
If I wanted to add, for example, if I did clicks and cost, you will see that in two columns as well. For analysis, it’s quite an easy way, a snapshot to see what your data is doing within your PPC campaigns. Now, this doesn’t just apply to PPC. If you have a CRM, for example, and you want to do some examinations of data there, the same principles still apply. You do a raw export of all your data, you put them into a pivot table and you adjust the data as you see fit.
Let’s try something a bit different. Let’s try and do campaigns, and then, let’s also do the month. Where are you, month? There it is. Let’s get rid of quarters, or we’ll do years, months campaign and let’s add the cost. Now, what you can see– Obviously, this chart’s quite small, so you can’t see properly but ultimately, what you can do is you can build the reports out and you can see here.
As you can see, the cost over time has been increasing and then it went down in 2016. Again, any time you add a data or change data in the pivot table, then it will update real-time in the chart as well, so you can start to see the applications this will have, you can do campaign analysis, you can produce data sets quite easily for people to analyze. If I get rid of the campaign area, we’ll see the sum of cost going up and down. The advantage of this is a snapshot.
You can go ahead and change and create graphs with the raw data without having to, I guess, export and update specific reports from Google Ads because as long as you’ve got that data feed of all that data, then you can go ahead and manipulate it as you see fit. Thanks for watching, guys. I really hope you enjoyed this tutorial on pivot tables for PPC. If you have any questions, please let me know in the comments. Check out the other stuff on my channel and don’t forget to subscribe, and I’ll see you guys on my next video. Thank you.