GA4で曜日別の平均セッション数を集計するSQLクエリ

本記事ではBigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、曜日別の平均セッション数を集計するSQLクエリについて解説をおこなう。

自社のサイトに訪問するユーザー行動の特徴を把握する上で基本となるSQLクエリである。

SQLクエリの概要

集計の手順としては、まずサブクエリ内で日次のセッション数を集計し、その際に event_date に対応する曜日（ DAYOFWEEK）を新たに抽出しておく。そしてメインクエリで曜日（ DAYOFWEEK）ごとにグループ化して、各曜日ごとの平均セッション数を算出する。

参考記事 : 日次のセッション数を集計するSQLクエリ | GA4QUERY

また、event_date はデータ型が文字列であるため、まずは PARSE_DATE 関数を用いて日付型に変換する。

PARSE_DATE('%Y%m%d', event_date)

公式リファレンス : PARSE_DATE関数

続いて、 EXTRACT 関数を用いて日付型に変換した event_date から曜日（ DAYOFWEEK）を抽出する。

EXTRACT(DAYOFWEEK FROM PARSE_DATE('%Y%m%d', event_date))

公式レファレンス : EXTRACT関数

EXTRACT 関数で曜日（ DAYOFWEEK）を抽出すると 1 ~ 7の数値が返される。それぞれ以下のように曜日と対応している。

1 : 日曜日
2 : 月曜日
3 : 火曜日
4 : 水曜日
5 : 木曜日
6 : 金曜日
7 : 土曜日

サンプルクエリでは、メインクエリにおいてCASE式を用いて数値から対応する曜日の表記に変換している。個人的に月曜始まりのほうがしっくりくるので ‘1. Monday’ と変換している。

今回はGoogleが用意しているGA4のサンプルデータセットを利用して集計をおこなっている。そのため、下記のSQLクエリサンプルをコピペしてそのままBigQueryで実行していただければ、同じ結果を得ることができるようになっている。

Google Merchandise Storeは Google ブランドの商品を販売するオンラインストアです。このサイトでは、Google アナリティクス 4 の標準のウェブ e コマースの実装と拡張計測機能が使用されます。BigQuery 一般公開データセットプログラムを通じて利用可能な ga4_obfuscated_sample_ecommerce データセットには、2020 年 11 月 1 日から 2021 年 1 月 31 日までの 3 か月間の難読化した BigQuery イベントエクスポートデータのサンプルが含まれています。

出典 : Google アナリティクス 4 e コマースウェブ実装向けの BigQuery サンプルデータセット

SQLクエリサンプル

今回のSQLクエリは、以下のようになる。

WITH prep AS (
   SELECT
    event_date,
    EXTRACT(DAYOFWEEK FROM PARSE_DATE('%Y%m%d', event_date)) AS day_of_week,
    COUNT(DISTINCT CONCAT(user_pseudo_id,  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) AS session_count
  FROM
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  GROUP BY event_date
  ORDER BY event_date
)

SELECT
  CASE WHEN day_of_week = 1 THEN '7. Sunday'
       WHEN day_of_week = 2 THEN '1. Monday'
       WHEN day_of_week = 3 THEN '2. Tuesday'
       WHEN day_of_week = 4 THEN '3. Wednesday'
       WHEN day_of_week = 5 THEN '4. Thursday'
       WHEN day_of_week = 6 THEN '5. Friday'
       WHEN day_of_week = 7 THEN '6. Saturday' 
  END AS day_of_week,
  ROUND(AVG(session_count), 0) AS avg_session
FROM prep
GROUP BY
  day_of_week
ORDER BY
  day_of_week
;

BigQueryでの集計結果

BigQueryでの集計結果は、以下のようになった。

ChatGPT（GPT-4モデル）によるSQL解説

ChatGPTによる解説を付与する。学習の参考にしてほしい。

このSQLクエリは、各曜日ごとの平均セッション数を計算し、その結果を曜日名とともに表示するものです。

まず、WITH句を使用して名前付きサブクエリprepを作成します。このサブクエリでは、以下の操作が行われています。

1.SELECT句で、イベントの日付（event_date）、その日付が週の何日目か（EXTRACT(DAYOFWEEK FROM PARSE_DATE(‘%Y%m%d’, event_date)) AS day_of_week）、およびその日のユニークなセッション数（COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘ga_session_id’))）を選択します。

2.FROM句で、データセットbigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*からデータを取得します。ここでを使って、events_で始まるすべてのテーブルを対象にしています。

3.GROUP BY句で、結果をevent_dateによりグループ化します。これにより、同一日付のレコードは一つにまとまります。

4.ORDER BY句で、結果をevent_dateの昇順に並べます。

次に、メインのクエリで以下の操作を行います。

1.SELECT句で、曜日（day_of_week）とその曜日の平均セッション数（ROUND(AVG(session_count), 0) AS avg_session）を選択します。ここでAVG関数を使用して各曜日の平均セッション数を計算し、ROUND関数を使用してその結果を四捨五入します。そして、曜日の数値を対応する曜日名に変換しています。この変換はCASE文を使用して行われています。

2.FROM句で、先に作成したサブクエリprepからデータを取得します。

3.GROUP BY句で、結果をday_of_weekによりグループ化します。これにより、同一曜日のレコードは一つにまとまります。

4.ORDER BY句で、結果をday_of_weekの昇順に並べます。

このクエリを実行することで、各曜日ごとの平均セッション数を曜日名とともに取得できます。これは、ウェブサイトやアプリのトラフィックが曜日によりどのように変化するかを理解するのに役立ちます。

今回の記事では、曜日別の平均セッション数を集計するSQLクエリについて解説を行った。

自社のサイトに訪問するユーザー行動の特徴を把握する上で基本となるSQLクエリなので、是非参考にしてほしい。

GA4で曜日別の平均セッション数を集計するSQLクエリ | GA4QUERY

SQLクエリの概要

SQLクエリサンプル

BigQueryでの集計結果

ChatGPT（GPT-4モデル）によるSQL解説