ウェブログからSQLで指標を計算する8構文~GA4のBigQueryを題材に

Googleアナリティクス4プロパティが登場し、誰でもBigQueryにログを出力できるようになった。ログ分析を始める環境は揃ったわけだが、ログ分析のノウハウはあまり世に出ていないので、それを少し紹介する。

どんな高度なログ分析をするにしても、これが基本。

ウェブ分析の指標

ウェブ分析の基本は

  • ページビュー数
  • セッション数
  • 人数

のカウント。複雑な分析も、結局カウントしているのはこの3つの指標に集約されることが多い。Eコマースになると購入金額の合計なども入ってくることはある。

そしてこれに「○○した」という条件が付いて

  • イベント○○が発生した回数
    • ○○したページビュー数
      • パラメータ△△の値が□□だったイベント○○が発生した回数(ページ□□のページビュー数)
  • ○○したセッション数
  • ○○した人数

そして率を計算

  • ○○したセッション率(セッション単位CVR)
  • ○○した人数率(ユーザ単位CVR)

指標をBigQueryからSQLで計算する

無条件の単純なページビュー数/セッション数/人数のカウント

GA4に限らずログ分析の原則として、1行1ページビューになっていれば

ページビュー数は

COUNT(ユーザID)

セッション数は

COUNT(DISTINCT セッションID)

ユーザ数は

COUNT(DISTINCT ユーザID)

となる。

GA4の形式の場合、ページビュー数(ページビューイベントが発生した回数)

SELECT
  COUNT(
    CASE
      WHEN event_name = 'page_view' THEN user_pseudo_id
      ELSE NULL
    END
  ) AS n_pageviews
FROM
  `prj.analytics_999999999.events_*`;

event_name = 'page_view'である行数をカウントする。

総人数

SELECT
  COUNT(
    DISTINCT user_pseudo_id
  ) n_visitors
FROM
  `prj.analytics_999999999.events_*`;
  • ユーザを特定する列user_pseudo_id(明示的にSDK内でユーザーIDを設定している場合はuser_idを使う。いずれも文字列型)

があるので、これをユニークカウントする。

総セッション数

SELECT
  COUNT(DISTINCT
    CONCAT(
      user_pseudo_id,
      '-',
      CAST(
        (SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id') AS string
      )
    )
  ) AS n_sessions
FROM
  `prj.analytics_999999999.events_*`;
  • ユーザごとのセッションを特定する(あくまでユーザ内なのでグローバルにセッションを特定できるわけではない)変数がページビューイベントのパラメータga_session_id(整数型)

にあるため、ユーザを特定するのに使ったuser_pseudo_id(またはuser_id)と組み合わせるとグローバルにセッションを特定できるIDになる。これをユニークカウントする。CONCAT()は文字列しか対象にできないので、数値を文字列に変換しておく。

イベントパラメータの抽出方法

ここでイベントパラメータの値を取得したが、これの取得方法を説明する。

イベントパラメータはネストされており、列名だけ指定しても使えない。

BigQueryのネスト構造

これがBigQuery+Googleアナリティクスの厄介な形式なのだが、イベントパラメータの値を取得するには以下のイディオムで覚えておけばいい。

整数型のパラメータ値を取得する

(SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id')

文字列型のパラメータ値を取得する

(SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'source')

条件付きページビュー数/セッション数/人数のカウント

実際にはサイト全体のページビュー数や訪問者数をカウントするだけでなく、○○したPV数、セッション数、人数を求めるのが普通である。

一般的なログ分析では「○○した」というのはCASE句を使って指定する。

条件に当てはまるページビュー数は

COUNT(CASE WHEN ○○した条件 THEN ユーザID ELSE NULL END)

条件に当てはまるセッション数は

COUNT(DISTINCT CASE WHEN ○○した条件 THEN セッションID ELSE NULL END)

条件に当てはまるユーザ数は

COUNT(DISTINCT CASE WHEN ○○した条件 THEN ユーザID ELSE NULL END)

となる。

GA4の場合、たとえば90%スクロールした数は

SELECT
  COUNT(
    CASE
      WHEN
        event_name = 'scroll'
          AND (SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'percent_scrolled') = 90
            THEN user_pseudo_id
      ELSE NULL END
    ) AS n_scrolls_90_percent
FROM
  `prj.analytics_999999999.events_*`;

event_name = 'scroll'percent_scrolled = 90の行数をカウントする。

つまり「パラメータ△△の値が□□だったイベント○○が発生した回数」に一般化できる。
「ページ□□のページビュー数」も同じで、「https://nomigame.com/archives/756/」のページビュー数

SELECT
  COUNT(
    CASE
      WHEN
        event_name = 'page_view'
          AND (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location') = 'https://nomigame.com/archives/756/'
            THEN user_pseudo_id
      ELSE NULL END
    ) AS n_pageviews_756
FROM
  `prj.analytics_999999999.events_*`;

event_name = 'page_view'かつpage_locationが「https://nomigame.com/archives/756/」が条件になる。

ページビュー関連のパラメータには

  • URLpage_location
  • ページタイトルpage_title
  • 参照元page_referrer

などがある。

「https://nomigame.com/archives/756/」を見たセッション数
特定のページ(購入完了など)を見たセッション数ということで、コンバージョンのカウントになる。

SELECT
  COUNT(DISTINCT
    CASE
      WHEN
        event_name = 'page_view'
          AND (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location') = 'https://nomigame.com/archives/756/'
            THEN CONCAT(user_pseudo_id, '-', CAST((SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id') AS string))
      ELSE NULL END
    ) AS n_sessions_756
FROM
  `prj.analytics_999999999.events_*`;

条件の部分は上と同じだが、THENの後つまりカウントする対象が

CONCAT(user_pseudo_id, '-', CAST((SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id') AS string))

つまり無条件の時に出てきたグローバルにセッションを特定するIDになっている。

「https://nomigame.com/archives/756/」を見た人数
コンバージョン人数になる

SELECT
  COUNT(DISTINCT
    CASE
      WHEN
        event_name = 'page_view'
          AND (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location') = 'https://nomigame.com/archives/756/'
            THEN user_pseudo_id
      ELSE NULL END
    ) AS n_sessions_756
FROM
  `prj.analytics_999999999.events_*`;

○○した率を計算

コンバージョン数だけでなくコンバージョン率も見る。絶対数だけでなく率も見るのが普通である。

  • 分子には条件に当てはまる○○
  • 分母には総○○

なので、条件に当てはまるページビュー率は

COUNT(CASE WHEN ○○した条件 THEN ユーザID ELSE NULL END) / COUNT(ユーザID)

条件に当てはまるセッション率は

COUNT(DISTINCT CASE WHEN ○○した条件 THEN セッションID ELSE NULL END) / COUNT(DISTINCT セッションID)

条件に当てはまるユーザ率は

COUNT(DISTINCT CASE WHEN ○○した条件 THEN ユーザID ELSE NULL END) / COUNT(DISTINCT ユーザID)

となる。

GA4では○○した率(CVR)

SELECT
  COUNT(DISTINCT
    CASE
      WHEN
        event_name = 'page_view'
          AND (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location') = 'https://nomigame.com/archives/756/'
            THEN CONCAT(user_pseudo_id, '-', CAST((SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id') AS string))
      ELSE NULL END
    ) / COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST((select ep.value.int_value from unnest(event_params) ep where ep.key = 'ga_session_id') AS string)))
    AS r_sessions_756
FROM
  `prj.analytics_999999999.events_*`;

複雑に見えるが、先に計算した「○○したセッション数」を「総セッション数」で割っただけである。

○○した人率(ユーザ単位CVR)

SELECT
  COUNT(DISTINCT
    CASE
      WHEN
        event_name = 'page_view'
          AND (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'page_location') = 'https://nomigame.com/archives/756/'
            THEN user_pseudo_id
      ELSE NULL END
    ) / COUNT(DISTINCT user_pseudo_id)
    AS r_sessions_756
FROM
  `prj.analytics_999999999.events_*`;

まとめ:ログ分析で使う指標のSQL8構文

  1. 総PV数
  2. 総セッション数
  3. 総人数
  4. ○○したPV数
  5. ○○したセッション数
  6. ○○した人数
  7. ○○したセッション率
  8. ○○した人率
SELECT
  -- 総PV数
  COUNT(CASE WHEN event_name = 'page_view' THEN user_pseudo_id ELSE NULL END) n_pageviews,
  -- 総セッション数
  COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST((select ep.value.int_value from unnest(event_params) ep where ep.key = 'ga_session_id') AS string))) n_sessions,
  -- 総人数
  COUNT(DISTINCT user_pseudo_id) n_visitors,
  -- ○○したPV数
  COUNT(CASE WHEN event_name = 'page_view' AND (select ep.value.string_value from unnest(event_params) ep where ep.key = 'page_location') = 'https://nomigame.com/archives/756/' THEN user_pseudo_id ELSE NULL END) n_pageviews_756,
  -- ○○したセッション数
  COUNT(DISTINCT CASE WHEN event_name = 'page_view' AND (select ep.value.string_value from unnest(event_params) ep where ep.key = 'page_location') = 'https://nomigame.com/archives/756/' THEN CONCAT(user_pseudo_id, '-', CAST((select ep.value.int_value from unnest(event_params) ep where ep.key = 'ga_session_number') AS string)) ELSE NULL END) n_sessions_756,
  -- ○○した人数
  COUNT(DISTINCT CASE WHEN event_name = 'page_view' AND (select ep.value.string_value from unnest(event_params) ep where ep.key = 'page_location') = 'https://nomigame.com/archives/756/' THEN user_pseudo_id ELSE NULL END) n_visitors_756,
  -- ○○したセッション率
  COUNT(DISTINCT CASE WHEN event_name = 'page_view' AND (select ep.value.string_value from unnest(event_params) ep where ep.key = 'page_location') = 'https://nomigame.com/archives/756/' THEN CONCAT(user_pseudo_id, '-', CAST((select ep.value.int_value from unnest(event_params) ep where ep.key = 'ga_session_number') AS string)) ELSE NULL END) / COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST((select ep.value.int_value from unnest(event_params) ep where ep.key = 'ga_session_id') AS string))) r_sessions_756,
  -- ○○した人率
  COUNT(DISTINCT CASE WHEN event_name = 'page_view' AND (select ep.value.string_value from unnest(event_params) ep where ep.key = 'page_location') = 'https://nomigame.com/archives/756/' THEN user_pseudo_id ELSE NULL END) / COUNT(DISTINCT user_pseudo_id) r_visitors_756,
FROM
  `prj.analytics_999999999.events_*`;

これは公式として覚えておくといい。

なおGA4のデータをBigQueryに出力方法はGoogleアナリティクス4プロパティのデータをBigQuery出力するを参照。

GCP/Firebase の記事一覧