How to Build a Content Audit Spreadsheet for Year-End Planning
A content audit spreadsheet template is one sheet that lists every URL on your site, pairs each page with traffic and conversion data, and ends with a single decision per page: keep, update, merge, or remove. Built in an afternoon, it turns your 2027 plan from guesswork into a ranked list of work.
In this article20 sections
This guide gives you the columns, formulas, and scoring model for a Google Sheets audit template. It also covers a 2026-specific trap. Google confirmed that a logging error inflated Search Console impressions from May 13, 2025 to April 27, 2026, while clicks were unaffected. If your audit leans on impressions from that window, your decay numbers will mislead you.
What Is a Content Audit Spreadsheet Template?#
A content audit spreadsheet template is a structured Google Sheets or Excel file with one row per URL and columns for identity, performance, freshness, and a recommended action. Its purpose is not to record everything about a page. It exists to force a decision on every page, so planning rests on evidence instead of hunches.
If you want the wider process around the sheet, the guide on what a content audit is and how to run one in 2026 covers goals and scope. This article focuses on the spreadsheet itself.
Why Run the Audit at Year-End#
Two practical reasons make November and December the right time.
Budgets and calendars get set now. An audit tells you how much of Q1 should go to updating existing pages versus publishing new ones.
Your data window is closing. Search Console keeps only the last 16 months of data, and the oldest day drops off daily. A year-over-year comparison needs about 13 months, so it still fits, but only if you export now. Save your exports in the spreadsheet’s raw-data tabs so next year’s audit has a baseline.
Step 1: Build the Content Inventory#
The content inventory method is simple: list every URL you own first, then attach data. Starting from analytics alone hides pages with zero traffic, and those are often where the clearest decisions sit.
- Collect URLs from two sources. Export your XML sitemap and run a crawl. The difference between them reveals orphaned or unindexed pages. Search Savvy’s roundup of content audit tools that speed up the crawl-and-merge stage compares options if you have not settled on one.
- Normalize URLs. Strip tracking parameters, trailing-slash differences, and http/https mismatches. Mismatched URLs are the most common reason lookups fail.
- Export Search Console data. In the Performance report, use the Pages view with the compare option: last 90 days against the same 90 days last year.
- Export GA4 data. Pull organic sessions and key events by landing page for the same period.
- Merge with lookups. In a new tab, put the master URL list in column A and pull each metric with =XLOOKUP(A2, GSC!A:A, GSC!B:B, 0).
- Keep zero-traffic rows. A page with no clicks is still a row that needs a decision.
Step 2: Add Traffic and Conversion Columns#
Traffic columns tell you whether a page is visible. Conversion columns tell you whether visibility matters. You need both, because a page with falling traffic and a strong conversion rate is worth far more attention than one with falling traffic that never converted.
| Column | Header | Source or formula |
| A | URL | Crawl or sitemap |
| B | Primary keyword | Your keyword map |
| C | Content type | Manual (guide, service page, product, post) |
| D | Published date | CMS |
| E | Last substantive update | CMS, only for real edits |
| F | Clicks, last 90 days | Search Console |
| G | Clicks, same 90 days last year | Search Console |
| H | Click change % | =IFERROR((F2-G2)/G2,””) |
| I | Organic sessions | GA4 |
| J | Key events | GA4 |
| K | Key event rate | =IFERROR(J2/I2,””) |
| L | Decay flag | =IF(AND(G2>=30,H2<=-0.3),”Decay”,””) |
| M | Impact (1 to 5) | Manual |
| N | Confidence (1 to 5) | Manual |
| O | Effort (1 to 5) | Manual |
| P | Priority score | =IFERROR((M2*N2)/O2,0) |
| Q | Action | Keep, Update, Merge, Redirect, Remove |
| R | Notes | Free text |
Freeze the header row, add a filter, and color-code column L. Resist adding more columns. Every extra field you have to fill by hand makes the audit slower and less likely to finish.
Step 3: Set Up Content Decay Tracking#
How do you track content decay in Google Sheets? Compare each URL’s organic clicks over the latest 90 days against the same 90 days a year earlier, then flag pages whose clicks fell by a set percentage and had enough traffic to matter. The year-over-year view removes seasonality, and a minimum click floor stops tiny pages from triggering false alarms.
The 30% drop and 30-click floor in the formula above are working thresholds, not industry standards. Adjust them to your site’s size.
Use clicks, not impressions#
For this audit cycle, base decay on clicks. Google’s own notice says only impressions and derived metrics such as CTR and average position were affected by the logging error, so click trends remain trustworthy. Impression-based comparisons that straddle May 2025 to April 2026 may show a drop that is partly a correction, not real decline. If you report impressions anyway, annotate the sheet with that date range.
Decay is not the only explanation#
A flagged page has lost clicks, but you still need to know why. Check the query-level data before deciding:
- Ranking loss: average position worsened for the main queries. This is classic decay.
- Demand loss: the topic is searched less. Updating will not bring that back.
- Result-page change: the query now shows an AI answer or other features that absorb clicks, even though your ranking held.
- Cannibalization: another page on your site started winning the same queries.
Only the first and fourth usually call for an update or merge. For a statistical approach to spotting decay earlier, see Search Savvy’s walkthrough on content decay analysis with predictive models.
Illustrative example#
These numbers are invented to show how the sheet behaves.
| URL | Clicks (now) | Clicks (last year) | Change | Key event rate | Flag |
| /guide-a | 120 | 310 | -61% | 3.2% | Decay |
| /guide-b | 48 | 52 | -8% | 0.4% | None |
| /service-c | 210 | 190 | +11% | 4.1% | None |
Guide A is the obvious first candidate: it lost most of its clicks and still converts well. Guide B is stable but barely converts, so its problem is intent or calls to action, not freshness.
Step 4: Apply Update Priority Scoring#
Update priority scoring ranks flagged pages so you work on the highest-return fixes first. The formula (Impact × Confidence) ÷ Effort rewards pages that matter commercially, where you understand the cause, and where the fix is cheap.
- Impact (1 to 5): business value of the page, based on key event rate, topic importance, and how many queries sit near the top of page one.
- Confidence (1 to 5): how sure you are that you know why it declined and what fixes it.
- Effort (1 to 5): hours required, from a quick factual refresh (1) to a full rewrite (5).
The same logic scales to broader work lists, as covered in this guide to prioritizing SEO tasks with limited time and budget.
Match the pattern to an action#
| What the row shows | Likely action |
| Clicks falling, strong key event rate | Update first |
| Stable clicks, weak key event rate | Rework intent match and calls to action |
| Two pages chasing the same query | Merge into one and 301 the weaker URL |
| No traffic, no links, no purpose | Remove or noindex |
| Healthy on both measures | Keep and schedule a review |
For updates, a fresh date is not enough. Revise what was wrong or missing: outdated steps, stale data, weak examples. Search Savvy’s content refresh strategy guide covers how to update without losing rankings.
For removals, be careful. Google’s guidance asks whether content is helpful, reliable, and people-first, and its spam policies target scaled content with little value for users. Bulk pages that exist only to catch keywords are legitimate removal candidates. A page with backlinks or conversions is a redirect candidate, not a deletion.
Step 5: Turn Scores Into a Year-End Plan#
Sort column P from highest to lowest and cut the list at your real capacity. If your team can handle twelve substantial updates per quarter, take the top twelve and defer the rest. Then assign owners, set review dates, and move the actions into your editorial calendar. A sheet that ends in a ranked, owned list gets used. One that ends in observations does not.
Common Mistakes to Avoid#
- Trusting 2025 to 2026 impressions. Use clicks and key events, and annotate any impression charts.
- Auditing only blog posts. Service, category, and product pages often carry more revenue.
- Merging without checking cannibalization. Confirm the overlap at query level, then redirect.
- Deleting pages that convert or hold links. Check both before any removal.
- Letting scoring become subjective. Define what a 1 and a 5 mean for each factor before you start.
- Skipping the data export. If you do not save the raw data, you lose the baseline.
The Bottom Line#
A good content audit spreadsheet template does one job: it ranks pages by the return you can expect from touching them. Build the inventory first, compare clicks year over year, add conversion data, and score every flagged page. If you would rather have another set of eyes on the findings, Search Savvy’s website audit services are a practical next step. Otherwise, start this week: export your data before the 16-month window moves again.
FAQ#
What columns should a content audit spreadsheet include?#
At minimum: URL, primary keyword, content type, last update date, clicks now and a year ago, click change, organic sessions, key events, a decay flag, a priority score, and a final action. Add more only if you will actually use them.
How often should I run a content audit?#
Once a year works for most small and mid-size sites, with a lighter quarterly decay check on your top pages. Larger sites may need continuous monitoring.
Can I build the audit in Google Sheets for free?#
Yes. Search Console and GA4 are free, and Sheets handles the lookups and formulas. Crawlers are the only part that may cost money, since free versions usually limit how many URLs they crawl.
Should I delete low-traffic pages?#
Not automatically. Check whether the page converts, has backlinks, serves a support role, or can be merged into a stronger page. Remove only what has no purpose.
How do I tell content decay from a normal traffic dip?#
Compare year over year to remove seasonality, then check whether rankings for the page’s main queries fell. If positions held but clicks dropped, the cause is likely demand or a changed results page, not decay.
How many pages should I update after an audit?#
As many as your team can finish well. Rank by priority score, take the top of the list that fits your quarterly capacity, and revisit the rest next cycle.
