Analyzing Your PPC Account

It’s the beginning of the month, what better to do than take a look at your account’s historical data.  Before creating an account plan for the month, I like to create a month to month comparison, so that I know what worked better in previous months, as well as the same month in the past year.  By doing so, I can also see what has changed (such as position or bid) and try to reconcile the differences, to ensure each keyword is in its optimal state.  You may have read my post about VLookups on Wordstream, back in August, but I think it’s a valuable topic, so I’ll share it again.

At Hanapin, we put Excel to work.  Using Excel to quantify changes makes it easier to plan future changes.  Follow these simple steps and let Excel do all the work in analyzing your account:

How To Compare A PPC Account Month to Month

1. Run a keyword report for the month. (If the month isn’t over, use the last month, to be sure you have enough data.)  Include anything that you’d like to compare.
2. Export it. *Note, for things like quality score that you can’t go back and lookup you would need to have previously exported the data.  I recommend automatically exporting a keyword report bi-weekly, or whatever time frame best suits the account.
3. Create the same keyword report for the previous month.  (For instance, if you’ve created a report for September, run the same report for August).
4. Both reports need to be in the same file, so copy and paste the second month into another sheet in the same Excel file.  Be sure to label each tab.
5. In order to be sure that Excel pulls the data for the right keyword, we need to be sure that it’s able to differentiate the same keyword with different match types. (If you download from the interface, you won’t need to do this; if you download from the Editor, you will.) Excel can only refer to one cell, so we need to put the keyword and the match type into one cell.  To do this, you’ll need to add a column after ‘Match Type’.  In that column concatenate (combine) the keyword and match type by typing Concatenate=(Cell1, Cell2).  It doesn’t matter if you add a space between the two cells, but make sure you do it the exact same way in both sheets.

   

6. Next we’ll do a VLookup, which is a formula that will look for our keyword on the second sheet and then bring back data.  We use a VLookup because our keywords won’t be in the same order on both sheets and it would take forever to look them up individually.  VLookups can be somewhat confusing if you, like me, don’t speak Excel – but it’s actually really simple after you’ve done it once, so bear with me! Well do this step by step.
7. In the most recent month’s sheet, add a column after anything you want to compare – clicks, position, conversions, etc.
8. In the first empty column, type in =VLOOKUP( – then click the newly concatenated cell.
   
9. Put a comma after the cell number, then go to the other sheet and highlight all of the columns from the concatenated cells to whatever it is that you’re looking for.
10. After another comma, count the columns from the reference cell to the column you are looking up. (Make sure you are on the sheet you are pulling from (I.E. if you set up the formula on the October 2010 sheet, make sure you are on the September 2010 sheet when you count columns, as the sheet with the formula now has additional columns.)  In this case, it is four. Type this number, another comma, and then the word false. *It’s important to note that you cannot lookup things that occur before your reference cell- so make sure your reference cells, in both sheets, are to the left of any information you might want.

    

11. Do that for each column you’d like to pull from the historical month and drop it all the way down.  Then, I like to create another column that shows the difference between months.  Simply subtract the past month from the current month.
12. If the difference is negative, it is generally not a good sign.  The quality score, impressions, clicks, or conversions have decreased.  (Remember if you are checking ad position, a negative number is a good thing)
13. Instead of manually checking each of the numbers, hold down control (command if using a mac) and highlight each difference column.  Then click conditional formatting from the format drop-down menu.
14. A conditional formatting box will pop up: change the second drop down to ‘less than’ and put a 0 in the last query.  Click format; patterns at the top; and select a color.  Hit okay and any cells that show a negative difference will be highlighted. From this view, you can see any negative changes that have occurred over time.  For instance, if position has decreased, you can raise position and see if the keyword will perform better.  If your position hasn’t changed but your clicks and conversions have decreased, then you’ll need to get a little more creative.  Has anything changed recently?  Landing page changes, overall site changes, or ad text changes could affect your accounts performance.
15. Repeat steps 1-14 for the same month in the previous year to account for seasonal differences.  (I.E. compare October 2010 to October 2009 in order to account for any fall or Halloween trends.)  You could do this by creating a whole new file, or by adding another sheet to your Excel file and pulling the numbers into the original sheet.  I would also suggest comparing the month against the best performing month year to date.

Your account may need a slight facelift.  These things always seem obvious on paper but, if you’re not careful, in a growing account, things can get hairy quick.  For that reason, I like to run these reports every month to ensure that my accounts are in top shape.

You can use VLookups for virtually any comparison and if you’d like to get an even better look at your account, you can use conditional formatting on each of the columns individually and add more specific criteria.  For instance, if you wanted to highlight major differences from month to month, you might format the difference in clicks to be orange if the number is between 0 and -200 and red if it is less than -200, so that you can look at a more detailed view of what has changed.  From this standpoint, you should be able to make more informed decisions as to how you can improve your account, whether it be increasing position, decreasing bids, etc.

Hopefully this helps you as much as it has helped me – try it out and let me know what you think!