はじめに

はじめまして、駆け出しのデータエンジニアです。
今回は、よく使うGA4の値をBigQueryで出力してみました。
ドキュメントに記載があるものは見つけられた範囲で関連リンクを張っています。

この記事には、SQLに関する説明(CASE, Window関数など)は含まれていません。
またの機会にそれらの学びもアウトプットしたいと考えています。

また、今回紹介する内容はあくまで自己流のため、誤りがございましたら 優しく ご指摘頂けると幸いです

出力対象

GA4のディメンションと指標に記載があるものから、一部を対象としています。
今回紹介できなかったもので「これよく使う」というものがあれば、ぜひコメントで教えて下さい。

GA4で閲覧する指標と必ずしも一致するとは限りません。

前提

eコマースではないウェブサイト を前提としています。

データ構造の概要は以下の通りです。(データ型は省略します)

データセット: staging
テーブル: page_view
スキーマ:
    event_date,
    event_timestamp,
    page_location,
    user_pseudo_id,
    ga_session_id,
    ga_session_number,
    session_engaged

BigQueryにExportされるGA4のスキーマは以下を参照ください。

GA4からExportされたテーブルから、私はpage_viewテーブルを作成して利用しています。
過去の記事も是非参考にして下さい。

ディメンション

「先月のランディングページの一覧が見たい」

こんな時は閲覧数が多い順にランディングページを出力しましょう。

以下の ページビューの順序 を参考に。

SELECT
  landing_page,
  COUNT(1) AS page_view_count
FROM(
  -- timestampの順に並べ最初の値(URL)を取得
  SELECT DISTINCT
    FIRST_VALUE(page_location) OVER (
      PARTITION BY user_pseudo_id, ga_session_id
      ORDER BY event_timestamp ASC
    ) AS landing_page,
    user_pseudo_id,
    ga_session_id
  FROM `stagign.page_view`
  WHERE event_date BETWEEN '2023-08-01' AND '2023-08-31'
)
GROUP BY landing_page
ORDER BY page_view_count DESC

指標

「先月のイベント数(表示回数)を一ヶ月前と比較して見たい」

こんな時は対象期間を取り出して、それぞれで数えましょう。

-- event_name = 'page_view' の数
SELECT
  SUM(
    CASE WHEN event_date BETWEEN '2023-08-01' AND '2023-08-31' THEN 1 ELSE 0 END
  ) AS current_count,
  SUM(
    CASE WHEN event_date BETWEEN '2023-07-01' AND '2023-07-31' THEN 1 ELSE 0 END
  ) AS previous_count
FROM `staging.page_view`
WHERE event_date BETWEEN '2023-07-01' AND '2023-08-31'

「先月の1ユーザーあたりのページビュー数が見たい」

こんな時は1ユーザーごとの閲覧数を出力し、合計閲覧数をユーザー数で割った平均値を出しましょう。

SELECT
  SUM(event_count) / COUNT(*)
FROM (
  -- レコード数がユーザー数
  SELECT
    user_pseudo_id,
    COUNT(1) AS event_count
  FROM `staging.page_view`
  WHERE event_date BETWEEN '2023-08-01' AND '2023-08-31'
  GROUP BY user_pseudo_id
);

「先月のエンゲージメントのあったセッション数が見たい」

こんな時はsession_engagedが'1'のセッション数を数えましょう。
逆にエンゲージメントのないセッションを数えるときはNULLの存在に注意しましょう。

SELECT
  SUM(CASE WHEN session_engaged = '1' THEN 1 ELSE 0 END) AS engaged_count
  SUM(
    CASE WHEN session_engaged IS NULL OR session_engaged <> '1' THEN 1 ELSE 0 END
  ) AS not_engaged_count
FROM(
  SELECT user_pseudo_id, ga_session_id, MAX(session_engaged) AS session_engaged
  FROM `staging.page_view`
  WHERE event_date BETWEEN '2023-08-01' AND '2023-08-31'
  GROUP BY user_pseudo_id, ga_session_id
);

「先月のセッション数が見たい」

こんな時は対象期間のユーザーID&セッションIDのユニーク数を数えましょう

-- user_pseudo_id と ga_session_id のユニーク数
SELECT COUNT(*)
FROM(
  SELECT user_pseudo_id, ga_session_id
  FROM `staging.page_view`
  WHERE event_date BETWEEN '2023-08-01' AND '2023-08-31'
  GROUP BY user_pseudo_id, ga_session_id
);

「先月のユーザーあたりのセッション数が見たい」

こんな時は対象期間のセッション数をユーザー数で割った平均を出しましょう。

SELECT SUM(session_count) / COUNT(*)
FROM(
  -- レコード数はユーザー数
  SELECT user_pseudo_id, COUNT(DISTINCT ga_session_id) AS session_count
  FROM `staging.page_view`
  WHERE event_date BETWEEN '2023-08-01' AND '2023-08-31'
  GROUP BY user_pseudo_id
);

「先月のセッションあたりのページビュー数が見たい」

こんな時は対象期間の閲覧数の合計をセッション数で割った平均を出しましょう。

SELECT
  SUM(event_count) / COUNT(*)
FROM (
  -- レコード数はセッション数
  SELECT
    user_pseudo_id,
    ga_session_id,
    COUNT(1) AS event_count
  FROM `staging.page_view`
  WHERE event_date BETWEEN '2023-08-01' AND '2023-08-31'
  GROUP BY user_pseudo_id, ga_session_id
);

「先月の新規ユーザー数とリピーター数が見たい」

こんな時は対象期間でユーザーの最大セッション数を見ましょう。

1回目のセッションが初回という認識で。

セッション番号は、あるユーザーが現在のセッションまでに開始したセッションの数を示します（例: お客様のサイトで開始した 3 回目のセッション）。

SELECT
  SUM(CASE WHEN max_session_number = 1 THEN 1 ELSE 0 END) AS new_users, -- 新規ユーザー数
  SUM(CASE WHEN max_session_number > 1 THEN 1 ELSE 0 END) AS returning_users, -- リピーター数
FROM(
  SELECT user_pseudo_id, MAX(ga_session_number) AS max_session_number
  FROM `staging.page_view`
  WHERE event_date BETWEEN '2023-08-01' AND '2023-08-31'
  GROUP BY user_pseudo_id
);

「先月の合計ユーザー数が見たい」

こんな時は対象期間のユーザーID数を数えましょう。

ドキュメントにも記載がありました。

COUNT(DISTINCT user_pseudo_id)

-- user_pseudo_id のユニーク数
SELECT COUNT(*)
FROM(
  SELECT user_pseudo_id
  FROM `staging.page_view`
  WHERE event_date BETWEEN '2023-08-01' AND '2023-08-31'
  GROUP BY user_pseudo_id
);

まとめ

今回はBigQueryでよく使う汎用的なGA4指標を紹介致しました。
実際の業務では、個々のプロダクト毎に固有のディメンションや指標があると思います。
私は、上記で紹介した内容やプロダクトでよく使うデータは事前にテーブルとして作成して、すぐに簡単に使えるように用意しています。

最後まで読んで頂きありがとうございました。
これからもデータエンジニアとしての学びをアウトプットして行きます。

おまけ

データ利用者と対話をすることで見つかる頻出ワードが よく使うデータ なんだなと最近思いました。