日別のユーザーエンゲージメントを集計するSQLクエリを書いてLooker Studioでグラフにする｜GA4×BigQuery
BigQueryに連携したGA4のデータから「日別のユーザーエンゲージメント」を集計して、Looker Studioでグラフにしてみましょう。
ユーザーエンゲージメントとはGA4において「ユーザーがエンゲージメントした時間の長さ」を表します。
今回も「Googleが公開しているBigQuery連携したGA4デモデータに接続する｜GA4×BigQuery」で紹介したデモデータに接続します。
BigQueryのコンソールで新しいエディタを開いたら、まずは下のクエリをコピペして「実行」してみてください。
WITH
prep AS (
SELECT
event_date,
user_pseudo_id,
(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
KEY = 'ga_session_id') AS session_id,
MAX((
SELECT
value.string_value
FROM
UNNEST(event_params)
WHERE
KEY = 'session_engaged')) AS session_engaged,
SUM((
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
KEY = 'engagement_time_msec'))/1000 AS engagement_time_seconds
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
GROUP BY
event_date,
user_pseudo_id,
session_id)
SELECT
event_date,
SAFE_DIVIDE(SUM(engagement_time_seconds),COUNT(DISTINCT
CASE
WHEN session_engaged = '1' THEN CONCAT(user_pseudo_id,session_id)
END
)) AS engagement_time
FROM
prep
GROUP BY
event_date
こんな感じにevent_dateごとのengagement_timeが集計できました。
※できるだけシンプルに解説するために「日付を日本時間に変更する」といったことは今回はやりません。
このデータをLooker Studioに接続してシンプルな「日別のエンゲージメント時間の折れ線グラフ」にするとこんな感じになります。
時間なので集計方法を「持続時間（秒）」の平均に変更してあります。
「直近28日間」と「前の期間」の折れ線グラフができました。
ついでにトレンドラインも入れてみたので、セッション数の推移が視覚的にわかりやすくなったのではないでしょうか？
エンゲージメント時間が先月の途中から下がっているのがわかります。
ひとつのグラフ内にどんな情報を入れ込むかは、読み取りたい情報の内容にもよるので、必要に応じて使いわけるのがいいですね。
より長期の推移を見るために、このデータが持っている全期間（92日間）の折れ線グラフにしてみました。
週単位にまとめてみました。全期間でみると５週間前くらいから急にエンゲージメント時間が減っているのがわかりますね。なにが起こったのか深掘りしてみたくなりますね。
以上、BigQuery連携したGA4の日別のエンゲージメント時間を集計するSQLクエリを書いて、Looker Studioでシンプルな折れ線グラフにするサンプルでした。
GA4やBigQuery連携したGA4のレポーティングなどご相談がありましたら、お気軽にお問い合わせください。
