Analyze Your PPC Account in 15 Minutes with Pivot Tables

Whether you are starting a campaign from scratch, or just inherited one from a new client, the most important skill in running a profitable PPC Campaign is being able to analyze your accounts performance on many different levels quickly, and efficiently. Trying to navigate through the AdWords interface to view your quality scores, ad performance, bids, and overall ROI, can give even the most season veteran a headache. Luckily, Microsoft Office enables us to do some pretty handy things when it comes to visualizing data. The most valuable skill for any PPC master is the ability to utilize pivot tables.
How to create a pivot table
The pivot table is a table in Microsoft Excel used to visualize large amounts of data. To create the pivot table we will be using today, you will need to run a keyword report for every keyword in your account. Now open this file in Excel. Highlight all of the data by clicking in the upper left corner. Then click to the data tab, and click on the pivot table option. Now a new worksheet should open up, and you should be able to customize the columns, and rows for each of the following techniques.
Account Structure
Having a clean account structure is one of the best ways to make your job easier. To see exactly how your account is arrange place the ad Group and Campaign values in the row label of your pivot table. Now this will give you a hierarchical view of your account. You should be looking for consistent organization (i.e. if you have one campaign organized by match types, all should be the same way). This may not be useful if you built a campaign from the ground up, but if you’ve ever inherited a PPC campaign, this will help give you an idea of what you predecessor was going for.
Bidding
Next up is to check on your bidding strategy. This is helpful for seeing where that sweet spot is on a CPC basis. You should really only make this table on a campaign or ad group level. Different campaigns should be performing differently, hence why they are a different campaign. Now clear out your last pivot table, and put the Max CPC in the rows field on your pivot table builder. You will need to use the calculated, at this point you will need to decide what you want to look at. You can either look at the number of conversions you have, or the cost/conversion. Either way this should help you determine where you should be shifting your bids.
Ad Performance
From there we can look at how your ads are performing. Now you will need to run an Ad report. You can then segment however you want. Typically I focus on testing headlines, and comparing them to click through rate and conversion rate. This will allow you to determine if specific headlines or patterns work best for your industry. For this table put the Ad Column in the row labels field of the pivot table builder, and put the CTR in the values column. Using this data, you can test your ads more easily & see the most profitable headlines.
Quality Score
Now last but not least the probably the most helpful pivot table of the ones listed is Quality Score. If you don’t know why quality score is so valuable, check out this article. Now whenever you are auditing a PPC account, you should always check your Quality Scores whether you are testing ads, examining your bid strategies, or looking at your account structure always make sure you look at your quality scores.
Before we actually make the pivot table you will need to make a new column. Because a keyword with 10 impressions can be counted the same as a keyword with 10,000 impressions if you aren’t careful, we need to normalize the data. To do this create a click to a new album, and multiply impressions * quality score.
Now we are ready to create a pivot table. Depending on the size of the account you can either put campaigns or ad groups in the row labels field of the pivot table builder. From there put your QS*impressions column in the values field. Then add the sum of impressions to the values field as well.
The final step is to copy all of the data & paste only the values elsewhere (trust me trying to do calculations in Pivot tables is a nightmare). From there take your QS*Impressions and divide it by total impressions for each ad group. This will identify ad groups or campaigns with low quality scores. Those areas should always be addressed.
Summary
So pivot tables can save you a lot of time when it comes to auditing your account, and finding areas for improvement. Make sure that any of the data you use for these pivot tables is at least a weeks worth of data. That way you will at least partially account for different patterns throughout the week.