How to Monitor SEO Optimizations with Screaming Frog & Google Sheets

Learn how to setup monitoring to ensure website optimizations don’t drop from your website.

I work with many eCommerce websites with hundreds of URLs to optimize, which can be a lot to track.

The other day, while checking one of the sites, I realized the optimized page titles and meta descriptions had been reverted back to defaults on close to 100 of the core URLs. Obviously, a big deal and not something I was happy to find.

So out of the mess, I created a quick method to track optimizations to make it easy to identify when titles, meta descriptions and H1s on a website do not match the optimizations.

Here is a quick look:

The sheet will track any URL you want to optimize, including the character count of titles and descriptions and will show when optimizations don’t match the data on the site.

The process to check your URLs and verify against your optimizations happens in 3 steps:

  1. Optimize URLs & upload to the website
  2. Crawl optimized URLs with Screaming Frog
  3. Import crawled data into the verification sheet

To run the verification, it will take less than 5 minutes for hundreds of URLs and beats manually trying to match back to your optimizations.

You only need 2 tools to get started:

  1. Download the free version of Screaming Frog (the paid version works too, of course)
  2. Make a copy of the SEO Optimization Verification sheet I created in a Google Sheets (File > Make a Copy…)

Now that you have the tools, let’s run through the process.

The next steps assumes you have a list of URLs you need to optimize or have already optimized…

Optimize URLs in the SEO Optimization Verification Template

Open the SEO Optimization Verification sheet in Google sheets and click on the Optimized vs Crawled tab.

There are 4 columns you need to fill in to add the optimizations, starting with the URLs to be optimized in column A (highlighted in yellow with an example filled out):

  1. URL
  2. Optimized Title
  3. Optimized Description
  4. Optimized H1

Once you have the URLs loaded, work your SEO magic and come up with an appropriate title, description and H1 for each URL.

Crawl your optimized URLs with Screaming Frog

Open Screaming Frog (this will work with the free version if you are crawling 500 or less URLs).

From your optimization sheet, highlight the URL column and copy:

In Screaming Frog, select Mode > List then select Upload > Paste:

Click OK when the pasted URLs appear - then click Start.

Let Screaming Frog do its magic and crawl the URLs. Once complete, from the Internal tab, select the HTML filter and click Export. Save the file to your desktop.

Import the crawled URLs into the Google Sheet

Go back to the SEO Optimization Verification sheet.

Click on the tab Crawled URLs.

Select File > Import

Find the location of your saved Screaming Frog crawl on your desktop.

In the window that appears, Select ‘Replace current sheet’ then click Import Data and the crawled URLs will replace the data in the Crawled URLs tab.

Go back to the Optimized vs Crawled tab, the sheet is designed to automatically match the URLs from the imported sheet with your optimized URLS. Notice the actual title, description and H1 should appear in the sheet. The ‘match’ columns will be green if there is a match and red if there is not match.

That’s it! Now you can keep adding new URLs to be optimized, rerun the crawl and add to the sheet to verify your optimizations are accurately showing on the website.

I recommend getting this into your weekly or monthly routine to make sure optimizations stay in tact.

Hit me up with any questions or if you have any improvements to the sheet.

Happy Optimizing!