Join us April 28-30 | Austin, TX

Learn More
Screen Shot 2013-07-18 at 4.07.53 PM

This 15 Minute Excel Macro Provides Granular Search Term Data

Print This Post Print This Post

It’s series week on PPC Hero and we continue our run-down of time-saving techniques we’ve adopted in the past 12 months. This week Eric has already looked at building out your account, Carrie has dived into quick and dirty quality score techniques, and Sam showed you the ten minute ‘sense-check’ audit.  Today, I’ll be showing you how to analyze every word of every search term in less than 15 minutes.

A search term report can be overwhelming when you have a large account. This is especially true if you have a messy account. We’ve all seen accounts with different match types triggering the same ads, no embedded negatives to prevent cross-pollution, and close variants turned on all campaigns to top it all off. Trying to sort those search terms out can be insane.

Something that I’ve touched on before is labeling your keywords or search terms by type, or function in your account to analyze them. Obviously, the ideal situation is one where your account is so organized that you can do this at a more top-view level, like ad group or campaign. This is for sure not the case with those messy accounts we talked about earlier.

These hot messes need broken down in order to be understood. You need to get granular, bro.

For this example, I’m going to use a diet pill supplement eCommerce site. Let’s say you bid on everything diet related: low carb diets, cleanse diets, high protein diets, etc. But, you also have keywords like “diets” in your account without any embedded negatives. Ugh, who knows where all that traffic is going. Well, if you want a real look at how search terms are performing, you should look at your core terms that differentiate these search terms. Sure, “low carb diet plan” and “low carb dieting” are different in a lot of ways, but both pertain to the term “carb”, and that can be argued to be the bigger influencing term.

With this Excel Macro (posted below), you can analyze every single word of every single search term quickly and automatically while you sip on some coconut water and eat your kale chips.

When you’re all done, you’ll have something like this (minus the color coding):

Screen Shot 2013-07-18 at 3.52.06 PM

Some of the terms will be silly, like “to”. Obviously you shouldn’t judge the word “to” by itself, but more substantial terms like these are the main influencers in the search terms they’re in. These results give you a quick view of how they’re doing in your account.

So, SQR Macro!

1) Download

2) Get your file ready with THESE headlines:

Screen Shot 2013-07-18 at 4.04.19 PM

3) Wait for the results!!

 

 

About the Author

Amanda @Amanda_WestBook

Amanda is a Senior Account Manager at Hanapin Marketing, a search engine marketing firm focused on generating results through pay-per-click advertising. Google+.
  • Sam Mazaheri

    This is great Amanda, thanks!

    • Amanda West-Bookwalter

      No problem Sam, enjoy!

  • Josh Muskin

    This line is failing for me.. any ideas?

    .Range(“F2″).AutoFill Destination:=.Range(“F2:F” & .Cells(Rows.Count, 1).End(xlUp).Row)

    • Amanda West-Bookwalter

      Hey Josh, did you make sure your columns matched the screenshot I posted?

    • Mike Rawlings

      Mine did the same thing. Make sure you are in the search report tab when you run the macro

      • Sam Mazaheri

        I ran into this as well. Works like a charm from the first tab.

  • Kayla Hiller

    When I follow the link, it produces something that looks like:

    ö¯¿±öBãü7/w–¸Ò´}Ykm|?aF‘Þ7ÐûRºã•FJxãÍÆ($ɹ;)|ƒNx€˜

    Is that suppose to happen?

  • Nanda

    Im receiving “Run time error ’9′: Subscript out of the range. Help me

  • Amanda West-Bookwalter

    Thanks for making this super clear for everyone!

  • siddharth

    This is awesome. Every person doing PPC must use it…

    Thanks for posting…

Close

Sometimes we all need a little help from a hero, and that's exactly what we offer at PPC Hero. From a simple account audit to full time management, you can hire a hero to work on your accounts!