Use These 2 Excel Tools To Find Your Next Keyword Target
January 20, 2016
There are many optimizations to be made in the daily life of account management. In Google AdWords and Bing Ads, specifically, you have a wide selection of bid adjustments, budget changes, and settings revisions to choose from. But one of the most significant and potentially impactful is that of the search term report.
Your search term report, formerly the Search Query Report, allows you to review the queries typed in by users that caused your ad to be seen. Often, your search terms will be fairly close to your keywords – as seen by the search term match type.
Search term reports are an effective source for two things:
Ways to cut irrelevant or wasteful spend
Ideas for expanded coverage
In this day of paid search there’s a sort of “expansion or extinction” mentality. If you’re not constantly looking for the next opportunity, you’ll miss the waves of change your users are riding and find yourself left behind. Instead of wondering what you missed, let’s go through some fairly simple ways to evaluate your potential reach, using some handy Excel shortcuts. This report will not just have you skimming your query report, but it’ll provide you with actionable counts of opportunities and profit.
What You Need
- 1 search term report with match type, impressions, clicks, cost and conversion volume
- 1-5 topics you suspect might be contained within your report
To accomplish this goal, you’ll also need to know 2 useful Excel functions – the SUMIF and the COUNTIF.
Below is a search term report for a children’s magazine. I’ve pulled out only the broad match queries, as the exact and phrase matched queries are likely associated with existing content in the account. The broad match terms are just luck pairings from Google and Bing.
Within this list are all the queries from the past 90 days. What I know about this account is that there have been a fair amount of queries about a few specific topics, such as “book of the month” club, birthday presents, and Canadian options.
Now why don’t I just use the Google Keyword Planner or another such tool to estimate search traffic and build out a sample campaign? I’m glad you asked. Although that is a potentially successful option, I’ve found that using my actual existing account data is a much more promising representation of the traffic I may see come through.
So let’s evaluate what this might look like. To do this, we begin with a SUMIF formula. SUMIF sums the numerical contents of a column (such as Cost) when another column’s value matches your value of choice.
In this report, we want to find out how many times in the past 90 days a query containing the term “month” as in “book of the month club” has caused an impression.
The formula we use will look like this:
In this case, the reference column is the B column which contains the search terms.
The query I want to gather data on is “month” and the use of the asterisks indicates that the term ‘month’ can occur at the beginning, end, or anywhere in between.
The last range is for the column I want to sum. In this case, impressions. What I learn is that the term “month” appears in 2,088 impressions.
But what about the variety of search terms? Am I looking at the same 4 queries causing all the ruckus or are there actually 2,088 queries coming in over the past 3 months?
To check this, I’ll find the count of the queries. This is a simply shift from the SUMIF function, using COUNTIF. Note that COUNTIF only needs the cells that are being checked for the term. Easy peasy.
In this example, we have 29 different queries associated with the term “month.” This tells me that there isn’t an unbelievable spread of terms coming through this search. Instead, it’s a decently manageable volume that I could easily expand into.
Our next question is always: What does this cost me? And then immediately to follow: What am I getting from these terms?
To do this, let’s shift our SUMIF formulas to include cost and conversion data.
What I can gain from these quick reports is an estimate of the impact these terms would have were we to target the queries through actual keywords. This would remove the less relevant versions that fail to convert and better manage bids for the terms that prove themselves to be strong.
The appeal of this method is also it’s universal application. I’ve pulled the broad-matched search terms from the past 90 days in Google and in Bing. And for this report, I have 3 major terms I’m looking for: month, birthday, and Canada (shortened to “canad” to include both Canada and Canadian).
These formulas can then be built and applied to multiple spreadsheets as needed, provided that the columns are in corresponding order.
The breakdown of the formulas break down as follows:
This can also be used for terms that repeatedly spend budget without converting. If you suspect there is a piece of content that is repeatedly occurring in your impression traffic, but it’s not as simple as tracking down that single term, you’ve got your solution. Identifying the full list of queries that contain the offending term will allow you to swiftly quantify the damage made by all variations.
Here I can see that the term “national geographic has generated a huge number of impressions, but only through 8 variations of the same term.
From these queries, we’ve spent quite a bit but for very few conversions (and certainly outside our CPA goal). This kind of quick insight allows us to ask the important questions of which terms we are prepared to exclude and what we’ll overlook. For now, at least.
While we see the paid search world continue to grow and change with social media and new targeting methods, the search term report will forever be a staple in effectively identifying new opportunities for reach and for efficiency. And the quicker you can accomplish this task, the more quickly you can continue on to your next big optimization!
[New Webinar!] How Brands Can Effectively Use Display Advertising
In this new live webinar, Hanapin Account Manager Kristin Vick will dive into the meat of Display advertising, revealing tips to use the strategy to its full potential and more specifically target your audiences.
5 Ways To Improve Display Network Performance By Using Layered Targeting
Display campaigns can be difficult when it comes to targeting the right audience. In this post, you will learn 5 ways to improve your Display strategy with multi-layered targeting.
Expanded Text Ads And Other Bing Ads Editor Updates
Pilot access for Expanded Text Ads is now available to Bing Ads users. Get access to the link and learn about other new features in the latest Bing Ads Editor update.
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
Keynotes from Bing & Google + the Hero Conf Advanced Track
Where do you go to get the most relevant information in PPC, plus the opportunity to network with the best and brightest the industry has to offer? Why Hero Conf, of course.
3 Questions You Need To Answer Before Beginning Paid Advertising
So you’ve you decided you want to start PPC advertising, but now you are asking, “Where do I start?” We've listed and answered 3 questions you should be asking.
Road Trip - 5 Tips For Creating International Campaigns
International markets can add complexity to any account, but here are a few settings and opportunities to make your global ad strategy stand out in non-US markets.
Discover 3 Levels Of Geo Targeting For Beginners, Intermediates, & Experts
Setting geographic targets is day one. Whether it’s to exclude places you cannot sell or serve or to specifically target your primary customer base, the implementation of geo-targets is on the must-do list.
[New Webinar!] What You Need To Know About New AdWords Features
In this new live webinar, Hanapin Associate Director of Services Jeff Baum and Optmyzr’s CEO Frederick Vallaeys will walk you through the new AdWords tools and show how you can use them to optimize your accounts.
The Social Strategies You Need To Know For App Promotion
Create social strategies to promote app awareness and leverage geographic and demographic data reported by these platforms to refine audiences.
Guide the Industry and Take the 2016 State of PPC Survey!
Each year, Hanapin Marketing manages extensive research into the State of PPC Report, which provides a statistical breakdown of the digital ad industry and guides expectations.
What's In A Name? Building Powerful Product Titles For Ecommerce
Over the years, we've tested a variety of different product title alterations in hopes of finding the perfect title. Did we? Yes, and no. The fact of the matter is, it depends.
What Upgraded Bing URLs Mean For You
Bing is continuing its effort to catch up with Adwords. Upgraded URLs, which isolate tracking parameters from final URLs, are now available to Bing advertisers.