BigQuery + GSC: queries your agency won't run

The Search Console interface caps you at 1,000 rows per report and aggregates data in ways that hide more than they show. Connect GSC to BigQuery via the Bulk Data Export and the game changes: you get millions of rows of impressions, query, page and device, no sampling. Most agencies stop at the default dashboard and ship a report with a line chart. The problem isn't data scarcity, it's SQL scarcity. This post brings queries that run in production and answer questions the UI can't, the type that asks: which pages are losing impressions month over month for bottom-of-funnel queries?

Before any query, get the setup right. Enable Bulk Data Export under Settings > Bulk Data Export and point it at a GCP project with billing active. The tables that matter are searchdata_site_impression and searchdata_url_impression. The first holds anonymized filtered queries and is great for aggregated trends; the second offers URL-level granularity. Costs? A site with 5M impressions/month generates about 200MB/day, which comes out to less than a dollar per month with careful on-demand queries. Before diving into SQL, make sure your on-page tracking is clean, as How to audit on-page SEO without falling into guesswork shows, or you'll correlate noise.

Query 1: content decay with a 28-day rolling window. Use LAG() over a weekly aggregation per URL to detect pages that dropped more than 30% in clicks versus the prior window, filtering for a minimum of 100 clicks/week to cut noise. The output is a prioritized rewrite list, infinitely better than opening the dashboard and squinting at red arrows. Cross with the worksheet from Content decay: spotting the posts quietly losing traffic and you already have a quarter of backlog. To decide between rewriting and rebuilding from scratch, Rewrite or rebuild: making the call with SERP data has the SERP-data framework that plugs right into this pipeline.

Query 2: striking-distance keywords at position 8-20 with CTR below benchmark. Join searchdata_url_impression with a CTE of position benchmarks (use the numbers from CTR benchmark by position: updated 2026 data or compute from your own site). When a URL shows up at position 11 with 0.8% CTR and the benchmark is 2.1%, you have a title or meta description problem, not a ranking problem. This is the query that pays for the consultant by itself: it produces a list of 50-200 pages where editing a title in one sprint becomes clicks the following month. Pair it with the patterns from Title tags that convert: 7 patterns tested on real SERPs.

Query 3: real cannibalization, not Ahrefs cannibalization. Group by query and count distinct URLs with more than 10 impressions in the same week. If a query has 3+ URLs surfacing, you have actual cannibalization (with demand signal), not just two pages sharing a word. Export to CSV and cross-reference with real canonical tags from a Screaming Frog crawl. Twenty percent of cases turn out to be misconfigured canonicals, see Canonical tags: common mistakes bleeding your organic traffic. The rest is editorial: consolidate, 301 redirect, or reposition one of the pages for a different intent.

Query 4: branded vs non-branded with REGEXP_CONTAINS. Build a calculated column classifying a query as brand when it matches your brand regex (include common typos). Group by month and you'll see what plenty of agencies hide: 60% of many clients' organic traffic is brand search, meaning SEO didn't bring it. This query saves relationships and justifies honest investment, aligned with Honest SEO KPIs: beyond rankings and traffic and SEO Attribution: Proving ROI Without Last-Click. Show it to the CFO before they ask.

Query 5: intent mismatch using query patterns. Classify queries by intent (informational, transactional, navigational, comparative) with CASE WHEN over simple regex ("how", "best", "price", "vs", "review"). Cross with page type (PLP, PDP, blog, home) and you uncover product pages ranking for informational queries or vice versa. This is gold for e-commerce, as E-commerce on-page: PLP vs PDP without cannibalization details. To ground the classification, Search intent: 4 types and how to map them on the SERP has the taxonomy I use.

Query 6: page-query fingerprint to detect algorithm shifts. Snapshot the top 20 queries per URL weekly. When the set changes by more than 40% in a single week with no on-page edits, Google reinterpreted the page. You only catch this with BigQuery, the dashboard will never tell you. Practical takeaway: schedule these six queries in BigQuery Scheduled Queries, materialize them into weekly tables, plug Looker Studio on top, and you have an observatory that costs less than $5/month and beats any $500/month platform. SQL isn't a big-agency luxury, it's the floor for anyone who takes SEO seriously.