Tabellab

Building a Content Inventory: One Row per URL, Content Age, Stale Pages and Pages per Content Type

Lesson 1 of 20Lab

All lessons
  1. Building a Content Inventory: One Row per URL, Content Age, Stale Pages and Pages per Content TypeLabFree
  2. Traffic and Conversion per URL: Conversion Rate, Revenue per Session, Share of Traffic and the Weighted Site RateLab Locked
  3. Cleaning the Inventory: Query Parameters, Variant Rows, Page-Level Sessions and Duplicate HighlightingLab Locked
  4. Traffic Trends per URL: Last 3 vs Prior 3 Months, Slope per Month, Declining and Growing Labels and a Monthly LineLab Locked
  5. Keep, Update, Merge or Remove: Threshold Table, Rule Order with IFS and Counts of Each OutcomeLab Locked
  6. Thin Content: Minimum Words per Page Type, Thin Flags, Average Length per Type, Correlation with Traffic and a Scatter ChartLab Locked
  7. Overlapping Pages: URLs per Query, Primary URL with MAXIFS, Click Share and Counting Cannibalised QueriesLab Locked
  8. Merge Plan: Group Sessions, Target Selection, Redirect Map and Expected Sessions After a MergeLab Locked
  9. URL Value Score: Indexing Three Signals, Weighted Score, Rank, Core/Support/Low Bands and Revenue ConcentrationLab Locked
  10. Protecting Backlinks When Retiring Pages: Redirect or 410 Rule, Authority Given Up and a Risk HighlightLab Locked
  11. Update Backlog Sizing: Hours per Page from Words and Update Level, Hours per Level and Months to Clear the BacklogLab Locked
  12. Prioritising Updates: Expected Extra Sessions, Gain per Hour, Rank, Cumulative Hours and a Sprint Cut-OffLab Locked
  13. Pruning Impact Tracking: Like-for-Like Sessions, Before and After Averages, Change % and a Two-Line ChartLab Locked
  14. Control Groups for Prune and Merge Results: Change per URL, Group Change, Difference in Percentage Points and a Column ChartLab Locked
  15. Redirect and Status QA After a Prune: Check Labels, Broken Targets, Problem Counts, Chain Share and a Problem HighlightLab Locked
  16. Did the Updates Work? Change per Page, Mean Change, Paired T-Test, Significance Verdict and a Confidence IntervalLab Locked
  17. Content Decay: Linear Forecast, Months Until a Sessions Floor, Priority Labels and an Actual-vs-Forecast ChartLab Locked
  18. What-If Grid for the Audit: Share Updated vs Uplift, Net Sessions, Break-Even Uplift, Annual Value and Loss ScenariosLab Locked
  19. Inventory Quality Checks: Source Counts, Issue Labels, Missing from the Sitemap, Session Reconciliation and an Incomplete-Row HighlightLab Locked
  20. Final Project: Content Audit Scorecard with Pages, Sessions and Hours per Decision, Projected Change, Net Effect and a ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

Why a content audit starts with an inventory

If your website has been running for a few years, it probably contains pages that nobody has looked at for a long time: a seasonal landing page from two summers ago, three blog posts that answer the same question, a buying guide whose prices and product names have changed. Nobody planned it that way. Content piles up because publishing is easy and cleaning up is not part of anyone's weekly routine. A content audit is the systematic review that fixes this: you look at every page, decide what should happen to it, and then do it.

The audit itself is a series of decisions, but decisions need facts, and the facts need a home. That home is the content inventory: a spreadsheet with one row per URL and a handful of columns that describe the page. Teams that skip the inventory and jump straight to opinions ("this blog feels old") end up arguing about taste. Teams that build it first can sort, filter and count, and the discussion moves to "these eight pages are stale and carry 44% of our organic sessions, so what do we do with them?"

This free lesson builds the first version of an inventory in Excel: content age, a stale flag driven by a threshold cell, and a summary per content type. It uses the same twelve pages as the interactive lab above. The company, Alderquay Outdoors, is a fictional retailer of camping and hiking gear with a guide section, a blog, category pages and landing pages. All pages, dates and numbers are invented for learning, and the thresholds are illustrative.

What an audit is for, and what it is not

An audit has a narrow job: to make sure that the content you keep serves a purpose, and that the content you cannot justify is improved, combined or retired in a controlled way. It helps to be clear about the typical goals before you start, because they change which columns you collect:

  • Quality and accuracy: find pages with outdated facts, prices, screenshots or advice, and put them in a queue for updating.
  • Focus: find thin, duplicate or overlapping pages that compete with each other, and merge them so that one strong page replaces several weak ones.
  • Performance: understand which pages bring traffic and conversions and which bring neither, so that the update budget goes where it pays.
  • Technical health: catch broken redirects, pages missing from the sitemap and pages that are crawled but never visited.
  • Capacity planning: size the work in hours and months, so that the audit ends in a plan an editor team can actually deliver.

An audit is not a deletion exercise. Removing pages can reduce clutter, but it is only one of four outcomes, and in practice most pages end up as Keep or Update. It is also not a one-off project: content ages every month, so the inventory needs a refresh cycle. Finally, it does not replace judgement. A spreadsheet can tell you that a page has 90 sessions and 260 words; it cannot tell you that the page is the one that your best customer found last year.

Everything in this course is a worked method on fictional data. Benchmarks, thresholds and rates are illustrative; use your own data and your own history before you set rules for a real site, and never promise a traffic outcome from an audit in advance.

The inventory: one row per URL

A useful inventory has few columns, all of them cheap to get and easy to keep current. For this lesson the columns are the page address, the content type, the publish date, the last updated date, the word count and the sessions of the last twelve months. Later lessons add conversions, referring domains, search queries and update hours.

Where do these values come from? Usually from three places that you combine on the URL: a crawl of your own site (addresses, word counts, status codes, titles), your content management system (publish and update dates, authors, categories) and your analytics tool (sessions and conversions per landing page). Lesson 3 shows how to clean the addresses so that the three sources match, and lesson 19 shows how to check that nothing is missing. For now, assume the data has been joined already.

Here is the worked example. The audit date is 30 Sep 2026 and the twelve pages are listed in the order of the lab, with the two calculated columns already filled in. The stale limit is 365 days.

URLTypeLast updatedWordsSessionsDays since updateStatus
/guides/how-to-choose-a-tentGuide20 Feb 20242,40018,400953Stale
/blog/best-trail-snacksBlog post2 May 20237802,1001,247Stale
/guides/waterproof-jacket-guideGuide5 Nov 20253,10024,650329Current
/category/sleeping-bagsCategory12 Mar 20264509,800202Current
/blog/camping-checklist-2022Blog post20 Apr 20221,1003,9001,624Stale
/landing/spring-saleLanding page1 Mar 2025300640578Stale
/guides/hiking-boot-sizingGuide22 Jan 20262,75015,200251Current
/blog/winter-trail-newsBlog post30 Nov 20215203101,765Stale
/category/backpacksCategory18 Sep 202552012,300377Stale
/blog/stove-comparisonBlog post30 Jun 20251,8006,700457Stale
/landing/summer-camp-offerLanding page10 Jun 202426090842Stale
/guides/sleeping-pad-r-valuesGuide1 Jun 20261,6507,400121Current

Step 1: content age in days

The first calculated column answers a simple question: how long ago was this page last touched? Excel stores dates as serial numbers, so the age in days is a subtraction. The DAYS function says the same thing more clearly and puts the later date first: =DAYS($C$2,D4), where $C$2 is the audit date and D4 the last update of the first page.

Two points deserve attention. First, use the last update, not the publish date. The sleeping-bag category page was created in April 2020, which sounds ancient, but it was revised in March 2026 and is only 202 days old for the purpose of the audit. Second, the audit date lives in one cell with an absolute reference (the dollar signs), so the whole column recalculates when you re-run the sheet next quarter with a new date.

A caution about the definition of "last update": a page that is edited for a typo is not necessarily current. If your content system records every save, you may overstate freshness. Many teams add a manual column such as "last reviewed" for editorial reviews, and measure staleness from that.

Step 2: stale or current, driven by a threshold cell

The second calculated column turns the age into a label. A page is stale when its age is greater than a limit, and current otherwise: =IF(G4>$G$2,"Stale","Current"). The limit, 365 days, sits in its own cell G2 instead of being typed into the formula. This is the single most important habit in this course: put every assumption into a visible cell. When the team later decides that product guides go stale after 180 days, you change one cell and every label moves.

Test your formula on a boundary. The category page for backpacks was updated on 18 September 2025, which is 377 days before the audit date, so it is stale by 12 days. One page that is exactly 365 days old would still be current, because the test is "greater than", not "greater than or equal to". Write down which of the two you mean.

In the example, 8 of the 12 pages are stale. The oldest is the winter trail news post, untouched for 1,765 days (almost five years); the most recently updated page is the sleeping-pad guide, 121 days ago. A single stale label per page is a blunt tool, but it is the right start: it creates the list from which later lessons choose Update, Merge or Remove.

Days since last update vs the 365-day stale limitCurrentStaleStale limit (365 days)1,765/blog/winter-trail-news1,624/blog/camping-checklist-20221,247/blog/best-trail-snacks953/guides/how-to-choose-a-tent842/landing/summer-camp-offer578/landing/spring-sale457/blog/stove-comparison377/category/backpacks329/guides/waterproof-jacket-guide251/guides/hiking-boot-sizing202/category/sleeping-bags121/guides/sleeping-pad-r-values03006009001,2001,5001,800Days since last update on 30 Sep 2026 (fictional pages)
Figure 1. Days since the last update for each page, oldest first. The dashed line is the 365-day limit. Eight bars cross it: those pages are stale on the audit date.

Step 3: summarise per content type

A list of twelve pages is easy to read; a list of twelve thousand is not. The next step is therefore a summary per content type. Three functions do all the work, each with the type in column B as the first condition:

  • Pages: =COUNTIF($B$4:$B$15,J4) counts the rows whose type equals the label in J4.
  • Stale pages: =COUNTIFS($B$4:$B$15,J4,$H$4:$H$15,"Stale") adds a second condition, the status.
  • Stale sessions: =SUMIFS($F$4:$F$15,$B$4:$B$15,J4,$H$4:$H$15,"Stale") adds up the sessions in column F where both conditions hold.

The same results can be reached without the status column by testing the date directly, with a criterion such as "<"&$C$2-$G$2 on the last-updated column (last update earlier than the audit date minus the limit). That version does not depend on another helper column, which makes it robust when you copy the summary into a different sheet.

Content typePagesStale pagesStale sessionsAll sessions
Guide4118,40065,650
Blog post4413,01013,010
Category2112,30022,100
Landing page22730730
All12844,440101,490

Read the table as a story. All four blog posts and both landing pages are stale, which is not surprising for news and campaign pages. Guides look healthy at first sight, with only one stale page out of four, but that one page is the tent-buying guide with 18,400 sessions, the second-largest in the whole sample. In total, 44,440 of the 101,490 sessions (43.8%) sit on stale pages. Staleness and importance are different questions, and the audit has to answer both.

Sessions on current and stale pages by content typeSessions on current pagesSessions on stale pagesGuide (4)47,25018,400Blog post (4)13,010Category (2)9,80012,300Landing page (2)730020,00040,00060,000Sessions in the last 12 months (fictional)
Figure 2. Sessions on current and stale pages for each content type (number of pages in brackets). The large stale block in the guides row is a single page, which is why the audit looks at traffic as well as age.

Reading the result

What does this first inventory tell Alderquay Outdoors? Three things. First, the problem is real but unevenly spread: the blog and the campaign pages are almost all old, while the guide section is mostly maintained. Second, the biggest single risk is not a long tail of old posts but one high-traffic guide that has not been revised since February 2024. Third, the two landing pages carry only 730 sessions in a year between them, so their staleness costs almost nothing.

That is exactly the kind of prioritisation a raw list cannot give you. A common pattern in real audits is that a small number of pages carry most of the value and most of the risk, while a large number of pages carry neither. The next lessons add conversions, trends, overlaps and links so that you can separate the pages that deserve an update from the ones that should be merged or retired.

Common mistakes in a first inventory

These are the mistakes that most often spoil a first inventory. Each of them can be found with a few formulas, which is why the lab asks you to build the sheet yourself:

  • Counting age from the publish date. A regularly maintained page then looks hopelessly old. Use the last meaningful update.
  • Typing the limit into every formula. You cannot change the rule without editing hundreds of cells, and you will forget one. Keep the limit in a cell.
  • Reversing DAYS. DAYS(end, start) takes the later date first. =DAYS(D4,$C$2) gives a negative age.
  • Forgetting the dollar signs. Without $C$2 the audit date moves down with the formula and the column becomes nonsense after the first row.
  • Mixing address variants. The same article under three addresses is three rows with a third of the traffic each. Clean the addresses first (lesson 3).
  • Treating stale as bad. Stale is a flag for review. A stale page with 18,400 sessions is a priority, not a deletion.

Quick reference: the functions in this lesson

A short list to keep next to the sheet. Every function appears again in later labs, so it is worth getting comfortable now:

  • DAYS(end_date, start_date): the number of days between two dates, with the later date first.
  • IF(test, value_if_true, value_if_false): returns one of two results. Use it for the Stale/Current label.
  • COUNTIF(range, criterion): counts the cells that meet one condition.
  • COUNTIFS(range1, criterion1, range2, criterion2, …): counts rows where all conditions are true.
  • SUMIFS(sum_range, range1, criterion1, …): adds the values in the sum range where all conditions are true.
  • $ in a reference: $C$2 is an absolute reference that does not change when you copy the formula.

Where the data comes from in practice

Most small and mid-sized sites can build an inventory in an afternoon without special software. A crawler export gives you the address, the page title, the word count and the HTTP status code. The content management system or a database export gives you the content type, the author and the dates. The analytics tool gives you sessions and conversions per landing page; export the same twelve-month window for all pages so the numbers are comparable.

Join the three tables on the address with a lookup such as XLOOKUP, and keep one row per cleaned address. Resist the urge to add thirty columns. Every column you cannot keep current is a source of wrong decisions later. Start with the six in this lesson, add the ones you need for the next decision, and record the date and the source of every export in a note on the sheet. That note is what lets a colleague, or you in six months, trust the numbers.

Be careful with privacy and consent as well. The inventory needs page-level numbers only, not data about individual visitors. Aggregate reports are enough, and they keep the audit clear of questions about personal data.

Keeping the inventory alive

An inventory is a snapshot. To make it useful over time, give it an owner, a refresh date and a home that the whole team can reach. Quarterly is a sensible rhythm for most sites, half-yearly for small ones. At each refresh you change the audit date, import new exports and re-run the formulas. The decisions of the last audit become columns of the next one: which pages were updated, merged or removed, and what happened afterwards. That history is what turns an audit into a learning loop.

The content audit cycle1 Inventoryone row per URL2 Measuretraffic, conversion3 Decidekeep, update,merge, remove4 Actupdate, redirect,retire5 Trackbefore/after,control groupRepeat every quarter or half-year: the inventory is never finished
Figure 3. The audit cycle in this course. The first lessons build the inventory and the measures; the middle lessons decide and plan the work; the last lessons track the effect and start the next cycle.

What you will build in the rest of this course

The remaining nineteen labs follow the cycle in Figure 3. Each uses a different fictional data set from Alderquay Outdoors and a new set of spreadsheet skills:

  1. Traffic and Conversion per URL: You add conversion rate, revenue per session and share of sessions to every URL, compute the weighted site conversion rate and chart revenue per session.
  2. Cleaning the Inventory: You strip query parameters from raw URLs, count variants per page, add up the traffic split across them, measure the share on parameter URLs and highlight duplicates.
  3. Traffic Trends per URL: You compare two three-month windows, calculate the monthly slope, label each URL Declining, Stable or Growing against threshold cells, count the decliners and chart the site total.
  4. Keep, Update, Merge or Remove: You calculate days since update, apply an ordered keep/update/merge/remove rule from a parameter table and count the merge and removal candidates and the sessions they carry.
  5. Thin Content: You look up the minimum word count per page type, flag thin pages, average the length per type, measure the correlation between length and traffic and plot it.
  6. Overlapping Pages: You count URLs per query, add up query clicks, calculate each URL's click share, label primary and competing URLs and count the queries with overlap.
  7. Merge Plan: You add up sessions per merge group, pick the target URL of each group, build the redirect map, estimate the sessions after the merge and count the redirects to implement.
  8. URL Value Score: You index sessions, revenue and referring domains, combine them with weights from cells into a value score, rank and band the URLs, measure revenue concentration and chart the scores.
  9. Protecting Backlinks When Retiring Pages: You assign a redirect or 410 action to every retired page, count the pages that end in 410, add up the referring domains lost and highlight the pages that need a manual redirect decision.
  10. Update Backlog Sizing: You turn the update list into hours per page and per level, size the total backlog against the editors' capacity and chart the hours by level.
  11. Prioritising Updates: You estimate the extra sessions of each update, divide by the hours, rank the pages without helper columns, accumulate the hours and mark what fits into the sprint capacity.
  12. Pruning Impact Tracking: You remove the pruned URLs' sessions from every month, compare three-month averages before and after the prune and chart the total against the like-for-like line.
  13. Control Groups for Prune and Merge Results: You calculate the change per URL, compare the merge targets with untouched control pages using totals, express the effect as a difference in percentage points and chart the changes.
  14. Redirect and Status QA After a Prune: You label every retired URL from its status code and hops, test the redirect targets against the keep list, count the problems, measure the chain share and highlight the rows that need a fix.
  15. Did the Updates Work?: You calculate the change per updated page, the mean change, the paired t-test p-value, a verdict against a significance level and the confidence interval of the mean change.
  16. Content Decay: You forecast month 9 with a fitted line, solve for the months until a sessions floor, label the urgency, build a fitted series for one page and chart it against the actuals.
  17. What-If Grid for the Audit: You build a two-way scenario grid with mixed references, find the break-even uplift, convert the grid to annual value after update cost, count the losing scenarios and colour them.
  18. Inventory Quality Checks: You count the sources per URL, label each inventory gap, count pages missing from the sitemap, reconcile the export total against a dashboard figure and highlight incomplete rows.
  19. Final Project: You summarise the audit decisions into a scorecard of pages, sessions, update hours and projected session change per decision, calculate the net effect and chart it.

Summary

A content audit starts with an inventory: one row per URL with type, dates, words and sessions. Content age is =DAYS($C$2,D4), measured from the last update; the status is =IF(G4>$G$2,"Stale","Current") against a limit kept in its own cell; COUNTIF, COUNTIFS and SUMIFS summarise pages, stale pages and stale sessions per type. At Alderquay Outdoors, 8 of 12 pages are stale and carry 44,440 of 101,490 sessions (43.8%), more than 40% of them on one tent guide, which shows why age alone never decides a page's fate.

Now scroll up to the lab and build the sheet yourself. It takes about fifteen minutes, the Coach checks each step, and you will end up with an inventory structure that you can apply to your own site today.

Frequently asked questions

What is a content inventory?

A content inventory is a spreadsheet with one row for every URL on your site and a few columns that describe it, such as content type, publish and update dates, word count and traffic. It is the basis of every content audit decision.

How do I calculate the age of a page in Excel?

Subtract the last update date from the audit date, or use =DAYS(audit_date, last_updated). Measure from the last meaningful update rather than the publish date, and keep the audit date in one cell with an absolute reference.

What makes a page stale?

A page is stale when the days since its last update exceed a limit that you choose, for example 365 days. Fast-moving topics may need a shorter limit. The limit should sit in its own cell so that you can change it without editing formulas.

Should I delete stale pages?

Not automatically. Stale only means the page needs review. A stale page with a lot of traffic or links is usually updated, and pages with no traffic, no conversions and no links are the typical removal candidates.

How many columns does an inventory need?

Start with about six: address, content type, publish date, last updated date, word count and sessions. Add conversions, links or search queries only when a decision needs them, and keep every column current.

How often should I repeat a content audit?

Most sites benefit from a refresh every quarter or half-year. Change the audit date, import new exports and re-run the formulas so that you can compare the results with the previous cycle.

Keep going: Content Audits: Inventory, URL Performance, Keep/Update/Merge/Remove Rules, Thin & Overlapping Pages, Pruning Impact & Update Backlog Sizing

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Traffic and Conversion per URL: Conversion Rate, Revenue per Session, Share of Traffic and the Weighted Site Rate
  2. Cleaning the Inventory: Query Parameters, Variant Rows, Page-Level Sessions and Duplicate Highlighting
  3. Traffic Trends per URL: Last 3 vs Prior 3 Months, Slope per Month, Declining and Growing Labels and a Monthly Line
  4. Keep, Update, Merge or Remove: Threshold Table, Rule Order with IFS and Counts of Each Outcome
  5. Thin Content: Minimum Words per Page Type, Thin Flags, Average Length per Type, Correlation with Traffic and a Scatter Chart
  6. Overlapping Pages: URLs per Query, Primary URL with MAXIFS, Click Share and Counting Cannibalised Queries
  7. Merge Plan: Group Sessions, Target Selection, Redirect Map and Expected Sessions After a Merge
  8. URL Value Score: Indexing Three Signals, Weighted Score, Rank, Core/Support/Low Bands and Revenue Concentration
  9. Protecting Backlinks When Retiring Pages: Redirect or 410 Rule, Authority Given Up and a Risk Highlight
  10. Update Backlog Sizing: Hours per Page from Words and Update Level, Hours per Level and Months to Clear the Backlog
  11. Prioritising Updates: Expected Extra Sessions, Gain per Hour, Rank, Cumulative Hours and a Sprint Cut-Off
  12. Pruning Impact Tracking: Like-for-Like Sessions, Before and After Averages, Change % and a Two-Line Chart
  13. Control Groups for Prune and Merge Results: Change per URL, Group Change, Difference in Percentage Points and a Column Chart
  14. Redirect and Status QA After a Prune: Check Labels, Broken Targets, Problem Counts, Chain Share and a Problem Highlight
  15. Did the Updates Work? Change per Page, Mean Change, Paired T-Test, Significance Verdict and a Confidence Interval
  16. Content Decay: Linear Forecast, Months Until a Sessions Floor, Priority Labels and an Actual-vs-Forecast Chart
  17. What-If Grid for the Audit: Share Updated vs Uplift, Net Sessions, Break-Even Uplift, Annual Value and Loss Scenarios
  18. Inventory Quality Checks: Source Counts, Issue Labels, Missing from the Sitemap, Session Reconciliation and an Incomplete-Row Highlight
  19. Final Project: Content Audit Scorecard with Pages, Sessions and Hours per Decision, Projected Change, Net Effect and a Chart

Buy course — $9.90 See the full course