Churn prediction
Train and inspect a logistic regression model, score customers, and understand evaluation and retraining.
The practical problem and model
A subscription team wants to identify customers who might cancel so it can prioritize useful outreach. The inputs here describe tenure, monthly charges, support tickets, and contract length. The label records whether a customer churned.
Logistic regression combines the inputs into a weighted score and applies a sigmoid, producing a value between zero and one. It is a useful baseline for binary outcomes because its input-to-score relationship is relatively simple. A score is an estimate from the training data, not a guarantee that a customer will leave.
The eight synthetic customers deliberately make the pattern easy to learn. This walkthrough teaches the SQL lifecycle; it does not measure the effectiveness of a retention campaign.
Before you run
Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.
Studio: Tutorial: Churn Prediction End-to-End. Download the complete SQL.
Step 1: Create eight labelled customers
The setup creates every table used below. The raw columns keep their natural units: tenure in days, charges in dollars, a ticket count, and contract length in months.
CREATE CATALOG IF NOT EXISTS tutorial;
CREATE SCHEMA IF NOT EXISTS tutorial.churn;
CREATE OR REPLACE TABLE tutorial.churn.customer_history (
customer_id BIGINT,
tenure_days DOUBLE,
monthly_charges DOUBLE,
support_tickets DOUBLE,
contract_months DOUBLE,
churned INT
);
INSERT INTO tutorial.churn.customer_history VALUES
(1, 730, 49.99, 0, 24, 0),
(2, 90, 99.99, 4, 1, 1),
(3, 365, 29.99, 1, 12, 0),
(4, 30, 79.99, 6, 1, 1),
(5, 540, 39.99, 0, 12, 0),
(6, 180, 89.99, 5, 1, 1),
(7, 900, 59.99, 1, 24, 0),
(8, 60, 119.99, 3, 1, 1);
Step 2: Train and inspect the model
The training query puts the label first and casts it to DOUBLE, because the trainer requires DOUBLE for every column. It then supplies four features in the same order as the model signature.
Each feature is scaled to a similar range: tenure in years, charges in hundreds of dollars, tickets in tens, and contract length in years. Gradient descent is sensitive to feature scale. On raw values such as days (up to 900), it saturates every probability at exactly 0 or 1, which makes the scores and the evaluation uninformative.
learning_rate controls the optimization step, l2_lambda penalizes large weights, and max_iters bounds training. Inspect the registered model and its active version before using it.
CREATE OR REPLACE MODEL tutorial.churn.churn_predictor
(DOUBLE, DOUBLE, DOUBLE, DOUBLE)
RETURNS DOUBLE
TYPE 'logistic'
OPTIONS (learning_rate = 0.1, l2_lambda = 0.01, max_iters = 1000, tolerance = 1e-4)
AS SELECT
CAST(churned AS DOUBLE) AS label,
tenure_days / 365.0 AS tenure_years,
monthly_charges / 100.0 AS charges_100usd,
support_tickets / 10.0 AS tickets_10s,
contract_months / 12.0 AS contract_years
FROM tutorial.churn.customer_history;
SHOW MODELS;
SHOW MODEL VERSIONS tutorial.churn.churn_predictor;
EXPLAIN MODEL tutorial.churn.churn_predictor;
Step 3: Score customers and inspect evaluation
The model is registered as a SQL function under its qualified name. Call it with the same scaling as training: the direct call scores a customer with 60 days of tenure, $89.99 monthly charges, 3 tickets, and a 1-month contract. The batch query ranks all eight customers. Evaluation uses the same fixture and scaling as training, so its metrics describe fit to this fixture rather than performance on unseen customers.
SELECT tutorial.churn.churn_predictor(60.0 / 365.0, 89.99 / 100.0, 3.0 / 10.0, 1.0 / 12.0) AS p_churn;
SELECT
customer_id,
tutorial.churn.churn_predictor(
tenure_days / 365.0, monthly_charges / 100.0,
support_tickets / 10.0, contract_months / 12.0
) AS p_churn
FROM tutorial.churn.customer_history
ORDER BY p_churn DESC;
EVALUATE MODEL tutorial.churn.churn_predictor
ON SELECT
CAST(churned AS DOUBLE) AS label,
tenure_days / 365.0 AS tenure_years,
monthly_charges / 100.0 AS charges_100usd,
support_tickets / 10.0 AS tickets_10s,
contract_months / 12.0 AS contract_years
FROM tutorial.churn.customer_history;
SHOW MODEL EVALUATIONS FOR tutorial.churn.churn_predictor;
Step 4: Exercise the demo retraining lifecycle
The tutorial deliberately drops and recreates the model with more iterations and a tighter tolerance, using the same scaled features. This is a reset exercise, not a zero-downtime deployment recipe. For staged artifact activation, continue to the imported-model tutorial.
DROP MODEL IF EXISTS tutorial.churn.churn_predictor;
CREATE OR REPLACE MODEL tutorial.churn.churn_predictor
(DOUBLE, DOUBLE, DOUBLE, DOUBLE)
RETURNS DOUBLE
TYPE 'logistic'
OPTIONS (learning_rate = 0.1, l2_lambda = 0.01, max_iters = 2000, tolerance = 1e-5)
AS SELECT
CAST(churned AS DOUBLE) AS label,
tenure_days / 365.0 AS tenure_years,
monthly_charges / 100.0 AS charges_100usd,
support_tickets / 10.0 AS tickets_10s,
contract_months / 12.0 AS contract_years
FROM tutorial.churn.customer_history;
SHOW MODEL VERSIONS tutorial.churn.churn_predictor;
What to check in the results
- The direct call returns a churn probability of about 0.94 (about
0.9413in the validated run). - The scoring query returns all eight customer IDs in descending score order. The four customers who churned rank first, at about 0.92–0.97 (IDs 8, 4, 2, 6). The retained customers follow: ID 3 at about 0.12, ID 5 at about 0.06, and IDs 1 and 7 below 0.01.
- Scores lie strictly between zero and one; none is exactly 0 or 1. Thresholding at
0.5separates the supplied positive and negative labels. If every score is exactly 0 or 1, check that the features are scaled as shown. - Evaluation is associated with the named model and the version that was evaluated. Version numbers depend on previous runs; do not expect a fixed version ID.
- The final model is available again after the explicit drop/recreate step.
Adapt it to real data
Define churn over a concrete future interval, such as cancellation in the next 30 days, and compute features only from information available at prediction time. Split customers and time periods so the same customer history cannot leak into both training and validation.
Preserve training-time feature transformations, such as the scaling used here, at scoring time. Evaluate calibration and the cost of false positives before selecting an outreach threshold. Measure whether the intervention helps with a controlled campaign; model accuracy alone cannot establish that.