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

calendar Nov 5, 2020 / · 4 分で読了 · GA4BigQueryアクセス解析  ·
共有する: linkedin copy

概要

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

ウェブ分析の基本は

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

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

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

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

をひたすらカウントする。たとえば

  • 資料ダウンロードボタンをクリックした回数
  • 80％以上スクロールしたページビュー数
  • 自然検索から流入したセッション数
  • コンバージョン完了ページを見た人数

である。これらもページビュー数／セッション数／PV数のカウントであるのは同じで、「○○した」条件に絞り込んでカウントしているだけである。

そして絶対数だけでなく率の指標

  • ○○したセッション率（セッション単位CVRなど）
  • ○○した人数率（ユーザ単位CVRなど）

を計算して歩留まりを見る。これにさまざまなディメンションをかけて比較するだけで、見ている指標はほとんどこれだけなのである。

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

COUNT(ユーザID)
sql

セッション数はユニークなセッションIDの数のカウント

COUNT(DISTINCT セッションID)
sql

ユーザ数はユニークなユーザIDの数のカウント

COUNT(DISTINCT ユーザID)
sql

となる。

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

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

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

総人数

SELECT
  COUNT(
    DISTINCT user_pseudo_id
  ) n_visitors
FROM
  `prj.analytics_999999999.events_*`;
sql

ユーザを特定する列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_*`;
sql

GA4の仕様で、単体でユニークなセッションを特定するIDを指す列はない。ユーザごとのセッションを特定する（あくまでユーザ内なのでグローバルにセッションを特定できるわけではない）変数がページビューイベントのパラメータga_session_id（整数型）になる。

つまり**user_pseudo_id（またはuser_id）とga_session_idを組み合わせることで全体でユニークにセッションを特定**するIDになる。CONCAT()の区切り文字はuser_pseudo_idとga_session_idに現れない文字であれば何でも可。文字列しか対象にできないので、数値を文字列に変換しておく。

ここでイベントパラメータの値を取得したが、これの取得方法を説明する。 イベントパラメータはネストされており、列名だけ指定しても使えない。

BigQueryのネスト構造
BigQueryのネスト構造

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

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

SELECT
  (SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key = 'ga_session_id')
FROM
  `prj.analytics_999999999.events_*`;
sql

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

SELECT
  (SELECT ep.value.string_value FROM UNNEST(event_params) ep WHERE ep.key = 'source')
FROM
  `prj.analytics_999999999.events_*`;
sql

目的のイベントパラメータ名「●●」に対して、

(SELECT ep.value.***_value FROM UNNEST(event_params) ep WHERE ep.key = '●●')
sql

を一つの列と見立てるのである。

実際にはサイト全体のページビュー数や訪問者数をカウントするだけでなく、○○したPV数、セッション数、人数を求めるのが普通である。一般的なログ分析では「○○した」というのはCASE句を使って指定する。

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

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

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

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

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

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

となる。

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_*`;
sql

event_name = 'scroll'でpercent_scrolled = 90の行数をカウントする。 つまり「パラメータ△△の値が□□だったイベント○○が発生した回数」に一般化できる。

「ページ□□のページビュー数」も同じで、「https://example.com/thankyou/」のページビュー数

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://example.com/thankyou/'
            THEN user_pseudo_id
      ELSE NULL END
    ) AS n_pageviews_cv
FROM
  `prj.analytics_999999999.events_*`;
sql

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

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

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

などがある。

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

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://example.com/thankyou/'
            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_cv
FROM
  `prj.analytics_999999999.events_*`;
sql

条件の部分は上と同じだが、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://example.com/thankyou/」を見た人数 コンバージョン人数になる

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://example.com/thankyou/'
            THEN user_pseudo_id
      ELSE NULL END
    ) AS n_visitors_cv
FROM
  `prj.analytics_999999999.events_*`;
sql

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

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

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

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

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

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

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

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

となる。

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://example.com/thankyou/'
            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_cv
FROM
  `prj.analytics_999999999.events_*`;
sql

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

○○した人率（ユーザ単位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://example.com/thankyou/'
            THEN user_pseudo_id
      ELSE NULL END
    ) / COUNT(DISTINCT user_pseudo_id)
    AS r_visitors_cv
FROM
  `prj.analytics_999999999.events_*`;
sql
  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://example.com/thankyou/' THEN user_pseudo_id ELSE NULL END) n_pageviews_cv,
  -- ○○したセッション数
  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://example.com/thankyou/' 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) n_sessions_cv,
  -- ○○した人数
  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://example.com/thankyou/' THEN user_pseudo_id ELSE NULL END) n_visitors_cv,
  -- ○○したセッション率
  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://example.com/thankyou/' 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))) r_sessions_cv,
  -- ○○した人率
  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://example.com/thankyou/' THEN user_pseudo_id ELSE NULL END) / COUNT(DISTINCT user_pseudo_id) r_visitors_cv
FROM
  `prj.analytics_999999999.events_*`;
sql

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

なおGA4のデータをBigQueryに出力する方法はこちらの記事を参照。 /ga-app-web-property-to-bigquery/

また出力されたデータの形式、仕様はこちらの記事を参照。 /ga4-bigquery-export-spec/