GA4

【GA4SQL】GENERATE_DATE_ARRAY関数で日付テーブルを作成

2024.04.07 2024.05.15

BigQueryのGENERATE_DATE_ARRAY関数を用いてGA4の日付テーブルを作成する方法についての解説。この日付テーブルの用途としては「日付欠けのない」テーブルを作成することで、「日付欠け」をなくすことになる。

日付欠けの何が問題なのか？
BigQuery GENERATE_DATE_ARRAY関数とは
単純な日付生成SQL
GA4のページごとの日付生成SQL

日付欠けの何が問題なのか？

日付が欠けている状態でビジュアライズすると、日付が欠けていても日付が連続してみえるため読み手側に誤解を与える可能性があるため、あまりよいビジュアライズ表現とはいえなくなるため。ディメンションに日付を置いているのであれば、日付は「連続」として潜在的に処理されてしまうので、日付が存在していなくともデータが存在しているものと誤った解釈を与えてしまう恐れがあるため、この問題に対処するための処理となる。

BigQuery GENERATE_DATE_ARRAY関数とは

日付データを生成するために1からコードを書く必要はなく、日付の配列を返すBigQueryのGENERATE_DATE_ARRAY関数が用意されているため、これを使う。

参考：GENERATE_DATE_ARRAY -BigQuery > 配列関数

単純な日付生成SQL

WITH
  gen_date AS (
    SELECT
      generate_date
    FROM(
      SELECT
        GENERATE_DATE_ARRAY(PARSE_DATE("%Y%m%d", '20240401'), DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS date_array
    )
    ,UNNEST(date_array) AS generate_date
  )

SELECT * FROM gen_date

GA4のページごとの日付生成SQL

GA4のページごとの開始日と終了日を取得して日付を生成する。開始日と終了日はページビューが発生した日付とする。このテーブルを日付マスターテーブルとしてして、LEFT JOINで結合することで日付とページをディメンションを持つ日付欠けのないテーブルを生成することが可能になる。

WITH
  PageDateRange AS (
    SELECT
      page_location_noparam AS page_location,
      generate_date
    FROM (
      SELECT
        page_location_noparam,
        start_date,
        end_date      
      FROM (
        SELECT
          REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location_noparam,
          MIN(PARSE_DATE("%Y%m%d", event_date)) AS start_date,
          MAX(PARSE_DATE("%Y%m%d", event_date)) AS end_date
        FROM
          `<project>.<dataset>.events_*`
        WHERE
          event_name = 'page_view'
        GROUP BY
          page_location_noparam
      )
    ),
    UNNEST(GENERATE_DATE_ARRAY(start_date, end_date)) AS generate_date
    ORDER BY
      page_location,
      generate_date ASC
  )

  SELECT * FROM PageDateRange