How to Create a Content Audit Spreadsheet

Key takeaways

Pull at least 16 months of Search Console data — that's the maximum window GSC retains — so decisions aren't based on a partial season of traffic.
Score every URL on three axes (performance, strategic relevance, content quality), not traffic alone, or you'll prune pages that are quietly earning backlinks.
End every row with an explicit decision and an owner — an audit without a decision column is just a spreadsheet nobody acts on.

What a Content Audit Spreadsheet Actually Needs to Track

Most templates people copy from a Google search have too many vanity columns (social shares, "readability score") and not enough of what actually drives a decision. Here's the column set that holds up after you've run a few of these:

URL and page title
Publish date and last-updated date
Word count
Primary target keyword and topic cluster
Organic clicks and impressions, trailing 12–16 months (from Search Console)
Average position for the target query
Referring domains (from Ahrefs, Semrush, or GSC's link report)
Internal links pointing to the page
Schema/structured data present (yes/no)
AI citation status — whether the page currently gets quoted by Perplexity, ChatGPT browsing, or Google's AI Overviews
Decision: Keep / Update / Merge / Prune
Owner and due date

That AI citation column is the one most templates skip, and it's becoming the more important one. A page can rank on page one of Google and still be invisible to generative engines because it doesn't answer the query in a self-contained, quotable paragraph. Tracking it separately from rank forces you to notice pages that need restructuring, not just a refresh.

Step-by-Step: Building the Spreadsheet

Get the full URL list. Pull it from your XML sitemap or crawl the site with a tool like Screaming Frog (the free tier handles up to 500 URLs, which covers most indie SaaS blogs). Don't trust your CMS's internal post count — orphaned or noindexed pages often don't show up there.
Export Search Console data. Go 16 months back, the longest window GSC stores, so seasonal SaaS content (e.g., "best tools for Q1 planning") isn't misjudged from a single low month.
Pull GA4 engagement data for the same window: average engagement time, scroll depth if you track it, and conversion events if pages are tagged.
Add qualitative columns by hand: does the page still reflect current pricing/features, is it citing outdated statistics, does it duplicate another page's angle.
Score and prioritize using the method below.
Assign a decision and an owner to every row before you close the sheet, even if the owner is "nobody, revisit next quarter."

How to Score Pages Without Just Looking at Traffic

Traffic-only audits prune pages that are doing real work. A page with 40 clicks a month might be the single biggest source of referring domains on the site, or it might be the page a competitor's comparison article links to. Score on three axes instead:

Performance — clicks, impressions, average position, trend direction over the 16-month window.
Relevance — does this still match a product you sell or a problem your current audience has. A 2023 post about a feature you've since killed scores low here regardless of traffic.
Quality — thin content, outdated facts, missing structure, no schema, high bounce rate relative to similar pages on the site.

A practical threshold that works for most SaaS blogs: pages with fewer than 10 organic clicks a month, published over 12 months ago, and off-topic for your current product get flagged as prune candidates automatically. Pages with high impressions but a conversion rate near zero are usually a title/meta problem, not a content problem — rewrite the snippet before you rewrite the page.

The Decision Column: Keep, Update, Merge, or Prune

Keep — performing and still accurate. No action needed this cycle.
Update — good topic, outdated or thin execution. Add current data, restructure for a direct-answer opening paragraph, check for missing structured data.
Merge — two or more pages competing for the same query. Combine into one, 301 the losers to the winner, and consolidate the backlinks instead of splitting link equity across near-duplicate URLs.
Prune — no traffic, no backlinks, no strategic fit. Either delete and 410, or redirect if it has any referring domains worth preserving.

The mechanism behind pruning matters more than people assume. Google's own guidance on site quality treats a large share of low-value, thin pages as a signal that drags down how the rest of the domain gets evaluated — it's not that one bad page tanks the site, it's that crawl and quality signals get diluted across a bloated index. Google's Search Central documentation covers how indexing and site quality interact in more detail than most audit guides bother to explain.

What Breaks When You Try to Automate This

We build the AI SEO agent behind Seolyn, so we've watched this go wrong in a specific, repeatable way: founders let an AI agent pull the data (fine, it's good at that) and then let it make the prune/keep decision unsupervised (not fine). The model has no idea that a near-zero-traffic page is the one your biggest enterprise lead found via a Slack community link last quarter. Traffic and backlink data tell you what happened; they don't tell you why a page exists.

The second failure mode is duplicate content masquerading as "updates." When teams batch-regenerate old posts with AI to boost word count, they sometimes end up with three pages that are 70% identical in structure and claims, which both confuses search engines about which page to rank and makes your "merge" decisions harder to make later. Running a quick duplicate/AI-content check — something like Originality.ai or Copyleaks — before you commit to "update" vs "merge" saves a round trip.

The safe automation boundary: let AI pull and normalize the data, draft the scoring, and flag outliers. A human (or at minimum a second review pass) makes the final keep/prune call on anything with existing backlinks or revenue attribution.

After the Audit: Turning Decisions Into Work

An audit that ends at the spreadsheet accomplishes nothing. Every "update" row needs to turn into a brief someone can execute — even if that someone is a freelancer you've never worked with before. The brief should carry over the target keyword, the current ranking position, the specific gaps found in the audit (missing current data, no direct-answer opening, no schema), and the internal links that should point to the page. If you're outsourcing the actual rewrite, a structured content brief for freelance writers is what keeps the audit's findings from getting lost between the spreadsheet and the draft.

For pages flagged with missing images alt text or poor image optimization during the quality pass, that's a narrow enough fix to batch separately — see the specific checks in optimizing images for AI search rather than folding it into a full content rewrite.

Common Mistakes

Auditing only blog posts. Docs, landing pages, and pricing pages carry just as much SEO weight and are usually more neglected because nobody "owns" them editorially.
Treating the audit as one-time. Run it quarterly for fast-moving SaaS content, or trigger one after any major product rename, pricing change, or a Google core update you suspect affected you.
No AI citation tracking. Checking rank in Google alone misses the growing share of queries answered directly inside Perplexity or an AI Overview without a click.
Skipping the decision column entirely. A list of 200 URLs with traffic numbers and no verdict just becomes a dashboard nobody opens again.

Frequently Asked Questions

Q: How often should I run a content audit?

Quarterly for an active SaaS blog, or immediately after a major product change, pricing update, or a noticeable organic traffic drop. Waiting a full year usually means you're cleaning up 12 months of compounding duplicate content at once.

Q: Do I need Google Analytics, or is Search Console data enough on its own?

Search Console tells you what queries and clicks a page earns; GA4 tells you what happens after someone lands, like engagement time and conversions. Use GSC as the primary data source and GA4 to validate whether high-traffic pages are actually converting.

Q: Should landing pages and documentation be included in the audit, not just blog posts?

Yes. Pricing pages, feature pages, and API docs accumulate the same kind of staleness as blog posts but rarely get reviewed because they're not treated as "content." They should get the same URL/performance/decision row.

Q: What should I use to build the spreadsheet — Google Sheets, Airtable, or a dedicated tool?

Google Sheets or Airtable both work fine up to a few hundred URLs; the format matters far less than whether every row ends with a decision and an owner. Dedicated audit tools mostly add convenience for exporting data, not better decision-making.

Q: How many clicks per month is "low enough" to consider pruning a page?

There's no universal number, but under 10 organic clicks a month over a 12-month window, combined with zero referring domains and no topical fit to your current product, is a reasonable starting threshold for most small SaaS blogs.