Content Audit: How to Audit Your Blog Posts in One Spreadsheet

A content audit is a review of every post on your blog, done with performance data, that ends with one decision per URL: keep it as is, refresh it, consolidate it into another post, or remove it. The output is a list of actions. A spreadsheet full of metrics with no decision column is an inventory, and an inventory alone changes nothing.

For a blog, the audit doesn't need a crawler subscription or a 40-column template. One row per post, about ten columns, and data you can pull from Google Search Console in under an hour will get you to a decision on every URL. The rest of this guide is that spreadsheet and the rules for filling in the last column.

What a Content Audit Is For (And What It Isn't)

The audit is the step that decides which posts get attention. The work itself happens elsewhere:

| Decision | What happens next | Where it's covered | | --- | --- | --- | | Keep | Nothing. The post is doing its job | — | | Refresh | Update facts, fill gaps, add internal links | Content refresh | | Consolidate | Merge into a stronger post and 301 the old URL | Content pruning | | Remove | Delete or noindex, after checking links | Content pruning | | Fix first | Solve an indexing or canonical problem before touching the text | See the indexing column below |

That last row is the one most audits skip. If a post lost traffic because Google stopped indexing it, no amount of rewriting will bring it back. The audit has to separate technical problems from content problems before it assigns any editing work. The diagnostic side of that, how to tell slow relevance loss from a sudden drop, is in the guide to content decay.

An audit also isn't a reason to change posts that are already performing. Google's own advice after a ranking drop is that a small slide, such as moving from position 2 to 4, doesn't call for drastic action, and that you should avoid changing content that already performs well. Most posts on a healthy blog should land in "Keep."

The Spreadsheet: One Row per Post

Here is the layout. Each column exists because a decision rule further down uses it. If a column doesn't feed a decision, leave it out.

| Column | Where it comes from | What it tells you | | --- | --- | --- | | URL | Your sitemap or CMS post list | The row key | | Target query | You, from memory or the post title | What the post is supposed to rank for | | Clicks (12 months) | Search Console → Performance → Pages | Whether anyone arrives from search | | Impressions (12 months) | Same export | Whether Google shows it at all | | Average position (last 28 days) | Same report, shorter range | How close it is to page one right now | | Click trend | Compare the last 3 months against the 3 before | Whether it's rising, flat, or sliding | | Indexing status | Search Console → URL Inspection or Page indexing | Whether Google has the page at all | | Internal links in | Your own count or a crawler | Whether the rest of your blog points at it | | Overlaps with | You, by reading titles side by side | Another post chasing the same query | | Decision | The rules in the next section | Keep, refresh, consolidate, remove, fix first |

Start the URL list from your own site, not from Search Console. Search Console's Performance report only lists pages that received impressions. A post Google never shows won't appear there, and those are exactly the posts an audit most needs to catch. Pull every post URL from your sitemap or CMS first, then match the Search Console numbers onto that list. Any post left with an empty row is a finding in itself.

Use two date ranges. Twelve months of clicks and impressions smooths out seasonality and tells you whether a post has demand. The last 28 days of average position tells you where it stands now. A post can have strong yearly totals and still be sliding today, which is what the trend column is for.

Mind the export limit. Search Console's own help page says exported report data is truncated to 1,000 rows, with Google Sheets, Excel, and CSV as the download formats. A blog with fewer than 1,000 posts fits under that at the page level. If yours doesn't, the Search Console API returns far more rows than the interface does.

Count incoming internal links honestly. A post with zero links from the rest of your blog is an orphan page, and an orphan with weak numbers often has a linking problem, not a writing problem. If you haven't checked your link structure recently, an internal linking audit covers the same posts from that angle and pairs well with this one.

How Do You Decide What Happens to Each Post?

Work through these rules in order and stop at the first one that matches. The order matters: technical checks come before content judgments, and evidence of demand comes before any decision to delete.

1. Not indexed, or indexed under a different canonical → Fix first. Check the URL Inspection tool. If Google reports the page as discovered or crawled but not indexed, or picked a different URL as canonical, the content decision waits. The common causes and fixes are in how to fix discovered and crawled but not indexed.
2. Overlaps with another post for the same query → Consolidate. Pick the post with more clicks, more backlinks, or the more complete answer as the survivor. Move anything unique from the weaker post into it and redirect the weaker URL. Two posts splitting one query is the pattern described in keyword cannibalization.
3. Average position 11–30 → Refresh. Google already considers the post relevant. It needs to become a more complete answer, plus two or three internal links from related posts.
4. Average position 1–10 with a low click-through rate → Refresh the title and description only. The body is earning the ranking. Change the snippet, not the content.
5. Steady or rising clicks at any position → Keep. Leave it alone, even if it's old.
6. Impressions present, position below 30 → Keep for now. These posts look like opportunities because the impression number is large, but a searcher rarely opens the result pages they sit on. Revisit them next audit.
7. No impressions for 12 months, no backlinks, no internal links → Remove. Nobody searches for it, Google doesn't show it, and nothing points at it. Check backlinks before you act. A post with even one referring domain worth keeping is a consolidation candidate, because a redirect can pass that link forward. A free way to check is in how to check backlinks for free.

Removal is the last rule on the list on purpose. Google's documentation on core updates calls deleting content "a last resort," to be considered only when the content can't be salvaged. On a small blog, most weak posts are better merged into something stronger than deleted outright.

A real example of rule 4 from this blog: in a recent Search Console pull, the query "backlink history checker" showed an average position of 3.3 with 47 impressions and no clicks. A post in that state doesn't need more words. It needs a title and description that make a searcher choose it over the results around it.

The Most Common Content Audit Mistakes

Sorting by publish date. Age says nothing about whether a post works. A three-year-old post holding position 4 should stay untouched, and a two-month-old post at position 18 may be your best refresh candidate. Sort by position and trend instead.

Treating every low performer as a rewrite. Rewrites are expensive and most low performers fall under a cheaper rule: a technical fix, a merge, a snippet change, or simply waiting.

Deleting in bulk. A batch of 404s on posts that had links throws away whatever those links were worth. Redirect what you remove when there's a sensible destination, and update the internal links that pointed at it so you don't create redirect chains.

Changing everything at once. If you refresh twelve posts, merge four, and delete six in the same week, you won't know which change moved which number. Batch by decision type and leave each batch alone long enough to measure.

Expecting fast results. Google says some changes can show an effect within days, but it can take several months for its systems to confirm that a site has improved. Judge refreshes after at least 28 days and site-wide changes over a longer window.

How Often Should You Run a Content Audit?

For most blogs, a full audit every three to six months is enough, with a lighter monthly check on your top posts by clicks. The full pass catches orphans, overlaps, and posts that never got indexed. The monthly check catches a sustained slide early, before a post falls from page one to page three.

Run the full audit sooner if something changes across the site: a migration, a URL structure change, a theme switch that altered internal links, or a broad traffic drop. In those cases, start with the indexing column.

Keep the spreadsheet after each audit. Next time, add a column for the previous decision and the date. A post you refreshed six months ago that hasn't moved is telling you something different from a post you've never touched, and the history is what lets you see it.

Making the Re-Check Easier

The audit itself is a one-afternoon job. The part that tends to slip is the follow-up: pulling the same numbers again a month later to see whether a refresh worked. Traffic Monitor connects to Search Console with read-only access and shows per-post clicks and impressions updated daily, so you can see whether a refreshed post is moving without rebuilding the export each time. You can sign in with Google to see where your posts stand before you start the spreadsheet.

The Short Version

List every post from your own site, not from Search Console. Add twelve months of clicks and impressions, the last 28 days of average position, indexing status, incoming internal links, and any overlapping post. Then give each row one decision, in this order: fix indexing first, consolidate overlaps, refresh posts at positions 11–30, rewrite snippets for page-one posts that don't get clicked, keep anything stable, and remove only what has no demand and no links. Make the changes in batches and measure each batch before starting the next.