Keyword Research Template: How to Build & Use One (Free Sheet)

Written by

in

A flat illustration of a keyword research spreadsheet with search volume and difficulty columns on a laptop screen

Most keyword research fails not because the keywords are wrong, but because they end up scattered across notes, screenshots and half-finished spreadsheets. A keyword research template fixes that: one sheet where every keyword lives with its numbers, its intent and the page it belongs to. This guide shows you how to build one, which columns actually matter, and how to use it to turn a pile of keywords into a publishing plan — whether you use Ahrefs, Semrush, Google Keyword Planner or a free tool.

What a Keyword Research Template Is

A keyword research template is a spreadsheet that organises your keywords so you can compare them and decide what to write. Each row is one keyword. Each column holds one fact about it: how many people search for it, how hard it is to rank for, what the searcher wants, and which page on your site should target it.

The template is not the research itself. The research is finding the keywords; the template is where the decisions get made. Without it, teams keep re-checking the same keywords, writing two articles for one topic, or publishing pages for keywords nobody searches for.

The Columns Your Template Needs

A useful template has about ten columns. More than that and nobody fills it in; fewer and you cannot make decisions from it.

Keyword. The exact phrase, written as people search for it. Keep one keyword per row — do not cram variations into a single cell.

Search volume. The average monthly searches, from your keyword tool. Write the number your tool shows, and note which tool and country it came from, because volumes differ between tools and databases.

Keyword difficulty (KD). The tool’s difficulty score. This is a rough guide, not a fact — treat it as a comparison between your own keywords, not as a promise about ranking.

Search intent. What the searcher wants: information, a comparison, or to buy something. Mark it simply: Informational, Commercial, Transactional or Navigational. Intent decides the page type — an informational keyword gets a guide, a transactional one gets a product or service page.

Current ranking. Your site’s current position for the keyword, if it ranks at all. Check this in Google Search Console. A keyword you already rank 8th for is a quicker win than one you do not rank for at all.

Target URL. The page that should rank for this keyword — either an existing page or a new one you plan to write. One keyword, one page: if two pages target the same keyword, they compete with each other.

Priority. A simple score or label — High, Medium or Low — decided after you weigh volume, difficulty, intent and how well the topic fits your business.

Status. Where the keyword sits in your workflow: Research, Briefed, Writing, Published, or Optimise. This single column turns the template into a content calendar.

Notes. Anything that changes the decision: a competitor ranking well, a seasonal spike, a keyword that only matters in one country.

Optional extras worth adding: the keyword’s cost-per-click if you also run ads, and a “cluster” column that groups related keywords under one topic, so you can see at a glance which topics you have covered and which are thin.

How to Score and Prioritise Keywords

A template full of numbers is useless until you rank the rows. The simplest scoring method that works uses three questions, and it takes seconds per keyword.

First, does the keyword match what your business actually does? A high-volume keyword on a topic you cannot serve is worth nothing. If it does not fit, delete the row.

Second, can you realistically rank for it? Compare its difficulty score with keywords you already rank for. If your site ranks for KD 20 keywords but not KD 40, treat KD 40 as a longer-term target, not a first article. New sites should start with the easiest keywords in their niche and build up — a strategy our guide on what SEO is explains in the context of how rankings actually work.

Third, is the volume worth the effort? A keyword with 50 searches a month can still be worth writing about if every searcher is a potential customer; a keyword with 10,000 searches is worthless if the searchers will never buy. Volume matters, but intent and fit matter more.

Mark the winners High priority, the rest Medium or Low, then sort by priority. Your next month of content is the top of that sorted list.

How to Fill the Template: A Step-by-Step Process

Step 1: Start with seed keywords. Write down the five to ten words that describe your business and its main topics. These are seeds, not targets — the tool will grow them into hundreds of ideas.

Step 2: Expand them in your keyword tool. Enter each seed into Ahrefs’ Keywords Explorer, Semrush’s Keyword Magic Tool, or Google’s free Keyword Planner, and export the ideas. Ahrefs has a detailed keyword research guide that walks through its own workflow, and the steps transfer to any tool. Google’s Keyword Planner is free with a Google Ads account — see Google’s own Keyword Planner help to set it up.

Step 3: Filter before you import. Remove keywords with no volume, keywords in languages you do not serve, and keywords that are clearly for a different business. Importing everything makes the template unreadable.

Step 4: Fill in intent and clusters. Go through the rows and label the intent of each keyword, then group related keywords into clusters — for example, all the “how to build backlinks” variations sit in one cluster, and one page covers them. This step is where most of the value is created, and it is the step most people skip.

Step 5: Assign a target URL to every row. If a page already exists for the topic, link the keyword to it. If not, the keyword becomes a new article in your publishing queue. Keywords with no page and no plan are just a list — the template only earns its keep when every row points somewhere.

Step 6: Sort, prioritise, and start writing. Sort by priority, take the top keywords, and write the articles. Update the Status column as you go, so the template always shows what is done and what is next.

Mapping Keywords to Pages Without Cannibalisation

The most common keyword-template mistake is giving two pages the same keyword. When two of your pages target one phrase, Google has to choose between them, and it often chooses neither — or picks the wrong one.

Prevent it with one rule: before you create a new page, search the template for its keyword. If a page already targets it, either improve that page or pick a different keyword for the new one. Related keywords with different intents get different pages — for example, “what is SEO” (a definition) and “how to do SEO” (a tutorial) are two pages, not one. But close variations of the same intent — “SEO meaning” and “what does SEO mean” — belong on a single page.

The cluster column helps here. When a cluster has five keywords and one page, the page is doing its job. When a cluster has five keywords and five pages, you have a cannibalisation problem to fix. Our article on SEO in digital marketing is an example of a single page covering one cluster of related terms.

Keeping the Template Alive

A keyword template goes stale fast. Search volumes change, competitors publish, and your own rankings move. Set a simple routine: once a month, update the Current Ranking column from Search Console, add new keyword ideas from your tool, and move published keywords to Published status.

Archive, do not delete, keywords you decided against — a note saying “rejected: wrong intent, checked March 2026” stops someone re-researching the same phrase six months later. And keep one copy of the template as the master: if two versions circulate, nobody trusts either.

For pages that are already published, the template also drives optimisation. A keyword ranking 8th to 15th is a “striking distance” keyword — a small update to the page can move it onto page one. See our guide on how to check your website’s SEO for the checks that tell you which pages are closest to moving up, and how to build backlinks for the off-page work that supports them.

Frequently Asked Questions

What is a keyword research template?

A spreadsheet where each row is a keyword and each column records a fact about it — search volume, difficulty, intent, target page and status — so you can compare keywords and plan content instead of guessing.

Is there a free keyword research template?

Yes — the layout in this article works in Google Sheets or Excel, and both are free. You only pay for a keyword tool if you want volume and difficulty data; Google Keyword Planner gives basic volume data free with a Google Ads account.

Do I need Ahrefs to use a keyword research template?

No. The template works with any tool — Ahrefs, Semrush, Moz, or Google Keyword Planner. The columns stay the same; only the source of the numbers changes.

How many keywords should be in a template?

Start with 50 to 100 for a small site. A template with thousands of unfiltered keywords is harder to use than a short one with clear priorities. Add keywords as you research new topics, and remove rows that no longer fit.

How do I know which keyword to target first?

Pick keywords that fit your business, match an intent you can serve, and have the lowest difficulty among the ones with real volume. Early wins build the authority that lets you target harder keywords later.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *