Pivot tables for content audits
Pivot tables are magic! Why? Because they help you turn a well-structured audit spreadsheet into actionable insights super quickly.
Benefits
Spreadsheets are big, boring and hard to understand.
Charts and tables are much easier to scan, interpret, act on. They are also much better “props” to explain the content ecosystem and roadmap to senior stakeholders.
Quick answers at scale
If your spreadsheet is well structured, you can use it to create a pivot on any variable (row heading).
For example.
What proportion of content has metadata?
What proportion is owned and by what team?
What is the content about?
What format is the content in?
What proportion of content has been rewritten, and therefore, how much has not been completed yet?
This is especially useful when working with thousands of URLs in a massive audit spreadsheet.
So long as your spreadsheet is structured well, any variable in your spreadsheet can be pivoted.
Learn how to use them
This video demonstrates how to create a very simple pivot table using Google Docs. Don’t worry, the process is very similar in Microsoft Excel.
How to structure your spreadsheet
To create pivot tables, your spreadsheet must be organised in a specific way. The example on the left will work, the option on the right won’t.
The focus here is on the attributes of each page rather than their relationship to each other. Though you still get that when you create your pivot. What you lose is the exact page order of each section, which is why it's beneficial to accompany this kind of audit with a high-level site map that records the ordering.
This video provides more information on how to structure your spreadsheet and why.
This final video shows you how to split your URL so you can create a pivot on page subject. This is the pivot I generally start with.
