BigQuery Bulk Export SQL Cookbook
Production-tested BigQuery SQL queries designed to analyze raw Google Search Console bulk export datasets without hitting the 1,000-row UI limitation.
Bulk Export Table Architecture
Google Search Console’s native BigQuery bulk export dumps your daily search performance data into two partition-enabled tables:
Site-level aggregated records. One row per site, query, country, device, search type, and date. Does not split records by landing URL. Best for macro brand and macro query trend analysis.
URL-level granular records. One row per landing page URL, query, country, device, and date. Necessary for finding cannibalization, striking distance URLs, and template audits.
Tested BigQuery SQL Queries
1. Striking Distance Opportunities (Positions 11–20)
Finds queries ranking on Page 2 (average position 11.0 to 20.0) that generated at least 500 impressions over the last 28 days. These represent the highest-leverage optimization targets.
SELECT
query,
url,
SUM(impressions) AS total_impressions,
SUM(clicks) AS total_clicks,
ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr_pct,
ROUND(AVG(sum_position / impressions) + 1, 2) AS avg_position
FROM
`YOUR_PROJECT_ID.searchconsole.searchdata_url_impression`
WHERE
data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
AND query IS NOT NULL
GROUP BY
query, url
HAVING
avg_position BETWEEN 11.0 AND 20.0
AND total_impressions >= 500
ORDER BY
total_impressions DESC
LIMIT 100;2. Query Cannibalization & Intent Conflict Detector
Identifies search queries where two or more distinct URLs from your site are competing and receiving impressions during the same 14-day window.
WITH QueryPageSummary AS (
SELECT
query,
url,
SUM(impressions) AS page_impressions,
SUM(clicks) AS page_clicks,
ROUND(AVG(sum_position / impressions) + 1, 2) AS page_avg_position
FROM
`YOUR_PROJECT_ID.searchconsole.searchdata_url_impression`
WHERE
data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
AND query IS NOT NULL
GROUP BY
query, url
)
SELECT
q.query,
COUNT(DISTINCT q.url) AS competing_urls_count,
STRING_AGG(q.url, '\n' ORDER BY q.page_impressions DESC) AS competing_urls,
SUM(q.page_impressions) AS aggregate_impressions,
SUM(q.page_clicks) AS aggregate_clicks
FROM
QueryPageSummary q
GROUP BY
q.query
HAVING
competing_urls_count > 1
AND aggregate_impressions >= 250
ORDER BY
competing_urls_count DESC, aggregate_impressions DESC
LIMIT 50;3. Year-over-Year (YoY) Search Traffic & Query Drift
Compares search demand and click volume for identical 28-day windows across two consecutive years, isolating declining queries from newly discovered keywords.
WITH CurrentPeriod AS (
SELECT
query,
SUM(clicks) AS clicks_current,
SUM(impressions) AS impressions_current
FROM
`YOUR_PROJECT_ID.searchconsole.searchdata_site_impression`
WHERE
data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
AND query IS NOT NULL
GROUP BY query
),
PreviousPeriod AS (
SELECT
query,
SUM(clicks) AS clicks_previous,
SUM(impressions) AS impressions_previous
FROM
`YOUR_PROJECT_ID.searchconsole.searchdata_site_impression`
WHERE
data_date BETWEEN DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), INTERVAL 28 DAY)
AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
AND query IS NOT NULL
GROUP BY query
)
SELECT
COALESCE(c.query, p.query) AS query,
COALESCE(c.clicks_current, 0) AS clicks_current,
COALESCE(p.clicks_previous, 0) AS clicks_previous,
(COALESCE(c.clicks_current, 0) - COALESCE(p.clicks_previous, 0)) AS click_difference,
COALESCE(c.impressions_current, 0) AS impressions_current,
COALESCE(p.impressions_previous, 0) AS impressions_previous
FROM
CurrentPeriod c
FULL OUTER JOIN
PreviousPeriod p ON c.query = p.query
WHERE
COALESCE(c.impressions_current, 0) >= 100 OR COALESCE(p.impressions_previous, 0) >= 100
ORDER BY
click_difference ASC
LIMIT 100;4. CTR Underperformer Audit (Top 5 Ranking with Below-Average CTR)
Finds URLs ranking prominently in the top 5 positions whose click-through rate is below 5%, signaling poor title tag or meta description snippet optimization.
SELECT
url,
query,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr_pct,
ROUND(AVG(sum_position / impressions) + 1, 2) AS avg_position
FROM
`YOUR_PROJECT_ID.searchconsole.searchdata_url_impression`
WHERE
data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
AND query IS NOT NULL
GROUP BY
url, query
HAVING
avg_position <= 5.0
AND ctr_pct < 5.0
AND impressions >= 1000
ORDER BY
impressions DESC
LIMIT 50;Cost Optimization: Partition Pruning Mandatory
Both export tables are partitioned by data_date. Always include a strict WHERE data_date >= ... condition in your queries. Querying without partition filtering forces BigQuery to scan your entire historical dataset, incurring unnecessary query fees.