Building a Customer Churn Prediction Scoring Table in pandas: Calculating Behavioral Flags, Weighted Risk Scores, and Exporting a Ranked Watch List to Excel

Introduction

Your head of customer success drops a spreadsheet on your desk. It has 12,000 customers, and somewhere in that list are the 400 or so who are quietly on their way out the door. The business is hemorrhaging revenue it doesn't know it's losing yet. No ML model, no data science team — just you, Python, and a pile of behavioral and transactional data.

This is exactly the kind of problem that sits in the gap between "data work" and "data science." You don't need a gradient boosting classifier to identify at-risk customers. What you need is a principled, transparent scoring framework: one that takes measurable signals like declining purchase frequency, shrinking order values, silence on support tickets, and inactivity windows, and transforms them into a single ranked risk score your team can act on. That's what we're building in this lesson.

By the end of this tutorial, you'll have a complete, production-ready scoring pipeline in pandas. You'll compute behavioral flags from raw transaction and engagement data, combine them into a weighted composite risk score, bucket customers into risk tiers, and export a fully formatted Excel watch list that a customer success manager can open Monday morning and immediately start working. No black boxes, no unexplained scores — a transparent, auditable system.

What you'll learn:

• How to engineer behavioral flags from multiple raw data signals (recency, frequency, spend trajectory, engagement gaps)
• How to build a weighted composite scoring model using vectorized pandas operations
• How to normalize numeric signals to a common scale so weights mean what you think they mean
• How to rank, bucket, and tier customers into an actionable watch list
• How to export a production-quality, conditionally formatted Excel workbook with openpyxl

---

Prerequisites

You should be comfortable with pandas DataFrames, groupby aggregation, and merging. If you need a refresher on groupby mechanics, Grouping and Aggregating in pandas: groupby as the PivotTable Replacement covers the fundamentals. For the Excel export section, Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work is worth reviewing.

You'll also want:

• Python 3.9+, pandas 1.5+, openpyxl, numpy
• A working Jupyter environment (see Setting Up Python for Data Analysis if you need setup help)

---

The Dataset: What We're Working With

We'll simulate a realistic SaaS or subscription e-commerce dataset. In practice, you'd load this from a SQL database or multiple CSV files. We have three source tables:

1. transactions — one row per order: customer ID, order date, order value
2. logins — one row per login event: customer ID, login date
3. support_tickets — one row per ticket: customer ID, ticket date, ticket status

Let's build these:

import pandas as pd
import numpy as np
from datetime import date, timedelta
import random

random.seed(42)
np.random.seed(42)

# Reference date — treat this as "today"
TODAY = pd.Timestamp("2024-03-31")

# ── Build synthetic transactions ─────────────────────────────────────────────
n_customers = 500
customer_ids = [f"CUST_{i:04d}" for i in range(1, n_customers + 1)]

rows = []
for cid in customer_ids:
    # Healthy customers: frequent, recent orders
    # At-risk customers: older, sparse orders
    is_at_risk = random.random() < 0.35
    n_orders = random.randint(1, 3) if is_at_risk else random.randint(4, 18)
    for _ in range(n_orders):
        # At-risk customers: orders skewed toward the past
        if is_at_risk:
            days_ago = random.randint(90, 540)
        else:
            days_ago = random.randint(0, 180)
        order_date = TODAY - pd.Timedelta(days=days_ago)
        base_value = random.uniform(80, 600)
        # At-risk customers show declining spend
        spend_decay = random.uniform(0.4, 0.9) if is_at_risk else random.uniform(0.9, 1.1)
        rows.append({
            "customer_id": cid,
            "order_date": order_date,
            "order_value": round(base_value * spend_decay, 2),
            "is_at_risk": is_at_risk  # we'll drop this later; just for validation
        })

transactions = pd.DataFrame(rows)
transactions["order_date"] = pd.to_datetime(transactions["order_date"])

# ── Build synthetic logins ────────────────────────────────────────────────────
login_rows = []
for cid in customer_ids:
    is_at_risk = transactions[transactions["customer_id"] == cid]["is_at_risk"].iloc[0]
    n_logins = random.randint(0, 4) if is_at_risk else random.randint(5, 30)
    for _ in range(n_logins):
        days_ago = random.randint(0, 365)
        login_rows.append({
            "customer_id": cid,
            "login_date": TODAY - pd.Timedelta(days=days_ago)
        })

logins = pd.DataFrame(login_rows)
logins["login_date"] = pd.to_datetime(logins["login_date"])

# ── Build synthetic support tickets ──────────────────────────────────────────
ticket_rows = []
for cid in customer_ids:
    is_at_risk = transactions[transactions["customer_id"] == cid]["is_at_risk"].iloc[0]
    n_tickets = random.randint(0, 1) if not is_at_risk else random.randint(0, 4)
    for _ in range(n_tickets):
        days_ago = random.randint(0, 365)
        status = random.choice(["open", "open", "resolved"]) if is_at_risk else "resolved"
        ticket_rows.append({
            "customer_id": cid,
            "ticket_date": TODAY - pd.Timedelta(days=days_ago),
            "ticket_status": status
        })

support = pd.DataFrame(ticket_rows)
support["ticket_date"] = pd.to_datetime(support["ticket_date"])

# Drop the helper column before we begin our "real" pipeline
transactions = transactions.drop(columns=["is_at_risk"])

print(f"Transactions: {transactions.shape}")
print(f"Logins:       {logins.shape}")
print(f"Support:      {support.shape}")

This gives you a realistic, messy multi-table setup. The at-risk signal is baked into the simulation but deliberately invisible in the final DataFrames — we have to rediscover it from the behavioral data.

---

Step 1: Building Behavioral Feature Tables

The first job is to aggregate each raw event table down to one row per customer. We're collapsing a time series of events into a summary of behavior. This is the feature engineering layer.

Transaction Features

We want several signals from the transaction history:

• Recency: how many days since the last order
• Frequency: total number of orders
• Monetary: total lifetime spend
• Spend trend: is the customer's recent spend higher or lower than their historical average?

# ── Recency, Frequency, Monetary ─────────────────────────────────────────────
rfm = transactions.groupby("customer_id").agg(
    last_order_date=("order_date", "max"),
    total_orders=("order_value", "count"),
    total_spend=("order_value", "sum"),
    avg_order_value=("order_value", "mean"),
).reset_index()

rfm["days_since_last_order"] = (TODAY - rfm["last_order_date"]).dt.days

# ── Spend trend: compare last 90 days vs prior spend ─────────────────────────
cutoff = TODAY - pd.Timedelta(days=90)

recent_spend = (
    transactions[transactions["order_date"] >= cutoff]
    .groupby("customer_id")["order_value"]
    .sum()
    .rename("recent_90d_spend")
)

prior_spend = (
    transactions[transactions["order_date"] < cutoff]
    .groupby("customer_id")["order_value"]
    .sum()
    .rename("prior_spend")
)

rfm = rfm.merge(recent_spend, on="customer_id", how="left")
rfm = rfm.merge(prior_spend, on="customer_id", how="left")
rfm[["recent_90d_spend", "prior_spend"]] = rfm[["recent_90d_spend", "prior_spend"]].fillna(0)

# Spend ratio: >1.0 means accelerating, <1.0 means declining
# Add small epsilon to avoid division by zero
rfm["spend_ratio"] = rfm["recent_90d_spend"] / (rfm["prior_spend"] + 1e-6)

print(rfm[["customer_id", "days_since_last_order", "total_orders", "spend_ratio"]].head(8))

Login Engagement Features

# ── Login recency and frequency ───────────────────────────────────────────────
login_agg = logins.groupby("customer_id").agg(
    last_login_date=("login_date", "max"),
    total_logins=("login_date", "count"),
    logins_last_30d=("login_date", lambda x: (x >= TODAY - pd.Timedelta(days=30)).sum()),
).reset_index()

login_agg["days_since_last_login"] = (TODAY - login_agg["last_login_date"]).dt.days

Customers who never logged in won't appear in login_agg at all — they'll be missing rows after the join. We'll handle that momentarily.

Support Ticket Features

Open, unresolved tickets are a churn signal. So are customers who submitted multiple tickets recently — that often correlates with frustration.

# ── Support ticket signals ────────────────────────────────────────────────────
support_agg = support.groupby("customer_id").agg(
    total_tickets=("ticket_date", "count"),
    open_tickets=("ticket_status", lambda x: (x == "open").sum()),
    tickets_last_60d=("ticket_date", lambda x: (x >= TODAY - pd.Timedelta(days=60)).sum()),
).reset_index()

---

Step 2: Joining Features into a Master Customer Table

Now we merge everything into one flat scoring table. This is the foundation every downstream calculation will build on.

# Start with the full customer list from transactions
customer_base = rfm.copy()

# Left join so customers with no logins or no tickets are retained
customer_base = customer_base.merge(login_agg, on="customer_id", how="left")
customer_base = customer_base.merge(support_agg, on="customer_id", how="left")

# Fill NaN for customers who never logged in or never raised a ticket
customer_base["days_since_last_login"] = customer_base["days_since_last_login"].fillna(999)
customer_base["total_logins"] = customer_base["total_logins"].fillna(0)
customer_base["logins_last_30d"] = customer_base["logins_last_30d"].fillna(0)
customer_base["total_tickets"] = customer_base["total_tickets"].fillna(0)
customer_base["open_tickets"] = customer_base["open_tickets"].fillna(0)
customer_base["tickets_last_60d"] = customer_base["tickets_last_60d"].fillna(0)

# Cast integer columns
int_cols = ["total_orders", "total_logins", "logins_last_30d",
            "total_tickets", "open_tickets", "tickets_last_60d"]
customer_base[int_cols] = customer_base[int_cols].astype(int)

print(customer_base.shape)
print(customer_base.dtypes)

For more on diagnosing join quality after merges like this, Detecting and Resolving Data Quality Issues Across Merged DataFrames walks through the exact checks you should run.

---

Step 3: Calculating Behavioral Flags

Behavioral flags are binary (0 or 1) columns that indicate the presence or absence of a specific risk signal. They're the most readable and auditable part of the scoring model — your stakeholders can look at any customer row and immediately understand why they scored high.

We'll create seven flags:

Flag	Condition	Interpretation
flag_no_recent_order	days_since_last_order > 90	No purchase in 3 months
flag_low_frequency	total_orders <= 2	Thin purchase history
flag_declining_spend	spend_ratio < 0.5	Recent spend less than half of prior
flag_inactive_login	days_since_last_login > 60	Hasn't logged in for 2 months
flag_no_recent_login	logins_last_30d == 0	Zero logins in last 30 days
flag_open_tickets	open_tickets >= 1	Has at least one unresolved support ticket
flag_ticket_spike	tickets_last_60d >= 3	Flurry of tickets recently

customer_base["flag_no_recent_order"] = (
    customer_base["days_since_last_order"] > 90
).astype(int)

customer_base["flag_low_frequency"] = (
    customer_base["total_orders"] <= 2
).astype(int)

customer_base["flag_declining_spend"] = (
    customer_base["spend_ratio"] < 0.5
).astype(int)

customer_base["flag_inactive_login"] = (
    customer_base["days_since_last_login"] > 60
).astype(int)

customer_base["flag_no_recent_login"] = (
    customer_base["logins_last_30d"] == 0
).astype(int)

customer_base["flag_open_tickets"] = (
    customer_base["open_tickets"] >= 1
).astype(int)

customer_base["flag_ticket_spike"] = (
    customer_base["tickets_last_60d"] >= 3
).astype(int)

flag_cols = [c for c in customer_base.columns if c.startswith("flag_")]

# Quick sanity check: distribution of flags
print(customer_base[flag_cols].sum().sort_values(ascending=False))

This gives you an immediate read on how many customers are triggering each signal. If flag_no_recent_order fires for 85% of your customers, your threshold is probably wrong for your business.

---

Step 4: Normalizing Continuous Signals

Binary flags are great for readability, but they lose information — a customer who hasn't ordered in 91 days gets the same flag value as one who hasn't ordered in 540 days. For a richer composite score, we also want normalized continuous features.

The goal is to transform raw metrics onto a common 0–100 scale where higher always means more risk.

Min-max normalization is the right choice here over z-scores, because:

• It produces bounded, interpretable output (0 to 100)
• It doesn't require the assumption of normality
• It's explainable to non-technical stakeholders

For risk scores, we also need to think about directionality. For days_since_last_order, more days = more risk, so higher normalized values should indicate higher risk. For total_orders, more orders = less risk, so we invert.

def min_max_normalize(series, invert=False, clip_max=None):
    """
    Normalize a series to 0-100.
    invert=True flips the scale so higher raw value → lower risk score.
    clip_max: cap extreme outliers before normalizing.
    """
    s = series.copy()
    if clip_max is not None:
        s = s.clip(upper=clip_max)
    s_min, s_max = s.min(), s.max()
    if s_max == s_min:
        return pd.Series(np.zeros(len(s)), index=series.index)
    normalized = (s - s_min) / (s_max - s_min) * 100
    if invert:
        normalized = 100 - normalized
    return normalized

# Higher days since last order → higher risk
customer_base["score_recency"] = min_max_normalize(
    customer_base["days_since_last_order"], invert=False, clip_max=365
)

# Higher total orders → lower risk → invert
customer_base["score_frequency"] = min_max_normalize(
    customer_base["total_orders"], invert=True, clip_max=20
)

# Higher spend_ratio → lower risk → invert; clip at 3 (3x spend is ceiling)
customer_base["score_spend_trend"] = min_max_normalize(
    customer_base["spend_ratio"], invert=True, clip_max=3.0
)

# Higher days since last login → higher risk
customer_base["score_login_recency"] = min_max_normalize(
    customer_base["days_since_last_login"], invert=False, clip_max=365
)

# More open tickets → higher risk
customer_base["score_support"] = min_max_normalize(
    customer_base["open_tickets"], invert=False, clip_max=5
)

score_cols = [c for c in customer_base.columns if c.startswith("score_")]
print(customer_base[score_cols].describe().round(1))

---

Step 5: Calculating the Weighted Composite Risk Score

This is where your domain knowledge becomes math. We're assigning weights to each signal to reflect their relative importance to churn. These weights should ideally be informed by historical data or expert judgment — if you have labeled churn data, you can back-calculate which signals were actually predictive. Here we'll use a principled starting point that you can tune.

# ── Define weights ────────────────────────────────────────────────────────────
# Score components (continuous normalized signals)
SCORE_WEIGHTS = {
    "score_recency":       0.30,   # Most predictive: when did they last buy?
    "score_frequency":     0.20,   # How many orders total?
    "score_spend_trend":   0.20,   # Is their spend declining?
    "score_login_recency": 0.20,   # Are they still using the product?
    "score_support":       0.10,   # Open tickets signal frustration
}

# Flag bonuses — added on top of the weighted score
FLAG_BONUSES = {
    "flag_no_recent_order": 5,
    "flag_low_frequency":   3,
    "flag_declining_spend": 4,
    "flag_inactive_login":  3,
    "flag_no_recent_login": 2,
    "flag_open_tickets":    4,
    "flag_ticket_spike":    4,
}

assert abs(sum(SCORE_WEIGHTS.values()) - 1.0) < 1e-9, "Weights must sum to 1.0"

# ── Compute weighted base score ───────────────────────────────────────────────
customer_base["weighted_score"] = sum(
    customer_base[col] * weight
    for col, weight in SCORE_WEIGHTS.items()
)

# ── Add flag bonuses ──────────────────────────────────────────────────────────
customer_base["flag_bonus"] = sum(
    customer_base[flag] * bonus
    for flag, bonus in FLAG_BONUSES.items()
)

# ── Final risk score, capped at 100 ──────────────────────────────────────────
customer_base["risk_score"] = (
    customer_base["weighted_score"] + customer_base["flag_bonus"]
).clip(upper=100).round(1)

print(customer_base["risk_score"].describe())
print(f"\nCustomers scoring above 70: {(customer_base['risk_score'] > 70).sum()}")

The weighted score lands roughly in the 0–100 range from the normalized components, and flag bonuses push truly bad signals higher. The final clip(upper=100) ensures no customer exceeds the ceiling.

This design is intentionally transparent. You can look at any customer and decompose their score: "They scored 78 because their recency component was 65/100 on a 30% weight, their spend trend was 80/100 on a 20% weight, and they had three flag bonuses totaling 12 points."

For a deeper look at this kind of custom aggregation pattern, Weighted Averages, Percent of Total, and Custom Aggregations in pandas covers the mechanics in detail.

---

Step 6: Assigning Risk Tiers

A single score is useful; a tier label is actionable. Customer success teams think in categories: "Who do I call today? Who do I monitor? Who's fine?"

We'll use pd.cut() to bucket customers into four tiers, then assign a priority label and a recommended action.

# ── Define tier boundaries ────────────────────────────────────────────────────
tier_bins = [0, 30, 55, 75, 100]
tier_labels = ["Low", "Medium", "High", "Critical"]

customer_base["risk_tier"] = pd.cut(
    customer_base["risk_score"],
    bins=tier_bins,
    labels=tier_labels,
    include_lowest=True
)

# ── Map to recommended actions ────────────────────────────────────────────────
action_map = {
    "Critical": "Immediate outreach — CSM call this week",
    "High":     "Schedule check-in within 2 weeks",
    "Medium":   "Add to nurture sequence",
    "Low":      "No action — monitor monthly",
}

customer_base["recommended_action"] = customer_base["risk_tier"].map(action_map)

# ── Tier distribution ─────────────────────────────────────────────────────────
print(customer_base["risk_tier"].value_counts().sort_index())

---

Step 7: Ranking and Building the Watch List

Now we build the actual deliverable: a sorted, ranked watch list showing only the customers worth acting on (High and Critical tiers), plus a summary of the signals that drove their score.

# ── Sort by risk score descending, assign rank ────────────────────────────────
customer_base = customer_base.sort_values("risk_score", ascending=False).reset_index(drop=True)
customer_base["rank"] = customer_base.index + 1

# ── Build the watch list: High + Critical only ────────────────────────────────
watch_list = customer_base[
    customer_base["risk_tier"].isin(["High", "Critical"])
].copy()

# ── Select and rename columns for the final output ───────────────────────────
output_cols = {
    "rank":                  "Rank",
    "customer_id":           "Customer ID",
    "risk_score":            "Risk Score",
    "risk_tier":             "Risk Tier",
    "days_since_last_order": "Days Since Last Order",
    "total_orders":          "Total Orders",
    "spend_ratio":           "Spend Ratio (Recent/Prior)",
    "days_since_last_login": "Days Since Last Login",
    "open_tickets":          "Open Support Tickets",
    "total_spend":           "Lifetime Spend ($)",
    "flag_no_recent_order":  "Flag: No Recent Order",
    "flag_declining_spend":  "Flag: Declining Spend",
    "flag_inactive_login":   "Flag: Inactive Login",
    "flag_open_tickets":     "Flag: Open Tickets",
    "flag_ticket_spike":     "Flag: Ticket Spike",
    "recommended_action":    "Recommended Action",
}

watch_list_output = watch_list[list(output_cols.keys())].rename(columns=output_cols)
watch_list_output["Lifetime Spend ($)"] = watch_list_output["Lifetime Spend ($)"].round(2)
watch_list_output["Spend Ratio (Recent/Prior)"] = watch_list_output["Spend Ratio (Recent/Prior)"].round(3)

print(f"Watch list size: {len(watch_list_output)} customers")
print(watch_list_output.head(5).to_string())

For the ranking mechanics, Ranking and Window Calculations in pandas goes deeper on rank(), ties, and percent-rank if you want more nuanced ranking options.

---

Step 8: Exporting a Formatted Excel Watch List

A plain CSV export would technically work, but a customer success manager opening a 300-row spreadsheet needs visual hierarchy. We'll use openpyxl to build a workbook that communicates risk at a glance: red rows for Critical, amber for High, frozen headers, and auto-fitted columns.

import openpyxl
from openpyxl.styles import (
    PatternFill, Font, Alignment, Border, Side, numbers
)
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows

OUTPUT_PATH = "churn_watch_list.xlsx"

wb = openpyxl.Workbook()

# ── Sheet 1: Watch List ───────────────────────────────────────────────────────
ws_watch = wb.active
ws_watch.title = "Watch List"

# Define color fills
fills = {
    "Critical": PatternFill(start_color="FF4C4C", end_color="FF4C4C", fill_type="solid"),
    "High":     PatternFill(start_color="FFB347", end_color="FFB347", fill_type="solid"),
    "header":   PatternFill(start_color="1F3864", end_color="1F3864", fill_type="solid"),
}

header_font   = Font(bold=True, color="FFFFFF", size=11)
data_font     = Font(size=10)
center_align  = Alignment(horizontal="center", vertical="center", wrap_text=False)
left_align    = Alignment(horizontal="left",   vertical="center")

thin = Side(style="thin", color="D3D3D3")
cell_border = Border(bottom=thin)

# Write headers
headers = list(watch_list_output.columns)
for col_idx, header in enumerate(headers, start=1):
    cell = ws_watch.cell(row=1, column=col_idx, value=header)
    cell.fill   = fills["header"]
    cell.font   = header_font
    cell.alignment = center_align

# Write data rows
for row_idx, row in enumerate(
    dataframe_to_rows(watch_list_output, index=False, header=False), start=2
):
    tier = watch_list_output.iloc[row_idx - 2]["Risk Tier"]
    row_fill = fills.get(tier)

    for col_idx, value in enumerate(row, start=1):
        cell = ws_watch.cell(row=row_idx, column=col_idx, value=value)
        cell.font      = data_font
        cell.border    = cell_border
        col_name = headers[col_idx - 1]

        if col_name in ("Rank", "Risk Score", "Total Orders",
                        "Open Support Tickets", "Days Since Last Order",
                        "Days Since Last Login"):
            cell.alignment = center_align
        else:
            cell.alignment = left_align

        # Apply tier color to Risk Score and Risk Tier columns only
        if col_name in ("Risk Score", "Risk Tier") and row_fill:
            cell.fill = row_fill
            cell.font = Font(size=10, bold=True, color="FFFFFF")

        # Format currency column
        if col_name == "Lifetime Spend ($)":
            cell.number_format = '"$"#,##0.00'

# Freeze the header row
ws_watch.freeze_panes = "A2"

# Auto-fit column widths (approximation)
for col_idx, col_name in enumerate(headers, start=1):
    max_len = max(
        len(str(col_name)),
        watch_list_output.iloc[:, col_idx - 1].astype(str).str.len().max()
    )
    ws_watch.column_dimensions[get_column_letter(col_idx)].width = min(max_len + 2, 40)

Adding a Score Summary Sheet

A second sheet gives leadership a one-page tier summary without the full detail.

# ── Sheet 2: Tier Summary ─────────────────────────────────────────────────────
ws_summary = wb.create_sheet("Tier Summary")

tier_summary = (
    customer_base.groupby("risk_tier", observed=True)
    .agg(
        Customer_Count=("customer_id", "count"),
        Avg_Risk_Score=("risk_score", "mean"),
        Total_Lifetime_Spend=("total_spend", "sum"),
    )
    .reset_index()
    .rename(columns={"risk_tier": "Risk Tier"})
    .sort_values("Avg_Risk_Score", ascending=False)
)

tier_summary["Avg_Risk_Score"] = tier_summary["Avg_Risk_Score"].round(1)
tier_summary["Total_Lifetime_Spend"] = tier_summary["Total_Lifetime_Spend"].round(2)

summary_headers = list(tier_summary.columns)
for col_idx, header in enumerate(summary_headers, start=1):
    cell = ws_summary.cell(row=1, column=col_idx, value=header)
    cell.fill = fills["header"]
    cell.font = header_font
    cell.alignment = center_align

for row_idx, row in enumerate(
    dataframe_to_rows(tier_summary, index=False, header=False), start=2
):
    for col_idx, value in enumerate(row, start=1):
        cell = ws_summary.cell(row=row_idx, column=col_idx, value=value)
        cell.font = Font(size=10)
        cell.alignment = center_align

for col_idx in range(1, len(summary_headers) + 1):
    ws_summary.column_dimensions[get_column_letter(col_idx)].width = 22

# ── Save workbook ─────────────────────────────────────────────────────────────
wb.save(OUTPUT_PATH)
print(f"Saved: {OUTPUT_PATH}")

The resulting workbook has two sheets: a color-coded watch list sorted by risk score, and a tier summary your head of customer success can screenshot for the weekly meeting.

For a full deep-dive on multi-sheet Excel reports with pivot tables and formatting, Building Summary Reports with pandas pivot_table and to_excel covers every corner case.

---

Step 9: Validating Your Scoring Model

Before you ship anything to a stakeholder, you need to sanity-check the model. Three questions to answer:

1. Do the top-ranked customers actually look different from the bottom-ranked?

# Compare mean feature values across tiers
validation_cols = [
    "days_since_last_order", "total_orders", "spend_ratio",
    "days_since_last_login", "open_tickets"
]

tier_profile = (
    customer_base.groupby("risk_tier", observed=True)[validation_cols]
    .mean()
    .round(2)
)
print(tier_profile)

You should see monotonically worsening values as you move from Low to Critical. If Critical customers have the same days_since_last_order as Low customers, your thresholds or weights need adjusting.

2. Are any flags completely uninformative?

flag_by_tier = (
    customer_base.groupby("risk_tier", observed=True)[flag_cols]
    .mean()
    .round(3)
)
print(flag_by_tier)

A flag that fires at the same rate across all tiers is adding noise, not signal. Remove it or investigate why it's not discriminating.

3. Is the score distribution reasonable?

print(customer_base["risk_score"].quantile([0.1, 0.25, 0.5, 0.75, 0.9, 0.95, 0.99]))

A healthy distribution has a spread across the range, not a pile-up at the extremes. If 60% of customers score above 70, your normalization or weights are miscalibrated.

---

Step 10: Making the Pipeline Reusable

Right now this is a linear script. In production, you want to run it weekly on fresh data with no manual edits. The key refactors:

# ── Put all tunable parameters at the top ────────────────────────────────────
CONFIG = {
    "today": pd.Timestamp("2024-03-31"),          # Override for backtesting
    "output_path": "churn_watch_list.xlsx",
    "watch_list_tiers": ["High", "Critical"],

    # Behavioral thresholds
    "no_recent_order_days": 90,
    "inactive_login_days": 60,
    "low_frequency_threshold": 2,
    "spend_ratio_threshold": 0.5,
    "ticket_spike_threshold": 3,

    # Score component weights (must sum to 1.0)
    "score_weights": {
        "score_recency":       0.30,
        "score_frequency":     0.20,
        "score_spend_trend":   0.20,
        "score_login_recency": 0.20,
        "score_support":       0.10,
    },

    # Tier bin boundaries
    "tier_bins":   [0, 30, 55, 75, 100],
    "tier_labels": ["Low", "Medium", "High", "Critical"],
}

Then wrap each stage as a function:

def build_rfm(transactions: pd.DataFrame, today: pd.Timestamp) -> pd.DataFrame:
    """Aggregate transaction data to customer-level RFM features."""
    rfm = transactions.groupby("customer_id").agg(
        last_order_date=("order_date", "max"),
        total_orders=("order_value", "count"),
        total_spend=("order_value", "sum"),
        avg_order_value=("order_value", "mean"),
    ).reset_index()
    rfm["days_since_last_order"] = (today - rfm["last_order_date"]).dt.days

    cutoff = today - pd.Timedelta(days=90)
    recent = transactions[transactions["order_date"] >= cutoff].groupby("customer_id")["order_value"].sum().rename("recent_90d_spend")
    prior  = transactions[transactions["order_date"] < cutoff].groupby("customer_id")["order_value"].sum().rename("prior_spend")
    rfm = rfm.merge(recent, on="customer_id", how="left").merge(prior, on="customer_id", how="left")
    rfm[["recent_90d_spend", "prior_spend"]] = rfm[["recent_90d_spend", "prior_spend"]].fillna(0)
    rfm["spend_ratio"] = rfm["recent_90d_spend"] / (rfm["prior_spend"] + 1e-6)
    return rfm


def add_flags(df: pd.DataFrame, config: dict) -> pd.DataFrame:
    """Add binary behavioral flags to customer table."""
    df = df.copy()
    df["flag_no_recent_order"] = (df["days_since_last_order"] > config["no_recent_order_days"]).astype(int)
    df["flag_low_frequency"]   = (df["total_orders"] <= config["low_frequency_threshold"]).astype(int)
    df["flag_declining_spend"] = (df["spend_ratio"] < config["spend_ratio_threshold"]).astype(int)
    df["flag_inactive_login"]  = (df["days_since_last_login"] > config["inactive_login_days"]).astype(int)
    df["flag_no_recent_login"] = (df["logins_last_30d"] == 0).astype(int)
    df["flag_open_tickets"]    = (df["open_tickets"] >= 1).astype(int)
    df["flag_ticket_spike"]    = (df["tickets_last_60d"] >= config["ticket_spike_threshold"]).astype(int)
    return df


def score_customers(df: pd.DataFrame, config: dict) -> pd.DataFrame:
    """Compute normalized scores, weighted composite, and risk tiers."""
    df = df.copy()
    df["score_recency"]       = min_max_normalize(df["days_since_last_order"], invert=False, clip_max=365)
    df["score_frequency"]     = min_max_normalize(df["total_orders"], invert=True, clip_max=20)
    df["score_spend_trend"]   = min_max_normalize(df["spend_ratio"], invert=True, clip_max=3.0)
    df["score_login_recency"] = min_max_normalize(df["days_since_last_login"], invert=False, clip_max=365)
    df["score_support"]       = min_max_normalize(df["open_tickets"], invert=False, clip_max=5)

    df["weighted_score"] = sum(df[col] * w for col, w in config["score_weights"].items())
    df["flag_bonus"] = sum(df[f] * b for f, b in FLAG_BONUSES.items())
    df["risk_score"] = (df["weighted_score"] + df["flag_bonus"]).clip(upper=100).round(1)

    df["risk_tier"] = pd.cut(
        df["risk_score"],
        bins=config["tier_bins"],
        labels=config["tier_labels"],
        include_lowest=True
    )
    df["recommended_action"] = df["risk_tier"].map(action_map)
    return df

Wrapping the pipeline in functions like this makes it trivially schedulable. For a full framework on building schedulable pipelines with pandas, Building and Automating Recurring Reports with pandas covers the scheduling infrastructure.

---

Hands-On Exercise

Take the pipeline you've built and extend it with the following challenges:

Challenge 1: Add a revenue-at-risk column

Create a column revenue_at_risk that estimates how much spend might be lost if the customer churns. Use a simple heuristic: avg_order_value * (total_orders / months_as_customer), where months as customer is calculated from their first order to today. Add this column to both the master table and the Excel output.

Challenge 2: Tune the tier boundaries for a real use case

Pretend you're presenting to a team of four customer success managers. Each can handle 25 customer outreach actions per week. Adjust the tier_bins in CONFIG so that "Critical" and "High" combined produce exactly 100 customers. What does this do to the composition of each tier?

Challenge 3: Add a cohort column

Add a customer_cohort column that groups customers by their first order quarter (e.g., "2023-Q1"). Then add a third sheet to the Excel workbook showing average risk scores by cohort — which acquisition cohorts are churning fastest?

Challenge 4: Build a flag explanation column

Create a column active_flags that is a comma-separated list of the flag names that are set to 1 for each customer (e.g., "No Recent Order, Declining Spend, Open Tickets"). Use vectorized string operations to build this. Hint: look at applying a row-wise lambda across the flag columns, or use a list comprehension with pd.Series.

---

Common Mistakes & Troubleshooting

"My risk scores are all bunched between 40 and 60"

This almost always means your normalization isn't spreading the data properly. Run customer_base[score_cols].describe() and check that the min and max of each score component are actually close to 0 and 100. If a column like score_support has a max of 12 (because almost no one has more than 1 open ticket), your clip_max=5 might be appropriate but the column itself may have low variance. Consider whether the signal is worth including, or whether the flag version is sufficient.

"Some customers have NaN risk scores"

This usually means NaN values snuck through one of the feature columns before normalization. The min_max_normalize function will propagate NaN through arithmetic. Add an assertion after each feature engineering step:

assert customer_base[score_cols].isna().sum().sum() == 0, "NaN values found in score components"

If the assertion fires, trace back to which column has NaN using .isna().sum().

"The watch list has 0 or 1 Critical customers"

Your tier bin boundaries are probably too high. Print the percentiles of risk_score and choose boundaries that correspond to meaningful percentile cutoffs in your actual distribution. The boundaries [0, 30, 55, 75, 100] were chosen for the synthetic data; real data may require [0, 25, 50, 70, 100] or entirely different values.

"The Excel file opens but columns are misaligned / values look wrong"

The most common cause is column order mismatch between watch_list_output.columns and the dataframe_to_rows iteration. Always use dataframe_to_rows(df, index=False, header=False) and verify the column names are in the exact order you expect before writing.

"Spend ratio for new customers is artificially extreme"

New customers with only one or two orders may have prior_spend = 0, making their spend ratio either very large (if they recently ordered) or exactly 0 (if their only order was old). Consider adding a flag flag_new_customer for accounts with fewer than 3 orders and suppressing the spend trend score for them — it's not interpretable with thin history.

"The model seems to flag churned customers I already know about, but misses sneaky ones"

Your behavioral signals are capturing obvious, overt disengagement. Some customers churn silently — they just quietly stop, with no ticket spike, gradual engagement decline, or obvious spend drop. Consider adding signals like order interval elongation (the time between consecutive orders is growing) or product mix narrowing (buying fewer product categories over time). These require more sophisticated time-series feature engineering; Working with Dates and Time Series in pandas covers the rolling window techniques that make this possible.

---

Summary & Next Steps

You've built a complete, production-grade churn scoring pipeline entirely in pandas. Here's what you accomplished:

1. Feature engineering: Aggregated raw transaction, login, and support data into per-customer behavioral signals covering recency, frequency, spend trajectory, engagement, and support friction.

2. Behavioral flags: Created seven binary flags that make individual customer risk transparent and auditable — anyone can read a row and understand why a customer is flagged.

3. Normalized continuous scoring: Applied min-max normalization with directional awareness and outlier clipping to transform raw metrics into a consistent 0–100 scale.

4. Weighted composite model: Combined continuous scores and binary flag bonuses into a final risk score, with weights exposed as named constants that business stakeholders can challenge and tune.

5. Tiering and ranking: Used pd.cut() to assign risk tiers, sorted the full table by risk score, and filtered to an actionable watch list.

6. Excel export: Built a formatted two-sheet workbook with color-coded risk indicators, frozen headers, currency formatting, and auto-fitted columns using openpyxl.

7. Validation and reusability: Added distribution checks and refactored the pipeline into parameterized functions ready for weekly automation.

Where to go from here:

• If you have historical labeled churn data (customers who actually churned), use it to validate whether high-scoring customers were disproportionately likely to churn. If they were, you can back-calculate optimal weights using simple regression. If they weren't, your signals need revisiting.

• Extend the flag and scoring logic with time-series signals. Order interval elongation, product category narrowing, and declining session length are all computable with pandas rolling windows and shift-based comparisons — see Comparing Periods and Calculating Month-over-Month, Year-over-Year, and Rolling Changes in pandas for the mechanics.

• For longer-horizon cohort analysis of who actually churned from each acquisition period, Cohort Analysis in pandas: Calculating Retention, Churn, and Lifetime Value from Transaction Data builds exactly the kind of historical ground truth you'd need.

• Once you've validated the model, wrap it in a scheduled script that pulls fresh data, regenerates the Excel file, and emails it to your team — no manual work required.