Have you ever looked through an old scrapbook to find yourself smiling at memories you’d nearly forgotten? For me, it may be dressing up as Aqua to perform a sixth grade rendition of “Barbie Girl,” holding my at-the-time new baby brother, or some other embarrassing, proud or hilarious moment. Some of these moments will live on only in the scrapbook. (I can double-check but I’m pretty positive my mom won’t have any more kids.) On the contrary, these memories can spark future plans such as a reunion with my fellow Aqua impersonators.
By now, you’re probably wondering where I’m going with this. Believe it or not, the lifespan of a PPC account follows the same trend. There are highs and lows, and often good times are forgotten. Looking back into your account’s history can help direct your future actions. Furthermore, reviewing your account against a historically successful month will give you a good grasp on where you stand and where you can stand to make improvements. At Hanapin, I like to put Excel to work. Using Excel to quantify past changes makes it easier to plan future changes. Follow these simple steps and let Excel do all the work in analyzing your account.
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.
3. Create the same keyword report for the month you’d like to compare it to. I like to use the same month last year, so that I know it isn’t affected by seasonal trends, but it can help to use the best performing month of the last year.
4. Both reports need to be in the same file, so copy and paste the second month into another tab. Be sure to label each so you remember which report is which.
5. In order to be sure that Excel pulls the data for the right keyword, we need to be sure that excel can differentiate the same keyword with different match types. 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:
a. In the most recent month’s sheet, add a column after anything you want to compare – clicks, position, conversions, etc.
b. In the first empty column, type in =VLOOKUP( – then click the newly concatenated cell.
c. Put a comma after the cell number, then go to the next sheet and highlight all of the columns from the concatenated cells to whatever it is that you’re looking for. (Quality score in this case)
d. After another comma, count the columns from the reference cell to the column you are looking up. (Make sure you are on the second sheet when you do this, as the first sheet 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.
7. 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.
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)
8. 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.
9. A conditional formatting box will pop up. Change the second drop-down to ‘less than’ and put a 0 in the last query. Then, click format; patterns at the top; and select a color. Hit okay and, voila, any cells that show a negative difference will be highlighted.
I like to look at it from this angle because it gets to the root of the problem. If clicks and conversions have decreased and so has position, maybe you need to increase bids to get into a higher position. If your position hasn’t changed but clicks have decreased, take a look at your ads to make sure they are tightly themed; if your ads don’t fit for every keyword in their respective ad groups, consider rewriting ads or restructuring if necessary. All of these things may seem like obvious solutions but in a constantly growing account these things can happen without our knowing. I like to run these reports once a month, or at least every few months, to be sure the account is in optimal shape.
Better yet, you can use this 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, 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.
Who knew Excel could be as fun as scrapbooking?!
Hopefully this helps you as much as it has helped me – try it out and let me know what you think!
Amy Hoffman is a search marketing consultant at Hanapin Marketing. She also blogs for PPC Hero and SEO Boy.
Please read our Comment Policy before commenting.