All articles
SEO & GrowthNovember 12, 2026 6 min read

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:

  1. 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.
  2. GA4 BigQuery export — for engagement signals (engaged sessions, scroll depth proxy via events, conversions) per URL per day.
  3. 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:

StateSignal patternTypical cause
Healthyclicks flat/upnothing to do
Position decayposition down, impressions flatcompetitors improved, link rot, stale content
SERP decayposition flat, CTR downAI Overview, new SERP feature
Demand decayimpressions down, position flattopic lost interest, keyword cannibalisation
Catastrophicclicks down >60% in 14dmanual 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.

#SEO#Analytics#GSC#GA4#Content Ops

Want a team like ours?

72Technologies builds production software for the kind of teams who actually read this blog.

Start a project