SEO keyword intelligence: turning search data into a content engine

Most keyword research produces a spreadsheet that gets used once and forgotten. Someone exports a few hundred keywords, sorts by volume, picks the top ten, and by the next quarter the list is stale and the process starts over from scratch. The problem is not the research; it is that it never becomes a system. A proper keyword intelligence pipeline treats keyword data as a living asset: it clusters queries by intent, scores them against your own real search performance, and turns the output directly into a content brief a writer can use, on a cadence that keeps the whole thing current.

This guide builds that pipeline: pulling live query data, clustering it by semantic similarity rather than surface-level overlap, scoring opportunity against your actual impressions and position, and generating a structured brief automatically from the highest-opportunity cluster.

Start from real query data, not a keyword tool’s guesses

The richest, most honest keyword data you have access to is the query data your own site already generates in Search Console: exactly what people typed, how often it showed your page, and where you ranked. A third-party keyword tool estimates volume for a market; Search Console tells you what actually happened for your domain, which is a categorically better starting point for prioritisation.

def pull_queries(gsc, site_url, days=90, row_limit=25000):
    from datetime import date, timedelta
    start = (date.today() - timedelta(days=days)).isoformat()
    end = date.today().isoformat()
    body = {"startDate": start, "endDate": end,
            "dimensions": ["query", "page"], "rowLimit": row_limit}
    resp = gsc.searchanalytics().query(siteUrl=site_url, body=body).execute()
    return [{
        "query": r["keys"][0], "page": r["keys"][1],
        "clicks": r["clicks"], "impressions": r["impressions"],
        "ctr": r["ctr"], "position": r["position"],
    } for r in resp.get("rows", [])]

Ninety days is a reasonable window for a first pull: long enough to smooth out weekly noise, short enough to reflect current search behaviour rather than a stale pattern from a year ago.

Cluster queries by intent, not by shared words

Grouping “credit risk model” with “credit card application” because they share a word is a mistake a naive keyword grouping makes constantly. What you actually want is queries clustered by semantic intent, so that “risk scorecard build” and “PD model development” land together even though they share no words, because a searcher typing either is looking for the same thing. TF-IDF vectorisation followed by clustering gets a workable version of this without needing a large embedding model.

from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.cluster import AgglomerativeClustering
import numpy as np

def cluster_queries(queries, n_clusters=12):
    texts = [q["query"] for q in queries]
    vec = TfidfVectorizer(ngram_range=(1, 2), min_df=2, stop_words="english")
    X = vec.fit_transform(texts).toarray()
    model = AgglomerativeClustering(n_clusters=n_clusters, metric="cosine", linkage="average")
    labels = model.fit_predict(X)
    for q, label in zip(queries, labels):
        q["cluster"] = int(label)
    return queries

For a genuinely semantic clustering, swap the TF-IDF vectors for embeddings from a language model and the rest of the pipeline is unchanged; agglomerative clustering with cosine distance works well on either representation, and starting with TF-IDF keeps the pipeline dependency-light and fast to run.

Name each cluster automatically

A cluster of numbers is not useful to a content strategist; it needs a human-readable label. The simplest reliable approach is to take the highest-impression query in each cluster as its representative name, which tends to be the most recognisable phrase a reader would use to describe the topic.

from collections import defaultdict

def label_clusters(queries):
    by_cluster = defaultdict(list)
    for q in queries:
        by_cluster[q["cluster"]].append(q)
    labels = {}
    for cid, items in by_cluster.items():
        top = max(items, key=lambda q: q["impressions"])
        labels[cid] = top["query"]
    return labels

Score opportunity, not just volume

Impressions alone favour big, already-visible topics. The score that actually drives a content calendar should reward clusters with real demand, weak current ranking, and headroom to improve, which is where the content investment pays off fastest. A cluster ranking on page four with meaningful impressions is a far better target than one you already dominate.

def score_cluster(items):
    total_impr = sum(q["impressions"] for q in items)
    total_clicks = sum(q["clicks"] for q in items)
    avg_position = sum(q["position"] * q["impressions"] for q in items) / max(1, total_impr)
    ctr = total_clicks / total_impr if total_impr else 0

    # reward impressions, reward weak position (headroom), reward low CTR (unrealised demand)
    position_headroom = max(0, avg_position - 3) / 20     # normalise roughly to 0..1
    ctr_gap = max(0, 0.15 - ctr) / 0.15                    # distance below a healthy CTR

    return round(total_impr * (0.5 + 0.3 * position_headroom + 0.2 * ctr_gap), 1)

def rank_clusters(queries):
    by_cluster = defaultdict(list)
    for q in queries:
        by_cluster[q["cluster"]].append(q)
    scored = [(cid, score_cluster(items), items) for cid, items in by_cluster.items()]
    return sorted(scored, key=lambda x: -x[1])

The weights here are a starting point, not a law. Tune them against your own judgement by checking whether the top-ranked clusters match what your team would independently flag as worth writing about; if they consistently do not, adjust the weighting rather than trusting the formula blindly.

Generate a structured brief from the top cluster

Once a cluster is identified as the priority, the queries inside it are the raw material for a content brief. Grouping by shared sub-phrases within the cluster surfaces the subtopics a comprehensive article should cover, and the top individual queries become natural section headings.

def build_brief(cluster_items, cluster_label):
    sorted_by_impr = sorted(cluster_items, key=lambda q: -q["impressions"])
    primary = sorted_by_impr[0]["query"]
    secondary = [q["query"] for q in sorted_by_impr[1:8]]
    weak_pages = sorted(
        {(q["page"], q["position"]) for q in cluster_items if q["position"] > 10},
        key=lambda x: -x[1]
    )
    return {
        "topic": cluster_label,
        "primary_keyword": primary,
        "supporting_keywords": secondary,
        "target_word_count": 1500,
        "existing_weak_pages": weak_pages[:3],
        "suggested_sections": [f"What is {primary}?"] +
                              [f"How {kw} works" for kw in secondary[:4]] +
                              ["Practical implementation", "Key takeaways"],
    }

The brief is deliberately structured as data, not prose, because a structured object can feed a writer, a content-management system, or a further AI drafting step interchangeably. Whichever way it is consumed downstream, it always carries the same evidence: the real queries, their real performance, and where the existing content is under-serving them.

Track whether the content actually closed the gap

The loop only closes if you check back. A few weeks after publishing against a brief, re-pull the same cluster’s Search Console data and compare position and impressions before and after. If the new content moved the cluster’s average position meaningfully, the scoring and clustering approach is working and worth trusting further. If it did not, that is useful information too, whether about the content itself, the competitiveness of the cluster, or a scoring weight that overvalued headroom that turned out not to be reachable.

def compare_before_after(gsc, site_url, cluster_queries, published_date):
    before = pull_queries(gsc, site_url, days=90)   # pulled before publishing, stored
    after = pull_queries(gsc, site_url, days=30)    # pulled after enough time has passed
    q_set = {q["query"] for q in cluster_queries}
    b_pos = [q["position"] for q in before if q["query"] in q_set]
    a_pos = [q["position"] for q in after if q["query"] in q_set]
    return {
        "avg_position_before": sum(b_pos) / len(b_pos) if b_pos else None,
        "avg_position_after": sum(a_pos) / len(a_pos) if a_pos else None,
    }

Run it as a monthly cadence, not a one-off project

The full value of this pipeline comes from running it on a schedule, not once. Pull fresh Search Console data monthly, recluster, rescore and regenerate briefs, and the content calendar stays anchored to what search behaviour is actually doing rather than a plan made a year ago. Clusters that scored highly and got written up drop in priority naturally as their headroom closes; new clusters emerge as search behaviour shifts. The pipeline does not replace editorial judgement about what is worth writing; it replaces the guesswork about where to point that judgement first.

Watch for cannibalisation before you commission new content

A high-opportunity cluster is not automatically a signal to write something new. Sometimes the cluster’s weak average position exists because two or three of your own pages are competing against each other for the same queries, splitting authority and confusing search engines about which page should rank. Checking for this before generating a brief prevents the common mistake of publishing a fourth article on a topic three existing pages already half cover.

def detect_cannibalisation(cluster_items, min_pages=2):
    pages = defaultdict(int)
    for q in cluster_items:
        pages[q["page"]] += q["impressions"]
    ranked_pages = sorted(pages.items(), key=lambda x: -x[1])
    if len(ranked_pages) >= min_pages and ranked_pages[1][1] > 0.3 * ranked_pages[0][1]:
        return True, ranked_pages[:3]
    return False, ranked_pages[:1]

When cannibalisation is detected, the right brief is not “write a new article”; it is “consolidate these pages into one, redirect the weaker ones, and strengthen the survivor”. That is a less exciting recommendation than a shiny new content brief, and it is very often the higher-leverage action, since consolidating authority onto one strong page frequently improves ranking more than any amount of new content aimed at a cluster your own site is already quietly competing against itself for.

Prioritise clusters against effort, not opportunity alone

The opportunity score answers how much a cluster is worth; it says nothing about how expensive that cluster is to serve well. A cluster demanding deep technical explanation with code examples takes materially longer to write well than one answering a straightforward definitional question, and a realistic content calendar weighs both. Attaching a rough effort estimate, even a simple low, medium or high tag based on the cluster’s apparent technical depth, and dividing opportunity by effort produces a prioritisation that a small content team can actually execute against, rather than a list that looks impressive but is not sequenced for the resources available to write it.

Why this earns more trust than a keyword tool’s volume estimate

Third-party keyword tools estimate national or global search volume for a term, aggregated across every website that could conceivably rank for it, which is a reasonable starting point for discovering that a topic exists at all but a poor guide to prioritising your own content calendar. A term with enormous estimated volume might be dominated by a handful of authoritative publishers you have no realistic path to outranking soon, while a term with modest estimated volume might be one where your specific domain already has partial visibility and a clear route to full ownership within a quarter. Search Console data sidesteps this entirely, because every number in it is already conditioned on your domain: the impressions are impressions your pages actually received, the position is where your pages actually rank, and the click-through gap is a gap your specific searchers are actually experiencing. A content calendar built from this data is therefore not a bet on a generic market opportunity; it is a direct response to demand your site has already demonstrated it can partially capture, which is a categorically safer basis for deciding where a small content team should spend its limited time next.

Combine it with a keyword tool for discovery, not prioritisation

None of this argues for abandoning third-party keyword tools entirely; it argues for using them for the one job they genuinely do well, which is discovering terms your site has never ranked for at all and therefore generates zero Search Console data on. A brand-new product line or a topic you have never published about will not appear in your own query history no matter how long you wait, simply because you have never been shown for it. A keyword tool fills exactly that gap: use it to discover candidate terms for genuinely new territory, then let the Search Console-driven pipeline described here take over the moment you have published anything and generated even a few weeks of real performance data. The tool answers what might exist; your own data answers what is actually working, and treating the two as complementary rather than competing sources produces a far more complete picture than relying on either alone.

Key takeaways

1. Start from your own Search Console query data, which reflects real behaviour rather than estimated volume.
2. Cluster by semantic intent, not shared words, so genuinely related queries group together.
3. Score opportunity by combining impressions, position headroom and unrealised click-through, not volume alone.
4. Turn the top cluster directly into a structured brief: primary keyword, supporting terms, weak existing pages, suggested sections.
5. Close the loop by re-measuring position after publishing, so the scoring approach earns or loses trust on evidence.
6. Run the whole pipeline monthly so the content calendar tracks real search behaviour instead of a stale plan.

A spreadsheet of keywords is a snapshot; a pipeline like this is a system that keeps pointing your content effort at wherever the real opportunity has moved to next.