GA4

【GA4 SQL】キーイベントを達成したセッション数をBigQueryから集計

2024.06.24

GA4における「コンバージョン」は「キーイベント」へと名称が変更された。どちらとも、もとになっているのは「イベント」であり、そのイベントに意味付けをしたものがコンバージョンなりキーイベントとなる。BigQueryにエクスポートされたGA4のデータでは、このような意味付けされたイベントという概念はないため、キーイベントの集計をする際は、該当するイベントを条件指定して集計することになる。そのため、これがBigQueryを活用することの大きなメリットとなるが、キーイベントとして設定していないイベントであっても過去に遡りイベントに対して自由にキーイベントとして扱えることが大きな利点となる。

また当クエリでは、集計したいキーイベントは、すでにイベントとして設定済みであることを前提としている。その箇所が event_name = ‘Key_event_A’ に該当するが、仮にイベントとしてGA4側で未設定であったとしても、クエリの中でキーイベントとして見立てたいアクションを指定すれば集計することができるため、このような柔軟性もBigQueryを活用するメリットとなる。

キーイベントの日次セッション数を集計するクエリ
- ChatGPTによるクエリ解説

キーイベントの日次セッション数を集計するクエリ

WITH
  CV_ssuu AS (
    SELECT
      ymd,
      COUNT(DISTINCT ssid) AS _cv_ssuu
    FROM (
      SELECT
        ymd,
        CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
      FROM (
        SELECT
            PARSE_DATE("%Y%m%d", event_date) AS ymd,
            user_pseudo_id,
            (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
        FROM
          `<project>.<dataset>.events_*`
        WHERE
            _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
          AND (
              event_name = 'Key_event_A'
            OR
              event_name = 'Key_event_B'
          )
      )
    )
    GROUP BY
      ymd
    ORDER BY
      ymd ASC
  )

SELECT
  *
FROM
  CV_ssuu

ChatGPTによるクエリ解説

このクエリはGoogle Analytics 4 (GA4) データを使用して、特定のイベントを達成したセッション数を日次で集計するために設計されています。以下にそのステップごとの解説を行います：

### 1. 内部の最も深いサブクエリ
– この部分では、GA4のイベントデータから必要な情報を取得しています。
– `PARSE_DATE(“%Y%m%d”, event_date)` でイベント発生日を年月日形式で抽出しています。
– `user_pseudo_id` はユーザー識別子で、セッション識別に使われる `ga_session_id` はイベントパラメータから抽出しています。
– `WHERE` 句で、データの期間を指定し、さらに特定のイベント（`Key_event_A` または `Key_event_B`）が発生したレコードのみをフィルタリングしています。

### 2. セッションID (`ssid`) の作成
– `CONCAT(user_pseudo_id, ‘-‘, CAST(ga_session_id AS STRING))` を使用して、ユーザーIDとセッションIDからユニークなセッションID (`ssid`) を生成しています。
– この `ssid` は各ユーザーの各セッションをユニークに識別するために用いられます。

### 3. セッション数の集計
– 外側のサブクエリで `ssid` をグルーピングして、日次 (`ymd`) ごとにユニークなセッション数を `COUNT(DISTINCT ssid)` を使って数えています。
– この集計により、指定された2つのイベントのいずれかを達成したユニークなセッションの数が日毎に算出されます。

### 4. 結果の出力
– 最終的に、計算された日付 (`ymd`) とセッション数 (`_cv_ssuu`) を含む結果が選択されて出力されます。
– `ORDER BY ymd ASC` で結果を日付順に並べて表示しています。

このクエリは、特定のイベントを達成したユーザーセッションの日次トレンドを把握するために有用です。それにより、これらのイベントの影響を時系列で分析することが可能となります。