lss_process_analytics.py

# =============================================================
# Lean Six Sigma Process Analytics — Proposal Cycle Time
# Author: Omkar Pallerla | MS Business Analytics, ASU
# DMAIC: Gentech $60B — 15% cycle time reduction
# Databricks monitoring pipeline included
# =============================================================

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
from scipy import stats
import warnings
warnings.filterwarnings('ignore')

plt.style.use('dark_background')
np.random.seed(42)
COLORS = ['#4f9cf9','#06d6a0','#7c3aed','#f59e0b','#ef4444']

# ══════════════════════════════════════════════════════════════
# GENERATE REALISTIC PROPOSAL DATA
# ══════════════════════════════════════════════════════════════
n_before = 300
n_after  = 200

# Before DMAIC: mean=31.6, std=14
before = np.random.normal(31.6, 14, n_before).clip(5, 80)

# After DMAIC: mean=26.9, std=9.5 (also tighter distribution)
after = np.random.normal(26.9, 9.5, n_after).clip(5, 60)

df_before = pd.DataFrame({
    'cycle_days':  before,
    'phase':       'Before',
    'brand':       np.random.choice(['Enterprise','SMB','Public Sector','Healthcare'], n_before),
    'region':      np.random.choice(['NA','EMEA','APAC','LATAM'], n_before),
    'bid_size':    np.random.choice(['Small (<50K)','Medium (50-500K)','Large (>500K)'], n_before,
                                     p=[0.4,0.4,0.2]),
})
df_after = pd.DataFrame({
    'cycle_days':  after,
    'phase':       'After',
    'brand':       np.random.choice(['Enterprise','SMB','Public Sector','Healthcare'], n_after),
    'region':      np.random.choice(['NA','EMEA','APAC','LATAM'], n_after),
    'bid_size':    np.random.choice(['Small (<50K)','Medium (50-500K)','Large (>500K)'], n_after,
                                     p=[0.4,0.4,0.2]),
})
df = pd.concat([df_before, df_after], ignore_index=True)
df['defect'] = (df['cycle_days'] > 35).astype(int)
df['date']   = pd.date_range('2022-01-01', periods=len(df), freq='D')

# ══════════════════════════════════════════════════════════════
# DMAIC CALCULATIONS
# ══════════════════════════════════════════════════════════════
def sigma_level(dpmo):
    """Convert DPMO to sigma level."""
    return round(stats.norm.ppf(1 - dpmo/1_000_000) + 1.5, 2)

def calc_metrics(data, label):
    mean   = data['cycle_days'].mean()
    std    = data['cycle_days'].std()
    defects= data['defect'].sum()
    n      = len(data)
    dpmo   = (defects / n) * 1_000_000
    sigma  = sigma_level(dpmo)
    print(f"\n{'─'*40}")
    print(f"PHASE: {label}")
    print(f"  Mean cycle time:  {mean:.1f} days")
    print(f"  Std deviation:    {std:.1f} days")
    print(f"  Defect rate:      {defects}/{n} ({defects/n*100:.1f}%)")
    print(f"  DPMO:             {dpmo:,.0f}")
    print(f"  Sigma Level:      {sigma}")
    return {'mean': mean, 'std': std, 'dpmo': dpmo, 'sigma': sigma, 'defects': defects}

print("=" * 50)
print("DMAIC METRICS — GENTECH PROPOSAL CYCLE TIME")
print("=" * 50)
m_before = calc_metrics(df_before, 'BEFORE DMAIC')
m_after  = calc_metrics(df_after,  'AFTER DMAIC')

improvement = (m_before['mean'] - m_after['mean']) / m_before['mean'] * 100
print(f"\n{'='*50}")
print(f"✅ Cycle time improvement: {improvement:.1f}%")
print(f"✅ Sigma improvement:      {m_before['sigma']} → {m_after['sigma']}")
print(f"✅ DPMO reduction:         {m_before['dpmo']:,.0f} → {m_after['dpmo']:,.0f}")

# ── ROOT CAUSE ANALYSIS ──────────────────────────────────────
root_causes = pd.DataFrame({
    'cause':    ['Manual CRM Data Entry','Sequential Legal+Pricing','Redundant Approval Loops',
                 'No Training Standards','Tool Integration Gaps'],
    'category': ['Technology','Process','Process','People','Technology'],
    'defects':  [142, 98, 67, 45, 28],
    'pct':      [37.1, 25.7, 17.5, 11.8, 7.3],
    'solution': ['RPA Automation','Parallel Processing','Bid Threshold Tiering',
                 'Standard Training','CRM Integration'],
    'days_saved':[4.2, 3.1, 1.4, 1.2, 0.8]
})
root_causes['cum_pct'] = root_causes['pct'].cumsum()
print("\nRoot Cause Analysis:")
print(root_causes[['cause','defects','pct','solution','days_saved']].to_string(index=False))

# ── EXPORT KPIs ──────────────────────────────────────────────
kpi_df = pd.DataFrame({
    'metric':  ['Mean Cycle Time','Sigma Level','DPMO','Rework Rate %','% On Time'],
    'before':  [31.6, 2.08, 281053, 12.0, 71.9],
    'after':   [26.9, 2.78, 158655,  2.0, 85.4],
    'change':  ['-4.7 days','+0.70','−44%','−83%','+13.5pts']
})
kpi_df.to_csv('outputs/lss_kpi_results.csv', index=False)

# Daily KPI tracking for Databricks pipeline
daily_kpi = df_after.copy()
daily_kpi['rolling_avg']    = daily_kpi['cycle_days'].expanding().mean()
daily_kpi['sla_breach']     = daily_kpi['cycle_days'] > 30
daily_kpi['alert_triggered']= daily_kpi['rolling_avg'] > 30
daily_kpi.to_csv('outputs/daily_kpi_tracking.csv', index=False)
print("Exported: outputs/lss_kpi_results.csv, outputs/daily_kpi_tracking.csv")

# ── DATABRICKS MONITORING PIPELINE STUB ─────────────────────
pipeline_code = '''
# databricks/proposal_kpi_pipeline.py
# Runs daily at 6 AM via Cloud Composer (Airflow)

from pyspark.sql import SparkSession
from pyspark.sql.functions import *

spark = SparkSession.builder.appName("LSS_KPI_Monitor").getOrCreate()

# Ingest from Salesforce CRM daily export
proposals = spark.read.format("delta").table("bronze.salesforce_proposals")

# Transform — compute KPIs
kpis = proposals.withColumn(
    "cycle_days", datediff(col("submitted_date"), col("request_date"))
).withColumn(
    "is_sla_breach", (col("cycle_days") > 30).cast("integer")
).withColumn(
    "rolling_7d_avg", avg("cycle_days").over(
        Window.orderBy("submitted_date").rowsBetween(-6, 0)
    )
).withColumn("loaded_at", current_timestamp())

# Write to Silver layer
kpis.write.format("delta").mode("append").saveAsTable("silver.proposal_kpis")

# Alert logic
alerts = kpis.filter(col("rolling_7d_avg") > 30)
if alerts.count() > 0:
    # Trigger email alert via SendGrid API
    alert_msg = f"⚠️ SLA Alert: 7-day avg cycle time = {alerts.first().rolling_7d_avg:.1f} days"
    print(alert_msg)
    # spark.sql(f"INSERT INTO gold.alerts VALUES (now(), '{alert_msg}')")
'''
import os; os.makedirs('databricks', exist_ok=True)
with open('databricks/proposal_kpi_pipeline.py', 'w') as f:
    f.write(pipeline_code)

# ── VISUALIZATIONS ───────────────────────────────────────────
fig, axes = plt.subplots(2, 3, figsize=(20, 12))
fig.patch.set_facecolor('#0d1117')

# Before/After distributions
ax = axes[0, 0]
ax.hist(before, bins=30, alpha=0.7, color='#ef4444', label=f'Before (μ={m_before["mean"]:.1f}d)', density=True)
ax.hist(after,  bins=30, alpha=0.7, color='#06d6a0', label=f'After (μ={m_after["mean"]:.1f}d)',  density=True)
ax.axvline(35, color='white', linestyle='--', alpha=0.6, label='SLA Limit (35 days)')
ax.set_xlabel('Cycle Time (days)')
ax.set_title('Proposal Cycle Time Distribution', color='white', pad=12)
ax.legend(fontsize=9)

# Pareto chart
ax = axes[0, 1]
ax2 = ax.twinx()
bars = ax.bar(root_causes['cause'], root_causes['defects'], color=COLORS[:5])
ax2.plot(root_causes['cause'], root_causes['cum_pct'], 'o-', color='#f59e0b', lw=2, ms=6)
ax2.axhline(80, color='white', linestyle='--', alpha=0.4)
ax.set_title('Pareto — Root Causes', color='white', pad=12)
ax.set_ylabel('Defect Count'); ax2.set_ylabel('Cumulative %')
ax.tick_params(axis='x', rotation=25, labelsize=7)

# Region analysis
ax = axes[0, 2]
region_df = df.groupby(['region','phase'])['cycle_days'].mean().unstack()
x = np.arange(len(region_df))
ax.bar(x - 0.2, region_df['Before'], 0.4, label='Before', color='#ef4444', alpha=0.8)
ax.bar(x + 0.2, region_df['After'],  0.4, label='After',  color='#06d6a0', alpha=0.8)
ax.set_xticks(x); ax.set_xticklabels(region_df.index)
ax.set_title('Avg Cycle Time by Region', color='white', pad=12)
ax.legend(); ax.set_ylabel('Days')

# KPI improvement
ax = axes[1, 0]
metrics  = ['Cycle\nTime (days)','Sigma\nLevel','DPMO\n(×1000)','Rework\nRate %']
b_vals   = [31.6, 2.08, 281.1, 12.0]
a_vals   = [26.9, 2.78, 158.7,  2.0]
x = np.arange(len(metrics))
ax.bar(x - 0.2, b_vals, 0.4, label='Before', color='#ef4444', alpha=0.8, borderradius=4 if False else 0)
ax.bar(x + 0.2, a_vals, 0.4, label='After',  color='#06d6a0', alpha=0.8)
ax.set_xticks(x); ax.set_xticklabels(metrics)
ax.set_title('DMAIC Improvement — Key Metrics', color='white', pad=12)
ax.legend()

# Control chart (after implementation)
ax = axes[1, 1]
dates_after = pd.date_range('2023-06-01', periods=len(after), freq='D')
rolling_avg = pd.Series(after).rolling(7).mean()
ax.plot(range(len(after)), after, alpha=0.3, color='#4f9cf9', lw=0.8)
ax.plot(range(len(after)), rolling_avg, color='#06d6a0', lw=2, label='7-day rolling avg')
ax.axhline(26.9, color='white', linestyle='--', alpha=0.5, label='New mean (26.9d)')
ax.axhline(30.0, color='#f59e0b', linestyle='--', alpha=0.7, label='Alert threshold (30d)')
ax.fill_between(range(len(after)), 0, 30, alpha=0.05, color='#06d6a0')
ax.set_xlabel('Days after implementation')
ax.set_ylabel('Cycle Time (days)')
ax.set_title('Control Chart — Post-DMAIC Monitoring', color='white', pad=12)
ax.legend(fontsize=8)

# Solutions impact
ax = axes[1, 2]
solutions = root_causes[['solution','days_saved']].sort_values('days_saved', ascending=True)
ax.barh(solutions['solution'], solutions['days_saved'], color=COLORS[:5])
ax.set_xlabel('Days Saved per Proposal')
ax.set_title('Solution Impact — Days Saved', color='white', pad=12)
for i, v in enumerate(solutions['days_saved']):
    ax.text(v + 0.05, i, f'{v}d', va='center', color='white', fontsize=9)

plt.tight_layout()
plt.savefig('outputs/lss_analysis.png', dpi=150, bbox_inches='tight', facecolor='#0d1117')
print("Saved: outputs/lss_analysis.png")
plt.show()