News Flash: Link disavowal research for a hacked client with a ton of backlinks is a huge PITA.
Thankfully, I have figured out a cool way to save a huge amount of time keeping spam links in check while maintaining my sanity thanks to Excel’s Conditional Formatting tool. For my very first post on LocalSEOGuide, I figured I’d geek out for you…
So our client was hacked with the usual “free Russian porn & DNC emails” (thanks Putin!) stuff. As part of our efforts to monitor the hack, we planned to pull backlinks from GSC and other backlink indexes once a week. Each week we found new keywords that appeared as edge cases, which ended up being more common as more backlinks were indexed. For example, the initial influx of indexed spam links and anchor text were all about pills while the second wave was a mix of porn and pills (so hoping my wife has not checked my browser history…).
We needed to find a simple solution for us to manage an ever-evolving disavow file. There are a lot of tools out there to help with this, but I am a total SEO noob. Understanding for me requires a lot of doing and fact-finding for myself. So like an idiot, I set out to build a better mousetrap.
At first I rolled with Excel formulas. (Get ready for some eye-glazing). There was a really great article on Moz about One [Excel] Formula To Rule Them All that describes how to use if(isnumber(search(…))) to partially match strings, which was the first formula I experimented with. You can see it outlined by Jeremy Gotleib here:
=if(isnumber(search(“string 1”, [beginning cell])),”Category 1”, if(isnumber(search(“string 2”, [beginning cell])),”Category 2”, “Other”)
As I learned with our disavow file, this idea of “one formula” for everything can get pretty hairy. Jeremy even suggests using the formula described below explicitly for disavow files, but you can see quickly the work gets more and more complicated:
=IF(ISNUMBER(SEARCH(“submit”,A2)),”Spam”,IF(ISNUMBER(SEARCH(“seo”,A2)),”Spam”,IF(ISNUMBER(SEARCH(“directory”,A2)),”Spam”,IF(ISNUMBER(SEARCH(“free”,A2)),”Spam”,IF(ISNUMBER(SEARCH(“drugs”,A2)),”Spam”,IF(ISNUMBER(SEARCH(“articles”,A2)),”Spam”,IF(ISNUMBER(SEARCH(“.xyz”,A2)),”Spam”,”Other”)))))))
Wow. That’s a lot of “Spam” to deal with, not to mention nearly 300 characters typed looking for just seven matches, all of which return “Spam.” This character number actually grows linearly as you build out this nested function. Over the course of a few weeks, we had over fifty words that we could match. If I had committed to the if(isnumber(search(…))) functions we’d have been dealing with adding to a string that was 2000+ characters crammed into a single cell.
So, we turned to another feature of Excel to improve the speed at which we could sort and manage a disavow file: conditional formatting (highlighting) of a matched word.
I created a master document with five columns:
– Backlink
– Anchor Text
– Source
– Date Added
– Disavow.
As we found common words in the backlinks I would add conditional highlighting for the matched words to the entire Backlink and Anchor Text columns. I could save the doc and just open it back up each week with the conditional formatting intact, paste in new URLs and anchor text, remove duplicates, and then sort the Backlink and Anchor Text columns based on cell color. This brings all suspected errors to the top, and now you can just drag “yes” under the Disavow column to anything that rises to the top (of course, you still need to put eyes on these links because they may be legitimate links – for example, if the hacked client is a site for seniors that has articles about keeping grandkids from ingesting pills, your backlink might look like example.com/keep-your-grandkids-from-accidentally-eating-your-viagra – particularly those pre-teen boys!).
As mentioned above, this process paid off much more than I imagined as spam links and anchor text transformed from edge cases to par for the course within a few weeks.
Are you about to start work on a disavow file and don’t know where to begin? A good place to start out if you’re dealing with pharmaceuticals is with Andrew’s list at Ultimate List of Pharmaceutical SPAM Keywords. In fact, we’ve done just that. You can download the conditionally formatted excel spreadsheet that has all of those words highlighted in the first two columns, and you can just add new matches using conditional highlighting.
Happy spam hunting, SEOs!
No Comment