How to Do Keyword Research: An 8-Step Process With a Spreadsheet and Prioritization Criteria

Keyword research is the process of identifying, classifying, and prioritizing the terms your audience types into search engines and asks AI tools, so you can decide which pages to create and in what order. The method combines four readings: search volume, difficulty relative to your domain authority, query intent, and the presence of an AI-generated answer on the results page.

Most guides on this topic were written by people selling a tool, which explains why they teach you to click buttons and skip the hard part: the decision criteria.

Here we take the opposite route. You leave with an 8-step process that works with either a paid or free tool, a spreadsheet described column by column, and a prioritization framework to turn your list into a calendar.

What keyword research is and what changed with AI search

Keyword research is the process of discovering what your audience types or asks in search engines and AI tools, measuring the demand and competition for each query, and deciding which content to produce first. The end result is a prioritized list that becomes an editorial calendar. With AI Overviews and ChatGPT answering right there on the screen, that list is no longer just a ranking of terms; it has become a map of questions your brand needs to answer well enough to get cited.

The four data points that matter for every keyword

Every query carries four pieces of information: volume (how many searches per month), difficulty (how contested the first page is), CPC (how much advertisers pay per click, a thermometer for commercial value), and the SERP, the results page itself. Step 4 of the process teaches you to read all four together, because in isolation they mislead.

How to identify intent without guessing

Intent is what the person wants to solve when they search. There are four types: informational (they want to learn), navigational (they want to reach a specific site), commercial (they're comparing options), and transactional (they want to buy now). The most honest way to classify it is to look at what already ranks: if the first page is nothing but guides, the intent is informational, and pushing a sales page there won't work.

Why a prompt is not a keyword

Nobody types "best crm small business" into ChatGPT. People write three lines of context. But the model breaks that prompt down into short queries to find sources, so the same keyword research feeds both games: classic rankings and the GEO work of showing up inside generated answers.

The 8-step process for doing keyword research

The example below uses a supplements e-commerce store, but the method works for any niche.

Step 1: define the objective and the destination page

Before opening any tool, answer this: what do you need to sell, and which page receives that traffic? Without it, the list is trivia, not a content plan.

Step 2: gather your seed terms

Write down 10 to 20 terms in your customer's own vocabulary: "does creatine make you gain weight," "whey protein for beginners." Sources: questions from the sales team, internal site searches, comments.

Step 3: expand with tools

Keyword Planner and Google autocomplete are free and handle the beginning just fine. Paid tools speed things up, but they don't change the criteria.

Step 4: mine competitors

See which terms sites similar to yours already rank for. Common mistake: copying a competitor whose domain is ten times stronger.

Step 5: read the SERP before deciding

Search the term. If only e-commerce pages show up, an article won't rank there. If there's an AI Overview, expect fewer clicks.

Step 6: group by intent

"Creatine before or after training" and "best time to take creatine" call for the same answer: one cluster, one piece of content.

Step 7: map each cluster to a URL

One intent per page. Two pieces of content fighting over the same term cannibalize each other.

Step 8: prioritize and produce

Cross business impact with a realistic chance of ranking. That becomes the production queue described in the next section.

How to build your keyword research spreadsheet

The spreadsheet is the final product of your research. If it doesn't exist, the work dies inside the tool's tab.

The 10 required columns

One row per query, with these columns: keyword, monthly volume, difficulty, CPC, intent (informational, commercial, or transactional), cluster, page type (article or product page), current position in Search Console, AI answer on the SERP (yes or no), and status (planned, in production, published).

The AI column deserves attention. If the query already opens with an AI Overview, clicks drop and the game becomes getting cited in the answer, which changes the brief for that content.

How to calculate the priority score

Give a score of 1 to 5 to three factors: volume, intent, and ease (the inverse of difficulty compared to your domain's strength). The formula: score = volume + (intent × 2) + (ease × 2). Intent and ease are double-weighted because they deliver results sooner.

Example from the supplements store: "does creatine make you gain weight" scores volume 5, intent 2, ease 4. Score: 5 + 4 + 8 = 17. Meanwhile, "buy creatine 300g" scores volume 3, intent 5, ease 2. Score: 3 + 10 + 4 = 17. Numerical tie, different decisions: the first becomes an article now, the second waits for the domain to build authority.

From spreadsheet to calendar

Sort by score, take the first 8 or 12 rows, and distribute them across the weeks of the quarter. Each row already carries its cluster, page type, and intent, which means the brief is half written.

How to prioritize and choose where to start

With the list ready, the temptation is to attack the highest-volume term. Resist. Good prioritization applies three filters in order: can you rank with your current authority, does it convert, and does it still generate clicks in the era of AI answers?

Difficulty compatible with your domain

Difficulty only makes sense when compared to your site's strength. Practical rule: a new site, or one with authority up to 20 in the tools, should target queries with difficulty up to 15. A mid-sized domain, between 20 and 40, can compete up to difficulty 35. Above that, head terms belong in the long-term plan, never in the first quarter.

In the supplements store, "does creatine make you gain weight" (low difficulty, specific question) beats "whey protein" off the starting line, even with a tenth of the volume.

Intent matters more than volume

A high CPC signals that advertisers pay for that click, meaning the query sells. Between two options with similar difficulty, pick the one with higher CPC and commercial intent. High volume with vague intent generates traffic that doesn't pay for the production.

AI citation potential

Before locking in your priorities, search the term on Google and on ChatGPT. If an AI summary already answers everything without citing sources, downgrade it. If the answer cites links, move it up: comparison queries and questions with specific context tend to generate citations, and citations have become the new first page.

Score each row in the spreadsheet from 1 to 3 on these three filters and add them up. The highest totals open the calendar.

Mistakes that invalidate your research and how to audit yours

A beautiful spreadsheet with the wrong criteria produces six months of content that doesn't rank. Before sending the list to production, run this audit.

The 7 most common mistakes

1. Trusting volume alone, without checking whether the query converts.
2. Never opening the actual SERP: if Google shows product pages for "buy creatine," an article won't compete there.
3. Grouping by textual similarity instead of intent. "Whey protein price" and "whey protein benefits" look like siblings, but they need different pages.
4. Creating two pages for the same intent, the classic cannibalization.
5. Listing keywords with no possible page on the site, such as terms for a product you don't sell.
6. Ignoring whether the query is already answered by an AI summary.
7. Copying a competitor's list without filtering by your own authority.

Validation checklist before producing

For each row, answer yes to three questions: did I open the SERP, and is the format that ranks the one I'm going to produce? Is there a single page dedicated to this intent? Does the term pass the difficulty rule set during prioritization?

Quarterly review with Search Console

Every quarter, export queries sitting in positions 5 to 20: these are quick wins the original research didn't anticipate. If you want to turn this routine into a complete method, you'll find the step by step in Rankys' SEO training program.

Your first prioritized spreadsheet ships this week

The problem for people learning SEO from scattered videos was never a lack of information, it was a lack of sequence. Now you have one: what to sell before touching any tool, idea sources, metrics read in the context of your domain, intent, clusters, priority matrix, calendar.

If you need to summarize the method for your team in three lines, here they are:

1. Research starts with the business, not the tool. Product, objection, and customer questions come before any search volume.
2. Metrics without context mislead. Difficulty only says something when compared to your site's authority, and a high CPC is a sign the query is worth money.
3. One intent per page. The cluster decides what becomes an article, what becomes a product page, and what becomes nothing at all.

Tomorrow's step fits in one hour. Open Search Console, export the queries in positions 5 to 20, paste them into the spreadsheet model described above, and classify each one's intent. That first almost-ranking cluster is the cheapest place to prove results inside your team.

After that, the rest of the method pays for itself.

If you'd like to walk this path with course corrections along the way, from the mechanics of search to a final project applied to your own site, check out the Advanced SEO and GEO Training, with Digo Garcia, André Mousinho, and Gabriel Raposo, the same people who do this every day for major brands.

A good keyword is one your site can win and your business can charge for.

Frequently asked questions

Can you do keyword research for free?

You can, and do it well. Google Ads Keyword Planner shows volume in ranges at no cost, Search Console delivers the queries your site already receives, and the search bar's autocomplete generates endless ideas. What paid tools add is estimated difficulty and competitor analysis at scale. For a small site or a first project, the free version covers the entire process.

What is a good search volume for a keyword?

It depends on intent, not on the number in isolation. A transactional query with 200 monthly searches can be worth more than an informational one with 10,000, because the person typing it already wants to buy. High volume with a CPC near zero usually indicates curiosity without commercial value. Always read volume alongside CPC, intent, and your real ability to rank for that term.

What does keyword difficulty mean?

It's an estimate, usually from 0 to 100, of how hard it is to rank on the first page for that query, calculated from the authority of the sites already there. The number alone means nothing: difficulty 40 is unreachable for a new domain and trivial for an established portal. Always compare it to your own site's strength before deciding.

How do I identify the search intent behind a keyword?

The most reliable way is to open the SERP and see what Google ranks. If the results are articles and tutorials, the intent is informational. If product pages, category pages, and shopping ads appear, it's transactional. Comparisons and "best of" lists indicate commercial intent. The search engine has already tested billions of clicks, so the pages it shows reveal what the user wants.

How many keywords should I use per page?

One intent per page, not one keyword per page. Queries like "creatine before or after training" and "best time to take creatine" share the same intent and should become a single article, which can rank for dozens of variations. Creating a page for each variation causes cannibalization: your own URLs compete with each other and none of them ranks well.

Is keyword research still worth it with ChatGPT and AI tools answering everything?

Yes, but the criteria have changed. Simple definition queries tend to be answered right on the screen, with fewer clicks. Queries that require comparison, a purchase decision, or hands-on experience still generate visits and have become the source of the citations AI tools make. Research now includes one extra column: checking whether the query already receives an AI answer and how much that cuts into clicks.

How often should I redo keyword research?

A full review every six months, plus a light monthly adjustment using Search Console, which reveals new queries your site already appears for without you having planned them. Niches with strong seasonality, like supplements before summer, call for a review ahead of each peak. The arrival of an aggressive competitor or a major Google update also justifies reopening the spreadsheet outside the schedule.

What's the difference between long-tail and short-tail keywords?

Short-tail is the generic one- or two-word query, like "creatine": high volume, vague intent, and heavy competition. Long-tail is the specific query, like "creatine monohydrate for beginner women": lower volume, clear intent, and far easier to rank for. New sites build authority by stacking long-tail terms before competing for the generic ones.