This 15 Minute Excel Macro Provides Granular Search Term Data
July 18, 2013
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):
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!
2) Get your file ready with THESE headlines:
3) Wait for the results!!
Browse By Category
The Advertising Principles That Guide Facebook's Decisions
Facebook highlights the advertising principles that guide their decision making in response to recent events.
Smart Ways to Get More Marketing Budget and Better Tools
In this new live webinar, Kristin Vick from Hanapin Marketing and Jeff Sauer from Jeffalytics discuss how marketers can ensure they have the budget they need to be effective with online advertising and get the right tools to make that argument.
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 Hero Conf Difference
Of the 50+ search and social conferences you can choose from, we're here to show you why Hero Conf is a can't miss event for busy digital marketers like you.
Improve Your Facebook Ads With Creative Hub and Split Testing
With the updates to Creative Hub and the Split Test feature, one can now institute a basic ad creation and testing process directly within Facebook.
Work Habits & Resources for the Stressed-Out PPC Manager
Feeling a bit stressed this holiday season? Get tips and resources that will help you become more efficient and alleviate stress!