Skip to main content

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:

ColumnTypeDescription
customer_idVARCHARUnique customer identifier
signup_dateDATEAccount creation date
last_loginTIMESTAMPMost recent login timestamp
total_ordersINTLifetime order count
total_spendFLOATLifetime spend in USD
support_ticketsINTNumber of support tickets filed
avg_session_minutesFLOATAverage session duration
subscription_planVARCHARCurrent plan: Free, Pro, Enterprise
is_churnedBOOLEANTarget 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:

MetricWhat It Tells YouTarget
AccuracyOverall correct predictions> 0.80
PrecisionOf predicted churners, how many actually churned> 0.70
RecallOf actual churners, how many were caught> 0.75
F1 ScoreHarmonic mean of precision and recall> 0.72
AUC-ROCModel'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.