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.
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.
| URL | Type | Last updated | Words | Sessions | Days since update | Status |
|---|---|---|---|---|---|---|
| /guides/how-to-choose-a-tent | Guide | 20 Feb 2024 | 2,400 | 18,400 | 953 | Stale |
| /blog/best-trail-snacks | Blog post | 2 May 2023 | 780 | 2,100 | 1,247 | Stale |
| /guides/waterproof-jacket-guide | Guide | 5 Nov 2025 | 3,100 | 24,650 | 329 | Current |
| /category/sleeping-bags | Category | 12 Mar 2026 | 450 | 9,800 | 202 | Current |
| /blog/camping-checklist-2022 | Blog post | 20 Apr 2022 | 1,100 | 3,900 | 1,624 | Stale |
| /landing/spring-sale | Landing page | 1 Mar 2025 | 300 | 640 | 578 | Stale |
| /guides/hiking-boot-sizing | Guide | 22 Jan 2026 | 2,750 | 15,200 | 251 | Current |
| /blog/winter-trail-news | Blog post | 30 Nov 2021 | 520 | 310 | 1,765 | Stale |
| /category/backpacks | Category | 18 Sep 2025 | 520 | 12,300 | 377 | Stale |
| /blog/stove-comparison | Blog post | 30 Jun 2025 | 1,800 | 6,700 | 457 | Stale |
| /landing/summer-camp-offer | Landing page | 10 Jun 2024 | 260 | 90 | 842 | Stale |
| /guides/sleeping-pad-r-values | Guide | 1 Jun 2026 | 1,650 | 7,400 | 121 | Current |
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.
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.
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 type | Pages | Stale pages | Stale sessions | All sessions |
|---|---|---|---|---|
| Guide | 4 | 1 | 18,400 | 65,650 |
| Blog post | 4 | 4 | 13,010 | 13,010 |
| Category | 2 | 1 | 12,300 | 22,100 |
| Landing page | 2 | 2 | 730 | 730 |
| All | 12 | 8 | 44,440 | 101,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.
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$2the 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$2is 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.
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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- Traffic and Conversion per URL: Conversion Rate, Revenue per Session, Share of Traffic and the Weighted Site Rate
- Cleaning the Inventory: Query Parameters, Variant Rows, Page-Level Sessions and Duplicate Highlighting
- Traffic Trends per URL: Last 3 vs Prior 3 Months, Slope per Month, Declining and Growing Labels and a Monthly Line
- Keep, Update, Merge or Remove: Threshold Table, Rule Order with IFS and Counts of Each Outcome
- Thin Content: Minimum Words per Page Type, Thin Flags, Average Length per Type, Correlation with Traffic and a Scatter Chart
- Overlapping Pages: URLs per Query, Primary URL with MAXIFS, Click Share and Counting Cannibalised Queries
- Merge Plan: Group Sessions, Target Selection, Redirect Map and Expected Sessions After a Merge
- URL Value Score: Indexing Three Signals, Weighted Score, Rank, Core/Support/Low Bands and Revenue Concentration
- Protecting Backlinks When Retiring Pages: Redirect or 410 Rule, Authority Given Up and a Risk Highlight
- Update Backlog Sizing: Hours per Page from Words and Update Level, Hours per Level and Months to Clear the Backlog
- Prioritising Updates: Expected Extra Sessions, Gain per Hour, Rank, Cumulative Hours and a Sprint Cut-Off
- Pruning Impact Tracking: Like-for-Like Sessions, Before and After Averages, Change % and a Two-Line Chart
- Control Groups for Prune and Merge Results: Change per URL, Group Change, Difference in Percentage Points and a Column Chart
- Redirect and Status QA After a Prune: Check Labels, Broken Targets, Problem Counts, Chain Share and a Problem Highlight
- Did the Updates Work? Change per Page, Mean Change, Paired T-Test, Significance Verdict and a Confidence Interval
- Content Decay: Linear Forecast, Months Until a Sessions Floor, Priority Labels and an Actual-vs-Forecast Chart
- What-If Grid for the Audit: Share Updated vs Uplift, Net Sessions, Break-Even Uplift, Annual Value and Loss Scenarios
- Inventory Quality Checks: Source Counts, Issue Labels, Missing from the Sitemap, Session Reconciliation and an Incomplete-Row Highlight
- Final Project: Content Audit Scorecard with Pages, Sessions and Hours per Decision, Projected Change, Net Effect and a Chart