Content Audit Template: Search Console, GA4 and Crawl to Keep, Update, Merge or Prune (Google Sheets)

This is a content audit template that joins your crawl, Search Console and GA4 data per URL and suggests a decision for every page: keep, update, merge, prune or redirect, each with a plain-English reason. Every threshold sits on a Rules tab, so you can see exactly why a page got its label and change the rules to fit your site.

I built it because most content audits stall in the middle, between exporting three spreadsheets and agreeing what to do with each page. Here the joins and the first-pass decisions are done by formulas, and your judgement goes into an override column that always wins. Overlapping pages are found with Screaming Frog’s near-duplicate and semantic similarity scores.

Who it’s for

Content leads, SEO leads and agencies running an annual or quarterly audit on a site of up to 2,000 indexable pages, who want decisions they can explain to a stakeholder.

How to use it

1. Crawl the site with Screaming Frog (turn on near-duplicates and, if you can, embeddings) and paste the Internal HTML export into the Crawl tab.
2. Export the Pages report from Search Console for the last three months and for the three months before, and paste them into GSC current and GSC previous.
3. Download the GA4 landing page report for the same dates and paste it into the GA4 tab.
4. On Rules, check that every column header was found, then tune the thresholds.
5. Filter the Audit tab by decision, read each reason, add referring domains for pages you might remove, and override anything you disagree with.

An optional Python helper in the GitHub folder cleans the three exports, reports how well they join and fills a copy of the workbook for you.

What you get

• One row per URL with clicks, impressions, CTR, position, click change, sessions, engagement time and key events.
• Signals for decay, striking distance, thin content, overlap with a stronger page and conversions.
• A suggested decision and reason, plus your override and a final decision column.
• A dashboard with URLs, clicks and sessions per decision, and a count of pages that get clicks but were missing from the crawl.

Limits

The rules can’t see seasonality, legal pages or campaign links, and they only know about backlinks if you add referring domains. Read the reason before you remove anything, and redirect whenever a page has links or traffic. URLs are matched on their path, so audit one host at a time. Merge suggestions need Screaming Frog’s duplicate or embedding features. Pair it with the free on-page duplication analysis template for a closer look at overlapping pages.

Read my guide to blog and site architecture in the AI search era, and learn the ML methods behind it in the Introduction to Machine Learning for SEO course.