🚴‍♂️

BigQueryで営業日を考慮したリードタイムを計算する

2022/06/29に公開

SQL

データ分析

BigQuery

tech

リードタイムを計算する

会員登録→購入、応募→採用、初回購入→2回目購入など、顧客のリードタイム分析を行う際に、簡易に集計するのであれば、DATE_DIFF関数を利用することで簡単に2期間の差分を出すことができる。
ただし、BtoB業種や、週の中で定休日がある店舗などでは、営業日に限定したリードタイムを計算したいということもあるだろう。

NETWORKDAYS.INTL関数

ExcelやGoogle スプレッドシートには、NETWORKDAYS.INTLという、特定の曜日 & 祝日リストを排除して営業日計算してくれる便利な関数がある。

BigQueryでもこれを再現できないかと思い、作ってみた。

集計の方針

大まかな集計のながれ

祝日・定休日カレンダーテーブルを作成
レコード（ユーザー）ごとに開始日～終了日の日単位のカレンダーを作成
2のカレンダーに1のカレンダーテーブルをLEFT JOIN＆日付ごとに曜日を出力し、定休日にフラグを立てる
ユーザー単位の日付の個数から3で求めたフラグの個数を減算する。

上記の流れでユーザーID単位でのリードタイムが出せるので、元のテーブルにユーザーIDなどをキーにJOINして分析する。

サンプルコード

WITH date_term AS (
  SELECT
    entry_user_id,
    CAST(start_date AS DATE) from_day, -- start_date に開始日を入力
    CAST(end_date AS DATE) to_day,  -- end_date に締め日を入力
    (CASE WHEN end_date IS NOT NULL THEN 1 ELSE 0 END) has_to_day
  FROM
    `project.dataset.table`
),

date_arr AS (
  SELECT
    entry_user_id,
    (CASE WHEN has_to_day = 1 THEN GENERATE_DATE_ARRAY(from_day, to_day, INTERVAL 1 DAY) ELSE NULL END) date_term_arr
  FROM
    date_term
),

date_unnest AS (
  SELECT
    entry_user_id,
    date_term,
    EXTRACT(DAYOFWEEK FROM date_term) date_term_day_of_week
  FROM
    date_arr, UNNEST(date_term_arr) AS date_term
),

add_holiday_flag AS (
  SELECT
    d.*,
    (CASE
      WHEN h.date IS NOT NULL THEN 1 -- holiday_listが突合された日付
      WHEN d.date_term_day_of_week = 1 THEN 1 --Sunday
      WHEN  d.date_term_day_of_week = 7 THEN 1 --Saturday
      ELSE 0 END) holiday_cnt
  FROM
    date_unnest AS d
    LEFT JOIN `project.dataset.table` AS h
      ON d.date_term = h.date
)

SELECT
  entry_user_id,
  (CASE
    -- 定休日に対応したなどのイレギュラーが発生するとマイナスになるため、
    -- 差分0の場合には0を返す
    WHEN COUNT(date_term) - SUM(holiday_cnt) = 0 THEN 0
    -- 開始日と締め日が当日になった場合のために1を引く
    ELSE COUNT(date_term) - SUM(holiday_cnt) - 1 END) lead_time
FROM
  add_holiday_flag
GROUP BY
  1

もっとシンプルに書ける方法があったら教えてほしいです。