ランディングページの2ページ目及び直帰数を同時にBigQueryから取得するSQLを解説する。探索の「経路データ探索」においてもページ遷移を確認することができるが、ユーザー単位での遷移でのみしか確認できない仕様のようなので、以下に提示するクエリ結果と「経路データ探索」の数値は完全に合致しない。そのため、計算結果の検証が難しくなったが、ロジックの正しさについては念入りににチェックを行ったので公開する。また、提示するSQLは簡易版であるため、より精緻にクエリを組み直したバージョンは完全版を参考して頂きたい。
参考：[GA4] ランディング ページ レポート
参考：[GA4] 閲覧開始数と離脱数
ランディングページの2ページ目を取得するクエリ
WITH
NextPage_01 AS (
SELECT
PARSE_DATE("%Y%m%d", event_date) AS ymd,
event_timestamp,
CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
(SELECT value.int_value FROM UNNEST(event_params) p WHERE p.key = 'entrances') AS entrances
FROM
`<project>.<dataset>.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
event_name = 'page_view'
),
NextPage_02 AS (
SELECT
ymd,
event_timestamp,
ssid,
page_location,
LEAD(page_location) OVER(PARTITION BY ssid ORDER BY event_timestamp ASC) AS next_page_location,
entrances
FROM
NextPage_01
),
NextPage_03 AS (
SELECT
ymd,
ssid,
page_location,
IFNULL(next_page_location, '( Exit )') AS nextpage_01,
entrances
FROM
NextPage_02
WHERE
entrances = 1
),
NextPage_04 AS (
SELECT
ymd,
page_location AS LandingPage,
nextpage_01,
COUNT(*) AS _cnt
FROM
NextPage_03
GROUP BY
ymd,
page_location,
nextpage_01
ORDER BY
ymd ASC,
LandingPage ASC,
_cnt DESC
)
SELECT
*
FROM
NextPage_04
クエリ解説
当クエリのランディングページの判定では’entrances’パラメータの値で判定を行っている。’entrances’パラメータはセッションにおける最初のページビューに立つフラグであるため、’entrances’の値が’1’である場合にランディングページとして区別することが可能になる。また同時にセッションベースでのランディングページの表示カウントにも活用することができ、この指標を「閲覧開始数」として探索上でも選択することができる。おそらく「閲覧開始数」は’entrances’パラメータの値に基づいて処理されている。
GA4の「ランディング ページ」レポートでは「セッション」数が指標として並んでいるが、ディメンションに「ランディング ページ」を選択した上で指標に「セッション」と「閲覧開始数」を並べると理論上は同値になり、実際にトラフィックが多くないサイトでは同値になる。ただサイト流入数のトラフィックが大きくなるにつれて、数値のズレが生じてくる。これはセッションにHyperLogLog++による集計処理が行われるため数値が完全には合致しなくなるため。このような理由から、「ランディングページ」を集計するためのカウント方法は’entrances’パラメータによる集計の方がより正確にカウントすることができるので、ランディングページに着地したセッション数として採用している。
次ページへの遷移はLEAD関数により’event_timestamp’の昇順で’page_location’を取得しているため、セッションによる2ページ目は「離脱」になるか、必ず「1ページのみ」が遷移ページとしてカウントされる。そのため、「経路データ探索」の「ユーザー」単位とは異なる集計方法としてカウントされる。
GA4の探索では「経路データ探索」
GA4の探索では行動フローをビジュアライズ的に分かりやすく可視化してくれる「経路データ探索」が備わっており、ユーザーの行動フローを可視化することができる。「経路データ探索」のビジュアライズはLooker Studioでは再現できないため、2ページ目、3ページ目と深掘りして分析する必要があれば必須の機能となる。ちなみに2ページ目以降のページ遷移もBigQueryで抽出することも可能ではある。
「経路データ探索」においてランディングページをスタート地点として分析する際は、始点にイベント名で”session_start”を指定する。ステップ１にはページタイトルかページパスを指定することで、ランディングページがステップ１に並ぶことになる。指標にはセッションや表示回数は選択できず、ユーザー単位の「アクティブ ユーザー数」か「総ユーザー数」を指定する。
BigQuery + Looker StudioでLP2ページ目を可視化
Looker Studio単体ではランディングページからの2ページ目や直帰率を可視化することはできない（※正確にいえば、2ページ目はできなくはないが）。BigQueryのみで集計されたデータもそれだけでは、人の脳ではどのような傾向があるのかを読み取ることは困難となる。そのため、ここからは集計されたデータをLooker Studioに渡し、直感的に分かり易いようにビジュアライズする必要がある。SQLもデータ可視化用に必要なカラム・行を追加する必要がある。そのコードの完成形は「Visual SEM Report」で提供している。以下はコードのみ。
ランディングページの2ページ目を取得するクエリ（同一ページを除去した改良版）
こちらのクエリでは同一ページで’page_view’が複数回カウントされた場合に重複した’page_view’を除去する処理を行っている。そのため、次のページは必ず次のページか離脱となる。’page_view’イベントの遷移をすべて可視化したいのであれば先に上げたクエリを使えばよいが、同一ページを2ページ目としないのであれば以下のクエリを使用する。
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');
WITH
BounceRate AS (
SELECT
ssid,
ymd,
event_timestamp,
Landing_page,
final_next_page,
IF(final_next_page = 'Exit',1, 0) AS _exit,
_entrances
FROM (
SELECT
ssid,
ymd,
event_timestamp,
page_location AS Landing_page,
CASE
WHEN next_page_location IS NULL THEN 'Exit'
ELSE next_page_location
END AS final_next_page,
IF (
entrances = 1
OR
entrances_01 = 1
,1,0
) AS _entrances
FROM (
SELECT
*,
IF (
LAG(entrances) OVER(PARTITION BY ssid ORDER BY event_timestamp ASC) = 1
AND
LAG(dup_page) OVER(PARTITION BY ssid ORDER BY event_timestamp ASC) = 1
,1,0
) AS entrances_01
FROM (
SELECT
ssid,
ymd,
event_timestamp,
page_location,
next_page_location,
IF(page_location = next_page_location,1, 0) AS dup_page,
entrances
FROM (
SELECT
ssid,
ymd,
event_timestamp,
page_location,
LEAD(page_location) OVER(PARTITION BY ssid ORDER BY event_timestamp ASC) AS next_page_location,
entrances
FROM (
SELECT
CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
PARSE_DATE("%Y%m%d", event_date) AS ymd,
event_timestamp,
REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
(SELECT value.int_value FROM UNNEST(event_params) p WHERE p.key = 'entrances') AS entrances
FROM
`<project>.<dataset>.events_*`
WHERE
_TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
event_name = 'page_view'
)
)
)
WHERE
entrances = 1
OR
dup_page = 0
)
WHERE
(dup_page = 0 AND entrances = 1)
OR
entrances_01 = 1
)
)
SELECT
*
FROM
BounceRate
