How to Build a Query Cannibalization Report From Google Search Console

Search Console has no cannibalization report. Its newer Query groups card, added to Search Console Insights in October 2025, clusters similar queries into topics, but it does not tell you when two of your pages compete for the same search. You have to build that view yourself from the Performance data.

Most cannibalization reports stop at “queries where more than one URL got impressions”. That list is long and mostly harmless. Big sites routinely have two or three pages showing for the same query, and Google often shows both on purpose. This guide builds a sharper report that adds one metric most tools skip: the flip rate, or how often the top-ranking page for a query changes from one day to the next. Pages that keep swapping places are the ones actually hurting each other.

What Counts as Cannibalization in Search Console Data

Two pages getting impressions for the same query is a symptom, not a diagnosis. Two questions separate real problems from noise: do the pages swap the top spot over time, and do they answer the same need?

Table 1: Signals in the Report and What They Tell You

Our rule of thumb: a query with a flip rate above 0.2 (the leading page changes on more than one day in five) and a top click share below 70% is worth investigating. A query where one page takes 90% of clicks and never flips is a double listing, not cannibalization, even if five URLs appear.

Method 1: Check a Single Query in the Search Console UI

For spot checks on a handful of money keywords, the interface is enough.

• Open Performance > Search results and set the date range to the last 3 months.
• Click + Add filter > Query, choose Exact query and enter the keyword.
• Open the Pages tab. Every URL listed got impressions for that query.
• To see swapping, click each page, switch the date filter to Compare, or add the Page filter and turn on the Position line in the chart for each URL in turn.

The UI hides the day-by-day swap pattern unless you check pages one at a time, and it cannot scan thousands of queries. For a full report, use one of the next two methods.

Method 2: Build the Report With the Search Console API

This Python script pulls 90 days of data by date, query and page, then calculates flip rate, top click share and position gap for every query with two or more URLs. It needs a Google Cloud service account added as a user on the property.

from datetime import date, timedelta
import pandas as pd
from google.oauth2 import service_account
from googleapiclient.discovery import build

SITE = "sc-domain:example.com"
KEY_FILE = "service-account.json"
END = date.today() - timedelta(days=3)
START = END - timedelta(days=90)

creds = service_account.Credentials.from_service_account_file(
    KEY_FILE, scopes=["https://www.googleapis.com/auth/webmasters.readonly"])
gsc = build("searchconsole", "v1", credentials=creds)

rows, start_row = [], 0
while True:
    resp = gsc.searchanalytics().query(siteUrl=SITE, body={
        "startDate": str(START), "endDate": str(END),
        "dimensions": ["date", "query", "page"], "type": "web",
        "rowLimit": 25000, "startRow": start_row}).execute()
    batch = resp.get("rows", [])
    rows += [dict(zip(["date", "query", "page"], r["keys"]),
                  clicks=r["clicks"], impr=r["impressions"], pos=r["position"])
             for r in batch]
    if len(batch) < 25000:
        break
    start_row += 25000

df = pd.DataFrame(rows)
df["page"] = df["page"].str.split("#").str[0]

multi = df.groupby("query")["page"].nunique()
df = df[df["query"].isin(multi[multi >= 2].index)]

daily_top = (df.sort_values(["query", "date", "pos", "impr"],
                            ascending=[True, True, True, False])
               .groupby(["query", "date"]).first().reset_index())
daily_top["flip"] = daily_top.groupby("query")["page"].transform(
    lambda s: (s != s.shift()).astype(int)) 
flips = daily_top.groupby("query").agg(days=("date", "count"), flips=("flip", "sum"))
flips["flips"] -= 1
flips["flip_rate"] = (flips["flips"] / flips["days"]).round(2)

df["pos_x_impr"] = df["pos"] * df["impr"]
pages = df.groupby(["query", "page"]).agg(
    clicks=("clicks", "sum"), impr=("impr", "sum"), pxi=("pos_x_impr", "sum"))
pages["avg_pos"] = (pages["pxi"] / pages["impr"]).round(1)
pages = pages.reset_index().sort_values(["query", "clicks", "impr"],
                                        ascending=[True, False, False])

def summarize(g):
    total = g["clicks"].sum()
    top2 = g.nsmallest(2, "avg_pos")["avg_pos"].tolist()
    return pd.Series({
        "urls": len(g), "impressions": g["impr"].sum(), "clicks": total,
        "top_click_share": round(g["clicks"].max() / total, 2) if total else 0,
        "position_gap": round(top2[1] - top2[0], 1) if len(top2) == 2 else None,
        "pages": " | ".join(f"{r.page} ({r.clicks} clicks, pos {r.avg_pos})"
                            for r in g.itertuples())})

report = pages.groupby("query").apply(summarize).join(flips)
report = report[report["impressions"] >= 100]
report["priority"] = (report["impressions"] * report["flip_rate"]).round()
report.sort_values("priority", ascending=False).to_csv("cannibalization_report.csv")
print(report.sort_values("priority", ascending=False).head(20))

The priority column multiplies impressions by flip rate, so busy queries with unstable rankings rise to the top. One limit to know: the API caps how many rows it returns per day, so very large sites lose some long-tail queries. If that is you, use BigQuery.

Method 3: Build It in BigQuery With the Bulk Data Export

Search Console’s bulk data export sends daily data to BigQuery with no row limits. Data only starts from the day you turn it on, so set it up today even if you plan to run the report later. The query below uses the searchdata_url_impression table, where average position is sum_position / impressions + 1 because positions are stored zero-based.

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);

WITH base AS (
  SELECT data_date, query, url,
         SUM(impressions) AS impr,
         SUM(clicks) AS clicks,
         SUM(sum_position) / SUM(impressions) + 1 AS pos
  FROM `your-project.searchconsole.searchdata_url_impression`
  WHERE data_date >= start_date
    AND search_type = 'WEB'
    AND NOT is_anonymized_query
  GROUP BY data_date, query, url
),
shared AS (
  SELECT query, COUNT(DISTINCT url) AS urls,
         SUM(impr) AS impr, SUM(clicks) AS clicks
  FROM base
  GROUP BY query
  HAVING urls >= 2 AND impr >= 100
),
daily_top AS (
  SELECT query, data_date, url
  FROM base
  WHERE query IN (SELECT query FROM shared)
  QUALIFY ROW_NUMBER() OVER (PARTITION BY query, data_date
                             ORDER BY pos, impr DESC) = 1
),
flip_stats AS (
  SELECT query,
         COUNT(*) AS days,
         COUNTIF(prev_url IS NOT NULL AND url != prev_url) AS flips
  FROM (SELECT query, data_date, url,
               LAG(url) OVER (PARTITION BY query ORDER BY data_date) AS prev_url
        FROM daily_top)
  GROUP BY query
),
by_url AS (
  SELECT query, url, SUM(clicks) AS c, SUM(impr) AS i,
         SUM(pos * impr) / SUM(impr) AS avg_pos
  FROM base
  WHERE query IN (SELECT query FROM shared)
  GROUP BY query, url
)
SELECT s.query, s.urls, s.impr, s.clicks,
       f.flips, f.days,
       ROUND(f.flips / f.days, 2) AS flip_rate,
       ROUND(SAFE_DIVIDE(MAX(u.c), s.clicks), 2) AS top_click_share,
       STRING_AGG(FORMAT('%s (%d clicks, pos %.1f)', u.url, u.c, u.avg_pos),
                  ' | ' ORDER BY u.c DESC) AS pages
FROM shared s
JOIN flip_stats f USING (query)
JOIN by_url u USING (query)
GROUP BY s.query, s.urls, s.impr, s.clicks, f.flips, f.days
ORDER BY s.impr * f.flips / f.days DESC
LIMIT 500;

Connect the result to Looker Studio or Sheets and you have a cannibalization report that refreshes itself. Anonymized queries are excluded because they have no query text, which is the same gap you see in the Performance report.

How to Read the Finished Report

Here is an illustrative example of three rows from a report for a marketing blog. The numbers are made up to show how the columns work together.

Table 2: Illustrative Report Rows

The first row is the one to fix. Both pages target the same intent, neither holds the top spot, and clicks are split almost evenly, so Google keeps testing both and neither builds strength. The second row looks scary in a basic report because three URLs appear, but one page takes 91% of clicks and rarely loses its position. The third row needs a different fix: the pages serve different needs, so the job is making that difference obvious rather than merging them.

How to Fix Each Type of Cannibalization

Table 3: Fixes by Verdict

A few practical points make the fixes stick:

• Pick the winner by data, not preference. Keep the page with more clicks, more backlinks and the better match to the query, even if the other page is newer.
• Move the useful content before redirecting. Sections, data and examples from the losing page should end up on the winner.
• Update internal links. Links to the redirected page should point straight to the winner. An AI internal linking tool speeds this up on large sites.
• Rerun the report 4 to 6 weeks later. A falling flip rate and a rising top click share confirm the fix worked.

Cannibalization often grows out of old content that was never pruned. Our guide to content pruning versus content decay covers when to merge, refresh or remove pages, and a strong topical authority plan keeps new content from overlapping in the first place.

Do Not Confuse Cannibalization With Canonical Problems

If the URLs competing for a query are near-duplicates of the same page, such as parameter, trailing-slash or http versions, the issue is canonicalization rather than content. Check the URL Inspection tool to see which version Google picked as canonical, and fix that at the template level as part of your technical SEO work.

FAQs

Does Google Search Console Have a Keyword Cannibalization Report?

No. Search Console has no built-in cannibalization report. You can check one query at a time in the Performance report, or build a full report with the Search Console API or the BigQuery bulk data export.

Is It Bad When Two Pages Rank for the Same Keyword?

Not always. If one page clearly leads and holds its position, or the pages serve different intents, there is usually nothing to fix. It becomes a problem when the pages keep swapping places and split clicks between them.

What Is a Good Flip Rate Threshold?

As a rule of thumb, a flip rate above 0.2, meaning the top page changes on more than one day in five, combined with the best page taking under 70% of clicks, is worth a closer look.

How Much Data Do I Need for a Cannibalization Report?

Use at least 90 days. Shorter windows exaggerate flip rates for low-volume queries, and a minimum of about 100 impressions per query filters out noise.

Should I Use a Canonical Tag or a 301 Redirect?

Use a 301 redirect when the weaker page no longer needs to exist. Use a canonical tag when both pages must remain accessible to users, such as sorted or filtered versions of the same listing.