Site migration issues happen. You plan, create a staging site, and then when the site goes live, there’s bound to be something wrong.
Quality assurance gets thrust into overdrive the moment that migrations are complete.
You sift through thousands of pages, metadata, and more to fix any problems before someone else notices.
It’s a lot of work and time-consuming to feel confident that a site migration is complete without issues.
But, I’m going to show you how to identify migration issues quickly using Google Sheets and AI. You still have a lot to do (migration experts, rejoice!), but this script is going to help you:
- Compare old and new ScreamingFrog crawls.
- Identify immediate issues that you need to resolve.
SEOs have their own strategies and practices that they follow, and this script is going to allow you to QA migrations quickly based on your own requirements.
You can adapt the script below to make this work for you, whether you’re working on a small local business site or an enterprise.
Setting Everything Up With Screaming Frog And Google Sheets
I’m using Screaming Frog for this example because it makes it easy for me to export data for both sites.
We’re going to assume the following:
- Your first version is your live website, which we’ll call the Old Crawl.
- Your second version is your new site on a staging environment, which we’ll call New Crawl.
You’re going to create a Google Sheets with the following Sheets:
- Overview.
- Old Crawl.
- New Crawl.
Once your Sheet is set up properly, run your ScreamingFrog scan using any settings that you like.
You’ll run the scan for your Old and New Crawl and then inmport the data to the Old Crawl and New Crawl tabs in your Sheets.
Your sheets will look something like this:
The New Crawl will look very similar.
Once you fill in both the New and Old Crawl sheets, you’ll need to populate your Overview sheet.
The table that you create in this sheet should contain the following columns:
- Existing (old) URL.
- New URL.
- Status Code.
- Indexability.
- Title 1.
- Meta Description 1.
- H1-1.
- H2-1.
- Column 3.
- Column 4.
Your Overview sheet will look something like this:
Migration QA Overview Sheet (Screenshot of Google Sheet, March 2025)Once you have your sheets set up, it’s time to put your favorite AI to work to compare your data.
I used ChatGPT, but you can use any AI you like. I’m sure Claude, Deepseek, or Gemini would do equally as well as long as you use similar prompts.
Prompts To Create Your Google Sheets Data
You can fill in your Google Sheet formulas by hand if you’re a formula guru, but it’s easier to let AI do it for you since we’re making basic comparisons.
Remember, the Old Crawl is the live site, and the New Crawl is my staging site.
Now, go to your AI tool and prompt it with the following:
I need a Google Sheets formula that compares values between two sheets: "Old Crawl" and "New Crawl." The formula should:
Look up a value in column A of "Old Crawl" using the value in column A of the current sheet.
Look up a value in column A of "New Crawl" using the value in column B of the current sheet.
Find the corresponding column in both sheets by matching the column header in row 1 with the current column header.
If the values match, return "Pass".
If they don't match, return "Error (old<>new)" with the differing values shown.
Use TEXTJOIN("<>", TRUE, ...) to format the error message.
Ensure compatibility with Google Sheets by specifying explicit ranges instead of full-column references.You can adjust these prompt points on your own.
For example, you can change “Old Crawl” to “Live Site,” but be sure that the sheet names match up properly.
ChatGPT generated code for me that looks something like this:
=IF(
INDEX('Old Crawl'!$A$1:$Z$1000, MATCH($A2, 'Old Crawl'!$A$1:$A$1000, 0), MATCH(C$1, 'Old Crawl'!$1:$1, 0)) =
INDEX('New Crawl'!$A$1:$Z$1000, MATCH($B2, 'New Crawl'!$A$1:$A$1000, 0), MATCH(C$1, 'New Crawl'!$1:$1, 0)),
"Pass",
"Error (" & TEXTJOIN("<>", TRUE,
IFERROR(INDEX('Old Crawl'!$A$1:$Z$1000, MATCH($A2, 'Old Crawl'!$A$1:$A$1000, 0), MATCH(C$1, 'Old Crawl'!$1:$1, 0)), ""),
IFERROR(INDEX('New Crawl'!$A$1:$Z$1000, MATCH($B2, 'New Crawl'!$A$1:$A$1000, 0), MATCH(C$1, 'New Crawl'!$1:$1, 0)), "")
) & ")"
)
You can use these basic formulas to start comparing rows by pasting the formula in row 2.
Adding the formula is as simple as double-clicking the field and pasting it in.
I know that you’ll want to make this a little more complex. You can do a lot of things with Google Sheets and formulas, so tweak things as needed.
Ideas For Expanding Your Migration Sheet
Your formulas will depend on the settings of your Screaming Frog crawl, but here are a few that I think will work well:
- Create a function to compare all of the status codes between the Old Crawl and New Crawl to identify key issues that exist. For example, if a page has anything but a 200 code, you can highlight the issue to fix it quickly.
- Add a formula to highlight metadata that is too long or short, so that you can add it to your task list for when the audit is over.
- Create a function to monitor Response Time between both the Old and New Crawl so that you can identify any issues that the new crawl may have or report speed increases if switching to a new host or server.
- Create another function to compare the URL structure of each URL. You might compare trailing slashes, structure and more.
- Develop a new function for Inlinks to be sure that no internal links were lost in the migration. You can also check external links using the same concept.
Migrating a site is always tedious.
A lot of QA goes into the process, and while necessary, the concept above will make the process much easier.
You can also use AI to recommend further enhancements to your newly migrated site.
How would you improve this file or its functionality?
More Resources:
Featured Image: TarikVision/Shutterstock
