All articles
SEO & GrowthOctober 24, 2026 6 min read

GSC API to BigQuery: The Query Mining Pipeline That Finds Hidden Wins

The Search Console UI hides your best growth opportunities behind a 1,000-row cap and 16-month wall. Here's the pipeline we build to pull GSC into BigQuery and surface pages that are one edit away from more traffic.

The Search Console UI is lying to you by omission. It caps rows at 1,000 per view, truncates anonymised queries, and only holds 16 months of data — which means every month, your best growth signals quietly fall off a cliff. If you run a programmatic or content-heavy site, you need the raw stream in a warehouse, not a dashboard.

This is the pipeline we set up for clients when they ask why their "SEO reports" never translate into shipped work. It pulls GSC via API into BigQuery, joins it with the GA4 export, and produces a weekly list of pages that are one edit away from meaningful traffic lift.

Why the GSC UI fails engineering teams

The web interface is built for spot-checks, not pipelines. Three specific limits bite:

  • 1,000-row cap per query. On a 50k-URL site, the long tail — where most of the opportunity lives — is invisible.
  • 16-month retention. Year-over-year comparisons past month 16 are impossible without your own storage.
  • No joins. You can't trivially blend GSC impressions with GA4 conversions, your CMS metadata, or your internal link graph.

The bulk data export to BigQuery (Google rolled this out in 2023) solves retention and volume, but it dumps raw rows. You still need a modelling layer to make it useful. And for sites that want backfill or a specific dimension pivot the bulk export doesn't give you, the Search Analytics API is still the right tool.

The pipeline shape

At a high level:

  1. Ingest — GSC bulk export (daily, automatic) plus a small Python job for API pulls where we need custom dimensions.
  2. Model — dbt or scheduled SQL that produces clean fact tables: fct_query_page_daily, dim_page, dim_query_cluster.
  3. Join — GA4 BigQuery export for conversion data, plus a CMS metadata table (publish date, author, template, word count).
  4. Surface — scheduled queries that write to a opportunities table, which feeds a Looker Studio board and a Slack digest.

Nothing exotic. The value is in the modelling choices, not the stack.

Setting up the bulk export

In Search Console, under Settings → Bulk data export, point it at a BigQuery dataset. You'll get three tables daily:

  • searchdata_site_impression — aggregated at site level
  • searchdata_url_impression — per-URL, with query dimension
  • ExportLog — audit trail

The searchdata_url_impression table is the one you'll live in. Each row is a (date, url, query, country, device, search_type) tuple with impressions, clicks, and summed position.

One gotcha: sum_position is summed, not averaged. To get average position you divide by impressions. Teams miss this constantly and then wonder why their "average position" chart is nonsense.

Building the striking-distance query

The single most useful artefact this pipeline produces is a striking-distance report: queries where you rank between position 5 and 20, have real impression volume, and a CTR that suggests the SERP would reward a jump.

Here's the core SQL, trimmed for readability:

WITH last_28 AS (
  SELECT
    url,
    query,
    SUM(impressions) AS impressions,
    SUM(clicks) AS clicks,
    SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) AS avg_position
  FROM `project.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                      AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    AND search_type = 'WEB'
    AND is_anonymized_query = FALSE
  GROUP BY url, query
),
scored AS (
  SELECT
    url,
    query,
    impressions,
    clicks,
    avg_position,
    SAFE_DIVIDE(clicks, impressions) AS ctr,
    -- opportunity score: impressions * position-decay toward 1
    impressions * (1.0 / avg_position) AS opp_score
  FROM last_28
  WHERE impressions >= 50
    AND avg_position BETWEEN 5 AND 20
)
SELECT *
FROM scored
ORDER BY opp_score DESC
LIMIT 500;

Tune the thresholds for your site. On a small blog, impressions >= 50 over 28 days is reasonable; on an enterprise programmatic site, you'll want 500 or 1,000 to cut noise.

Clustering queries before you act

Raw query lists are noisy — the same intent shows up with twenty surface variations. Before handing anything to a writer or an engineer, cluster.

Two approaches we use:

  • Cheap: group by the destination URL. If 40 queries all point to the same page, that page is your work unit.
  • Better: embed queries with a small model (OpenAI text-embedding-3-small or similar), run HDBSCAN or a simple cosine-similarity threshold, and give each cluster a representative label. Store the cluster_id back in BigQuery so you can track cluster-level impressions over time.

Clustering is what turns "500 keywords to review" into "23 pages to improve." That difference is the whole point.

Joining GA4 for revenue-weighted priorities

Impressions and clicks don't pay the bills. Conversions do. The GA4 BigQuery export gives you event-level data; join it to GSC on landing-page URL.

WITH ga_sessions AS (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location') AS landing_url,
    COUNTIF(event_name = 'purchase') AS purchases,
    SUM(ecommerce.purchase_revenue) AS revenue
  FROM `project.analytics_XXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY))
    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  GROUP BY landing_url
)
SELECT
  o.url,
  o.query,
  o.impressions,
  o.opp_score,
  g.purchases,
  g.revenue,
  o.opp_score * COALESCE(g.revenue / NULLIF(g.purchases, 0), 10) AS weighted_score
FROM `project.seo.striking_distance` o
LEFT JOIN ga_sessions g
  ON REGEXP_EXTRACT(g.landing_url, r'https?://[^/]+(/.*?)(?:\?|$)') = o.url;

The COALESCE to a floor value (here, 10) prevents brand-new pages with no conversion history from being ranked at zero. In our experience, that floor should be your site-wide average order value or a conservative lead value — not a guess.

Operationalising the output

A table in BigQuery is not a workflow. You need the opportunities to show up where people make decisions.

The weekly Slack digest

A scheduled Cloud Run job queries the top 15 opportunities, formats them, and posts to a channel. Include: URL, cluster label, impressions, current position, estimated monthly revenue lift (impressions × delta-CTR × AOV). Keep it one message, scannable in ten seconds.

The ticketing bridge

For sites with real content velocity, we auto-create tickets. Each opportunity above a threshold gets a Linear or Jira issue with a templated brief: target query cluster, current page, position, suggested changes (new H2s, FAQ additions, internal links to add from the top 10 referring internal pages). The engineer or writer picks it up, ships, and the pipeline tracks the delta over the next 28 days.

This is the part most teams skip. Without a feedback loop, you'll optimise the same pages twice and never know whether anything worked.

Pitfalls we've hit

  • Anonymised queries silently inflate totals. The is_anonymized_query = TRUE rows have no query string but still have impressions. Filter them out of query-level analysis, keep them for page-level totals.
  • URL normalisation. Trailing slashes, query strings, fragments, and www vs apex all fracture your joins. Normalise once in a view and reuse.
  • Partitioning cost. The GSC export is partitioned by data_date. Always filter on it. We've seen SELECT * scans burn hundreds of dollars because someone forgot a WHERE clause.
  • GA4 attribution lag. Conversion data for the last 48 hours is incomplete. Lag your joins by two days.

If you want a deeper walkthrough of the content-engine side — how we turn these opportunity tickets into shipped page changes — our content and SEO services page covers the end-to-end setup.

Where we'd start

If you're setting this up from scratch this week: turn on the GSC bulk export today (the backfill is zero, so every day you delay is data you'll never get). Set up the GA4 BigQuery export in parallel. Spend week two writing the striking-distance query against real data and tuning thresholds for your traffic volume. Week three, wire the Slack digest. Don't build clustering or ticketing until the digest is producing opportunities a human agrees are real — the rest is premature optimisation on a pipeline no one trusts yet.

#SEO#BigQuery#Google Search Console#Analytics#Data Engineering

Want a team like ours?

72Technologies builds production software for the kind of teams who actually read this blog.

Start a project