Keyword analysis template: the columns that turn a list into decisions

A keyword analysis template is one spreadsheet with four blocks of columns: what the query is, what the demand looks like, what the competition looks like, and what you decide to do about it. The decision block is what separates a template from an export. Without a cluster name, an assigned URL, an intent label and a priority score, a keyword list is data nobody acts on. The structure below works in Google Sheets or Excel, takes about twenty minutes to set up, and ends with a ranked build order rather than a list of words.

This page gives the columns, the formulas and the process. Finding the raw queries in the first place is the research step that feeds this sheet.

The columns, block by block

Block 1: the query

Column	What goes in it
keyword	The query exactly as people type it
cluster	The group of queries that share one answer. This is the most important column in the sheet.
intent	informational, commercial, transactional or navigational
funnel stage	problem aware, solution aware, vendor aware
market or language	Only if you operate in more than one
source	Where the query came from: planner, Search Console, site search, sales calls, competitor

Block 2: demand

Column	What goes in it
volume	Monthly searches from one source. Use the same source for every row or the comparison is meaningless.
trend	Rising, flat or falling over twelve months
seasonality	Peak months, if any
cost per click	A usable proxy for commercial value, because advertisers reveal what a click is worth
result features	What occupies the page: ads, a local pack, shopping, video, an AI summary. This decides how much of the click is available.

Block 3: competition and your position

Column	What goes in it
difficulty	Your tool's score, used only to compare rows inside this sheet
top result type	Homepage, product page, category, blog, forum, directory, video. If the top ten are all forums, a product page will struggle.
authority of page one	Whether those pages sit far above or near your own site strength
our current rank	From Search Console, which is the only trustworthy source for your own position
our current URL	The page that ranks today, if any
impressions and clicks	Pulled from Search Console, so you can see demand you already touch

Block 4: the decision

Column	What goes in it
target URL	The one page that will own this cluster. One cluster, one page.
action	create, improve, consolidate, redirect or ignore
page type	What format the answer needs: guide, comparison, calculator, location page, product page
business value	1 to 3, scored by how close the query is to revenue
effort	1 to 3, where 1 is a title and section edit and 3 is a new asset with original research
priority score	Calculated, see below
owner and due date	Because a sheet with no owner is a wish list
notes	What the reader must be told, and anything that would make the page genuinely better than page one

The priority score

Rank by expected value for effort, not by volume. A workable formula using the columns above:

priority = (volume_score * business_value * winnability) / effort

volume_score  = LOG10(volume + 1)
business_value = 1 to 3
winnability   = 3 if our rank is 4 to 20
                2 if our rank is 21 to 50 or page one is weak
                1 if we do not rank and page one is strong
effort        = 1 to 3

Two deliberate choices in that formula. Volume is logged so a single very large head term does not dominate the sort. Winnability rewards queries where you already rank just off the top, because moving a page from position eleven to position six is usually cheaper than creating a page that outranks established competitors. Those near miss rows are the fastest wins in most sheets, and they are invisible if you sort by volume.

The process, start to finish

1. Pull everything in. Planner exports, Search Console queries for the last twelve months, your own site search log, competitor rankings, and the words your sales team hears.
2. Deduplicate and normalise. Lower case, trim spaces, strip country suffixes you do not need, remove rows with no volume and no impressions.
3. Cluster by answer, not by words. The test is whether one page would satisfy every query in the group. Plural and singular, abbreviations and synonyms usually belong together. Different intents never do, even when the wording is close.
4. Label intent by looking at what currently ranks, not by guessing from the phrasing.
5. Join your own data. Bring in rank, URL, impressions and clicks so improvement opportunities surface alongside new builds.
6. Assign one URL per cluster. Where two existing pages target the same cluster, mark one consolidate and one redirect. This single step fixes more lost traffic than any new content.
7. Score and sort. Then pick the top items you can actually deliver this quarter and leave the rest in the sheet.
8. Set a refresh date. Update impressions and ranks monthly, volumes quarterly, and the clustering only when the business changes.

The checks that catch the common mistakes

• Duplicate target URL across different clusters. A conditional format on the target URL column surfaces cannibalisation before it is published.
• Volume from mixed sources. Keep one column per source if you must use two, and never sort on a mixed column.
• Zero volume rows with impressions. Keep them. Queries with no reported volume still bring qualified traffic, especially long questions.
• Rows with no action. Every row gets an action, including ignore, with a reason. Otherwise the sheet is reviewed again from scratch next quarter.
• Clusters with more than one intent. Split them. This is the most common cause of a page that ranks for nothing.

Asked and answered: keyword analysis template

How many keywords should a template hold?

However many it takes to cover your clusters, which for most sites is a few hundred rows resolving into a few dozen clusters. The useful limit is the cluster count, since that equals the number of pages you are committing to own.

Can I use Google Sheets rather than a tool?

Yes, and many teams prefer it, because the decision columns are the point and those judgement calls are yours to make. Pull data in with exports or the Search Console add on, and keep the sheet as the source of truth for what gets built.

Where does search volume come from?

Keyword Planner reports it from Google's own data in ranges, and third party tools model it from clickstream and index data. Both are estimates, so pick one source and use it consistently, and treat your own Search Console impressions as the more reliable signal for queries you already appear for.

How often should the analysis be redone?

Refresh your own rank and impression columns monthly so near miss opportunities stay current, and revisit volume and difficulty quarterly. Rebuild the clustering when you add a product line, a market or a new audience.

Should paid and organic keywords live in one sheet?

One sheet, with a channel column. Paid search terms reveal which queries convert long before organic data accumulates, and sharing the sheet stops both teams buying and building for the same intent twice.

One column worth adding this year

Every column above describes a results page with ten links on it. A growing share of the same questions is now put to an assistant, which returns one answer naming two or three companies. ChatGPT, Gemini, Perplexity, Claude and Google's AI Overviews answer the same query differently and cite different sources, so there is no rank to put in a cell.

What goes in the sheet instead is useful: for each commercial cluster, who gets named and which pages get cited. It tells you which of your own pages are doing the work and which competitor sources to go after. Keep your own brand name out of the prompt so the answer is not steered. AI Knows Us runs those checks across the assistants and reports the sources behind each answer.