How to use Google Sheets to Track Content Decay

Tracking content decay can help you to monitor the performance of your content over time and identify areas that need improvement or adjustment

With Google Search Console and Google Sheets, you can easily get a holistic view of your content performance. By identifying pages with significant drop, you can make informed decisions about how to adjust it or reshape content strategies as needed.

Step 1. Create a new Google Sheets (or make a copy of the template)

First, create a new Google Sheets or make a copy of my content decay template.

In your Sheets, you should have:

1. “Overview” tab

The “Overview” tab will contain the information that you need for analysing content decay of your website.

To start with, you should put all the URLs of your website in the first column.

You should also create additional columns for the monthly impression numbers.

2. Monthly tab

You should also create a separate tab for each month to store your page-level search performance data. At this step, you can keep the tab empty as you will paste the data directly after exporting them from Google Search Console.

In my template, I’m having tabs for 6 months. But you can create more depending on your needs. Or you would like to have a QoQ analysis, you can also rename the columns and tabs to the quarter.

Step 2. Go to Google Search Console and Open Search Performance Report

Next, go to Google Search Console and choose the domain property you want to analyse. Select the search performance report in the left-hand menu.

Step 3. Filter by date range and export the data

Then filter by date range and customise to the complete calendar month. If you would like to compare the data quarter-on-quarter, you can also customise that.

Step 4. Copy & Paste the data to relevant tab

In the exported data, head to the “Pages” tab. Here you will have your search performance number on page-level.

Copy the whole tab data and paste it to the relevant tab in your Content Decay Analysis Sheets. Repeat Step 3 and Step 4 for each period you would need to analyse.

Step 5. Use VLOOKUP to search for impressions number

If you’re using my template, the impression number should be automatically extracted in the first tab .

However, if you’d prefer to build it on your own, you can use VLOOKUP to pull the impressions number for a unique URL:

=IFERROR(VLOOKUP($A2,'Month 1'!$A:$E,3,FALSE),0)

• IFERROR is used so if your URL isn’t showing up in the GSC report, it will return 0. This mostly happens for new pages you recently published as GSC won’t have data prior to the publish date.
• $A2 refers to the value to search for, i.e. URL of the landing page.
• ‘Month 1’!$A:$E’ is the range of value that you are looking for. So here we’re lookging
• “3” is the index of the column with the return value of the range. We are using ‘3’ as Impressions are the third column of the range. If you want to see the number of clicks, use 2 instead.

Step 6. Use Conditional Formatting to Colour-Code changes

Going through the Overview full of numbers can be overwhelming, that’s why it would be handy to colour code the changes to make it more intuitive for analysis.

Again, if you’re using my template, the formatting is done already. But you can do it yourself with conditional formatting to highlight URLs:

• Increase in impressions: green (=C2>B2)
• Decrease in impressions: red (=C2<B2)
• No change: white

Step 7. Calculate changes

Finally, you can add another column and use this formula =IFERROR((G2-B2)/B2,”N/A”) to calculate the changes comparing Month 6 to Month 1.

You can also use conditional formatting to highlight pages that have dropped when compared to 6 months ago.

Step 8. Analyse the data

Now you can review the changes and analyse why certain pages had huge drops in traffic or had a big increase.

For example, if you see some pages that had a constant or huge drop in traffic over 6 months, you should look into why it happened and take measures to improve the page rank and visibility on search engines. This could involve content optimisation (rewriting meta tags, optimising keyword targeting etc.) or any other SEO related activities.

By leveraging Google Search Console and Google Sheets, you can monitor your content’s performance over time and identify signs of content decay at a glance.

This method not only helps you stay on top of your SEO game but also empowers you to make informed decisions about where to direct your optimisation efforts.

Remember, content decay is a natural part of the digital landscape, but with a proactive and systematic approach, you can mitigate its effects and enhance your site’s overall performance.