keyword cannibalism - JSON & SQL code to resolve it FAST

After implementing structured schema markup, improving page speed, and optimizing metadata for the PetsRPals candle listings, we expected to see improved organic traffic and conversions. However, performance data revealed an unexpected issue.

example: high intent searches not yielding positive results or conversions

Two of the PetsRPal’s top-selling items at physical locatons — Jasmine & Lavender Catnip Candle and the Jasmine & Lavender Catnip Oil — were significantly underperforming online. This was surprising because search data shows that these products also receive the highest number of high-intent branded searches, including queries such as:

“PetsRPals catnip candle”
“pet safe petsrpals catnip oil”

high bounce rate, low engagement & low session time in google analytics / google search console combined with high intent and what it could mean

Search Console data confirms that users are in fact clicking on PetsRPals search results. However, behavioral analytics indicate that many visitors leave the site shortly after landing. Over a 30-day analysis period, the candle product pages showed unusually weak engagement metrics.

Metric	Value	Ecomm Benchmark
Bounce Rate	78%	40–55%
Average Engagement Time	22 seconds	1–2 minutes
Average Session Duration	31 seconds	1.5–3 minutes
Conversion Rate	0.4%	2–3%

These metrics suggest that users are not finding the content or product page they expected when arriving from search results. A high bounce rate combined with extremely short session durations often indicates that the landing page does not match the user’s search intent.

Further investigation revealed that many users searching for specific candle products were frequently landing on the Cucumber & Juicy Pear Catnip Candle page instead of the intended product pages. This misalignment between search intent and landing page relevance may indicate a keyword cannibalization or canonicalization issue.

Solution

Create a sample SQL script to scan search performance data and detect potential keyword cannibalization across PetsRPals product pages.
Implement canonical tags for the candle products in the previously created JSON file to help search engines identify the correct primary page for each product.

SQL Code Block:

WITH normalized AS (
    SELECT
        LOWER(TRIM(query)) AS query,
        LOWER(TRIM(landing_page)) AS landing_page,
        SUM(clicks) AS total_clicks,
        SUM(impressions) AS total_impressions,
        ROUND(SUM(clicks) * 1.0 / NULLIF(SUM(impressions), 0), 4) AS calc_ctr,
        ROUND(AVG(avg_position), 2) AS avg_position
    FROM search_console_pages
    WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
      AND landing_page LIKE 'https://www.petsrpals.com/%'
      AND query IS NOT NULL
      AND TRIM(query) <> ''
      AND (
            query ILIKE '%petsrpals%'
         OR query ILIKE '%catnip candle%'
         OR query ILIKE '%catnip oil%'
         OR query ILIKE '%jasmine%'
         OR query ILIKE '%lavender%'
         OR query ILIKE '%chamomile%'
         OR query ILIKE '%peach%'
         OR query ILIKE '%cucumber%'
         OR query ILIKE '%pear%'
         OR query ILIKE '%relax%'
         OR query ILIKE '%smile%'
         OR query ILIKE '%focus%'
      )
    GROUP BY
        LOWER(TRIM(query)),
        LOWER(TRIM(landing_page))
),

competing_queries AS (
    SELECT
        query,
        COUNT(DISTINCT landing_page) AS ranking_url_count,
        SUM(total_clicks) AS query_clicks,
        SUM(total_impressions) AS query_impressions
    FROM normalized
    GROUP BY query
    HAVING COUNT(DISTINCT landing_page) > 1
),

ranked_pages AS (
    SELECT
        n.query,
        n.landing_page,
        n.total_clicks,
        n.total_impressions,
        n.calc_ctr,
        n.avg_position,
        ROW_NUMBER() OVER (
            PARTITION BY n.query
            ORDER BY
                n.total_clicks DESC,
                n.total_impressions DESC,
                n.avg_position ASC,
                n.landing_page
        ) AS page_rank
    FROM normalized n
    INNER JOIN competing_queries cq
        ON n.query = cq.query
),

cannibalization_report AS (
    SELECT
        rp.query,
        COUNT(*) AS total_urls_ranking,

        MAX(CASE WHEN rp.page_rank = 1 THEN rp.landing_page END) AS primary_url,
        MAX(CASE WHEN rp.page_rank = 1 THEN rp.total_clicks END) AS primary_clicks,
        MAX(CASE WHEN rp.page_rank = 1 THEN rp.total_impressions END) AS primary_impressions,
        MAX(CASE WHEN rp.page_rank = 1 THEN rp.calc_ctr END) AS primary_ctr,
        MAX(CASE WHEN rp.page_rank = 1 THEN rp.avg_position END) AS primary_position,

        MAX(CASE WHEN rp.page_rank = 2 THEN rp.landing_page END) AS competing_url_1,
        MAX(CASE WHEN rp.page_rank = 2 THEN rp.total_clicks END) AS competing_clicks_1,
        MAX(CASE WHEN rp.page_rank = 2 THEN rp.total_impressions END) AS competing_impressions_1,
        MAX(CASE WHEN rp.page_rank = 2 THEN rp.calc_ctr END) AS competing_ctr_1,
        MAX(CASE WHEN rp.page_rank = 2 THEN rp.avg_position END) AS competing_position_1,

        MAX(CASE WHEN rp.page_rank = 3 THEN rp.landing_page END) AS competing_url_2,
        MAX(CASE WHEN rp.page_rank = 3 THEN rp.total_clicks END) AS competing_clicks_2,
        MAX(CASE WHEN rp.page_rank = 3 THEN rp.total_impressions END) AS competing_impressions_2,
        MAX(CASE WHEN rp.page_rank = 3 THEN rp.calc_ctr END) AS competing_ctr_2,
        MAX(CASE WHEN rp.page_rank = 3 THEN rp.avg_position END) AS competing_position_2
    FROM ranked_pages rp
    GROUP BY rp.query
)

SELECT
    query,
    total_urls_ranking,
    primary_url,
    primary_clicks,
    primary_impressions,
    primary_ctr,
    primary_position,
    competing_url_1,
    competing_clicks_1,
    competing_impressions_1,
    competing_ctr_1,
    competing_position_1,
    competing_url_2,
    competing_clicks_2,
    competing_impressions_2,
    competing_ctr_2,
    competing_position_2,
    CASE
        WHEN total_urls_ranking >= 3 THEN 'High cannibalization risk'
        WHEN total_urls_ranking = 2 THEN 'Moderate cannibalization risk'
        ELSE 'Review manually'
    END AS cannibalization_flag
FROM cannibalization_report
ORDER BY total_urls_ranking DESC, primary_clicks DESC, query;

Schema Markup & Canonical Tagging Code Block

{
  "@context": "https://schema.org",
  "@graph": [
    {
      "@type": "Product",
      "name": "PetsRPals \"Relax\" Jasmine & Soft Lavender Catnip Candle",
      "sku": "PRP-CAN-RELAX-001",
      "category": "Pet-Safe Candles",
      "url": "https://www.petsrpals.com/products/relax-jasmine-soft-lavender-catnip-candle",
      "image": [
        "https://www.petsrpals.com/images/relax-jasmine-soft-lavender-catnip-candle-1.jpg"
      ],
      "brand": {
        "@type": "Brand",
        "name": "PetsRPals"
      },
      "description": "Pet-safe jasmine and soft lavender catnip candle.",
      "canonical": "https://www.petsrpals.com/products/relax-jasmine-soft-lavender-catnip-candle"
    },
    {
      "@type": "Product",
      "name": "PetsRPals \"Smile\" Chamomile & Sweet Peach Catnip Candle",
      "sku": "PRP-CAN-SMILE-001",
      "category": "Pet-Safe Candles",
      "url": "https://www.petsrpals.com/products/smile-chamomile-sweet-peach-catnip-candle",
      "image": [
        "https://www.petsrpals.com/images/smile-chamomile-sweet-peach-catnip-candle-1.jpg"
      ],
      "brand": {
        "@type": "Brand",
        "name": "PetsRPals"
      },
      "description": "Pet-safe chamomile and sweet peach catnip candle.",
      "canonical": "https://www.petsrpals.com/products/smile-chamomile-sweet-peach-catnip-candle"
    },
    {
      "@type": "Product",
      "name": "PetsRPals \"Focus\" Cucumber & Juicy Pear Catnip Candle",
      "sku": "PRP-CAN-FOCUS-001",
      "category": "Pet-Safe Candles",
      "url": "https://www.petsrpals.com/products/focus-cucumber-juicy-pear-catnip-candle",
      "image": [
        "https://www.petsrpals.com/images/focus-cucumber-juicy-pear-catnip-candle-1.jpg"
      ],
      "brand": {
        "@type": "Brand",
        "name": "PetsRPals"
      },
      "description": "Pet-safe cucumber and juicy pear catnip candle.",
      "canonical": "https://www.petsrpals.com/products/focus-cucumber-juicy-pear-catnip-candle"
    },
    {
      "@type": "Product",
      "name": "PetsRPals \"Relax\" Jasmine & Soft Lavender Catnip Oil",
      "sku": "PRP-OIL-RELAX-001",
      "category": "Pet-Safe Oils",
      "url": "https://www.petsrpals.com/products/relax-jasmine-soft-lavender-catnip-oil",
      "image": [
        "https://www.petsrpals.com/images/relax-jasmine-soft-lavender-catnip-oil-1.jpg"
      ],
      "brand": {
        "@type": "Brand",
        "name": "PetsRPals"
      },
      "description": "Pet-safe jasmine and soft lavender catnip oil.",
      "canonical": "https://www.petsrpals.com/products/relax-jasmine-soft-lavender-catnip-oil"
    },
    {
      "@type": "Product",
      "name": "PetsRPals \"Smile\" Chamomile & Sweet Peach Catnip Oil",
      "sku": "PRP-OIL-SMILE-001",
      "category": "Pet-Safe Oils",
      "url": "https://www.petsrpals.com/products/smile-chamomile-sweet-peach-catnip-oil",
      "image": [
        "https://www.petsrpals.com/images/smile-chamomile-sweet-peach-catnip-oil-1.jpg"
      ],
      "brand": {
        "@type": "Brand",
        "name": "PetsRPals"
      },
      "description": "Pet-safe chamomile and sweet peach catnip oil.",
      "canonical": "https://www.petsrpals.com/products/smile-chamomile-sweet-peach-catnip-oil"
    },
    {
      "@type": "Product",
      "name": "PetsRPals \"Focus\" Cucumber & Juicy Pear Catnip Oil",
      "sku": "PRP-OIL-FOCUS-001",
      "category": "Pet-Safe Oils",
      "url": "https://www.petsrpals.com/products/focus-cucumber-juicy-pear-catnip-oil",
      "image": [
        "https://www.petsrpals.com/images/focus-cucumber-juicy-pear-catnip-oil-1.jpg"
      ],
      "brand": {
        "@type": "Brand",
        "name": "PetsRPals"
      },
      "description": "Pet-safe cucumber and juicy pear catnip oil.",
      "canonical": "https://www.petsrpals.com/products/focus-cucumber-juicy-pear-catnip-oil"
    }
  ]
}

effective SQL code to quickly detect & resolve keyword cannibalism on your site

example: high intent searches not yielding positive results or conversions

high bounce rate, low engagement & low session time in google analytics / google search console combined with high intent and what it could mean

Solution

SQL Code Block:

Schema Markup & Canonical Tagging Code Block

Related Posts