Free Keyword Research Template and Step-by-Step Guide

Updated: September 2021

Here at Jellyfish Training, we believe in the power of effective keyword research. Whether you’re an SEO beginner or specialist, our free keyword research template in Google Sheets will help you focus your research, whatever your level.

In this guide, we will walk you through each step of the research process. We’ll cover how to do keyword research through to analysis of keyword ranking and content mapping. To get started, open the keyword research template . This version is view only, so you’ll need to make a copy. You can do this by going to File then Make a copy

This guide forms part of our SEO Standard course. If you’re looking to elevate your SEO strategy and master the basics of search engine optimisation, take a look at the range of SEO training courses delivered by our in-house experts.

How to conduct keyword research using our free keyword research template via Google Sheets

Once you’ve made a copy of the template, you’re ready to jump into keyword research. To make sure you get the most value from this guide, we’ve divided it into seven steps:

1. Selecting your themes
2. Creating your seed list
3. Building out your keyword list using Google Ads
4. Analysing and refining your list
5. Intent analysis
6. Final keyword selection
7. Keyword ranking and content mapping

Step 1: Select your themes

Before you dive into keywords, you first need to consider themes. This is an important step that will help steer your keyword research in the right direction. So, to understand your themes, you need to think about your products or services and your customers. Then identify the core topics or messages of your website and marketplace.

Here’s an example. A skincare brand geared towards the premium marketplace with eco-friendly goals could identify luxury face cream, sustainable body lotion, and organic skincare as their themes.

Depending on your business, your website may be geared towards one theme only or a variety of themes. Once you’ve determined your theme(s), you can list these in the Seed List tab on your template.

Step 2: Create your seed list

With the list of themes identified, it’s time to think about the search terms your ideal customers might use to find the products, services or information you offer.

Open your template and add the keywords you feel your target audience might use to find your website, starting at your theme and then branching out. It’s also important to consider all three types of queries:

• Navigational queries : a search query with clear intent, as the user is searching for a specific website or vendor. These queries are harder to rank for if you do not own the brand.
• Informational queries : a search query where a user is looking for information, such as ‘tips to create a better SEO strategy’.
• Transactional queries: a search query that can include brand names or appear generic, and the user intends to complete a purchase.

Step 3: Build out keyword lists using Google Ads Keyword Planner

This next step focuses on finding effective keywords using your seed list.

Access the Keyword Planner tool
You will need access to your Google Ads account or create a new account using any Google account. Once you’ve logged in, go to Tools at the top of the page and select Keyword Planner from the dropdown menu.

Expand your seed list
The Google Keyword Planner will take your seed list(s) and expand on them by providing related search terms users might use to find a website or webpage like yours. Here’s what you need to do:

1. Select the Discover new keywords option.
2. Copy the keywords from your first seed list and paste them into the ‘Your product or service’ field. Make sure that the targeting options match your intended geographical and language targets.
3. Once you hit Get Results , Google will return average monthly search data for keywords within your seed list, plus all of the terms Google considers related.
4. Click the blue arrow in the upper righthand corner within the Google Keyword Planner to download your keyword list into a spreadsheet.
   

Step 4: Analyse and refine full keyword list

With your spreadsheet (.csv file) downloaded, you’ll see several columns of data. You’ll want to focus on three columns only: ‘Keyword’, ‘Avg. Monthly Searches’ and ‘Competition’. Unclutter the sheet by deleting all other columns.

Now, explore the keywords appearing in the list. You’ll find some seem irrelevant, off-topic or provide too little search volume to warrant inclusion in your final keyword selection.

Generally, the more searches a keyword receives, the better because we know people are searching this particular query. But the higher the search volume, the greater the competition, so we don’t want to select high search volumes only.

On the other hand, keywords identified as ‘low search volume’ receive very little search traffic on Google. These can be identified as keywords generating under 250 searches a month. This low search volume indicates these keywords may not be relevant to our target audience and won’t result in traffic to your website.

We recommend aiming for keywords with high-medium search volumes in your initial research. Occasionally, low difficulty keywords can benefit your ranking in search engine results pages (SERPs), as they’re usually far less competitive; you can come back to these later if needed.

Finally, refine your list of keywords by deleting those we’ve identified above as either irrelevant or with a low search volume. You now have the final keyword selection, otherwise known as the ‘clean list’.

Step 5: Intent analysis

It’s essential in SEO to make sure the pages you are targeting for any particular keyword match the query’s intent. To help you establish whether keywords should be targeted on landing pages, supporting pages, or informational pages, it’s useful to organize your keywords by commercial intent.

Firstly, create a new column in your spreadsheet and mark the key phrases that directly match your core products or services with ‘high’ conversion intent. Where a user could be interested in your product or service but isn't likely to convert, mark these as ‘medium’. If the query relates to your audience but, at this stage, they're probably not interested in your offering, mark these as ‘low’.

Step 6: Final keyword selection

Your ‘clean list’ will include all of the keywords that you should consider targeting as part of your campaign. Naturally, some of these phrases will be of greater value than others – some will be ‘vanity’ keywords while others will be ‘long-tail keywords’. Here is how we define these:

Vanity keywords: Broad phrases representing an entire industry or topic that are hard to rank for but have higher search volume.

Long-tail keywords: Longer, more specific keyword phrases that can be easier to rank for but have lower search volume.

So, to refine your keyword selection further, it’s a good idea to split your ‘clean list’ into the following groups:

1. Primary Opportunities - Keywords listed in your final selection that provide the most desirable opportunities for conversion intent and search volume. Your website will typically look to focus on these primary opportunities.
2. Secondary Opportunities - Alternative (often long-tail) keywords that provide variation to the primary opportunities. These keywords will typically generate less search volume but still offer relevance, although possibly lower conversion intent, and are valid for inclusion within your SEO campaign.
3. Tertiary Opportunities - Further keyword alternatives that provide the least lucrative opportunities in terms of search volume, and of medium-low conversion intent. In most cases, these will be least targeted within an SEO campaign. These phrases are those you're likely to target with supporting content or blog posts.