マーケxコード

【言語分析】SQLxBigQuery:GA4LPに対する表示回数、ユーザーが離脱するまでの平均表示回数、次のページとコンバージョン数を計測

GA4にてLPで流入した場合、ユーザーが離脱するまでの平均表示回数、ランディングページから次のページ閲覧と表示回数、CV数を算出。

この分析を行うことで、ランディングページから流入した際にコンバージョンするまで何ページ閲覧しているか、またどの程度の平均ページ閲覧で離脱したか、1セッションに関する行動や傾向がわかります。

WITH session_data 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,
    event_timestamp,
    DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
    ROW_NUMBER() OVER (PARTITION BY 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)) ORDER BY event_timestamp) AS page_view_rank
  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'
),
landing_pages AS (
  SELECT
    user_pseudo_id,
    session_id,
    page_location AS landing_page,
    MIN(event_timestamp) AS landing_timestamp,
    DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS landing_date
  FROM
    session_data
  GROUP BY
    user_pseudo_id, session_id, page_location
),
entry_complete AS (
  SELECT
    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,
    COUNT(event_name) AS EntryCompleteCount
  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 = '計測するイベント名を入力' ###イベント名を入力
  GROUP BY
    session_id
),
page_views AS (
  SELECT
    sd.user_pseudo_id,
    sd.session_id,
    sd.page_location,
    sd.page_view_rank,
    lp.landing_page,
    lp.landing_date
  FROM
    session_data sd
  JOIN
    landing_pages lp
  ON
    sd.user_pseudo_id = lp.user_pseudo_id
    AND sd.session_id = lp.session_id
  WHERE
    sd.event_timestamp >= lp.landing_timestamp
)
SELECT
  pv.session_id,
  pv.landing_date AS event_date,
  pv.landing_page,
  pv.page_location AS next_page,
  COUNT(*) AS views,
  AVG(pv.page_view_rank) AS avg_views_before_exit,
  COALESCE(ec.EntryCompleteCount, 0) AS EntryCompleteCount
FROM
  page_views pv
LEFT JOIN
  entry_complete ec
ON
  pv.session_id = ec.session_id
GROUP BY
  pv.session_id, pv.landing_date, pv.landing_page, pv.page_location, ec.EntryCompleteCount
ORDER BY
  EntryCompleteCount DESC;

・使用方法

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

AND event_name = '計測するイベント名を入力' ###イベント名を入力
Lコンバージョンするイベント名を入力

1. session_data CTE (Common Table Expression)
目的: ユーザーのページビューに関する情報を取得します。

WITH session_data 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,
    event_timestamp,
    DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
    ROW_NUMBER() OVER (PARTITION BY 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)) ORDER BY event_timestamp) AS page_view_rank
  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'
)

  • 主なフィールド:

    • user_pseudo_id: ユーザーの識別子。

    • session_id: セッションの識別子。user_pseudo_idとga_session_idから生成されます。

    • page_location: ページのURL。

    • event_timestamp: イベントのタイムスタンプ。

    • event_date: イベントの日付。

    • page_view_rank: 同じセッション内でのページビューの順序。

2. landing_pages CTE
目的: 各セッションの最初のページビュー（ランディングページ）を特定します。

landing_pages AS (
  SELECT
    user_pseudo_id,
    session_id,
    page_location AS landing_page,
    MIN(event_timestamp) AS landing_timestamp,
    DATE(TIMESTAMP_MICROS(MIN(event_timestamp))) AS landing_date
  FROM
    session_data
  GROUP BY
    user_pseudo_id, session_id, page_location
)

  • 主なフィールド:

    • user_pseudo_id: ユーザーの識別子。

    • session_id: セッションの識別子。

    • landing_page: ランディングページのURL。

    • landing_timestamp: ランディングページのタイムスタンプ。

    • landing_date: ランディングページの日時。

3. entry_complete CTE
目的: 特定のイベント（エントリー完了）の発生回数をセッションごとにカウントします。

entry_complete AS (
  SELECT
    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,
    COUNT(event_name) AS EntryCompleteCount
  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 = 'エントリー完了'
  GROUP BY
    session_id
)

  • 主なフィールド:

    • session_id: セッションの識別子。

    • EntryCompleteCount: エントリー完了イベントのカウント。

4. page_views CTE
目的: ランディングページ以降のページビューを取得します。

page_views AS (
  SELECT
    sd.user_pseudo_id,
    sd.session_id,
    sd.page_location,
    sd.page_view_rank,
    lp.landing_page,
    lp.landing_date
  FROM
    session_data sd
  JOIN
    landing_pages lp
  ON
    sd.user_pseudo_id = lp.user_pseudo_id
    AND sd.session_id = lp.session_id
  WHERE
    sd.event_timestamp >= lp.landing_timestamp
)

  • 主なフィールド:

    • user_pseudo_id: ユーザーの識別子。

    • session_id: セッションの識別子。

    • page_location: ページのURL。

    • page_view_rank: ページビューの順序。

    • landing_page: ランディングページのURL。

    • landing_date: ランディングページの日付。

5. メインクエリ
目的: 各セッションごとにランディングページとその後のページビューのデータをまとめ、エントリー完了イベントのカウントを含めたレポートを生成します。

SELECT
  pv.session_id,
  pv.landing_date AS event_date,
  pv.landing_page,
  pv.page_location AS next_page,
  COUNT(*) AS views,
  AVG(pv.page_view_rank) AS avg_views_before_exit,
  COALESCE(ec.EntryCompleteCount, 0) AS EntryCompleteCount
FROM
  page_views pv
LEFT JOIN
  entry_complete ec
ON
  pv.session_id = ec.session_id
GROUP BY
  pv.session_id, pv.landing_date, pv.landing_page, pv.page_location, ec.EntryCompleteCount
ORDER BY
  EntryCompleteCount DESC;

  • 主なフィールド:

    • session_id: セッションの識別子。

    • event_date: イベントの日付。

    • landing_page: ランディングページのURL。

    • next_page: 次のページのURL。

    • views: ページビューのカウント。

    • avg_views_before_exit: ページビューの平均ランク（順序）。

    • EntryCompleteCount: エントリー完了イベントのカウント。

この記事を書いた人
セイさん
セイさん
最近書いた記事
マーケxコード
【BigQuery】特定ページからの“本当に進んでいるページ”を正確に把握
マーケxコード
【Big quert】アトリビューション再現SQL
マーケxコード
【Big query】アトリビュージョン計測用
マーケxコード
【言語分析】BigqueryxSQL:コンバージョンしたユーザーの訪問数とセッションメディアを算出
おすすめの記事