A keyword research template and what each column is for

A keyword research template is worth having for one reason: it forces the decisions that turn a keyword list into published pages. Most templates track keyword, volume and difficulty, then stop, which is why so many of them are opened once and abandoned. The four columns that make the difference are intent, assigned URL, page type and status. Those turn a reference document into a queue somebody can work from.

Here is the column set worth building, what each one is actually for, and the rules that keep the sheet useful three months in.

The nine columns

1. Keyword. The exact phrase, lowercase, one per row. Keep the phrasing users typed rather than a tidied-up version of it, because the wording changes which results page you are competing on. "How to fix" and "why does" are different queries with different results, even about the same problem.

2. Monthly search volume. A band is fine and often more honest than a number. Under 100, 100 to 1,000, 1,000 to 10,000, above that. Precision here is false comfort: no tool knows whether a term gets 800 or 1,100 searches, and the decision you make is identical either way. Record the source in the column header so you remember what you compared against later.

3. Difficulty. Whatever scale your tool uses, with the tool named. Difficulty scores are not comparable across vendors, so a sheet mixing two sources produces a ranking that means nothing. One source, consistently.

4. Intent. The first of the columns most templates skip, and the one that changes the most outcomes. Four values are enough: informational, commercial, transactional, navigational. Add a fifth practical one if you sell something, a simple yes or no for "would this person ever pay us". That column alone will delete a third of your list, and the third it deletes is the third that would have wasted a quarter.

5. Cluster. The topic group this keyword belongs to. Keywords a single page can satisfy share a cluster name. This is what stops you writing four thin posts for four phrasings of one question, and it is the column that makes the sheet sortable into work.

6. Assigned URL. One URL per cluster, whether it exists yet or is a planned slug. Filling this in is what surfaces cannibalization, because the moment two clusters point at the same URL, or one cluster has two candidate URLs, you have found a problem before it costs you rankings rather than after.

7. Page type. Landing page, blog post, product, collection, or revision of an existing page. The cluster tells you this if you read it: a group full of comparison queries wants a comparison page, a group of how-to queries wants an article. Writing it down prevents the common mistake of answering a buying query with a blog post.

8. Status. Not started, briefed, drafted, published, needs update. This is the column that makes the sheet a working document instead of an archive. Without it, nobody can tell at a glance what is left to do, so nobody opens it.

9. Current position. Where you rank today, pulled from Search Console rather than a third-party estimate. Anything sitting between 5 and 20 is a revision candidate and should jump the queue ahead of new writing, because the ranking signal already exists.

Columns worth skipping

A template gets abandoned when maintaining it costs more than it returns. Three columns that regularly show up and rarely earn their keep:

• Cost per click. Useful if you run ads, noise if you do not. It is a proxy for commercial intent, and your intent column is a better one.
• Word count target. Reverse-engineered from whatever currently ranks, which encodes a competitor's padding decisions into your brief. Cover the topic completely and let the length follow.
• Trend data. Interesting for seasonal categories, decorative everywhere else, and it goes stale the day you paste it in.

How to fill it in without losing a day

The order matters, because filling columns in the wrong sequence means doing work on rows you are about to delete.

Start by expanding your seed topics into a raw keyword list and paste it into column one. Then classify intent immediately, before you look up a single volume figure, and delete every row that fails the "would they pay us" test. People do this in the opposite order, look up metrics for four hundred keywords, and then throw half of them away. Intent first is twenty minutes of work that saves two hours.

Now add volume and difficulty for what survived. Then cluster, which is the slowest manual step and the one worth automating: grouping two hundred keywords by meaning takes a couple of hours by hand and produces inconsistent results because your judgment drifts by row 120. A keyword clustering tool does the pass in seconds and leaves you reviewing groups rather than building them.

Assign URLs and page types per cluster, pull current positions for anything you already have a page for, and set every status. The sheet is now a queue. Sort by intent value against difficulty, and the top ten rows are your next quarter.

Keeping it alive

The failure mode is not building the template, it is the third month. Two habits keep it current with almost no cost.

Update status on publish, in the same sitting as the publish. If it waits until later, later does not come, and a sheet with stale statuses is one nobody trusts. Second, refresh the current-position column monthly from Search Console. That refresh is what surfaces the fastest work available to you: pages that drifted into positions 5 to 20, where a revision beats anything new you could write.

Roughly once a quarter, add a fresh gap pass so the sheet does not slowly become a record of what you already decided. Pulling the terms competitors rank for and you do not, as described in how to find the keywords your competitors rank for, keeps new rows arriving at the same rate you are clearing old ones.

When a spreadsheet stops being the right container

A template scales fine to a few hundred keywords and one site. It stops scaling in two situations.

The first is running multiple sites or client accounts, where you are now maintaining six sheets with divergent conventions and no way to see across them. That is the point where keyword research for agencies stops being a spreadsheet discipline and starts being a workflow problem.

The second is catalog scale. A store with a thousand URLs cannot be mapped by hand at all, and the manual version produces exactly the cannibalization the map was supposed to prevent. That is a bulk keyword research job.

There is also the moment the sheet needs to become a slide for someone who will never open a spreadsheet. If you present quarterly plans to a client or an executive, having a way to turn the document into a presentation saves the hour that usually goes into rebuilding the same information in a deck.

Either way, the columns above are the right ones no matter what holds them. If you want the first six filled in for you, drop a seed topic into the free demo and export what comes back: expanded keywords, scored, with intent read and clusters already formed. You add the URL, page type and status, and the queue exists.