売上データからSQLで LTV 分析データを作る

以前 Google Cloud ブログの 〜AutoMLで実践する〜 ビジネスユーザーのための機械学習入門シリーズ 【第 2 回】AutoML Tables ではじめる LTV 分析入門という記事で AutoML による Life Time Value(以降 LTV) 分析についてご紹介しましたが、実際に実務で LTV 分析を行うには、売上データを変換して 分析用に整形する必要があります。このブログでは、このように売上や収益のデータを BigQuery で LTV 分析用に変換する例をご紹介します。

サンプルデータセット取得

ここでは、UCI が公開している Online Retail Data Set を使う例をご紹介します。まず、Excel 形式として提供されているデータをダウンロードします。Google Spreadsheet の「インポート > アップロード」メニューを開き、ダウンロードしたファイルを取り込みます。

このファイルを取り込むと、以下のような列で構成されていることが分かります。各商品ごとの売上が記録されているので、商品単価と商品数を掛け算し、InvoiceNo ごとに集計することで、各トランザクションの合計金額が計算できそうです。

• InvoiceNo: 請求書番号
• StockCode: 商品 (アイテム) コード
• Descripsion：商品（アイテム）名
• Quantity: トランザクションごとの各製品 (アイテム) の数量
• InvoiceDate: 請求の日付と時刻。数値、各トランザクションが生成された日時
• UnitPrice: 商品当たりの製品価格 (英ポンド)
• CustomerID: 顧客番号
  Country: 国名

BigQuery テーブル作成

BigQueryの任意のデータセット（この例ではonlineretailというデータセットを作成しています）を選択し、「テーブルを作成」リンクを開きます。次に、以下のように 上記のスプレッドシートをソースとして「raw_retail_online」という名称のテーブルを作成してください。この時、スキーマを自動検出するオプションをチェックし、スキップするヘッダー行に1を指定してください。

この状態では、スプレッドシートが外部テーブルとなっていますが、もう少しBigQueryで扱いやすくするために以下のSQLでbaseという名称のBigQueryテーブルにしておきます。

CREATE OR REPLACE TABLE onlineretail.base AS
SELECT * FROM onlineretail.raw_retail_online

データの前処理

上の手順で取り込んだ base テーブルを以下の SQL で整形します。ここでは、2011年8月8日までに購入を行なっている顧客のデータを抽出しています。データセット名はご自身の環境にあわせて変更ください。

CREATE OR REPLACE TABLE onlineretail.data_cleaned AS
WITH a AS
(
 -- 顧客IDごとに購買日、購買金額、購買数、直近の購買日を抽出
 SELECT CustomerID AS customer_id,
 CAST (InvoiceDate AS DATE)  AS order_date,
 ROUND(SUM(UnitPrice * Quantity), 2) AS order_value,
 SUM(Quantity) AS order_qty_articles,
 (
  SELECT MAX(InvoiceDate)
  FROM onlineretail.base tl
  WHERE tl.CustomerID = t.CustomerID
 ) latest_order
 FROM onlineretail.base t
 GROUP BY CustomerID, order_date
), 
b AS
(
 -- 基準日より前に少なくとも一回購入していた顧客IDのみ抽出
 SELECT CustomerID
 FROM (
  -- 顧客ごとに一円以上の購入があった回数を集計
  SELECT CustomerID,
  SUM(positive_value) cnt_positive_value
  FROM (
   -- 1円以上の購入があったか（返品でない)フラグを positive_value として作成
   SELECT
   CustomerID,
   (CASE WHEN SUM(UnitPrice * Quantity) > 0 THEN 1 ELSE 0 END ) positive_value
   FROM onlineretail.base
   WHERE
   CAST(InvoiceDate AS DATE) < CAST("2011-08-08" AS DATE)
   GROUP BY CustomerID,CAST (InvoiceDate AS DATE))
   GROUP BY CustomerID )
  WHERE cnt_positive_value > 1
)
SELECT customer_id, 
CAST(latest_order AS DATE) latest_order,
order_date, 
order_value, 
order_qty_articles
FROM a INNER JOIN  b
ON  a.customer_id = b. CustomerID
--最終日から９０日以内に購入している人のみ抽出
WHERE DATE_DIFF(CAST("2011-12-12" AS DATE), CAST(latest_order AS DATE), DAY) <= 90
-- 購入金額と数に不正な値がないか確認
AND   (order_qty_articles > 0 and order_value > 0) OR (order_qty_articles < 0 and order_value < 0)

さらに、LTV分析に必要なmonetary, recency, frequency などの値を以下のように計算します。

CREATE OR REPLACE TABLE onlineretail.ltv_features AS
SELECT
  tf.customer_id,
  tf.monetary,
  tf.cnt_orders AS frequency,
  tf.recency,
  tf.T,
  ROUND(tf.recency/cnt_orders, 2) AS time_between,
  ROUND(tf.avg_basket_value, 2) AS avg_basket_value,
  ROUND(tf.avg_basket_size, 2) AS avg_basket_size,
  tf.cnt_returns,
  (CASE
      WHEN tf.cnt_returns > 0 THEN 1
      ELSE 0 END) AS has_returned,
  ROUND(tt.target_monetary, 2) as target_monetary
FROM
  -- 基準日より前のデータを抽出
  (
    SELECT
      customer_id,
      SUM(order_value) AS monetary,
      DATE_DIFF(MAX(order_date), MIN(order_date), DAY) AS recency,
      DATE_DIFF(CAST("2011-08-08" AS DATE), MIN(order_date), DAY) AS T,
      COUNT(DISTINCT order_date) AS cnt_orders,
      AVG(order_qty_articles) avg_basket_size,
      AVG(order_value) avg_basket_value,
      SUM(CASE
          WHEN order_value < 1 THEN 1
          ELSE 0 END) AS cnt_returns
    FROM onlineretail.data_cleaned
    WHERE
      order_date <= CAST("2011-08-08" AS DATE)
    GROUP BY
      customer_id) tf,
-- ターゲット変数(target_monetary)を全期間で計算 (基準日以降も使用可能)
  (
    SELECT
      customer_id,
      SUM(order_value) target_monetary
    FROM
      onlineretail.data_cleaned
    GROUP BY
      customer_id) tt
WHERE
  tf.customer_id = tt.customer_id
  AND tf.monetary > 0
  AND tf.monetary <= 15000 -- 購買金額の上限

テーブル ltv_features が作成できたら、 target_monetary をターゲット変数として、回帰モデルで予測モデルを学習することが可能になります。

まとめ

この記事では、小売業などの売上データからLTV分析のための表を作る手順例をご紹介しました。ここでは公開データセットを使いましたが、SQLを参考にご自身のデータも加工してみてください。なお、AutoMLの使い方やrecency, frequency など各値の意味も上述のブログに記載があるのであわせて参照ください。