【言語分析】BigqueryxSQL:GA4でコンバージョンに至るまでの情報をユーザーid or セッションid単位で抽出

このクエリは、ユーザーの行動をより詳細に分析し、どのユーザーが最も多くのエントリー完了をしているか、またそのユーザーのページビュー数やセッション数を知るのに役立ちます。

WITH tmp AS (
  SELECT
    user_pseudo_id,
    CONCAT(CAST(user_pseudo_id AS STRING), "_", CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    COUNT(event_name) AS PageViews,
    event_date
  FROM
    `.events_*` ###テーブルID
  WHERE
    _table_suffix BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -300 DAY))
    AND FORMAT_DATE("%Y%m%d", CURRENT_DATE())
    AND event_name = 'page_view'
  GROUP BY
    user_pseudo_id, session_id, page_location, event_date
),
entry_count AS (
  SELECT
    user_pseudo_id,
    CONCAT(CAST(user_pseudo_id AS STRING), "_", CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    COUNT(event_name) AS EntryCompleteCount,
    event_date
  FROM
    `q-cut-424009.analytics_312395325.events_*`
  WHERE
    _table_suffix BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -300 DAY))
    AND FORMAT_DATE("%Y%m%d", CURRENT_DATE())
    AND event_name = 'イベント名' ###計測したいイベント名を入力
  GROUP BY
    user_pseudo_id, session_id, page_location, event_date
),
session_counts AS (
  SELECT
    user_pseudo_id,
    COUNT(DISTINCT session_id) AS session_count,
    event_date
  FROM tmp
  GROUP BY user_pseudo_id, event_date
)
SELECT 
  tmp.event_date,
  tmp.user_pseudo_id,
  tmp.session_id,
  tmp.page_location,
  tmp.PageViews,
  session_counts.session_count,
  COALESCE(entry_count.EntryCompleteCount, 0) AS EntryCompleteCount
FROM 
  tmp
LEFT JOIN 
  entry_count
ON 
  tmp.user_pseudo_id = entry_count.user_pseudo_id
  AND tmp.session_id = entry_count.session_id
  AND tmp.page_location = entry_count.page_location
  AND tmp.event_date = entry_count.event_date
LEFT JOIN
  session_counts
ON
  tmp.user_pseudo_id = session_counts.user_pseudo_id
  AND tmp.event_date = session_counts.event_date
ORDER BY
  EntryCompleteCount DESC;

使い方
FROM `.events_*` ###テーブルID
LテーブルIDを入力する

AND event_name = 'イベント名' ###計測したいイベント名を入力
L計測したいイベント名を入力する

WITH tmp AS (

• user_pseudo_id: ユーザーの擬似ID。

• session_id: ユーザーIDとセッションIDを組み合わせたユニークなセッションID。

• page_location: ページのURL。

• PageViews: ページビューの数。

• event_date: イベントの日付。

このセクションでは、過去300日間のpage_viewイベントを集計し、ユーザーごとのページビュー数をカウントします。

WITH tmp AS (
  SELECT
    user_pseudo_id,
    CONCAT(CAST(user_pseudo_id AS STRING), "_", CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    COUNT(event_name) AS PageViews,
    event_date
  FROM
    `.events_*`
  WHERE
    _table_suffix BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -300 DAY))
    AND FORMAT_DATE("%Y%m%d", CURRENT_DATE())
    AND event_name = 'page_view'
  GROUP BY
    user_pseudo_id, session_id, page_location, event_date
),

entry_count AS (

• EntryCompleteCount: 'エントリー完了'イベントの数。

このセクションでは、過去300日間のイベントを集計し、ユーザーごとのエントリー完了数をカウントします。

entry_count AS (
  SELECT
    user_pseudo_id,
    CONCAT(CAST(user_pseudo_id AS STRING), "_", CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    COUNT(event_name) AS EntryCompleteCount,
    event_date
  FROM
    `q-cut-424009.analytics_312395325.events_*`
  WHERE
    _table_suffix BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -300 DAY))
    AND FORMAT_DATE("%Y%m%d", CURRENT_DATE())
    AND event_name = 'イベント計測'
  GROUP BY
    user_pseudo_id, session_id, page_location, event_date
),

session_counts AS (

• session_count: ユニークセッションの数。

このセクションでは、過去300日間のユニークセッション数をカウントします。

session_counts AS (
  SELECT
    user_pseudo_id,
    COUNT(DISTINCT session_id) AS session_count,
    event_date
  FROM tmp
  GROUP BY user_pseudo_id, event_date
)

最終的なSELECT文

• 各ユーザーのページビュー、セッション数、エントリー完了数を結合して表示します。

• tmpテーブルをベースに、entry_countとsession_countsをLEFT JOINで結合。

• 結果をEntryCompleteCountの降順で並び替え。

SELECT 
  tmp.event_date,
  tmp.user_pseudo_id,
  tmp.session_id,
  tmp.page_location,
  tmp.PageViews,
  session_counts.session_count,
  COALESCE(entry_count.EntryCompleteCount, 0) AS EntryCompleteCount
FROM 
  tmp
LEFT JOIN 
  entry_count
ON 
  tmp.user_pseudo_id = entry_count.user_pseudo_id
  AND tmp.session_id = entry_count.session_id
  AND tmp.page_location = entry_count.page_location
  AND tmp.event_date = entry_count.event_date
LEFT JOIN
  session_counts
ON
  tmp.user_pseudo_id = session_counts.user_pseudo_id
  AND tmp.event_date = session_counts.event_date
ORDER BY
  EntryCompleteCount DESC;