Analyzing Your PPC Account

By ,

67 SHARES

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.
    Concatenate the keyword and match type to ensure that when you pull data from the other tab, you are matching to the exact same keyword.

    Concatenate the keyword and match type to ensure that when you pull data from the other tab, you are matching to the exact same keyword.

  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.Vlookup with only reference cell in formula thus far
  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.
    VLookup Formula

    This is the entire Vlookup formula, start to finish. First: Reference Cell (it will look for this cell on the other sheet), Second: Highlighted columns including reference cell and data that you want it to bring back, Third: Number representing the count of columns between the ref cell and the data you want to pull (take this count from the second sheet as you have added columns on the first), Fourth: False.

  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!

Get more weekly links with our Fast Five newsletter! Five Fast Links in Your Email Every Friday.

Also send me a daily RSS digest

ACO_endad_AgencyLibrary1

Twitter Facebook LinkedIn Google+ Email Print More
  • http://www.chadsummerhill.com Chad Summerhill

    Hi Amy! Adding context to your data analysis is definitely the right thing to do and a time-frame comparison is awesome context.

    My latest blog post is somewhat related to yours so I thought I would include a link:

    http://www.chadsummerhill.com/calculate-peer-comparisons-ppc-data-analysis/

    Thanks,
    Chad

  • http://www.go-optimisation.co.uk Kane

    Hi Amy,

    Interesting post.

    Just wanted to clarify something – quality score reporting doesn’t change over time does it? It was my understanding that quality score was static and no matter what date range you are looking at it will be showing your current QS?

    Thanks,
    Kane.

    • Amy Hoffman

      Yes, Kane, you are correct. Unfortunately, that was a poor example. I’ve changed the post to utilize a more relevant example.

  • Gaute

    Hi Amy,

    Excellent article!!!

    I have a slight problem reproducing this in Excel. I would really appreciate if you could say exactly which columns in which sheets you are supposed to highlight. It’s a bit confusing when you just refer to sheet one and two (which is which). :)

    It might be that it’s lost due to me not being english/american, however, I would really appreciate if you could enlighten me on this matter!

    -Gaute-

    • Gaute

      Forget it. I solved the problem. :)

      • Amy Hoffman

        Hi Gaute,

        Thanks for reading! I’m glad you were able to figure it out. If you have any other questions, please let me know!

  • Alex E

    This was excellent advice Amy.

    As someone who lives and breathes paid search, this will not only save me a lot of time, but it will help identify a lot of optimization opportunities. Thanks for sharing!

    • Amy Hoffman

      Thanks for reading!

  • kg

    This can also be done in Google Analytics and is a lot easier. Just go to the Adwords section under traffic sources, then click campaigns, then drill down as far as you want (ad group, keyword,etc.), then proceed to the clicks tab. From there you can compare date ranges and see the changes over time.

    • Amy Hoffman

      You can find it in Analytics, which is handy. However, I find it more informative to look at clicks, impressions, position, and conversions all at once. For some reason, in Analytics the goals don’t show on that tab at this point. Plus, I’d prefer to look at a conditionally formatted Excel sheet so that I can quickly scan the information.

  • Davey

    Hi,

    I am only a young-star and I find it a little confusing to follow…mainly because its stated that if you export from the interface you need not concatenate.

    Then all the instructions after this step involve manipulating concatenated cells, which I didnt think I needed…

    I am just confused with this bit – Step 8. Then click the newly concatenated cell. What cell is this, the first keyword cell? no idea…

    Can you help me?

    … or were my parents always right :-)

  • Davey

    OK sorted it.

    • http://www.HanapinMarketing.com Amy Hoffman

      Hi Davey,

      Glad you got it all worked out, sorry I wasn’t of assistance! For anyone else with the same question, if you download it from Adwords Editor, you’ll need to concatenate but if you download it from the interface, you won’t because it will download with the symbols denoting match type. E.G. “phrase”, [exact], broad It used to download in separate columns, as it does when pulled from Adwords Editor. :)

      If you downloaded from the interface, for step 8 you’ll want to click the keyword cell where it says to click the newly concatenated cell and then for step 9, on the next sheet, highlight everything from the keywords to the variable that you are trying to look up.

  • http://profiles.google.com/craig.j.desmarais Craig Desmarais

    Awesome article! Very thorough and informative. Everyone new to PPC or managing SEM campaigns should take good notes from this article, as it is a great foundation to build your skills upon.

    Great job!

  • Alex Edlund

    I think this can be done by using SUMIF and I believe it would be less complicated. VLOOKUP may be more suitable for multiple sheets. For SUMIF all you would have to do is click on the entire column of data you would like to compare (say April keywords column) then click on the column of other sheet with the data your comparing (March keywords column), then click on the metric column you want to compare (revenue), and you’re all set. This is the formula.

     =SUMIF(March!A:A,April!A:A,March!D:D)

    I find that there’s less room for error with this formula. What do you think guys?

    • Anonymous

      Thanks for sharing, Alex! I will definitely give that a try!

  • Pingback: PPC Task Checklist For Account Success | PPC Marketing Advice

  • http://www.facebook.com/profile.php?id=519755111 Kay Mok

    @fe16933958769da26397db7b307289f6:disqus : i believe what you mean is SUMIFS, but if one is using excel 03 or previous version, CONCATENATE should be the only way they can do this kind of analysis

  • http://www.facebook.com/profile.php?id=519755111 Kay Mok

    @fe16933958769da26397db7b307289f6:disqus : i believe what you mean is SUMIFS, but if one is using excel 03 or previous version, CONCATENATE should be the only way they can do this kind of analysis

  • Pingback: PPC Task Checklist For Account Success | apressclub.com

  • Pingback: PPC Task Checklist For Account Success | vpressclub.com - Press Release, Press Releasing writing and Distribution

  • Zander Jones

    Amy, is there a Youtube demo of this? Between the step-by-steps above and the adjustments suggested in the comments below, I’m finding it difficult to get this VLOOKUP to work properly in my reports. Any help is greatly appreciated!