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

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

  2. 2

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

  3. 3

    GA4 クロスドメイン設定では、リンク先に「_gl」パラメータが付く。勝手なパラメータを付けるとエラーになるページは要注意

  4. 4

    GA4のレポート画面の数値データは、イベント発生時から何時間後に確定するのか?

  5. 5

    GA4で設定したイベントの消し方(削除の仕方)

  6. 6

    「GA4【無料】オンラインセミナー」セミナーアーカイブ(登録不要)

  7. 7

    ZOOMでの オンラインセミナー 参加方法

  8. 8

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

  9. 9

    ECサイトのゴールデンルートを発見する方法 – Google Analytics 4とPythonを活用して(第2回)

  10. 10

    YouTubeの概要欄に貼られたリンクをクリックしてサイトを訪れたユーザーを計測するために

最近の記事

  1. 生成AIを調整してABテストを実装したら想像以上に早かった

  2. 無料で使えるABテストツールのバージョンアップを行いました

  3. 無料ABテストツール(and,B)のマニュアルを作成しました

カテゴリー

 
TOP