Creating a Framework for Content Creation: Keyword Research

This is a Standard Operating Procedure for doing Keyword Research. This post was originally a Google Doc – if you want to read the Google Doc instead (and Make a Copy) feel free to do so. If you’re done with this SOP, you can move on to the Content Creation and Management SOP.

Figure Out What You’re Looking For

The first part of KW Research is coming up with Broad KW Ideas to look into.

  • First thing you should do is ask yourself the following questions
    • What niche are you in?
    • How do you currently make money?
    • How do you want to make money in the future?


If you had a site selling a course that teaches French, while KWs about french recipes might be tangentially related and easy to rank, they’re unlikely to convert organic visitors into paying customers. Have your eye on your target (profit) when doing KW research.

If you sell products either as an affiliate or as a product creator, you should think about what KWs will likely convert well, not just on volume or CPC.

Generating an Initial List of KWs

There are a number of ways to generate initial KW lists. Here are a few:

Competition Analysis

  • This method is much easier if you have access to paid tools like SEMRush that scope out the competition for you. Here’s how we do it with SEMRush:
    • Find a website in your niche that has high SE Traffic, yet a low DA /Domain Rank (I prefer Domain Rank in Ahrefs). I like to focus on smaller sites that have a DA/DR that is no more than 5 above my site.
    • Focus only on the terms they are ranked on the first page for. Go through and record these terms. Most smaller sites will be getting most of their traffic from just a few articles.
    • Rinse and repeat until you have about 30 keywords.
  • It is also possible to do it manually. Here’s how:
    • Look up the SERPs of KWs you’re already ranking for. Find some sites that are in your niche, ideally find some sites that are not massive players, they should have a link profile that’s not much stronger than yours (IE no more than 5 DA more than your site).
    • Scope out their content, paying particular attention to article headers and title tags. These will often let you know what KWs they are targeting. Most websites will be targeting key phrases – try to work out what these are.
    • Collect a batch of about 30 KWs that you want to do further research on


If I was doing research for a site about learning french, I might check out the SERPs for ‘Learn French Online’ and find the site (ranks bottom of page 1). I decided that this was a good fit because some of the higher rank sites look very authoritative and less niche focused. Looking at their content briefly, I see that they’re probably targeting the following KWs:french tutorials, french listening resources, informal french slang, french phonetics, etc.


  • Think about the content that is already on your site. What areas does it make sense to expand into? Pick an area that makes sense. You can then follow that thread by making use of free or paid tools.
    • The obvious free tool to use here is the Google Keyword Planner. If you put in the broad area that you want to find KWs for, Keyword Planner will generate a list of up to 800 related KWs for you. I suggest you put a basic filter to not bring up keywords with less than a few hundred searches. You will need to prune this down later, but it’s a good base list of KWs to have.
    • Paid tools like LongTailPro, and or any other KW Tool that you prefer will typically also include a function that allows you to generate a list of related KWs based on a single root KW.


With my theoretical french language site, I’d probably want to hone in on my niche – I put learn french into the Google KW Tool and got a list of 800 KWs to filter. Not all of these will be directly relevant to learning french, but a list of 800 KWs is plenty to start with.

Filtering your KW List

Quick Filters

Exactly how you decide to filter your KWs will depend on the strength of your site and how confident you are that you can attract links.

  • First, make a copy of this spreadsheet, then copy and paste your initial list of KWs into the sheet (tab) named “KW: First Filter”. You’ll be using your copy of this spreadsheet throughout this process.
  • These are some of the first filters we’ll run on our KW lists – note that you can do some of these filters in the Keyword Planner itself, but I’m just used to working in Excel/Google Sheets.
    • Single word KWs (these are typically too competitive)
    • Irrelevant KWs and KWs that are only tangentially related to your niche.
    • Branded KWs where people are searching for a page on a specific site ( e.g french grammar wiki – rule this out because even if you rank your CTR will be low)
    • KWs with too little search volume. For a small or new site, you can use 100 search volume as a minimum. You may want to raise this minimum if you’re working on a larger site.
    • KWs that you don’t think will convert well for the product you’re selling if you’re monetizing via a specific affiliate offer or your own product.
  • Obviously, there is no need for you to use the “Pass?” and “Reason” columns. These are just to provide a clear example of how we complete our first filters


The template spreadsheet has an example sheet with 200 KWs relating to ‘Learn French’. I’ve filtered out some KWs based on the criteria above and marked them accordingly. Obviously, you don’t need to provide reasoning when you’re doing your own filtering – I included the extra columns to make it clear. The KWs that are not filtered out will be the KWs we’ll use in the next stage. Keep in mind, I truncated the original list of 800 to 200 KWs for brevity’s sake.

Difficulty Filter

The next step is filtering for difficulty. The metric that we’ll use to filter out KWs that are too difficult is Average PA. This metric isn’t super robust, but we’re using it here because

  • It works as a pretty effective initial filter .
  • You can access PA/DA metrics for free, and we want this KW research method to work for everyone.

If you have your own preferred metric or tool that you use to measure KW difficulty, feel free to replace Average PA here with your favorite metric.

If you’re happy using Average PA and you already have a tool that can generate Average PA for KWs in bulk, you can ignore the three alternatives below. If need a tool to bulk-generate the average PA you can choose one of the following options:

  1. For those of you without access to paid tools, you can opt to sign up for a one month Moz Free Trial and use their Keyword Difficulty Tool to find Average PAs of SERPs
  2. You could download the Moz Bar and do it manually for each SERP
  3. Alternatively, we’ve devised a DIY way to calculate average PAs on SERPs. The instructions on how to do this can be found here – it’s image heavy so we created a separate doc.
  • Once you’ve got the hang of calculating Average PAs, you can start running through the filtered KW list that we created earlier.
    • Open up your copy of the template spreadsheet and go to the sheet named “KW Secondary Filter” – paste your filtered KWs from the first filter sheet here.
    • Start working on filling out the Average PAs. Don’t worry about the other columns for now. Make sure you fill out the sheet as you go along – there’s nothing worse than not noting your results and having to go back and redo a KW.
  • The rule of thumb that we use is you want to filter out KWs with average PA higher than the DA of your site. If the site you’re working on has DA under 30, filter out KWs with average PA higher than 30.
    • If you’re using our DIY method to calculate average PAs, I’d recommend starting with lower volume KWs (less than 1000) and working your way upwards. High volume KWs tend to have higher difficulty. Start with lower volume KWs and you’ll be able to find some KWs that pass the Average PA filter. This will help you uncover some immediately targetable KWs.
    • The manual method is obviously much less efficient than just running a set of KWs through a tool, but it’s free, and we wanted this process to work for everyone, not just those who have access to tools.


The tab named “Example KW Secondary Filter” shows all the Average PAs for the KWs that passed our initial check.

Revenue Potential

The next thing we’ll do is try to work out the revenue potential of each KW based on its volume, conversion rate, and profit per conversion. We calculate this as follows:

Visitors to Page = CTR from SERPs x KW Volume

Visitor Value = CTR on Adsense x 66% of CPC OR Conversion x Profit per Sale

Revenue Potential = Visitors to Page x Visitor Value

The default settings that we’ve used are:

  1. SERP CTR: 20%
  2. Adsense CTR or Conversion Rate: 1%
  3. Monetization Model: Adsense
  4. Product Profit/Commission: $20

You can see and adjust the settings on the Settings tab – your own copy of the spreadsheet will have a settings tab that you can play with.

Spreadsheet Usage Notes: On the settings page, if cell B5 is set to ‘Adsense’, revenue will be calculated based on the ‘Suggested Bids’ column, which is unique to each KW. If cell B5 is set to ‘Product’, revenue projections will be based on the profit/commission per product sold, which is set at $20.

On the sheet ‘KW Secondary Filter’, column L can be used to override the default CTR. If you enter any % into an cell in column L, the CTR column will change to match it.

The idea here is that if you’re using adsense to monetize, chances are your CTRs will be similar across the board, but your CPCs will vary based on the KW.

On the other hand, if you’re selling products (either affiliate or your own), your profit per product will be stable, but your conversion rates for different KWs will change.

We’ll work on calculating revenue potential on all the KWs that pass our Average PA filter. Keep in mind that these revenue potentials are just assumptions – this is a relative metric as opposed to an absolute metric, you use it to compare different KWs to each other, not to establish exactlyt how much any given KW will earn you per month.

  • Adjust the settings in the spreadsheet.
    • If you’re selling a product rather than using Adsense, change cell B5 on the settings sheet to ‘Product’
    • You can also adjust CTRs individually for each KW using the CTR Override column. You only really need to do this for KWs that pass your Average PA filter (marked green)
  • Sort your KWs by Revenue Potential to see which KWs are both rankable and profitable.

Congrats! You should now have a couple of base Keywords to target.


You can refer to the “Example: KW Secondary Filter” sheet to see a fully filled out sheet with revenue potentials calculated.

Finding KW Concept Add-Ons

We no longer live in a world where micro-targeting a few specific exact match KWs makes sense. It’s not necessarily because this strategy no longer works – you can still target exact match KWs with below average content and rank for them. However, in terms of ROI on both your time and your money, it makes more sense to try and create content that is above a certain quality threshold.

This is doubly true if you’re a website buyer – low quality content can affect the long term value of your asset by damaging your site’s reputation and potentially putting you at risk of a Google penalty.

So, instead of focusing on the exact match KW, we’ll focus on KW concepts. Your target might be french conversation.

If you were targeting that as an exact match using the old-school method, you would try to include French Conversation in your article X amounts of times to reach Y Keyword density.

Instead, what we’ll be doing is thinking of the KW as an umbrella concept – so conversation in french and french conversational dialogue would all fall under the same umbrella concept. We’re targeting the concept rather than the exact KW.

Another thing that we’ll be doing is trying to find out what information Google thinks is relevant to the KW Concept of french conversation and serving up that related information to Google on a platter.

We want to reverse engineer Google to see what information they think is relevant to our KW concept. We then create content that’s targeting not just one specific KW, but a whole range of ideas relevant to that KW. How do we do this?

It’s easy – Google tells us what they want. Here’s a few ways to work out what information Google wants to see for each KW:

  • Before you start, make a copy of this new spreadsheet.
  • We’ll be using this new spreadsheet in this section as well as in the following SOP about content creation.
  • Search one of your target KWs into Google
    • The first way you can find related ideas is to scroll to the bottom where it says “Searches Related To” – right off the bat, there are 8 KW ideas that Google believes are relevant to our target KW.
    • Another way is to take a look at the pages that are currently ranking. See what ideas and topics those pages cover on top of whatever is directly related to your target KW and note these other KW concepts down. In particular, take a look at Page Titles and Meta Description tags – these often give away a lot of useful information.
    • Use Google Instant (that’s where Google instantly gives you suggestions based on what you typed into the search bar)
    • Use Google Adword’s Ad Group Ideas to see what other KWs Google groups together with your target KW – when doing this, you should actually click into the group and see which KWs fall under which semantic umbrella in the mind of Google.
  • Gather up all these KW concepts that you’ve found and fill out the sheet “KW Target & Addons” – it’s more or less self explanatory. Check out the example if you’re not sure what to do.

That’s pretty much it for the KW Research portion – if you want to see what we do with all these juicy KW concepts to target, check out our Content Creation SOP.


If we’re targeting the KW concept french conversation, we’ll likely naturally pick up a bunch of LT KWs that fall under the same umbrella like conversations in french or french conversation practice depending on what our content covers. You can take a look at the example sheet to see how we record down our add-on KW concepts.

The rule of thumb for long tail KW volume is that on average it will add up to be equal to the volume of the root KW. So French Conversation has 1000 volume, all the KWs that are Long Tails of French Conversation and it’s supporting keyword concepts will have volumes that add up to 1000.

We’re looking for Add-On KW Concepts to include in our article.

First we’ll take what Google explicitly suggests:

Keyword Research SOP - Searches Related To

Next we’ll scope out the competition.

Keyword Research SOP: Scoping Out Competitors

From the above screenshot, I got the following KW concepts:

French Phrases and Expressions

Translations of common words and Phrases

Common conversational phrases in french

After a brief glance at the pages that are currently ranking, I see that many of these pages discuss french phrases and expressions and common conversational phrases in french, and french translations of common words and phrases.

Finally,we’ll use Google Instant:

Keyword Research SOP: Using Google Instant

All in all, we’ve found that a page targeting the KW concept french conversation should also include information about the following things:

french conversation topics

french conversation starters

french conversation examples

french conversation audio

common french words, phrases and expressions

french translations of common phrases

beginner french conversations

waiter/customer conversation in french

french conversation practice

french greetings

french dialogue videos

Again – we aren’t trying to target these KWs specifically – we’re targeting the concepts. We’re not going to try and force mentions of certain KWs. Instead, what you’ll see in the Content Creation SOP is how we break articles down into sections and subsections in order to target these KW concepts.


Spread the love

Leave a Reply 1 comment

John - May 16, 2016 Reply

Indeed a great post about keyword research.

It is true that keyword research is the very 1st step to target the right traffic from search engines like Google. I have seen so many people who never does keyword research and always keep themselves busy in writing articles on different different random keywords which is not good.

If We really want to get potential traffic which can converts easily then we must have to target the right keyword.

For a productive work, every expert suggest to target long tail keywords with having low competition. Because long tail keywords with low competition are easily to rank rather than short tail high competitive keywords.

I always use Google Keyword Planner to check the keyword search volume and Long Tail Pro for keyword competition score. I am glad that You have covered such an indepth article on keyword research.

Thanks for sharing it with us. 😀

Leave a Reply: