「曜日別のセッション数」を集計する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が集計できました。
※できるだけシンプルに解説するために「日付を日本時間に変更する」といったことは今回はやりません。
曜日を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のレポーティングなどご相談がありましたら、お気軽にお問い合わせください。
