「ユーザーごとのセッション数と表示回数」を集計するSQLを書いてLooker Studioで可視化する──GA4×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のレポーティングなどご相談がありましたら、お気軽にお問い合わせください。
