Skip to content

Instantly share code, notes, and snippets.

@sterlingsky

sterlingsky/pages losing visibility due to num=100

Last active October 18, 2025 21:02
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save sterlingsky/b09c954d0cd791db64953278cf8a0ec1 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save sterlingsky/b09c954d0cd791db64953278cf8a0ec1 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
pages losing visibility due to num=100
WITH A AS (
SELECT
url,
SUM(clicks) as clicks,
SUM(impressions) as impressions,
SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) as avg_position
FROM `project`.`searchconsole`.`searchdata_url_impression`
WHERE data_date BETWEEN '2025-09-03' AND '2025-09-09' -- Week 1
AND query IS NULL
GROUP BY url
),
B AS (
SELECT
url,
SUM(clicks) as clicks,
SUM(impressions) as impressions,
SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) as avg_position
FROM `project`.`searchconsole`.`searchdata_url_impression`
WHERE data_date BETWEEN '2025-09-10' AND '2025-09-16' -- Week 2
AND query IS NULL
GROUP BY url
)
-- Pages in Week 1 but not in Week 2 (lost visibility)
SELECT
A.url,
A.impressions as week1_impressions,
A.clicks as week1_clicks,
A.avg_position as week1_avg_position
FROM A
LEFT JOIN B ON A.url = B.url
WHERE B.url IS NULL
ORDER BY A.impressions DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment