Search Console data at scale. Export it. Query it. Trust it.

-- Latest complete 28 days, Pacific Time
SELECT
  url,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr_pct,
  ROUND(SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1, 1) AS avg_position
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY)
                    AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 3 DAY)
  AND search_type = 'WEB'
GROUP BY url
HAVING SUM(impressions) >= 100
ORDER BY clicks DESC
LIMIT 100;

-- Replace the pattern with your own brand names and common misspellings
SELECT
  CASE
    WHEN is_anonymized_query THEN 'hidden (anonymised)'
    WHEN REGEXP_CONTAINS(LOWER(query), r'\b(acme|acme[\s-]?analytics)\b') THEN 'brand'
    ELSE 'non-brand'
  END AS query_group,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  ROUND(SAFE_DIVIDE(SUM(sum_top_position), SUM(impressions)) + 1, 1) AS avg_position
FROM `your-project.searchconsole.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY)
                    AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 3 DAY)
  AND search_type = 'WEB'
GROUP BY query_group
ORDER BY impressions DESC;

SELECT
  FORMAT_DATE('%Y-%m', data_date) AS month,
  device,
  SUM(impressions) AS impressions,
  SUM(IF(is_anonymized_query, impressions, 0)) AS hidden_impressions,
  ROUND(SAFE_DIVIDE(SUM(IF(is_anonymized_query, impressions, 0)), SUM(impressions)) * 100, 1) AS hidden_share_pct
FROM `your-project.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 90 DAY)
  AND search_type = 'WEB'
GROUP BY month, device
ORDER BY month, device;

SELECT
  query,
  url,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  ROUND(SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1, 1) AS avg_position
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY)
                    AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 3 DAY)
  AND search_type = 'WEB'
  AND query IS NOT NULL
GROUP BY query, url
HAVING SUM(impressions) >= 200
  AND SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1 BETWEEN 4 AND 10
ORDER BY impressions DESC
LIMIT 100;

-- 1) Dates in the last 60 days with no rows at all
WITH expected AS (
  SELECT day
  FROM UNNEST(GENERATE_DATE_ARRAY(
    DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 60 DAY),
    DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 3 DAY))) AS day
),
seen AS (
  SELECT DISTINCT data_date
  FROM `your-project.searchconsole.searchdata_site_impression`
  WHERE data_date >= DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 60 DAY)
)
SELECT expected.day AS missing_date
FROM expected
LEFT JOIN seen ON seen.data_date = expected.day
WHERE seen.data_date IS NULL
ORDER BY missing_date;

-- 2) What the export log says was published, and when
SELECT
  namespace,
  MAX(data_date) AS newest_data_date,
  MAX(publish_time) AS last_publish_time,
  COUNT(*) AS logged_exports
FROM `your-project.searchconsole.ExportLog`
GROUP BY namespace
ORDER BY namespace;

-- recent = 30 to 3 days ago, prior = 58 to 31 days ago (Pacific Time)
WITH w AS (
  SELECT
    url,
    SUM(IF(data_date >= DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), clicks, 0)) AS recent_clicks,
    SUM(IF(data_date <  DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), clicks, 0)) AS prior_clicks,
    SUM(IF(data_date >= DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), impressions, 0)) AS recent_impressions,
    SUM(IF(data_date <  DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), impressions, 0)) AS prior_impressions
  FROM `your-project.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 58 DAY)
                      AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 3 DAY)
    AND search_type = 'WEB'
  GROUP BY url
)
SELECT
  url,
  prior_clicks,
  recent_clicks,
  recent_clicks - prior_clicks AS click_delta,
  ROUND(SAFE_DIVIDE(recent_clicks - prior_clicks, prior_clicks) * 100, 1) AS click_change_pct,
  recent_impressions - prior_impressions AS impression_delta
FROM w
WHERE prior_clicks >= 20
ORDER BY click_delta ASC
LIMIT 50;

WITH w AS (
  SELECT
    query,
    SUM(IF(data_date >= DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), impressions, 0)) AS recent_impressions,
    SUM(IF(data_date <  DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), impressions, 0)) AS prior_impressions,
    SUM(IF(data_date >= DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), clicks, 0)) AS recent_clicks,
    SUM(IF(data_date <  DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), clicks, 0)) AS prior_clicks
  FROM `your-project.searchconsole.searchdata_site_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 58 DAY)
                      AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 3 DAY)
    AND search_type = 'WEB'
    AND query IS NOT NULL
  GROUP BY query
)
SELECT
  query,
  prior_impressions,
  recent_impressions,
  recent_impressions - prior_impressions AS impression_gain,
  prior_clicks,
  recent_clicks
FROM w
WHERE prior_impressions >= 100
  AND recent_impressions >= prior_impressions * 1.5
  AND recent_clicks <= prior_clicks * 1.1
ORDER BY impression_gain DESC
LIMIT 50;

WITH recent AS (
  SELECT query, SUM(impressions) AS impressions, SUM(clicks) AS clicks
  FROM `your-project.searchconsole.searchdata_site_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY)
                      AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 3 DAY)
    AND search_type = 'WEB'
    AND query IS NOT NULL
  GROUP BY query
  HAVING SUM(impressions) >= 20
),
earlier AS (
  SELECT DISTINCT query
  FROM `your-project.searchconsole.searchdata_site_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 210 DAY)
                      AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 31 DAY)
    AND search_type = 'WEB'
    AND query IS NOT NULL
)
SELECT r.query, r.impressions, r.clicks
FROM recent AS r
LEFT JOIN earlier AS e ON e.query = r.query
WHERE e.query IS NULL
ORDER BY r.impressions DESC
LIMIT 100;

WITH qu AS (
  SELECT query, url, SUM(impressions) AS impressions, SUM(clicks) AS clicks
  FROM `your-project.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY)
                      AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 3 DAY)
    AND search_type = 'WEB'
    AND query IS NOT NULL
  GROUP BY query, url
  HAVING SUM(impressions) >= 20
),
shares AS (
  SELECT
    query,
    url,
    impressions,
    SUM(impressions) OVER (PARTITION BY query) AS query_impressions,
    SAFE_DIVIDE(impressions, SUM(impressions) OVER (PARTITION BY query)) AS impression_share
  FROM qu
),
agg AS (
  SELECT
    query,
    ANY_VALUE(query_impressions) AS query_impressions,
    COUNTIF(impression_share >= 0.2) AS urls_with_20pct_share,
    STRING_AGG(url, ' | ' ORDER BY impressions DESC LIMIT 3) AS top_urls
  FROM shares
  GROUP BY query
)
SELECT *
FROM agg
WHERE query_impressions >= 200
  AND urls_with_20pct_share >= 2
ORDER BY query_impressions DESC
LIMIT 50;

WITH d AS (
  SELECT
    COALESCE(REGEXP_EXTRACT(url, r'^https?://[^/]+(/[^/?#]*)'), '/') AS directory,
    SUM(IF(data_date >= DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), clicks, 0)) AS recent_clicks,
    SUM(IF(data_date <  DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), clicks, 0)) AS prior_clicks,
    SUM(IF(data_date >= DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), impressions, 0)) AS recent_impressions,
    SUM(IF(data_date <  DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), impressions, 0)) AS prior_impressions
  FROM `your-project.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 58 DAY)
                      AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 3 DAY)
    AND search_type = 'WEB'
  GROUP BY directory
)
SELECT
  directory,
  prior_clicks,
  recent_clicks,
  ROUND(SAFE_DIVIDE(recent_clicks - prior_clicks, prior_clicks) * 100, 1) AS click_change_pct,
  ROUND(SAFE_DIVIDE(recent_clicks, SUM(recent_clicks) OVER ()) * 100, 1) AS share_of_recent_clicks,
  prior_impressions,
  recent_impressions
FROM d
WHERE prior_clicks + recent_clicks >= 20
ORDER BY recent_clicks DESC;

WITH d AS (
  SELECT
    COALESCE(REGEXP_EXTRACT(url, r'^https?://[^/]+(/[^/?#]*)'), '/') AS directory,
    SUM(IF(device = 'MOBILE', impressions, 0)) AS mobile_impressions,
    SUM(IF(device = 'DESKTOP', impressions, 0)) AS desktop_impressions,
    SUM(IF(device = 'MOBILE', clicks, 0)) AS mobile_clicks,
    SUM(IF(device = 'DESKTOP', clicks, 0)) AS desktop_clicks,
    SUM(IF(device = 'MOBILE', sum_position, 0)) AS mobile_sum_position,
    SUM(IF(device = 'DESKTOP', sum_position, 0)) AS desktop_sum_position
  FROM `your-project.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY)
                      AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 3 DAY)
    AND search_type = 'WEB'
  GROUP BY directory
)
SELECT
  directory,
  mobile_impressions,
  desktop_impressions,
  ROUND(SAFE_DIVIDE(mobile_clicks, mobile_impressions) * 100, 2) AS mobile_ctr_pct,
  ROUND(SAFE_DIVIDE(desktop_clicks, desktop_impressions) * 100, 2) AS desktop_ctr_pct,
  ROUND(SAFE_DIVIDE(mobile_sum_position, mobile_impressions) + 1, 1) AS mobile_position,
  ROUND(SAFE_DIVIDE(desktop_sum_position, desktop_impressions) + 1, 1) AS desktop_position
FROM d
WHERE mobile_impressions >= 500
  AND desktop_impressions >= 500
ORDER BY mobile_impressions DESC
LIMIT 50;

WITH qp AS (
  SELECT
    url,
    query,
    IF(data_date >= DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY), 'recent', 'prior') AS period,
    SUM(impressions) AS impressions
  FROM `your-project.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 58 DAY)
                      AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 3 DAY)
    AND search_type = 'WEB'
    AND query IS NOT NULL
  GROUP BY url, query, period
  HAVING SUM(impressions) >= 5
),
b AS (
  SELECT
    url,
    COUNTIF(period = 'prior') AS prior_queries,
    COUNTIF(period = 'recent') AS recent_queries
  FROM qp
  GROUP BY url
)
SELECT url, prior_queries, recent_queries, recent_queries - prior_queries AS query_change
FROM b
WHERE prior_queries >= 10
ORDER BY query_change ASC
LIMIT 50;