Content Decay Analysis Template
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.
=IF(I13="","",IF(I13<>"Ready","n.a.",E13-L13))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.
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
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.
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
- Export two comparable periodsPull impressions and clicks per page from Search Console for the same months in two years.
- Add a demand indexUse Google Trends or keyword volume to estimate demand now vs then (0.9 = demand fell 10%).
- Compare CTR and impressionsImpressions falling means visibility loss; CTR falling with stable impressions means the SERP changed.
- 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.
- 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 } ]
- Save the full JSON responseSave the complete response, not just the task ID, next to the template folder.
- 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
- Add what the API can't knowEarlier impressions, Earlier clicks, Current impressions, Current clicks, Demand multiplier, Work hours
- 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.
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
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.
Use it with these templates
Each template answers one decision. These are the ones that feed into, or follow from, the content decay analysis template.
20Content Audit TemplateOn-Page API
Score every URL by lost clicks, conversions and links, choose keep, refresh, merge or retire, and catch the risky calls.
24Title Tag CTR Test CalculatorOn-Page API
Compare two CTRs with a 95% interval and plain-English verdicts, so you don't ship a title change that was just noise.
22Keyword Cannibalization Checker TemplateLabs: Ranked Keywords
Find queries splitting clicks across two URLs, check whether intent really overlaps and decide whether to consolidate.
06SEO Visibility Score CalculatorLabs: Ranked Keywords
Weight rankings by search volume and CTR to see real visibility gains and losses across a fixed keyword set.
23SEO Content Calendar TemplateLabs: Keyword Overview
Track owners, due dates and estimated vs completed hours, and flag overdue work automatically.
19SEO Content Brief TemplateLabs: Keyword Overview
A content brief built for Google and AI answers: real questions, original evidence, sources and a three-step approval workflow.
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.
Further reading on Optimization Theory
Built by Jourdan Rombough, technical SEO and GEO consultant with 15+ years in search. About Jourdan or get an SEO plan.






