I played pickup basketball recently for the first time in about six years. My previous attempt to make a comeback ended up with a very badly sprained ankle. I am glad to report no injuries this time, though my lower limbs are making themselves known.
Just like my jump shot, I recently dusted off something else from my past: topic clusters.
I think the AI-era is exposing two things:
That website traffic as a metric is/was stupid and that most content is/was corporate navel-gazing with zero impact on the bottom line.
That commodity content (what is X, how to do Y) is dead.
I have no well-formed answer yet on how to navigate this beyond #tryyourbest and fight the urge to start dancing on TikTok.
All of that said I do think there is value in thinking about your website as an entity (and how your content feeds the entity’s reputation). And also that it should be easy for any bot to identify the page on your website for a specific topic.
And because I’m not ready to dance.
First, the results
I’ve run this pipeline for two topic clusters. It’s helped me identify:
79 pages that need to be retired or merged into existing pages.
The 16% of pages that drive 93% of the clicks.
Eleven pages splitting 107K impressions for one keyword at a collective 0.1% CTR
And a host of other things!
I have now rewritten/published seventeen pages off the back of it, with plans to scale production. It’s a little soon to declare victory, but early signs are promising.
What you need to run this
You need to enable the Search Console API and create credentials. This is free and if you ask your LLM of choice it will explain the steps. (Note: it’s a bit of a pain in the ass though, tut-tut Google.)
Ahrefs API (or similar SEO tool).
Claude Code (or Codex) to write scripts.
I also use AirOps for fan out queries and to understand what articles are getting cited.
The workflow architecture
Seven scripts, run in order. Every output is a CSV, every API response is cached, and the final deliverables (a decision workbook and the stakeholder deck itself) are generated from the data, so a re-run refreshes every number in every slide.
[Inventory: markdown corpus scan]
|
v
[GSC pull: page metrics]───[GSC pull #2: top 250 queries PER URL]
| |
v v
[Ahrefs pull: you + competitors] [Intent classification]
| |
v |
[Embeddings: local bge-small] |
| |
+──────────────┬───────────────+
v
[Join → master.csv]
|
+────────+────────+
v v
[Decision workbook] [Deck, generated]Step 1: Inventory what actually exists
Get your site into a markdown corpus first (crawl it once; ours is ~6,600 pages of markdown). Shoutout to my awesome web devs who did this for me (it now runs weekly). You can also figure it out with Screaming Frog if you don’t have awesome web devs at your disposal.
Then a script scans it for pages materially about the topic. The topic has to be the page’s primary subject and not a passing mention. After it writes a CSV: URL, slug, title, page type, word count.
This sounds trivial, but is probably the most important step of the process. “How many pages do we have about X” is a question most content teams can’t answer easily if they’re managing a large-ish site.
It’s also where the skeletons surface. I saw the wrong-language pages on English URLs; debris from a platform migration eighteen months earlier, sitting there cannibalizing the English pages for their own keywords.
Step 2: Pull the queries, not the clicks
Two Search Console scripts. The first pulls standard page-level metrics for a 90-day window. Everyone has this.
The second is the one that matters: top 250 queries for every significant URL — about 9,000 rows for one cluster's 43 top pages.
The GSC UI actively discourages this view, which is why nobody has it, which is why page-level click counts survive as a KPI despite being largely meaningless.
I then classify every query into intent buckets. This is an LLM-shaped task; the prompt is roughly:
Classify each search query into exactly one bucket for [COMPANY],
a [CATEGORY] company:
- JUNK: hack/spy/surveillance intent, prank tools (”bomber”),
pirated/modified apps, anything with zero legitimate buyer intent
- CONSUMER: legitimate but non-commercial (”is [app] down”,
“[app] usage by country”)
- BUSINESS: API/developer, business accounts, pricing, features,
OTP/verification, marketing use cases, branded queries
Return query, bucket, confidence. When unsure between CONSUMER
and BUSINESS, look at whether the query implies operating an
account at scale.Roll the buckets up per page and every URL gets an honest label. Ours ranged from 95% business intent (keep, obviously) to 96% craptacular on the biggest traffic page in one cluster (kill, obviously).
Step 3: Competitor keyword universes
One Ahrefs script pulls the keyword universe for your domain and each competitor in the category, caching every response so re-runs are free. This feeds two things: evidence for the cannibalization calls, and expansion opportunities. These are keywords where a competitor ranks #1 and you don’t have a page at all.
Step 4: Embeddings, locally, for free
Embeddings get a lot of hate as a nerdy SEO thing that people use to sell expensive agency engagements. That said, content cannibalization is mostly a machine problem and therefore benefits from a machine-based analysis:
Strip each page to its main content. Kill the nav, footer, CTA blocks, and related-posts modules before you do anything else. Skip this and every page on your site scores as similar to every other page.
Chunk into ~250-word passages with 40-word overlap; embed every chunk with BAAI/bge-small-en-v1.5 via sentence-transformers. This runs on a laptop, no API (1,239 passages took a couple of minutes).
Mean-pool chunks into one normalized vector per page, and keep the chunk vectors. You want two similarity numbers per page pair: whole-page cosine, and the max similarity between any chunk of one page and any chunk of the other. The first catches twin pages. The second catches the sneakier case, like a 3,000-word guide whose middle section duplicates another page wholesale, which mean-pooling averages into invisibility.
Flag thin pages (under ~200 words of real content) rather than scoring them.
Compute all pairwise similarities; keep pairs ≥ 0.70 as a triage list, not a verdict list. Agglomerative clustering over the page vectors gives you topic groups — useful for organizing the review, but clusters shift with the linkage settings, so treat them as folders and not findings.
Step 5: Join and generate
A join script merges inventory + GSC + intent + Ahrefs + AI-citation data into one master table, one row per URL. Two generator scripts then build the human-facing outputs: a multi-sheet Excel decision workbook and the PowerPoint deck itself.
Note: You need to look over this stuff manually. I have caught mistakes. (People love to call out LLMs for making mistakes, I also catch my own mistakes.)
Making the calls
The pipeline assembles evidence and then it’s up to me to pour over them and sanity check the whole thing. Every URL gets KEEP / KEEP+REWRITE / MERGE / KILL / CHECK-FIRST (that last one for anything that might be in active campaigns). Three rules of thumb from two rounds of this:
Kill your winners when the queries say so. I’ve killed a cluster’s #1 traffic page. Ranking top-10 for a term that is spammy isn’t something to brag about.
Keep despite similarity when the citations say so. Figure out how to reangle or rework if valuable and being cited.
I’ve struggled a bit with how to manage these across multiple languages, so it’s something I’m still working through.
What happens to the keepers
Every kept page then goes through a rewrite discipline that will get its own post soon, but the shape is: a query fan-out audit before outlining (enumerate the sub-queries an AI engine decomposes your topic into — attributes, comparisons, tasks, regions — and score the current page against each one), the rewrite grounded in a verified internal fact base (truth layer) rather than whatever the old page claimed, and a verification report mapping every factual claim to its source.
Important caveats
The intent classification is only as good as your buckets. Spend time reading raw queries before you define them; our “junk” category was invented after seeing all the junk queries.
Small embedding models are fine for near-duplicate detection at page level. They are not fine for judging content quality which is a very subjective, very human job.
The similarity threshold does not transfer between corpora. Calibrate every time; it’s ten minutes.
GSC’s per-URL query API caps what it returns and samples long-tail queries.