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!
How Should You Use Display Advertising?
We hear similar questions from clients on a regular basis. One question we hear often is “How do your other clients use Display Advertising?”
Expanded Text Ads: 7 Million Clicks Say They’re Underperforming
Over the past 6 weeks, we have been lucky enough to experiment with expanded text ads, and frankly, I’m not impressed with their performance.
£300 Off Hero Conf London Ends Tomorrow!
Time is nearly gone to reserve your seat for Hero Conf London, 24-26 October at etc.venues St Paul’s, at a huge discount off the regular conference rate!
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
Expanded Text Ads Come To Google AdWords Editor
Advertisers have been able to create expanded text ads, but couldn't utilize AdWords Editor. That has now changed with the release of the latest version of AdWords Editor.
Bidding Farewell to Adwords Converted Clicks: Don't Panic
AdWords will soon be saying goodbye to the "Converted Clicks" metric. Learn more about what this means for your account and how to prepare for the transition.
Olympic PPC Ad Strategies
How spectators consume sporting events and a massive expansion in the digital space will prove to shake up who and how advertisers reach their audiences.
Hanapin's PPC Resources Are Now Ungated!
You can now access Hanapin's whitepapers and toolkits without having to fill out a form!
View Ad Creative Through The Eyes Of Your Searchers
Learn to understand that ads do not live in the vacuum of an excel sheet and common practices should be tested.
[New Webinar!] 11 Red Flags To Watch Out For When Working With An Agency
Leveraging Analytics For Remarketing: Picking The Ripest Fruit
Remarketing prevalence is well founded based on competitive returns in both volume and efficiency. With that said, this "low hanging fruit" world isn't always easy to pick.
How To Analyze Time-Of-Day Performance For Luxury Products
The importance of ad schedules varies across industries, services, and products. For e-commerce companies that sell luxury goods, ad schedules are more crucial.
One Week Left to Save 25% at Hero Conf London!
You still have time to join us for Hero Conf London, 24-26 October at etc.venues St Paul's at 25% off the conference price. But time is running out.
4 Common Questions Prospects Ask Me During The Sales Process
There’s always new, hot topics and questions we get asked by prospects to see what our company’s take is, and how we go about implementing from a services perspective. Read what our most common questions are.