Wiring GA4 and Search Console Into One Trustworthy Query Table

Every content team we work with hits the same wall around month six of a programmatic build: GA4 tells you what users did but not what they searched, and Search Console tells you what they searched but almost nothing about what happened after the click. Stitching them together sounds trivial. It isn't — and the way you stitch them decides whether your content prioritization is honest or theatre.

This is the workflow we've landed on after doing it wrong a few times. It runs on BigQuery, costs pennies at most sites, and gives editors a single table they can actually query.

Why the naive join fails

The obvious approach is: pull GSC's Search Analytics API by page + query, pull GA4 by landing page, join on URL. It looks fine in a dashboard. It's also wrong in three specific ways.

First, GSC's query-level data is anonymized. Google drops queries below a threshold (roughly 1% of impressions for a property, in our experience), so the sum of queries for a page rarely equals the page's total clicks. If you naively distribute GA4 sessions across queries proportionally, you're inventing data.

Second, GA4's landing_page isn't a stable key. It includes query strings, sometimes fragments, sometimes UTM residue. GSC normalizes to the canonical. A raw string join loses 10–30% of rows on most sites we've audited.

Third, dates don't line up. GSC data is in Pacific Time and finalizes over three days. GA4 BigQuery export is in the property's reporting timezone and lands with intraday tables. Join on date naively and your last three days will look like a cliff.

The mental model that works

Stop trying to attribute sessions to queries. Instead, build a page-day grain table where GSC metrics and GA4 metrics live side-by-side, and keep queries in a separate long table joined by page + date. Anything that needs query-level behavioral data gets a documented approximation, not a fake number.

The two sources, set up properly

You need both exports flowing into the same BigQuery dataset. The GA4 side is a one-click linkage in the GA4 admin. The Search Console bulk data export (available since 2023) is the piece most teams still miss — it drops daily tables into BigQuery with searchdata_url_impression and searchdata_site_impression tables, and it's the only way to get query data without the API's row limits.

A few gotchas we've hit:

• Enable the GSC export at the domain property level if you can. URL-prefix properties fragment your data.
• The GSC export starts from the day you enable it. There's no backfill. Turn it on before you need it.
• GA4's export has an events_intraday_* table for today and events_* for finalized days. Query the wildcard events_* for reporting, keep intraday for freshness checks only.

Building the unified page-day table

Here's the shape of the core query. It normalizes URLs, aligns timezones, and gives you one row per canonical page per day.

WITH gsc AS (
  SELECT
    DATE(data_date) AS date,
    -- Strip protocol, trailing slash, lowercase
    REGEXP_REPLACE(
      LOWER(REGEXP_REPLACE(url, r'^https?://[^/]+', '')),
      r'/$', ''
    ) AS page_path,
    SUM(impressions) AS impressions,
    SUM(clicks) AS clicks,
    SAFE_DIVIDE(SUM(sum_position * impressions), SUM(impressions)) AS avg_position
  FROM `project.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE()
    AND is_anonymized_query = FALSE
  GROUP BY 1, 2
),
ga4 AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS date,
    REGEXP_REPLACE(
      LOWER(REGEXP_EXTRACT(
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
        r'^https?://[^/]+([^?#]*)'
      )),
      r'/$', ''
    ) AS page_path,
    COUNT(DISTINCT CONCAT(user_pseudo_id,
      CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
    )) AS sessions,
    COUNTIF(event_name = 'scroll') AS scrolls,
    COUNTIF(event_name IN ('sign_up','purchase','generate_lead')) AS conversions
  FROM `project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') = 'organic'
  GROUP BY 1, 2
)
SELECT
  COALESCE(g.date, a.date) AS date,
  COALESCE(g.page_path, a.page_path) AS page_path,
  g.impressions, g.clicks, g.avg_position,
  a.sessions, a.scrolls, a.conversions,
  SAFE_DIVIDE(a.conversions, a.sessions) AS cvr
FROM gsc g
FULL OUTER JOIN ga4 a
  USING (date, page_path);

A few things worth calling out.

The FULL OUTER JOIN matters. An inner join hides pages GSC sees but GA4 doesn't (usually rendering or consent issues) and pages GA4 sees but GSC doesn't (usually referrer misclassification or non-Google traffic that snuck into an organic bucket). Both discrepancies are diagnostics you want.

The is_anonymized_query = FALSE filter on the URL table is intentional. We're building the page-day grain here, so anonymized rows are fine to include — but we filter them out when we go query-level later.

We filter GA4 to medium = 'organic' at the event level. Doing it downstream in the join is tempting but breaks session counting because a single session can have multiple traffic sources.

Handling the timezone drift

GSC dates are Pacific. GA4 dates follow your property setting. On a US property this is often close enough to ignore. On a European or APAC property, the same 24-hour window ends up split across two GSC dates. The fix isn't complicated — shift one side — but pick one convention and document it. We standardize on GSC's date and accept that GA4 metrics for date = X mean "GA4 activity that mostly overlaps with GSC's day X." For weekly and monthly rollups it disappears.

The query-level table, done honestly

Once the page-day table exists, layer queries on top:

SELECT
  DATE(data_date) AS date,
  query,
  url AS page_url,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SAFE_DIVIDE(SUM(sum_position * impressions), SUM(impressions)) AS avg_position
FROM `project.searchconsole.searchdata_url_impression`
WHERE is_anonymized_query = FALSE
  AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE()
GROUP BY 1, 2, 3;

The key discipline: never join this to session or conversion counts at the query level. If a stakeholder asks "which query converts best?", the answer is that you don't know — Google doesn't tell you. What you can do is join query intent (via clustering or manual labels) to the page-day table and report conversion rate by intent cluster. That's a defensible number.

Turning the table into decisions

A unified table is only useful if it changes behavior. The three views we build on top for every content client:

Opportunity pages. Pages with high impressions, position between 5 and 15, and CTR at least 20% below the site's average for that position band. These are pages where a title/meta rewrite or a content refresh has a good chance of moving the needle. Sort by impressions * (expected_ctr - actual_ctr).

Leaky pages. Pages with strong clicks but conversion rate more than one standard deviation below the template's median. Usually a UX or intent mismatch problem, not an SEO one — but it's the SEO table that surfaces it.

Dying pages. Pages where clicks in the last 28 days are down 40%+ versus the prior 28 days, and impressions are also down (rules out a CTR blip). Feed these into your refresh queue before Google decides for you.

Freshness and cost

The GSC export lands roughly two days late. GA4's finalized export lands the next day. Schedule the join to run once a day, around 14:00 UTC, and materialize it as a partitioned table clustered on page_path. On sites up to a few hundred thousand pages, the daily incremental costs less than a coffee per month. Full 90-day rebuilds we schedule weekly to absorb any late-arriving GSC corrections.

If you want a dashboard on top, point Looker Studio or Metabase at the materialized table, not at the raw sources. Every team that queries the raw GA4 export from a BI tool eventually gets a surprise bill.

Where we'd start

If you're setting this up from scratch this week: turn on the Search Console BigQuery export today (it doesn't backfill, so every day you wait is a day of query history you'll never have), then wire up the GA4 export, then wait a week and write the join. Don't build the dashboard first. Build the page-day table, query it manually for a fortnight, and let the questions your editors actually ask shape what you put on the dashboard. If you'd rather have us stand the whole pipeline up, that's the kind of work our data and analytics engagements are built around.