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
Pitch to Speak Now: Hero Conf LA is Calling!
The deadline for speaker pitches to Hero Conf Los Angeles is midnight Friday, December 2. If that's enough to remind you to get writing, then head on over and pitch now!
Prioritizing Your PPC To-Do List
Managing PPC accounts can be overwhelming. Get tips on how to prioritize your PPC to-do list and manage your time wisely.
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
5 Fast Fixes For Your Holiday PPC Strategy
Despite the launch of holiday songs in October, the majority of shoppers will not complete their lists until mid-December. To go beyond, over 30% of those shoppers will continue up until Christmas Eve. For digital marketers, this provides just enough time to double down on...
Are You Thinking Of Account-Wide Goals? Let's Change That Mindset
Don't think: "what would be the easiest way to look at our data", think: "what makes the most sense per each type of advertising" when deciding on goals for different areas of your account. Learn more on how to think through goals!
Extended One Day Only! Get $600 Off Hero Conf LA
It's alright. We completely forgive you for missing our Cyber Monday sale on tickets to the biggest, the baddest, the most PPCest conference out there. If you're like the typical PPCer, you had a pretty full plate of your own client sales to run yesterday.