こんにちは、STORES でデータアナリストをしている@yougaiです。

サービスを運営していく中で、月次でKPIの目標を持っているチームは多いと思います。そこで月中に気になるのが、今の調子で月末に目標は達成できるのか、すなわち月末の着地数値です。

着地予測数値を用いて目標との差分をより早く認識することで、リカバーするための施策検討や現状の問題分析にいち早く取り組むことができるでしょう。

今回はBigQueryMLの時系列モデルを使った月末着地予測をご紹介します。記事内ではパブリックなデータセットを扱いますが、BigQueryで自社データを触れる方なら、少しクエリを変えるだけで自社データで試せる内容となっております。

BigQueryMLとは

cloud.google.com

機械学習モデルの作成からそのモデルを使った数値予測までBigQuery上で簡単に行うことができるというものです。弊社ではdigdagを用いてBigQuery上にデータウェアハウスを構築しており、BigQueryMLはSQLを実行するだけでいいので、digdagのジョブに組み込みやすいという利点があります。

扱うデータセット

BigQueryにデフォルトで用意されているニューヨーク市のシティバイクの利用ログを使用します。レンタサイクルサービスの利用ログで、いつ・どのステーションからどのステーションまで・どんな属性の人が利用したか記載されています。

利用開始日が2013年1月1日から2018年5月31日までデータがあり、今回は2018年4月15日までの実績があるとして、2018年4月の利用件数の月末着地予測を行います。

データ全体の利用件数推移を可視化したものが以下です。一部期間でデータ欠損があります。 ※BigQueryのコンソール画面で集計後、 データを探索 > データポータルで調べる からデータポータル上で可視化しています。

f:id:yougaihey:20201214185201p:plain

2018年4月に絞ったものが以下です。

f:id:yougaihey:20201214185331p:plain

また、毎回実績を集計するクエリを載せると長くなるため、今後記載するクエリでは、下記のクエリが最初に記されているとします。 2018年4月1日から2018年4月15日のdateに対して、その日の利用回数をvalue、その日までの合計利用回数をcumu_valueとして集計しています。

WITH data AS (
  SELECT
    date,
    value,
    SUM(value) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumu_value
  FROM
    (
      SELECT
        DATE(starttime) AS date,
        COUNT(*) AS value,
      FROM
        `bigquery-public-data.new_york_citibike.citibike_trips`
      WHERE
        DATE(starttime) BETWEEN '2018-04-01' AND '2018-04-15'
      GROUP BY
        1
    )
)

単純に割り戻す予測

BigQueryMLを使う前に2つの単純な予測を行います。

1. 月の中の経過日数で割り戻す

15日まで経過しているなら、15日までの実績に対して30日/15日=2倍を掛けるというものです。 dateに対して経過日数で割り戻し、その日時点での月末着地予測数値をforecast_valueとして、正解との比較用に4月の実績をreal_valueとして出したものが以下です。

WITH data AS (
  ...
)
SELECT
  date,
  cumu_value * EXTRACT(DAY FROM DATE_SUB(DATE_ADD(DATE_TRUNC(date, MONTH), INTERVAL 1 MONTH), INTERVAL 1 DAY)) / EXTRACT(DAY FROM date) AS forecast_value,
  (SELECT COUNT(*) FROM `bigquery-public-data.new_york_citibike.citibike_trips` WHERE DATE(starttime) BETWEEN '2018-04-01' AND '2018-04-30') AS real_value
FROM
  data

f:id:yougaihey:20201214185353p:plain

2018年4月は後半ほど利用回数が増えているため、月初のみの結果だと大きく過少予測しています。

2. 過去の実績で割り戻す

例えば去年の実績をみて、2017年4月の実績が150万、2017年4月1日から2017年4月15日の実績が50万だったなら、今年も同じような傾向だろうとして2018年4月15日までの実績に対して150万/50万=3倍を掛けるというものです。

※WITH句でdataテーブルを作成したのと同じ要領で、2017年4月1日から2017年4月30日の実績をdata_lastyearテーブルに集計した上でのクエリです。

WITH data AS (
  ...
), data_lastyear AS (
  ...
), rebate_ratios AS (
  -- 去年の実績から割り戻す率を計算する
  SELECT
    date,
    SUM(value) OVER() / cumu_value AS rebate_ratio
  FROM
    data_lastyear
)
SELECT
  data.date,
  cumu_value * rebate_ratio AS forecast_value,
  (SELECT COUNT(*) FROM `bigquery-public-data.new_york_citibike.citibike_trips` WHERE DATE(starttime) BETWEEN '2018-04-01' AND '2018-04-30') AS real_value
FROM
  data
  JOIN rebate_ratios ON EXTRACT(DAY FROM data.date) = EXTRACT(DAY FROM rebate_ratios.date)

f:id:yougaihey:20201214185422p:plain

使うデータの量が少ない月初はブレが大きいですが、ある程度データが溜まってきた月中では、±5%ほどに収まっています。前年ではなく前月の実績を使うこともできますし、簡単に着地予測を行うことができます。

BigQueryMLを使った予測

2020/07/01のリリースでBigQueryMLが時系列モデルに対応しました。 cloud.google.com

時系列モデルとは、時間経過によって変化するデータを予測するためのモデルで、季節や休日調整などを盛り込むことが可能です。

モデル作成

ご自身のプロジェクト下にml_testデータセットを作成し、そのデータセット下にcitibike_arimaモデルを作成します。学習に使うデータ量が少ないと季節性があまり学習されず精度もよくないので、2017年4月1日から2018年4月15日のデータを学習に使っています。

CREATE OR REPLACE MODEL `{{your_project_name}}.ml_test.citibike_arima`
OPTIONS
  (model_type = 'ARIMA',
   time_series_timestamp_col = 'date',
   time_series_data_col = 'value'
  ) AS
SELECT
  DATE(starttime) AS date,
  COUNT(*) AS value,
FROM
  `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
  DATE(starttime) BETWEEN '2017-04-01' AND '2018-04-15'
GROUP BY
  1

結果予測

horizonとして何日先までのデータを出すか、confidence_levelとして信頼区間を指定し、作ったcitibike_arimaモデルから結果を予測します。基本的にモデルから取り出して使う数値は、forecast_valueのみだと思います。

WITH data AS (
  ...
)
SELECT
  day,
  value AS real_value,
  NULL AS forecast_value,
  NULL AS prediction_interval_lower_bound,
  NULL AS prediction_interval_upper_bound
FROM
  data
UNION ALL
SELECT
  DATE(forecast_timestamp),
  NULL,
  forecast_value,
  prediction_interval_lower_bound,
  prediction_interval_upper_bound
FROM
  ML.FORECAST(MODEL `{{your_project_name}}.ml_test.citibike_arima`,
              STRUCT(15 AS horizon, 0.8 AS confidence_level))

f:id:yougaihey:20201214185500p:plain

2018年4月16日から2018年4月30日の実績とも比較してみましょう。

WITH data AS (
  ...
)
SELECT
  data.date,
  value AS real_value,
  forecast_value
FROM
  data
  LEFT JOIN (
    SELECT
      DATE(forecast_timestamp) AS date,
      forecast_value,
    FROM
      ML.FORECAST(MODEL `{{your_project_name}}.ml_test.test_arima`,
                  STRUCT(15 AS horizon, 0.8 AS confidence_level))
  ) AS forecast_data ON data.date = forecast_data.date

f:id:yougaihey:20201214185516p:plain

2018年4月の 実績利用回数: 1,307,543 に対して、 時系列モデルを利用した着地予測: 1,215,798 となり、単純な割り戻し着地予測とそれほど変わらない結果となってしまいました。

シティバイクの利用件数だと、気温や天気に大きく左右されることが考えられますし、広告費のかけ方やキャンペーンなど外部要因の影響もあるでしょう。

精度を向上させる改善点として、BigQueryMLの時系列モデルは、モデル作成時に1つ変数を加えるだけで複数の時系列モデルを一度に作成することができ、今回のデータセットだと出発ステーション毎に利用回数を予測するようなモデルや、自社サービスに置き換えるとユーザーの属性や継続期間などのセグメント毎にモデルを作ることが考えられます。

以下は、出発ステーション毎にモデルを作成するクエリです。

CREATE OR REPLACE MODEL `{{your_project_name}}.ml_test.citibike_multi_arima`
OPTIONS
  (model_type = 'ARIMA',
   time_series_timestamp_col = 'date', 
   time_series_id_col = 'start_station_id',
   time_series_data_col = 'value'
  ) AS
SELECT
  DATE(starttime) AS date,
  start_station_id,
  COUNT(*) AS value,
FROM
  `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
  DATE(starttime) BETWEEN '2017-04-01' AND '2018-04-15'
GROUP BY
  1, 2