コンテンツに進む

「曜日別のセッション数」を集計するSQLクエリを書いてLooker Studioでグラフにする|GA4×BigQuery

BigQueryに連携したGA4のデータから「日別のセッション数」を集計して、Looker Studioでグラフにしてみましょう。

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

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

SELECT
DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
FORMAT_TIMESTAMP('%A', TIMESTAMP_MICROS(event_timestamp)) AS day_of_the_week,
FORMAT_TIMESTAMP('%w', TIMESTAMP_MICROS(event_timestamp)) AS day_of_the_week_num,
COUNT(DISTINCT CONCAT(user_pseudo_id,(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
KEY = 'ga_session_id'))) AS sessions
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
GROUP BY event_date,
day_of_the_week,
day_of_the_week_num

こんな感じにevent_dateごとの、day_of_the_week、day_of_the_week_num、sessionsが集計できました。

「曜日別のセッション数」を集計するSQLクエリを書いてLooker Studioでグラフにする|GA4×BigQuery

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

曜日をday_of_the_weekとday_of_the_week_numのふたつの形式で取得しているのは、Looker Studioで並び替える際に数字表記のday_of_the_week_numがあったほうが便利だからです。

このデータをLooker Studioに接続してシンプルな「曜日別のセッション数の棒グラフ」にするとこんな感じになります。

全期間(92日間)にして、曜日ごとの傾向を見てみましょう。土日がわかるように土曜日=ブルー、日曜日=ピンクに色付けしてみました。

土日に色が付いたので周期性がはっきりしてきました。

ひとつのグラフ内にどんな情報を入れ込むかは、読み取りたい情報の内容にもよるので、必要に応じて使いわけるのがいいですね。

週単位で各曜日のセッションの比率を見るために、積み上げ縦棒グラフにしてみました。

これでも傾向がわかりますね。

※サンプルデータを元に擬似的に調整した日付なので、実際の曜日には連動していません。

以上、曜日別のセッション数を集計するSQLクエリを書いてLooker Studioでグラフにするサンプルでした。

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

記事に戻る