【GA4】特定のユーザの行動ログを取得するクエリ

Google Analytics 4をBigQuery接続している場合

例えば、コンバージョンしたユーザが、各々どのような経路をたどってwebサイトを閲覧していたか知りたい時は以下のように書く。

WITH t1 AS(
   SELECT 
      user_pseudo_id, --ユーザ識別ID
      DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS date_time, --サイトへのアクセス時間
      event_name, --イベント名
      REGEXP_EXTRACT((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"[^\?]+") AS page, -- ページURL
      CONCAT((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'), '-', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number')) AS session_count, -- セッション
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_url') as link_url, --リンクURL
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') as page_title, --ページタイトル
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_reffer') as page_reffer, --ページ参照元
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') as medium, --メディア
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') as campaign, --キャンペーン
      geo.country as country,
      geo.region as resion,
      geo.city as city
       FROM `{プロジェクト名}.{テーブル名}.events_*` 
   WHERE _TABLE_SUFFIX BETWEEN '20220825' AND '20221010'
),

-- CVのあったユーザのID配列
t2 AS(
   SELECT ARRAY(SELECT DISTINCT user_pseudo_id
                        FROM t1
                       WHERE event_name = '{パラメータ}'
                        ) as users
)

-- 必要なデータからCVした人のログを取り出す
SELECT 
   user_pseudo_id,
   date_time,
   event_name,
   page,
   session_count,
   ROW_NUMBER() OVER (PARTITION BY session_count ORDER BY date_time) AS hit_count,
   link_url,
   page_title,
   page_reffer,
   medium,
   campaign,
   country,
   resion,
   city
FROM t1
WHERE user_pseudo_id IN UNNEST((SELECT users from t2))
ORDER BY user_pseudo_id, date_time, session_count, hit_count

使いたい時はプロジェクト名、テーブル名、パラメータ名、日付の期間を編集してください。

パラメータ名の調べ方

Google Analytics4を開き、画面左下にある「管理」から「カスタム定義」を開き、「カスタムディメンション」で定義したイベント名が一覧で見ることができます。

コンバージョンのパラメータ名は「ユーザプロパティ/パラメータ」に記載されているものになります。