デスクトップやモバイルの内訳データのセッション推移数を単体でモニタリングすることの他に、セグメントとしてデバイス情報を取得するにあたってのデータの集計方法について解説する。
GA4はセッションの最初のデバイス情報をレポート指標に採用している
GA4のセッションの仕様では、途中でデバイス／OS／ブラウザが変わってもセッションが切り替わることはない。ではその場合、どのデバイス情報が採用されているのかというと、最初のデバイス情報になる。これは参照元／メディアでも同様。
そのため、デバイス情報の取得にあたってはこの仕様を考慮しなければならず、単純にMAX関数やグルーピングして取得してしまうと、アルファベットに基づく順番で取得されてしまうため不正確となる。
デバイス情報のセグメントはセッションIDで結合
デバイスごとの集計を求めるにあたり、デバイス情報とセッションIDが対になった情報も同じSQLの中で集計できるようにしておくことで、セグメントとして活用することができる。セグメントとして活用するには、セッションIDをLEFT JOINして結合する。
デバイスごとの日次セッション数を集計するクエリ
WITH
DeviceSession_01 AS (
SELECT
ymd,
event_timestamp,
device,
CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
FROM (
SELECT
PARSE_DATE("%Y%m%d", event_date) AS ymd,
event_timestamp,
device.category AS device,
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))
)
),
DeviceSession_02 AS (
SELECT
ymd,
ssid,
ARRAY_AGG (
device IGNORE NULLS
ORDER BY
event_timestamp ASC
LIMIT
1
) [SAFE_OFFSET(0)] AS device,
FROM
DeviceSession_01
GROUP BY
ymd,
ssid
),
DeviceSession_03 AS (
SELECT
ymd,
device,
COUNT(DISTINCT ssid) AS _ssuu
FROM
DeviceSession_02
GROUP BY
ymd,
device
ORDER BY
ymd ASc,
device ASC,
_ssuu DESC
)
SELECT
*
FROM
DeviceSession_03
ChatGPTによるクエリ解説
ARRAY_AGGは、特定のセッションにおけるデバイス情報を取得するための処理を行っています。具体的には、セッションごとのデバイス情報を配列として集約しています。そして、その配列から最初の要素（最も古いイベントに基づくデバイス情報）を取り出しています。
### 解説
– **`ARRAY_AGG(… ORDER BY event_timestamp ASC LIMIT 1)`**: これは、各セッションにおけるイベントタイムスタンプが最も古いデータ（セッションの開始に最も近いデータ）に基づいてデバイス情報を集めることを指示しています。`ORDER BY event_timestamp ASC`により、イベントタイムスタンプが昇順で並べ替えられ（最も古いイベントが最初に来る）、`LIMIT 1`はその中の最初の要素だけを取得することを意味します。– **`IGNORE NULLS`**: これは`ARRAY_AGG`関数がNULLではない値だけを集計するようにするためのオプションです。つまり、デバイス情報が未記載（NULL）のイベントは集計から除外されます。
– **`[SAFE_OFFSET(0)]`**: `ARRAY_AGG`により作成された配列から、`SAFE_OFFSET(0)`を使って配列の最初の要素を安全に取り出します。`SAFE_OFFSET`は指定した位置に要素が存在しない場合にNULLを返すので、エラーが発生することなく処理が行われます。
デバイスごとのセッションIDを取得
左記のクエリから最終的にアウトプットさせるデータを1つ前の’DeviceSession_02’にすることでデバイスごとのセッションIDを集計することが可能になる。セグメントとして活用する際は、このセッションIDを元になるデータと結合する。
デバイス／OS／ブラウザ別の日次セッション数を集計するクエリ
WITH
DOB_Session_01 AS (
SELECT
ymd,
event_timestamp,
device,
os,
browser,
CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
FROM (
SELECT
PARSE_DATE("%Y%m%d", event_date) AS ymd,
event_timestamp,
device.category AS device,
device.operating_system AS os,
device.web_info.browser AS browser,
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))
)
),
DOB_Session_02 AS (
SELECT
ymd,
ssid,
ARRAY_AGG (
device IGNORE NULLS
ORDER BY
event_timestamp ASC
LIMIT
1
) [SAFE_OFFSET(0)] AS device,
ARRAY_AGG (
os IGNORE NULLS
ORDER BY
event_timestamp ASC
LIMIT
1
) [SAFE_OFFSET(0)] AS os,
ARRAY_AGG (
browser IGNORE NULLS
ORDER BY
event_timestamp ASC
LIMIT
1
) [SAFE_OFFSET(0)] AS browser,
FROM
DOB_Session_01
GROUP BY
ymd,
ssid
),
DOB_Session_03 AS (
SELECT
ymd,
device,
os,
browser,
COUNT(DISTINCT ssid) AS _ssuu
FROM
DOB_Session_02
GROUP BY
ymd,
device,
os,
browser
ORDER BY
ymd ASC,
device ASC,
_ssuu DESC
)
SELECT
*
FROM
DOB_Session_03
