Build a Content Audit Spreadsheet in an Afternoon
A content audit spreadsheet is a list of every post on your blog with a few key numbers and a decision for each: keep, update, merge, or remove. You can build one in an afternoon with a page export from Search Console, a column for notes, and a simple set of rules.
The point is to replace vague worry about old posts with a ranked to-do list. Once the sheet exists, you stop guessing which posts deserve your time.
Decide what the audit is for
Before opening a spreadsheet, write one sentence about the goal. Examples: find posts worth updating, find posts that overlap, or find posts with no value. The goal shapes which columns matter.
For most small blogs, the goal is to find updates that bring the most gain for the least work. That is the version described here.
Limit the first pass to what you can finish in one sitting. A rough audit that is done is more useful than a perfect one that never ends.
Gather the page list
Start with a list of all your posts. Most blogging platforms can export one, and SEO plugins or free site crawlers can produce a list with titles and URLs.
Put each post on its own row. Add columns for the title, the URL, the publish date, and the last updated date. These facts tell you how old each post is.
If your site has a sitemap, it can serve as a starting point too, though it may lack titles and dates.
Add performance data
Open the Performance report in Search Console, set the date range to the last three to six months, and view the Pages tab. Export the table, which gives clicks, impressions, click-through rate, and average position per URL.
Paste or merge those columns into your sheet by URL. Posts missing from the export had no impressions in that period, which is itself useful information.
If you use an analytics tool, add a column for visits or engaged sessions. Keep it to two or three metrics, since too many make the sheet hard to read.
Add your own columns
Beyond numbers, add columns that capture judgment:
- Main keyword: what the post is trying to rank for.
- Topic group: the subject the post belongs to.
- Accuracy: current, partly outdated, or outdated.
- Quality: strong, okay, or weak, based on a quick read.
- Overlap: the name of another post that covers the same ground, if any.
Fill these quickly. You are not grading each post at length, only marking what stands out. Sorting by topic group often reveals overlaps you had not noticed.
Assign a decision to each post
Apply a simple set of rules to give each post an action:
- Keep: performs well and is accurate. No work needed.
- Update: has impressions but a weak position, or is outdated but relevant.
- Merge: overlaps with another post on the same search.
- Remove or redirect: no traffic, no value, and no fit with the blog.
- Rewrite: the topic is right but the post is poor.
Write the decision in its own column. Do not agonize over borderline cases; mark them “update” and revisit.
A small worked example
Imagine a post with 4,000 impressions, a position of 14, and a note that two of its statistics are outdated. It gets “update,” and it goes near the top of the queue. A post with 12 impressions in six months, and a topic nobody searches, gets “remove or merge.” A post with strong traffic and current facts gets “keep.”
Doing this for fifty posts takes a couple of hours, and the result is an ordered list of what to do.
Prioritize and schedule
Sort by decision and then by impressions. Posts marked “update” with the highest impressions go first. Estimate the effort for each, such as light, medium, or heavy, and pick a few that give the best return for the time.
Put them on a calendar. A pace of one or two per week is realistic for most solo bloggers. Add a column for the date completed and the result.
Keep the sheet alive
An audit is not a one-time job. Update the sheet every quarter with fresh Search Console data, add new posts, and re-evaluate decisions. It becomes a living map of your content.
Over time the sheet also shows which kinds of posts perform best for you, and that insight feeds your future keyword plans.
Common audit mistakes
The first mistake is collecting too much data. Ten columns you never read are worse than five you use. The second is making decisions on a single metric, such as clicks, without looking at impressions and position. A post with many impressions and few clicks may need a better title, not removal.
The third is skipping the reading step. Numbers cannot tell you that a post is wrong or that it duplicates another. Spend a minute skimming each post that looks borderline.
Tools you already have
You do not need a paid platform for this. A spreadsheet, the Search Console export, and a free crawler cover almost everything. If your blog has more than a few hundred posts, you can filter by topic group or by date to audit it in sections over several weeks.
Share the sheet with your future self
Add a notes tab that explains your columns and decision rules. In three months you will not remember why you marked a post “merge.” A few lines of explanation make the next audit much faster.
What to do next
Block out three hours. Export your posts and Search Console page data, merge them into one sheet, and add the five judgment columns. Mark a decision on every row.
Then pick the three “update” posts with the most impressions, and schedule them this month. Save the file with the date in its name, so you can compare it with the next audit in three months.