Harvesting Bulk Negative Keywords with Excel

When I was just a boy, my mother caught me out behind the garage using broad match. I couldn’t sit for a week. Later, I graduated to the hard stuff: media buys, QR codes, direct mail… bad scene, man.

Still, despite her best attempts at teaching me the value of plus signs, quotes and brackets, some habits die hard.

The wide net of broad match, though often shunned in favor of more stringent match types, still has its place in the world of paid search. However, as Google and Bing-Hoo race to loosen the elasticity of these matches, wrestling your matched queries into submission becomes a river of tears labor of love. Fortunately, there are ways to process low-relevance queries in bulk without need for any scripting chops or a surplus of free time.

Enter Fuzzy Lookup.

Fuzzy Lookup is an add-in for Microsoft Excel that simplifies comparative text analysis and allows for variable thresholds of output similarities. Such a tool has countless potential applications for anyone working with large sets of data. For our purposes, Fuzzy Lookup will enable us to prune our search queries using our bidded keywords as matching criteria. The output can then be used to build large sets of negative keywords and, ultimately, maximize the quality of traffic being driven by non-exact keywords.

Before we get to work, you’ll need to download and install the Fuzzy Lookup add-in from Microsoft. Once finished, open a blank Excel workbook and look for the Fuzzy Lookup icon in the ribbon of your Excel window. Salvation lies within.

To populate your workbook, grab a list of your bidded keywords and the corresponding search queries. It doesn’t matter if you pull these from reports or just download them directly from the AdWords UI, but keep your initial selection to one Ad Group. This method is capable of processing much larger lists of keywords, but a smaller selection will serve you well when setting up for the first time.

Paste each list in a separate worksheet, using descriptive headers like “Bidded Keywords” and “SearchQueries”. Once both are in place, highlight either list and click the “Format as Table” button in the Excel ribbon. Which style of table you choose is entirely up to you, but do remember to indicate that your table has headers. Do this for both lists, and then click back to your Bidded Keywords worksheet.

You should now have two worksheets, once with a table of raw search queries, and the other with a table of bidded terms. I know how exhausted you must be, but mop that e-sweat from your iBrow and let’s see this one through. “TEAM” on three!

There’s a tab in the Excel menu titled “Fuzzy Lookup”, and a button by the same name behind it. When you click the “Fuzzy Lookup” button, it searches out all tables within the workbook, regard less of how many worksheets are present. A sidebar control panel will appear, and you should see the Fuzzy Lookup has already Identified your tables.

I generally keep the Bidded Keywords table as my Left Column, and my Search Queries table as my right.

Now, because I have 23 queries that I’m looking to match, I’ve set the Number of Matches to exactly 23. Experiment with lowering this number as you explore the tool and see the difference in the results that Fuzzy Lookup returns. I also keep my “Similarity Threshold” just West of center at around 0.33. Again, the specific results you’re after may be hiding behind a different set of conditions, so give yourself some time to try out alternative settings.

Finally, if my Bidded Keywords table is on Sheet 1 A:A, then I’ll highlight Sheet 1 B1 and click “Go”.

Note: Make sure the “FuzzyLookup.Similarity” option is checked.

Above is a picture of the output I receive using my lists of Bidded Keywords and Search Queries. For a little extra flair, I’ve added color scales as conditional formatting.

With this information, I can use the similarity score both to identify similar terms I may consider actively bidding on, and to identify low-relevance queries that could find a home in a negative keyword list somewhere. No pulling and comparing reports, no manual examination of hundreds of queries, just a free plugin, some swift fingers, and a lot of leftover time.

So what do to with that time? Here’s a thought: VLookups. Try adding in engine-side performance data (CTR, CPA, Impressions, etc) and see what correlations you find. What is the similarity threshold beyond which your CTR takes a dive? What are the low-similarity but high-impression terms that are artificially deflating your overall CTR?

Go forth, geek out, and keep it fuzzy!