Capstone: Customer Churn Prediction
Project Overview
Build an end-to-end customer churn prediction system using Snowflake ML. You will prepare data, train a classification model, evaluate its performance, generate predictions, and create a monitoring dashboard — all within Snowflake.
Dataset Description
The project uses a customer activity dataset with the following structure:
| Column | Type | Description |
|---|---|---|
customer_id | VARCHAR | Unique customer identifier |
signup_date | DATE | Account creation date |
last_login | TIMESTAMP | Most recent login timestamp |
total_orders | INT | Lifetime order count |
total_spend | FLOAT | Lifetime spend in USD |
support_tickets | INT | Number of support tickets filed |
avg_session_minutes | FLOAT | Average session duration |
subscription_plan | VARCHAR | Current plan: Free, Pro, Enterprise |
is_churned | BOOLEAN | Target variable — TRUE if customer churned |
-- Create the training dataset
CREATE OR REPLACE TABLE ml_db.public.churn_dataset AS
SELECT
customer_id,
DATEDIFF('day', signup_date, CURRENT_DATE()) AS account_age_days,
DATEDIFF('day', last_login, CURRENT_DATE()) AS days_since_login,
total_orders,
total_spend,
support_tickets,
avg_session_minutes,
subscription_plan,
is_churned
FROM raw_db.public.customers;
Data Preparation
Feature Engineering
-- Add derived features
CREATE OR REPLACE TABLE ml_db.public.churn_features AS
SELECT
*,
total_spend / NULLIF(total_orders, 0) AS avg_order_value,
support_tickets / NULLIF(account_age_days, 0) * 30 AS tickets_per_month,
CASE
WHEN days_since_login > 90 THEN 'inactive'
WHEN days_since_login > 30 THEN 'at_risk'
ELSE 'active'
END AS activity_segment
FROM ml_db.public.churn_dataset;
Train/Test Split
-- Split data 80/20
CREATE OR REPLACE TABLE ml_db.public.churn_train AS
SELECT * FROM ml_db.public.churn_features SAMPLE (80);
CREATE OR REPLACE TABLE ml_db.public.churn_test AS
SELECT * FROM ml_db.public.churn_features
WHERE customer_id NOT IN (SELECT customer_id FROM ml_db.public.churn_train);
Model Training
Use Snowflake ML Classification to train the model:
CREATE OR REPLACE SNOWFLAKE.ML.CLASSIFICATION churn_model(
INPUT_DATA => TABLE(ml_db.public.churn_train),
TARGET_COLNAME => 'IS_CHURNED',
CONFIG_OBJECT => {
'evaluate': TRUE,
'on_error': 'skip'
}
);
Evaluation Metrics
Retrieve the model evaluation report:
-- Get evaluation metrics
CALL churn_model!SHOW_EVALUATION_METRICS();
Key metrics to review:
| Metric | What It Tells You | Target |
|---|---|---|
| Accuracy | Overall correct predictions | > 0.80 |
| Precision | Of predicted churners, how many actually churned | > 0.70 |
| Recall | Of actual churners, how many were caught | > 0.75 |
| F1 Score | Harmonic mean of precision and recall | > 0.72 |
| AUC-ROC | Model's ability to distinguish classes | > 0.80 |
-- View feature importance
CALL churn_model!SHOW_FEATURE_IMPORTANCE();
Making Predictions
-- Score the test set
CREATE OR REPLACE TABLE ml_db.public.churn_predictions AS
SELECT
customer_id,
is_churned AS actual,
churn_model!PREDICT(
INPUT_DATA => OBJECT_CONSTRUCT(*)
):class::BOOLEAN AS predicted_churn,
churn_model!PREDICT(
INPUT_DATA => OBJECT_CONSTRUCT(*)
):probability_TRUE::FLOAT AS churn_probability
FROM ml_db.public.churn_test;
-- View high-risk customers
SELECT customer_id, churn_probability
FROM ml_db.public.churn_predictions
WHERE predicted_churn = TRUE
ORDER BY churn_probability DESC
LIMIT 20;
Scoring New Data
Create a scheduled pipeline to score active customers:
-- Create a task to score customers daily
CREATE OR REPLACE TASK ml_db.public.daily_churn_scoring
WAREHOUSE = compute_wh
SCHEDULE = 'USING CRON 0 6 * * * America/New_York'
AS
CREATE OR REPLACE TABLE ml_db.public.current_churn_scores AS
SELECT
customer_id,
churn_model!PREDICT(
INPUT_DATA => OBJECT_CONSTRUCT(*)
):probability_TRUE::FLOAT AS churn_probability,
CURRENT_TIMESTAMP() AS scored_at
FROM ml_db.public.churn_features
WHERE is_churned = FALSE;
ALTER TASK ml_db.public.daily_churn_scoring RESUME;
Building a Monitoring Dashboard
Create a Streamlit dashboard to monitor churn predictions:
import streamlit as st
from snowflake.snowpark.context import get_active_session
session = get_active_session()
st.title("Customer Churn Monitor")
# Summary metrics
scores = session.sql("""
SELECT
COUNT(*) AS total_customers,
SUM(CASE WHEN churn_probability > 0.7 THEN 1 ELSE 0 END) AS high_risk,
SUM(CASE WHEN churn_probability BETWEEN 0.4 AND 0.7 THEN 1 ELSE 0 END) AS medium_risk,
AVG(churn_probability) AS avg_risk_score
FROM ml_db.public.current_churn_scores
""").to_pandas()
col1, col2, col3 = st.columns(3)
col1.metric("Total Customers", int(scores["TOTAL_CUSTOMERS"][0]))
col2.metric("High Risk", int(scores["HIGH_RISK"][0]))
col3.metric("Avg Risk Score", f"{scores['AVG_RISK_SCORE'][0]:.2%}")
# Risk distribution chart
risk_dist = session.sql("""
SELECT
CASE
WHEN churn_probability > 0.7 THEN 'High'
WHEN churn_probability > 0.4 THEN 'Medium'
ELSE 'Low'
END AS risk_level,
COUNT(*) AS customer_count
FROM ml_db.public.current_churn_scores
GROUP BY 1
""").to_pandas()
st.bar_chart(risk_dist.set_index("RISK_LEVEL"))
Summary
This capstone covered the full ML lifecycle in Snowflake: data preparation with feature engineering, model training with Snowflake ML Classification, evaluation with standard metrics, batch prediction scoring, scheduled refresh with Tasks, and a Streamlit monitoring dashboard.