Automate Content Decay Detection: A Python Pipeline That Separates Real Decline From Seasonality in Search Console

Most content decay is invisible in the report everyone actually looks at. You open Search Console, glance at the site-wide clicks line, see it drifting sideways or gently up, and move on. Meanwhile a few hundred of your best-performing pages are quietly bleeding — losing 3–5% of their clicks every month — and the loss is being masked by a handful of newer pages that are still growing. The aggregate line is a weighted average of two opposite forces, and averages are exactly where problems go to hide.

The reflexive fix is to sort pages by “clicks last 28 days vs. previous 28 days” and refresh whatever dropped. That surfaces noise, not decay. A page can fall 40% month-over-month because the query it ranks for is seasonal, because a competitor ran a temporary campaign, or because one viral week finally reverted to baseline. None of those are decay, and refreshing them wastes the scarcest resource you have: writer and editor hours. This article builds a pipeline that does the harder, more useful thing — it separates structural decline from seasonality and volatility, and turns the survivors into a ranked refresh queue you can actually work through.

Why “traffic is down” is the wrong unit of analysis

Content decay is a per-URL phenomenon, so it has to be measured per URL. When you aggregate first, you destroy the signal before you ever see it. Consider a 2,000-page site where 1,700 pages are flat, 200 are decaying at 4% a month, and 100 new pages are ramping fast. The site total can rise 6% year-over-year while a fifth of your library rots. If you only watch the total, you’ll congratulate yourself right up until the decaying cohort crosses some threshold and the aggregate finally rolls over — at which point you’ve lost a year of compounding and the pages are far harder to revive.

The correct unit is a monthly clicks time series for every URL, over the longest window Search Console will give you (16 months). Sixteen months is deliberate: you need at least two observations of the same calendar month to reason about seasonality at all. Anything shorter and you cannot tell a page that dies every January from a page that is genuinely dying.

Three things that look identical on a line chart

Before writing any code, be clear about what you’re separating, because a naive slope will conflate all three:

Decay

A persistent, roughly monotonic decline in clicks that is not explained by the calendar. The page is losing ground it will not get back on its own — usually because the SERP moved on, the content aged out of relevance, freshness signals faded, or a competitor simply did the topic better. This is what you want to catch.

Seasonality

A repeating annual pattern. “Best tax software” cratering in May is not decay; it’s April doing what April does. A decay model that flags every de-seasonalized dip will bury you in false positives during every off-season.

Volatility

High month-to-month variance with no persistent direction. Small pages and long-tail queries bounce around because a few impressions swing the whole series. A single 40% drop means nothing here; you need a trend that holds across many months to call it decay.

Pulling the per-URL series from the Search Console API

Start by pulling monthly clicks by page. The Search Analytics API caps a single query at 25,000 rows, so for larger sites paginate with startRow and loop month by month rather than requesting one enormous range.

from googleapiclient.discovery import build
from google.oauth2.credentials import Credentials
import pandas as pd
from dateutil.relativedelta import relativedelta
import datetime as dt

svc = build("searchconsole", "v1", credentials=Credentials.from_authorized_user_file("token.json"))
SITE = "https://example.com/"

def month_rows(start, end):
    rows, start_row = [], 0
    while True:
        resp = svc.searchanalytics().query(siteUrl=SITE, body={
            "startDate": start, "endDate": end,
            "dimensions": ["page"],
            "rowLimit": 25000, "startRow": start_row,
        }).execute()
        batch = resp.get("rows", [])
        rows += batch
        if len(batch) < 25000:
            break
        start_row += 25000
    return [{"page": r["keys"][0], "clicks": r["clicks"]} for r in rows]

frames = []
first = dt.date.today().replace(day=1) - relativedelta(months=16)
for i in range(16):
    m0 = first + relativedelta(months=i)
    m1 = m0 + relativedelta(months=1) - dt.timedelta(days=1)
    df = pd.DataFrame(month_rows(m0.isoformat(), m1.isoformat()))
    df["month"] = m0.isoformat()
    frames.append(df)

wide = (pd.concat(frames)
        .pivot_table(index="page", columns="month", values="clicks", fill_value=0)
        .sort_index(axis=1))

You now have one row per URL and 16 columns of monthly clicks. Filter aggressively before you analyze: drop any page whose median monthly clicks over the window is below, say, 20. Decay analysis on pages that never had meaningful traffic is just volatility with extra steps, and those pages belong in an opportunity pipeline, not a decay one.

Separating trend from season with a rank-based test

The instinct is to fit a linear regression and read the slope. Don't — ordinary least squares assumes normal residuals and gets yanked around by the one viral month every real series has. Use the Mann–Kendall trend test instead. It's non-parametric: it only looks at the sign of every pairwise change, so a single outlier can't dominate it, and it returns a p-value that tells you whether the downward tendency is statistically real or just noise. Pair it with a Theil–Sen slope (the median of all pairwise slopes) for a robust magnitude.

Handle seasonality by comparing like calendar months rather than adjacent ones. The cleanest robust signal is a year-over-year ratio on matched months: the last available month against the same month a year earlier, plus the trailing quarter against its year-earlier quarter. If both YoY comparisons are down and Mann–Kendall is significant, seasonality is not your explanation.

import numpy as np
from scipy.stats import kendalltau

def decay_signal(series):
    y = series.values.astype(float)
    n = len(y)
    t = np.arange(n)

    # Mann-Kendall via Kendall's tau on (time, clicks)
    tau, p = kendalltau(t, y)

    # Theil-Sen robust slope (clicks lost per month)
    slopes = [(y[j] - y[i]) / (j - i)
              for i in range(n) for j in range(i + 1, n)]
    ts_slope = np.median(slopes)

    # Year-over-year on matched months (needs >= 13 points)
    yoy_month = (y[-1] + 1) / (y[-13] + 1) if n >= 13 else np.nan
    yoy_qtr = (y[-3:].sum() + 1) / (y[-15:-12].sum() + 1) if n >= 15 else np.nan

    return dict(tau=tau, p=p, ts_slope=ts_slope,
                yoy_month=yoy_month, yoy_qtr=yoy_qtr,
                median_clicks=np.median(y))

results = wide.apply(decay_signal, axis=1, result_type="expand")

The +1 smoothing on the YoY ratios keeps a month that briefly hit zero from producing an infinite or undefined result. It's a small thing that saves you a lot of NaN-chasing.

Turning signals into a ranked refresh queue

A boolean "decaying / not decaying" flag is the wrong output. You have finite refresh capacity, so you want an ordering that puts the page where a fix recovers the most traffic at the top. Combine three factors: how certain the decline is (Mann–Kendall confidence), how much traffic is being lost in absolute terms (Theil–Sen slope times the window), and how salvageable the page looks. Weight by absolute lost clicks, not percentages — a page shedding 8% of 4,000 clicks matters far more than one shedding 50% of 60.

def decay_score(r):
    if not (r.tau < 0 and r.p < 0.05):        # real, significant decline only
        return 0.0
    if not (r.yoy_month < 0.9 or r.yoy_qtr < 0.9):  # not just seasonality
        return 0.0
    confidence = 1 - r.p                        # 0..1
    lost_per_month = max(0.0, -r.ts_slope)      # clicks/month being lost
    projected_annual_loss = lost_per_month * 12
    return round(confidence * projected_annual_loss, 1)

results["decay_score"] = results.apply(decay_score, axis=1)
queue = (results[results.decay_score > 0]
         .sort_values("decay_score", ascending=False))
queue.to_csv("refresh_queue.csv")

On a real mid-size site this typically collapses a "3,000 URLs, everything's fine" export into a 40–80 page shortlist ranked by projected annual clicks at risk. That's a queue an editor can actually clear in a quarter. Feed the top of it straight into whatever refresh workflow you already run — if the drop is concentrated in CTR rather than position, the fix may be as cheap as a title and meta description rewrite rather than a full rewrite.

The pitfalls that produce false positives

Three failure modes will pollute your queue if you don't guard against them. First, cannibalization masquerading as decay: a page can lose clicks because you published a newer, better page that now outranks it for the same query. That isn't decay, it's consolidation working as intended, and the fix is a merge or a canonical, not a refresh. Cross-reference your queue against pages that share primary queries before acting. Second, URL changes and deindexing: a slug change or a noindex makes a series look like it fell off a cliff. Join against the current sitemap and index-coverage status so a "decay" of 100% doesn't just mean the URL no longer exists. Third, site-wide events: a core update or a migration drops everything at once. If your decaying cohort suddenly spikes on one specific month, you're looking at an anomaly, not a hundred independent decays — diagnose it at the site level first, because refreshing individual pages will do nothing about it.

Run this monthly, append each run to a small history table, and watch which pages keep reappearing. A page that shows up three months running is a genuine decay case; one that appears once and vanishes was volatility you correctly declined to chase. The whole point of the pipeline is not to find every dip — it's to spend your refresh hours only on the declines that are real, material, and fixable.

Frequently asked questions

How much Search Console history do I need before this works?

At least 13 monthly data points, and ideally the full 16 months the API exposes. Below 13 you cannot make a single year-over-year comparison, so you have no defensible way to rule out seasonality and the model will flag every off-season page as decaying. If your property is newer than that, wait — or pull the underlying data into BigQuery via the bulk export so you can accumulate a longer window than the 16-month API limit.

Why not just use a linear regression slope to detect decay?

Ordinary least squares assumes well-behaved, normally distributed residuals, and click series violate that constantly — one viral month or one holiday spike drags the fitted line and produces a slope that describes the outlier, not the trend. The Mann–Kendall test only uses the direction of pairwise changes, so it's robust to those outliers and additionally gives you a significance value, letting you distinguish a real trend from noise instead of trusting a slope that could be an artifact.

How is content decay detection different from anomaly detection?

Anomaly detection catches sudden, site-level breaks — a drop-off after a core update, a tracking outage, a migration gone wrong — usually within days. Content decay is the opposite failure mode: a slow, per-URL erosion that unfolds over months and is invisible in aggregate. You need both, but they answer different questions and run on different cadences: anomaly detection is a daily alarm, decay detection is a monthly triage.

Should I refresh every page the pipeline flags?

No. The score ranks pages by projected annual clicks at risk, so start at the top and stop where the expected recovery no longer justifies the editorial cost. Also screen the shortlist for cannibalization and deindexing first — a meaningful fraction of apparent "decay" is actually a consolidation or a URL change that a rewrite won't fix and might make worse.