GoogleAnalytics4のデータをBigQueryで分析してみる

準備編

GoogleAnalytics4のデータをBigQueryに転送する

• GoogleAnalytics4の設定から可能です。

• 設定すると、BigQueryに自動でデータセットとテーブルが生成されます。
  • 生成されるテーブルは、整数範囲パーティション分割テーブルで、yyyymmddの形式で日付ごとに分割されています。
  • 接続するBIツールによっては、日付の分だけテーブルが表示されてしまうため、別途時間単位の列パーティション分割テーブルに切り出した上で、そちらを参照する方法もあります。
  • 公式からスキーマも公開されています。

データ編

GoogleAnayltics4のイベントを理解する

• GoogleAnalytics4では、ユーザーがサイト上(アプリ上)で起こした行動を、イベントという単位で計測していきます。

  • セッションをスタートした(session_start)
  • ページを閲覧した(page_view)
  • クリックした(click)
    • ※外部ドメインへのリンクをクリックした時のみ計測される
  • スクロールした(scroll)
  • その他、自動的に収集されるイベントはこちらをご参照ください。

• イベント1つ1つに対して、複数のパラメータが設定されています。

  • パラメータは、key-value形式で保存されています。
  • valueに関して、内部的にはINTEGER、FLOAT、STRINGの3種類のデータ型のカラムが用意されています。
  • SQLを書く際は、出力したいvalueのデータ型によって、参照するカラムを変える必要があるのでご注意ください。

実際のデータで理解する

• 下記は、私がQiitaアカウントに設定しているGoogleAnalytics4のデータをBigQueryに転送したものです。

• 1行1行がイベントになります。
• ユーザーが起こしたイベントの名称がevent_nameカラムに格納され、イベント1つ1つに対して、event_params.key及びevent_params.valueが複数格納されています。
• GoogleAnalytics及びGoogleAnalytics360では、1行の単位がセッションで、その中に複数のヒットが格納されているため、データ構造自体も大きく違うことが見て取れるかと思います。

余談「なぜBigQueryに転送する必要があるのか？」

• ブラウザ上でGoogleAnalytics¹にアクセスすれば、蓄積されたデータをレポート形式で出力して確認することは可能です。
• しかし、扱える指標やディメンションに制限があり、場合によっては集計したい形式でレポートを作成することができないことがあります。
• 具体的な例を出すと、イベントの値という項目をディメンションに設定することができません。(2021年10月16日現在)
  • イベントの値とは、key-value形式のvalueのカラムを指します。
• これにより、ページごとのユニークユーザー数を出力するというレポートを作ることができないのです。
  • ユーザーが閲覧したページパスの情報は、keyがpage_locationのSTRING型のvalueカラムに格納されています。
  • 選択できるディメンションの中に、このvalueカラム(正確にはイベントの値という項目)が存在しません。
• なお、GoogleデータポータルからデータソースにGoogleAnalytics¹を選択した場合も同様に選択することができません。
• 元々BigQueryへの転送は、有償版のGoogleAnalytics360のみで開放されていた機能でした。
• 無償で利用できるGoogleAnalytics4でも転送ができるのは、あくまでデータを蓄積する箱としてプロパティを利用し、その先のデータ分析はBigQueryに転送した上で行うことを前提にしているのではないか？と予想しています。

クエリ編

• ここから先では、いくつかクエリ例をご紹介します。
• なお、公式にもサンプルがいくつか公開されているので、こちらも必要に応じてご参照ください。

ページごとのユニークユーザー数

SELECT 
  value.string_value
  , COUNT(DISTINCT user_pseudo_id) AS uu
FROM 
  `project_name.dataset_name.events_*`
  , UNNEST(event_params)
WHERE
  _TABLE_SUFFIX BETWEEN '20211009'
  AND '20211015'
  AND event_name = 'page_view'
  AND key = 'page_location'
GROUP BY  
  1
ORDER BY
  2 DESC

• key-value形式のパラメータはRECORD型のカラムのため、UNNEST関数を用いてフラット化することで、中身を参照することができるようになります。
• ユーザーがページを閲覧したイベントは、イベント名page_viewで送信されます。
• ユニークユーザーID(≒ブラウザごとに設定された一意の値)は、user_pseudo_idカラムに格納されています。

セッション開始日ごとのユニークユーザー数

SELECT 
  DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS session_begin_date
  , COUNT(DISTINCT user_pseudo_id) AS uu
FROM 
　　`project_name.dataset_name.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20211009'
  AND '20211015'
  AND event_name = 'session_start'
GROUP BY  
  1
ORDER BY
  1

• ユーザーがセッションを開始したイベントは、イベント名session_startで送信されます。
• イベント発生時刻は、event_timestampカラムにINT型で格納されています。
  • TIMESTAMP_MICROS関数及び、DATE関数を用いて変換しましょう。

参照元/メディアごとのユニークユーザー数

SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS utm_source
  , (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS utm_medium
  , COUNT(DISTINCT user_pseudo_id) AS uu
FROM 
  `project_name.dataset_name.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20211009'
  AND '20211015'
  AND event_name = 'page_view'
GROUP BY  
  1,2
ORDER BY
  3 DESC

• 参照元(utm_source)、メディア(utm_medium)の情報は、イベント名page_viewのパラメータにkey-value形式で格納されています。
• 1レコードに複数のパラメータのvalueを並べて出力したい場合は、SELECT句にサブクエリを書きましょう。

直近3ヶ月の記事ごとのLGTM率、ストック率

WITH 
  query_results AS (
    SELECT 
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title
      , COUNT(DISTINCT CASE WHEN event_name = 'page_view' THEN user_pseudo_id END) AS uu
      , COUNT(DISTINCT CASE WHEN event_name = 'Like an Article' THEN user_pseudo_id END) AS like_an_article_uu
      , COUNT(DISTINCT CASE WHEN event_name = 'Stock an Article' THEN user_pseudo_id END) AS stock_an_article_uu
    FROM 
      `project_name.dataset_name.events_*`
    WHERE
      _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 3 MONTH))
      AND event_name IN ('page_view', 'Like an Article', 'Stock an Article')
    GROUP BY  
      1
  )

SELECT 
  page_title
  , uu
  , like_an_article_uu
  , ROUND(SAFE_DIVIDE(like_an_article_uu, uu), 3) AS like_an_article_rate
  , stock_an_article_uu
  , ROUND(SAFE_DIVIDE(stock_an_article_uu, uu), 3) AS stock_an_article_rate
FROM 
  query_results 
ORDER BY
  2 DESC

• 現在の日付から3ヶ月前を、日付関数で計算して出力します。
  • event_yyyymmddテーブルには、当日のテーブルが含まれないため、厳密には3ヶ月-1日のテーブルが対象となります。
• Qiitaでは、LTGMされるとLike an Articleというイベント名で、ストックされるとStock an Articleというイベント名でデータが送信されます。
  • (なお、私がこれまで書いた記事では、LTGM率は1%、ストックは0.5%程度でした)

初回閲覧記事と次に閲覧した記事の組み合わせごとのユニークユーザー数

WITH 
  query_results AS (
    SELECT 
      ROW_NUMBER() OVER(PARTITION BY user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') ORDER BY event_timestamp) AS event_number
      , event_timestamp
      , user_pseudo_id AS unique_user_id
      , (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
      , (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title
    FROM 
      `project_name.dataset_name.events_*`
    WHERE
      _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 3 MONTH))
      AND event_name IN ('page_view')
  )

SELECT 
  COUNT(DISTINCT step1.unique_user_id ) AS uu
  , step1.page_title AS step1_page_title
  , step2.page_title AS step2_page_title
FROM 
  query_results AS step1
INNER JOIN 
  query_results AS step2
ON 
  step1.unique_user_id = step2.unique_user_id 
  AND step1.session_id = step2.session_id
  AND step1.event_timestamp < step2.event_timestamp
  AND step2.event_number = 2
WHERE 
  step1.event_number = 1
  AND step1.page_title != step2.page_title
GROUP BY  
  2,3
ORDER BY   
  1 DESC

• page_viewのイベントに絞った上で、ROW_NUMBER関数でイベント発生順に数字を付与していきます。
• WITH句で生成した結果を自己結合し、event_numberの値が1の場合初回閲覧記事、2の場合次に閲覧した記事と定義して絞り込んでいます。

まとめ

• 今回は、GoogleAnayltics4のデータをBigQueryで集計するお話を書かせていただきました。
• 筆者もまだまだ手探りのため、間違いなどございましたらコメントにてご指摘いただけますと幸いです。

1. WEBサービスとしての、GoogleAnalyticsのこと。GoogleAnalytics360やGoogleAnalytics4は、あくまでプロパティの名称でありサービス名ではない。 ↩ ↩²