There is no avoiding the fact that Negative keyword research is a vital component of any PPC campaign using broad or phrase match keywords. It is also one of the most labour intensive tasks in the process and involves some painstaking diligence.
The practice of downloading vast lists of keywords, splitting them into single word rows, and manually checking each word for relevance can be arduous. As well as being a gruelling task it also leaves itself open to human error, you can miss a word which may prevent half the ads in a campaign from displaying, prompting you to search for and find the offending negative and remove it.
So how can you make negative keyword research easier? Well, I’m all for working smart rather than hard wherever possible and coming from a technical background as an IT Engineer for almost a decade there are a few tricks up my techy sleeves. One of the simplest and most eloquent little tricks is using MS Excel’s useful “VLOOKUP” formula in conjunction with an auto-filter.
Fear not, this is much easier than it sounds!
The ‘VLOOKUP’ command allows you to compare data sets and draw information in from one to the other and the ‘auto filter’ can remove (filter!) anything that you do not need. To paint a clearer picture for you I will use an example from a recent PPC client that came onboard. They had a campaign that had not been touched in over 12 months, combined with their huge budget meant that I had tonnes of lovely data to work with.
The downside of course was that there are literally hundreds of thousands of search terms available for my perusal. This also presented another problem which was that the client sells a massive range of products with thousands of esoteric brand names I am unfamiliar with. After downloading an ‘all time search query report’ from AdWords I quickly realised that I was looking at just over a quarter of a million made-up words, brand names, and miss-spells among all of the other more common words.
Unfortunately I will not live forever and sorting through 250,000 potential negative keywords was not an option. So in order to reduce this list down to the sort of size that someone other than Rain Man could manage I decided to use some Excel magic…
The most obvious place to look for keywords that you can remove from the list is AdWords, clearly any keyword already in the campaign as either a positive or negative could be removed without concern. So I did a CSV export for the whole account from within AdWords Editor and copied out all of the negative and positive keywords.
First off, split the keyword phrases and search terms into individual rows, so you have one word per row. I use a Macro for this (which will be discussed in a later post) but for the time being you can use the ‘text to columns’ tool under the ‘Data’ tab in Excel. This will split all of the words in a cell into individual cells in the same row.
Then just cut and paste the keywords back into their original column, repeat this for both lists (keywords and search terms). Copy both these two lists into a new spreadsheet into two columns separated by at least three columns. So now I have two columns; ‘column A’ which has the exported keywords and ‘column E’ which contains the search queries. (I have made these pretty in the image below)
If you are following this you should now have two columns long enough to turn the scrolling icon into a tiny little rectangle! Remove the duplicates (also found under the Data tab) and you have two slightly shorter lists.
Here comes the magic; in ‘Column B’ (next to your exported keywords) copy the word “yes” into every cell adjacent to a keyword. Now in ‘column F’ (column next to the search terms) select a cell adjacent to a search term and type the following formula:
=VLOOKUP(E2,A:B,2,FALSE)
Now copy that formula into all of the cells adjacent to the search terms and voila you now have a long list of “Yes’” and the ugly “#N/A”.
Sort ‘column F’ alphabetically (agreeing to expand the selection) and copy all search terms that have “#N/A” in the cell next to them into a new blank spreadsheet.
What you have in front of you is a list of all search terms broken down into single words, without duplicates and that do not exist in the account as either a positive or negative keyword!
To continue with the example, after I did this I was left with 600 words, from 250,000. Nice.
The next step in this process, normally, is to remove any word which you do not feel would be relevant as a negative keyword. Instead what we will do is move those words into a new column; at the end of this process you will have two columns, one will be negative keywords which are ready to import into AdWords Editor; the other column is to form a new list which I call “non-negatives”, in reality of course they are really non-negative or positive keywords, but that doesn’t roll of the tongue.
This list of non-negatives is actually a really useful list of words that you have discerned to not be used as a negative keyword and also words that do not exist with the account. The next time you come around to do doing some more negative keyword research you can copy this non-negative list underneath the exported keywords; which means that they will be filtered out of any future potential negative keyword lists.
The beauty of this is that every time you follow this process you make it more efficient; by adding more and more generic words to the non-negative list, you remove the need to check those words again. Eventually this process will become so refined that you can download hundreds of thousands of potential negative keywords (from whatever tool you like) and boil them down to a few hundred highly relevant negatives within seconds.
To follow me on Twitter you can do so at @Koozai_Alec.
Leave a Reply