In this video, I’m going to show you my word frequency method for finding negative keywords using the data from the search term report. You’ll also be using an online tool that I’ll show you, and you’ll be using Excel. Some of the stuff in this video can be more advanced, especially if you’re not used to using Excel. I’m not not going to slow down for it, so you might just have to pause and rewind if you missed something.
I’m going to show you this method that I came up with. The reason I came up with this method is, some keywords you’re just never going to have much data for, then make it search one or two times ever. But, within those keywords there are some commonalities, some similarities, and we may be able to pull out some negative keywords, if we can combine some of that data. That’s what I’m going to show you here. How we can combine some of the data from some lower impression keywords, and be able to identify negative keywords that way.
The first thing we need to do is, take this search term report and download into an Excel spreadsheet. For that we’re going to click download here. We can leave this in this format, excel.csv and click download. Once it’s ready, we’ll open the report, and this is what it looks like. This is all the data from that report, 8800 lines of data. Pretty overwhelming if you don’t know how to use the report — though we’ll get into that here right now. The first thing we want to do to make this usable is to put it into a table. To do that, we need to highlight the data. So we’re going to start with this row here; row two which is the header row. I’ve clicked here; cell A2, and now we need to scroll all the way down to the bottom of the report. We don’t want to include this bottom line which shows the totals, you want to include all the lines up to that point.
So what I’ll do now is, hold down shift and click this last cell here. By holding down shift, I was able to highlight all the cells from the top left down to the bottom right where I clicked. We have all of this highlighted now — including the header call. Next step is to go to insert tab. We want to insert a table, so click on table. Leave this box checked here, ”my table has headers”; because we included the header line when we highlighted everything, and click OK. So you can see that changed a little bit now we have this table, we have this data in a table. We have the search term table here. We have all the columns that we were looking at when we were logged in to Adwords, but now it’s in an Excel spreadsheet. So we can do different things with this.
The main reason we have this data — the table is, so we can sort the data, so we can sort clicks — highest to lowest. We can sort cost, highest to lowest. We could sort search terms alphabetically. For the purpose of what I’m going show you, you’re going to want to take the converted clicks column, and sort it from largest to smallest. So at the top; this should start with the keywords that have the most converted clicks, and you can see you most of the keywords only have one converted click, till to get down a little further, and the rest of them have a zero. So we have that sorted highest to lowest. The next thing to do is highlight the whole cost column. I’m going to start to the top of the column. Again here I’ll hold down shift, and click the last row of the column. So now we’ve highlighted this whole column. I notice at the bottom here — these data points, the average account and the sum. So what is this showing me is, for all the data I’ve highlighted, this is the average, this is the account — meaning I’ve highlighted 8814 cells, and then this is the sum. So since this is the cost column, what we’re looking at here is the total cost of all these clicks. And this sum is what we’ll be looking at here shortly once we start to dig a little bit deeper into what I’m doing with this.
Then the next thing we want to do here is actually be on highlighting this cost for now, we’ll come back and highlight that again. But, what we want to do is, copy all of these search terms. Again, hold down shift, and then we will copy that, control C copies. And what I’m gonna do is paste that into an online tool that I’ve developed on my website. If you go to adleg.com/word-frequency-counter — adleg.com word frequency counter. You see, there’s a box here where you can insert text. So I’m going to paste all those keywords that I just highlighted, and there’s probably other tools you can find online that’ll do this same thing. I have developed this one specifically for what I’m doing.
Some of the other tools that I found initially couldn’t handle the amount of data that I needed handled, so I had this tool created so that it could handle pretty much any length of keywords that we need. So we have the keywords here, and we’re going to click count. What this is going to show us is, the number of times each of those keywords appears on the list. If we look back at these keywords — so it’s not going to count the number of times each keyword got clicked on.
Fruitcake has 305 clicks, it’s not going to count that 305 times. It’s going to count fruit one time, it’s going to count cake, one time; then it counts buy, it counts fruit, and it counts cake. So each individual word that appears on this list is going to be counted. If we look back here, we can see cake is that the number one word which make a lot of sense because that’s what we’re selling — that’s what most of our key words contain that word. So a lot of the top words here is very common to what we’re selling, or sometimes you’ll see short words here like, “two” or “a.”
Let’s look at what we can do with this information. We’re basically going to use these words here and consider adding some of them as negative keywords, and to make that decision will look at the data from the spreadsheet. So starting with the most frequent words, we’ll see which which of these we might want to add as a negative keywords that’s not producing good results. These top ones here are pretty essential to what we’re selling, so I won’t even bother to look at those — but we’ll get down here to this one.
The cakes this company sells are not iced, so someone looking for an iced cake isn’t going find it on our website. So let’s look and see how keywords and search queries with that word in them are actually performing. Go back to the spreadsheet and we want to make sure that the cost column here is highlighted. What I want to do is; filter the search term column here, so it only shows me keywords that have the word iced in them. Click on that arrow in the search box here. If we type iced, it’s going to filter that keyword, click OK. Now you can see — basically what happened is any role that didn’t have the word iced in the search term is hidden. So the row numbers here jump around now from 2 to 19, 45, and so on. It’s only showing us the rows that have the word iced in this column. Then you can see also what it did here, it changed the sum. This used to be two thousand, now it’s showing a sum of only 561. It’s summing the highlighted cells here but it’s only including the cells that are now showing.
What we want to do with this then is, take a look at the converted clicks column. This is why I wanted to sort this column from highest to lowest, because it’s still just going to show us the rows at the top that have converted clicks. What we can do is, count this up and this actually totals 29 converted clicks. So we’ll take the cost column, $561.20 divided by 29 converted clicks and we get $19.35. That’s actually twice as much as our average cost per converted click is, and it is more than we can then we can afford to pay for per converted click. If we’re paying that much we wouldn’t be making a profit. This tells us that we had actually want to add iced, as a negative keyword. So yes, it is it is producing some conversions for us, but those conversions are actually coming out as loss. Once we add iced as a negative keyword, the rest of the campaign is going to be more profitable.
Let’s take a look back here. If you’re doing this on your own, obviously you’d want to look at every one of these words. You’ll come to a point where some of the more infrequent keywords just don’t have enough data. There isn’t going to be enough cost associated with those, to make any decisions yet to exclude them. So just work down the list until you get to that point.
I have couple of more examples. So we excluded iced, let’s look at the keyword icing. To replace this, we can just tape icing here. And now, instead of showing us search terms with iced, it’ll show us search terms with icing, and you can see the sum here dropped all the way down to $25.50; and there are 1, 2, 3, 4 converted clicks. If you do the math there, that’s actually a better cost per converted click on an average. There’d be no reason to exclude icing, that seems to be working so far.
Look at one more here, as an example. Let’s look at recipe. Just knowing what we know about this company, recipe probably is not a good keyword. If you watched the last video, we’ve already excluded this list to see what it looks like on this spreadsheet. We’ll add recipe. Cost of $19.12 for only to convert it clicks. These are pretty horrible numbers. We obviously want to add recipe as a negative keyword here. Now notice one thing here. When we did this, not only did it include words with recipe in it, but also included recipes — that’s because obviously recipes includes the word recipe in it. If we want to do this; but we don’t want to look at recipes, if we only want to look at recipe, there are some more filters that we can use on this tool. For this example, let’s say we just want to look at recipe but not recipes. So do a custom filter, and chose contains recipe leave this box checked for and, and does not contain recipes. Now you can see the sum dropped here to $54. Now it’s only going to include recipe, it’s not going to include recipes.
So that’s how you use this method. You do need quite a bit of conversion data for this to really work properly, but this is a pretty advanced methods once you’re at the point where you have that conversion data. This is one of those things that’s really going to help you fine-tune your campaign. Start to get rid of some additional search queries that aren’t really benefiting you, so can continue to move your campaigns forward, and focus on any keywords that really are converting and that are profitable for you.