· Ben · process · 7 min
Find your decayed posts with 40 lines of Search Console API
Two searchanalytics.query calls, grouped by page, sorted by click delta. The script, the quota traps, and how to tell real decay from seasonality.
There's a post on your blog that used to bring in half your trial signups. It doesn't anymore. You found out by accident, three months late, while looking for something else.
The Search Console UI can show you this. Performance, set a date range, tick Compare, switch to the Pages tab, sort by click difference. It works, and it's the right first move. It also compares exactly one pair of periods at a time, tops out at 1,000 rows in the export, and lives in a browser tab that nobody opens on a Monday morning. What you actually want is a list of filenames, sorted worst first, produced by something that runs whether or not anyone remembers to look.
That's a script. It's about forty lines, it uses two API calls, and the hard part isn't the code.
Finding declining pages is two queries and a join
searchanalytics.query grouped by page, run once for the recent window and once for the window before it. Key both responses by URL, subtract, sort ascending on the delta.
import datetime as dt
from google.oauth2 import service_account
from googleapiclient.discovery import build
SITE = "sc-domain:example.com"
SCOPES = ["https://www.googleapis.com/auth/webmasters.readonly"]
creds = service_account.Credentials.from_service_account_file("sa.json", scopes=SCOPES)
gsc = build("searchconsole", "v1", credentials=creds)
def window(start, end):
rows, start_row = {}, 0
while True:
resp = gsc.searchanalytics().query(siteUrl=SITE, body={
"startDate": start,
"endDate": end,
"dimensions": ["page"],
"dataState": "final",
"rowLimit": 25000,
"startRow": start_row,
}).execute()
batch = resp.get("rows", [])
if not batch:
return rows
for r in batch:
rows[r["keys"][0]] = (r["clicks"], r["impressions"])
start_row += len(batch)
end = dt.date.today() - dt.timedelta(days=3)
now = window(str(end - dt.timedelta(days=28)), str(end))
before = window(str(end - dt.timedelta(days=56)), str(end - dt.timedelta(days=29)))
report = []
for url, (c0, i0) in before.items():
c1, i1 = now.get(url, (0, 0))
report.append({"url": url, "clicks": (c0, c1), "impr": (i0, i1), "delta": c1 - c0})
for r in sorted(report, key=lambda r: r["delta"])[:20]:
print(f"{r['delta']:+5d} {r['clicks'][0]:>4}->{r['clicks'][1]:<4}"
f" {r['impr'][0]:>6}->{r['impr'][1]:<6} {r['url']}")
Run it. You get twenty URLs and two numbers each. On a blog of a hundred posts it takes about four seconds and costs you two API calls.
Everything after this is about not trusting the output.
Group by page, or pay for it
Google publishes two quota types for Search Console: QPS and load. QPS is generous enough that a decay script will never see it: 1,200 queries per minute per site, 40,000 per minute per project. Load is the one that bites, measured in 10-minute and 1-day chunks, and Google says plainly which query shapes cost the most: grouping or filtering by page or query is expensive, and grouping by page and query together is the most expensive thing you can ask for. Load also grows with the length of the date range, and re-requesting the same data counts every time.
So the shape above is deliberate. One dimension. Two fixed windows. Two calls, cached to disk.
The version that gets you a quotaExceeded is the obvious-looking one: loop over dates, group by ["date", "page", "query"], so you can see which query decayed on which day for which URL. That's a hundred-plus of the most expensive request shape to answer a question you haven't earned yet. Get the list of suspects first. Query-level detail is a second script you run on five URLs, not a first script you run on four hundred.
startRow is why the pagination loop exists at all. rowLimit caps at 25,000 per request, default 1,000, and startRow is a zero-based offset. When the offset passes the end of the result set the API returns a successful response with no rows key, which is a clean loop terminator. Most blogs never paginate. Write the loop anyway, because the day you point this at a docs site with 8,000 URLs you won't remember that the default silently truncated you at a thousand.
dataState is why your last two weeks look like a crash
Leave dataState out and you get finalized data only. Set it to "all" and you get the fresh, still-settling last few days folded in. If your recent window includes unfinalized data and your comparison window doesn't, every page on your site looks like it's dying. That's not decay, that's Google still counting.
Same class of problem with dates. startDate and endDate are YYYY-MM-DD in Pacific Time, not UTC and not your laptop's timezone. If you're in Europe running a cron at 06:00 local, "today" in your script is a day that hasn't finished in Search Console's ledger. Hence the days=3 buffer on end above. Three days is conservative and costs you nothing, because you're comparing 28-day blocks.
One more default: type is "web", which is the combined tab and excludes Discover and Google News. If a post's traffic was Discover-shaped, this script will report it as flat while the actual thing that happened is invisible.
A click drop is not decay
Here's where every version of this script I've written has been wrong at least once. A negative delta is a signal. It is not a diagnosis, and it is not permission to rewrite the post.
Three gates before a URL is allowed on the list.
Gate one, the property baseline. If the whole site is down 20% because a core update landed or your PPC budget ran out, the raw sort will hand you forty innocent URLs. Compare each page to the site, not to zero:
factor = sum(c for c, _ in now.values()) / sum(c for c, _ in before.values())
for r in report:
r["excess"] = r["clicks"][1] - r["clicks"][0] * factor
Sort on excess and you get pages that fell further than the property did. That's a much shorter, much more honest list.
Gate two, clicks versus impressions. Two numbers, two different fixes. Impressions flat and clicks down means you still rank, and something about the result changed. Title, snippet, a SERP feature above you eating the click. Impressions down means visibility went, which is position or indexing. Google's own guidance on debugging traffic drops separates a small position drop (2 to 4: clicks fall, impressions barely move) from a large one (top 10 to 29), and explicitly warns against making radical changes to a page that's still performing. A title rewrite and a full content refresh are not interchangeable, and the impressions column is what tells you which one you need.
Gate three, a year-over-year window. Google lists seasonality alongside algorithmic updates and technical problems as a cause of drops, and recommends comparing against a similar period, including the same period last year, before concluding anything. Adding a third window() call for the same 28 days twelve months ago is two lines. If a post drops every August and recovers every September, you don't have decay, you have a calendar.
Write your own history, because Google forgets in 16 months
The Performance report keeps 16 months. Your blog is going to outlive that, and the interesting question about a post from 2024 is not "how is it doing this month" but "when did it start sliding".
So append. Every run, dump the rows to a JSON or CSV file in the repo and commit it. It's a tiny file, it diffs readably, and after a year it's the longitudinal record the API will never give you back. git log on that file is the history of your organic traffic, sitting next to the posts it describes, which is the same argument for reviewing content the way you review code applied to the analytics instead of the prose.
When forty lines stops being enough, the escape hatch is the bulk data export to BigQuery. Daily dumps of the full performance data with no row limits, into searchdata_site_impression and searchdata_url_impression. It covers everything except anonymized queries, and it's aimed at sites large enough that the API's row caps are a real constraint. If you're running a blog with a few hundred posts, you're not that site yet. Set up the export anyway if you're patient, because it also starts accumulating history the day you turn it on.
Put it on a cron and let it open the PR
0 6 * * 1 in a GitHub Action. Weekly is the right cadence. Daily noise on a 28-day window is meaningless, monthly is late.
The output is URLs. Your URLs map to slugs, slugs map to files, files map to diffs. That's the whole reason this is worth automating on a git-based blog and not on a CMS: the thing the script identifies is a path you can open a pull request against. Print the slug next to the delta and the queue writes itself.
For Contentcron projects, the Search Console integration reads exactly this signal and the decayed post comes back as a refresh: full research pass, rewritten MDX, PR on contentcron/<slug> with the old and new side by side in the diff. Refreshes count against your monthly article allowance because they're a complete pipeline run. PR-comment revisions don't.
It reads growth the same way it reads decay. The first eight weeks of Grepture's Search Console data is the same two queries with the sort reversed: 318 clicks and 43,627 impressions, impressions going from near zero in early May to roughly 1,800 a day by late June.
What this script can't tell you
It ranks decay. It doesn't explain it.
It can't see the competitor who published something better and more recent than you. It can't see the AI overview that now answers the query above your result. It can't see that the intent behind the phrase moved from "what is" to "best tool for", and your post is answering a question nobody's asking in those words anymore. It can't see that you deprecated the feature the post is about.
Anything that claims to infer that from a clicks table is guessing, and a confident guess is worse than a blank. The list is a queue, not a verdict. You still open the file, read what you wrote eighteen months ago, and decide whether it needs a title, a rewrite, a redirect, or a delete.
If your posts are markdown in a repo and nobody noticed the last four drops, copy the script and run it this week. If you'd rather the fix showed up as a pull request on Monday morning, the first article is free, no card required.