There are a lot of benefits to having a good content strategy. Publishing effective content at the right time will help keep your brand relevant to what’s on people’s minds, increasing interest and bringing more traffic to your website.
The problem? Churning out content is no easy task, and is especially daunting if you’re just getting started. Luckily, there are a number of tools and techniques that can help you with the content brainstorming process. In this post, we will focus on one particular tool - the Google AdWords Keyword Planner - which was discussed in a previous blog post about how to develop content. Not only does the Keyword Planner help you think of great topics, it also allows you to understand the importance of seasonality.
The Keyword Planner and content strategy
For example - in the spirit of tax season - I pulled search volume data for the keywords “taxes” and “tax forms” from the Keyword Planner. The graph below illustrates that the search terms “taxes” and “tax forms” are very popular at the beginning of the year through April, and are less popular in the following months. This is a simple example, but you can imagine how valuable it can be to draw this out to any keyword you want. In this post, we’ll take this process even further and group similar keywords that together form a content group. We can then determine the search volume for a specific content group, and put together a “calendar” of when certain content topics are popular during the year.
The main use of the Keyword Planner is to help AdWords advertisers think of new words or phrases to add to their pay-per-click advertising campaigns. If you’re familiar with AdWords, you know that ads are organized into Ad Groups within the platform, and that it’s a best practice to create groups that target similar keywords. Fortunately, AdWords doesn’t restrict us to using the Keyword Planner for advertising campaigns.
You can input data into the Keyword Planner, like a list of search terms or the URL of your landing page, and the tool will return either the search volume for a specific keyword or a list of recommended keywords along with their search volume by month.
As a content marketer, knowing which types of keywords are popular and how search behavior changes during the year is very valuable. It allows you to identify content opportunities and know what times during the year certain content should be prioritized. This is exactly what the Keyword Planner can help you do.
To illustrate how you can use the Keyword Planner to create a content calendar, let's use the “Search for new keywords using a phrase, website or category” option in the Keyword Planner. This is a great feature that can help you get started when searching for keywords related to your website - and, at the same time, it can return keywords that aren’t relevant. For best results, we recommend going through iterations of building a keyword list that includes only relevant search terms. You can then upload this list into the Keyword Planner to get the average search volume per month for each keyword.
Using this feature of the Keyword Planner, along with some simple spreadsheet formatting, can help you develop an effective content calendar. This content calendar can be used to organize the content that you will publish throughout the year and can either be your main content calendar or a stepping-stone to a more robust one.
How to use the Keyword Planner to identify blog post topics
In our previous blog post that touched on the Keyword Planner, our keyword of interest was “diy” or “do it yourself”. We’ll build our content calendar using this keyword once again.
Let’s say that you have a blog that focuses on publishing content about diy topics. Using the keyword planner, we can input the search term “diy” and see what suggested keywords come out from the tool. We can choose to either look at data from the past 12 months (the default setting), or, if you’d like to get the most data possible, you can grab data from the past 24 months, the maximum length allowed by the tool.
Once you click “Get ideas”, the Keyword Planner will return a list of keyword ideas, and will even group similar keywords into Ad Groups that you can use as a content topic. You can use your judgement to decide which groups to consider in your final content calendar. You can also see the trend of average monthly searches during the time period you selected. The image below is an example of what the Keyword Planner returns. You can see the average search volume of all keywords returned graphed over time. Below the graph, you'll see specific ad groups along with some keywords that are included in the ad group.
It’s important to note that the monthly search metrics returned by the Keyword Planner is only counted when someone searches the exact keyword phrase (same words in the same order) that you can find in the results. Although the actual numbers related to a content topic may be much larger due to variations in search phrases, the Keyword Planner still gives a good estimate to the seasonality of a content topic.
At this point, you’ll want to download either an .xls or .csv file of your results or save them to your Google Drive account. I’ll be downloading the file as an .csv. Be sure to segment statistics by month so that you can get a sense of seasonality for your content topics. See below:
Once you open the file, you’ll see something formatted like the spreadsheet below:
You now have all of the data needed to create a content calendar. You can repeat this process with as many different keywords as you’d like, and you can even upload an entire keyword list with search terms related to your business - the best approach, since you’re giving the Keyword Planner more data to use.
Formatting Excel documents to create your content calendar
For the second part of creating a content calendar, you’ll need to manipulate data within a spreadsheet. In the image below, you can see what you get once you open up the spreadsheet exported by the Keyword Planner. Column B contains the keyword list with its respective Ad Groups in column A:
The only columns that are really necessary are ones with average search volume in a given month. If you want, you can delete the unnecessary columns or rename the columns we will be using. The “Avg. Monthly Searches” column can be useful since it allows you to prioritize Ad Group or content topics (whichever Ad Group has the highest sum total of the average monthly searches is the most popular content topic from your keyword list, search-wise).
By now, you should have a long list of keywords and Ad Groups and may feel overwhelmed. That’s okay! This is where the pivot table feature of Excel comes in handy.
You'll want to select the entire table, then create a pivot table by selecting the Insert tab and clicking the Pivot Table button.
I always create my pivot table in a new sheet, which is the best option in this scenario since there is so much data to deal with. In your pivot table, drag or select the Ad Group dimension into the row field. In the columns, select the Average Monthly Searches metric for each month (initially formatted as Searches: Month Year). If you selected 24 months of data, like I did, you’ll have two observations of each month, and you’ll want to find the average of both observations. Using my pivot table, I select each month in order (January 2015, January 2016, February 2015, February 2016, etc.) and create a table that way:
At this point, you’ll have all of your content topics as rows in your pivot table, with the search volume for each month as the column. For the next step, we copy and paste the resulting pivot table into a separate sheet. The final content calendar should present topics as rows and months as columns. Since we have two data points for each month (assuming you grabbed 24 months of data), we need to find the average of the two search volume observations for each month.
Some Ad Groups are better than others
Sometimes AdWords outputs some very general ad groups that are not actionable (like the “diy” ad group). I recommend going through the table and keeping only the rows with Ad Groups that are most useful. Delete the other if you wish.
From the pivot table, we can create a new table with the average search volume for each month, with each ad group having its separate row.
The final step is to use the conditional formatting function for each content topic row. It’s important to conditionally format each row, and not the whole table at once. This end result will be a visual content calendar that you can work off of, like one below.
Questions? Comments? Let me know on Twitter @silverbackstrat.