BigQuery中CASE语句统计会话数与GA不符的原因咨询

Hey there! Let's figure out why your CASE statement approach is giving results that don't match Google Analytics (GA), while your colleague's subquery + JOIN method hits the mark. The key here boils down to how we count unique sessions vs. counting individual events—and GA’s definition of a "session with the event."

Why Your CASE Statement Might Be Off #

Chances are your original CASE query looked something like this:

SELECT
  COUNT(CASE WHEN hits.eventInfo.eventCategory = 'view' AND hits.eventInfo.eventAction = 'basket' THEN 1 END) AS session_count
FROM
  `project.dataset.ga_sessions_*`,
  UNNEST(hits) AS hits

The problem here is that if a single session has multiple hits matching your event criteria, this query counts each matching hit as a separate session. GA, however, counts a session only once if it contains at least one instance of the event—even if the event fires 5 times in that session. Your original query was essentially counting event occurrences, not unique sessions that include the event.

Why Your Colleague’s Approach Works #

Your colleague’s subquery + JOIN method isolates unique sessions first, then counts them—aligning with GA’s logic. Here’s a simplified version of what they might have written:

SELECT
  COUNT(DISTINCT s.fullVisitorId || '.' || s.visitId) AS session_count
FROM
  `project.dataset.ga_sessions_*` s
INNER JOIN (
  -- First get all unique sessions that have the target event
  SELECT
    fullVisitorId,
    visitId
  FROM
    `project.dataset.ga_sessions_*`,
    UNNEST(hits) AS hits
  WHERE
    hits.eventInfo.eventCategory = 'view' AND hits.eventInfo.eventAction = 'basket'
  GROUP BY
    fullVisitorId,
    visitId
) matching_sessions 
ON s.fullVisitorId = matching_sessions.fullVisitorId 
AND s.visitId = matching_sessions.visitId

The subquery first pulls every unique session (fullVisitorId + visitId is GA’s unique session key) that has at least one matching hit. Joining back to the main table (or just counting distinct sessions directly in the subquery) ensures each session is only counted once—exactly how GA tallies these metrics.

Fixing Your CASE Statement #

You don’t have to abandon CASE entirely! Adjust it to check for the presence of the event per session first, then count those sessions. Here’s how:

SELECT
  COUNT(has_basket_view) AS session_count
FROM (
  SELECT
    fullVisitorId,
    visitId,
    -- Mark the session as having the event if any hit matches
    CASE WHEN MAX(CASE 
      WHEN hits.eventInfo.eventCategory = 'view' AND hits.eventInfo.eventAction = 'basket' 
      THEN 1 
      ELSE 0 
    END) = 1 THEN 1 END AS has_basket_view
  FROM
    `project.dataset.ga_sessions_*`,
    UNNEST(hits) AS hits
  GROUP BY
    fullVisitorId,
    visitId
) session_level_data

The inner query groups by session, using MAX(CASE...) to check if any hit in the session matches your criteria (returns 1 if yes, 0 otherwise). The outer query then counts all sessions where has_basket_view is 1—this gives you the same unique session count as GA.

Quick Recap #

• GA counts unique sessions that contain at least one instance of the event, not the number of event occurrences.
• Your original CASE query counted event hits, not unique sessions.
• Both the subquery+JOIN approach and the adjusted CASE+GROUP BY approach fix this by focusing on session-level uniqueness.

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