Keyword Cannibalization in Python: Find Competing Pages by Intent

The keyword cannibalization checker on this site works the way most of them do. It groups Search Console rows by query and flags any query where two of your URLs show up. It’s useful, and it’s wrong in two directions at once.

It misses pages that fight over one searcher without ever sharing an exact query. “seo timeline” on one page and “how long does seo take” on another look unrelated to a string match. And it flags pages that only share a phrase, like a service page and a pricing page that both turn up for “dental seo”.

So I built the version I wanted. A Python script compares what each page ranks for by meaning, then checks the flagged URLs against the live site. It runs on a laptop with no API key. On a synthetic set of 18,000 rows and about 1,500 pages it finished in 13 seconds.

Below: how it works, the full code, how I picked the threshold (and got it wrong first), and what it found on two of my own sites.

Where exact match goes wrong

Take my second site. Over about six months of data, 36 pairs of pages shared at least one exact query. That’s the list a standard checker hands you.

Then I asked a different question: do the two pages’ whole sets of queries overlap in meaning? Only 12 of the 36 pairs did. The other 25 dropped out because the shared query was a small slice of what at least one of the two pages ranks for. And one of the 12 had no shared query at all, which exact match could never flag.

A shared query is weak evidence on its own. The overlap in meaning is what tells you two pages are chasing one audience.

The idea: give every page a query profile

Search Console already tells you what Google shows each page for. So instead of embedding titles or page text, I embed those queries.

1. Embed every unique query with a small local model.
2. For each page, average its query vectors, weighted by the square root of impressions. The square root stops one huge head query from becoming the whole page.
3. Score a pair of pages by overlap: the share of page A’s impressions that sit on queries with a near twin on page B (cosine similarity of 0.92 or more). Do it both ways and keep the smaller number.

Keeping the smaller number filters out the lopsided cases. A homepage shows for a bit of everything, so a service page can overlap into it heavily while the homepage barely overlaps back. That’s a hub and a spoke, not two pages fighting. Both directions have to be high before a pair gets flagged.

Pairs are only compared with each page’s 15 nearest profiles, which is how 1,500 pages stay quick.

Setup

pip install fastembed numpy google-api-python-client google-auth

The first run downloads BAAI/bge-small-en-v1.5 (around 130 MB). After that it’s offline and runs on a CPU.

For data, either export query and page rows from Search Console into a CSV with the columns query, page, clicks, impressions, position, or use a service account and let the script pull them. The service account setup is the same as in my earlier post on the Search Console API, including why you have to page through every row yourself.

The script

"""Find pages on one site that compete for the same search intent.

Two signals, both from Search Console:
  1. exact overlap: the same query shows two of your URLs
  2. intent overlap: queries that mean the same thing, even when the words differ

Each page gets a profile, the impression-weighted average of the embeddings of
the queries it shows for. Pages with close profiles serve the same searcher.

Usage:
  python3 cannibal.py --csv gsc.csv --brand yourbrand
  python3 cannibal.py --site sc-domain:example.com --key service-account.json --brand yourbrand

The CSV needs the columns: query, page, clicks, impressions, position.
"""
import argparse
import csv
import datetime as dt
import re
import urllib.error
import urllib.request
from collections import defaultdict

import numpy as np

MODEL = "BAAI/bge-small-en-v1.5"  # 130 MB, runs on a laptop CPU, no API key
NEAR = 0.92  # two queries this close count as the same intent. 0.85 was too loose on my data
MIN_QUERIES = 3  # a page with one or two queries gives an overlap of 100% by accident
MIN_OVERLAP = 0.25  # share of a page's impressions that must sit on shared intent
DOMINANT_GAP = 5.0  # average position gap that makes one page the clear winner


def fetch_rows(site, key_file, days=90):
    from google.oauth2 import service_account
    from googleapiclient.discovery import build

    creds = service_account.Credentials.from_service_account_file(
        key_file, scopes=["https://www.googleapis.com/auth/webmasters.readonly"]
    )
    gsc = build("searchconsole", "v1", credentials=creds)
    end = dt.date.today() - dt.timedelta(days=3)
    body = {
        "startDate": (end - dt.timedelta(days=days)).isoformat(),
        "endDate": end.isoformat(),
        "dimensions": ["query", "page"],
    }
    rows, start = [], 0
    while True:  # the API pages by startRow and sorts by clicks, so take it all
        batch = gsc.searchanalytics().query(
            siteUrl=site, body=dict(body, rowLimit=25000, startRow=start)
        ).execute().get("rows", [])
        rows += batch
        if len(batch) < 25000:
            break
        start += 25000
    return [
        dict(query=r["keys"][0], page=r["keys"][1], clicks=r["clicks"],
             impressions=r["impressions"], position=r["position"])
        for r in rows
    ]


def read_csv(path):
    with open(path, newline="", encoding="utf-8") as f:
        return [
            dict(query=r["query"], page=r["page"], clicks=float(r["clicks"]),
                 impressions=float(r["impressions"]), position=float(r["position"]))
            for r in csv.DictReader(f)
        ]


def clean(rows, brand_terms, min_impr):
    """Drop brand queries, scraper-style operators and rows too thin to trust."""
    out = []
    for r in rows:
        q = r["query"].lower()
        if any(b in q for b in brand_terms) or re.search(r"\s-?site:|\binurl:|\bintitle:", q):
            continue
        if r["impressions"] >= min_impr:
            out.append(r)
    return out


def embed(texts):
    from fastembed import TextEmbedding

    vecs = np.array(list(TextEmbedding(model_name=MODEL).embed(texts)), dtype=np.float32)
    return vecs / np.linalg.norm(vecs, axis=1, keepdims=True)


def build_pages(rows, vec_of):
    pages = defaultdict(lambda: {"q": {}, "impr": 0.0, "clicks": 0.0, "pos_w": 0.0})
    for r in rows:
        p = pages[r["page"]]
        p["q"][r["query"]] = r["impressions"]
        p["impr"] += r["impressions"]
        p["clicks"] += r["clicks"]
        p["pos_w"] += r["position"] * r["impressions"]
    for u in [u for u, p in pages.items() if len(p["q"]) < MIN_QUERIES]:
        del pages[u]
    for p in pages.values():
        p["pos"] = p["pos_w"] / p["impr"]
        qs = list(p["q"])
        # sqrt keeps one huge head query from being the whole page
        w = np.sqrt([p["q"][q] for q in qs])
        prof = (np.array([vec_of[q] for q in qs]) * w[:, None]).sum(axis=0)
        p["profile"] = prof / np.linalg.norm(prof)
        p["mat"] = np.array([vec_of[q] for q in qs])
        p["qlist"] = qs
    return pages


def overlap(a, b):
    """Share of page a's impressions on queries that have a near twin on page b."""
    sims = a["mat"] @ b["mat"].T
    hit = sims.max(axis=1) >= NEAR
    imp = np.array([a["q"][q] for q in a["qlist"]])
    return float(imp[hit].sum() / imp.sum())


def verdict(a, b, exact):
    gap = abs(a["pos"] - b["pos"])
    if gap >= DOMINANT_GAP:
        return "keep the stronger page, retarget the weaker one"
    if exact:
        return "merge the weaker into the stronger, 301, repoint links"
    return "same searcher, different words: differentiate titles or merge"


def candidate_pairs(pages, k=15):
    """Compare each page only with its k nearest profiles, so 5,000 pages stay quick."""
    urls = list(pages)
    P = np.array([pages[u]["profile"] for u in urls])
    S = P @ P.T
    np.fill_diagonal(S, -1)
    seen = set()
    for i in range(len(urls)):
        for j in np.argsort(-S[i])[:k]:
            pair = tuple(sorted((i, int(j))))
            if i != j and pair not in seen:
                seen.add(pair)
                yield urls[pair[0]], urls[pair[1]]


def find_pairs(pages):
    out = []
    for ua, ub in candidate_pairs(pages):
        a, b = pages[ua], pages[ub]
        exact = sorted(set(a["q"]) & set(b["q"]))
        oa, ob = overlap(a, b), overlap(b, a)
        score = min(oa, ob)  # both pages must be hurt, otherwise it is just a niche inside a hub
        if score < MIN_OVERLAP:
            continue  # a shared phrase alone is not enough if the intents are not close
        out.append({
            "page_a": ua, "page_b": ub,
            "intent_similarity": round(float(a["profile"] @ b["profile"]), 3),
            "overlap_a": round(oa, 2), "overlap_b": round(ob, 2),
            "shared_queries": len(exact),
            "impressions_at_stake": int(a["impr"] + b["impr"]),
            "pos_a": round(a["pos"], 1), "pos_b": round(b["pos"], 1),
            "suggestion": verdict(a, b, bool(exact)),
            "example_queries": " | ".join(exact[:3]),
        })
    return sorted(out, key=lambda x: (x["overlap_a"] + x["overlap_b"]) * x["impressions_at_stake"], reverse=True)


class _NoRedirect(urllib.request.HTTPRedirectHandler):
    def redirect_request(self, *args, **kwargs):
        return None


def live_state(url, _cache={}):
    """What the URL does today: redirects, canonicalises elsewhere, or is fine."""
    if url in _cache:
        return _cache[url]
    opener = urllib.request.build_opener(_NoRedirect)
    req = urllib.request.Request(url, headers={"User-Agent": "Mozilla/5.0 (cannibal-check)"})
    state = "ok"
    try:
        html = opener.open(req, timeout=20).read().decode("utf-8", "replace")
        m = re.search(r'<link[^>]+rel=["\']canonical["\'][^>]*href=["\']([^"\']+)', html, re.I)
        if m and m.group(1).rstrip("/") != url.rstrip("/"):
            state = "canonical to " + m.group(1)
    except urllib.error.HTTPError as e:
        state = f"{e.code} to " + e.headers.get("Location", "?") if 300 <= e.code < 400 else f"http {e.code}"
    except OSError:
        state = "unreachable"
    _cache[url] = state
    return state


def add_live_state(pairs):
    """Search Console remembers URLs for months. Drop pairs you already fixed."""
    for p in pairs:
        p["state_a"], p["state_b"] = live_state(p["page_a"]), live_state(p["page_b"])
        if p["state_a"] != "ok" or p["state_b"] != "ok":
            p["suggestion"] = "already handled, the data is history"
    return pairs


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--csv")
    ap.add_argument("--site")
    ap.add_argument("--key", default="service-account.json")
    ap.add_argument("--brand", nargs="*", default=[])
    ap.add_argument("--min-impressions", type=int, default=5)
    ap.add_argument("--no-verify", action="store_true", help="skip the live redirect and canonical check")
    ap.add_argument("--out", default="cannibalisation.csv")
    args = ap.parse_args()

    rows = read_csv(args.csv) if args.csv else fetch_rows(args.site, args.key)
    rows = clean(rows, [b.lower() for b in args.brand], args.min_impressions)
    queries = sorted({r["query"] for r in rows})
    vecs = embed(queries)
    pages = build_pages(rows, dict(zip(queries, vecs)))
    pairs = find_pairs(pages)
    if not args.no_verify:
        pairs = add_live_state(pairs)

    if pairs:
        with open(args.out, "w", newline="", encoding="utf-8") as f:
            w = csv.DictWriter(f, fieldnames=pairs[0].keys())
            w.writeheader()
            w.writerows(pairs)
    print(f"{len(rows)} rows, {len(queries)} queries, {len(pages)} pages, {len(pairs)} competing pairs")
    for p in pairs[:10]:
        print(f"\n{p['page_a']}\n{p['page_b']}\n  overlap {p['overlap_a']}/{p['overlap_b']}, "
              f"shared {p['shared_queries']}, positions {p['pos_a']}/{p['pos_b']}: {p['suggestion']}")


if __name__ == "__main__":
    main()

Run it:

python3 cannibal.py --csv gsc.csv --brand yourbrand

You get a console summary and cannibalisation.csv, sorted by how much is at stake: overlap in both directions, shared queries, average positions, a suggested move and the live state of each URL.

Picking the threshold (the part tutorials skip)

I started at 0.85. That was a guess, and it was too loose.

Embedding models that only ever see SEO queries think everything is a bit related. On the 145 queries from my main site, the median similarity between two random queries was 0.65, the 90th percentile was 0.81 and the 99th was 0.95. Two pairs I checked by hand:

The first pair is two different jobs: someone learning what it is, and someone ready to buy. At 0.85 they counted as twins. At 0.92 they don’t, and the second pair still does.

The first run at 0.85 gave me 11 pairs on my main site, which looked great until I read them. Most were pages with one or two queries, where a single shared query reads as 100% overlap. A bug that paired a page with itself made it look even busier. I raised the threshold to 0.92 and made the script ignore pages with fewer than three queries. Eleven pairs became one.

Your number will be different. It depends on the model and on how narrow your niche is. Print the percentiles of your own query similarities before you trust mine.

What it found

Cleaning drops brand queries, scraper operators like -site:reddit.com and rows under 5 impressions. My main site had several of those scraper-style queries.

Main site. The one flagged pair was two D2C case study URLs, sharing “d2c seo”, “d2c ecommerce seo” and “d2c seo services”, at average positions 42 and 47. It looked like a clean merge case. Then I checked the old URL by hand: it already 301s to the new one. Search Console keeps the history for the whole date range, so a plain export keeps flagging a fix you’ve already shipped.

That false alarm is why the script now requests each flagged URL without following redirects, reads the status and the canonical tag, and labels the pair “already handled, the data is history”.

Second site. Of the 12 flagged pairs, the strongest were a pricing page against its AI SEO pricing sub-page (overlap 0.89 and 0.98, nine shared queries) and two service pages with zero shared queries but overlap of 0.91 and 0.78. No exact-match tool would have shown that second pair.

An honest limit: both sites sit mostly beyond page five for these terms, so there’s very little traffic at stake. Read this as a test of the method. I haven’t merged or redirected anything on the strength of it yet, so I can’t tell you it recovers rankings.

What to do with a flagged pair

The script’s suggestion is only a starting point:

• Average positions five or more apart: one page is already winning. Keep it and retarget the other.
• Same intent with exact queries shared: merge the weaker page into the stronger, 301 it, point internal links at the winner.
• Same searcher but no shared words: sharpen the titles and headings so each page owns its own wording, or merge if they’re really one topic.

Before you redirect anything, search the top two or three flagged queries yourself and look at what Google ranks. If page one is a mix of guides and product pages, your two pages may be serving different searchers after all. The decision framework in my cannibalization guide covers merge, redirect, differentiate and canonical. If the conflict is a blog post against a service page, that has its own fix. To confirm a case by hand in Google’s own report, use Search Console’s Pages tab.

Limits worth knowing

• Embeddings measure how queries are worded. Google has no published cannibalization signal, so this is a proxy, and the live search results are the check.
• The export has no date dimension, so you can’t see the ranking URL flipping between two pages. That flip is the strongest evidence there is. Adding date to the dimensions and counting URL changes per query is the obvious next step.
• Pages with fewer than three queries are skipped on purpose. A new page can compete before it has the data to show it.
• The model is English-focused. For other languages, swap in a multilingual model from fastembed’s list.
• Brand filtering is manual. Pass your brand terms or they will pollute the profiles.

Prior art

Two good write-ups come at this from other angles. Search Engine Journal has a tutorial that embeds page titles with OpenAI and groups pages above a 0.9 similarity. JC Chouinard’s Python and Search Console tutorial flags queries with several ranking URLs. This script differs in three ways: it embeds the queries Google actually shows each page for, it uses a free local model, and it checks the live URLs so you don’t chase fixes you’ve already made.

Questions people ask

What is keyword cannibalization?

It's when two or more pages on your own site compete for the same query and the same searcher. Google splits the signals between them, and neither page ranks as well as one strong page would.

How do you detect keyword cannibalization with Python?

Pull query and page rows from the Search Console API or a CSV export, embed the queries, and build a profile for each page from the queries it ranks for. Flag pairs of pages whose queries overlap in meaning in both directions, then check the flagged URLs against the live site and the search results.

Which embedding model should I use?

I used BAAI/bge-small-en-v1.5 through fastembed. It's around 130 MB, runs on a CPU and needs no API key. Any sentence embedding model can work, but the similarity threshold changes with the model, so calibrate it on your own queries.

What cosine similarity counts as the same intent?

There's no universal number. On my queries 0.85 was too loose and 0.92 worked better with the bge-small model. Print the percentiles of your own query similarities and check a few pairs by hand before you pick one.

Why embed queries instead of page titles?

Queries show what Google actually matches each page to, including wording you never put on the page. Titles show what you meant. Titles are still a useful second check.

Is keyword cannibalization always a problem?

No. There's no penalty for it. It costs you when the ranking URL keeps changing and neither page climbs out of the lower positions. The cannibalization guide on this site covers how to tell the two cases apart.

Does this work on a large site?

Each page is compared with its 15 nearest profiles instead of every other page, so thousands of pages stay quick. A synthetic test of about 1,500 pages and 18,000 rows ran in 13 seconds on my laptop.