301 Redirect Mapping Using Excel Logic for Many URLs

Are you considering re-platforming or switching domains? Both typically bring up one issue that is a cause for concern for Webmasters, that is setting up 301 redirects for a massive amount of URLs. eCommerce websites, especially ones that have been fully indexed in search engines for quite some time that have built up backlinks and good rankings typically yield more URLs than content based, informational sites (with the exception of Buzzfeed, The Chive, etc). The culprit, thousands among thousands of product URLs. The painful reality is unless you map every single URL the correct corresponding location via 301 redirect, those pages will 404 in search engines index.

301redirect mapping

Related: Mini Tutorial: Store/Website Redirect Based On GeoIP in Magento

A simple solution is only focusing on redirecting category pages, CMS pages and pages with significant back links, leaving the rest to the land of the lost 404’s. An impact like that could be seen as a bad sign to search crawl bots but for the most part they don’t have a significant affect on rankings if it is not a high quality page that receives many sessions. The other solution is to upload a file that contains old URLs with the corresponding new URL using an extension depending on your platform.

Below are a few examples of extensions we have used for Shopify and Magento sites for massive 301 redirect files.

Shopify – Traffic Control

Traffic Control allows you to upload .CSV files containing a large amount of old and new urls and then from the dashboard you can add redirects one at a time. This is great for Shopify users and we have seen many successful migrations using this tool. Note: if the only change you are making to your URLs is at the domain level, you will have to make sure that the domain is registered to your Shopify account or else Traffic Control will not upload the items correctly.

Parse Traffic Control

Magento – Mass 301 Redirects

Similar to Traffic Control, Mass 301 Redirects is an upload module where you provide a .CSV file which is uploaded to .htaccess which will then look in the DNS for the corresponding URLs. Once installed, the settings for this module are found in the System – Configuration of Magento. You can download any files from the extension that you have uploaded to retrieve what has been uploaded and what has not.

Mass301

Related: Magento Layout XML Action Method Reference List

Both of these add on modules have one thing in common, they require a .CSV containing old URLs in one column, and new URLs in another. It is quite a tedious initiative considering the biggest challenge with massive 301 redirect mapping is figure out a way to not have to manually set up each old to new URL one at a time. In the case in which your website has 10,000 + URLs, there simply is not enough man hours to get this done in full (unless you have 1,000 monkeys in a room with laptops and a list of URLs). However with a little help from our friend “Microsoft Excel”, there is a short cut you can take by using formulas and logic to map the mass amounts of URLs.

1.) Gather your data

First you need to gather your data using a sitemap generator of your choosing. There is software out there such as Screaming Frog, Moz etc. you can use as well. All this does is access your sitemap and pull the URLs into a document that you can use. If you are in the process of re-structing your categories on a staging/test environment, the URL paths should be accessible from there so long as you have generated a sitemap file. Make sure you check the URLs to ensure that they have been indexed by search engines, you may even discover a few 404’s that need to be cleaned up.

2.) Sort your data

Have two columns of data, old and new and sort in ascending order. If you are changing domains, this is relatively easy if the navigation and product URLs are all staying the same. In that scenario, you can easily have two columns with the URLs under the old domain, and simply look up and change the domain name in the “new” column. You can compile this data early so you have the data ready before switching the domains in the DNS, then proceed to Step 5.

Scenario #2 is if the URL structure on your existing domain is changing (ex. category paths), you will need to proceed with the next few steps.

Old-New-Format

3.) Parse data

Parse data based on “/”s (text to columns). This will split the URL every time each keyword hits a “/”. This will break apart the URL into multiple cells including each keyword into it’s own cell. Remember to parse the data for both your old URL column as well as the new URL column. This is very important as it will affect how you match the data in the next step.

www.mysite.com/category/product.html should become www.mysite.com | category | product.html

Parse

4.) Match list on final products

Using if statements and vlookups, match the old list to the new list (ex. old: www.mysite.com/oldcategory/product.html, new: www.mysite.com/newcategory/product.html). Once parsed, you will have two points ending in .html on both lists, match them up using vlookup formulas. Since you split apart the data in the previous step, your “if statements” will eventually match the final word in the URL parameter which in this case is the name of the product. This logic will match the final word in the URL to both lists that you parsed in the previous step, leaving you with the mapped URL.

5.) Save you list and upload your file

In the end if your list contains different formulas, you may want to save the two columns in another spreadsheet without any formulas so you are just left with the cells containing destination URLs. Save the file in the required format the modules accept (typically .CSV) and upload them, the add on modules will do the rest. It is important that you have set up your DNS accordingly with your domain provider and pointed the URLs (if changing domains) to your eCommerce platform or else the redirects will not work.

Related: Testing Google’s Enhanced eCommerce Analytics on Magento