GA4 + GSC Data Joins: Building a Query-Level Performance Table That Doesn't Lie

Every SEO deck we've ever seen ranks queries by clicks. That's fine until someone asks the obvious follow-up: which of those queries actually made us money? GA4 knows revenue but not the query. GSC knows the query but not the revenue. The join is the whole game, and most teams do it wrong.

This is how we build a query-level performance table in BigQuery that engineering and marketing can both trust, and the specific traps that make the numbers lie if you're not careful.

Why the join is harder than it looks

The honest version: GA4 and GSC measure different things, on different clocks, with different identities.

• GA4 measures sessions and events on your site. Its unit of truth is the session_id and, for revenue, the purchase event.
• GSC measures impressions and clicks in Google Search. Its unit of truth is the (query, page, country, device, date) tuple.
• The only field they share is landing page URL (and date, roughly). Query never appears in GA4. User never appears in GSC.

So any join between the two is a page-level join, not a query-level one. When you see a dashboard claiming "the query best crm for freelancers generated $1,240 in revenue," what it actually means is: "the landing page that ranks for that query generated $1,240, and we attributed a slice of it proportional to that query's share of the page's clicks." That's a proportional allocation, not a fact. Say so out loud, and your stakeholders will trust the rest of the pipeline more.

The (not provided) problem, revisited

Since ~2011, Google has stripped the query from the referrer. GSC is the only sanctioned way back in, and even it caps at 1,000 rows per API call and applies anonymisation on low-volume queries. If you're not using the BigQuery bulk export for Search Console, you're leaving 40–60% of your long-tail queries on the table in our experience. Turn it on before you build anything else.

The data model

We end up with three staging tables and one mart table.

Staging

1. stg_gsc_daily — from the GSC BigQuery export (searchdata_url_impression). Grain: (date, url, query, country, device, search_type).
2. stg_ga4_sessions — from the GA4 BigQuery export. Grain: (date, session_id, landing_page, source, medium).
3. stg_ga4_revenue — purchase events aggregated to session. Grain: (session_id, revenue, currency, items).

Mart

mart_query_performance — grain: (date, url, query, country, device). Columns include impressions, clicks, position, organic_sessions, conversions, revenue_allocated, and a revenue_confidence score.

That last column is the one nobody builds and everybody needs.

The actual SQL

Here's a stripped-down version of the join. We use it as a scheduled query in BigQuery, run daily at 09:00 UTC (GSC data lag is 2–3 days, so we always look back a week).

-- Step 1: Page-level GA4 organic revenue
WITH ga4_page_revenue AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS date,
    REGEXP_REPLACE(
      (SELECT value.string_value FROM UNNEST(event_params)
       WHERE key = 'page_location'),
      r'\?.*$', ''
    ) AS landing_page,
    SUM(ecommerce.purchase_revenue) AS revenue,
    COUNT(DISTINCT CONCAT(user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params)
       WHERE key = 'ga_session_id'))) AS sessions,
    COUNTIF(event_name = 'purchase') AS conversions
  FROM `project.analytics_XXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))
    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
    AND (SELECT value.string_value FROM UNNEST(event_params)
         WHERE key = 'medium') = 'organic'
  GROUP BY 1, 2
),

-- Step 2: GSC query clicks per page, per day
gsc_query_share AS (
  SELECT
    data_date AS date,
    url,
    query,
    country,
    device,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    SAFE_DIVIDE(SUM(clicks), SUM(SUM(clicks))
      OVER (PARTITION BY data_date, url)) AS click_share
  FROM `project.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN
    DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)
    AND DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
    AND is_anonymized_query = FALSE
  GROUP BY 1, 2, 3, 4, 5
)

-- Step 3: Allocate revenue by click share
SELECT
  g.date,
  g.url,
  g.query,
  g.country,
  g.device,
  g.impressions,
  g.clicks,
  g.click_share,
  r.sessions AS page_organic_sessions,
  r.revenue AS page_revenue,
  r.revenue * g.click_share AS revenue_allocated,
  r.conversions * g.click_share AS conversions_allocated,
  CASE
    WHEN g.clicks >= 20 AND r.sessions >= 30 THEN 'high'
    WHEN g.clicks >= 5  AND r.sessions >= 10 THEN 'medium'
    ELSE 'low'
  END AS revenue_confidence
FROM gsc_query_share g
LEFT JOIN ga4_page_revenue r
  ON g.date = r.date
  AND g.url = r.landing_page;

A few things to notice.

The traps

URL normalisation will bite you

GSC stores canonical URLs. GA4 stores whatever page_location fired in the tag — including query strings, tracking parameters, trailing slashes, and mixed-case hostnames. If you don't normalise both sides to the same shape, roughly 15–25% of your page joins will silently fail. In our template above we strip query strings; in production you also want to lowercase the host, strip www. consistently, and decide whether trailing slashes are canonical.

Build a dim_url table that maps every observed variant to a canonical form, and join through it.

Anonymised queries are not zero

GSC's is_anonymized_query = TRUE rows still contain real clicks and impressions — you just don't know the query. Filter them out of the query-level mart, but keep them in a separate mart_page_performance table so your page totals reconcile. If you skip this, your "total organic clicks per page" from the mart will be lower than the GSC UI and someone will file a bug.

Click share ≠ revenue share

Proportional allocation assumes every visitor from every query converts at the same rate on the same page. That's rarely true — brand queries convert 3–10x better than discovery queries in our experience. The revenue_confidence column is the honest hedge. For 'low' confidence rows, don't rank by revenue; rank by clicks and position.

If a specific page really matters (checkout, pricing, high-intent landing), consider a query classification layer — brand vs. non-brand vs. informational — and allocate revenue per class rather than uniformly. That's a whole second pipeline; do it when the money justifies it.

Time zone drift

GSC reports in Pacific Time. GA4 reports in your property's configured timezone. If you join on date naively, you'll get a rolling 3–5% mismatch that looks like data quality issues but is actually just midnight. Convert both to UTC dates before joining, and document it.

What to actually do with this table

Once the mart is stable, the questions get interesting:

• Revenue per impression by query cluster — where should we invest more content?
• Position 4–10 queries with high allocated revenue — the classic "one push and they're on page one" list, but weighted by money instead of clicks.
• Pages with high clicks but zero allocated revenue — either a conversion problem or a query-intent mismatch worth investigating.
• Cannibalisation detection — same query, multiple URLs, revenue split across them. Merge or canonicalise.

We expose the mart to the content team through a Looker Studio dashboard, but the real users are the engineers building the programmatic SEO pipelines. When a template goes live, they want to know within a fortnight whether it's earning or just decorating the sitemap.

Where we'd start

If you're staring at a GSC UI and a GA4 UI and trying to reconcile them in a spreadsheet, stop. In order:

1. Enable the GSC BigQuery bulk export today. It's free and the data starts from the day you turn it on, so every day you wait is data you'll never get back.
2. Confirm the GA4 BigQuery export is on and has purchase events with revenue.
3. Build a dim_url normalisation table before you write a single join. It'll save you a week of debugging later.
4. Ship the mart with the revenue_confidence column from day one. Once stakeholders see numbers without it, they'll trust them too much.
5. Only then build the dashboard.

The point isn't a prettier report. It's giving your content and engineering teams a shared, honest number for the question they keep asking anyway: did this page pay for itself?