Content Decay Detection with GSC + GA4: The Refresh Queue That Actually Moves Rankings
Most 'content refresh' programmes are vibes. Here's the data pipeline we use to detect decaying URLs, score them by recoverable traffic, and feed a refresh queue writers actually ship against.
Every content team we've worked with has a "refresh initiative" buried somewhere in a Notion doc. Almost none of them have a defensible way to pick what to refresh next. This is the pipeline we use to turn decay detection into a ranked queue writers can actually work through.
Why most refresh programmes fail
The default workflow looks like this: someone exports a GSC report, sorts by "clicks down year-over-year", and hands the top 20 URLs to an editor. Two months later, traffic hasn't moved and nobody can explain why.
The failure modes are predictable:
- Seasonal URLs look like decay. A tax calculator losing 60% of its traffic in July is not decaying. It's July.
- SERP volatility looks like decay. A URL that lost a featured snippet to a competitor needs a different fix than one losing to intent drift.
- Impression growth hides click decay. A page can gain impressions and still be rotting if CTR is collapsing because the SERP layout changed.
- Nobody scores recoverable upside. Refreshing a page that peaked at 40 clicks/month is a waste of an editor's afternoon.
A real decay detector needs to separate those signals, and a real refresh queue needs to rank by expected recovered traffic, not by raw loss.
The data model
We pull three sources into BigQuery (Snowflake or DuckDB work equally well) on a daily cadence:
- GSC bulk data export — the native BigQuery export Google rolled out in 2023. This gives you per-URL, per-query, per-day clicks, impressions, position, and CTR without API quota pain.
- GA4 BigQuery export — for engagement signals (engaged sessions, scroll depth proxy via events, conversions) per URL per day.
- A content registry — our own table listing URL, template type, publish date, last_edited_at, primary entity, word count, and author. This usually lives in the CMS.
The joins look like this:
CREATE OR REPLACE TABLE seo.url_daily AS
SELECT
gsc.url,
gsc.date,
SUM(gsc.clicks) AS clicks,
SUM(gsc.impressions) AS impressions,
SAFE_DIVIDE(SUM(gsc.clicks), SUM(gsc.impressions)) AS ctr,
AVG(gsc.position) AS avg_position,
ANY_VALUE(ga.engaged_sessions) AS engaged_sessions,
ANY_VALUE(reg.template) AS template,
ANY_VALUE(reg.last_edited_at) AS last_edited_at
FROM `project.searchconsole.searchdata_url_impression` gsc
LEFT JOIN seo.ga4_url_daily ga
ON ga.url = gsc.url AND ga.date = gsc.date
LEFT JOIN seo.content_registry reg
ON reg.url = gsc.url
WHERE gsc.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 540 DAY)
GROUP BY gsc.url, gsc.date;
540 days gives you enough runway to compute year-over-year with a trailing baseline. Anything less and seasonality will keep biting you.
Normalising for seasonality
Before we score decay, we deseasonalise. The cheap version: compute a 28-day trailing average and compare it to the same 28-day window from 365 days ago. The slightly better version: use STL decomposition (statsmodels has it, or BigQuery ML's ARIMA_PLUS returns seasonal components for free).
We group URLs by template first — a /compare/[a]-vs-[b]/ page has a very different seasonality profile from a /guide/ page — and compute the seasonal index per template, not per URL. Per-URL seasonality is too noisy unless the URL is a monster.
Detecting decay, not just loss
Here's the scoring function we settled on after a few iterations. For each URL, we compute four signals over the last 28 days versus a seasonality-adjusted baseline from the prior 90 days:
- Click delta — absolute change in daily clicks.
- Position delta — change in average weighted position (weight by impressions so a drop on a 10-impression query doesn't dominate).
- CTR delta at constant position — if position is stable but CTR fell, the SERP changed around you (new ads, new AI overview, new sitelinks competitor).
- Impression delta — if impressions are down while position is stable, demand changed or you lost query coverage.
We classify each URL into one of five states:
| State | Signal pattern | Typical cause |
|---|---|---|
| Healthy | clicks flat/up | nothing to do |
| Position decay | position down, impressions flat | competitors improved, link rot, stale content |
| SERP decay | position flat, CTR down | AI Overview, new SERP feature |
| Demand decay | impressions down, position flat | topic lost interest, keyword cannibalisation |
| Catastrophic | clicks down >60% in 14d | manual action, indexing issue, canonical drift |
The state is what tells the editor what kind of fix is needed. A SERP-decay URL doesn't need new paragraphs; it needs a title and meta rewrite plus schema work. A position-decay URL needs actual subject-matter updates and probably new internal links. Catastrophic needs an engineer, not a writer.
Scoring recoverable traffic
This is the part most teams skip. Loss alone is a terrible prioritisation signal because a URL that lost 500 clicks from a peak of 10,000 is probably not going back to 10,000 — the SERP moved on. We score expected recoverable clicks as:
recoverable = (baseline_clicks - current_clicks)
* recovery_factor[state]
* confidence[template]
Where recovery_factor is an empirical multiplier we calibrated from past refreshes:
- Position decay: 0.55 — you can usually claw back about half of what you lost on a good refresh
- SERP decay: 0.30 — harder, because the SERP structurally changed
- Demand decay: 0.10 — mostly unrecoverable, consider consolidating or deprecating
- Catastrophic: 0.80 — if the root cause is fixable, recovery is usually fast and large
confidence[template] is a template-level prior based on how predictable refreshes have been for that content type historically. Programmatic pages tend to score higher here than editorial essays, because the fix is more mechanical.
Sort the queue by recoverable desc, cap at whatever your weekly throughput is, done.
Wiring it into a workflow
A ranked CSV in BigQuery that nobody reads is not a refresh programme. We push the queue into whatever the content team already uses — usually Linear, Asana, or Jira — with a scheduled job that creates one ticket per URL per week, up to the team's capacity.
Each ticket includes:
- The URL and its decay state
- The three queries that lost the most clicks, with their current and baseline positions
- A diff of the SERP (top 10) from 90 days ago vs now, where we have it
- The expected recoverable click count (so the editor knows if this is a 2-hour job or a half-day)
- Whether an engineer is required (catastrophic state, or template-level issues flagged by more than N URLs in the same batch)
Closing the loop
The part we underinvested in for about a year: measuring whether refreshes actually worked. We now tag every refresh with a timestamp in the content registry, then run a 56-day post-refresh evaluation comparing the URL's trajectory against a matched control group (same template, similar baseline traffic, not refreshed in the window).
This matters for two reasons. First, it stops editors gaming the queue by picking easy URLs. Second, it lets us recalibrate the recovery_factor multipliers every quarter against actual outcomes rather than wishful thinking.
A few things that didn't work
- Using GA4 engagement as a decay signal on its own. Engagement metrics are too noisy at the URL level for most sites, and GA4's thresholding makes small-traffic URLs unusable. We use it as a tiebreaker, not a primary signal.
- LLM-generated refresh drafts without editor review. Quality collapsed, and we started seeing Helpful Content signals wobble on affected sections. If you use models in the loop, use them for research synthesis and outline work, not final prose.
- Refreshing everything on a fixed cadence ("every URL every 6 months"). This burns editor hours on healthy URLs and starves the actually-decaying ones.
Where we'd start
If you're setting this up from scratch, do it in this order: turn on the GSC BigQuery export today (it only backfills from the moment you enable it, so every day you wait is lost data), build the per-template seasonality baseline next, and only then start scoring decay states. Resist the urge to build the fancy recovery model in week one — a simple "position decay vs SERP decay vs demand decay" split, ranked by lost clicks, will already beat whatever your team is doing now. The calibration comes with reps.
If you want a hand wiring the pipeline into your stack, our data and SEO engineering team builds these as part of growth engagements.
Want a team like ours?
72Technologies builds production software for the kind of teams who actually read this blog.
Start a projectKeep reading
Query Clustering with Embeddings: Building the Topic Map That Killed Our Cannibalization
Keyword cannibalization on a 12k-page site isn't a content problem — it's a clustering problem. Here's the embeddings pipeline we built to merge, redirect, and consolidate pages that were fighting each other in Google.
Hreflang at Scale: The Sitemap-Driven Approach That Survived 40 Locales
Inline hreflang tags break once you hit a dozen locales. Here's the sitemap-driven setup we use on programmatic sites with 40+ locales, including the validation pipeline that catches drift before Google does.
Faceted Navigation Without the Index Bloat: A Rules Engine That Keeps Google Happy
Faceted navigation is where programmatic sites quietly burn crawl budget and dilute rankings. Here's the rules engine we use to decide which facet combinations deserve an index, a noindex, or a hard block.
