Customer churn prediction

Dataset

This tutorial uses the Ecommerce Customer Churn Analysis and Prediction dataset from Kaggle. It contains historical customer behavior records with churn labels — input for building a retention prediction model.

The dataset has 20 fields:

Create the table and import data

1. Create the raw data table:

   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. Download the dataset and import it. Replace /path/to/dataset with the actual file path:

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

Use the psql tool to import data. If you use another SDK, import with the COPY or INSERT statement.

Check null counts

Run the following query to count null values across all columns:

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 $$;

Sample result:

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

Inspect column distributions

For columns with null values, inspect the data distribution to choose an imputation strategy. The following helper function computes distinct count, min, max, mean, and median for any column:

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;

Example — inspect the tenure column:

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

Sample result:

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

Preprocess data

Seven columns contain null values. The following table summarizes the imputation strategy for each, based on the distribution analysis:

These strategies map directly to the preprocess parameter in pgml.train():

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

CityTier and Complain are stored as INTEGER but represent categorical labels. Cast them to TEXT and apply one-hot encoding during training.

Create a training view

Create a view that applies type casts without modifying the raw table. This keeps the original data intact and lets you iterate on features by recreating the view.

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;

Apply feature engineering

Feature engineering derives additional signals from existing columns. The following four derived features capture per-order behavior patterns:

Recreate the view to include these features:

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;

Select an algorithm

Use pgml.train() to fit multiple algorithms and compare their F1 scores. All examples use the same project name, task, data source, and preprocessing parameters — only the algorithm value changes between runs.

`pgml.train()` key parameters:

Fit the XGBoost model:

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, ...}

Fit the bagging model for comparison:

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 achieves a higher F1 score (0.9543 vs. 0.9271), so this tutorial proceeds with XGBoost for hyperparameter tuning. To compare other algorithms, replace the algorithm value. For the full list of supported algorithms, see the pgml.algorithm enumeration type table in Use machine learning.

Tune hyperparameters

Run a grid search with 5-fold cross-validation to find the optimal XGBoost hyperparameters. The search explores the following space:

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
);

Sample result:

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)

The search identifies {"eta": 0.1, "max_depth": 6, "n_estimators": 1000} as the best configuration, achieving an F1 score of 0.9874 on the held-out validation set. The "cv": 5 setting means each configuration is evaluated on 5 different data splits, which makes the score more reliable than a single train/test split.

Train on full data with optimal hyperparameters

Use the best hyperparameters from the grid search to train a final model on the full dataset:

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
);

Sample result:

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

The final model achieves an F1 score of 0.9926 on the test set, an improvement over the cross-validated score of 0.9874 from the grid search run.

Real-time inference

Real-time inference returns a prediction immediately for a single input record. Use it when a data analyst or application needs an instant response based on a customer's behavioral profile.

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
));

Sample result:

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

A result of 0 means the model predicts this customer will not churn.

Batch inference

Batch inference processes many records in a single query. Use it when you need to score a large customer segment and throughput matters more than response latency.

First, create a prediction view that applies the same feature engineering used during training:

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;

Then run predictions across all rows:

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;

Sample result:

 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)

Customers with a prediction of 1 are identified as likely to churn. Use this output to prioritize retention campaigns or targeted offers.