Cannibalization: A Query–URL Matrix and a Decision Rule

Search Console attributes clicks, impressions and position to the canonical URL of the link that earned them. That is stated in the metrics documentation, and the Search Analytics API says the same thing when it defines byPage aggregation as aggregation “by canonical URI”. So when two of your URLs show up as two rows for one query, the report is not being ambiguous. Google’s canonicalization has already run and has decided those are two pages.

That removes most of what gets filed under the label. Parameter variants, protocol duplicates, tracking URLs, mobile/desktop pairs: none of them can produce two rows, because they were consolidated before the reporting layer saw them. If you can see two rows, you have two pages competing. Whether that competition costs you anything is a separate question, and it is the one worth measuring.

What cannibalization is not

Start with co-ranking. Two of your URLs on the same results page is the outcome people are usually trying to prevent, and it is generally free. The performance report is explicit about the accounting: chart data is aggregated by property, and “if two results from the same site appear for one query, they count as a single impression”. Your property is charged one impression and offered two click targets. There is no ranking tax in that arrangement. The URL-level table splits it into two rows, which is why the URL export looks like a problem while the site export shows nothing happened.

Next, sitelinks. They are generated automatically from your link structure and clustered under one result. Position is computed per element, and all links inside an element normally carry the container’s position, so a sitelink child appears in the export as a second URL at the same position as its parent, with impressions that move in lockstep. That is one result rendering as several rows, not two pages fighting.

Then URL churn during a rollout. After a migration or a template change, the old URL and the new one both appear for weeks. Google documents that a medium-sized site can take “a few weeks or more” before the new URLs replace the old ones in results. Check the response codes of both URLs before you diagnose anything. If one of them is a 301, you are watching a migration finish, and the correct action is to wait.

And multi-page brand coverage: brand and navigational queries legitimately return your homepage, your pricing page and your docs. “Fixing” that by merging is how you lose a navigational surface you did not know you had.

What is left after those four is the case worth naming: two pages Google treats as interchangeable answers to one query, swaps between across days, and never shows together. That substitution is the thing that costs traffic, and it has a signature you can compute.

Building the query–URL matrix

The instrument is a table with one row per date, query and URL. Search Console will give you that, but only through the bulk export: the reports and the API both truncate the long tail, which is exactly where a weak URL’s unique surface lives. Google is direct about it: “Search Console stores and shows only the most important data rows… The most complete list of queries can be exported using bulk data exports.”

The bulk data export writes three tables into a BigQuery dataset named searchconsole by default: searchdata_site_impression, searchdata_url_impression and ExportLog. Both data tables carry data_date, site_url, query, is_anonymized_query, country, search_type, device, impressions and clicks. The site table adds sum_top_position; the URL table adds url, is_anonymized_discover, a set of is_[search_appearance_type] booleans, and sum_position. The schema is the whole contract, and two details in it determine whether your detector is correct.

First, sum_position is a sum, not a position, despite the field description reading like a scalar. The documented way to get average position is SUM(sum_position)/SUM(impressions) + 1, and the + 1 is there because the stored value is zero-based. Divide before you compare anything.

Second, rows are not deduplicated. Data is accumulated incrementally, so the same date, query and URL can appear several times, and every metric has to be aggregated. Google’s own query guidelines open with this, and skipping it produces a matrix that is wrong in a way no sanity check will catch.

CREATE OR REPLACE TABLE analysis.query_url_daily AS
SELECT
  data_date,
  query,
  url,
  SUM(impressions)  AS impressions,
  SUM(clicks)       AS clicks,
  SUM(sum_position) AS sum_position
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND is_anonymized_query = false
  AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY) AND CURRENT_DATE()
GROUP BY data_date, query, url;

Filter on is_anonymized_query rather than on query != ''. The reference page says in one line that the query field is a zero-length string when the flag is true and in the next that it is null; the boolean is unambiguous either way.

What the matrix does not contain

Anonymized queries are dropped by design, and the queries most likely to be anonymized are the rarest ones. That bias runs in one specific direction for this analysis: the long tail that only the weaker URL matches is systematically undercounted, which makes merging look safer than it is. Keep that in mind when you get to the pre-flight check below.

Average position is an average over impressions, and position is only recorded when there was an impression. A URL that surfaces on 5% of a query’s SERPs at position 3 reports a better average position than a URL that surfaces on 95% of them at position 4. Never rank the members of a pair by average position alone; weight by impressions or you will nominate the wrong survivor.

Minimum history

The export has no backfill. It starts producing data from the day you configure it, with the first write landing within 48 hours of setup; anything earlier has to come from the API or the reports, and those only reach back 16 months. Exported tables are retained forever by default, which is why a partition expiration is worth setting on day one.

The detector needs enough days for a swap to be distinguishable from a coin flip. A query with two impressions a day changes its top URL at random, and any alternation metric computed over it is measuring sampling noise. My starting floor is 90 days of history, a query with at least 300 impressions across that window, and impressions on at least 45 of the 90 days. Under that, roll the matrix up to ISO weeks with DATE_TRUNC(data_date, WEEK) and accept a coarser signal rather than a fictional one.

Before trusting any window, confirm the days are actually there. ExportLog is the only place that records what was written, and the two tables are exported separately, so one can be missing while the other is complete. epoch_version above zero means Google went back and revised that day.

SELECT data_date,
       COUNTIF(namespace = 'searchdata_url_impression')  AS url_rows,
       COUNTIF(namespace = 'searchdata_site_impression') AS site_rows,
       MAX(epoch_version) AS epoch
FROM searchconsole.ExportLog
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY) AND CURRENT_DATE()
GROUP BY data_date
HAVING url_rows = 0 OR site_rows = 0 OR epoch > 0
ORDER BY data_date;

Without BigQuery: the Search Analytics API

The API can build the same matrix, with two constraints that shape the work. rowLimit accepts 1 to 25,000 and defaults to 1,000, so you page with startRow until a request returns zero rows. And grouping by page and query together is the single most expensive thing you can ask for: the usage limits page states that “queries grouped/filtered by page AND query string are the most expensive”, with load quota measured in 10-minute and 1-day windows.

{
  "startDate": "2026-04-14",
  "endDate": "2026-07-13",
  "dimensions": ["date", "query", "page"],
  "type": "web",
  "dataState": "final",
  "rowLimit": 25000,
  "startRow": 0
}

Fetch one day per request rather than a range: load cost grows with the date range, and per-day requests fail independently, so a quota error costs you one day instead of a quarter. Note that aggregationType cannot be byProperty when you group by page, so the API cannot produce the co-ranking metric below. That one needs both export tables.

Detection metrics

Four numbers, computed per query over the same window.

Impression split: each URL’s share of the query’s URL-level impressions. A pair where one URL holds 97% is not a contested query; it is a strong page and a page that occasionally picks up scraps.

CREATE OR REPLACE TABLE analysis.pair_stats AS
WITH per_url AS (
  SELECT query, url,
         SUM(impressions) AS impressions,
         SUM(clicks)      AS clicks,
         SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1 AS avg_position,
         COUNT(DISTINCT data_date) AS days_seen,
         STDDEV_SAMP(SAFE_DIVIDE(sum_position, impressions) + 1) AS position_sd
  FROM analysis.query_url_daily
  WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY query, url
)
SELECT *,
       impressions / SUM(impressions) OVER (PARTITION BY query) AS url_share,
       COUNT(*)    OVER (PARTITION BY query) AS urls_on_query
FROM per_url;

Alternation rate. For each day, the URL Google placed highest; across days, the share of transitions where that URL changed. This is the metric that separates a stable hierarchy from a swap.

WITH winner AS (
  SELECT data_date, query,
         ARRAY_AGG(url ORDER BY SAFE_DIVIDE(sum_position, impressions) ASC,
                              impressions DESC LIMIT 1)[OFFSET(0)] AS top_url
  FROM analysis.query_url_daily
  WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY data_date, query
),
seq AS (
  SELECT query, top_url,
         LAG(top_url) OVER (PARTITION BY query ORDER BY data_date) AS prev_url
  FROM winner
)
SELECT query,
       COUNTIF(prev_url IS NOT NULL) AS transitions,
       COUNTIF(prev_url IS NOT NULL AND top_url != prev_url) AS flips,
       SAFE_DIVIDE(COUNTIF(prev_url IS NOT NULL AND top_url != prev_url),
                   COUNTIF(prev_url IS NOT NULL)) AS alternation_rate
FROM seq
GROUP BY query;

Co-ranking rate is the one that earns its keep, and it comes from the accounting rule quoted earlier. The site table charges one impression when two of your results appear for one query; the URL table charges one per URL. So the gap between them is co-occurrence, measured rather than assumed.

WITH u AS (
  SELECT data_date, query, SUM(impressions) AS url_impr
  FROM searchconsole.searchdata_url_impression
  WHERE search_type = 'WEB' AND is_anonymized_query = false
    AND data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY data_date, query
),
s AS (
  SELECT data_date, query, SUM(impressions) AS site_impr
  FROM searchconsole.searchdata_site_impression
  WHERE search_type = 'WEB' AND is_anonymized_query = false
    AND data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY data_date, query
)
SELECT query,
       SUM(site_impr) AS site_impressions,
       SAFE_DIVIDE(SUM(url_impr) - SUM(site_impr), SUM(site_impr)) AS co_rank_rate
FROM u JOIN s USING (data_date, query)
GROUP BY query;

A rate near zero means your URLs never appear together and are substituting for each other. A rate near one means both appear on nearly every SERP. That is the difference between cannibalization and free extra shelf space, and no third-party rank tracker can compute it.

Calibrate before you trust it. Pick twenty queries where you are certain only one URL can rank, a unique product identifier or a document title, and check the metric reads near zero for them. Small negative values are normal, because the two tables are exported separately and thresholds are applied to each independently. Treat anything inside roughly plus or minus 0.05 as zero.

Position variance: the standard deviation of the daily average position, per URL. High alternation with low variance means two pages parked at adjacent positions trading places inside a noise band. High alternation with high variance is a ranking system that has not settled.

Do not adopt my thresholds without looking at your own distribution first. Plot alternation_rate across every query with two or more URLs. On most sites it is heavily bimodal, and the point where the tail separates from the bulk is your threshold, not mine.

The decision rule

A query that clears all six is a candidate, not a verdict. The tiebreaker is what the two pages are for, and the way to answer that without arguing about it is to fingerprint the SERP for the shared queries and check whether one page type is being rewarded or two.

The last two rows are different operations and get confused constantly. A 301 tells the indexing pipeline that “the redirect target should be canonical” and moves signals to it. noindex removes a page and its signals from consideration. If the page has anything worth keeping, redirect; if it never should have existed, noindex. Doing both to one URL is self-cancelling.

“Differentiate” also has to mean something specific. Rewrite the title, H1 and opening section of the weaker page to the second fingerprint, re-point the internal anchors that describe it in the survivor’s vocabulary, and re-measure the same six numbers in six to eight weeks. If the alternation rate has not fallen, differentiation failed and you are back at the merge decision with better information.

Executing a merge without discarding signals

Order matters more than any individual step, because each step is only correct once the previous one has landed.

1. Freeze the snapshot. Details in the next section. Half of it becomes unrecoverable the moment the redirect goes live.

2. Move the content, and publish the survivor before anything else changes. The loser ranks for its unique queries because of specific text on it. Redirect before that text exists on the destination and you are pointing those queries at a page that does not answer them. Confirm the merged page has been recrawled and the new sections indexed before step four.

3. Rewire internal links, then rewrite the anchors. Every internal link pointing at the loser becomes a link to the survivor. Redirects are documented as a canonicalization signal, but internal links are one of the signals Google names for canonicalization and they are the half of the link graph you control outright, which is the argument for pulling the edge list off your own crawl rather than editing the links you happen to remember. Anchors need a second pass: an anchor that described the loser’s narrow subtopic now points at a broader page, and leaving it verbatim tells the graph something that is no longer true. Point it at the merged section instead, fragment and all.

4. Apply the 301. Server-side, single hop, loser to survivor. Google’s redirect table is ordered by how reliably each method is interpreted, with server-side first and JavaScript last. If you cannot issue a 301, say because the loser is a live page people still need, use rel="canonical", understanding that it is a strong signal and not a directive, while the redirect is stronger.

5. Update the sitemaps. Remove the loser, bump the survivor’s lastmod. If the retired set is large enough that recrawl latency matters, keep a temporary sitemap containing only the retired URLs so they get refetched and the redirect gets seen; that is the same mechanic the site-move guidance uses, where the old sitemap’s indexed count is expected to drain toward zero. Delete that sitemap once it has. A sitemap full of redirecting URLs is not a permanent fixture.

6. Leave the redirect alone for at least a year. Google’s guidance is explicit: “Keep the redirects for as long as possible, generally at least 1 year,” because that is the window in which signals transfer. Removing a redirect after a quarter because the old URL “has no traffic” throws away the thing you did the merge for.

The snapshot to take before touching anything

The question you will be asked in three months is whether it worked, and you cannot answer it from the survivor’s numbers alone; the survivor’s clicks going up is arithmetic, not evidence. What you need is the state of the world at the moment before the change.

CREATE TABLE analysis.merge_snapshot_20260831 AS
SELECT CURRENT_DATE() AS snapshot_date, d.*
FROM analysis.query_url_daily d
WHERE d.data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)
  AND d.query IN (
    SELECT DISTINCT query
    FROM analysis.query_url_daily
    WHERE url IN ('https://example.com/a', 'https://example.com/b')
      AND data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)
  );

That freezes the union of every query either URL served, at daily grain, for both URLs and for every other URL of yours that also ranked on those queries. Alongside it, record:

• Response headers and the full redirect chain for both URLs, as they are today.
• Every internal link pointing at either URL, with its anchor text and source page.
• External links to the loser, and their anchor text, from whatever source you have.
• The SERP fingerprint for the top shared queries, so you can tell later whether the SERP moved or you did.
• The lastmod and sitemap membership of both URLs.
• A control cohort: 200 to 500 queries the site ranks for that neither URL serves, matched roughly on impression volume. Without it, a core update inside your observation window makes the result unreadable.

for u in https://example.com/a https://example.com/b; do
  curl -sSI -o /dev/null -w "$u -> %{http_code} %{redirect_url}\n" "$u"
  curl -sSL -o /dev/null -w "  lands on %{url_effective} in %{num_redirects} hop(s)\n" "$u"
done

Reading the aftermath

Nothing can change before the loser is refetched, so the first observable is a crawl event, not a ranking one. That is a log question, the request line for the retired URL and the status it received, and until it appears, any movement you see is something else.

Then measure at the level of the query cohort, never the URL. The metric is the sum of clicks and impressions across the frozen union of queries, compared against the control cohort over the same days. The survivor absorbing the loser’s clicks is a mechanical outcome and proves nothing.

The shape to look for: the loser’s impressions decay toward zero over days to weeks while the survivor’s impressions on the shared queries rise, and site-level impressions for the cohort end at or above where they started. If the cohort total falls with the loser, the survivor did not inherit the queries, and you now have a concrete list of which ones.

On timing, you are waiting on a system whose documented pace for medium-sized sites is “a few weeks or more”. That is the observation. My reading of it, and it is a reading: four weeks is the earliest point at which the absence of recovery means anything, and I would not write a verdict before eight to twelve weeks. While you wait, resist attributing every wobble to the merge. A decline that started before you touched anything is a decay problem with its own decomposition, and the two are easy to conflate when you are looking for an effect.

Finally, check what became of the retired URLs. They should move to a redirect state in the page indexing report rather than sitting in a queue, and the difference between those two states is diagnostic, not cosmetic.

Merges that cost traffic

Consolidation is published almost exclusively as an upside story, which is strange, because the operation is irreversible in practice and has at least four failure modes that are visible in advance.

Two pages doing two jobs. The pair clears every numeric threshold and still represents two intents: a comparison page and an implementation guide, a category and a buying guide. The merged page can only match one fingerprint. It wins those queries and loses the others, and because the losing set is usually the lower-volume one, the aggregate looks flat for a month before the deficit becomes obvious.

A long-tail surface that only existed on the weaker URL. This is the expensive one. The weak page carried a section (a troubleshooting list, a spec table, an FAQ) that matched hundreds of low-volume queries the strong page never touched. Redirect it, drop that section during the rewrite, and the surface is gone. The redirect moves signals, not text.

Different SERP fingerprints under one query set. The two pages were being shown for different result formats. Merging into one page type surrenders whichever format the survivor does not fit, and the loss appears as a format disappearing rather than as a position drop, which is why position monitoring misses it entirely.

Internal links that meant something. The loser was a node in the graph. Other pages linked to it with anchors specific to its subtopic; it linked out to a cluster that now has one less inbound path. After the merge those anchors describe a page that covers the topic in a subsection, if at all. The redirect keeps the links alive as hops and does nothing about what they now say.

The pre-flight check that catches the second and, indirectly, the first is a disjoint-query count. Split the pair’s 90-day query set into three buckets and look at where the loser’s clicks actually live.

WITH a AS (
  SELECT query, SUM(clicks) AS c, SUM(impressions) AS i
  FROM analysis.query_url_daily
  WHERE url = 'https://example.com/survivor'
    AND data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY query
),
b AS (
  SELECT query, SUM(clicks) AS c, SUM(impressions) AS i
  FROM analysis.query_url_daily
  WHERE url = 'https://example.com/loser'
    AND data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY query
)
SELECT
  CASE WHEN a.query IS NULL THEN 'loser_only'
       WHEN b.query IS NULL THEN 'survivor_only'
       ELSE 'shared' END AS bucket,
  COUNT(*) AS queries,
  SUM(COALESCE(a.c, 0) + COALESCE(b.c, 0)) AS clicks,
  SUM(COALESCE(a.i, 0) + COALESCE(b.i, 0)) AS impressions
FROM a FULL OUTER JOIN b ON a.query = b.query
GROUP BY bucket;

The loser_only bucket is the bet. Every click in it is a click the survivor has never earned on a query it has never had an impression for, and a 301 is a wager that the merged page will start earning them. My gate is 20%: if loser_only holds more than a fifth of the loser’s clicks, the merge does not proceed until the text responsible for those queries is identified and carried over verbatim, and even then I would rather differentiate. Remember that anonymized queries are missing from this count and that they are the rarest ones, so the real loser_only share is larger than what the query returns. The bias always runs toward making the merge look safer.

The other two failure modes are caught by the snapshot rather than by a query: the fingerprint record tells you whether one page type or two were being rewarded, and the inlink export with anchors tells you what the graph will say about the survivor after you rewire it. Neither is expensive, and both are unrecoverable once the redirect is live.

A merge is a deletion with a forwarding address. The numbers above decide whether the deletion is warranted; the snapshot decides whether you will ever be able to tell.