Turn your content inventory into an automated content audit scheduler
Content auditing is a process technical writers and content managers of all kinds must do to keep their content up to date and accurate. In agile software, where products can change from week to week, keeping a constant watch on the accuracy and currency of published content is both crucial and challenging. That’s why I’ve always kept a content inventory and a content prioritization system to help me keep content current in rapid development organizations.
If you are using a spreadsheet, or content inventory, to keep track of your published help articles, videos, courses, or any other kind of content, you can turn it into a simple audit planning tool with just some minor spreadsheet savvy and a few spare minutes. You’ll have a dashboard in your content inventory that automatically lists past due audits and content to be audited this week or next week.
In this guide, I show you how to create an automated content audit scheduler. The goal is to give writers and managers a look at all the existing content that needs to be audited for content currency. All you need to do is add a couple extra columns, two formulas, and an additional spreadsheet tab to your Microsoft Excel or Google Sheets workbook.
All formulas shown in this guide work in both Microsoft Excel and Google Sheets. Here is a sample template you can use if you want to follow along.
Step 1: Get your priorities straight
Before you make any changes to your existing content inventory - or “tracking spreadsheet” - you will want to define priorities that align well with your current workflow and development cycles at your organization. I like to define priority like this:
High priority content is for products and features that are actively being developed or marketed by the organization and therefore can change every release.
Medium priority content is for products and features that are actively maintained, but not necessarily changing every release.
Low priority content is for products and features that are fairly static and may change rarely.
After you have defined your priorities, you will need to define how they influence your content audit schedule. High priority content should be updated more often than medium priority, and medium priority content more often than low. For example, if your organization has monthly releases, you might consider the following model, which I will use as the example in this guide:
High priority - Audit every 30 days
Medium priority - Audit every 60 days
Low priority - Audit every 90 days
Step 2: Let’s get columnar
If you are using a spreadsheet as your content inventory, then you likely have a column tracking a last modified date. Maybe you call it “Last Updated”, but either way, it is a column tracking the last time changes were made to a piece of content. If you don’t have one, you’ll need one. To begin building the automated scheduler, do the following:
Create a Priority column.
Create a Next Audit Date column.
Here is a very simplified content inventory as an example:
For the Priority column, use data validation to create a drop-down list in the cells to choose from your priority options. Then assign priorities for all of your content.
In the Next Audit Date column, we want the date to be generated based on the relationship between the Last Modified Date and the Priority.
Let’s start with some plain language logic using the priority model we defined above:
If Priority is High, then add 30 days to the Last Modified Date, and
If Priority is Medium, then add 60 days to the Last Modified Date, and
If Priority is Low, then add 90 days to the Last Modified Date
Let’s use the IFS formula to add that logic to cells in column D. In column D2, input the following formula:
=IFS(C2="high",B2+30, C2="medium",B2+60,C2="low",B2+90)
Make sure you set the format of the cells in column D to the Date Format. When it looks like the cell is calculating correctly, select the cell, then click and drag to copy the formula for the entire column.
And there you have it! Now you have an automated audit date for each piece of content in your content inventory. You can change the Last Modified Date and Priority as needed, and the Next Audit Date will automatically update.
But let’s make it even easier to know what to audit at any given time.
Step 3: Who doesn’t like a dashboard?
Next, we will create a simple dashboard that pulls over all content that has passed it’s next audit date, is due for audit this week, and due in two weeks. The dashboard helps to plan work at any given time by giving a filtered look at all content that is coming up for audit.
Start by creating a new sheet and naming it Dashboard (Or whatever you want to name it. I’ve also seen it called ‘Sheet 2’. But that’s not terribly helpful.)
The goal for this dashboard is to show all content in three categories: Past due, Due next week, and Due in 2 weeks. Here is a very basic example of what this might look like:
The lists in the Past due, Due next week, and Due in 2 weeks columns are generated using the FILTER formula. Here is the plain language logic for what we are trying to accomplish in each column:
Past due: Show content where Next Audit Date is less than (before) today
Due this week: Show content where Next Audit Date is between Today and Today + 7 days
Due next week: Show content where Next Audit Date is between Today + 7 days and Today + 14 days
The following FILTER formulas go in cells A3, B3, and C3. The lists are generated and populate the column.
Past due
=filter('Content Inventory'!A:A,('Content Inventory'!D:D<=today()) * ('Content Inventory'!D:D<>""))
The highlighted condition states that if the date in column D (Next Audit Date) of the content inventory is less than or equal to today, show it in the filtered list. Only content items that are past due for an audit appear.
Due this week
=filter('Content Inventory'!A:A, ('Content Inventory'!D:D<=today()+7) * ('Content Inventory'!D:D>today())* ('Content Inventory'!D:D<>""))
The highlighted condition states that if the date in column D (Next Audit Date) of the content inventory is between today and 7 days from now, show it in the filtered list. Only content items that are due for audit in the next week appear.
Due in 2 weeks
=filter('Content Inventory'!A:A,('Content Inventory'!D:D<=today()+14) * ('Content Inventory'!D:D>today()+7)* ('Content Inventory'!D:D<>""))
The highlighted condition states that if the date in column D (Next Audit Date) of the content inventory is between 7 days from now and 14 days from now, show it in the filtered list. Only content items that are due for audit two weeks from now appear.
And that’s that. With the dashboard set up and your next audit dates populating automatically, every time you open your content inventory you will have a clear view of what content needs to be audited. What took you only a few minutes to set up will save you hours of planning and organizing. Now go tell your boss how you revolutionized content management and demand a raise!
Commenti