You know that feeling when you have so much to do, you don't even know where to start? That was me last month, staring down a massive spreadsheet of 2,000 URLs. My goal was to fix any technical errors on these web pages, such as images that are too large (slowing down page load time) or any broken links. I was also looking to clean up the content: ensuring there weren't multiple pages with the same title, and checking that a meta description was added to the summary field where applicable. See our SEO on-page checklist for tips on what to look for when optimizing your pages.
As I was trekking my way down the list of pages, I began to wonder if there was a way to maximize my time on pages with the greatest return on investment (ROI). It was clear to me that pages bringing the most traffic to our site should be prioritized over pages that hadn't been viewed in months, but all I had was a list of URLs with errors.
Here's what I used to turn a potentially weeks-long project into a quick, strategic optimization:
Step 1: Gathering the Data
First, I headed to Google Search Console (GSC). The CALS/LAS web team has this set up on our websites to track performance in Google search.
I used this to tell me which URLs are getting the most impressions, or views in Google. In GSC,
- Select your site
- Set the time frame you're targeting
- Export a CSV with URLs, total impressions, and clicks
Pro Tip: GSC is like a search performance dashboard for your website. The impressions tell you how often your pages are showing up in search results, and what specific keywords visitors are searching to find you.
If you use the Sites+ platform, I can get this list for you upon request.
Step 2: Setting Up the Spreadsheet
I created a new Google Sheet with two tabs:
- Tab A: "URLs" - All the links needing updates from my original task
- Tab B: "Metrics" - URLs with their search performance data (the exported data from Step 1)
Step 3: The AI Magic
Here's the cool part. Finding the correct spreadsheet formula has never been a strength of mine. Instead of spending loads of time searching for someone in my same situation, I turned to AI for a ready-to-use merging formula. I've got two tabs, both with a column of URLS. I need to match up my error URLs with high trafficked URLs from the second tab. I stated, "I want a formula for my Google Sheet: if cell in column a matches cell in tab "metrics", then pull in column b."
AI gave me a formula to try right away.
=IFERROR(VLOOKUP(A2, 'Metrics'!A:B, 2, FALSE), "Not found")
Pasting this formula in cell B2 of my "URLs" tab should:
- Look up each URL in the metrics tab to find a match
- Pull in the corresponding impression data
- Mark cells it can't find a match for
Next, I double-clicked the corner of the cell to fill down the formula. Don't forget to do some spot-checking!
Step 4: Prioritization 
With the formula applied, I could now sort my URLs by impression count with the filter tool. This allowed me to focus my optimization efforts on pages that already get the most Google visibility.
- Click create a filter from the toolbar
- In the "impressions" column, Sort Z to A
I used the same method to create a "URL Clicks" column for each page. Then, I added columns to track my meta description and title updates, and I began making my changes.
Key Takeaway: Work smarter, not harder. Next time you're facing a daunting task, ask if AI may be able to help. With the help of your newest teammate and a bit of spreadsheet know-how, you can turn overwhelming tasks into strategic opportunities!