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

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

ウェブ分析の指標

ウェブ分析の基本は

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

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

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

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

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

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

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

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

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

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

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

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

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

ページビュー数は行数のカウント

COUNT(ユーザID)

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

COUNT(DISTINCT セッションID)

ユーザ数はユニークなユーザ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_*`;

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

つまりuser_pseudo_id（またはuser_id）とga_session_idを組み合わせることで全体でユニークにセッションを特定するIDになる。

CONCAT()の区切り文字はfullVisitorIdとvisitIdに現れない文字であれば何でも可。文字列しか対象にできないので、数値を文字列に変換しておく。

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

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

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

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

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

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

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

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

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

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

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

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

実際にはサイト全体のページビュー数や訪問者数をカウントするだけでなく、○○した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に出力方法はこちらの記事を参照。

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

[公開日：2020年11月5日] [更新日：2021年3月11日]

アクセス解析 の記事一覧

marketechlabo
 
11 Users
21 Pockets

Googleアナリティクス4のBigQueryエクスポート仕様 | marketechlabo
https://www.marketechlabo.com/ga4-bigquery-export-spec/

www.marketechlabo.com

Page Not Found
https://www.marketechlabo.com/ga4-bigquery-export-differences/

www.marketechlabo.com

Page Not Found
https://www.marketechlabo.com/ga4-explore-translation/

marketechlabo
 
6 Users
4 Pockets

GA4/Firebaseのログをフラット化する汎用クエリ | marketechlabo
https://www.marketechlabo.com/ga4-firebase-log-preprocessing/

marketechlabo
 
7 Users
12 Pockets

GA4の計測検証をBigQueryを使って行う | marketechlabo
https://www.marketechlabo.com/verify-ga4-tracking-using-bigquery/