ユーザーごとのセッション数と表示回数を集計するSQLクエリを書いてLooker Studioで可視化する｜BigQuery

BigQuery連携したGA4のユーザーごとのセッション数と表示回数（ページビュー）を集計して、Looker Studioでビジュアライズしてみましょう。

今回も「Googleが公開しているBigQuery連携したGA4デモデータに接続する｜GA4×BigQuery」で紹介したデモデータに接続します。

BigQueryのコンソールで新しいエディタを開いたら、まずは下のクエリをコピペして「実行」してみてください。

SELECT
user_pseudo_id,
COUNTIF(event_name = 'session_start') AS sessions,
COUNTIF(event_name = 'page_view') AS pageviews
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
GROUP BY user_pseudo_id

こんな感じにuser_pseudo_idごとのsessionsとpageviewsが集計できました。

※できるだけシンプルに解説するために「日付を日本時間に変更する」といったことは今回はやりません。

このデータをLooker Studioに接続してシンプルな「ユーザーごとのセッション数と表示回数の表」にするとこんな感じになります。

セッションは最大12回ということがわかりました。表示回数に差がありそうなので、「セッションあたりの表示回数」を計算フィールドで作ってヒートマップにしてみましょう。

計算フィールドで計算した「セッションあたりの表示回数」を表に追加します。

ひとり、１回のセッションで500ページビュー以上しているユーザーがいますね。botでしょうか？

１回のセッションで100ページも閲覧するのは例外なので、現実的なラインでフィルタしてみましょう。

今回はセッションあたりの表示回数が50ページ以上のユーザーIDは、なんらかの機械的なセッションと見なします（もちろんそのサイトの内容にもよります）。

これで実際にありそうなユーザーのサイト閲覧状況の表ができました。

以上、ユーザーごとのセッション数と表示回数を調べてLooker Studioで可視化する方法の解説でした。

GA4やBigQuery連携したGA4のレポーティングなどご相談がありましたら、お気軽にお問い合わせください。