How to Do 301 Redirect Map Using SEO Tools and Excel to Save Hours of Manual Work? Real-Life Example of Challenging Migration

Table of contents:

• Why Are Redirects Important and How to Do 301 Redirect?
• Why Was Making the 301 Redirect Map for the Client Difficult?
• Gather the Basic Data You Need for the 301 Redirect Map
• Automate Whatever Can Be Automated
  • Remove URL Elements That Won’t Be Used in New Addresses
  • Create New Links for Your 301 Redirect Map
• Find Which Redirect Map Elements Require Manual Verification
  • Manually Verify Links with 404 Error to Find New Redirect URLs
• Include Hreflangs in Your Redirect Map
• Test Your 301 Redirect Map Once the Website Goes Live
• How to Do 301 Redirect Map – What You Need to Remember

What Will You Learn?

• Why redirects are so important when undergoing migration,
• How to do a 301 redirect map for developers,
• What tools can streamline the process,
• How to do a hreflang map for various language versions.

During website migration, there is a high risk that your links will stop working. That’s why it’s best to not modify URLs. But, as you may guess, it’s not always possible.

Why?

Because it’s crucial to implement changes if your link structure isn’t correct, your offer has been modified, or you want to refine product names. Actually, these are only a few reasons, and the list of situations when it’s necessary to edit URLs is much longer.

When talking about simple modifications, such as deleting product ID numbers from all URLs, you can create redirects in bulk.

However, the bigger your website, the more language versions it has, and the more modifications you plan to implement, the more challenging the process of doing a 301 redirect map becomes. Believe me, this can be a really tedious process, but the juice is worth the squeeze.

Fortunately, there are tools and tricks that make the task easier and this is what I’ll be talking about in today’s article. And if you need help with creating a proper redirect map, contact our technical SEO specialists.

Why Are Redirects Important and How to Do 301 Redirect?

Before I move on to hands-on tips, let’s start with some theory.

In a nutshell, redirects can be described as HTTP responses instructing the search engine to take users to a specific page. There are various types of redirects, including 301, 302, 303, 304, 305, and 307 redirects.

As you can guess, redirects are important for both users and SEO.

Why?

Because they make website navigation much easier and help to prevent situations when users see a 404 error.

Thanks to 301 redirects, you can pass the vast majority of the link juice to a new address, which has a positive impact on SEO and helps to ensure that your link building efforts won’t go down the drain.

Let’s go back to the topic of our article.

Why do you need redirects when doing website migration?

The answer to this question is simple. If you don’t create redirects or you leave incorrect URLs, it’ll have a drastic negative impact on your website visibility.

Just take a look at the screenshot below. It shows how page visibility decreases after page migration without redirects:

Why Was Making the 301 Redirect Map for the Client Difficult?

To put you in the picture, I’ll describe the process of creating a 301 redirect map using a real-life example.

In the case of one of our clients, the task was particularly complex, because I was dealing with an ecommerce website with multiple language versions, and there were lots of changes in the URL structure.

The client decided to choose a different CMS, remove ID numbers from addresses, and modify categories and product names that were used to generate URL addresses.

Taking into account the complexity of the tasks, it wasn’t possible to manage the process as a whole.

What did it mean for me?

It was crucial to analyze every single link, and based on that, create a 301 redirect map that would be implemented by a developer.

Step 1 – Gather the Basic Data You Need for the 301 Redirect Map

Where should you start?

The first step is to collect and compare all the necessary information. Gather both old URLs and new ones from the developer page version.

Note that it’s important to prepare redirects already when creating the developer page version and then implement them when the page goes live.

Otherwise, once the website is launched, there might be numerous 404 errors that have a negative impact on both UX and SEO.

Why?

Users can get frustrated when they spend time on the website only to see that they can’t access a particular page.

What about Google?

It’ll see that your website isn’t correct and shouldn’t be ranked high.

For this reason, it’s crucial to prepare a 301 redirect map already when working on the website development. Thanks to it, you’ll be able to implement it right away when the site is active.

How to collect links from both the dev and old website versions?

For this purpose, you can use Google Search Console (if your page is small and has less than 1000 links), or Screaming Frog, OnCrawl, SiteBulb, and similar (which allows you to crawl the website and export data to Excel).

Apart from those, you can also use the sitemap (if it’s been configured properly) or ask your web developer for help (it’s possible to export all the links from the CMS database).

To make your job easier, categorize spreadsheets by language versions. This will help you organize your work and will make it less chaotic.

Step 2 – Automate Whatever Can Be Automated

Since this case study was rather unusual and involved numerous modifications, the lion’s share of the work had to be performed manually.

However, it doesn’t mean that it was impossible to automate certain elements.

For this purpose, I analyzed which pages didn’t require substantial modifications and I separated them from the remaining URLs that had to be included in the 301 redirect map (redirects from this map had to be implemented manually by the developer).

When looking at the URLs from the dev version, I observed that product ID numbers were removed from all addresses.

Then, I decided to check how many links this would be the only modification for. I prepared a list of these URLs (they can be managed in bulk).

And I prepared a list of the remaining URLs that have to be managed manually.

Does it mean that you should check link by link manually?

Of course not. For this purpose, you can use Excel and Screaming Frog. Let’s discuss the process step-by-step.

Remove URL Elements That Won’t Be Used in New Addresses

So, you have the list of URLs from the old website version and firstly you need to remove product ID numbers.

Instead of doing it manually, you can benefit from Excel formulas and complete the task with a few clicks.

How to do it?

Select all the cells with links and use the “split text to columns” function as shown in the screenshot below.

Choose “/” as the separator.

This will allow you to separate all the elements that appear in the URL after the root domain.

When you use the formula, the links will be separated after each “/”.

As shown in the screenshot below, in some cases, product IDs weren’t placed directly after the root domain, but at the end of the URL:

So, what should you do next?

ID numbers can be easily filtered out and removed. Sort your list by links with numbers in the last cell (the links are already separated thanks to the formula described above).

Mark these numbers and delete them.

There are still some ID numbers left that are connected with the product name in the URL by a hyphen.

In order to delete them, copy the entire URL that contains them to a new column (this way you won’t accidentally delete anything important).

Then apply the “split text to columns” function on the copied part of the URL.

This time set “-” as the separator. This is what you’ll get:

Now you have to manually go through these URLs and remove the ID numbers.

In this case, you can’t filter links with ID numbers and display them at the top.

Why?

Because it can mess up the order and relationship between original and copied URLs.

And this relationship and order will be important in the next step.

Create New Links for Your 301 Redirect Map

At this stage, you have already removed all the product ID numbers from the old URLs.

Although it could have been done manually, using the above formulas has significantly shortened the process.

Now you need to connect the separated links again, so you can check if they’re modified in any other way.

For this, you can use the TEXTJOIN formula:

Choose “-” as a delimiter, it’ll separate words.

Then select “1” because empty elements should be ignored. Choose the columns that should be included and apply the formula to all links.

At this stage, your original links after removing the ID number have 3 parts:

To check them in the tool you have to use TEXTJOIN again and connect them together.

However, before you do that, copy fragments of the links you want to join to the cache, remove them, and paste them again to have specific data in the cells.

Then, you can continue connecting link elements. Use TEXTJOIN again. This time you should set “/” as a delimiter to connect the sections you had to separate to remove the ID numbers.

Don’t add “https://” to the link at this stage, because to do this fully automatically you need to take 2 separate steps.

Apply the formula to all links.

Next, add the “https://” to all connected URLs.

How to do it?

Select the entire column and use the “Find and Replace” function. Type “www.” in “find” and “https://www.” in “replace with”:

After this step, you can get rid of the remains of the separating process.

And voila. You’ve prepared “new” URLs with removed product ID numbers.

This process can also be applied to other bulk URL changes if a given website migration requires them.

In the screenshot below, you can see new URLs compared with the old ones:

If this was the only modification, you would have your 301 redirect map ready to send for implementation to developers.

However, as I’ve already mentioned, this case was much more complex.

Step 3 – Find Redirect Map Elements That Require Manual Verification

Once you’re done with the bulk modifications, you can move on to other procedures.

Of course, this process starts with preparing a list of URLs that require non-standard modifications. This can be done with the use of Screaming Frog.

This time, you don’t want to crawl the entire website, so it’s crucial to change the mode from the “spider” to “list”:

Then, you upload the previously prepared list of URLs to the tool.

Copy them from Excel, click “upload” and paste:

Then, click “start”.

“Response codes” are elements you should pay particular attention to:

As you can see, most of the URLs have correct response codes, which means they’re fine, and can be used in the redirect map.

On the other hand, URLs with “client error” require further action.

Don’t forget to export this data to Excel:

Then, create a new tab “response codes”  in the file you’ve used at the beginning to prepare a list of URLs.

Now, it’s time to take a closer look at URLs that generate errors.

The order of links pasted from Screaming Frog won’t be the same as the order of links prepared in Excel earlier.

To make things easier and organize the data, you need to use the VLOOKUP formula.

Select an empty column, and enter the formula. Choose your URL (from the list of previously prepared addresses), then select the range (all links from Screaming Frog) and 0 (not sorted).

This way, you’ll get a combination of the original links from Excel with the assigned codes generated by Screaming Frog.

Manually Verify Links with 404 Error to Find New Redirect URLs

Combining the data from Screaming Frog and the previously prepared list of old and new URLs will help you decide which URLs are correct, and only require removing product IDs.

In their case, it’s enough to set redirects from the old address to the new URL you’ve prepared.

However, for URLs with 404 errors, you need to implement further changes.

Unfortunately, it’s not possible to automate this step.

If you have URLs generating 404 errors, you have to check them manually, see what can be found at the old address, and find a corresponding URL leading to the new website version.

Prepare a list of these links (and redirects) in Excel. Once the file is ready, you can send it to your developer for implementation.

This will allow you to prevent situations when your new website generates 404 errors once it’s launched.

Step 4 – Include Hreflangs in Your Redirect Map

At this stage, you have a ready redirect map for one main language version of your new website.

Now it’s time to create hreflangs for the remaining languages. Apart from collecting old and new URLs, you also need a map of old and new hreflangs.

How to create it?

Don’t worry! Screaming Frog once again comes to the rescue.

Crawl your page and paste the appropriate data to a new tab in your Excel file.

Then, use the VLOOKUP formula again, to assign the right hreflangs to correct URLs.

In my case, the formula looked like this:

This way, you’ve found URLs corresponding to the URLs on a different language version (in my case it was German).

Then, apply the formula to all links to find pages in a given language corresponding to pages in your main language.

Then, use VLOOKUP again. This time, you’ll need it to find links from the new developer website version.

You have to apply the same formula, but you should select the next row and the next sheet to work on the new versions of the links.

This way, you have a full redirect map that includes old links in your main language version, new links in your main language version, old links corresponding to specific language versions (in my case it was German), and now links corresponding to specific language versions.

In the next step, you can continue using the VLOOKUP formula to prepare similar URL pairs for subsequent language versions of your website.

Prepared this way, your URL file can be safely sent to developers that will implement necessary redirects.

Step 5 – It’s Better to Be Safe than Sorry – Test Your 301 Redirect Map Once the Website Goes Live

Keep in mind that even a flawlessly prepared redirect map may not work correctly. Therefore, make sure that everything is fine once you send the file to the developer, and your new website version is live.

Of course, you don’t have to do it manually. Use Screaming Frog to paste the links and check the response codes they generate.

Since your goal was to set 301 redirects, the effects of this verification in Screaming Frog should look in the following way:

How to Do 301 Redirect Map – What You Need to Remember

Although the described process is time-consuming and tedious, it’s worth the effort.

Remember that you don’t have to perform all the steps of creating a 301 redirect map manually. Some activities can be automated, however, there is no one-fits-all solution and every case should be considered individually.

If you’re planning a website migration, be sure to take into account the potential aftermaths of URL modifications. Think about elements that should be changed, analyze what can be done collectively, and what requires manual verification.

Don’t forget that the process is complex and there are many things that can go wrong. Although there are tips and guides (like our entry) that can help you, each website should be analyzed and approached individually.

If you don’t have appropriate experience and expertise, it’s better to outsource the task to specialists. Contact us and we’ll create a comprehensive 301 redirect map for you.