BigQuery：Legacy SQL转Standard SQL后GA跳出率结果不一致排查

Let's break down the issues in your Standard SQL conversion and fix them to match the Legacy SQL logic exactly:

Key Issues in Your Current Standard SQL Query #

1. Incorrect FIRST → FIRST_VALUE Mapping
   Legacy SQL's FIRST(hits.isInteraction) operates on rows grouped by source, medium, session_hitsType, session_sessionId, picking the first value in that grouped set. Your current Standard SQL applies FIRST_VALUE at the un-nested hit level before grouping, which changes the scope of what "first" refers to.

2. Redundant DISTINCT in APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT already handles deduplication—adding DISTINCT inside the function is unnecessary and can skew results.

3. Broken totals.pageviews Aggregation
   When you un-nest hits, each row represents a single hit, but totals.pageviews is a session-level metric. Summing it directly over un-nested rows will multiply the value by the number of hits per session, which doesn't match the Legacy SQL behavior.

Corrected Standard SQL Query #

This query preserves the exact grouping and aggregation logic of your Legacy SQL code:

WITH session_hit_groups AS (
  -- Match Legacy SQL's first GROUP BY: source, medium, session_hitsType, session_sessionId
  SELECT
    source,
    medium,
    session_sessionId,
    session_hitsType,
    SUM(session_pageviews) AS session_pageViews_sum,
    -- Get first isInteraction in the grouped set (ordered by hit time to match Legacy's implicit order)
    FIRST_VALUE(session_isInteraction) OVER(
      PARTITION BY source, medium, session_sessionId, session_hitsType
      ORDER BY hit_time
    ) AS session_isInteraction_first
  FROM (
    SELECT
      trafficSource.source AS source,
      trafficSource.medium AS medium,
      sessionId AS session_sessionId,
      hits.type AS session_hitsType,
      totals.pageviews AS session_pageviews,
      hits.isInteraction AS session_isInteraction,
      TIMESTAMP_SECONDS(hits.time) AS hit_time
    FROM `test.Test.session_streaming_*`,
         UNNEST(hits) hits
    WHERE _table_suffix BETWEEN '20180401' AND '20180430'
  )
  GROUP BY source, medium, session_sessionId, session_hitsType, session_isInteraction, hit_time
),
session_aggregates AS (
  -- Match Legacy SQL's second GROUP BY: source, medium, session_sessionId
  SELECT
    source,
    medium,
    session_sessionId,
    SUM(IF(session_hitsType = 'event' AND session_isInteraction_first = 1, 1, 0)) AS session_isEventInteraction_sum,
    SUM(session_pageViews_sum) AS session_pageViews_sum_sum
  FROM session_hit_groups
  GROUP BY source, medium, session_sessionId
),
bounce_session_counts AS (
  -- Count bounce sessions (no interaction events + 1 pageview)
  SELECT
    source,
    medium,
    APPROX_COUNT_DISTINCT(session_sessionId) AS session_bounceSessionCount
  FROM session_aggregates
  WHERE session_isEventInteraction_sum = 0
    AND session_pageViews_sum_sum = 1
  GROUP BY source, medium
),
total_session_counts AS (
  -- Count total sessions per source/medium
  SELECT
    trafficSource.source AS source,
    trafficSource.medium AS medium,
    APPROX_COUNT_DISTINCT(sessionId) AS total_session,
    APPROX_COUNT_DISTINCT(sessionId) AS session_sessionId_distinct_count
  FROM `test.Test.session_streaming_*`
  WHERE _table_suffix BETWEEN '20180401' AND '20180430'
  GROUP BY source, medium
)
-- Calculate bounce rate and filter for target medium
SELECT
  IFNULL(bs.session_bounceSessionCount, 0) / ts.session_sessionId_distinct_count AS session_bounceRate,
  bs.source,
  bs.medium,
  ts.total_session
FROM bounce_session_counts bs
RIGHT JOIN total_session_counts ts
  ON bs.source = ts.source
  AND bs.medium = ts.medium
WHERE ts.medium = 'zadv_display'
ORDER BY ts.total_session DESC;

Critical Fixes Explained #

• Accurate FIRST Replacement: We apply FIRST_VALUE within the same grouping as Legacy SQL, ordered by hit time to replicate the implicit row order Legacy uses for FIRST.
• Fixed Pageview Aggregation: We sum totals.pageviews only after grouping by session and hit type, ensuring we don't overcount the session-level metric.
• Removed Redundant DISTINCT: APPROX_COUNT_DISTINCT(session_sessionId) correctly matches Legacy's COUNT(DISTINCT session_sessionId, 10000000) approximate deduplication.
• Right Join for Coverage: Using RIGHT JOIN ensures we include all source/medium combinations from total sessions, even if they have no bounce sessions (matching Legacy's IFNULL handling).

To validate, test with a small subset of session IDs to confirm both queries flag the same sessions as bounces.

内容的提问来源于stack exchange，提问作者Mayank