How to cluster keywords into a content plan

A keyword export tells you what people search. It does not tell you how many pages to write. This is the whole workflow — export, clean, cluster, prioritise — in about ten minutes, without your keyword list leaving your browser.

If you are not sure what clustering is or why it matters, start with what is keyword clustering. This guide assumes you are convinced and want the mechanics.

Step 1 — Get a keyword list

You need a CSV with one keyword per row. Ideally it also has a volume column, but clustering works without one.

If your site already has traffic: Search Console

This is the best source, because it is your real data rather than an estimate. In Google Search Console go to Performance → Search results, set the date range to Last 12 months, then Export → Download CSV. The file you want from the zip is Queries.csv.

Search Console caps the export at 1,000 rows per view. To get more, filter by page or by country and export several times.

If your site is new: Keyword Planner

Google Ads Keyword Planner is free and needs no ad spend, though the signup nudges you toward creating a campaign. When it does, look for the small Switch to Expert Mode link, then Create an account without a campaign. Inside, go to Tools → Keyword Planner → Discover new keywords, enter three to seven seed terms, and use Download keyword ideas.

One honest caveat: without active spend, Google shows volumes as buckets like 1K – 10K rather than exact numbers. Treat them as rough ordering, not truth.

Step 2 — Clean the list

Five minutes here saves an hour later. Remove:

• Your own brand terms. They cluster together into a group that tells you nothing.
• Obvious typos and one-off long tails with a single impression.
• Other languages, unless you intend to plan for them separately. Mixed-language lists produce mixed-language clusters.
• Anything you have no intention of competing for. A keyword list is not a to-do list.

Duplicates you can leave — most tools drop them for you.

Step 3 — Group by meaning, not by shared words

There are two keyword grouping methods available to you here. You can group by shared words in a spreadsheet — tag every keyword against a list of terms, then pivot — which is fast, exact and completely reasonable for a few hundred keywords; the Excel formulas are in their own guide. Or you group by meaning, which is what the rest of this step does and what makes the difference on a list of any size.

Drop the CSV into the tool. It reads the file in your browser, converts each keyword into a vector using a compact language model, and groups the ones that sit close together in meaning. A few hundred keywords take seconds; a few thousand take a minute or two, mostly spent downloading the model the first time.

What comes out is a map. Each sphere is one topic, its size is how many keywords it contains, and lines connect topics that belong together. The small dots orbiting each sphere are your individual keywords.

Because the model runs locally, nothing is uploaded — which matters if the list belongs to a client and you would otherwise need a data-processing agreement to paste it into a web app.

Step 4 — Read the map before you write anything

Three readings, in this order.

Sort by opportunity

The most useful sort is not "most keywords" but volume concentrated in few keywords. A topic with high total search volume spread across only a handful of terms is one that a single good page can plausibly take. Topics with hundreds of keywords are usually competitive and slow.

Colour by search intent

Switch colouring to search intent. Roughly three quarters of any keyword list is informational — "how", "what", "guide". The interesting minority is commercial ("best", "vs", "alternatives") and transactional ("buy", "price"). Commercial clusters are where readers choose a product, so they convert; informational clusters build the authority that makes the commercial ones rankable. You need both, and if you are new, commercial pages are usually worth writing earlier than people expect.

Check the gaps against a competitor

If you have a competitor's keyword export, load it as a second file. Topics they cover and you do not are your content gaps, and they are usually a more reliable prioritisation signal than volume alone — someone has already validated that the topic is worth covering.

Step 5 — Turn clusters into pages

Open a topic and you get a brief: how many keywords, total monthly searches, the intent mix, related topics to link to internally, and the keyword list sorted by volume. That is enough to write from.

For each cluster you decide to write, record:

Export the clusters as CSV and you have a content calendar. Export the map as PNG if you need to show a client why you are proposing eleven pages rather than sixty.

Step 6 — Mine what you already have

Everything above plans new pages. If your site already has traffic, the same export usually answers a cheaper question first: which pages are nearly winning. Two columns decide whether you get this, and most people export without them.

Export Search Console with position and page

In Performance → Search results, Export → Download CSV gives you a zip of separate sheets. Queries.csv carries a Position column, and that alone unlocks the first view below.

The second view needs something Search Console's own export will not give you: the query and the URL that ranked for it, in the same file. Queries.csv and Pages.csv are separate sheets, so pairing them is not possible from the UI export. Three sources do carry both side by side:

• Looker Studio with the Search Console connector, using Query and Landing Page as dimensions, then exporting the table as CSV. Free.
• The Search Analytics for Sheets add-on, which pulls the same pairing through the Search Console API into a spreadsheet. Free.
• An Ahrefs organic-keywords export or a Semrush organic-positions export, both of which put keyword and URL in adjacent columns.

With one of those, two extra views appear:

• Quick wins — queries ranking roughly 8th to 25th. A page already exists and already earns impressions, so improving it is far cheaper than writing something new. Sort by impressions and start at the top.
• Overlaps — queries where more than one of your own URLs takes impressions. Two pages splitting one query is the everyday form of cannibalisation, and the fix is usually to merge them or to point one at the other.

Neither needs anything beyond the file you already downloaded.

Then crawl the site itself

A keyword file describes demand; a crawl describes what you actually published. To produce one:

1. Install Screaming Frog SEO Spider. The free tier covers 500 URLs, which is most small sites in full.
2. Enter your domain and press Start. Wait for the crawl to reach 100%.
3. Open the Internal tab, set the filter to HTML, and choose Export. Save the CSV.
4. Optional but worth it: Bulk Export → Links → All Inlinks. This is the link graph.

Drop the first file in and the tool switches to its Pages workspace automatically — the file type is detected, so there is no mode to select. You get the topic map of your existing site, an Issues view listing everything the crawl can prove (broken pages, redirects, orphans, thin content, duplicate and missing titles, near-identical pages), and Link ideas: pairs of pages covering one topic with no link between them. Add the All Inlinks export as a second file and the pairs that already link are filtered out, leaving only genuine gaps.

Two things to watch

Split any cluster over about fifteen keywords. Large clusters often hide two intents. Our own top cluster is a good example: twenty-odd terms, all correctly about keyword clustering, but half of them wanted an explanation and half wanted a tool to use — two pages, not one. The full worked example is here.

Do not write every cluster. A keyword list describes a market, not an obligation. Pick the three or four you can genuinely write better than what currently ranks, and check that by actually googling the main keyword first. If page one is entirely large established brands, take the narrower topic next to it instead.

Next