Customer Churn Risk Evaluation with SQL and Machine Learning

Home » Python » Customer Churn Risk Evaluation with SQL and Machine Learning

Customer churn is one of those silent profit killers that slowly erodes growth if you don’t measure it properly and act in time. The good news is that today you can build robust churn risk models directly with SQL on top of your data warehouse, combining classic machine learning techniques, managed cloud services and very practical business metrics.

This guide walks you end-to-end through churn risk evaluation with SQL across different scenarios: from using Amazon Redshift ML and Amazon SageMaker to train models with pure SQL, to creating logistic regression churn models on web events, all the way to more advanced techniques like hyperparameter tuning and handling imbalanced data (churn vs non-churn) inspired by Python-based workflows. The goal is to show you, in detail, how to go from raw data to actionable risk scores that your marketing, customer success and finance teams can actually use.

Why churn risk evaluation with SQL matters for your business

Predicting which customers are likely to leave is one of the highest‑ROI use cases for applied machine learning and analytics. Losing a customer is usually far more expensive than retaining them, and small improvements in retention have a disproportionate impact on revenue and long‑term profitability.

SQL plays a central role in this journey because most transactional, behavioral and customer data already lives in databases and cloud data warehouses; an overview of data storage systems helps understand how to leverage them. If your teams can create, train and deploy churn models directly from SQL, they avoid constant data exports, tool switching and complex engineering pipelines, drastically reducing time‑to‑value.

Modern cloud platforms now blur the line between analytics and ML. Services like Amazon Redshift ML let data analysts and developers build, train and use ML models from familiar SQL statements, while still relying under the hood on fully managed services like Amazon SageMaker and SageMaker Autopilot. That means you can stand up churn models without becoming a full‑time ML engineer.

On top of the technology, churn analysis must remain tightly tied to business reality: how you define an “active” customer, which signals indicate risk, what threshold of inactivity matters (30, 60, 90 days…), and how much you are willing to invest in retention campaigns based on predicted risk. The techniques we’ll cover are flexible enough to adapt to very different industries: banking, telecom, SaaS, ecommerce and more.

Using Amazon Redshift ML to build churn and risk models with SQL

Amazon Redshift ML is a great illustration of how to bring ML to where your data already lives. It allows you to create, train and deploy models using SQL commands inside Amazon Redshift, while Amazon SageMaker does the heavy lifting in the background.

In practice, Redshift ML exposes your trained model as a SQL function that you can call in queries, dashboards and ETL jobs. For churn and risk use cases, that means you can seamlessly embed predictions such as “high‑risk customer”, “credit default probability” or “churn likelihood” into your standard reporting and data pipelines.

Under the hood, Redshift ML relies on Amazon SageMaker Autopilot. Autopilot automatically explores multiple algorithms and hyperparameters, trains and tunes candidate models, and selects the best one given your objective and data. You keep full visibility and control, but you skip most of the low‑level ML plumbing.

The end result is a familiar developer experience: you write a SQL CREATE MODEL statement on top of your Redshift tables, point to an S3 bucket for intermediate artifacts, and when training is done you get a SQL scalar function that can be used for inference at scale across your warehouse.

End‑to‑end example: credit risk and churn‑like prediction in Redshift

To ground the concepts, let’s walk through a concrete example based on financial risk. Although the target variable in this case is credit risk (high vs low), the workflow is identical to a classic churn prediction: you have labeled historical data, you train a binary classifier, and then you score new or existing customers on demand.

The sample dataset comes from the UCI Machine Learning Repository and includes 1,001 records, each describing a banking customer with 14 attributes related to their financial profile and relationship with the institution. While modest in size by modern standards, it’s enough to illustrate the process from raw data to deployed SQL model.

The key attributes (features) in this dataset cover both demographic and financial behavior:

• existingchecking: status of the existing current account.
• duration: months of relationship or credit duration.
• creditamount: credit amount requested.
• savings: current savings level.
• employmentsince: length of current employment.
• sex: customer gender.
• status: marital status.
• age: customer age.
• housing: housing situation (own, rent, etc.).
• existingcredits: number of existing credits.
• job: employment status.
• jobtype: type of job.
• dependents: number of dependents.
• risk: target variable; indicates whether the customer is considered high risk.

The target variable, risk, is binary, so this is a classic binary classification problem. You can think of risk = TRUE as analogous to a churn label, where you want to identify customers that are likely to default or leave so you can act proactively.

Despite the small dataset, the setup mirrors a real‑world ML workflow: you still split data into train and inference sets, define an appropriate schema in Redshift, create an S3 bucket for training data and artifacts, and configure an IAM role with access to S3 and SageMaker. In production, you’d simply scale this up with more rows and richer feature sets.

Preparing the Redshift environment and data

Before training any model, you need to make sure the Redshift cluster and permissions are in place. You can either create the cluster via the AWS Management Console or use a CloudFormation template that automates the network and security configuration.

When provisioning via the console, you typically choose a node type and count (for example, dc2.large with two nodes for a demo), set a database port, master username and password, and crucially attach an IAM role that grants the cluster access to the S3 bucket where your training and inference CSV files reside.

If you prefer infrastructure as code, a CloudFormation template can spin up the Redshift cluster along with its security groups, subnet group and IAM role in one go. From the perspective of churn risk modeling, the important part is simply that the cluster can read from and write to the designated S3 bucket.

Once the cluster is running, you move to the Redshift Query Editor. From there you connect to the database, verify your credentials, and start by creating two tables: one for training (historical labeled customers) and one for inference (records you’ll use later to test model performance).

The training table schema closely mirrors the CSV structure:

• Text columns for attributes like existingchecking, savings, employmentsince, sex, status, housing, job and jobtype.
• Numeric columns for duration, creditamount, age, existingcredits and dependents.
• A boolean column risk, used as the target to be predicted.

Data loading is handled via the Redshift COPY command, which pulls from S3 using the IAM role, specifies CSV format, header handling and delimiter, and populates both the training and inference tables. After the COPY operations succeed, you can check the object tree in the editor to confirm the tables and row counts.

Creating and training a Redshift ML model with SQL

With the data in place, the next step is to train a Redshift ML model using a CREATE MODEL statement. This is where SageMaker Autopilot kicks in under the covers to test multiple candidate algorithms and hyperparameters for your binary classification problem.

The CREATE MODEL command selects all the relevant predictor columns from risk_prediction_training, designates the risk column as the TARGET, and defines the name of the SQL function that will be used later for inference over your data warehouse.

Two key settings are required: IAM_ROLE and S3_BUCKET. The IAM role must allow listing and reading from the S3 bucket, and the S3 bucket is used by Redshift and SageMaker to exchange training data and model artifacts. You can also specify a MAX_RUNTIME in seconds to limit how long Autopilot is allowed to experiment.

It’s common to run into trust relationship issues the first time. If SageMaker cannot assume the IAM role associated with your Redshift cluster, the CREATE MODEL command will fail. You then need to adjust the role’s trust policy to include sagemaker.amazonaws.com as a trusted service principal.

If a previous model with the same name exists, you can drop it using DROP MODEL before recreating it. This lets you iterate on your modeling strategy or tweak settings without cluttering your environment with obsolete models.

Monitoring training and validating the Redshift ML model

Training time will vary based on data size and runtime limits, but for the sample credit risk dataset you can expect something on the order of an hour. During that time, you can check model status and metadata by running SHOW MODEL with the model name.

SHOW MODEL reveals key information such as the training status (for example, TRAINING, READY), the selected algorithm, objective metric and validation scores. For binary classification, one of the crucial metrics is often the F1 score, which ranges from 0 to 1 and balances precision and recall.

Once the model status is READY, you can start evaluating its predictive performance using the separate inference dataset that the model has never seen during training. This mirrors a real‑world scenario where new customers are scored on the fly.

A straightforward first check is to compute overall accuracy. You do this by running a SQL query that: extracts the actual risk label, calls the model function (for example, func_risk_prediction_model) to obtain the predicted label, flags correct vs incorrect predictions, and then aggregates to compute num_correct divided by total.

Beyond raw accuracy, you can compute aggregate risk distributions on the inference set. For instance, you can count how many customers are assigned to each risk category (high, low, indeterminate) to understand model behavior and how many cases would be flagged for further review or proactive retention actions.

Defining customer behavior features for SQL churn models

Moving from credit risk to actual churn, the same ML principles apply: you need labeled historical data and meaningful features that capture how customers behave and evolve over time. For ecommerce or digital products, this usually means aggregating purchase and interaction metrics per customer.

A typical SQL churn model starts from a table of web events or transactions, where each row represents a purchase or commerce event with fields such as timestamps, order IDs, product prices and quantities, and user identifiers.

From these raw events you can engineer powerful behavioral features that summarize a customer’s history:

• total_purchases: total number of completed purchases per customer.
• total_revenue: cumulative revenue generated by that customer.
• avg_order_value: average basket value; total_revenue divided by total_purchases.
• customer_lifetime: days between the first and last purchase.
• days_since_last_purchase: recency, measured as days from the most recent purchase to a reference date.
• purchase_frequency: number of distinct months in which the customer purchased, capturing regularity.

These features are crucial because churn is rarely random. Customers that progressively buy less often, spend less and ignore your marketing are usually sending clear signals that they might be about to leave. Capturing frequency, recency and monetary value (the classic RFM trio) in SQL is typically the first step.

Underpinning all of this is a reliable customer identifier. In many digital analytics setups, an Experience Cloud ID (ECID) or similar ID stored in a field like identityMap.id is what lets you stitch events across sessions and devices into a single customer history.

Data requirements and assumptions for web‑based churn modeling

To train a churn model directly from web events, your dataset has to meet certain minimal requirements. Each row should represent a transaction or purchase event with enough detail to be aggregated into customer‑level features.

The typical required fields include:

• identityMap.id: a stable cross‑session customer identifier.
• productListItems.priceTotal: total cost of items per transaction.
• productListItems.quantity: total quantity of items.
• timestamp: event datetime in a format compatible with date/time functions like DATEDIFF (for example, YYYY-MM-DD HH:MM:SS).
• commerce.order.purchaseID: a non‑null value that confirms a completed purchase.

Historical depth is important. To distinguish between temporary inactivity and real churn, you need enough months of data to see multiple purchase cycles per customer, especially in verticals with long buying intervals (travel, insurance, B2B contracts, etc.).

The model also depends on a clear, operational definition of churn. A common, practical rule for ecommerce is considering a customer as churned if they haven’t purchased in the last 90 days relative to a reference date. This threshold can be adapted (30, 60, 180 days) based on your normal purchase cycle.

Once the dataset is structured and assumptions are clear, you can use SQL to create labels (churned vs not churned) by comparing days_since_last_purchase with your threshold and then generate the training table that feeds the logistic regression or other classification algorithm.

Building a logistic regression churn model with SQL

Logistic regression is a natural fit for churn prediction with SQL because it outputs probabilities between 0 and 1 and is often supported natively or via ML extensions in modern analytics databases and customer data platforms.

The modeling process typically runs in three phases: feature engineering, label assignment and model training.

First, you aggregate your web events into customer‑level rows computing total_purchases, total_revenue, avg_order_value, customer_lifetime, days_since_last_purchase and purchase_frequency. This can be done in a single SQL statement with GROUP BY and window functions, or in stages with intermediate tables.

Second, you create a churn label based on an inactivity rule. For example, churned = 1 if days_since_last_purchase > 90, else churned = 0. This labeled dataset becomes your input to the logistic regression training routine, which may be invoked via a SQL CREATE MODEL statement or a vendor‑specific function.

Third, you train the logistic regression model specifying which columns are features and which column is the target label (churned). The ML engine learns coefficients that reflect how each feature contributes to churn risk, which can be very insightful for business stakeholders.

The model output is usually a table or view with one row per customer, including the engineered features and the churned label. Later, when you use the model for prediction, you’ll get an additional prediction column representing either the predicted label (0 or 1) or the churn probability.

Evaluating churn models: metrics that actually matter

Training a churn model is only half the battle; you must rigorously evaluate its performance before deploying it in production campaigns. SQL‑based ML frameworks often expose evaluation helpers, such as a model_evaluate function, that compute common metrics.

For churn, it’s crucial to look beyond raw accuracy. Accuracy simply measures the percentage of correct predictions, but in imbalanced problems (where most customers do not churn) a model can be “accurate” while being almost useless for your business.

Key metrics for churn prediction include:

• AUC-ROC: measures the model’s ability to distinguish churners from non‑churners across all classification thresholds; values closer to 1 indicate stronger discrimination.
• Precision: proportion of predicted churners that are truly churners; high precision means fewer false alarms and more efficient retention spending.
• Recall: proportion of actual churners that the model correctly identifies; high recall ensures you don’t miss many at‑risk customers.
• F1 score: harmonic mean of precision and recall, useful when you need a balance between catching many churners and avoiding too many false positives.

In many real‑world churn projects, business stakeholders care more about precision and recall on the positive class (customers predicted to churn) than about global accuracy. After all, the goal is to target retention offers efficiently, not to look good on a single averaged metric.

SQL‑driven evaluation is typically done against a hold‑out test set that was not used for training. You pass this dataset to the model_evaluate function or equivalent, obtain AUC-ROC, accuracy, precision and recall, and then iterate on feature engineering, thresholds or algorithms based on those results.

Python‑inspired techniques to improve churn models

Many best practices in churn modeling come from the broader ML ecosystem, where Python and libraries like scikit‑learn, imbalanced‑learn and others are widely used. The concepts, however, are transferable to SQL‑centric workflows or hybrid setups where SQL handles feature creation and Python handles advanced modeling.

A common pattern is to start exploring churn with a public dataset such as a bank churn CSV from Kaggle. These datasets typically include demographics (age, country, gender), account tenure, number of products, credit score and whether the customer exited (churned).

The usual workflow starts by loading and inspecting the dataset: checking the number of rows and columns, summarizing numerical features, exploring the target distribution, and identifying obviously irrelevant attributes like customer surnames or opaque IDs that won’t help prediction.

Visual exploration is especially helpful. Plotting distributions and boxplots of continuous variables (like age or tenure) split by churn label can quickly reveal which features have explanatory power. Histograms for categorical variables (gender, country) show whether certain categories correlate with higher churn.

During this exploratory phase you also look for data quality issues: missing values, extreme outliers, dominant categories and suspicious patterns. All of these can affect downstream model performance and may require cleaning, capping or re‑encoding.

Categorical variables are another crucial point. ML algorithms typically expect numeric input, so textual categories must be encoded. Simple ordinal encoders map categories to integers, which can work but may introduce artificial ordering (e.g., color codes where 6 is not “greater than” 2 in any meaningful sense). More sophisticated encodings like one‑hot or target encoding usually yield better models, albeit at the cost of more features.

From the first churn model to robust evaluation

After basic cleaning and encoding, a first churn model can be trained—for example, a random forest classifier, which is robust, handles nonlinear relationships well and requires relatively little feature scaling.

You then split the data into training and test sets (for example, 70% train, 30% test) to simulate future, unseen customers. The model is fit on the training set and evaluated on the test set using metrics like accuracy, precision, recall and F1 score.

At this stage it’s very easy to be misled by high accuracy numbers. In imbalanced churn problems, a model can achieve high accuracy simply by always predicting the majority class (non‑churn), while barely catching any actual churners. That’s why precision, recall and F1 specific to the churn class are much more relevant.

The ROC curve and its area under the curve (AUC) provide a more nuanced view, showing the trade‑off between true positive rate and false positive rate across all thresholds. A curve that clearly dominates the diagonal baseline indicates a useful model, but again you must relate this to business cost/benefit trade‑offs.

Choosing the right evaluation metric is a business decision. If retention outreach is expensive, you may prefer high precision (only target likely churners). If losing a customer is extremely costly, you may accept more false positives and focus on recall (catch as many churners as possible), even if that means contacting more customers.

Hyperparameter tuning and dealing with imbalanced churn labels

Once a baseline churn model is in place, the next big gains usually come from hyperparameter tuning. Hyperparameters are configuration values external to the training process (number of trees, tree depth, learning rate, etc.) that can dramatically impact model quality.

A practical approach is to define a hyperparameter search space (a grid or random ranges for each parameter) and then explore a subset of combinations using randomized search or Bayesian optimization. For each candidate configuration, cross‑validation is run on the training data, and a metric like F1 score is used to compare them.

For churn, F1 is often a better objective than pure accuracy because it balances precision and recall, which is what you typically care about when prioritizing at‑risk customers.

Another major challenge in churn modeling is label imbalance: there are usually many more non‑churners than churners in your historical data. If left unaddressed, most algorithms will learn to “play it safe” and predict the majority class most of the time.

There are several strategies to handle imbalanced churn data:

• Oversampling the minority class using techniques like SMOTE, ADASYN or SVMSMOTE, which synthesize new minority examples by interpolating between existing ones.
• Undersampling the majority class to shrink the dataset while making the classes more balanced (sometimes combined with oversampling).
• Using algorithms or wrappers that handle class weights or balanced subsets internally, such as balanced random forests that train each tree on a class‑balanced bootstrap sample.

Empirically, it’s crucial that your test set remains untouched and imbalanced, reflecting the true production distribution. You can oversample or otherwise manipulate only the training set; otherwise, evaluation metrics will be overly optimistic and not representative of real‑world performance.

In many experiments, using algorithm‑level balancing (like a balanced random forest) without changing the raw training data has produced substantial gains in precision and F1, sometimes by ten percentage points or more. For a churn model, this can translate into significantly better targeting of at‑risk customers and higher ROI on retention campaigns.

Remember that every percentage point of improvement in effective retention can have an outsized impact on recurring revenue and customer lifetime value. Accurately detecting more churners is not the final goal, but it gives you the leverage to deploy offers, service improvements and personalized interventions where they matter most.

All in all, combining SQL‑native ML capabilities (such as Amazon Redshift ML and SQL‑driven logistic regression) with solid machine learning practices (feature engineering, proper metrics, hyperparameter tuning and imbalance handling) gives you a powerful toolkit for evaluating churn risk directly where your data lives. Whether you operate in finance, telecom, ecommerce or SaaS, these techniques allow you to transform raw interaction histories into clear churn risk scores that marketing and operations teams can confidently act on, tightening the feedback loop between analytics and business decisions.