DEV Community

Prox Digital Agency London
Prox Digital Agency London

Posted on

Find keyword cannibalisation with the Search Console API and Python

The Search Console API will give you up to 25,000 rows per request, but Google's documentation is explicit that it "does not guarantee to return all data rows but rather top ones" (Search Console API reference). That caveat shapes everything in this post, so keep it in mind.

The goal: a small Python script that pulls query and page data for a site and flags queries where more than one URL is earning meaningful impressions. That's the raw signal behind keyword cannibalisation. Checking it by hand in the Search Console UI works for ten queries. This works for ten thousand.

What we're actually detecting

For our purposes, a cannibalisation candidate is a query where two or more of your URLs each earn a meaningful share of impressions over the same period.

It's a candidate, not a verdict. A guide and a product page can legitimately appear for the same broad query. The script narrows thousands of rows down to a short list a human can judge.

Setup

  1. In Google Cloud, create a project and enable the Google Search Console API.
  2. Create a service account and download its JSON key.
  3. In Search Console, add the service account's email address as a user on the property. Read access is enough.
  4. Install the libraries:
pip install google-api-python-client google-auth pandas
Enter fullscreen mode Exit fullscreen mode

Domain properties are addressed as sc-domain:example.com; URL-prefix properties use the full URL, such as https://www.example.com/.

Pull query and page rows

The API accepts dimensions including query and page. rowLimit tops out at 25,000, and you paginate with startRow.

from google.oauth2 import service_account
from googleapiclient.discovery import build

SCOPES = ["https://www.googleapis.com/auth/webmasters.readonly"]
SITE = "sc-domain:example.com"

creds = service_account.Credentials.from_service_account_file(
    "service-account.json", scopes=SCOPES
)
service = build("searchconsole", "v1", credentials=creds)

def fetch_rows(start_date, end_date, page_size=25000):
    rows, start_row = [], 0
    while True:
        body = {
            "startDate": start_date,
            "endDate": end_date,
            "dimensions": ["query", "page"],
            "type": "web",
            "rowLimit": page_size,
            "startRow": start_row,
        }
        resp = service.searchanalytics().query(siteUrl=SITE, body=body).execute()
        batch = resp.get("rows", [])
        rows.extend(batch)
        if len(batch) < page_size:
            return rows
        start_row += page_size

rows = fetch_rows("2026-08-01", "2026-10-31")
Enter fullscreen mode Exit fullscreen mode

Three months is a sensible window: long enough to smooth out daily noise, short enough to reflect the current state of the site.

Flag candidate queries

import pandas as pd

df = pd.DataFrame(
    {
        "query": r["keys"][0],
        "page": r["keys"][1],
        "clicks": r["clicks"],
        "impressions": r["impressions"],
        "position": r["position"],
    }
    for r in rows
)

BRAND = r"(acme|acme ltd)"   # your brand terms
MIN_IMPRESSIONS = 50         # per query/page pair
MIN_SHARE = 0.2              # each page needs at least 20% of the query's impressions

df = df[~df["query"].str.contains(BRAND, case=False, regex=True)]
df = df[df["impressions"] >= MIN_IMPRESSIONS]
df["share"] = df["impressions"] / df.groupby("query")["impressions"].transform("sum")

contenders = df[df["share"] >= MIN_SHARE]
counts = contenders.groupby("query")["page"].nunique()
suspects = counts[counts >= 2].index

report = (
    contenders[contenders["query"].isin(suspects)]
    .sort_values(["query", "impressions"], ascending=[True, False])
    [["query", "page", "impressions", "clicks", "position", "share"]]
)
report.to_csv("cannibalisation_candidates.csv", index=False)
print(f"{len(suspects)} queries with two or more competing pages")
Enter fullscreen mode Exit fullscreen mode

The share threshold is what stops a page with three stray impressions from triggering a false alarm. Tune both thresholds to your site's size.

Cut the false positives

Before anyone acts on the CSV, filter out the usual suspects:

  • Brand queries. Your homepage and About page will often share them, and that's fine. The regex above removes them.
  • The homepage. Consider excluding it entirely for a first pass; it appears for many broad queries by design.
  • Different intents. A query like "invoice template" can reasonably surface both a template page and a how-to guide. If the two URLs meet different needs, they aren't competing.
  • Parameter and duplicate URLs. If the "two pages" are the same content under different URLs (tracking parameters, trailing slashes), that's a canonicalisation issue, not a content one.

What's left is a list worth a human review, usually much shorter than people expect.

Reading the output

Each suspect query in the CSV comes with two or more rows, one per competing URL. A quick triage for each group:

What you see Likely meaning Next step
Two pages, similar share, similar average position Search engines can't decide between them Decide which URL owns the need; merge or differentiate the other
One page with most impressions, a second with a steady minority share A secondary page creeping into the primary's territory Tighten the secondary page's focus and add a link to the primary
Two pages that are the same content at different URLs Duplicate URLs, not competing content Fix canonicalisation (redirect or canonical)
Two pages meeting clearly different needs Healthy overlap Leave alone; note it in the map so nobody "fixes" it later

It also helps to add a date dimension for the top suspects. Re-run fetch_rows with "dimensions": ["date", "page"] and a dimensionFilterGroups filter on the query, and you can plot whether the two URLs alternate over time. Alternation is the strongest evidence that they're genuinely competing.

Running it on a schedule

The script is most useful as a monthly routine. Two practical points:

  • Keep each run's CSV. Name files by month (candidates-2026-10.csv) so you can diff them. New suspects after a content push tell you the brief process let an overlap through.
  • Keep the key out of your repository. Load the service account path from an environment variable or your scheduler's secret store rather than committing the JSON file.

Caveats worth stating in any report

  • Top rows only. As quoted above, the API returns top rows, not every row, so treat results as a strong sample.
  • Anonymised queries. Search Console omits anonymised queries when you filter or group by query, so rare long-tail overlaps won't appear.
  • Fresh data. By default the API returns finalised data. Setting "dataState": "all" includes fresh data, which may still change.
  • Impressions aren't rankings. Two URLs sharing impressions tells you both were shown, not that either ranked well.

Extending it: check against your keyword map

If you keep a keyword map as a CSV (one row per query family, with the URL that should own it), you can flag a more specific problem: queries where the page getting the most impressions isn't the page you assigned.

mapping = pd.read_csv("keyword_map.csv")  # columns: query, owner_url
top = df.sort_values("impressions", ascending=False).drop_duplicates("query")
check = top.merge(mapping, on="query", how="inner")
mismatches = check[check["page"] != check["owner_url"]]
mismatches[["query", "owner_url", "page", "impressions"]].to_csv(
    "owner_mismatches.csv", index=False
)
Enter fullscreen mode Exit fullscreen mode

Mismatches are often more actionable than general overlap. They tell you exactly which page search engines prefer over the one you planned, and that's usually a sign the owner page needs clearer focus or more internal links.

Where this fits

Detection is only half the job. Once you've confirmed a real overlap, someone has to decide which URL owns the need, and record that decision so it doesn't recur. If you're building that record from scratch, this walkthrough on mapping keywords to pages covers the process the script is checking against.

A useful next step is to run the script monthly and diff the outputs. New suspects appearing after a content push is a good prompt to check the brief process, not just the pages.


This article was drafted with AI assistance and reviewed by the Prox Digital Agency team. Test the code in your own environment before relying on it.

Top comments (0)