ChatGPT Keyword Research: Clustering, Search Intent and Google Sheets Automation

ChatGPT keyword research can turn hours of grouping and intent tagging into minutes, as long as you set it up correctly. However, ChatGPT does not know search volume; that number has to come from Google's own tools. In this guide I walk you through the workflow I have used on client projects since 2012: collecting data, clustering with ChatGPT, labelling intent and automating the whole process inside Google Sheets.

What is ChatGPT keyword research?

ChatGPT keyword research means taking a keyword list from real search data and asking a language model to cluster it, label each query by search intent and turn the groups into content ideas. The model does not create the data; it interprets the list you give it. Volume, competition and clicks must come from Google Ads and Search Console.

I want to make this distinction clear from the start, because it is the most common mistake I see. Someone types "give me keywords for a hair salon with monthly search volume" and treats the table as real. Those numbers are the model's guesses, and often pure invention. So the model's job is not to produce numbers but to make sense of the numbers you collected.

Used properly, ChatGPT works like an analyst's assistant. For example, it reads a 500 row list, groups synonyms together, separates informational queries from purchase queries and suggests a page type for each group. You then review the output and make the decisions.

Why can't ChatGPT give accurate search volume?

Language models generate answers from text patterns in their training data; they have no access to Google's search counters. Therefore, when you see a figure like "2,400 searches per month", you should know it is not a measurement. Even with web search switched on, the model usually stitches together scattered estimates from third party pages.

For reliable volume you have two official sources. The first is Keyword Planner inside Google Ads, which shows monthly search ranges and bid estimates. The second is the Search Console performance report, which lists the queries your site already appears for, along with clicks and average position.

That is why the order in my workflow never changes: first I pull data from Google, then I give ChatGPT an interpretation task only. In short, the numbers come from Google and the meaning comes from the model. Break that rule and the whole analysis rests on a false foundation.

Keep in mind that Planner figures are ranges too. Still, they rest on real search data and give you enough to rank keywords against each other. Relative size matters more than an exact number, so always report a figure together with its source and keep estimates and measurements in separate columns.

Which data sources should you start with?

The quality of the analysis depends on the quality of the raw list you give the model. I usually combine four sources:

• Search Console queries: every query with impressions over the last 3 or 16 months.
• Keyword Planner: ideas that grow from your seed terms, with monthly search ranges.
• Google autocomplete and "People also ask" boxes: real question patterns.
• Competitor page titles: they show which angles already rank.

Merge these lists into a single Google Sheets tab and remove duplicates. Also add a source column to every row, so the model knows which keywords already earn real impressions.

You can use the keyword suggestion tool on my site as a starting point for seed terms. I explained how to choose keywords close to a sale in how to find keywords that drive sales, so I will not repeat that topic here.

How do you import Search Console data into Sheets?

The easiest route is exporting the performance report. In Search Console, open Performance, choose a date range and press Export; you can open the results directly as a Google Sheets file. The Queries tab becomes the raw material of your analysis.

However, this method needs monthly repetition and can hit the export row limit. If you work on a regular basis, calling the Search Console API from Apps Script or using the bulk data export to BigQuery offers a more durable solution. For small and medium sites, a manual monthly export is usually enough.

After the import, fix your column names first: query, clicks, impressions, CTR, position. Your automation script will rely on these names, so keep the same layout every month. Also move queries that contain your brand name to a separate tab. Branded queries distort intent analysis, because the user is already looking for you.

How do you merge Keyword Planner output?

Keyword Planner gives you ideas derived from your seed terms together with average monthly search ranges. You can download the results as CSV or Google Sheets. In accounts with low ad spend the figures appear as wide ranges; that is normal and still gives direction.

In the merge step you bring Search Console queries and Planner ideas onto one sheet. I use a simple key column for this: the query text in lowercase with extra spaces removed. Then a lookup function such as XLOOKUP adds the volume data to the Search Console rows.

As a result, every row holds both real impressions and market volume. Keywords that get demand in Planner but no impressions on your site show up in a separate list. That list is the most valuable input you can send ChatGPT for clustering, because it reveals your content gaps.

How do you do ChatGPT keyword research step by step?

I split the process into five steps, because one giant prompt both hides errors and makes results hard to reproduce.

1. Cleaning: remove typos, irrelevant queries and duplicates.
2. Clustering: ask the model to group keywords that serve the same need.
3. Intent label: have it assign informational, comparison, transactional or local to each group.
4. Page mapping: decide whether each group belongs to an existing URL or needs a new page.
5. Priority: calculate a ranking score from volume, business value and current position.

ChatGPT helps in the first four steps; the fifth is a formula based entirely on your own data. That way the model handles language work while numeric decisions stay in your sheet.

Page mapping needs its own discipline. I covered it with a table template in the keyword mapping guide, and you can paste ChatGPT's output straight into that template.

What prompt should you write for clustering?

A good clustering prompt defines the role, the input, the rules and the output format clearly. Here is the skeleton I use most often:

Prompt: "You are an SEO analyst working in the UK market. Each line below is a search query. Put queries that share the same search intent and could be answered by a single page into the same cluster. Give each cluster a short name. Do not add keywords that are not in the list and do not estimate volume. Return only a two column table: query, cluster name."

The critical sentences are "do not add keywords" and "do not estimate volume". Without these restrictions the model tries to be helpful, invents new keywords and pollutes your sheet.

Also send the list in batches. With very long lists the model can skip rows towards the end. I work with batches of 100 to 200 rows and check the row count after each batch. If the number of rows in and out does not match, I run that batch again.

How do you get ChatGPT to label search intent?

The most important thing in intent labelling is a fixed label dictionary. If you leave it open, the model writes "commercial" on one row, "purchase" on another and "buying" on a third, and filtering becomes impossible.

That is why I list the labels one by one in the prompt: INFO, COMPARE, TRANSACT, LOCAL, BRAND. Then I define each label in one sentence. For example: "TRANSACT: the user looks for a price, a purchase, a quote or a booking."

Add an escape label for ambiguous queries as well: CHECK. Then the model does not force uncertain rows into a box; it leaves them to you. Afterwards you look at those rows in Google yourself. Whether the results page shows product listings, guides or a map pack reveals the real intent.

In my experience the model guesses intent reasonably on most rows. However, it can get industry jargon wrong. So verify part of the labels by hand in the first batch and sharpen the definitions in the prompt if needed.

Why does Google Sheets automation pay off?

Pasting a list into the chat window and copying the result back works for small jobs. But if you repeat the same analysis every month with fresh Search Console data, that copy and paste loop costs time and creates errors.

Google Sheets automation breaks the loop. Keywords sit in one column, and the column next to it calls the model and brings back the label. In other words, you write the prompt once and the sheet applies the same rule to every new row. Moreover, the result can go straight into filters, pivot tables and shared views for your team.

For me the real value of automation is consistency. When you run with the same prompt, the same label dictionary and the same temperature, you can compare this month's analysis with last month's. With manual chat sessions that comparison is almost never possible.

Another benefit is shared work. Because the sheet is a single source, the content writer, the ads specialist and the developer all look at the same clusters. For instance, the ads side turns transactional clusters into ad groups while the content side moves informational clusters into the editorial calendar.

Which automation options exist inside Google Sheets?

In practice I see three routes. The table below summarises who each one suits.

Google's AI function help page explains that the function takes a prompt and an optional range and that responses are limited to text. It is a good start for a quick test. For recurring client work, however, I prefer Apps Script, because I can version the prompt and the model.

How do you connect ChatGPT to Sheets with Apps Script?

The setup has four parts, and even someone with little coding experience can finish it in half an hour.

1. Create an API key on the OpenAI platform and enable billing.
2. Open the Apps Script editor from the Extensions menu of your spreadsheet.
3. Store the key in script properties under Project Settings instead of writing it into the code.
4. Write a custom function that takes the keyword in the cell, combines it with the prompt, sends it to the API and returns the answer.

For the request you use the UrlFetchApp service in Apps Script. In the body you send the model name, the system instruction and the user message as JSON. Do not hardcode a model name from memory; pick the currently recommended, cost effective one from OpenAI's models page. For simple tasks like labelling, a small and cheap model is usually enough.

I do not cover API basics here; account setup, keys and the first request belong to a separate article. The focus here is how you plug this connection into your keyword sheet.

Which settings in the custom function shape the result?

The same code can give very different results; a handful of settings make the difference. I pay attention to these:

• Low temperature: labelling is a deterministic job, so you do not want creativity.
• Short, fixed output: ask for the label word only; explanation sentences break filtering.
• Batch requests: instead of one request per cell, send 20 to 30 keywords at once and split the answer into rows.
• Caching: use Apps Script's CacheService so you never query the same keyword twice.

Custom functions have an execution time limit and can recalculate whenever the sheet refreshes. Consequently, for large lists a script triggered from a menu that writes the results as values is more robust. That way you do not pay API fees every time you open the file.

How do you keep automation costs low?

In API based automation, cost scales with the amount of text you send and receive. Therefore the biggest saving comes from keeping the prompt short and the output even shorter. Sending keywords in groups instead of repeating a long system instruction for every row cuts cost noticeably.

The second saving comes from model choice. Narrow tasks such as labelling and clustering do not need the largest model. Compare input and output prices of smaller models on OpenAI's pricing page and run your validation set on the cheaper one first. If the result holds up, stay there.

Third, freeze results as values. Live formulas may run again whenever the sheet recalculates. A menu command processes only rows with an empty label and writes the result into the cell. As a result, you never pay twice for the same keyword and your monthly budget stays predictable.

How do you validate ChatGPT keyword research results?

Automation scales mistakes. A wrong prompt means a hundred errors in a hundred rows. That is why I test every new prompt on a small validation set before going live.

In practice, the method is simple: label fifty rows yourself, then give the same rows to the automation and compare the two columns. Look at the mismatches; the cause is usually a vague label definition. Fix the definition and repeat the test.

Also compare clusters against the search results page. If two keywords show the same URLs on page one, they belong in the same cluster. If the URLs differ, you may need separate pages even when the model merged them. This check balances the model's language based decisions with real search behaviour.

Finally, for page level checks such as keyword density you can use the keyword density tool; before content goes live you see whether the target term reads naturally.

Which column layout should you use to store cluster output?

The layout decides how easy it will be to return to the analysis three months later. I keep these columns as a standard:

• Query and source (Search Console, Planner, autocomplete)
• Monthly search range and recent impressions
• Cluster name and intent label
• Target URL or a "new page" flag
• Business value, priority score and status (planned, writing, live)
• Prompt version and analysis date

The last column is the one most people skip and the one that helps most. When you change the prompt, you know which rows carry old rules and which carry new ones. So when results differ, you find the reason quickly.

How do you calculate a priority score in the sheet?

Once the model delivers clusters and intent, the ranking is yours. In practice, I use a simple scoring formula and adjust the weights to each client's business model.

One example: put the lower bound of the monthly search range in one column, business value from one to three in another, and the average Search Console position in a third. Then give extra weight to transactional clusters. Clusters sitting between positions 8 and 20, right on the edge of page one, usually bring the fastest wins.

I never ask ChatGPT "which one should we write first?" The model does not know your margin, your stock or your sales team's capacity. You fill in the business value column yourself, and the formula stays transparent.

How should you use ChatGPT for content ideas?

Once clusters are ready, the model becomes useful on the creative side. For example, you can ask for a title suggestion for each cluster, the sub questions users will ask and the sections the page should contain.

Even here I set limits. I check whether the site already has a page covering the suggested topic. Skip that check and you write two pages for the same intent, turning your own site into your competitor.

Compare sub questions with real data too. The model can produce questions that sound logical but nobody searches. Question patterns with impressions in Search Console, or Google's question boxes, show which subheadings have genuine demand. To test how clickable a title is, try the headline analyzer. At the writing stage, the checklist in how to write SEO friendly content makes the job easier.

How does ChatGPT keyword research change for multilingual projects?

On sites that serve several markets, the biggest trap is translating one keyword list into other languages. Translation does not show how people actually search in that language. German users, for instance, often search with compound nouns, while English users prefer shorter patterns.

So you collect a separate raw list for each language: that country's Search Console data and Planner ideas with the right language and location settings. You use ChatGPT not as a translator but as an analyst clustering each language's own list. Also state the market and the language clearly in the prompt.

After clustering, mapping between languages is a good job for the model. The question "which English cluster matches this German cluster?" also forms the basis of your hreflang plan. The multilingual website SEO guide and the hreflang generator speed this up.

How often should you update the analysis?

For most sites a full analysis every quarter and a light check every month is a sensible rhythm. In the light check you only pull newly appearing Search Console queries and feed them into the automation; rows with an empty label get processed, older rows stay untouched.

In seasonal industries, adjust the rhythm to the season. Education, travel or holiday driven e-commerce categories, for example, see demand shift within a few weeks. Updating the sheet before those periods lets you publish content on time.

Then recalculate the priority score with every update. If a cluster moved up or a new competitor appeared, the order changes. As a result, your content calendar stops being a static list and becomes a plan that renews itself based on your site's current state.

What should you watch for in terms of data security?

Keyword lists look harmless, but sometimes they contain client names, internal product codes or campaign names that have not launched yet. Therefore, remove such data before sending anything to an API.

OpenAI states that data sent through the API is not used to train models by default; still, check your company policy and contracts. With Marketplace add-ons, data may first go to the add-on developer's server, so read the permission screen carefully.

Never put the API key in a cell or in shared code. In practice, script properties exist for exactly this purpose. Also set a monthly spending limit in the OpenAI dashboard. That way, even if a faulty loop fires thousands of requests, your bill stays under control.

What are the most common mistakes?

In the audits my team and I run, we see the same mistakes again and again:

• Putting ChatGPT's volume figures into a report as if they were real data.
• Asking for intent classification without a label dictionary.
• Sending thousands of rows in one request and missing skipped rows.
• Leaving formulas live and paying API fees on every open.
• Moving clusters into the content calendar without checking the results page.
• Leaving competitor analysis entirely to the model.

The last point matters most. Specifically, the model cannot know which keywords your competitors rank for; only real SERP data shows that. I described how I handle the competitor side in how to do SEO competitor analysis.

Who is ChatGPT keyword research right for?

This method suits teams working with many long tail queries. E-commerce sites, multi service companies and brands that publish regularly gain the most time.

On the other hand, a short manual analysis is often enough for a single page local business. If the volume of work is small, the cost of building automation may never pay back. So look at the size of your list first.

Then there is technical capacity. If you do not want to write Apps Script, the Sheets AI function or a trusted add-on is a good middle ground. Whichever route you choose, the numbers still come from Google and the decisions still stay with you.

Finally, think about your time budget. Once set up, monthly maintenance rarely takes more than an hour or two. The first setup, however, needs a few days for prompt tests, the validation set and the column layout. That investment only pays off if you repeat the analysis regularly.

When should you hand this process to a professional?

When the list grows to thousands of rows, several languages and markets come into play, or the results drive budget decisions directly, the process needs serious discipline. At that point, building the setup, prompt versioning and validation sets correctly once saves time in the long run.

Within our SEO consulting work, my team and I build these sheets inside the client's own Google account, so data and automation stay with the company. If you want to use the same keyword data on the ads side as well, take a look at Google Ads management.

If you prefer to go on your own, my advice is to start small: a fifty keyword test list, one labelling prompt and one validation column. Once those three work reliably, scale up.

How is AI search changing this analysis?

Some users now type their questions directly into ChatGPT Search, Gemini or Google's AI Overviews. These queries are longer and closer to spoken language. Consequently, tracking question style clusters separately in your sheet starts to make sense.

That does not mean classic keyword research is over. Intent, clusters and page mapping remain the foundation; what changes is the format of the answer. Pages that meet question clusters with clear, short answers become more visible both in classic results and in AI summaries.

I cover this side in detail in how to write content for AI Overviews and is SEO dead. Read your keyword sheet with both perspectives and you prepare for today's search and tomorrow's.