GA4×BigQueryで特定ユーザーの行動を把握するSQLクエリ

Webサイトに訪れたユーザーは “user_pseudo_id” という特定ユーザーを一意に識別するための値としてGA4で自動的に集計されます。

この “user_pseudo_id” をもとにBigQueryで特定ユーザーの行動を把握することができると、Webサイト運営における「改善」や「勝ちパターン」を見出すことにつながるかもです。（CVしたユーザーの行動把握など）

本記事では、それを実現するためのSQLクエリを紹介していきます。

本記事の筆者

国立理系修士2年（24歳 | 専攻は機械学習）
SEOマーケティング会社で実務を2年ほど経験
ブログ運営3年目（月間10,000PVほど）
2025年4月からIT事業会社のデータサイエンティストとして働く予定で、現在はBigQueryを活用したGA4やGSCのデータ分析に注力

GA4×BigQueryにおける基本的なSQLの使い方は下記記事で紹介してますので、参考までにどうぞ<(_ _)>
» GA4とBigQueryを連携するメリット& 基本的なSQLの使い方を解説

また、SQL（BigQuery）スキルを習得するために以下のUdemy講座はおすすめです。
» BigQuery で学ぶ非エンジニアのための SQL データ分析入門

GA4×BigQueryで特定ユーザーの行動を把握するSQLクエリ

さっそく下記にSQLクエリを紹介していきます。

SQLクエリ

/*
特定ユーザーの行動を把握
*/

select
  format_timestamp('%Y-%m-%d %H:%M:%S', timestamp_micros(event_timestamp), 'Asia/Tokyo') as jst_time,
  event_name,
  event_params
from
  `ga4-bigquery-pj-392206.analytics_342165491.events_*`
where
  _table_suffix between '20240301' and '20240331'
  and user_pseudo_id = '1439162265.1711059337'
order by
  jst_time

時系列順に特定ユーザーのイベント名やイベントの中身を把握できるようなクエリです。

今回は2024年3月22日にお問い合わせが入ったユーザーがいたので、そちらのユーザーの3月中に取った行動データを分析していきます。

実行結果

上記のようにfirst_visitやsession_startのイベントが記録されており、その中身はどういったチャネルでどういったページに訪れているかなどが把握できます。

今回はGoogle検索で下記ページに訪れ、お問い合わせへと繋がったことがわかりました。

特定ユーザーの行動を把握する簡易版のSQLクエリ

先ほど紹介したクエリではあまり必要でない情報も取得しているので、要点を絞った簡易版のSQLクエリを紹介していきます。

SQLクエリ

/*
特定ユーザーの行動を把握（簡易版）
*/

select
  format_timestamp('%Y-%m-%d %H:%M:%S', timestamp_micros(event_timestamp), 'Asia/Tokyo') as jst_time,
  event_name,
  array(
    select as struct param.key, param.value.string_value
    from unnest(event_params) as param
    where param.key in ('page_title', 'page_location', 'campaign', 'source', 'page_referrer', 'link_url')
  ) as filtered_event_params
from
  `ga4-bigquery-pj-392206.analytics_342165491.events_*`
where
  _table_suffix between '20240301' and '20240331'
  and user_pseudo_id = '1439162265.1711059337'
order by
  jst_time

上記のように記述してあげることで、event_paramsの’page_title’, ‘page_location’, ‘campaign’, ‘source’, ‘page_referrer’, ‘link_url’だけを取得することができます。