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!
Road Trip - 5 Tips For Creating International Campaigns
International markets can add complexity to any account, but here are a few settings and opportunities to make your global ad strategy stand out in non-US markets.
Discover 3 Levels Of Geo Targeting For Beginners, Intermediates, & Experts
Setting geographic targets is day one. Whether it’s to exclude places you cannot sell or serve or to specifically target your primary customer base, the implementation of geo-targets is on the must-do list.
[New Webinar!] What You Need To Know About New AdWords Features
In this new live webinar, Hanapin Associate Director of Services Jeff Baum and Optmyzr’s CEO Frederick Vallaeys will walk you through the new AdWords tools and show how you can use them to optimize your accounts.
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
The Social Strategies You Need To Know For App Promotion
Create social strategies to promote app awareness and leverage geographic and demographic data reported by these platforms to refine audiences.
Guide the Industry and Take the 2016 State of PPC Survey!
Each year, Hanapin Marketing manages extensive research into the State of PPC Report, which provides a statistical breakdown of the digital ad industry and guides expectations.
What's In A Name? Building Powerful Product Titles For Ecommerce
Over the years, we've tested a variety of different product title alterations in hopes of finding the perfect title. Did we? Yes, and no. The fact of the matter is, it depends.
What Upgraded Bing URLs Mean For You
Bing is continuing its effort to catch up with Adwords. Upgraded URLs, which isolate tracking parameters from final URLs, are now available to Bing advertisers.
Where Does Your Hero Conf Dollar (or Pound) Go?
5 Common Excel Issues And How To Fix
Excel, while a fabulous tool for PPC, is not without its occasional hang-ups. In this post, we’ll go through some common problems, and how to fix them.
[New Whitepaper!] Guide to Google AdWords: Advanced Edition
In this whitepaper, you will gain a greater understanding of the distinction between e-commerce and lead generation PPC strategies, learn the effective use of Remarketing, how to utilize advanced segmentation for improved targeting, cross-device attribution, and how to use automated rules in your accounts.
Google Announces Controversial Updates To The Keyword Planner Tool
Google updated the AdWords Community on the state of the Keyword Planner tool and the response from several advertisers was quite dramatic.
7 Overlooked Facebook Power Editor Features That Will Save You Time
I will be sharing often overlooked time-saving features for Facebook Power Editor so you can spend more energy on the tactics and strategy.
Ecommerce Secrets with Google, Bing, & Hanapin - New Webinar Panel!
This Thursday, August 18th at noon EST, we'll be hosting a webinar called "Supercharge Holiday Sales With These Must-Have PPC Tactics." Presenters include Carrie Albright, Jose Rodriguez, Purna Virji, and Matt Umbro.