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:
Alternative Uses
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.
Example:
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!
