基于BigQuery的Google Analytics数据SQL查询扩展需求

Got it, let's tackle your requirements: we need to correctly count event1 and event2 for sessions without a url_target (like session 2), and flag sessions where the target page was visited before events (like session 3) as a special case (target=2) as requested.

Here's the full BigQuery SQL that will produce your expected results:

WITH session_target_time AS (
  -- Grab the timestamp of the url_target visit for each session that has one
  SELECT
    session_id,
    hit_timestamp AS target_time
  FROM
    `your-project.your-dataset.your-table`
  WHERE
    url = 'url_target'
),
session_events_with_target AS (
  -- Join each event with its session's target time (if exists)
  SELECT
    e.session_id,
    e.event_category,
    e.hit_timestamp,
    t.target_time
  FROM
    `your-project.your-dataset.your-table` e
  LEFT JOIN
    session_target_time t
  ON
    e.session_id = t.session_id
)
SELECT
  session_id,
  -- Count events: if there's a target time, only count events BEFORE the target; else count all events
  COUNTIF(event_category = 'event1' AND (target_time IS NULL OR hit_timestamp < target_time)) AS event1,
  COUNTIF(event_category = 'event2' AND (target_time IS NULL OR hit_timestamp < target_time)) AS event2,
  -- Determine target status:
  CASE
    WHEN target_time IS NULL THEN 0  -- No target page visited
    -- Check if any event happened AFTER the target page visit
    WHEN EXISTS (
      SELECT 1 
      FROM session_events_with_target sub
      WHERE sub.session_id = main.session_id
        AND sub.event_category IN ('event1','event2')
        AND sub.hit_timestamp > sub.target_time
    ) THEN 2  -- Special case: target visited before events
    ELSE 1  -- Normal case: events happened before target
  END AS target
FROM
  session_events_with_target main
GROUP BY
  session_id, target_time
ORDER BY
  session_id

How this works: #

1. session_target_time CTE: Isolates the timestamp of the url_target visit for each session that has one. This lets us compare event times against the target visit time later.
2. session_events_with_target CTE: Joins every event record with its session's target time (if the session visited the target page). This gives us a unified view of events and their relative timing to the target.
3. Final Aggregation:
   • Event Counting: For sessions without a target, we count all events (fixing session 2's incorrect 0 values). For sessions with a target, we only count events that happened before the target page was visited (which aligns with your expected result for session 3, where the event came after the target and isn't counted).
   • Target Status:
     • 0 = No target page visited (like session 2)
     • 1 = Target page visited after all events (normal conversion, like sessions 1 and 4)
     • 2 = Target page visited before at least one event (special case, like session 3)

Simplified Version (if target=2 isn't needed) #

If you don't need to flag the special case and just want to fix the event counting for non-target sessions, use this shorter query:

SELECT
  session_id,
  COUNTIF(event_category = 'event1') AS event1,
  COUNTIF(event_category = 'event2') AS event2,
  MAX(IF(url = 'url_target', 1, 0)) AS target
FROM
  `your-project.your-dataset.your-table`
GROUP BY
  session_id
ORDER BY
  session_id

This will still fix session 2's counting issue, but session 3 will show target=1 instead of 2.

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