AnalyticDB:顧客離反予測

データセット

このチュートリアルでは、Kaggle の Ecommerce Customer Churn Analysis and Prediction データセットを使用します。このデータセットには、離反ラベル付きの過去の顧客行動レコードが含まれており、維持予測モデルを構築するための入力となります。

データセットには 20 のフィールドがあります。

テーブルの作成とデータのインポート

1. 生データテーブルを作成します。

   CREATE TABLE raw_data_table (
       CustomerID INTEGER,
       Churn INTEGER,
       Tenure FLOAT,
       PreferredLoginDevice TEXT,
       CityTier INTEGER,
       WarehouseToHome FLOAT,
       PreferredPaymentMode TEXT,
       Gender TEXT,
       HourSpendOnApp FLOAT,
       NumberOfDeviceRegistered INTEGER,
       PreferedOrderCat TEXT,
       SatisfactionScore INTEGER,
       MaritalStatus TEXT,
       NumberOfAddress INTEGER,
       Complain INTEGER,
       OrderAmountHikeFromlastYear FLOAT,
       CouponUsed FLOAT,
       OrderCount FLOAT,
       DaySinceLastOrder FLOAT,
       CashbackAmount FLOAT
   );

2. データセットをダウンロードしてインポートします。/path/to/dataset を実際のファイルパスに置き換えてください。

   COPY raw_data_table FROM '/path/to/dataset.csv' DELIMITER ',' CSV HEADER;

データのインポートには psql ツールを使用してください。他の SDK を使用する場合は、COPY 文または INSERT 文でインポートします。

NULL 値のカウント

次のクエリを実行して、すべての列の NULL 値をカウントします。

DO $$
DECLARE
    r RECORD;
    SQL TEXT := '';
BEGIN
    FOR r IN
        SELECT column_name
        FROM information_schema.columns
        WHERE table_name = 'raw_data_table'
    LOOP
        SQL := SQL ||
            'SELECT ''' || r.column_name || ''' AS column_name, COUNT(*) FILTER (WHERE ' || r.column_name || ' IS NULL) AS null_count FROM raw_data_table UNION ALL ';
    END LOOP;

    SQL := LEFT(SQL, length(SQL) - 11);

    FOR r IN EXECUTE SQL LOOP
        RAISE NOTICE 'Column: %, Null Count: %', r.column_name, r.null_count;
    END LOOP;
END $$;

結果の例：

NOTICE:  Column: customerid, Null Count: 0
NOTICE:  Column: churn, Null Count: 0
NOTICE:  Column: tenure, Null Count: 264
NOTICE:  Column: preferredlogindevice, Null Count: 0
NOTICE:  Column: citytier, Null Count: 0
NOTICE:  Column: warehousetohome, Null Count: 251
NOTICE:  Column: preferredpaymentmode, Null Count: 0
NOTICE:  Column: gender, Null Count: 0
NOTICE:  Column: hourspendonapp, Null Count: 255
NOTICE:  Column: numberofdeviceregistered, Null Count: 0
NOTICE:  Column: preferedordercat, Null Count: 0
NOTICE:  Column: satisfactionscore, Null Count: 0
NOTICE:  Column: maritalstatus, Null Count: 0
NOTICE:  Column: numberofaddress, Null Count: 0
NOTICE:  Column: complain, Null Count: 0
NOTICE:  Column: orderamounthikefromlastyear, Null Count: 265
NOTICE:  Column: couponused, Null Count: 256
NOTICE:  Column: ordercount, Null Count: 258
NOTICE:  Column: daysincelastorder, Null Count: 307
NOTICE:  Column: cashbackamount, Null Count: 0

列の分布の調査

NULL 値を含む列については、データ分布を調査して補完戦略を選択します。以下のヘルパー関数は、任意の列の個別カウント、最小値、最大値、平均値、中央値を計算します。

CREATE OR REPLACE FUNCTION print_column_statistics(table_name TEXT, column_name TEXT)
RETURNS VOID AS $$
DECLARE
    SQL TEXT;
    distinct_count INTEGER;
    min_value NUMERIC;
    max_value NUMERIC;
    avg_value NUMERIC;
    median_value NUMERIC;
    r RECORD;
BEGIN
    SQL := 'SELECT
                COUNT(DISTINCT ' || column_name || ') AS distinct_count,
                MIN(' || column_name || ') AS min_value,
                MAX(' || column_name || ') AS max_value,
                AVG(' || column_name || ') AS avg_value,
                PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ' || column_name || ') AS median_value
            FROM ' || table_name;

    EXECUTE SQL INTO r;

    distinct_count := r.distinct_count;
    min_value := r.min_value;
    max_value := r.max_value;
    avg_value := r.avg_value;
    median_value := r.median_value;

    RAISE NOTICE 'Distinct Count: %', distinct_count;

    IF distinct_count < 40 THEN
        SQL := 'SELECT ' || column_name || ' AS col, COUNT(*) AS count FROM ' || table_name ||
               ' GROUP BY ' || column_name || ' ORDER BY count DESC';

        FOR r IN EXECUTE SQL LOOP
            RAISE NOTICE '%: %', r.col, r.count;
        END LOOP;
    END IF;

    RAISE NOTICE 'Min Value: %, Max Value: %, Avg Value: %, Median Value: %',
                 min_value, max_value, avg_value, median_value;
END;
$$ LANGUAGE plpgsql;

例 — tenure 列の調査：

SELECT print_column_statistics('raw_data_table', 'tenure');

結果の例：

NOTICE:  Distinct Count: 36
NOTICE:  1: 690
NOTICE:  0: 508
NOTICE:  <NULL>: 264
NOTICE:  8: 263
...
NOTICE:  Min Value: 0, Max Value: 61, Avg Value: 10.1898993663809, Median Value: 9

データの事前処理

7 つの列に NULL 値が含まれています。以下の表は、分布分析に基づいた各列の補完戦略をまとめたものです。

これらの戦略は、pgml.train() の preprocess パラメーターに直接マッピングされます。

{
  "tenure": {"impute": "median"},
  "warehousetohome": {"impute": "median"},
  "hourspendonapp": {"impute": "mean"},
  "orderamounthikefromlastyear": {"impute": "mean"},
  "couponused": {"impute": "zero"},
  "ordercount": {"impute": "zero"},
  "daysincelastorder": {"impute": "max"}
}

CityTier と Complain は INTEGER として保存されていますが、カテゴリカルなラベルを表します。これらを TEXT にキャストし、トレーニング中に One-Hot エンコーディングを適用してください。

トレーニングビューの作成

生データテーブルを変更せずに型キャストを適用するビューを作成します。これにより、元のデータはそのまま維持され、ビューを再作成することで特徴量のイテレーションが可能になります。

CREATE OR REPLACE VIEW train_data_view AS
SELECT
  Churn::TEXT,
  Tenure,
  PreferredLoginDevice,
  CityTier::TEXT,
  WarehouseToHome,
  PreferredPaymentMode,
  Gender,
  HourSpendOnApp,
  NumberOfDeviceRegistered,
  PreferedOrderCat,
  SatisfactionScore,
  MaritalStatus,
  NumberOfAddress,
  Complain::TEXT,
  OrderAmountHikeFromlastYear,
  CouponUsed,
  OrderCount,
  DaySinceLastOrder,
  CashbackAmount
FROM
  raw_data_table;

特徴量エンジニアリングの適用

特徴量エンジニアリングは、既存の列から追加のシグナルを導き出します。以下の 4 つの派生特徴量は、注文ごとの行動パターンを捉えます。

これらの特徴量を含めるようにビューを再作成します。

CREATE OR REPLACE VIEW train_data_view AS
SELECT
  Churn::TEXT,
  Tenure,
  PreferredLoginDevice,
  CityTier::TEXT,
  WarehouseToHome,
  PreferredPaymentMode,
  Gender,
  HourSpendOnApp,
  NumberOfDeviceRegistered,
  PreferedOrderCat,
  SatisfactionScore,
  MaritalStatus,
  NumberOfAddress,
  Complain::TEXT,
  OrderAmountHikeFromlastYear,
  CouponUsed,
  OrderCount,
  DaySinceLastOrder,
  CashbackAmount,
  CashbackAmount/OrderCount AS AvgCashbkPerOrder,
  HourSpendOnApp/OrderCount AS AvgHourSpendPerOrder,
  CouponUsed/OrderCount AS CouponUsedPerOrder,
  log(1+CashbackAmount) AS LogCashbackAmount
FROM
  raw_data_table;

アルゴリズムの選択

pgml.train() を使用して複数のアルゴリズムを適合させ、それらの F1 スコアを比較します。すべての例で同じプロジェクト名、タスク、データソース、および前処理パラメーターを使用し、実行ごとに変更されるのは algorithm の値のみです。

`pgml.train()` の主要パラメーター：

XGBoost モデルを適合させます。

SELECT * FROM pgml.train(
    project_name => 'Customer Churn Prediction Project',
    task => 'classification',
    relation_name => 'train_data_view',
    y_column_name => 'churn',
    preprocess => '{
            "tenure": {"impute": "median"},
            "warehousetohome": {"impute": "median"},
            "hourspendonapp": {"impute": "mean"},
            "orderamounthikefromlastyear": {"impute": "mean"},
            "couponused": {"impute": "zero"},
            "ordercount": {"impute": "zero"},
            "daysincelastorder": {"impute": "max"},
            "avgcashbkperorder": {"impute": "zero"},
            "avghourspendperorder": {"impute": "zero"},
            "couponusedperorder": {"impute": "zero"},
            "logcashbackamount": {"impute": "min"}
        }',
    algorithm => 'xgboost',
    runtime => 'python',
    test_size => 0.2
);
-- {"f1": 0.9543147, "precision": 0.96907216, "recall": 0.94, "accuracy": 0.9840142, ...}

比較のためにバギングモデルを適合させます。

SELECT * FROM pgml.train(
    project_name => 'Customer Churn Prediction Project',
    task => 'classification',
    relation_name => 'train_data_view',
    y_column_name => 'churn',
    preprocess => '{
            "tenure": {"impute": "median"},
            "warehousetohome": {"impute": "median"},
            "hourspendonapp": {"impute": "mean"},
            "orderamounthikefromlastyear": {"impute": "mean"},
            "couponused": {"impute": "zero"},
            "ordercount": {"impute": "zero"},
            "daysincelastorder": {"impute": "max"},
            "avgcashbkperorder": {"impute": "zero"},
            "avghourspendperorder": {"impute": "zero"},
            "couponusedperorder": {"impute": "zero"},
            "logcashbackamount": {"impute": "min"}
        }',
    algorithm => 'bagging',
    runtime => 'python',
    test_size => 0.2
);
-- {"f1": 0.9270833, "precision": 0.96216214, "recall": 0.89447236}

XGBoost はより高い F1 スコア (0.9543 対 0.9271) を達成したため、このチュートリアルではハイパーパラメーターのチューニングに XGBoost を使用します。他のアルゴリズムを比較するには、algorithm の値を置き換えてください。サポートされているアルゴリズムの完全なリストについては、「機械学習の使用」の pgml.algorithm 列挙型テーブルをご参照ください。

ハイパーパラメーターのチューニング

5 分割交差検証によるグリッドサーチを実行して、最適な XGBoost のハイパーパラメーターを見つけます。検索では、以下の空間を探索します。

SELECT * FROM pgml.train(
    project_name => 'Customer Churn Prediction Project',
    task => 'classification',
    relation_name => 'train_data_view',
    y_column_name => 'churn',
    preprocess => '{
            "tenure": {"impute": "median"},
            "warehousetohome": {"impute": "median"},
            "hourspendonapp": {"impute": "mean"},
            "orderamounthikefromlastyear": {"impute": "mean"},
            "couponused": {"impute": "zero"},
            "ordercount": {"impute": "zero"},
            "daysincelastorder": {"impute": "max"},
            "avgcashbkperorder": {"impute": "zero"},
            "avghourspendperorder": {"impute": "zero"},
            "couponusedperorder": {"impute": "zero"},
            "logcashbackamount": {"impute": "min"}
        }',
    algorithm => 'xgboost',
    search_args => '{ "cv": 5 }',
    SEARCH => 'grid',
    search_params => '{
        "max_depth": [4, 6, 8, 16],
        "n_estimators": [100, 200, 300, 400, 500, 1000, 2000],
        "eta": [0.05, 0.1, 0.2]
    }',
    hyperparams => '{
        "nthread": 16,
        "alpha": 0,
        "lambda": 1
    }',
    runtime => 'python',
    test_size => 0.2
);

結果の例：

INFO:  Best Hyperparams: {
  "alpha": 0,
  "lambda": 1,
  "nthread": 16,
  "eta": 0.1,
  "max_depth": 6,
  "n_estimators": 1000
}
INFO:  Best f1 Metrics: Number(0.9874088168144226)

この検索により、最適な構成として {"eta": 0.1, "max_depth": 6, "n_estimators": 1000} が特定され、ホールドアウト検証セットで 0.9874 の F1 スコアを達成しました。"cv": 5 の設定は、各構成が 5 つの異なるデータ分割で評価されることを意味し、これによりスコアは単一のトレーニング/テスト分割よりも信頼性が高くなります。

最適なハイパーパラメーターを使用した全データでのトレーニング

グリッドサーチで得られた最適なハイパーパラメーターを使用して、全データセットで最終的なモデルをトレーニングします。

SELECT * FROM pgml.train(
    project_name => 'Customer Churn Prediction Project',
    task => 'classification',
    relation_name => 'train_data_view',
    y_column_name => 'churn',
    preprocess => '{
            "tenure": {"impute": "median"},
            "warehousetohome": {"impute": "median"},
            "hourspendonapp": {"impute": "mean"},
            "orderamounthikefromlastyear": {"impute": "mean"},
            "couponused": {"impute": "zero"},
            "ordercount": {"impute": "zero"},
            "daysincelastorder": {"impute": "max"},
            "avgcashbkperorder": {"impute": "zero"},
            "avghourspendperorder": {"impute": "zero"},
            "couponusedperorder": {"impute": "zero"},
            "logcashbackamount": {"impute": "min"}
        }',
    algorithm => 'xgboost',
    hyperparams => '{
        "max_depth": 6,
        "n_estimators": 1000,
        "eta": 0.1,
        "nthread": 16,
        "alpha": 0,
        "lambda": 1
    }',
    runtime => 'python',
    test_size => 0.2
);

結果の例：

INFO:  Training Model { id: 170, task: classification, algorithm: xgboost, runtime: python }
INFO:  Hyperparameter searches: 1, cross validation folds: 1
INFO:  Hyperparams: {
  "eta": 0.1,
  "alpha": 0,
  "lambda": 1,
  "nthread": 16,
  "max_depth": 6,
  "n_estimators": 1000
}
INFO:  Metrics: {"roc_auc": 0.9751001, "log_loss": 0.19821791, "f1": 0.99258476, "precision": 0.9936373, "recall": 0.9915344, "accuracy": 0.9875666, "mcc": 0.95414394, "fit_time": 0.9980099, "score_time": 0.0085158}
INFO:  Comparing to deployed model f1: Some(0.9874088168144226)
INFO:  Deploying model id: 170
                  project              |      task      | algorithm | deployed
-----------------------------------+----------------+-----------+----------
 Customer Churn Prediction Project | classification | xgboost   | t

最終モデルはテストセットで 0.9926 の F1 スコアを達成し、グリッドサーチ実行時の交差検証スコア 0.9874 から改善されました。

リアルタイム推論

リアルタイム推論は、単一の入力レコードに対して即座に予測を返します。データアナリストやアプリケーションが、顧客の行動プロファイルに基づいて即時の応答を必要とする場合に使用します。

SELECT pgml.predict('Customer Churn Prediction Project',
( 4, 'Mobile Phone'::TEXT, 3, 6,
'Debit Card'::TEXT, 'Female'::TEXT, 3, 3,
'Laptop & Accessory'::TEXT, 2,
'Single'::TEXT, 9 ,
'1'::TEXT, 11, 1, 1, 5, 159.93,
159.93, 3, 1, 2.206637011283536
));

結果の例：

 predict
---------
       0
(1 row)

結果が 0 の場合、モデルはこの顧客が離反しないと予測していることを意味します。

バッチ推論

バッチ推論は、単一のクエリで多くのレコードを処理します。大規模な顧客セグメントをスコアリングする必要があり、応答レイテンシーよりもスループットが重要な場合に使用します。

まず、トレーニング中に使用したのと同じ特徴量エンジニアリングを適用する予測ビューを作成します。

CREATE OR REPLACE VIEW predict_data_view AS
SELECT
  CustomerID,
  Churn::TEXT,
  Tenure,
  PreferredLoginDevice,
  CityTier::TEXT,
  WarehouseToHome,
  PreferredPaymentMode,
  Gender,
  HourSpendOnApp,
  NumberOfDeviceRegistered,
  PreferedOrderCat,
  SatisfactionScore,
  MaritalStatus,
  NumberOfAddress,
  Complain::TEXT,
  OrderAmountHikeFromlastYear,
  CouponUsed,
  OrderCount,
  DaySinceLastOrder,
  CashbackAmount,
  CashbackAmount/OrderCount AS AvgCashbkPerOrder,
  HourSpendOnApp/OrderCount AS AvgHourSpendPerOrder,
  CouponUsed/OrderCount AS CouponUsedPerOrder,
  log(1+CashbackAmount) AS LogCashbackAmount
FROM
  raw_data_table;

次に、すべての行に対して予測を実行します。

SELECT CustomerID, pgml.predict('Customer Churn Prediction Project', (
  "tenure",
  "preferredlogindevice",
  "citytier",
  "warehousetohome",
  "preferredpaymentmode",
  "gender",
  "hourspendonapp",
  "numberofdeviceregistered",
  "preferedordercat",
  "satisfactionscore",
  "maritalstatus",
  "numberofaddress",
  "complain",
  "orderamounthikefromlastyear",
  "couponused",
  "ordercount",
  "daysincelastorder",
  "cashbackamount",
  "avgcashbkperorder",
  "avghourspendperorder",
  "couponusedperorder",
  "logcashbackamount"
)) FROM predict_data_view LIMIT 20;

結果の例：

 customerid | predict
------------+---------
      50005 |       0
      50009 |       0
      50012 |       0
      50013 |       0
      50019 |       0
      50020 |       0
      50022 |       0
      50023 |       0
      50026 |       0
      50031 |       1
      50039 |       1
      50040 |       0
      50043 |       1
      50045 |       1
      50047 |       0
      50048 |       1
      50050 |       1
      50051 |       1
      50052 |       1
      50053 |       0
(20 rows)

予測が 1 の顧客は、離反する可能性が高いと識別されます。この出力を使用して、維持キャンペーンやターゲットを絞ったオファーの優先順位を付けます。