Keyword Clustering

Two keywords belong together when Google returns the same pages for both. Not when the words look alike. If most of the top results are shared, one page can rank for both. That's a fact about the SERP, not a guess about language. Keyword Insights proved the method. It also charges monthly, and the roadmap is theirs.

What bothers me is where the tool stops. The clusters come back and my actual question is still open. Which of these is worth paying a writer for? How much link work does it take to reach the top three? What would the same clicks cost on Ads instead? Nothing I've used closes that loop, from clusters to money.

So I built both halves.

docker compose up starts Postgres, Redis, a FastAPI backend and a worker. Upload a CSV. Each keyword's SERP is fetched through DataForSEO and cached for thirty days, so a second run over overlapping keywords costs nothing. Clustering builds an inverted index of URL to keyword, scores every pair that co-occurs on at least one URL by how many of the top seven results they share, and drops anything under the threshold. Three shared URLs by default, adjustable from one to seven. Connected components are the clusters. Out comes JSON and a workbook: two sheets for a clustering run, six with economics.

The half nobody sells

Give it a brand domain and an Ahrefs key and every cluster gets an investment case. Capital outlay split into content and links, expected monthly value, probability of reaching the top three, and the month the capital comes back.

Maintenance comes out before breakeven. Content goes stale and links decay, so the model carries a monthly burn: a refresh every twelve months at a quarter of the original cost, links decaying fifteen per cent a year. Breakeven divides capital by value minus that burn, never by gross value. Each cluster also carries what the same clicks would cost on Ads, the spread between that and the burn, and the cost of not shipping: what a page you already rank with loses over twelve months at two per cent a month. SERP features scale that decay. An AI overview takes it to 1.8 times baseline, a shopping pack to 1.35, capped at 2.5.

Probability is a sigmoid over log ratios rather than linear gaps. Brand against the top-three median on DR, on dofollow referring domains, and on difficulty. DR 10 to 20 is a doubling. DR 70 to 80 isn't. A linear coefficient pretends those are the same move.

Where it's guessing

Probability and months-to-rank are estimates. They rank one cluster against another. They aren't promises.

When Ahrefs has no difficulty score, the run estimates one from the SERP's own authority and labels the row with where the number came from. When a fetch fails it writes a failure row instead of a zero, because "this keyword has no volume" and "the API returned a 500" shouldn't look identical. Referring-domain counts are always filtered to dofollow.

Not finished

Agglomerative clustering raises NotImplementedError. Topical clusters, the competitor scan and the twenty-four month projection are stubs that return nothing behind a docstring. There's a model picker on the form for an LLM layer no stage calls. Every SERP goes through DataForSEO's live queue at roughly three times the price of the async one. The client already has the submit, ready and get calls. Nothing in the fetch stage uses them.

None of that is a decision, it just isn't written yet. The thin HTMX interface is the opposite. The plan called for a Next.js front end with a graph view, and I'd rather not maintain one.

It runs on my own machine, single user, one API key. Nothing is deployed and there's nothing to sign up for.

Stack: Python and FastAPI with Jinja and HTMX, so the interface is server-rendered HTML with no front-end build to keep alive. Postgres holds every SERP, cluster and cached metric, with pgvector there for embedding work I haven't done. Redis and RQ run jobs in the background so a long SERP fetch doesn't hold a request open. Docker Compose ties the four together, because "clone it and run one command" is the only install instruction I want to write.