# AI Churn Management — Dashboard Card Queries & Logic

> **All data is REAL**, pulled from your production database.  
> Source file: `app/Filament/Pages/RiskManagement/AiChurnManagement.php` → `computeAllData()`

---

## Data Coverage Status

| Metric | App 1004 | All Apps |
|--------|----------|----------|
| Total distinct customers in `tbl_customer_subscriptions` | **823,317** | — |
| Currently in `churn_feature_store` | **201** | **715** |
| Currently in `churn_predictions` | **201** | **715** |
| **Remaining to sync** | **~823,116** | — |

> [!IMPORTANT]
> Right now only **~200 customers per app** are synced (100 churned + 100 active sample).  
> To get ALL 823K customers for app 1004, run the full sync:  
> `php artisan churn:sync-features --app_id=1004 --batch=100`  
> Then re-run `python3 train_model.py && python3 predict_churn.py`

---

## Section 1: Overview KPI Cards (Top Row)

### Card 1 — Overall Risk Score: `48.7%`
```sql
-- Source table: churn_predictions
SELECT COALESCE(AVG(churn_probability), 0) * 100 AS avg_score
FROM churn_predictions;
```
**Logic**: Average of all LightGBM-generated `churn_probability` values × 100. Higher = worse overall health.

---

### Card 2 — High Risk Predictions: `350`
```sql
-- Source table: churn_predictions
SELECT SUM(CASE WHEN risk_level IN ('high','critical') THEN 1 ELSE 0 END) AS high_risk
FROM churn_predictions;
```
**Logic**: Count of customers where `risk_level` is either `high` (probability 0.50–0.80) or `critical` (>0.80).

---

### Card 3 — Primary Risk Factor: `Approaching Expiration`
```sql
-- Source table: churn_predictions
SELECT top_factors FROM churn_predictions
WHERE top_factors IS NOT NULL
ORDER BY RANDOM() LIMIT 1;
```
**Logic**: Randomly samples one prediction row, decodes the `top_factors` JSON, and displays the first key.  
The JSON is generated by `predict_churn.py`:
- If `days_until_expiry < 7` → `"Approaching Expiration": weight`
- If `total_revenue < 20` → `"Low Revenue Engagement": weight`
- Otherwise → `"Baseline Behavior"`

---

### Card 4 — Model Accuracy: `94.0%`
```sql
-- Source table: churn_model_registry
SELECT accuracy FROM churn_model_registry WHERE is_active = true LIMIT 1;
```
**Logic**: The `accuracy` field (0.0–1.0) × 100. Written by `train_model.py` when the model is trained.

---

## Section 2: Probability Distribution (Bar Chart)

```sql
-- Source table: churn_predictions (single query with Section 1)
SELECT
    SUM(CASE WHEN churn_probability > 0.8 THEN 1 ELSE 0 END)               AS prob_high,  -- 292
    SUM(CASE WHEN churn_probability BETWEEN 0.5 AND 0.8 THEN 1 ELSE 0 END) AS prob_med,   -- 58
    SUM(CASE WHEN churn_probability < 0.5 THEN 1 ELSE 0 END)               AS prob_low    -- 365
FROM churn_predictions;
```

| Bucket | Threshold | Count | Bar Color |
|--------|-----------|-------|-----------|
| High (>60%) | `churn_probability > 0.8` | 292 | Red |
| Medium (50-60%) | `0.5 ≤ churn_probability ≤ 0.8` | 58 | Yellow |
| Low (<50%) | `churn_probability < 0.5` | 365 | Green |

**Percentage** = `(bucket_count / total_count) × 100`

---

## Section 3: Subscription Expiry vs. AI Risk

```sql
-- Source tables: churn_predictions JOIN churn_feature_store (on sms_cust_id)
SELECT
    SUM(CASE WHEN fs.days_until_expiry < 7 THEN 1 ELSE 0 END)                                                      AS exp_7,
    SUM(CASE WHEN fs.days_until_expiry < 7 AND cp.risk_level IN ('high','critical') THEN 1 ELSE 0 END)             AS exp_7_high,
    SUM(CASE WHEN fs.days_until_expiry BETWEEN 7 AND 30 THEN 1 ELSE 0 END)                                         AS exp_30,
    SUM(CASE WHEN fs.days_until_expiry BETWEEN 7 AND 30 AND cp.risk_level IN ('high','critical') THEN 1 ELSE 0 END) AS exp_30_high,
    SUM(CASE WHEN fs.days_until_expiry BETWEEN 31 AND 45 THEN 1 ELSE 0 END)                                         AS exp_45,
    SUM(CASE WHEN fs.days_until_expiry BETWEEN 31 AND 45 AND cp.risk_level IN ('high','critical') THEN 1 ELSE 0 END) AS exp_45_high,
    SUM(CASE WHEN fs.days_until_expiry > 45 THEN 1 ELSE 0 END)                                                      AS exp_90,
    SUM(CASE WHEN fs.days_until_expiry > 45 AND cp.risk_level IN ('high','critical') THEN 1 ELSE 0 END)             AS exp_90_high
FROM churn_predictions cp
JOIN churn_feature_store fs ON cp.sms_cust_id = fs.sms_cust_id;
```

| Card | Filter | Total | High Risk | % |
|------|--------|-------|-----------|---|
| Expires < 7 Days | `days_until_expiry < 7` | 327 | 229 | 70% |
| Expires 7–30 Days | `BETWEEN 7 AND 30` | 253 | 109 | 43% |
| Expires 30–45 Days | `BETWEEN 31 AND 45` | 0 | 0 | 0% |
| Expires > 45 Days | `> 45` | 134 | 11 | 8% |

**Where does `days_until_expiry` come from?**  
Computed in `SyncChurnFeatureStore.php` / `SyncSampleChurnData.php`:
```php
$daysUntilExpiry = (int) $today->diffInDays(Carbon::parse($latest->end_date), false);
// Reads end_date from tbl_customer_subscriptions (latest subscription per customer)
```

---

## Section 4: Win-Back Intelligence — KPI Cards

### How Churn vs Win-Back Are Handled

| Concept | Definition | Source |
|---------|------------|--------|
| **Churned** | Customer has `status IN (3, 7)` in `tbl_customer_subscriptions` (Cancelled or Expired) | `churn_feature_store.has_ever_churned = 1` |
| **Win-Back Eligible** | Churned customer who had >1 subscription OR any renewal history | `has_ever_churned = 1 AND (total_subscriptions > 1 OR renewal_rate > 0)` |
| **Already Won Back** | Previously churned but currently has an active subscription | `churns_count > 0 AND active_subscription_count > 0` |

### Card — Total Expired: `423`
```sql
-- Source table: churn_feature_store
SELECT SUM(CASE WHEN has_ever_churned = 1 THEN 1 ELSE 0 END) AS total_expired
FROM churn_feature_store;
```

### Card — Win Back Eligible: `279`
```sql
SELECT SUM(CASE WHEN has_ever_churned = 1
    AND (total_subscriptions > 1 OR renewal_rate > 0)
    THEN 1 ELSE 0 END) AS wb_eligible
FROM churn_feature_store;
```

### Card — Already Won Back: `217`
```sql
SELECT SUM(CASE WHEN churns_count > 0 AND active_subscription_count > 0
    THEN 1 ELSE 0 END) AS won_back
FROM churn_feature_store;
```

---

## Section 5: AI Win-Back Score

> **Key Insight**: A subscriber LightGBM rated LOW churn risk but who still left → situational churn (price rise, life event) → HIGH win-back potential.  
> A subscriber rated HIGH churn risk → deeply disengaged → HARD to re-engage.  
> **Win-Back Score = 1 − churn_probability**

```sql
-- Source tables: churn_predictions JOIN churn_feature_store
SELECT
    SUM(CASE WHEN cp.churn_probability <= 0.30 THEN 1 ELSE 0 END) AS wb_high,  -- 77
    SUM(CASE WHEN cp.churn_probability > 0.30 AND cp.churn_probability <= 0.60 THEN 1 ELSE 0 END) AS wb_med, -- 27
    SUM(CASE WHEN cp.churn_probability > 0.60 THEN 1 ELSE 0 END) AS wb_low     -- 319
FROM churn_predictions cp
JOIN churn_feature_store fs ON cp.sms_cust_id = fs.sms_cust_id
WHERE fs.has_ever_churned = 1;
```

| Bucket | Threshold | Meaning | Count |
|--------|-----------|---------|-------|
| High (≤ 30% churn) | `churn_probability ≤ 0.30` | Easiest to re-engage | 77 |
| Med (31–60% churn) | `0.30 < churn_probability ≤ 0.60` | Moderate effort | 27 |
| Low (> 60% churn) | `churn_probability > 0.60` | Deeply disengaged | 319 |

---

## Section 6: Reactivation Window

```sql
-- Source table: churn_feature_store ONLY
SELECT
    SUM(CASE WHEN subscription_gap_days IS NOT NULL AND subscription_gap_days <= 30 THEN 1 ELSE 0 END)  AS r30,  -- 334
    SUM(CASE WHEN subscription_gap_days > 30 AND subscription_gap_days <= 60 THEN 1 ELSE 0 END)         AS r60,  -- 42
    SUM(CASE WHEN subscription_gap_days > 60 AND subscription_gap_days <= 90 THEN 1 ELSE 0 END)         AS r90,  -- 8
    SUM(CASE WHEN subscription_gap_days > 90 OR subscription_gap_days IS NULL THEN 1 ELSE 0 END)        AS r_old -- 39
FROM churn_feature_store
WHERE has_ever_churned = 1;
```

**Where does `subscription_gap_days` come from?**  
Computed in `SyncChurnFeatureStore.php`:
```php
// Gap = days between the 2nd-most-recent subscription's start_date and the latest subscription's start_date
$gapDays = max(0, Carbon::parse($second->start_date)->diffInDays(Carbon::parse($latest->start_date)));
```

| Card | Filter | Count | Label |
|------|--------|-------|-------|
| 🔴 Urgent | `gap_days ≤ 30` | 334 | Highest win-back rate |
| 🟠 Warm | `30 < gap_days ≤ 60` | 42 | Still receptive |
| 🟣 Cooling | `60 < gap_days ≤ 90` | 8 | Needs strong offer |
| 🔵 Cold | `gap_days > 90 or NULL` | 39 | Lower priority |

---

## Complete Data Pipeline Summary

```
┌─────────────────────────────────────────────────────┐
│           ORIGINAL TABLES (Read-Only)               │
│  tbl_customer_subscriptions  →  status, dates       │
│  tbl_order                   →  revenue, payments   │
│  customer_devices            →  login, platform     │
└────────────────────┬────────────────────────────────┘
                     │  php artisan churn:sync-sample
                     ▼
┌─────────────────────────────────────────────────────┐
│           churn_feature_store (computed)             │
│  sms_cust_id, app_id, tenure, revenue, expiry,      │
│  payment failures, has_ever_churned, gap_days...     │
└────────────────────┬────────────────────────────────┘
                     │  python train_model.py
                     │  python predict_churn.py
                     ▼
┌─────────────────────────────────────────────────────┐
│           churn_predictions (ML output)              │
│  sms_cust_id, app_id, churn_probability,             │
│  risk_level, top_factors                             │
└────────────────────┬────────────────────────────────┘
                     │  Filament reads directly
                     ▼
┌─────────────────────────────────────────────────────┐
│           AI Churn Management Dashboard              │
│  KPIs, Charts, Win-Back all from JOINs above        │
└─────────────────────────────────────────────────────┘
```