Find Decaying Content With Free Search Console Data in One Hour

Most teams compare this month with last month, see nothing alarming, and move on. That is the belief worth breaking. A month-on-month view cannot show decay, because seasonality and slow drift both look exactly like ordinary noise at that resolution. Line the same quarter up against the same quarter a year earlier and the damage becomes obvious. What you will build is one sheet: every page that has lost a meaningful share of its traffic, each with a cause written beside it.

Who this suits: any property holding twelve months or more of Search Console history, with enough pages that reading them all is not an option. What it costs: nothing, and roughly an hour. What to skip: paid decay monitoring, until the manual version has run once and you know what it would be watching.

Ahrefs names the problem plainly in its guide to finding and fixing content decay: a page slides downhill gradually, losing rankings and organic visits over a long stretch rather than falling off a cliff on a single day. The detection logic below follows that guide, implemented with nothing but Search Console and a spreadsheet.

Step 1: Export the same quarter twice, one year apart

Open your property and go to the performance view for search results. Switch the date control into comparison mode, then set the last three complete months against the identical three months from the previous year. Move to the view that lists pages, and export.

PROPERTY ....... [https://yoursite.com/]
RECENT WINDOW .. the last three complete months
OLDER WINDOW ... the same three months, twelve months earlier
BREAK DOWN BY .. page
TAKE IT AS ..... CSV or straight into a spreadsheet

What comes back:

• One row per address
• Clicks, impressions, click-through rate and average position, for both windows
• The delta between windows, pre-calculated by the comparison view

What to do next:

• Trim the final two or three days off the recent window. Google's documentation notes the freshest data can still be preliminary, which makes those days read artificially low.
• Match the two windows on length and on weekday composition, or you are comparing a different number of Mondays.
• Delete any page that did not exist twelve months ago. It cannot have decayed.
• Delete addresses carrying parameters or campaign tags, which split one page across several rows and hide the real total.

Causes stay unknown at this point. You are holding two photographs taken a year apart.

Step 2: Mark the pages that gave up a fifth of their clicks

Drop both exports into one sheet and calculate the proportional movement in clicks for every row. Then mark the losers. For a quarterly review the Ahrefs guide puts the line at a 20% fall against the same period last year, which is a sensible opening position and not a law of nature.

DELTA ...... ([Clicks recent] - [Clicks older]) / [Clicks older]
MARK IT .... when [Clicks older] clears [FLOOR]
             and [DELTA] is worse than [THRESHOLD]
ORDER BY ... clicks lost, largest first

What comes back:

• A shortlist of marked addresses, usually far shorter than the fear suggests
• Each loss expressed in absolute clicks as well as in proportion
• A sort order that puts the expensive losses at the top

What to do next:

• Set the floor high enough that a page falling from three clicks to two never reaches the list.
• Sort by clicks lost rather than by proportion. A quarter shaved off your strongest page matters more than a total collapse on something three people ever opened.
• Promote anything with commercial intent behind it, such as a page that feeds trials, ahead of general-interest pieces.
• Leave seasonal pages until you can compare their peak against last year's peak, not against an off-season quarter.

Real decay and a tracking fault look identical here. A broken tag, a site migration or a changed URL structure all produce the same shape.

Step 3: Put impressions and click-through rate side by side

Every marked page now gets two more columns: movement in impressions, and movement in click-through rate. Read on their own, neither says much. Read together they distinguish three entirely different problems, and the three demand different responses. Ahrefs lays the patterns out:

• Both falling: ordinary decay. Fewer people see it and fewer of those click.
• Impressions falling while click-through rate rises: rankings slipped, but whoever still finds the page wants it. Often recoverable.
• Impressions flat while click-through rate falls: your position held and the page around it did not. Something above you is absorbing the click: an AI answer, an expanded snippet, a carousel of results that did not exist last year.

ADDRESS ....... [URL]
IMPRESSIONS ... [recent] against [older], change
CLICK RATE .... [recent] against [older], change in points
POSITION ...... [recent] against [older]
READS AS ...... decay / slipped / the results page moved

What comes back:

• One pattern label per page, which is what determines the repair
• A position trend, which separates losing ground from holding ground and losing the click anyway

What to do next:

• Where the results page moved, rewrite nothing yet. Search the query in a clean window, look at what is taking the click, and decide whether a different angle or format could win that block instead.
• Where rankings slipped, read your page against whatever now outranks it, specifically for depth and for recency.
• Where both fell, schedule a genuine refresh.
• Rule out the boring causes first: a stray noindex, a redirect chain, a canonical pointing somewhere unexpected.

Which sections have gone stale is still unanswered. That takes a person reading the page.

Step 4: Tag the page section by section before anyone writes

The Ahrefs guide lays out the available responses: update it, consolidate it into something else, redirect it, prune it, or rewrite from scratch. Choose the smallest one that solves the diagnosed problem. Where the answer is an update, read the page one heading at a time and tag each heading before any writing starts.

HEADING ..... [the H2 as published]
TAG IT ...... hold / repair / delete / missing
BECAUSE ..... stale figure | too thin | wrong intent | question unanswered
EVIDENCE .... [URL of the newer source]

What comes back:

• Something a writer can finish inside an hour rather than a day, because the judgement happened before they opened it
• A written record of what changed, which is what makes the result readable later

What to do next:

• Hold every section that still matches what the searcher wanted. Rewriting those costs time and gains nothing.
• Repair dated figures, old screenshots and claims that have quietly expired, and date the new ones.
• Delete advice that is simply wrong now. Leaving it there is worse than having no section.
• Add answers to the questions appearing in that page's own query list, since those are requests you already receive.
• Merge or redirect pages competing with each other for the same query, rather than improving both.

Recovery is not guaranteed by any of this. Sometimes the subject itself lost its audience, and the correct response is to stop investing.

The whole playbook, and when to run it again

1. Export matching quarters a year apart, trimmed of preliminary days.
2. Mark the pages down sharply that still have enough clicks to matter.
3. Label each by its impressions and click-rate pattern.
4. Tag the sections, repair the minimum, and record the date.

Cadence: quarterly, with each repaired page re-read eight to twelve weeks later. Our piece on how long search results actually take to move covers why judging a refresh after a fortnight tells you nothing. Where you maintain a large library, keep this sheet beside your regular pass over where content work gets stuck.

Past a few hundred pages, the export runs out of road

Step 1 works beautifully until the library gets large, at which point the interface export stops being the right instrument. There are two ways past it, and they have different prices and very different urgency.

The first is to script the same two pulls through the Search Console API, and the published allowances make that comfortable. Google's usage limits page gives Search Analytics 1,200 queries per minute per site and the same per user, with the per-project ceiling at 40,000 per minute. A quarterly job sending a handful of paged requests is nowhere near those figures.

What the same page describes alongside the query counts is a load quota, measured in ten-minute windows for the short term and in one-day windows for the long term, where wider date ranges and finer grouping or filtering draw more of your allowance. A year-on-year pull is precisely the greedy shape that quota exists to meter: two long windows, grouped by page, sometimes by query as well. Request the windows as separate jobs, cache both to disk, and resist re-running them while you debug a formula.

The second route is Google's bulk data export into BigQuery, which has no row ceiling at all. Search Console charges nothing for it; you pay Google Cloud's storage and query rates above a free usage level, and the setup needs a Cloud project with billing enabled, the BigQuery and BigQuery Storage APIs switched on, and Google's export service account granted the BigQuery Job User and Data Editor roles. The first delivery arrives up to 48 hours after configuration, and changing the table schema afterwards causes the export to fail.

One line in that documentation decides the timing for this particular playbook: the export does not backfill. Nothing from before the day you configure it is ever delivered. A method built on comparing a quarter against the same quarter a year earlier therefore depends on a switch somebody has to flick twelve months in advance, and the team that flicks it today is the only team that will have the option next autumn.

Stop tuning the threshold. Set a click floor.

Teams spend a surprising amount of argument on the percentage. Should decay be a fifth lost, or a quarter, or a tenth. Our position is that the 20% line is close to irrelevant once step 2 is sorted by absolute clicks lost, because the sort order, not the cut-off, is what the reviewer actually reads. Move the threshold five points in either direction and the top of the list barely changes.

The knob that does matter is the floor, and the common error is setting it too low out of a wish to be thorough. A low floor fills the sheet with pages that collapsed from eleven clicks to four, every one of which reads as a crisis in percentage terms and none of which is worth a writer's afternoon. Pick the floor so the marked list comes out at roughly twenty rows, work those, and ignore the proportion entirely. A list nobody finishes is worse than a shorter list somebody does, because an unfinished list teaches the team that this exercise produces homework rather than recoveries.

The same logic says the highest-value action in this whole playbook is not running the loop. It is configuring the export that cannot backfill, which takes an afternoon and pays out a year later. Teams that skip it usually discover the gap at the exact moment they need a long comparison and find the history was never collected.

Where this call breaks down: if your clicks are concentrated, so that a handful of pages carry most of the traffic, then neither the floor nor the threshold is doing any work. Read those pages directly each quarter, against their own query lists, and skip the sheet.

Four situations where this reads wrong

• Thin history. Under twelve months of data means no matching window exists. Compare consecutive periods instead, and distrust the result.
• Infrastructure changes. A migration, a new domain or restructured addresses all imitate decay perfectly. Check redirects before rewriting a single sentence.
• Answers above the results. Some clicks are absorbed before anyone reaches the list of links at all. Rewriting may not bring those back, and the better question is whether the page can earn a mention inside the answer, which is the subject of reworking published pages for AI answers.
• Chasing small numbers. Every hour given to the tail of trivial declines is an hour taken from the ten pages your business actually runs on.