Advanced Keyword Research Template (Complete Guide)
Improve processes and organize your keyword research with this free template. This is an advanced template that partially automates the research process by using Google Sheets, but doesn’t require any technical knowledge of spreadsheets.
Whether you’re building a new website, planning content, or just trying to set up tracking, this keyword research template will make the process easier.
Follows these steps to perform keyword research using the template:
- Determine competitors
- Find competitor keywords
- Prospective keyword research
- Group or map keywords by theme
- Finalize keyword research
Note: This template uses competitor insights and keyword data from SEMrush. If you don’t already have a SEMrush account, get a 14-day trial to create a free account.
1. Determine Competitors
One of the best ways to find keywords is by first identifying competitors. In this example, I’ll use a dog day care business located in St. Louis, Missouri to find keyword opportunities.
Find competitors by performing an organic search.
This will return set of results relevant to your business’s service. In this example, we want to find businesses that are relevant to our service (dog day care) and target location (st louis) – for local searches, this will include Google Ads, Local 3-Pack, and Local Organic Results.
For now, we’ll only be looking at competitors who rank are ranking in the top Local Organic Results…
Pro Tip: Ranking in Google’s Local 3-Pack can generate tons of additional calls for local businesses, and shouldn’t be ignored. Use this Google My Business checklist & audit guide to improve rankings in Local Pack by leveraging competitor insights.
Once you’ve identified competitors, add each competitor site into Column A of the Competitors sheet found in the template.
Once each competitor is entered, a link will be automatically generated in Column B to view each competitor’s organic keyword rankings.
2. Find competitor keywords
To view competitor keywords, click the View keywords link → in the Competitors sheet of the template (see image above).
This will take you to SEMrush’s Organic Research report, which shows all organic keyword rankings for the domain entered in the sheet.
Note: The template will only show keyword rankings for terms that are ranking in positions 1-25. This is to create a refined list of keywords that are highly likely to be generating traffic (or on page 2 of Google and could be generating traffic if optimized).
It’s also helpful to excluded branded terms such as business names.
Pro Tip: For those who are more experienced with keyword research and know (roughly) what they’re hoping to find, it can also be helpful to add “include” filters for keywords that contain partial matches of products, services, or question words for terms with investigational intent.
Once filters have been added and applied, export the keyword data as a CSV file.
Then, import the CSV file from SEMrush into the Import Sheet in Cell A1 of the Keyword Research Template.
Afterwards, repeat this process for each site that was added to the Competitors sheet in Step 1.
3. Prospecting keywords
Once each file as been imported, the Research sheet will be automatically populated with the following data:
- Keyword – Search term bringing users to an analyzed domain via Google’s organic results.
- Volume – The average number of times users have searched for a given keyword per month.
- SERP Features – Any result found on a Google Search Engine Results Page (SERP) that is not a traditional organic results.
Use these columns (see above) to determine which keywords are relevant for your website.
Once you’ve determined a keyword to be relevant and valuable to your website, enter Yes in Column D, and set a target page (or bucket) for they keyword in Column E.
Now, we can better organize our keyword research by grouping related keywords or mapping them to a target page.
4. Group or map keywords by theme
After setting a target page (or group) for your keywords, rename the Page sheet in the template to match your target page / group name.
Once renamed, you’ll have a group of relevant keywords for that topic or target page and the following additional keyword data:
- KD – Keyword difficulty (KD) is an estimate of how difficult it would be to rank well in organic search results for a given keyword. The higher the percentage, the harder it is to rank.
- Ranking pages – The page ranking for each keyword based on Google’s organic search results.
Note: The sheet name must match exactly what is found in Column E of the Research sheet. Otherwise, this will not work.
This process can be repeated for additional target pages or keyword groups by duplicating the Page sheet. Additionally, the formula in Cell A1 may have to be re-copy & pasted for the new page to work (see below).
Pro Tip: This process is also referred to as keyword mapping, which is essential to content creation and any successful SEO campaign.
5. Finalize your keyword research
With this data, you should be able to finalize your keyword research and make data-driven decisions based on the following:
- How relevant that keyword is to your business (e.g. aligns with a service)
- The popularity of the keyword (e.g. it’s monthly search volume)
- SERP features that display for the keyword (e.g. Local Pack or Featured snippets)
- Keyword difficulty (e.g. how hard it is to rank)
- Ranking pages (e.g. which competitor pages are currently ranking in SERPs)
You can add or remove keywords to each group / target page by updating Column D and Column E within the Research sheet.
Pro Tip: This keyword research process can be used to create site structure and to plan / prioritize content.
Next steps after keyword research
Once you’ve finalized keyword research using the template, you’ll likely need to do one of the following:
- Update on-page SEO for existing pages
- Create content relevant to your new keywords
In some cases, an overwhelming amount of content may be needed. Pages or keyword groups can be prioritized based on the search volume or keyword difficulty metrics found within the keyword research template.
This makes it helpful to prioritize which pages should be created first for your SEO strategy. For Local SEO, this can be a great way to determine which local business landing pages to create and optimize with Surfer SEO.
Pro Tip: Don’t forget to add keywords to a tracking software like SEMrush in order to measure progress and track progress. This should be done before publishing new content or make updates.
Following this process makes keyword research super easy and scalable by adding automation and organization to the keyword research process.
And if done correctly, leads to an increase in keyword rankings and website traffic which means…
Happy SEO-ing 🙂
Frequently asked questions
How is keyword research performed?
The best way to do keyword research is by identifying competitors and target queries that are likely to bring traffic and convert visitors. This template will help you do exactly that to optimize your website.
Why is keyword research important?
Keyword research is often one of the first steps to any successful SEO campaign. It’s important to perform keyword research to identify terms that will generate traffic to your website. Keywords research can also help you understand what content to write and how to write it.
How do I choose keywords?
This is different for every niche, but answer each of these questions to determine if a keyword is a good fit:
- Is this keyword relevant to existing content or products / services on my site?
- Does the keyword intent align with my business goals?
- Can I create competitive content to target the keyword?
If you’ve answered, Yes, to all 3 questions, the keyword is likely a good choice to target.