「日別のエンゲージのあったセッション数」を集計するSQLを書いてLooker Studioでグラフにする──GA4×BigQuery
BigQueryに連携したGA4のデータから「日別のセッション数」を集計して、Looker Studioでグラフにしてみましょう。
今回も「Googleが公開しているBigQuery連携したGA4デモデータに接続する|GA4×BigQuery」で紹介したデモデータに接続します。
BigQueryのコンソールで新しいエディタを開いたら、まずは下のクエリをコピペして「実行」してみてください。
SELECT
event_date,
COUNT(DISTINCT
CASE
WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'session_engaged') = '1' THEN CONCAT(user_pseudo_id,( SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id')) END
) AS engaged_sessions
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
GROUP BY
event_date
こんな感じにevent_dateごとのengaged_sessionが集計できました。
※できるだけシンプルに解説するために「日付を日本時間に変更する」といったことは今回はやりません。
このデータをLooker Studioに接続してシンプルな「日別のエンゲージのあったセッション数の棒グラフ」にするとこんな感じになります。
棒グラフだと「前の期間との比較」が表示できないので、折れ線グラフにしてみましょう。
「直近28日間」と「前の期間」の折れ線グラフができました。
ついでにトレンドラインも入れてみたので、セッション数の推移が視覚的にわかりやすくなったのではないでしょうか?
ひとつのグラフ内にどんな情報を入れ込むかは、読み取りたい情報の内容にもよるので、必要に応じて使いわけるのがいいですね。
より長期の推移を見るために、このデータが持っている全期間(92日間)の折れ線グラフにしてみました
こうするとセッション数の上がり下がりの傾向がよりハッキリとわかりますね。なんとなく「平日にセッションが増えて」「休日にセッションが減る」という傾向があるように見えます。
※擬似的に調整した日付なので、実際の曜日には連動していません。
以上、BigQuery連携したGA4の日別のエンゲージのあったセッション数を集計するSQLクエリを書いて、Looker Studioでシンプルな縦棒グラフ、折れ線グラフにするサンプルでした。
GA4やBigQuery連携したGA4のレポーティングなどご相談がありましたら、お気軽にお問い合わせください。