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;
クエリ解説
上部の一時関数でパラメータとイベントを絞込み、パラメータごとに型名とその値までを集計する。
結果テーブル
- event_name
イベント名 - parameter_name
パラメータ名 - parameter_type
パラメータの型 - int_value
INT64型の値 - float_value
FLOAT64 型の値 - double_value
FLOAT64 型の値 - string_value
STRING型 - parameter_count
グループの行数