Analytics with Ottimo
Topic strategy & tracker: how-to & template
Identify the topics that appeal to your target audience
Stop generating topic ideas out of thin air. (We’ve done it that way. Save yourself the trouble.)
Instead, take a strategic approach to topic selection and production. It will improve your ROI (and morale) all around.
This template integrates several content strategy tasks in one place:
- Choosing topics for content campaigns
- Generating individual blog titles within each topic
- Managing production of that blog content
Click the button below to duplicate the template into your Google Drive
Why we created this template
We use this template to guide our clients through every phase of content strategy, from the initial brainstorm sessions to ongoing library management.
It enables our clients to find topics that are in the sweet spot:
- Your readers are eager to learn about it
- You have unique expertise to share about it
And it does this with real data. No more guesswork.
What you need to get started
- Keyword position tracker: we use Google Search Console
- Keyword research tool we use the keyword magic tool by SEMRush
- API tool (optional): we use the SyncWith extension for Google sheets
How this template works
Completing this template involves some research and data collection.
You’ll start by creating a long list of potential topics, based on your company’s marketing plan as well as research into the language used by your competitors and target audience.
Next you’ll assign three types of data to each potential topic:
- Search volume (pulled from a keyword tool)
- Competition (pulled from the same keyword tool)
- Relevance (assigned by your team)
This data is weighted by the template to create a final list of suggestions. From that list, you’ll choose a few topics to begin creating blog titles.
Step-by-step: using the Topic strategy template
Gather your topic research
The first step is to fill out the Topics tab.
Column A is where you will enter the topics you’ve decided on.
Column B can be used if desired to keep track throughout the process which topics you would like to discuss, mark as a campaign, discuss, or exclude. This can be updated throughout the process as you discuss relevance with your team.
Assign relevance scores
Column C is where you will give each topic a relevance score.
This is done on a scale of 1-5, 5 being the highest. This is very specific to each company and could vary greatly from one another.
One way to look at it is to weigh how each topic relates to the audience’s interest and the client’s ability to provide/help the audience with it. Also important to take into consideration are how closely would your company like to be tied with the topic? Where would you like to rank for each topic? How closely can you tie your content with the topics.
If you have several team members weighing in on the relevance of topics, you can have them vote for each using columns F, G and H. An average score (column D) as well as a standard deviation score (column E) will be created by the tool.
Standard deviation numbers can be very helpful in sparking discussions within your team regarding marketing decisions.
Column I is available for any info or notes you need to jot down.
Generate keywords from topics
The next step is to generate the data. This is done at Ercule using SEMRush - each topic is entered into a keyword tool (at Ercule we use semrush.com). You will also need to pull volume and competition stats for each keyword. Volume is essentially the demand for content of any given keyword, and is more or less an estimate whether you manually come up with the data, or use a keyword tool.
Competition is the existing content already out there, and it’s validity. This is also an estimate, but can be very helpful when determining which keywords you may have a good chance ranking in.
Adjust metric weightings
On the strategy tab, the keyword data is automatically filled in once the above steps have been completed. The top of the spreadsheet will display high volume (column E), low competition (column F), and relevance (column G).
You are also able to adjust the percentages for each of these columns. Changing the percentages will change the rankings in the spreadsheet. When you are just starting out, increasing the percentages in the relevance and competition columns can be very helpful. If you’re not so new to the game, you may want to up the percentage for volume to make that a priority on the spreadsheet.
Discuss priorities and build SEO campaigns
Once you have a list that seems to be a good fit, go through and weigh the pros and cons of each. How do they relate to your other content? Do they line up with your current product or direction? This is ultimately a list of recommendations to help you choose 2 or 3 topics to start brainstorming what could be written and outlined.
Everything we have to say on SEO, content performance, and content analytics – in convenient email form.
(Having doubts? Here's a recent issue.)
- Topic strategy & tracker: how-to & template
- Why we created this template
- What you need to get started
- How this template works
- Step-by-step: using the Topic strategy template
- Gather your topic research
- Assign relevance scores
- Generate keywords from topics
- Adjust metric weightings
- Discuss priorities and build SEO campaigns