GA4

【GA4SQL】イベント・パラメータの対応一覧を確認

2024.09.27

GA4のイベントとパラメータの対応を確認したいことがまあまあの頻度であるので、それをSQLで抽出するクエリ。BigQueryにエクスポートされたテーブルを直接確認してもよいが、ある程度の期間でまとめてグルーピングすることで抜け漏れをなくせるのと、パラメータに紐づくイベントを確認したいときはテーブルを直接みても把握しにくいためBigQueryのコンソールにおける確認用クエリ。

パラメータに紐づくイベントを抽出するクエリ
- クエリ解説
イベントに紐づくパラメータを抽出するクエリ
- クエリ解説
パラメータの詳細を抽出するクエリ
- クエリ解説
- 結果テーブル

パラメータに紐づくイベントを抽出するクエリ

CREATE TEMP FUNCTION param() RETURNS STRING AS ('entrances');     -- パラメータ
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');  -- 開始日
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20240430');    -- 終了日

WITH
  event_parm_1 AS(
    SELECT
      event_name
    FROM
      `<project>.<dataset>.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN date_from() AND date_to()    
    GROUP BY
      event_name
  ),
  event_parm_2 AS (
    SELECT
      event_name,
      event_params.key AS event_params
    FROM
      `<project>.<dataset>.events_*`, UNNEST(event_params) AS event_params
    WHERE
        _TABLE_SUFFIX BETWEEN date_from() AND date_to()
      AND
        event_params.key = param()
    GROUP BY
      event_name,
      event_params
)

SELECT
  x.event_name,
  y.event_params
FROM
  event_parm_1 AS x
LEFT JOIN
  event_parm_2 AS y
ON x.event_name = y.event_name
ORDER BY
 x.event_name ASC

クエリ解説

検証したいパラメータを上部の一時的関数であるparam()の戻り値に設定する。ここでは’entrances’パラメータを設定している。開始日と終了日には1週間から1ヶ月間の間隔を設ければ十分だろう。また、期間を設定する理由としては抽出クエリ量の削減のために期間を絞る必要がある。

この結果から’entrances’パラメータは’page_view’イベントに紐づくということが分かった。もちろん、「サイト」における結果であるので「アプリ」の場合は異なる結果が得られるだろう。’entrances’パラメータが’page_view’イベントに紐づくのであれば、条件として’page_view’イベントを指定すればよいという判断が取れる。

イベントに紐づくパラメータを抽出するクエリ

CREATE TEMP FUNCTION event() RETURNS STRING AS ('first_visit');   -- イベント
CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240301');  -- 開始日
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20240430');    -- 終了日

SELECT
  event_name,
  event_params.key AS event_params
FROM
  `<project>.<dataset>.events_*`, UNNEST(event_params) AS event_params
WHERE
    event_name = event()
  AND
    _TABLE_SUFFIX BETWEEN date_from() AND date_to()  
GROUP BY
  event_name,
  event_params
ORDER BY
  event_name

クエリ解説

検証したいイベントを上部の一時的関数であるevent()の戻り値に設定する。結果は指定した期間でイベントで紐づいたパラメータを取得するため、そのイベントで必ず毎回、そのパラメータが取得できているとは限らないことは留意する必要がある。

パラメータの詳細を抽出するクエリ

さらにパラメータの詳細を抽出したい場面に遭遇することが多く、上記のクエリ結果に加えてパラメータに紐づく情報を取得できるクエリを作成した。

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20240401');  -- 開始日
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20240930');    -- 終了日
CREATE TEMP FUNCTION parameter_name() RETURNS STRING AS ('session_engaged');  -- パラメータ名
--CREATE TEMP FUNCTION event_name_list() RETURNS ARRAY<STRING> AS (['session_start']); -- 単一のイベントを検証
CREATE TEMP FUNCTION event_name_list() RETURNS ARRAY<STRING> AS (['session_start', 'page_view', 'click', 'first_visit', 'scroll', 'user_engagement']);  -- 複数イベント名を検証
--CREATE TEMP FUNCTION event_name_list() RETURNS ARRAY<STRING> AS (['']);  -- 空欄指定で全イベント抽出

WITH
  parameter_types AS (
    SELECT
      event_name,
      param.key AS parameter_name,
      param.value.int_value AS int_value,
      param.value.float_value AS float_value,
      param.value.double_value AS double_value,
      param.value.string_value AS string_value,
      CASE 
        WHEN param.value.int_value IS NOT NULL THEN 'int_value'
        WHEN param.value.float_value IS NOT NULL THEN 'float_value'
        WHEN param.value.double_value IS NOT NULL THEN 'double_value'
        WHEN param.value.string_value IS NOT NULL THEN 'string_value'
        ELSE 'unknown'
      END AS parameter_type,
      COUNT(*) AS parameter_count
    FROM
      `<project>.<dataset>.events_*`,
      UNNEST(event_params) AS param
    WHERE
      _TABLE_SUFFIX BETWEEN date_from() AND date_to()
      -- イベント名が空配列でない場合にのみフィルタを適用。空配列の場合はすべてのイベントを表示
      AND (ARRAY_LENGTH(event_name_list()) = 1 AND event_name_list()[OFFSET(0)] = '' OR event_name IN UNNEST(event_name_list()))
    GROUP BY
      event_name,
      parameter_name,
      int_value,
      float_value,
      double_value,
      string_value,
      parameter_type
)

SELECT
  event_name,
  parameter_name,
  parameter_type, 
  int_value,
  float_value,
  double_value,
  string_value,
  parameter_count
FROM
  parameter_types
WHERE
  parameter_name = parameter_name()
ORDER BY
  event_name,
  parameter_name;