Querying AI Referrals in GA4 BigQuery Without Double Counting

Google Analytics 4 (GA4) records website activity as events, such as page views and form submissions, and its BigQuery export stores those events in rows. One visit can produce many rows. Counting event rows as visits inflates the total. Adding AI referrals to a broader referral count can also count the same visit twice.

The reliable approach is to reduce GA4 events to one row per session before counting or joining other data. A session is a group of events from one visit. Use GA4’s session-scoped source and medium fields to classify it, then keep an AI-referral flag as a subset or assign the session to exactly one channel. This identifies visits with observable AI-related source or referrer data; it does not measure every visit influenced by an AI answer.

Use session fields and event parameters for detection

For reports about visits, start with session_traffic_source_last_click.manual_campaign.source and .medium. GA4 uses these fields to attribute the visit's source (where it came from) and medium (its traffic type) to the session. The separate traffic_source.source field shows the source that first brought the user to the site. It does not show which source brought in a later session. Google documents these distinctions in its BigQuery traffic attribution guidance.

Two other sets of fields can help you investigate a session's traffic source. collected_traffic_source.manual_source and .manual_medium contain source and medium values collected with an event. The event_params array can contain ga_session_id, page_location, and page_referrer. Use page_referrer to inspect the referring URL, especially on the session’s first page view. It is a diagnostic signal: it does not guarantee that GA4 recorded a complete referral path. The GA4 export schema documents these fields and the repeated structure of event_params.

There is no guaranteed, complete list of hostnames for AI products. Build an allowlist from the source values in your GA4 property. Possible entries include chatgpt.com, perplexity.ai, gemini.google.com, and claude.ai, but check which values appear in your data and update the list to match. Do not count a broad source such as bing.com on its own, because it also represents ordinary search traffic. Examples from practitioners can help you start an investigation, but they are not an official or complete list of AI referrers.

Build one row per session

The query below reads event parameters without using CROSS JOIN UNNEST, which would create extra rows from the event parameters. It then groups events by user_pseudo_id, ga_session_id, and stream_id. Google’s documented session-counting key combines user_pseudo_id and ga_session_id. Including stream_id helps keep keys distinct when you combine data streams. See Google’s session-counting guidance.

Replace the project, dataset, date range, and hostname list. The example uses daily export tables and keeps the original source and medium fields for auditing.

WITH event_rows AS (
  SELECT
    stream_id,
    user_pseudo_id,
    event_timestamp,
    event_name,
    PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS event_date,

    (
      SELECT ep.value.int_value
      FROM UNNEST(event_params) AS ep
      WHERE ep.key = 'ga_session_id'
      LIMIT 1
    ) AS ga_session_id,

    (
      SELECT ep.value.string_value
      FROM UNNEST(event_params) AS ep
      WHERE ep.key = 'page_location'
      LIMIT 1
    ) AS page_location,

    (
      SELECT ep.value.string_value
      FROM UNNEST(event_params) AS ep
      WHERE ep.key = 'page_referrer'
      LIMIT 1
    ) AS page_referrer,

    session_traffic_source_last_click.manual_campaign.source
      AS session_source,
    session_traffic_source_last_click.manual_campaign.medium
      AS session_medium,

    collected_traffic_source.manual_source AS collected_source,
    collected_traffic_source.manual_medium AS collected_medium

  FROM <code>your_project.analytics_123456789.events_*
  WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, r'^\d{8}$')
    AND _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
),

sessions AS (
  SELECT
    stream_id,
    user_pseudo_id,
    ga_session_id,
    MIN(event_date) AS session_date,

    ARRAY_AGG(
      IF(
        session_source IS NOT NULL OR session_medium IS NOT NULL,
        STRUCT(session_source, session_medium),
        NULL
      )
      IGNORE NULLS
      ORDER BY event_timestamp
      LIMIT 1
    )[SAFE_OFFSET(0)] AS attribution,

    ARRAY_AGG(
      collected_source IGNORE NULLS
      ORDER BY event_timestamp
      LIMIT 1
    )[SAFE_OFFSET(0)] AS first_collected_source,

    ARRAY_AGG(
      collected_medium IGNORE NULLS
      ORDER BY event_timestamp
      LIMIT 1
    )[SAFE_OFFSET(0)] AS first_collected_medium,

    ARRAY_AGG(
      IF(
        event_name = 'page_view' AND page_location IS NOT NULL,
        STRUCT(page_location, page_referrer),
        NULL
      )
      IGNORE NULLS
      ORDER BY event_timestamp
      LIMIT 1
    )[SAFE_OFFSET(0)] AS landing

  FROM event_rows
  WHERE user_pseudo_id IS NOT NULL
    AND ga_session_id IS NOT NULL
  GROUP BY stream_id, user_pseudo_id, ga_session_id
),

session_signals AS (
  SELECT
    *,
    LOWER(NET.HOST(landing.page_referrer)) AS landing_referrer_host,

    LOWER(
      NET.HOST(
        CASE
          WHEN REGEXP_CONTAINS(
            TRIM(attribution.session_source),
            r'(?i)^https?://'
          )
            THEN TRIM(attribution.session_source)
          WHEN attribution.session_source IS NOT NULL
            THEN CONCAT('https://', TRIM(attribution.session_source))
          ELSE NULL
        END
      )
    ) AS session_source_host,

    LOWER(
      NET.HOST(
        CASE
          WHEN REGEXP_CONTAINS(
            TRIM(first_collected_source),
            r'(?i)^https?://'
          )
            THEN TRIM(first_collected_source)
          WHEN first_collected_source IS NOT NULL
            THEN CONCAT('https://', TRIM(first_collected_source))
          ELSE NULL
        END
      )
    ) AS collected_source_host

  FROM sessions
),

classified AS (
  SELECT
    *,
    (
      session_source_host IN (
        'chatgpt.com',
        'chat.openai.com',
        'perplexity.ai',
        'gemini.google.com',
        'claude.ai',
        'copilot.microsoft.com'
      )
      OR collected_source_host IN (
        'chatgpt.com',
        'chat.openai.com',
        'perplexity.ai',
        'gemini.google.com',
        'claude.ai',
        'copilot.microsoft.com'
      )
      OR landing_referrer_host IN (
        'chatgpt.com',
        'chat.openai.com',
        'perplexity.ai',
        'gemini.google.com',
        'claude.ai',
        'copilot.microsoft.com'
      )
    ) AS is_identifiable_ai_referral

  FROM session_signals
)

SELECT
  session_date,
  stream_id,
  user_pseudo_id,
  ga_session_id,
  landing.page_location AS landing_page,
  landing.page_referrer AS landing_referrer,
  landing_referrer_host,
  attribution.session_source AS session_source,
  attribution.session_medium AS session_medium,
  first_collected_source,
  first_collected_medium,
  is_identifiable_ai_referral
FROM classified;

NET.HOST parses a hostname from a URL. The query adds a scheme to source values that lack one so it can parse hostname-like values such as chatgpt.com. Inspect the actual source values in your export before relying on this conversion. If a source field contains labels or other text instead of a hostname, adjust the detection rule to match the observed format.

For each session, the query groups all events into one row and takes the landing page and referrer from the earliest page view with a location. This prevents multiple events from inflating session counts or a later page's referrer from being attributed to the first page. If events share a timestamp and the choice matters, add an ordering field such as batch_event_index after confirming that the export schema includes it.

The query sets session_date to the earliest event date included for each session. If a session crosses midnight or the date range starts partway through a session, decide which date to assign and document that rule. The query excludes rows without a user or session key from session counts. Report those rows separately as unkeyed events instead of making up session IDs. GA4 daily export tables can receive late-arriving events for up to three days. Account for those events when finalizing recent dates, as described in the export documentation.

Keep AI referrals from overlapping other channels

An AI flag answers whether a session has an identifiable AI signal. It does not make that session an extra visit. Count the flag as a subset of all sessions:

SELECT
  COUNT(*) AS all_sessions,
  COUNTIF(is_identifiable_ai_referral) AS identifiable_ai_sessions
FROM <code>your_project.reporting.ga4_sessions;

This count assumes the input table contains one row per session. Do not run COUNT(*) over event rows to count visits.

For a channel report whose categories must sum to the session total, assign each session once with an ordered CASE. Put the AI condition before a generic referral condition, and retain source and medium for audit:

SELECT
  CASE
    WHEN is_identifiable_ai_referral THEN 'AI referral'
    WHEN session_medium = 'referral' THEN 'Other referral'
    WHEN session_medium = 'organic' THEN 'Organic'
    WHEN session_medium = 'cpc' THEN 'Paid search'
    WHEN session_source IS NULL THEN 'Unassigned / unknown'
    ELSE 'Other'
  END AS reporting_channel,
  COUNT(*) AS sessions
FROM <code>your_project.reporting.ga4_sessions
GROUP BY reporting_channel;

Here, “AI referral” replaces the generic referral category for sessions that match the AI rule. If you report AI sessions as a separate KPI instead, label them as a subset and do not add them to channel totals.

Join CRM data only after choosing a grain

A grain is what one row represents, such as one session, one lead, or one opportunity. A session can contain many events, and a CRM lead can have multiple updates or associated activities. Joining those raw rows on a repeated key multiplies records and inflates counts. BigQuery’s query optimization guidance recommends reducing data before joining when that prevents unnecessary row growth.

To join GA4 lead events to CRM leads, use a shared, stable key, such as a lead_id recorded on the GA4 lead event and stored in the CRM. The example assumes both datasets have that key. It assigns each lead to its earliest captured lead event and selects the CRM row with the latest update. Use a different event or rule if your reporting question requires a different attribution definition.

WITH ga_lead_events AS (
  SELECT
    (
      SELECT ep.value.string_value
      FROM UNNEST(event_params) AS ep
      WHERE ep.key = 'lead_id'
      LIMIT 1
    ) AS lead_id,

    user_pseudo_id,

    (
      SELECT ep.value.int_value
      FROM UNNEST(event_params) AS ep
      WHERE ep.key = 'ga_session_id'
      LIMIT 1
    ) AS ga_session_id,

    session_traffic_source_last_click.manual_campaign.source
      AS session_source,
    session_traffic_source_last_click.manual_campaign.medium
      AS session_medium,
    event_timestamp

  FROM <code>your_project.analytics_123456789.events_*
  WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, r'^\d{8}$')
    AND _TABLE_SUFFIX BETWEEN '20261001' AND '20261031'
    AND event_name = 'generate_lead'
),

one_ga_row_per_lead AS (
  SELECT *
  FROM ga_lead_events
  WHERE lead_id IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY lead_id
    ORDER BY event_timestamp
  ) = 1
),

one_crm_row_per_lead AS (
  SELECT *
  FROM your_project.crm.leads
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY lead_id
    ORDER BY updated_at DESC
  ) = 1
)

SELECT
  c.lead_id,
  c.lead_status,
  c.created_at,
  g.user_pseudo_id,
  g.ga_session_id,
  g.session_source,
  g.session_medium
FROM one_crm_row_per_lead AS c
LEFT JOIN one_ga_row_per_lead AS g
  USING (lead_id);

Before joining, check that each dataset has one row per join key and compare the row counts before and after the join. If the CRM table contains opportunities or activities instead of snapshots, group those records to the reporting grain rather than discarding them with ROW_NUMBER(). For revenue reports, decide whether each row represents a lead or an opportunity. Sum revenue only after shaping the CRM data so each row represents that chosen unit. BigQuery’s query insights can help diagnose joins that produce unexpectedly many rows.

Without a shared identifier captured by both systems, GA4 does not provide a universal way to connect an anonymous session to a CRM person. Do not treat an email or CRM person record as a GA4 key unless your implementation has a documented identity-linkage design.

Compare citation exports at a shared grain

A citation export records that a page appeared as a citation. GA4 records activity on the measured website. A citation observation alone does not identify a GA4 session or prove that the citation caused a visit.

If the datasets have no shared click or session ID, first group both by the same reporting unit, such as date and normalized URL, then compare them. The comparison shows page-level patterns over time. It does not attribute visits to individual people.

WITH ga_page_day AS (
  SELECT
    session_date AS report_date,
    normalized_landing_url AS page_url,
    COUNT(*) AS identifiable_ai_sessions
  FROM <code>your_project.reporting.ga4_ai_sessions
  WHERE is_identifiable_ai_referral
  GROUP BY report_date, page_url
),

citation_page_day AS (
  SELECT
    observation_date AS report_date,
    normalized_cited_url AS page_url,
    COUNT(DISTINCT citation_id) AS citation_observations
  FROM your_project.citations.export
  GROUP BY report_date, page_url
)

SELECT
  COALESCE(g.report_date, c.report_date) AS report_date,
  COALESCE(g.page_url, c.page_url) AS page_url,
  g.identifiable_ai_sessions,
  c.citation_observations
FROM ga_page_day AS g
FULL OUTER JOIN citation_page_day AS c
  USING (report_date, page_url);

Replace the example table and field names with those in your export, and apply the same URL-normalization rules on both sides. If the citation data contains multiple records per page, date, or platform, aggregate it to the intended join grain first. Include platform in the comparison only when GA4 has a comparable observed source classification; otherwise, a page-level citation count cannot be assigned to individual AI sessions.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com