• [GA4] Automatically collected events – Firebase Help (google.com)

screen_view (event_params)

• firebase_screen
• firebase_screen_class
• firebase_screen_id (int_value)
• firebase_previous_screen
• firebase_previous_class
• firebase_previous_id (int_value)
• engagement_time_msec

• ga_session_id (int_value)
• ga_session_number (int_value)

SELECT
  user_pseudo_id,
  (SELECT value.int_value    FROM UNNEST(event_params) WHERE key = 'ga_session_id')            AS ga_session_id,
  (SELECT value.int_value    FROM UNNEST(event_params) WHERE key = 'firebase_previous_id')     AS firebase_previous_id,
  (SELECT value.int_value    FROM UNNEST(event_params) WHERE key = 'firebase_screen_id')       AS firebase_screen_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_previous_screen') AS firebase_previous_screen,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_screen')          AS firebase_screen,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_previous_class')  AS firebase_previous_class,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_screen_class')    AS firebase_screen_class,
FROM
  `my_project.my_dataset.events_*`
WHERE
  event_name = 'screen_view'
  AND _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'
ORDER BY 
  ga_session_id,
  event_timestamp

firebase_previous_screen และ firebase_screen

แสดงทุก screen , แสดงคู่ firebase_previous_screen และ firebase_screen

SELECT
  firebase_previous_screen,
  firebase_screen,
  COUNT(*) AS cnt
FROM 
(
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_previous_screen') AS firebase_previous_screen,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_screen') AS firebase_screen,
  FROM
    `my_project.my_dataset.events_*`
  WHERE
    event_name = 'screen_view'
    AND _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'
)
GROUP BY 
  firebase_previous_screen,
  firebase_screen
ORDER BY 
  firebase_screen

+--------------------------+-----------------+-----+
| firebase_previous_screen | firebase_screen | cnt |
+--------------------------+-----------------+-----+
| ScreenA                  | ScreenB         | 10  |
| ScreenB                  | ScreenC         | 5   |
| ScreenD                  | ScreenE         | 1   |
+--------------------------+-----------------+-----+

เลือกเฉพาะ screen จาก session ที่มี screen ที่กำหนดเท่านั้น (เช่น ใน session นั้นๆมี ‘ScreenA’ หรือ ‘ScreenB’ ก็แสดงทุก screen ใน session นั้น)

WITH table_screen_view AS
(
  SELECT
    user_pseudo_id,
    event_timestamp,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_previous_screen') AS firebase_previous_screen,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_screen') AS firebase_screen,
  FROM
    `my_project.my_dataset.events_*`
  WHERE
    event_name = 'screen_view'
    AND _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'
)
SELECT 
  firebase_previous_screen,
  firebase_screen,
  COUNT(*) AS cnt,
FROM table_screen_view
WHERE 
  ga_session_id IN ( SELECT ga_session_id 
                     FROM table_screen_view 
                     WHERE firebase_screen IN ('ScreenA', 'ScreenB')
                   )
GROUP BY
  firebase_previous_screen,
  firebase_screen
ORDER BY 
  firebase_screen

+--------------------------+-----------------+-----+
| firebase_previous_screen | firebase_screen | cnt |
+--------------------------+-----------------+-----+
| ScreenA                  | ScreenB         | 10  |
| ScreenB                  | ScreenC         | 5   |
+--------------------------+-----------------+-----+

firebase_screen

แสดงทุก screen (firebase_screen) ใน session เดียวกัน โดยเรียงจาก event_timestamp

WITH sequence_screen AS
(
  SELECT 
    ga_session_id,
    firebase_screen,
    concat('stage', cast(stage_rank as string)) AS stage_rank,
  FROM
  (
    SELECT 
      ga_session_id,
      firebase_screen,
      RANK() OVER (PARTITION BY ga_session_id ORDER BY event_timestamp ASC) AS stage_rank,
    FROM
    (
      SELECT
        event_timestamp,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'firebase_screen') AS firebase_screen,
      FROM
        `my_project.my_dataset.events_*`
      WHERE
        event_name = 'screen_view'
        AND _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'
    )
  )  
)
SELECT * FROM sequence_screen 
PIVOT(any_value(firebase_screen) FOR stage_rank IN ('stage1', 'stage2', 'stage3', 'stage4', 'stage5'))
ORDER BY ga_session_id

+-------------------------+---------+---------+---------+---------+
| ga_session_id | stage1  | stage2  | stage3  | stage4  | stage5  |
+-------------------------+---------+---------+---------+---------+
| 1234567891    | ScreenA | ScreenB | ScreenC | ScreenD | ScreenE |
| 1234567892    | ScreenA | ScreenC | ScreenD | NULL    | NULL    |
+-------------------------+---------+---------+---------+---------+