【保存版】GA4×BigQueryで「CVまでのセッション履歴」を再現するSQL徹底解説

このSQLで解決できる課題

GA4を日々見ていると、「コンバージョン（CV）した人が、最初どんなページから入って、どれくらいの回数でCVしたのか」を知りたくなる瞬間があります。

しかしGA4の標準レポートでは、「ユーザー単位でのセッション履歴」を時系列で追うことはできません。たとえば次のような課題に直面します。

• どのランディングページがCVに繋がるまでに多くのセッションを要しているか知りたい
• 1回の訪問でCVするユーザーと、10回以上訪問してCVするユーザーの違いを把握したい
• 「お気に入り追加」など中間イベント（mCV）を行ったユーザーのCV確率を分析したい
• 流入チャネル別に「初回接点とCV接点の違い」を比較したい

このSQLでは、GA4の生データ（events_*）を基に、ユーザーごとにCVまでの全セッション履歴を構築します。
結果として、以下のような「ユーザー単位の行動ストーリー」を再現できるようになります。

• 初回訪問日・ランディングページ
• CVした日・そのセッションの流入元
• CVまでのセッション回数・経過日数
• 途中で行った中間イベント数（例：お気に入り追加）
• 最大10回分のセッション履歴（日時ベース）

つまり「どのような経路でコンバージョンに至ったのか？」を俯瞰し、マーケティングの改善議論に直結する基礎データを作ることができるのです。

実行結果イメージ

1ユーザーあたりに「初回訪問〜CVまでの全履歴・セッション日時」「中間イベント（mCV）数」「セッション回数・日数差」がまとめられます。
マーケ分析・広告施策のどちらにも即活用できる構造です。

背景・利用シーン

このSQLが役立つシーンは多岐にわたります。特に以下のようなケースで力を発揮します。

• 広告改善フェーズ
  　→Google広告やMeta広告の流入別に、CVに至るまでの「育成プロセス」を把握。
• リマーケティング設計
  　→何回目の訪問でCVが多いかを分析し、リターゲティングの配信期間を最適化。
• UX・導線改善
  　→初回ランディングページとCVページの差分から、導線上のボトルネックを特定。
• CRM・リピート施策
  　→お気に入りや検討イベントを指標に、「温度感の高いユーザー層」を抽出。

たとえば中古車サイトであれば「お気に入り追加→5日後にCV」という行動パターンが可視化でき、
「初回訪問から3〜7日後に再訪を促すリマケ広告」を設計する、というような施策に直結します。

抽出されたSQLから考察できる5つの会話例

1. マーケ担当A：「CVユーザーの平均セッション回数は何回？」
   分析担当B：「全体では2.8回、Google流入は1.9回、Meta流入は4.1回です。」
2. 広告担当C：「最初の流入がGoogleで、最後がDirectのユーザーが多いのはなぜ？」
   分析担当B：「初回で認知し、指名検索やブックマーク経由で再訪するケースが多そうです。」
3. UXデザイナーD：「お気に入り追加した人のCV率ってどれくらい？」
   分析担当B：「mCVありユーザーのCV率は28%、なしは8%です。」
4. 経営者E：「最短・最長の日数差は？」
   分析担当B：「即日CVもありますが、最長で45日間の検討期間が見られます。」

チーム全体：「3回目訪問以降のCV率が上がる理由は？」
分析担当B：「セッション3回目で商品比較ページ閲覧率が上がっており、検討深化が要因と考えられます。」

コード内容

--=====================================================================
--🚀GA4×BigQuery「CVまでのセッション履歴を再現する完全版SQL」
--変更は下の“可変パラメータ”だけでOK（プロジェクト／データセットは固定）
--=====================================================================


--▼可変パラメータ（ここだけ手入力）
DECLAREstart_dateSTRINGDEFAULT'2025-・・-・・';--解析開始日（YYYY-MM-DD）
DECLAREend_dateSTRINGDEFAULT'2025-・・-・・';--解析終了日（YYYY-MM-DD）
DECLAREcv_event_nameSTRINGDEFAULT'・・';--CVイベント名（例：'purchase','form_submit'）
DECLAREmcv_event_nameSTRINGDEFAULT'・・';--中間イベント名（例：'add_to_cart','favorite'）


--▼内部用（上の可変パラメータから自動算出：編集不要）
DECLAREsfx_startSTRING;
DECLAREsfx_endSTRING;
SETsfx_start=REPLACE(start_date,'-','');--'20250901'
SETsfx_end=REPLACE(end_date,'-','');--'20251031'


--▼参照テーブル（固定）：あなたの環境に合わせて一度だけ置換
--※BigQueryはFROM句の変数展開不可のため固定記述にしています
--例）`myproject.analytics_123456789.events_*`
--`・・・・・.analytics_・・・・.events_*`
--=====================================================================


WITHsessionsAS(--①セッション単位の行動ログ（page_view起点）
SELECT
user_pseudo_id,
--セッションID=user_pseudo_id+ga_session_id
CONCAT(
user_pseudo_id,
(SELECTvalue.int_valueFROMUNNEST(event_params)WHEREkey='ga_session_id')
)ASsession_id,
TIMESTAMP_SECONDS(CAST(event_timestamp/1000000ASINT64))ASevent_datetime,
(SELECTvalue.string_valueFROMUNNEST(event_params)WHEREkey='page_location')ASpage_location,
traffic_source.nameAStraffic_source_name,
traffic_source.mediumAStraffic_source_medium,
collected_traffic_source.manual_sourceASmanual_source,
collected_traffic_source.manual_mediumASmanual_medium
FROM`idemitsu-447809.analytics_328712105.events_*`
WHEREevent_name='page_view'
AND_TABLE_SUFFIXBETWEENsfx_startANDsfx_end
),


session_first_pageAS(--②セッション開始ページと初回流入
SELECT
user_pseudo_id,
session_id,
MIN(event_datetime)ASsession_start_time,
ARRAY_AGG(page_locationORDERBYevent_datetimeLIMIT1)[OFFSET(0)]ASlanding_page,
ARRAY_AGG(COALESCE(manual_source,traffic_source_name)ORDERBYevent_datetimeLIMIT1)[OFFSET(0)]ASsource,
ARRAY_AGG(COALESCE(manual_medium,traffic_source_medium)ORDERBYevent_datetimeLIMIT1)[OFFSET(0)]ASmedium
FROMsessions
GROUPBYuser_pseudo_id,session_id
),


cv_eventsAS(--③CVイベント（最新CV特定の母集団）
SELECT
user_pseudo_id,
TIMESTAMP_SECONDS(CAST(event_timestamp/1000000ASINT64))AScv_datetime,
DATE(TIMESTAMP_SECONDS(CAST(event_timestamp/1000000ASINT64)))AScv_date,
CONCAT(
user_pseudo_id,
(SELECTvalue.int_valueFROMUNNEST(event_params)WHEREkey='ga_session_id')
)AScv_session_id
FROM`idemitsu-447809.analytics_328712105.events_*`
WHEREevent_name=cv_event_name
AND_TABLE_SUFFIXBETWEENsfx_startANDsfx_end
),


latest_cv_per_userAS(--④ユーザーごとの最新CV
SELECT
user_pseudo_id,
MAX(cv_datetime)ASlatest_cv_datetime
FROMcv_events
GROUPBYuser_pseudo_id
),


cv_session_infoAS(--⑤CVセッションの着地・流入
SELECT
cv.user_pseudo_id,
cv.cv_date,
cv.cv_datetime,
s.landing_pageAScv_landing_page,
s.sourceAScv_source,
s.mediumAScv_medium,
cv.cv_session_id
FROMcv_eventscv
JOINlatest_cv_per_userlatest
ONcv.user_pseudo_id=latest.user_pseudo_id
ANDcv.cv_datetime=latest.latest_cv_datetime
LEFTJOINsession_first_pages
ONs.session_id=cv.cv_session_id
),


user_sessions_until_cvAS(--⑥CVまでの全セッション履歴
SELECT
s.user_pseudo_id,
s.session_id,
s.session_start_time,
s.landing_page,
s.source,
s.medium,
ROW_NUMBER()OVER(
PARTITIONBYs.user_pseudo_id
ORDERBYs.session_start_time
)ASsession_rank
FROMsession_first_pages
JOINcv_session_infocv
ONs.user_pseudo_id=cv.user_pseudo_id
WHEREs.session_start_time<=cv.cv_datetime
),


session_countsAS(--⑦CVまでのセッション数
SELECT
user_pseudo_id,
COUNT(*)ASsession_count_until_cv
FROMuser_sessions_until_cv
GROUPBYuser_pseudo_id
),


first_session_infoAS(--⑧初回訪問情報（LP/Source/Medium）
SELECTDISTINCT
user_pseudo_id,
FIRST_VALUE(session_start_time)OVER(
PARTITIONBYuser_pseudo_idORDERBYsession_start_time
)ASfirst_session_datetime,
FIRST_VALUE(landing_page)OVER(
PARTITIONBYuser_pseudo_idORDERBYsession_start_time
)ASfirst_landing_page,
FIRST_VALUE(source)OVER(
PARTITIONBYuser_pseudo_idORDERBYsession_start_time
)ASfirst_source,
FIRST_VALUE(medium)OVER(
PARTITIONBYuser_pseudo_idORDERBYsession_start_time
)ASfirst_medium
FROMuser_sessions_until_cv
),


favorite_eventsAS(--⑨中間イベント（mCV）件数
SELECT
user_pseudo_id,
COUNT(*)ASmCV
FROM`idemitsu-447809.analytics_328712105.events_*`
WHEREevent_name=mcv_event_name
AND_TABLE_SUFFIXBETWEENsfx_startANDsfx_end
GROUPBYuser_pseudo_id
)


--⑩出力（ユーザー単位：初回～CVの行動ストーリー）
SELECT
f.user_pseudo_idASuser_id,
DATETIME(f.first_session_datetime)ASfirst_visit_datetime,
DATETIME(cv.cv_datetime)ASconversion_datetime,
cv.cv_dateASconversion_date,


f.first_landing_page,
cv.cv_landing_page,


f.first_source,
f.first_medium,
cv.cv_source,
cv.cv_medium,


IFNULL(fe.mCV,0)ASmCV,--中間イベント数
sc.session_count_until_cv,--CVまでのセッション回数


--訪問〜CVまでの日数差（同日CVは0）
DATE_DIFF(
DATE(MAX(us.session_start_time)),
DATE(MIN(us.session_start_time)),
DAY
)ASdays_until_conversion,


--最大10回分の訪問タイムスタンプ（必要に応じて列数を増減）
MAX(IF(us.session_rank=1,us.session_start_time,NULL))ASsession_1_datetime,
MAX(IF(us.session_rank=2,us.session_start_time,NULL))ASsession_2_datetime,
MAX(IF(us.session_rank=3,us.session_start_time,NULL))ASsession_3_datetime,
MAX(IF(us.session_rank=4,us.session_start_time,NULL))ASsession_4_datetime,
MAX(IF(us.session_rank=5,us.session_start_time,NULL))ASsession_5_datetime,
MAX(IF(us.session_rank=6,us.session_start_time,NULL))ASsession_6_datetime,
MAX(IF(us.session_rank=7,us.session_start_time,NULL))ASsession_7_datetime,
MAX(IF(us.session_rank=8,us.session_start_time,NULL))ASsession_8_datetime,
MAX(IF(us.session_rank=9,us.session_start_time,NULL))ASsession_9_datetime,
MAX(IF(us.session_rank=10,us.session_start_time,NULL))ASsession_10_datetime


FROMfirst_session_infof
JOINcv_session_infocvONf.user_pseudo_id=cv.user_pseudo_id
JOINsession_countsscONf.user_pseudo_id=sc.user_pseudo_id
LEFTJOINuser_sessions_until_cvusONf.user_pseudo_id=us.user_pseudo_id
LEFTJOINfavorite_eventsfeONf.user_pseudo_id=fe.user_pseudo_id


GROUPBY
f.user_pseudo_id,
f.first_session_datetime,
f.first_landing_page,
f.first_source,
f.first_medium,
cv.cv_datetime,
cv.cv_date,
cv.cv_landing_page,
cv.cv_source,
cv.cv_medium,
sc.session_count_until_cv,
fe.mCV


ORDERBYconversion_date;

SQLから応用編

このSQLで取れるのは「1ユーザーの行動ストーリー」です。
つまり、集計で明らかにできるのは：

これらを整理すると、「どうやってユーザーが購買・申込に至ったか」を、数値で語れるようになります。

基礎集計（全体傾向の把握）

ポイント：この段階で「短期CVvs長期CV」のボリュームを見せると、商材の検討特性が直感的に伝わります。

まとめ

このSQLを使えば、「CVユーザーの行動ストーリー」をBigQuery上で完全に再現できます。
特に、GA4の標準レポートでは不可能な以下の分析が可能になります。

• セッション単位での再訪・行動深度の把握
• 初回流入とCV流入のチャネル差分分析
• 中間イベントを活用した「検討フェーズ可視化」
• 訪問からCVまでの日数・セッション数の統計分析

マーケティングの文脈で言えば、ファネルの“縦の動き”をデータで再現するSQLです。
「このSQLを理解すれば、ユーザーの時間軸上の行動を数字で語れる」ようになります。
BigQuery連携のあるGA4ユーザーは、必ず一度試してみてください。