Branding Questions and Answers

Keyword Research Template & Step-By-Step User Guide

This article walks through how to use the free template below to research, categorize, and prioritize target keywords.

SEO and keyword research has changed dramatically and I've seen first-hand how much more difficult keyword research has become over the last few years.

As we explored in a recent article on Latent Semantic Indexing (LSI) keywords, content strategies must evolve past the old model of targeting one keyword per page with thin or low-quality content (mediocre content won't do either).

With the saturation of content on nearly every topic imaginable, brands who are looking to win in the search rankings need to not only create uniquely valuable content, but it needs to be uniquely comprehensive as well.

Why? Because people (and Google) love in-depth answers to their questions. An analysis of a million SERPs revealed that the average word count of a first-page result on Google is 1,890.

*Note: I'm not saying that content should be intentionally drawn out longer than it needs to be. Content should be as concise as possible, but as thorough as needed to fully answer all aspects of a given question.

So, if our content is intended to aim for quality and comprehensiveness, we can leverage keyword research to support our content strategy by:

Identifying organic search opportunities around specific phrases and core underlying topics
Categorizing similar questions that our audience is asking so that we can create content efficiently and organize it intuitively on media platforms
Prioritizing our editorial efforts around content pillars with the most search volume and lowest competition

But in order to do that, we need a pretty advanced spreadsheet - the common static Excel template you see on the internet just won't get the job done. We need all the bells, whistles, formulas, and filters. Fortunately, we built one.

And with that, we present the keyword research template we use to guide our clients' SEO and content strategies.

👉 Access the Free Keyword Research Template Here 👈

For a step-by-step walkthrough of how we use this template, read on.

Step 1 - Create a master "Seed List" of Keywords

This is the brainstorming phase. Start by listing every possible keyword you, your team, and/or your client can think of that could possibly be relevant to your product or service in column A.

The goal here is to quickly build as big of a list as possible, so don't worry about analyzing them yet. Just get the keywords listed out. Typically we'll start with ~200-500 depending on the scope and size of a client's website, but this number can vary depending on your project.

As you compile your list, keep a running tally of potential high-level categories that similar keywords might be grouped into in column C.

keyword-research-ideas

Keyword inspiration can also include recommendations from various tools (Moz, SEMRush, Google Auto-suggest, etc.). For more tips on keyword research, check out our post on LSI Keywords.

Step 2 - Generate and Import the Keyword Data to Determine Demand and Competition

Once you have a large list of keywords, import them into your tool of choice (we use Moz and SEMRush) to get the estimated monthly search and competition data.

Once you can export the data from your keyword tool, paste it into the spreadsheet tab labeled "Filtered Keyword Data" and be sure to align the data correctly within columns B through E. For now, Columns A and F should be blank.

If you have more than 500 keywords you'll need to simply add as many rows as necessary above the summary row on row 500.

Step 3 - Manually Assign Keyword Topics

With all of your keyword data in place, it's now time to go through and manually assign a topic to each keyword in column A. It's critical that all topics are spelled the same. Because of this, we use the data validation feature to create a drop-down selection.

To edit this drop-down with your own topics, highlight all the cells in column A, navigate to Data > Data Validation, and change your inputs in the Criteria field.

** Pro Tip: You'll see there is conditional formatting to quickly identify the keywords with low competition. I tend to use the filter in cell A7 to select specific topics, and then use the filter in cell C7 to sort by the largest to smallest search volumes. **

As you assign topics, you'll see the aggregated topical data appear dynamically in the "Topical Overview" tab. This tab is populated completely by the "Filtered Keyword Data" tab, so there is no need to adjust any of the data here.

Step 4 - Identify High-level Topics with the Most Opportunity

Now that your targeted keywords are organized by topic, it should be pretty obvious where the opportunity lies.

The topics that should be prioritized are those with the highest search volume and lowest competition.

Step 5 - Create and Iterate

Now it's time to start creating pillar pages and content clusters around those topics. But remember, this template is intended to be a living and breathing document that should be updated as you create content and discover new ideas.

In the "Filtered Keyword Data" use column F to identify the keywords that you have created content for. Ideally, you're using a tool like Moz or AHrefs to track your rankings so that you can review how effectively content is ranking for a given term over time.

** Pro Tip - If you use a sales and marketing intelligence platform like HubSpot, you can even track how each content cluster drives new leads and/or revenue. **

Extra Credit! Evaluate Your Existing Content with an Automated Content Audit

Before beginning any new campaigns, content, or website initiatives, I highly recommend beginning with an audit and evaluation of your existing content. We've found that significant results can come from simply updating existing content - you don't always need to create something brand new.

Either way, this audit will give you a quantitative assessment of all the webpages on a domain and provide visibility into the content that’s driving results for your business and which pages might be holding you back.

👉 Access the Free Content Audit Tutorial Here 👈

----