and,a株式会社 デジタルマーケティングエンジニア 佐藤洋太
GA4のSQLを開発で日付の出力の際にこんな経験はありませんか？
「日付が歯抜けで抽出されている？」
「飛び飛びで日付が生成されていて綺麗に1日おきにデータがない」
今回はデータ分析時における欠落日付の補完のお悩みを解決したいと思います。
準備
ごく簡単な例です。
まずはこのようなテーブルがあるとしましょう。
| 行 | date | url | count |
|---|---|---|---|
| 1 | 2022-09-01 | https://www.example.com/page01/ | 2 |
| 2 | 2022-09-02 | https://www.example.com/page01/ | 3 |
| 3 | 2022-09-03 | https://www.example.com/page01/ | 4 |
| 4 | 2022-09-05 | https://www.example.com/page01/ | 6 |
| 5 | 2022-09-07 | https://www.example.com/page01/ | 8 |
内容はpage01の日付ごとのページビュー数（ないしイベント数）とします。
注目するポイントは2022/09/04と2022/09/06のレコードがない点です。
このような欠落日付のレコードは分析時に取り回しが非常に悪いです。
では、2022/09/04と2022/09/06にcountが0と表示され分析時に取り回しがしやすいよう工夫をしてみましょう。
実践
SQLの処理順としては以下のようになります。
- 期間の日付を格納した配列を作成
- 欠落日付を含んだテーブルを作成
- 前述の２工程を結合。この際に値を変換
実際のコードはこちらです。
WITH
duration AS (
SELECT *
FROM UNNEST(GENERATE_DATE_ARRAY( DATE('2022-09-01'), DATE('2022-09-07') )) AS date
),
tbl AS (
SELECT date, url, count
FROM `project01.sample.table01`
)
SELECT
date,
COALESCE(url, "https://www.example.com/page01/") AS url,
COALESCE(count, 0) AS count,
FROM duration
LEFT JOIN tbl USING (date)解説
今回の肝となる部分です。
duration AS (
SELECT *
FROM UNNEST(GENERATE_DATE_ARRAY( DATE('2022-09-01'), DATE('2022-09-07') )) AS date
),2022/09/01〜2022/09/07の期間の配列をGENERATE_DATE_ARRAYというBigQueryの関数で生成し、UNNESTにより展開しています。
分析の期間を変更することで任意の期間の配列を生成できます。
GENERATE_DATE_ARRAYは非常に便利な関数ですので覚えておきましょう
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#generate_date_array
そうすることで、下記のような1日ずつの日付レコードが生成されます。
| 行 | date |
|---|---|
| 1 | 2022-09-01 |
| 2 | 2022-09-02 |
| 3 | 2022-09-03 |
| 4 | 2022-09-04 |
| 5 | 2022-09-05 |
| 6 | 2022-09-06 |
| 7 | 2022-09-07 |
次に先ほどの準備で用意したテーブルをそのまま抽出します。
tbl AS (
SELECT date, url, count
FROM `project01.sample.table01`
)最後に工程１、２で抽出したテーブルを結合します。
SELECT
date,
COALESCE(url, "https://www.example.com/page01/") AS url,
COALESCE(count, 0) AS count,
FROM duration
LEFT JOIN tbl USING (date)この時、コード上ではCOALESCEを使って値の置換を行っていますが、使わないとどうなるかの比較を以下に添付します。
置換なし
| 行 | date | url | count |
|---|---|---|---|
| 1 | 2022-09-01 | https://www.example.com/page01/ | 2 |
| 2 | 2022-09-02 | https://www.example.com/page01/ | 3 |
| 3 | 2022-09-03 | https://www.example.com/page01/ | 4 |
| 4 | 2022-09-04 | null | null |
| 5 | 2022-09-05 | https://www.example.com/page01/ | 6 |
| 6 | 2022-09-06 | null | null |
| 7 | 2022-09-07 | https://www.example.com/page01/ | 8 |
置換あり
| 行 | date | url | count |
|---|---|---|---|
| 1 | 2022-09-01 | https://www.example.com/page01/ | 2 |
| 2 | 2022-09-02 | https://www.example.com/page01/ | 3 |
| 3 | 2022-09-03 | https://www.example.com/page01/ | 4 |
| 4 | 2022-09-04 | https://www.example.com/page01/ | 0 |
| 5 | 2022-09-05 | https://www.example.com/page01/ | 6 |
| 6 | 2022-09-06 | https://www.example.com/page01/ | 0 |
| 7 | 2022-09-07 | https://www.example.com/page01/ | 8 |
分析時において取り回しがしやすくなっていることが想像できると思います。
GA4でページビュー数やイベント数を抽出する際に有用です。
今回は欠落日付を補完するテクニックのご紹介でした。
コメント