【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を開き、画面左下にある「管理」から「カスタム定義」を開き、「カスタムディメンション」で定義したイベント名が一覧で見ることができます。
コンバージョンのパラメータ名は「ユーザプロパティ/パラメータ」に記載されているものになります。