In recent years, the nature of SEO has become more and more data-driven, paving the way for innovative trends such as AI or natural language processing.
This has also created opportunities for smart marketers, keen to use everyday tools such as Google Sheets or Excel to automate time-consuming tasks such as redirect mapping.
Thanks to the contribution of Liam White, an SEO colleague of mine always keen on improving efficiency through automation, I started testing and experimenting with the clever Fuzzy Lookup add-in for Excel.
The tool, which allows fuzzy matching of pretty much any set of data, represents a flexible solution for cutting down manual redirects for 404 not-found pages and website migrations.
In this post, we’ll go over the setup instructions and hands-on applications to make the most of the Excel Fuzzy Lookup for SEO.
1. Setting up Excel Fuzzy Lookup
Getting started with Fuzzy Lookup couldn’t be easier — just visit the Fuzzy Lookup download page and install the add-in onto your machine. System requirements are quite basic. However, the tool is specifically designed for Windows users — so no Mac support for the moment.
Unlike the not-exact match with Vlookup (which matches a set of data with the first result), Fuzzy Lookup operates in a more comprehensive way, scanning all the data first, and then providing a fuzzy matching based on a similarity score.
The score itself is easy to grasp, with a score of one being a perfect match, for instance. This score then decreases with the matching accuracy down to a score of zero where there is no match. Regarding this, it’s advisable not to venture below the 0.5 to 0.6 similarity threshold in the settings, as the results are not consistent enough for a site migration or 404 redirects purpose below that limit.
For greater accuracy, it’s also desirable to trim the domain (or staging site equivalent) from the URLs, making sure that the similarity score is not altered by too many commonalities. For more information about the setup, you can also refer to this Fuzzy Lookup guide.
2. Redirect mapping automation and its benefits
Considering the time necessary to familiarize with the site, categories and products/services, it’s safe to assume that a person would manually match two URLs roughly every thirty seconds. If that doesn’t sound too bad, consider that it would take between five to eight hours for a website of 1,000 URLs. This would make it quite a tedious and time-consuming task.
Bearing in mind that Fuzzy Lookup can provide nearly immediate results with a reliable fuzzy matching for at least 30 to 40 percent of the URLs, then this approach starts to appear interesting. If we consider the savings in terms of time as well, this would translate to about three hours for a small site or over ten hours for large ecommerce site.
3. Dealing with site migration redirects
If you are changing the structure of a site, consolidating more domains into one, or simply switching to a new platform, then redirect mapping for a website migration is definitely a priority task on your list. Assuming that you already have a list of existing pages plus the new site URLs, then you are all set to go with Fuzzy Lookup for site migrations.
Once you have set up the two URL lists in two separate tables, you can fire up the Fuzzy Lookup and order the matched URLs by the similarity score. In my tests, this has proven to be an effective, time-saving solution, helping in cutting down the manual work by several hours.
As displayed in the screenshot below, the fuzzy matching excelled with product codes and services/goods (such as 20600 and corner-sofas, for example). This allows the matching of IDs with IDs, and the URL with the parent category, in the case where an identical ID is not available.
4. 404 error redirects
Pages with a 404 status code are part of the web and no website is immune, hosting at least a few of them. Having said that, 404 errors have the potential of creating problems, hurting the user experience and SEO. Fuzzy Lookup can help with that, requiring just one simple addition a recent crawl of your site to extract the list of live pages, like the example below:
The fuzzy matching works pretty well in this instance too, matching IDs with IDs, and leaving the match to the most relevant category if a similar product/service is not live on the site. As per the site migrations, the manual work is not completely wiped out, but it’s made a whole lot easier than before.
5. Bonus: Finding gap/similarities in the blog
Another interesting application for Excel Fuzzy Lookup can be found in analyzing the blog section. Why? Simply because if you’re not in charge of the blog then you are not likely to be aware of what’s in it now, and what has been written in the past.
This solution works in two ways as well, because if a similarity is found, then you have the confirmation that the topic has been already covered. If not, this means that there’s still room for creating relevant content that can be linked to the service/product category to improve organic reach as well.
Wrapping up
Time is money, and when it comes to dealing with large numbers of URLs that need to be redirected, a solution like Fuzzy Lookup can help you in cutting down the tedious manual redirect mapping. Thus, why not embrace fuzzy automation and save time for more exciting SEO tasks?
Marco Bonomo is an SEO & CRO Expert at MediaCom London. He can be found on Twitter @MarcoBonomoSEO.
The post Excel Fuzzy Lookup for SEO: Effortless 404 and site migration redirects appeared first on Search Engine Watch.