Now, I’m going to show you how to use the Google Keyword planner to find negative keywords and more specifically how you can use a spreadsheet from the keyword planner — edit that spreadsheet, and then added a long list of negative keywords all at once.
So to get started we need to pull up the keyword planner, and the easiest way would be to go Google and simply search keyword planner. It should be the top result, and you should be familiar with this tool, if not I’ll give you a quick overview. This is basically used to find keyword ideas for your campaigns — so it will show you different data related to keywords for AdWords including search volume and estimated cost per click, and using that data we can also find negative keywords.
To get started, we’ll click here — search for new keyword and ad group ideas. For this example I will look for keywords related to the word, “plumber” under plumber there — we don’t need to enter anything else that targeting you might want to change to your country, or if you have a specific localized service you could change it to your city or state, I’m going to leave it to United States. Again, nothing else here that needs to be changed for this and we’re going to click, “Get ideas.”
The page that it takes you on here — if you scroll down a little, it’s going to show you, “Add group ideas”;which isn’t something we want. What we want our keyword ideas, so we’ll click this tab here. So you can see it’s showing us the search term, “Plumber.” This is the average number of times that that keyword to get search per month. Google’s suggested bid if you were to target the entire US with that keyword that’s how much they’re suggesting you bid. If you scroll down then you’ll see related keywords. So again this tool is usually used to find keywords but as you’re going through looking for keywords you want to pay attention to any negative keywords you want to add also. So if you’re offering plumbing services — you probably want to add salary as a negative keyword depending on where you are, you probably want to add some of these different cities as negative keywords as well.
So let’s look at how we can do this a little faster, because yes we can look down this whole list, and you see this is showing keywords 1 to 30. There’s 800 keywords, so we could cycle throughout these pages looking at every keyword and finding negative keywords — or let’s do this. Let’s download this data into a spreadsheet, we’ll click download. We can leave this just how it is, Excel CSV format, download, save file, and then open this right into Microsoft Excel.
Now you can see here we have all the same data we were just looking at, except it is in spreadsheet format showing us all of the eight hundred keyword suggestions. So here’s what I want to do with this information. The first thing I’ll do is, copy this keyword column, and then going to paste, and we can just select this first option here. Looks like a piece of paper paste, and then it is going to paste this into this column here, and I’ll show you why do that in a second.
The next thing you want do is put this into a table that’s a little easier to use. So to do that you want to click the top left cell here. So A1, and then I’m going to scroll all the way to the bottom of the table — the bottom right. Now in order to highlight all of this data at once, and I’m going hold down shift and then click on this cell. And you see that highlighted all this data, go to insert table. My table has headers — yes it does, so we’ll leave that box checked, and click OK. So now we have our list here in a table, in this table now you can see we can sort the data — if we want to sort of average monthly searches, from largest to smallest, we can do that. If we want to search smallest to largest ,we can obviously do that as well — or we could sort the the keywords in alphabetical order. I like to do the largest to smallest here, because that’s going to start the most widely searched keywords, and work our way down.
The next thing I want to do here is take this column, and I want to remove the keyword, “plumber”; or anything that’s very closely related to that. So I want to remove, plumber, plumbers, plumbing, so to do that; highlight this column, go back to the home tab, find and replace. So first I want to find plumbing, and if we leave this replace box blank — it’s going to replace plumbing with a blank space, which is what I wanna do in order to remove that word. So a quick replace all, and you’ll see I made a 137 replacements anytime plumbing appeared on here — it removed that word.
Do the same thing with plumbers, replace all, and then plumber replace all. Now you can see we’re left with a list here of words that are related plumbers, plumbing. This list now is our potential list of negative keywords. So all I’m going to do is go down this list. If there’s a word here that we don’t want to exclude from our campaign, then we would delete it. If there’s a word that we do want to exclude, if there’s a word we think would make a good negative keyword we’ll leave it on the list. In this case we’re doing this as if we’re a plumbing service company, so keywords here related to supply –that’s a negative keyword, drain, snake, would be a negative keyword. Near me, we don’t want to exclude people searching for plumbers near me, so we’ll delete this from the list.
Now the reason I copied the list, is so we can still look to the left here and see why, “Joe the” is on the list? So Joe the plumber was the original keyword we had. We can exclude joe the plumber supplies and services, we will delete from the negative keyword list — and you just keep going down this list to leading any words that you don’t want to have as a negative keyword leaving any good negative keyword on the list, it might take you awhile. Again, there’s eight hundred keywords on this list, but when you’re done with this you’re going have a very extensive list of negative keywords, it’s going to help you save lots of money in your campaign.
I would say it’s definitely worth taking the time to do this. Once you’re done with that, you want to highlight — highlight this whole column. Again, to highlight an entire column; click at the top, hold down the shift key and click at the bottom. It highlights everything, and you can just copy and paste this into your campaign. You don’t need to worry about spaces, those are fine to leave in there. Just copy and paste the whole list and save it to your campaign, or save it to a negative keyword list.