Advanced Keyword Management Technique Using The Search Terms Report
The search terms report in AdWords should probably be the first thing you check when you are trying to better understand your account health or looking to make it more efficient. Keyword management is an art of balancing reach and cost efficiency - if you have too many broad keywords, you could be paying for clicks that are driving little or no value to the campaign goal. On the other hand, if you have too few exact keywords, you could be falling short of the target number of conversions. To find the sweet spot, the search terms report should be periodically used to add keywords and block irrelevant searches.
Sometimes, it can get too much. It's too time-consuming to check search terms every day, but checking it too infrequently, you face hundreds and thousands of search terms with no way of going through them all.
Since I don't check search terms frequently, I came up with a technique to work with a large search terms report. This post is for those whom, like myself, have scratched their head wondering if there's any way to comb through rows and rows of search terms and understand every searcher's intent.
Pamella Neely at SEMrush did an excellent job explaining how you can spot intent in each search term. “Each search query is not just empty words,” she says. “It holds an elusive but critical intent behind it.”
The key is finding the word within a search term that signal intent, because the intent is what determines if someone will convert or not. Looking for the intent keyword in each line is a lot easier than evaluating every word. Let’s see what I mean with an example below.
blue hats for free (4)
free yellow hats (3)
green hats free giveaway (4)
buy one get one free red hat (7)
free coupons for yellow hats (5)
There are 23 words in the five search terms and free appears in every line. What if the search terms were dissected and the words were counted? Free would be at the top of the list.
While all the search terms may point in the same direction - I want free stuff - because the search terms have different modifiers and order, they appear in separate lines. This is the biggest difficulty working with a large volume of search terms; there are countless ways to how people search. Although this example lists similar search terms together, in an actual search terms report, they could be anywhere in a list of thousands of rows. Also, if we don’t know what we are looking for, how are we going know where it is?
Here’s a guide on how you can quickly find these intent keywords in a large search terms report.
Step 1: Download a search terms report in the Keywords Tab.
Make sure the date range is wide enough for a report to have a large volume. Otherwise, it’s more efficient to work in the platform and make changes directly.
Step 2: Copy the search terms and paste them on to a single column in a new tab
TIP: Click the column heading to select the entire column.
Step 3: Select all rows below the heading
Select A2 and follow the instruction on the image to select all occupied cells in Column A.
Step 4: Separate every word in all of the search terms
Go to the Data tab and click on Text to Columns. Select Delimited and then “Space”. This indicates that white spaces are where splits will occur.
Step 5: Use a macro to put all the words into a single column
We are going to use a macro that moves all delimited words to right below the last occupied cell in Column A. If you don’t see the Developer Tab, follow this guide to enable it.
Copy and paste the following macro (Source: MrExcel):
Dim LR As Long, i As Long
For i = 2 To 4
LR = Cells(Rows.Count, i).End(xlUp).Row
Range(Cells(1, i), Cells(LR, i)).Copy Destination:=Cells(Rows.Count, 1).End(xlUp).Offset(1)
Make sure the macro is pasted only on the sheet that has the search terms.
Save the file in the Excel Macro-Enabled Workbook format (*.xlsm).
Step 6: Create a pivot table
Make sure the range includes the entire column (A:A). Filter out (blank), numbers and common words like prepositions in the pivot table and sort the table in descending order from largest to smallest.
And there you have it. This table lists every word in the search terms report separately. When you consider that it’s only one or two words that signal the intent of a search, you can really see the value of arranging all the words like this.
Personally, I’ve used this method to add negative keywords more often that I’ve used it to add keywords. Because I can set the match type to be phrase, adding just a couple of irrelevant negative keywords ensures that I’m blocking all possible variations that include those words.
Users vary a lot in how they search and it can get very overwhelming trying to digest every search term in its entirety. This method will help you find just handful of overarching words that are roots of many different variations. Of course, some queries could lose their meaning when dissected this way and you shouldn’t use this every time you look at a search terms report. Think of the method as an occasional in-depth review.
Keywords in paid search platforms are probably the most important element in your campaign. Think of it this way, even if you have not so good ad copies and a landing page, having all the right keywords could get you some conversions; conversion rate may not be high, but it will still be higher than 0. If you have zero relevant keywords, but the best ad copies and landing page, you won’t get any conversion. This is how important keywords are. Search funnels, customer’s online journeys varying in lengths and complexity, all start with a search. Keyword selection is like picking the paths that you think your customers will travel. It’s important to frequently check that you are on the right paths.