Content Decay Analysis Template

Content SEO Toolkit, template 3 of 6

Content Decay Analysis Template

Separate lost impressions, lower CTR and falling demand, so you refresh the pages that really decayed.

Whether a page lost clicks because it lost rankings, lost CTR or the market lost interest, and how big the demand-adjusted gap is.

  • Excel workbook (.xlsx), opens in Google Sheets
  • PDF field guide, 9 to 11 pages
  • Blank and worked CSV templates
  • DataForSEO import script

Free, no email required. Version 3.0, updated November 2026.

Content Decay Analysis Template.xlsx
M13fx=IF(I13="","",IF(I13<>"Ready","n.a.",E13-L13))

Start HereDashboardWorkspaceDelivery QAField Guide

The formula above is the real one from cell M13. Scroll it sideways to read the whole rule.

Connected to the DataForSEO API

This template is designed around one DataForSEO endpoint. Pull the data, convert it with one command, and the workbook does the rest.

Setup steps

DataForSEO Labs: Ranked KeywordsEvery keyword a domain ranks for, with its ranking URL, position and volumedataforseo_labs/google/ranked_keywords/live

Save the JSON responseThe full API response, kept as your source of truth.response.json

Run the import scriptOffline. No API calls, no credentials.dataforseo_import.py import --template 21

Paste at A13Dashboard, checks and action list update instantly.Content Decay Analysis Template.xlsx

Column map for this template:Filled by the APIAudit trail (automatic)You add

Page URLEarlier impressionsEarlier clicksCurrent impressionsCurrent clicksDemand multiplierWork hoursSource reference
2 of 8 input columns come straight from DataForSEO

What the content decay analysis template decides

Traffic decline is a symptom with several causes. A page can lose clicks because it dropped in rankings (fewer impressions), because something on the SERP is stealing the click (lower CTR), or because people simply search for the topic less. Each cause needs a different fix, and only one of them is fixed by rewriting the page.

This content decay analysis template compares impressions and clicks for two periods, calculates CTR for each, and adjusts the earlier clicks by a demand index so you can see the gap that remains after market changes. Pages with a large negative gap are your real decay.

Who it's for

  • Content teams deciding which posts to refresh
  • SEOs explaining a traffic decline to stakeholders
  • Analysts separating SERP feature impact from ranking loss
  • Publishers with large archives

Where it fits in your workflow

The content decay analysis template answers one question. These three templates answer the questions around it:

  • Content Audit Template: what to do with each existing page (keep, refresh, merge or retire) and which decisions would throw away conversions, links or a merge destination.
  • Title Tag CTR Test Calculator: whether a title or snippet change produced a CTR difference that's distinguishable from noise, with honest labels for small samples and non-randomized comparisons.
  • Keyword Cannibalization Checker Template: which query and URL pairs are true cannibalization worth consolidating, which are distinct intents to leave alone, and which page should win.

What's inside the download

One ZIP with the workbook, an 8-column input template (blank and filled), the PDF field guide and an offline script that converts saved DataForSEO responses into the input CSV. The workbook has five tabs.

Workspace. The working table. 8 input columns, 5 calculated columns, room for 250 rows. Dropdowns, Yes/No fields and range checks catch bad inputs as you paste.
Start Here. What it decides, a four-step setup and the colour key.
Dashboard. KPI cards, charts, a ranked Top 10 and an automatic action list.
Delivery QA. Pass, fail or exclude each validation step with evidence, owner and date.
Field Guide. Every field explained, plus each formula in plain English.

How the content decay calculation works

Earlier CTR = clicks before ÷ impressions before. Current CTR = clicks now ÷ impressions now. Demand-adjusted prior clicks = clicks before × demand index. Click gap = clicks now − adjusted prior clicks.

The key formula, exactly as it appears in the workbook

=IF(I13="","",IF(I13<>"Ready","n.a.",E13-L13))
Result column Plain-English formula What it tells you
Earlier CTR Earlier clicks ÷ Earlier impressions Earlier clicks divided by earlier impressions.
Current CTR Current clicks ÷ Current impressions Current clicks divided by current impressions.
Demand-adjusted prior clicks Earlier clicks × Demand multiplier Earlier clicks scaled by the stated independent demand proxy.
Demand-adjusted click gap Current clicks-Demand-adjusted prior clicks Observed clicks minus demand-adjusted earlier clicks.

How to use the content decay analysis template

  1. Export two comparable periodsPull impressions and clicks per page from Search Console for the same months in two years.
  2. Add a demand indexUse Google Trends or keyword volume to estimate demand now vs then (0.9 = demand fell 10%).
  3. Compare CTR and impressionsImpressions falling means visibility loss; CTR falling with stable impressions means the SERP changed.
  4. Refresh the true decayersSort by demand-adjusted gap. Refresh visibility losers; rethink titles and snippets for CTR losers.

The worked example in the Workspace tab. Clear the lime input cells, paste your CSV at A13 (values only) and every result recalculates.

Fill the content decay analysis template with DataForSEO

Every template in this library is built to run on DataForSEO data. This one uses DataForSEO Labs: Ranked Keywords: every keyword a domain ranks for, with its ranking URL, position and volume.

Most of the data columns fill automatically. Add the business inputs listed below, then paste.

  1. Request the dataSend this request with your DataForSEO API login (HTTP Basic auth).
    POST https://api.dataforseo.com/v3/dataforseo_labs/google/ranked_keywords/live [ { "target": "yourdomain.com", "location_code": 2840, "language_code": "en", "limit": 1000 } ]
  2. Save the full JSON responseSave the complete response, not just the task ID, next to the template folder.
  3. Convert it with the included scriptIt runs offline, makes no API calls and never sees your credentials.
    python3 Automation/dataforseo_import.py import --template 21 --input response.json --output my-inputs.csv
  4. Add what the API can't knowEarlier impressions, Earlier clicks, Current impressions, Current clicks, Demand multiplier, Work hours
  5. Paste at A13Clear the demo rows, paste the CSV values at Workspace cell A13 and the dashboard updates.
Fills automatically from DataForSEO You add
Page URL Earlier impressions, Earlier clicks, Current impressions, Current clicks, Demand multiplier, Work hours

Source reference fills automatically with the task ID, endpoint, location, language, device and snapshot time, so every row is traceable. Before a big pull, estimate the cost with the DataForSEO pricing calculator. The same steps, plus an example request file, are in the download's Automation folder.

Worked example: Northbeam Outdoor

Every template ships filled with data from Northbeam Outdoor, a fictional outdoor-gear retailer that appears across all 40 workbooks, so you can see what useful output looks like before you add your own. This workbook has 10 example rows.

10Records
0Rows with input issues
-21,735Demand-adjusted click gap
10Pages losing clicks

The 'top tents of 2022' post lost both impressions and CTR: classic decay. The Adirondacks hikes post gained impressions but lost CTR, which points to a changed SERP rather than a content problem.

Bottom 10 by demand-adjusted click gap (biggest losses first)

# Page URL Demand-adjusted click gap
1 https://www.northbeam.example/blog/top-10-tents-2022/ -6,732
2 https://www.northbeam.example/blog/winter-camping-tips/ -3,776
3 https://www.northbeam.example/blog/best-hikes-adirondacks/ -2,296
4 https://www.northbeam.example/blog/how-to-waterproof-boots/ -2,244
5 https://www.northbeam.example/blog/trail-running-for-beginners/ -2,155

Input fields

Keep the columns in this order when you prepare a CSV. A missing observation stays blank; use 0 only for a measured zero or a verified No. Fields marked DataForSEO can be filled automatically by the included script.

CSV field Column Meaning and rule Source
url Page URL Exact absolute URL. Keep case and query parameters unless a documented normalization rule applies. DataForSEO: url
impressions_before Earlier impressions GSC impressions for a complete comparison window. Your records
clicks_before Earlier clicks Clicks for the same earlier window. Your records
impressions_now Current impressions GSC impressions for the current comparable window. Your records
clicks_now Current clicks Clicks for the current window. Your records
demand_index Demand multiplier Independent demand proxy current / earlier for this topic. 1 means unchanged. Your records
effort_hours Work hours Estimated implementation hours, greater than zero. Your records
source_ref Source reference Task ID, export filename and observation scope. Retain the untouched source response separately. Optional. DataForSEO: source_ref

Reading the result honestly

An unchanged CTR with lower impressions points toward exposure or demand. A lower CTR needs a SERP and snippet review.

Limits. Monthly keyword-volume estimates and GSC impressions measure different populations. The demand multiplier is an explicit heuristic.

Content decay mistakes to avoid

Treating every decline as decayIf demand fell 20%, a 20% traffic drop isn't something you can fix with content.
Comparing different seasonsCompare the same months year over year.
Refreshing pages with CTR problems by rewriting the bodyIf impressions held and CTR fell, start with the title, snippet and SERP features.

Tips from practice

  • Check SERP features, AI Overviews especially, for pages whose CTR dropped.
  • Use the title tag CTR test after changing snippets.
  • Feed confirmed decayers into the content audit as Refresh.

Frequently asked questions

What is content decay?

The gradual decline in a page's organic traffic over time as rankings slip, competitors improve, information ages or the SERP changes.

How do I find decaying content?

Compare Search Console clicks and impressions for each page across the same months in two years, adjusted for changes in demand.

How do I fix content decay?

Update facts, add original evidence, improve coverage of current questions, strengthen internal links and retest titles. Merge if a stronger page covers the topic.

Can AI Overviews cause traffic decline?

Yes. AI Overviews and other SERP features can lower CTR even when rankings hold. This template separates that from ranking loss.

Part of the Content SEO Toolkit

Get all 6 Content SEO templates in one ZIP, or the complete set of 40 SEO and GEO templates with the 401-page field handbook.

Download the Content SEO Toolkit

See the complete SEO & GEO toolkit

Jourdan Rombough

Built by Jourdan Rombough, technical SEO and GEO consultant with 15+ years in search. About Jourdan or get an SEO plan.

More on digital marketing in our blog

See all posts