Google Search Console + Google Analytics together tell you something neither can alone

Search Console tells you how people find you. Google Analytics tells you what they do after they arrive. Neither tells you the whole story.

GSC knows you ranked #4 for "data warehouse consulting" and got 12 clicks last week. It doesn't know that 3 of those clicks became leads and one signed a $60K contract. GA4 knows someone converted on your contact page. It doesn't know which search query brought them there.

The full story — which search queries generate revenue — only exists when both datasets join in a warehouse.

What each tool actually captures

Google Search Console (pre-click)

GSC records what happens in the search results before someone clicks:

• Queries — the exact search terms that triggered your pages
• Impressions — how many times your page appeared in results
• Clicks — how many times someone clicked through
• Position — your average ranking for that query
• CTR — clicks divided by impressions

This is the only free source of real Google ranking data. Not estimated, not scraped — actual impressions and positions reported by Google.

Google Analytics / GA4 (post-click)

GA4 records what happens after the click:

• Sessions — grouped events attributed to a traffic source
• Pageviews — which pages were loaded
• Engagement — time on page, scroll depth, interactions
• Conversions — goal completions, form submissions, purchases

GA4 knows the user's journey through your site. It does not know which query they typed to get there. The source/medium says "google / organic" but the actual keyword is gone — Google stopped passing it in the referrer years ago.

The gap

GSC has the query. GA4 has the conversion. Neither has both.

This means nobody can answer the question every business needs answered: which search queries drive revenue?

You can guess. If your top-converting page is /services/data-engineering and GSC shows that page ranks for "data engineering consulting," you can infer the connection. But inference breaks down when:

• A page ranks for 50 queries and you don't know which one drove the conversion
• A user lands on one page and converts on another
• Multiple sessions from the same user span different queries

The join has to happen in a warehouse.

The architecture

Google Search Console ──→ BigQuery (via GSC API / Fivetran / BigQuery Data Transfer)
GA4 ──→ BigQuery (native export)
CRM ──→ BigQuery (via Fivetran)
         │
         ▼
      dbt models
         │
         ▼
   Sigma / Looker

Staging: GSC data

-- models/staging/stg_gsc__search_performance.sql
SELECT
    data_date                   AS search_date,
    query,
    page                        AS landing_page_url,
    country,
    device                      AS device_type,
    impressions,
    clicks,
    SAFE_DIVIDE(clicks, impressions) AS ctr,
    position                    AS avg_position
FROM {{ source('gsc', 'search_appearance') }}
WHERE search_type = 'web'

Staging: GA4 sessions with conversions

-- models/staging/stg_ga4__organic_sessions.sql
SELECT
    session_id,
    user_pseudo_id,
    session_start,
    landing_page_url,
    utm_source,
    utm_medium,
    has_conversion,
    conversion_page
FROM {{ ref('int_ga4__sessions') }}
WHERE utm_source = 'google'
  AND utm_medium = 'organic'

The join: queries → pages → conversions

-- models/marts/mart_search_to_conversion.sql
SELECT
    gsc.query,
    gsc.landing_page_url,
    SUM(gsc.impressions)                AS impressions,
    SUM(gsc.clicks)                     AS clicks,
    SAFE_DIVIDE(SUM(gsc.clicks), SUM(gsc.impressions)) AS ctr,
    AVG(gsc.avg_position)               AS avg_position,
    COUNT(DISTINCT ga.session_id)       AS organic_sessions,
    COUNT(DISTINCT CASE WHEN ga.has_conversion THEN ga.session_id END) AS converting_sessions,
    SAFE_DIVIDE(
        COUNT(DISTINCT CASE WHEN ga.has_conversion THEN ga.session_id END),
        COUNT(DISTINCT ga.session_id)
    ) AS conversion_rate
FROM {{ ref('stg_gsc__search_performance') }} gsc
LEFT JOIN {{ ref('stg_ga4__organic_sessions') }} ga
    ON gsc.landing_page_url = ga.landing_page_url
    AND gsc.search_date = DATE(ga.session_start)
GROUP BY 1, 2

Now you can rank queries not just by clicks, but by conversions. The queries with the highest impressions aren't always the ones generating leads.

Four patterns this unlocks

1. High-impression, low-CTR queries (title rewrite candidates)

SELECT query, landing_page_url, impressions, ctr, avg_position
FROM {{ ref('mart_search_to_conversion') }}
WHERE impressions > 100
  AND ctr < 0.02
  AND avg_position < 15
ORDER BY impressions DESC

These are queries where you're showing up but nobody's clicking. Usually a title or meta description problem. A 15-minute rewrite can 3-5x clicks.

2. High-converting queries (protect and invest)

SELECT query, impressions, clicks, converting_sessions, conversion_rate
FROM {{ ref('mart_search_to_conversion') }}
WHERE converting_sessions > 0
ORDER BY conversion_rate DESC

These are your money queries. They might have low volume, but the people who search them convert. Write more content around these topics. Build topic clusters. Don't let a competitor outrank you.

3. Content gaps (high impressions, no page)

SELECT query, SUM(impressions) AS total_impressions, AVG(avg_position) AS position
FROM {{ ref('stg_gsc__search_performance') }}
WHERE avg_position > 20  -- you're barely ranking
  AND impressions > 50
GROUP BY 1
ORDER BY total_impressions DESC

Queries where Google is showing your site in results but you don't have a dedicated page for the topic. Each one is a blog post waiting to be written.

4. Decaying queries (position dropping over time)

SELECT
    query,
    DATE_TRUNC(search_date, WEEK) AS week,
    AVG(avg_position) AS avg_position,
    SUM(clicks) AS clicks
FROM {{ ref('stg_gsc__search_performance') }}
WHERE query IN (SELECT query FROM {{ ref('mart_search_to_conversion') }} WHERE converting_sessions > 0)
GROUP BY 1, 2
ORDER BY 1, 2

When a converting query starts losing position, you want to know immediately — not three months later when traffic has halved.

Why this has to be a warehouse job

Both GSC and GA4 have UIs. Both have APIs. You could theoretically do this analysis in a spreadsheet. So why bother with a warehouse?

Three reasons:

1. The join is non-trivial. GSC data is at the query + page + date grain. GA4 data is at the session + event grain. Matching them requires date alignment, URL normalization, and aggregation rules that change as your questions get more specific. A spreadsheet falls apart. A dbt model handles it cleanly.

2. History matters. GSC's UI shows 16 months of data. The BigQuery export keeps everything. Position trends, seasonal patterns, year-over-year comparisons — you need the full history.

3. It compounds. Once the model exists, every future analysis builds on it. Add CRM data and you get revenue per query. Add ad spend and you get blended CAC by keyword. The first join is the hard one; everything after it is incremental.

The payoff

Most companies treat SEO as a traffic game — more impressions, more clicks, better rankings. That's necessary but insufficient. The real question is: which of those clicks become customers?

Search Console tells you what you rank for. GA4 tells you what converts. Your warehouse tells you which rankings are worth fighting for.

We build GSC + GA4 joined models in dbt for companies that need to know which search queries actually generate revenue. If your SEO reporting stops at "clicks," book a discovery call and we'll show you what the full funnel looks like.