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!!