BigQueryテクニック集 – 欠落日付の補完

BigQuery

and,a株式会社 デジタルマーケティングエンジニア 佐藤洋太

GA4のSQLを開発で日付の出力の際にこんな経験はありませんか?
「日付が歯抜けで抽出されている?」
「飛び飛びで日付が生成されていて綺麗に1日おきにデータがない」
今回はデータ分析時における欠落日付の補完のお悩みを解決したいと思います。

準備

ごく簡単な例です。
まずはこのようなテーブルがあるとしましょう。

dateurlcount
12022-09-01https://www.example.com/page01/2
22022-09-02https://www.example.com/page01/3
32022-09-03https://www.example.com/page01/4
42022-09-05https://www.example.com/page01/6
52022-09-07https://www.example.com/page01/8

内容はpage01の日付ごとのページビュー数(ないしイベント数)とします。
注目するポイントは2022/09/04と2022/09/06のレコードがない点です。
このような欠落日付のレコードは分析時に取り回しが非常に悪いです。

では、2022/09/04と2022/09/06にcountが0と表示され分析時に取り回しがしやすいよう工夫をしてみましょう。

実践

SQLの処理順としては以下のようになります。

  1. 期間の日付を格納した配列を作成
  2. 欠落日付を含んだテーブルを作成
  3. 前述の2工程を結合。この際に値を変換

実際のコードはこちらです。

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
12022-09-01
22022-09-02
32022-09-03
42022-09-04
52022-09-05
62022-09-06
72022-09-07

次に先ほどの準備で用意したテーブルをそのまま抽出します。

tbl AS (
  SELECT date, url, count
  FROM `project01.sample.table01` 
)

最後に工程1、2で抽出したテーブルを結合します。

SELECT 
  date,
  COALESCE(url, "https://www.example.com/page01/") AS url,
  COALESCE(count, 0) AS count,

FROM duration
LEFT JOIN tbl USING (date)

この時、コード上ではCOALESCEを使って値の置換を行っていますが、使わないとどうなるかの比較を以下に添付します。

置換なし

dateurlcount
12022-09-01https://www.example.com/page01/2
22022-09-02https://www.example.com/page01/3
32022-09-03https://www.example.com/page01/4
42022-09-04nullnull
52022-09-05https://www.example.com/page01/6
62022-09-06nullnull
72022-09-07https://www.example.com/page01/8

置換あり

dateurlcount
12022-09-01https://www.example.com/page01/2
22022-09-02https://www.example.com/page01/3
32022-09-03https://www.example.com/page01/4
42022-09-04https://www.example.com/page01/0
52022-09-05https://www.example.com/page01/6
62022-09-06https://www.example.com/page01/0
72022-09-07https://www.example.com/page01/8

分析時において取り回しがしやすくなっていることが想像できると思います。

GA4でページビュー数やイベント数を抽出する際に有用です。
今回は欠落日付を補完するテクニックのご紹介でした。

関連記事

特集記事

コメント

この記事へのコメントはありません。

ランキング(週間)

  1. 1

    【UA→GA4】プロパティ 自動作成  2023.3.1 GA4 補完計画

  2. 2

    GA4 × ChatGPT 爆速!GA4 設定 & 解析 革命 第1回

  3. 3

    GA4の指標「総ユーザー数」VS「アクティブ ユーザー数」

  4. 4

    ユニバーサル アナリティクス終了 過去データはどうなる?

  5. 5

    GA4 「ページ ロケーション」と「ページ パス と スクリーン クラス」何が違う?ドメインの有無以外にもパラメータの扱いに違いあり

  6. 6

    GA4の探索で「ページ別訪問数」を見ることは可能か?

  7. 7

    GA4 レポート用識別子、user_id の活用

  8. 8

    GA4 & Looker Studio(旧データポータル) 「割り当てエラー」問題 原因と打開策「データの抽出機能」(2023年1月14日 修正)

  9. 9

    GA4 拡張計測機能の新顔「フォームの操作」 form_start, form_submit

  10. 10

    GA4で異なるドメイン(サブドメイン)の同一ページパスをドメイン付きで表示する

最近の記事

  1. GA4 × ChatGPT 爆速!GA4 設定 & 解析 革命 第1回

  2. ユニバーサル アナリティクス終了 過去データはどうなる?

  3. 【UA→GA4】プロパティ 自動作成  2023.3.1 GA4 補完計画

カテゴリー

 
TOP