Analyzing Your PPC Account
November 1, 2010
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
- 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.
- 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.
- 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).
- 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.
- 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.
- 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.
- In the most recent month’s sheet, add a column after anything you want to compare – clicks, position, conversions, etc.
- In the first empty column, type in =VLOOKUP( – then click the newly concatenated cell.
- 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.
- 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.
- 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)
- 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.
- 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.
- 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!
How Should You Use Display Advertising?
We hear similar questions from clients on a regular basis. One question we hear often is “How do your other clients use Display Advertising?”
Expanded Text Ads: 7 Million Clicks Say They’re Underperforming
Over the past 6 weeks, we have been lucky enough to experiment with expanded text ads, and frankly, I’m not impressed with their performance.
£300 Off Hero Conf London Ends Tomorrow!
Time is nearly gone to reserve your seat for Hero Conf London, 24-26 October at etc.venues St Paul’s, at a huge discount off the regular conference rate!
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Expanded Text Ads Come To Google AdWords Editor
Advertisers have been able to create expanded text ads, but couldn't utilize AdWords Editor. That has now changed with the release of the latest version of AdWords Editor.
Bidding Farewell to Adwords Converted Clicks: Don't Panic
AdWords will soon be saying goodbye to the "Converted Clicks" metric. Learn more about what this means for your account and how to prepare for the transition.
Olympic PPC Ad Strategies
How spectators consume sporting events and a massive expansion in the digital space will prove to shake up who and how advertisers reach their audiences.
Hanapin's PPC Resources Are Now Ungated!
You can now access Hanapin's whitepapers and toolkits without having to fill out a form!
View Ad Creative Through The Eyes Of Your Searchers
Learn to understand that ads do not live in the vacuum of an excel sheet and common practices should be tested.
[New Webinar!] 11 Red Flags To Watch Out For When Working With An Agency
Leveraging Analytics For Remarketing: Picking The Ripest Fruit
Remarketing prevalence is well founded based on competitive returns in both volume and efficiency. With that said, this "low hanging fruit" world isn't always easy to pick.
How To Analyze Time-Of-Day Performance For Luxury Products
The importance of ad schedules varies across industries, services, and products. For e-commerce companies that sell luxury goods, ad schedules are more crucial.
One Week Left to Save 25% at Hero Conf London!
You still have time to join us for Hero Conf London, 24-26 October at etc.venues St Paul's at 25% off the conference price. But time is running out.
4 Common Questions Prospects Ask Me During The Sales Process
There’s always new, hot topics and questions we get asked by prospects to see what our company’s take is, and how we go about implementing from a services perspective. Read what our most common questions are.