Keyword Research Checklist for Ahrefs users

Receive an SEO Strategy every 2 weeks

    This keyword research checklist is perfect for Ahrefs users but works for other tools.

    Template included can be used to present to clients or your manager.

    Enter seed keyword into keyword explorer & use modifiers

    To start you will need:

    • An Ahrefs paid for account
    • Google Sheets
    • A calculator on your phone or desktop device

    The first step is to enter a seed keyword into the keyword explorer tool, select your country and search.

    To filter all the keywords with some modifiers, select

    1. Phrase match
    2. Include
    3. Any word
    4. Copy and paste the below modifiers below into the box
    5. Press Apply.

    Problem stage modifiers

    how, what, and, for, with, where, when, for, with, and, issue, fix, improve, increase, reduce, remove, lower, grow

    Information stage modifiers

    example, guide, learn, tutorial, video, calculator, best, top, bespoke, service, color, colour

    (include your own product, brand, service name, category words)

    Evaluation of alternatives keyword modifiers

    review, compare, comparison, vs, versus, alternative, similar

    Purchase decision stage modifiers

    Buy, nearest, cheap, dimensions, size, shop, store, discount, coupon, deal, sale, ebay, amazon, offer

    You may want to add some industry specific keyword modifiers such as size, small, large or gender, left, right etc

    Download : Keyword Research Checklist & Template

    Add relevant keywords to a keyword list

    Filter out particularly high keyword difficulty keywords.

    The level of difficulty you filter for a brand new website depends; I’m choosing 30 in this example.

    Now you can exclude some keywords such as brand names.

    You can also filter out keywords that don’t generate any clicks.

    Now scan through your keyword list

    1. Tick the boxes next to all the relevant keywords for your project.
    2. Click Add to
    3. Keyword List
    4. Select + New List

    Now, name your list and select create.

    Your keyword list will appear at foot of the grey left hand navigation.

    You can of course edit or add keyword modifiers in the include box as you begin to learn some keyword opportunities.

    I found some keywords about a range of topics including watches, gps, gifts and disc golf.

    Bear in mind there’s a limit to the number of keywords you can add to an Ahrefs keyword list; 100 keywords on the $99 plan and 250 keywords on the $179 plan.

    You can create another keyword list if you reach your plan limit or choose to research another seed keyword.

    Prune your keyword list

    Go through your keyword list.

    Remove any duplicate keywords.

    Remove any keywords that do not match the parent topic keyword.

    Remove any keywords with zero or not available clicks.

    Download : Keyword Research Checklist & Template

    Populate the Keyword Research Template

    I have created a keyword research planning template especially for Ahrefs users.

    This involves populating the template with the data from your keyword list.

    Export your keyword list

    The first step is to export your keyword list from Ahrefs and import the data into a blank sheet in Google Sheets.

    There is an option to include the SERPs data in the export; which I decided not to include in my process.

    Now make a copy of the keyword research template.

    1. Select File
    2. Select Make a Copy

    Import the keyword list into the keyword research template.

    1. Select File > Import Data from the menu and choose upload
    2. Drag the CSV file 
    3. Into the upload dialogue or select the file from it’s download location

    Next

    1. Select append to current sheet
    2. Click import data

    Remove or hide the following columns.

    1. #
    2. Country
    3. CPC
    4. CPS
    5. Return Rate
    6. Parent Topic
    7. Last Update
    8. SERP Features

    Add additional columns

    1. Select the tab named Add These Columns
    2. Select cells A1 to G2 like this
    3. Copy the cells
    1. Go to the spreadsheet tab containing your keyword list
    2. Select the cell to the right of clicks.
    3. And paste.

    Your spreadsheet will look like this with 3 arrows and dropdown fields.

    To tidy up the font formatting, select all the cells and then Format > Clear Formatting.

    Or left align the column headers and sort adjust the column width to make the sheet look prettier.

    And finally copy the Ave Traffic, Top Pages, Top URL, Select User Intent, Write Page Title and Add to Website Plan row into all the rows below.

    Add SERPS data

    Now you need to manually enter the SERPS data from your keyword list into each row on your spreadsheet.

    1 Click the arrow on the SERP button next to each keyword.

    2 From traffic column, calculate average traffic to the top pages

    Use a calculator or quickly mentally estimate the average traffic.

    Enter that number on the Ave Traffic cell.

    (sometimes I’ll exclude the number one result where it’s traffic is really high compared to the other pages so I don’t provide unrealistic traffic potential to clients

    In the Do all top pages get traffic, select YES or NO from the cell drop down

    (this should be a yes for all keywords if you’ve filtered clicks >1 in step 3.

     3 Copy and paste the number URL into the Top URL Column.

    This seems like a very slow and boring process.

    However when you start examining the top page URLs and their traffic you can further eliminate some keywords; some keywords may be unsuitable, irrelevant or not have sufficient traffic.

    Your sheet will start looking like this.

    Download : Keyword Research Checklist & Template

    Select User Intent

    I have created a keyword research planning template especially for Ahrefs users.

    This involves populating the template with the data from your keyword list.

    Export your keyword list

    The first step is to export your keyword list from Ahrefs and import the data into a blank sheet in Google Sheets.

    There is an option to include the SERPs data in the export; which I decided not to include in my process.

    Now make a copy of the keyword research template.

    1. Select File
    2. Select Make a Copy

    Import the keyword list into the keyword research template.

    1. Select File > Import Data from the menu and choose upload
    2. Drag the CSV file 
    3. Into the upload dialogue or select the file from it’s download location

    Next

    1. Select append to current sheet
    2. Click import data

    Remove or hide the following columns.

    1. #
    2. Country
    3. CPC
    4. CPS
    5. Return Rate
    6. Parent Topic
    7. Last Update
    8. SERP Features

    Add additional columns

    1. Select the tab named Add These Columns
    2. Select cells A1 to G2 like this
    3. Copy the cells
    1. Go to the spreadsheet tab containing your keyword list
    2. Select the cell to the right of clicks.
    3. And paste.

    Your spreadsheet will look like this with 3 arrows and dropdown fields.

    To tidy up the font formatting, select all the cells and then Format > Clear Formatting.

    Or left align the column headers and sort adjust the column width to make the sheet look prettier.

    And finally copy the Ave Traffic, Top Pages, Top URL, Select User Intent, Write Page Title and Add to Website Plan row into all the rows below.

    Add SERPS data

    Now you need to manually enter the SERPS data from your keyword list into each row on your spreadsheet.

    1 Click the arrow on the SERP button next to each keyword.

    2 From traffic column, calculate average traffic to the top pages

    Use a calculator or quickly mentally estimate the average traffic.

    Enter that number on the Ave Traffic cell.

    (sometimes I’ll exclude the number one result where it’s traffic is really high compared to the other pages so I don’t provide unrealistic traffic potential to clients

    In the Do all top pages get traffic, select YES or NO from the cell drop down

    (this should be a yes for all keywords if you’ve filtered clicks >1 in step 3.

     3 Copy and paste the number URL into the Top URL Column.

    This seems like a very slow and boring process.

    However when you start examining the top page URLs and their traffic you can further eliminate some keywords; some keywords may be unsuitable, irrelevant or not have sufficient traffic.

    Your sheet will start looking like this.

    Download : Keyword Research Checklist & Template

    Write proposed Page Title

    The purpose of keyword research is to identify relevant traffic opportunities but we’re not finished yet.

    The client or the agency need to know what to write.

    And this requires a brief.

    First thing I do before writing the brief is writing out a page title based upon the user intent.

    To do this go to a tool called Thruuu

    • Paste your keyword into the “enter a keyword” box
    • Select your country
    • Select your location
    • Select your search engine
    • Select SCRAPE THE SERP

    The tool will return valuable information about the top pages for this keyword that you can use to write a page title with the user intent in mind.

    Read the page titles again.

    Scroll down to the Title area.

    You can create a page title based on

    • The search query
    • Your reading and interpretation of the top pages
    • The most frequent keywords in the page title

    Eg:

    Search query : disc golf bags

    Frequent keywords : 

    • disc
    • golf
    • bags
    • gotta
    • backpacks
    • sale

    So I’d write my page title as follows

    Disc Golf Bag and Backpacks for sale

    When complete, write or paste the page title into the Write Page Title cell.

    Ps: sometimes writing out the page title and figuring out what the user wants requires more research.

    With the Thruuu tool you can export the SERP data and study the h2 and h3 headers of the top pages.

    And even create your page content outline or page brief using the exported SERP data from Thruuu about a keyword.

    Complete the rest of the page titles for the pages you will include in your project.

    Download : Keyword Research Checklist & Template

    Add to Plan

    My keyword research contained over 200 topics where all the top pages earn traffic.

    A client or agency isn’t going to use every single topic on the list.

    So there’s an Add to Plan column; it’s a simple Yes/No option.

    You can give the spreadsheet to the client with instructions on how to interpret the findings and then choose Yes/No in this cell.

    Or select Yes/No yourself.

    Now you can use this spreadsheet to map these pages to the structure of the website you’re working on.

    Sitemap

    Now you can start to populate a sitemap with your keyword findings with a tool such as https://octopus.do/sitemap/resource/explore

    Content Plans and briefs

    With a sitemap and structure based on pages you have researched you can begin creating the site and the content.

    Summary and Download

    There isn’t one fixed way to do keyword research with Ahrefs; you could add in opportunities you find from competitor and content analysis.

    Don’t just deliver a bunch of keywords and data, deliver an end result from doing keyword research; traffic opportunities, how page content to write and how to structure web pages.

    Download : Keyword Research Checklist & Template

    Checklist