How to visualise your keyword seasonality in Google Sheets

by David Gossage June 7, 2023

Understanding the seasonality of your website is important to the success of your not just your SEO campaign but across all of your marketing channels. It can give you the competitive edge when it comes to content prioritisation and promotion. It can also be important when it comes to link building as you will know what type of content to promote on other sites whenm increasing your success rate.

But how can you get a clear understanding of the seasonality of your site in a simple way? This challenge becomes more difficult the bigger your site gets. Follow these simple steps too see how your predicted traffic changes over time.

Step 1 - Cluster your keywords

Topic clustering is a popular topic in SEO and there are many ways to do so. There are many ways to do this, depending on your site, so we won't go into detail on how to do so.

What you need is a spreadsheet which simply contains 2 columns; the keyword and the topic it belongs to. You can take it a step further and add a column for subcategories to add extra detail if your site is big enough.

Step 2 - Get search volumes

Start by opening Google's keyword planner and add you keyword list. Where keyword planner has an advantage over other keyword research tools is the ability to get search volume broken down by month for the last 2 years. Export the historical search volumes and copy the results into your Google Sheet.

You now have all your keywords broken down by search volume per month. Use a simple vlookup() to add your topics to your search volumes.

Note: Two major flaws of Keyword Planner are close match keywords and excluding keywords. Close match will rewrite the keywords in your export so comparing this with your original keyword list will create some errors. It will take a bit of manual editing to fix this. Often keywords will be also excluded altogether if Google deems them inappropriate or irrelevant. This means that some of your keyword seasonality may not be able to have their seasonality tracked.

Step 3 - Create a pivot table

Highlight all your data in Google Sheets and go to 'Insert > Pivot Table'. If you're not familiar with pivot tables, don't worry they're not too difficult. Under "Rows", select your topic column. If you have added extra columns for subcategories, add these to the rows too. Under "Values" add each month column and set to "sum".

You will now have the search volume for every topic on your website, broken down month by month. It's as simple as that! You can now easily check, month by month, which topics on your site are popular at which time of year.

Step 4 - Add some colour

Let's step things up a bit. You now have a spreadsheet which displays the search volumes per topic broken down by month. However, this table can be difficult to read. Let's make it a bit easier by adding a splash of colour.

The normal way to do this is to highlight a row, go to 'Format > Conditional Formatting' and then select 'Color scale'. Then repeat for every row.

But what if you have dozens of rows? Hundreds? There's better ways for you to spend your time. To do this in bulk, go to 'Extensions > Apps script' and paste in the following code:

function applyColorScaleFormatting() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var lastRow = sheet.getLastRow();

var lastColumn = sheet.getLastColumn();

var range = sheet.getRange(1, 1, lastRow, lastColumn);

var rules = sheet.getConditionalFormatRules();

// Remove existing color scale rules

for (var i = rules.length - 1; i >= 0; i--) {

if (rules[i].getBooleanCondition().getType() === SpreadsheetApp.ConditionalCriteria.CELL_IS_COLOR_SCALE) {

rules.splice(i, 1);

}

// Apply color scale formatting to each row

for (var row = 1; row <= lastRow; row++) {

var startColumn = 1;

var endColumn = lastColumn;

var rule = SpreadsheetApp.newConditionalFormatRule()

.setRanges([sheet.getRange(row, startColumn, 1, endColumn)])

.setGradientMaxpointWithValue('#57bb8a', SpreadsheetApp.InterpolationType.PERCENT, '100')

.setGradientMidpointWithValue('#ffffff', SpreadsheetApp.InterpolationType.PERCENT, '50')

.setGradientMinpointWithValue('#e67c73', SpreadsheetApp.InterpolationType.PERCENT, '0')

.build();

rules.push(rule);

}

// Update conditional format rules

sheet.setConditionalFormatRules(rules);

}

Highlight the top row in your sheet, click "Run" in the script and watch the magic happen.

The result is that you end up with something like this:

The green areas are those with high search volume. The red are for low search volume. Now you can instantly see which topics are popular month by month, allowing you to plan your marketing activity around it.

Have a go and let me know how you get on.