Analysing Telecom Customer Retention

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "cust_id":       ["C001","C002","C003","C004","C005","C006","C007","C008",
                      "C009","C010","C011","C012","C013","C014","C015","C016",
                      "C017","C018","C019","C020"],
    "contract":      ["Month-to-Month","Annual","Month-to-Month","Two-Year",
                      "Month-to-Month","Annual","Month-to-Month","Two-Year",
                      "Month-to-Month","Annual","Month-to-Month","Two-Year",
                      "Month-to-Month","Annual","Month-to-Month","Two-Year",
                      "Month-to-Month","Annual","Month-to-Month","Two-Year"],
    "tenure_months": [3,18,7,28,2,24,11,36,5,14,9,42,4,30,6,48,8,22,13,38],
    "monthly_spend": [48,62,44,78,41,68,46,82,43,65,47,88,45,71,49,85,42,64,50,80],
    "quality_score": [2,4,3,5,2,4,2,5,3,4,2,5,2,3,3,5,2,4,3,5],
    "data_gb":       [4.2,8.1,3.8,12.4,2.9,9.6,4.8,14.2,3.4,7.8,5.1,16.8,
                      3.1,10.4,4.5,15.6,3.8,8.9,5.8,13.2],
    "churned":       [1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0]
})

overall_churn = df["churned"].mean()
print(f"Subscribers: {len(df)} | Churned: {df['churned'].sum()} | Rate: {overall_churn:.1%}")

# Tenure bands
df["tenure_band"] = pd.cut(
    df["tenure_months"],
    bins   = [0, 6, 12, 24, float("inf")],
    labels = ["0-6 months","7-12 months","13-24 months","24+ months"]
)

# Churn and revenue-at-risk by contract type
contract_stats = df.groupby("contract").agg(
    subscribers   = ("cust_id",        "count"),
    churned_count = ("churned",        "sum"),
    churn_rate    = ("churned",        "mean"),
    mean_spend    = ("monthly_spend",  "mean")
).round(3).reset_index()

contract_stats["rev_at_risk"] = (
    contract_stats["churned_count"] * contract_stats["mean_spend"]
).round(0)
contract_stats["churn_pct"] = (contract_stats["churn_rate"] * 100).round(0)
contract_stats = contract_stats.sort_values("churn_rate", ascending=False)

print("\nChurn and revenue-at-risk by contract type:")
print(contract_stats[["contract","subscribers","churned_count",
                       "churn_pct","mean_spend","rev_at_risk"]].to_string(index=False))

# Churn by tenure band
tenure_stats = df.groupby("tenure_band", observed=True).agg(
    subscribers   = ("cust_id",        "count"),
    churned_count = ("churned",        "sum"),
    churn_rate    = ("churned",        "mean"),
    mean_tenure   = ("tenure_months",  "mean")
).round(2).reset_index()
tenure_stats["churn_pct"] = (tenure_stats["churn_rate"] * 100).round(0)

print("Churn by tenure band:")
print(tenure_stats[["tenure_band","subscribers","churned_count",
                     "churn_pct","mean_tenure"]].to_string(index=False))

# Churn by quality score
quality_stats = df.groupby("quality_score").agg(
    count      = ("cust_id",        "count"),
    churned    = ("churned",        "sum"),
    churn_rate = ("churned",        "mean"),
    mean_spend = ("monthly_spend",  "mean")
).round(2).reset_index()
quality_stats["churn_pct"] = (quality_stats["churn_rate"] * 100).round(0)

print("\nChurn by quality score (1=poor, 5=excellent):")
print(quality_stats[["quality_score","count","churned",
                      "churn_pct","mean_spend"]].to_string(index=False))

# Correlations with churn
print("\nCorrelations with churned:")
for col in ["quality_score","data_gb","tenure_months","monthly_spend"]:
    r = df[col].corr(df["churned"]).round(3)
    print(f"  {col:<18} r = {r:+.3f}")

# Pivot: churn rate by contract x tenure band
pivot_churn = pd.pivot_table(
    df,
    index      = "contract",
    columns    = "tenure_band",
    values     = "churned",
    aggfunc    = "mean",
    fill_value = 0
).round(2)

print("Churn rate pivot (contract x tenure band):")
print((pivot_churn * 100).round(0).to_string())

# Revenue-at-risk pivot: filter to churned subscribers only
pivot_rev = pd.pivot_table(
    df[df["churned"] == 1],
    index      = "contract",
    columns    = "tenure_band",
    values     = "monthly_spend",
    aggfunc    = "sum",
    fill_value = 0
)
print("\nMonthly revenue at risk by segment ($):")
print(pivot_rev.to_string())

# Profile comparison: churned vs retained
profile = df.groupby("churned").agg(
    count         = ("cust_id",        "count"),
    mean_spend    = ("monthly_spend",  "mean"),
    mean_tenure   = ("tenure_months",  "mean"),
    mean_quality  = ("quality_score",  "mean"),
    mean_data     = ("data_gb",        "mean")
).round(1)
profile.index = profile.index.map({0:"Retained", 1:"Churned"})
print("\nChurned vs Retained profile:")
print(profile.to_string())

# Target segment: Month-to-Month subscribers in first 12 months
target = df[
    (df["contract"] == "Month-to-Month") &
    (df["tenure_months"] <= 12)
].copy()

print(f"Target segment size: {len(target)} subscribers")
print(f"Currently churned:   {target['churned'].sum()} of {len(target)}")
print(f"Monthly rev at risk: ${target[target['churned']==1]['monthly_spend'].sum()}")

# If we convert 50% of this segment to Annual — what revenue is saved?
conversion_rate = 0.50
subscribers_saved = int(len(target) * conversion_rate)
avg_spend = target["monthly_spend"].mean()
monthly_rev_saved = subscribers_saved * avg_spend

print(f"\nIf 50% convert to Annual contract:")
print(f"  Subscribers retained:  ~{subscribers_saved}")
print(f"  Monthly revenue saved: ~${monthly_rev_saved:.0f}")
print(f"  Annual revenue saved:  ~${monthly_rev_saved * 12:.0f}")

# Max retention spend per subscriber (breakeven)
# Worth spending up to 3 months of revenue to retain a subscriber
retention_budget_per_sub = avg_spend * 3
print(f"\nBreakeven retention spend per subscriber: ${retention_budget_per_sub:.0f}")
print(f"(= 3 months of avg spend at ${avg_spend:.0f}/month)")

# Quality score improvement opportunity
low_quality = df[(df["quality_score"] <= 2) & (df["churned"] == 1)]
print(f"\nChurned subscribers with quality score <= 2: {len(low_quality)}")
print(f"  These represent ${low_quality['monthly_spend'].sum()} monthly revenue")
print(f"  All could be retained with targeted service quality improvement")