ランディングページの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”を指定する。ステップ１にはページタイトルかページパスを指定することで、ランディングページがステップ１に並ぶことになる。指標にはセッションや表示回数は選択できず、ユーザー単位の「アクティブ ユーザー数」か「総ユーザー数」を指定する。

参考：[GA4] 経路データ探索

BigQuery + Looker StudioでLP2ページ目を可視化

Looker Studio単体ではランディングページからの2ページ目や直帰率を可視化することはできない（※正確にいえば、2ページ目はできなくはないが）。BigQueryのみで集計されたデータもそれだけでは、人の脳ではどのような傾向があるのかを読み取ることは困難となる。そのため、ここからは集計されたデータをLooker Studioに渡し、直感的に分かり易いようにビジュアライズする必要がある。SQLもデータ可視化用に必要なカラム・行を追加する必要がある。そのコードの完成形は「Visual SEM Report」で提供している。以下はコードのみ。

Protected Area

This content is password-protected. Please verify with a password to unlock the content.

Enter your password Unlock