This 15 Minute Excel Macro Provides Granular Search Term Data
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
PPC, KPIs, and the Marketing Funnel
Bidding In A Mobile Centric 2017
Mobile is continuing to dominate digital advertising. With mobile spend rising, start thinking about how to change your mobile bidding method.
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
How to Take Your Retargeting To The Next Level
Get tips on how to take your retargeting to the next level and get more out of your online advertising!
Ask Us Anything About Programmatic!
Hanapin Account Managers Emma Franks and Bryan Gaynor are here to answer ANY and ALL questions you might have about Programmatic. Ask away!
Google To Let Advertisers Use Search Data For YouTube Ad Targeting
As the YouTube viewer increasingly shifts to mobile, Google is actively searching for ways to build solutions to reach customers across screens in more effective ways.