【GA4×BigQuery】LP別のセッション数＆平均PV数を算出する方法

イメージ図

今回のSQLクエリは、Google Analytics 4 (GA4) データを使用して、訪問者のランディングページとその後のページビューの動作を分析しています。

具体的には、ユーザーが特定のページに最初に到達した際のセッション（訪問セッション）ごとに、いくつのページを閲覧したかを集計し、各ランディングページごとのセッション数と平均ページビュー数を算出しています。

この分析をすることによって、Webサイトに訪問したランディングページ毎に、表示回数が多い記事を見つけることで興味関心の高いランディングページを判断することができます。

この分析結果は、Google Analytics 4では表示させることはできません。

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,
    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
  FROM
    session_data
  GROUP BY
    user_pseudo_id, session_id, page_location
),
page_views AS (
  SELECT
    sd.user_pseudo_id,
    sd.session_id,
    lp.landing_page,
    COUNT(*) AS pages_viewed_before_exit
  FROM
    session_data sd
    JOIN landing_pages lp
    ON sd.user_pseudo_id = lp.user_pseudo_id
    AND sd.session_id = lp.session_id
    AND sd.event_timestamp >= lp.landing_timestamp
  GROUP BY
    sd.user_pseudo_id, sd.session_id, lp.landing_page
)
SELECT
  pv.landing_page,
  COUNT(*) AS sessions_count,
  AVG(pv.pages_viewed_before_exit) AS avg_pages_viewed_before_exit
FROM
  page_views pv
GROUP BY
  pv.landing_page
ORDER BY
  sessions_count DESC;

使用方法
FROM
`################.events_*` ###テーブルID
この部分にGA4の情報「テーブルID」を入力すると実行が可能になります。

WITHセクション
session_data:

user_pseudo_id：各ユーザーを識別する擬似ID。
session_id：ユーザーIDとセッションIDを組み合わせて一意のセッションIDを生成。
page_location：ページのURL。
event_timestamp：イベントのタイムスタンプ。
page_view_rank：各セッション内のページビューの順序を指定するランク。
この部分では、過去300日間のデータを抽出し、ユーザーごとのセッションID、ページのURL、イベントタイムスタンプ、ページビューの順序を取得します。

landing_pages:

user_pseudo_id：各ユーザーを識別する擬似ID。
session_id：ユーザーIDとセッションIDを組み合わせた一意のセッションID。
landing_page：ランディングページ（最初にアクセスされたページ）。
landing_timestamp：ランディングページへの最初のアクセスのタイムスタンプ。
この部分では、各セッションにおいて最初にアクセスされたページ（ランディングページ）を特定します。

page_views:

user_pseudo_id：各ユーザーを識別する擬似ID。
session_id：ユーザーIDとセッションIDを組み合わせた一意のセッションID。
landing_page：ランディングページ。
pages_viewed_before_exit：セッション内でランディングページ以降に閲覧されたページの数。
この部分では、ランディングページから開始されたセッションごとのページビューの総数をカウントします。

landing_page：ランディングページのURL。
sessions_count：各ランディングページで開始されたセッションの総数。
avg_pages_viewed_before_exit：ランディングページ以降に平均して何ページが閲覧されたか。