GA4

【GA4 SQL】ユーザーエクスプローラーをBigQueryで再現

2024.09.27 2024.10.25

GA4のデータ探索ツールには個々のユーザー行動を追える「ユーザーエクスプローラー」機能があるが、BigQueryでクエリを作成している際にこのような「ユーザーエクスプローラー」のようなもので確認したいケースが度々ある。確認したい指標は必要最低限でよく、データビジュアライズで魅せるためのものではないのでLooker Studioと連携する必要もなく、都度必要であればカスタマイズしてSQLの検証用でなどに活用していくことを想定している。

参考：[GA4] ユーザーエクスプローラ -アナリティクスヘルプ

ユーザーエクスプローラーを再現するクエリ

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20240630');

WITH
  UserExplorer AS (
    SELECT
      ymd,
      event_timestamp_jst,
      event_timestamp,
      ga_session_number,
      user_pseudo_id,
      ssid,
      event_name,
      --page_location,
      REGEXP_EXTRACT(page_location, r'https?://[^/]+(.*)') AS path,
      page_referrer,
      _engagement_time_seconds,
      batch_page_id,
      session_engaged
    FROM (
      SELECT
        PARSE_DATE("%Y%m%d", event_date) AS ymd,
        FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP_MICROS(event_timestamp), "Asia/Tokyo") AS event_timestamp_jst,
        event_timestamp,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number,
        user_pseudo_id,
        CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
        event_name,
        REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS page_referrer,        
        FLOOR(SAFE_DIVIDE(COALESCE((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec'), 0), 1000)) AS _engagement_time_seconds,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'batch_page_id') AS batch_page_id,
        (SELECT COALESCE(e.value.string_value, SAFE_CAST(e.value.int_value AS STRING)) FROM UNNEST(event_params) e WHERE e.key = 'session_engaged') AS session_engaged 
      FROM
        `<project>.<dataset>.events_*`
      WHERE
          --_TABLE_SUFFIX BETWEEN date_from() AND date_to()
          _TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
        --AND
          --REGEXP_CONTAINS(event_name, r'first_visit|user_engagement|session_start|page_view|scroll|click')
      ORDER BY
        ssid ASC,
        event_timestamp ASC
    )
  )

SELECT
  *
FROM
  UserExplorer
WHERE
  REGEXP_CONTAINS(user_pseudo_id, r'xxxx.xxxx')
    --REGEXP_CONTAINS(ssid, r'xxxx.xxxx-xxxx')
  --AND
    --event_name = 'page_view'