The goal of this project is to identify relevant keywords for your content marketing strategy. Specifically, you’ll:

• Develop ideas for relevant topics to create content about
• Find out which topics users are actively searching for information about and how difficult it is to create highly ranking content for each topic
• Organize keywords into topic clusters for easier prioritization

This project is best completed using a keyword research tool like Ahrefs and Semrush.  We’ll be using Ahrefs but many other keyword research tools have the same features with a different name. They also often offer a free or low-cost trial, including:

• Ahrefs: 1 week for $7
• Semrush: 1 week for free
• Moz: 30 days for free
• Ubersuggest: 1 week for free

To get started on this project, make a copy of our template.

Phase 1: Generate keywords

There are many ways to generate keywords, but starting out, we’ll focus on three.

1. Off-the-dome: Ideate keywords using your brain and creativity
2. Value props: Generate words based on the problem your product solves
3. Autocomplete: Find out popular searches using Google’s autocomplete feature

We’ll walk you through each of these methods. Enter your keyword ideas in the Phase 1 tab of our spreadsheet.

Important note: You may think of or find keyword variations throughout this phase—keywords that have the same intent but use different phrasing. Include these in your spreadsheet.

Brainstorm

List everything you can think of that your target audience would search on Google. It can be tangentially related to your product as long as the person Googling may be interested in your product at some point. Use your intuition.

For example, if your product is a photo editing app, your target audience might search:

• photo editing app
• photo editing software
• cheap photo editing app
• vintage photo editing
• best photo editing app for android
• photo editing app with filters

Value props

Think about the problem(s) your product solves. What would your target audience Google to solve it? Consider the different angles they’re approaching it from.

Continuing with the above example, keywords for a photo editing app’s value props might include:

• how to edit photos on phone
• how to make photos look vintage
• how to make professional photo edits
• how to correct photo lighting
• how to edit photos without photoshop
• filters to make photos look professional
• ways to edit photos on phone

Autocomplete

Begin typing some of the keyword phrases you previously generated into Google and see what its autocomplete feature suggests. (You may not need to type the entire keyword phrase, as Google will give suggestions with just one or two words entered.) These suggestions are based on what users search, so you can easily find out other keywords relevant to your product.

Not all of Google’s autocomplete suggestions will be relevant to your company or product. Look for keywords that are relevant and copy these into the third column of the Phase 1 tab—these are the keywords you should focus on targeting.

Phase 2: Collect keyword data

The keywords generated in Phase 1 are a good starting point for what users might search, but without any quantitative data, they’re only guesses at best for what users are actively searching. (Google’s autocomplete suggestions do, however, give us a clue about which keyword phrases may be more popular.)

Copy each group of keywords, one at a time, and paste them into Ahrefs’ Keywords Explorer tool (or the equivalent feature in another keyword research tool). Pasted directly from the spreadsheet, each keyword should be on a separate line. Since this keyword research is for SEO, be sure to select Google from the list of search engine options.

Then click the button with the magnifying glass.

Ahrefs will return keyword data, but not all of your entered keywords will necessarily match. You may see a message that says something like:

That’s fine—it may be that some of your ideated phrases are not very popular or searchers phrase it differently. You can identify these later on as you go through the data.

For now, select the keywords that do have data. Then add them to a new list in Ahrefs.

You’ll continue building on this list in Phase 3, and eventually export this data to the keyword research spreadsheet’s Phases 2-4 tab.

Again, most other keyword research tools generally have the same functionality, making it easy to collect keyword data in a list and then export them. You can go through the same steps here even with a different tool.

Phase 3: Keyword expansion

We’re going to build on the keywords you came up with in Phase 1. On Ahrefs, click on each of the options under the Keyword ideas heading in the sidebar:

• Matching terms: Shows other keywords that match or closely resemble your top inputted keywords (the ones with the highest search volume). Since this list is focused on matching, it tends to be shorter than the list of related terms.
• Related terms: Shows keywords related to your top inputted keywords. Since this list is more inclusive, not all keywords will be relevant.
• Search suggestions: Shows autocomplete suggestions for your top inputted keywords. Think of this as a more detailed and expanded version of Google’s autocomplete feature.

You can also click on Ahref’s Parent topics button to see keywords sorted according to a larger umbrella topic. Browse through all of these keywords and add those that are relevant to your product/company to your existing list on Ahrefs.

There’s a lot of data to take in—Ahrefs may have more than a hundred pages of matching and related keywords. You don’t need to go through all of them. The goal is to collect only the highest-leverage keywords for your brand. You can quickly identify these based on three pieces of information:

• The keyword itself: Is it relevant to your product? Could a piece of content about a particular keyword reasonably direct users toward your business?
• Search volume: You should target a variety of keywords, not only low-volume or high-volume ones. But as a starting point, focus on keywords with a search volume between 50 and 5,000. This means only looking at the first page or two of keyword data rather than combing through 50.
• Difficulty: (Ahrefs uses KD, short for keyword difficulty, here.) Focus on keywords with a difficulty score below 80. A lower difficulty score suggests a keyword will be easier to rank for, but this is only a guideline at best.

Keyword research can be very intensive, showing you new angles and perspectives for your content. Some companies like to dive in deeply all at once, and create a long-term editorial calendar based on their keyword data. Others prefer to revisit keyword data in shorter, regular sessions, like on a quarterly basis, since new topics may emerge.

We recommend the latter approach so that you can get a better sense of how search queries in your niche may change over time.

For this project, once you reach about 100-250 keywords, export it as a CSV file. There are many columns of data, but the keyword, search volume, and difficulty are most important. Copy these columns into the Phases 2-4 tab of your spreadsheet.

Mining for keywords

Besides your keyword tool’s suggestions, there are other tactics for finding relevant keywords. Try these strategies to find other keyword opportunities.

• Competitor URLs: Enter your competitors’ URLs into your keyword research tool. This could be their homepage or any relevant blog posts. Your keyword tool will show what keywords your competitors’ pages rank for. For Ahrefs, you can do this with the Site Explorer feature, under the Organic keywords section.

Grab the keywords that are relevant to your business and can funnel users toward your product.

• Online communities: Look for relevant communities and forums where people discuss topics related to your product, like Reddit and Quora. Then enter their URLs into your keyword tool to find out what keywords they rank highly for. You’ll need to set filters to narrow your data.
  • Example: In Semrush, if you want to find out all the keywords that a subreddit and its posts rank for, you should input the subreddit URL and choose “Subfolder.” You’ll then see data only for posts in the specific subreddit (rather than all posts across Reddit).

• • Look for pages of online communities that rank highly for keywords; these represent great opportunities for creating more targeted landing pages or blog posts for that specific keyword.

Here’s an example of the second strategy in practice.

According to Ahrefs, one Reddit post at r/Entrepreneur ranks #4 in search results for the keyword phrase “starting a business with 50k”.

Googling the phrase confirms this, showing Quora and Reddit pages at the top of organic search results.

There’s clear interest in this question—consider how many questions have been asked about it on two platforms—but few landing pages and blog posts dedicated to answering it. In this case, keyword research has uncovered a great keyword/content opportunity for those in this particular niche.

Phase 4: Group keywords

The last phase of this project revolves around sorting your keyword data. At this point, you should have exported your list of relevant keywords, and copied and pasted them into the Phases 2-4 tab of the keyword research spreadsheet.

Now it’s time to organize them.

During your keyword research, you likely noticed keywords that were variations of each other. In our example, there were many phrases related to “how to edit photos on iphone.” These keywords all have the same goal, so it’s not necessary to create individual pages for each variation. Instead, you should group these related keywords, as a single piece of content can address them all.

We’ll organize keywords into clusters so it’s clear how many individual topics exist and what pages should be created for them.

Here’s how we approach this process:

• Type: Use this field to designate whether a keyword is the primary, secondary, or tertiary keyword for a cluster.
  • Primary: This is generally the phrasing with the highest search volume; its popularity indicates that more users approach a particular topic in this way. There should only be one primary keyword per cluster. Example: Users more commonly search “how to edit photos on iphone” (4,600 searches per month) than “how to edit photos on iphone to look professional” (400 searches).
  • Secondary: Secondary keywords have the same user intent as primary keywords but have a lower search volume. They’re usually the less common ways of phrasing a particular query.
  • Tertiary: These keywords have the same overarching intent as primary and secondary keywords but are slightly more nuanced. The user may be looking for something more specific in a topic. Example: The keyword phrase “how to make photos look vintage in photoshop” is specific to photoshop—it is a tertiary keyword for the more commonly searched phrase “how to make photos look vintage.”
  • More research needed: We’ve included this option for keywords that appear as one-off topics or don’t seem to belong to any keyword cluster. These may be topics that require their own blog post but more research is needed to validate this.
• Page: Use this field to group keywords by specifying the topic they belong to. You can also think of this field as the string of words that will be used for your URL’s slug later on. When you sort the spreadsheet by page, you can clearly see all of the keywords that belong in a single cluster.
  • We often use the primary keyword here, but not always. That might be because the primary keyword has a typo or grammatical error.

Your organized keyword research acts as a basis for your SEO content strategy. Use this spreadsheet to prioritize which topics to create content for first (based on search volume and difficulty) and what keywords should be included in each piece of content.

You can also cross-check each keyword cluster’s topic by searching them in Google and seeing what results appear; this will help you to understand the main goal behind these searches.

For example, in our sample data, we can see that a lot of users want to find out how to make their photos look professional. This is a fairly general query—it could refer to posing and staging for photos, not just editing. But we can see that the lower-volume secondary and tertiary keywords related to this topic refer to editing apps and programs like Photoshop.

To validate this, we also search for this topic in Google, and articles like “How to Edit Photos like a Pro in 10 Steps” appear. This is a good sign that the goal of these keywords actually revolves around photo editing.