This is the new and improved version of my initial Excel negative keyword tool. That tool was not very good because it could only identify strings, not whole words.
With the help of the genius Excel gurus over at ExcelForum, I was able to throw together a process for analyzing search query reports for the purposes of identifying negative keywords... but also to identify keyword opportunities.
I started out with the idea of
What if I could assign a value to each word within a given keyword phrase?
Then I thought,
Well, I'd need to know every single word within the keyword list.
That's where it started.
How to use the tool
First download your search query reports from Adwords and Bing Ads.
Next, remove all columns except for cost and conversions.
Next, you can clean your search query list using an excel formula called 'Substitute'. I essentially use this formula as a find/replace where it's substituting nothing in place of the characters I don't want in my keyword list.
Next, pivot the data so all search queries are unique and have total cost and conversions summed.
Next, I added my my Excel keyword density tool to analyze the phrase density of the search query report. This will output 1-word, 2-word and 3-word phrases and their frequency within your search query report.
Next, I input the one-word phrases under the "Lookup Value" column heading. Then drop down the formulas. Doing this assigns a weighted value to each word.
Lastly, now that we have assigned a weight to each word to ALL words in our search query report, we just need to sum those weights for each ACTUAL search query. That's what the formula in the "Results" column does.
How can I add a different language?
Special thanks to Alexander for showing me this. He wanted to make the tool evaluate Russian characters so he edited the code below and also allowed it to accept numbers! I'm thinking this should work with other language alphabets but I'm not 100% sure.
The negative keyword tool won't analyze words greater than 7 words.