# Churn Rate Calculation Documentation

## Overview
The Churn Rate on the dashboard measures the percentage of subscribers who canceled their subscriptions during a given period. It's calculated in the `KpiCardsWidget` component and displayed on the main dashboard page.

## Location
- **Widget Class**: `app/Filament/Widgets/Dashboard/KpiCardsWidget.php`
- **View**: `resources/views/filament/widgets/dashboard/kpi-cards-widget.blade.php`
- **Query Helper Method**: `app/Filament/Pages/Dashboard.php::getChurnRateQuery()`

## Calculation Formula

```
Churn Rate = (Churned Users / Subscribed Users at Start) × 100
```

Where:
- **Churned Users**: Users who had active subscriptions at the start of the period but have NO active subscriptions at the end of the period
- **Subscribed Users at Start**: Distinct count of users with active subscriptions at the start of the period

## Step-by-Step Calculation Process

### Step 1: Determine Date Range
The date range is determined from dashboard filters:
- Default: Last 30 days
- Options: Today, 7 days, 30 days, MTD (Month to Date), YTD (Year to Date), or Custom range

### Step 2: Count Subscribed Users at Start of Period

**Query Location**: Lines 301-328 in `KpiCardsWidget.php`

**SQL Logic**:
```sql
SELECT COUNT(DISTINCT subscriber_id) 
FROM tbl_customer_subscriptions
WHERE status = 2  -- Active/Success status
  AND subscriber_id IS NOT NULL
  AND (
    (start_date IS NULL OR start_date <= :churnStartDate)
    AND (end_date IS NULL OR end_date >= :churnStartDate)
  )
  -- Additional filters applied:
  -- - Currency filter (if region selected)
  -- - Package/Plan filter (if product_plan selected)
  -- - Payment Gateway filter (if gateway selected)
```

**Key Conditions**:
- `status = 2` (Success/Active subscription)
- Subscription was active at the start date:
  - `start_date` is NULL or <= start date
  - `end_date` is NULL or >= start date

### Step 3: Identify Users with Ended Subscriptions

**Query Location**: Lines 360-381 in `KpiCardsWidget.php`

**SQL Logic**:
```sql
-- First, get users who had active subscriptions at start
SELECT DISTINCT subscriber_id
FROM tbl_customer_subscriptions
WHERE status = 2
  AND subscriber_id IS NOT NULL
  AND (
    (start_date IS NULL OR start_date <= :churnStartDate)
    AND (end_date IS NULL OR end_date >= :churnStartDate)
  )

-- Then, find subscriptions that ended during the period
SELECT DISTINCT subscriber_id
FROM tbl_customer_subscriptions
WHERE subscriber_id IN (:usersAtStart)
  AND subscriber_id IS NOT NULL
  AND end_date IS NOT NULL
  AND end_date >= :churnStartDate
  AND end_date <= :churnEndDate
  AND (
    status IN (3, 7)  -- Explicitly Cancelled or Expired
    OR (status = 2 AND end_date <= NOW())  -- Naturally expired (status not updated)
  )
```

**Key Conditions**:
- `status IN (3, 7)` (Cancelled or Expired) OR
- `status = 2` but `end_date <= NOW()` (naturally expired, status not updated)
- `end_date` falls within the period
- **Note**: Removed `is_cancelled = 1` requirement as it was too restrictive

### Step 4: Filter to True Churned Users

**Query Location**: Lines 390-418 in `KpiCardsWidget.php`

**Important**: A user is only counted as "churned" if they have **NO active subscriptions** at the end of the period. This prevents false positives where a user might have canceled one subscription but still has another active one.

**Logic**:
For each user with ended subscriptions, check if they have ANY active subscription at the end date:

```sql
SELECT COUNT(*)
FROM tbl_customer_subscriptions
WHERE subscriber_id = :userId
  AND status = 2
  AND (
    (start_date IS NULL OR start_date <= :churnEndDate)
    AND (end_date IS NULL OR end_date >= :churnEndDate)
  )
```

If count = 0, the user is considered churned.

### Step 5: Calculate Churn Rate

**Code Location**: Line 426 in `KpiCardsWidget.php`

```php
$churnRate = $subscribedUsersAtStart > 0 
    ? round(($churnedUsersCount / $subscribedUsersAtStart) * 100, 2) 
    : 0.0;
```

### Step 6: Calculate Previous Period Comparison

**Code Location**: Lines 428-554 in `KpiCardsWidget.php`

The same calculation is performed for the previous period to show trend comparison:
- Previous period churn rate
- Percentage change from previous period
- Color coding (green = improvement, red = increase)

## Example Calculation

**Scenario**:
- Period: January 1-31, 2025
- Subscribed users at start: 1,000
- Users with ended subscriptions: 150
- Users with NO active subscriptions at end: 120

**Calculation**:
```
Churn Rate = (120 / 1,000) × 100 = 12.0%
```

## Filters Applied

The churn rate calculation respects the following dashboard filters:

1. **Date Range**: Determines the period for calculation
2. **Region**: Filters by currency (if region selected)
3. **Product/Plan**: Filters by `package_id` (if plan selected)
4. **Payment Gateway**: Filters by `pg_name` (if gateway selected)
5. **App ID**: Automatically applied via `AppIdFilterApplicator` (if applicable)

## Status Codes Reference

- **Status 2**: Active/Success subscription
- **Status 3**: Cancelled subscription
- **Status 7**: Expired subscription

## Viewing the Actual SQL Queries

To see the exact SQL queries being executed, you can:

1. **Via Dashboard Method**: Call `getChurnRateQuery()` method in `Dashboard.php` (lines 1096-1215)
2. **Via Query Parameter**: Add `?query=1` to the dashboard URL to bypass cache and see queries

## Related Files

- `app/Filament/Widgets/Dashboard/KpiCardsWidget.php` - Main calculation logic
- `app/Filament/Pages/Dashboard.php` - Query helper method
- `app/Filament/Pages/AnalyticsAndAdmin/BusinessAnalytics.php` - Alternative calculation method (for Business Analytics page)
- `app/Helpers/ViewHelper.php` - Helper method `calculateChurnRate()` (different implementation)

## Notes

1. **Caching**: Results are cached for 5 minutes to improve performance
2. **Distinct Users**: Uses `DISTINCT subscriber_id` to avoid double-counting users with multiple subscriptions
3. **True Churn**: Only counts users with NO active subscriptions at period end (prevents false positives)
4. **Null Handling**: Properly handles NULL `start_date` and `end_date` values
5. **Rounding**: Final churn rate is rounded to 2 decimal places
6. **Status Handling**: Includes both explicit cancellations (status 3/7) and natural expirations (status 2 with past end_date)
7. **Fixed Issue**: Removed `is_cancelled = 1` requirement which was preventing valid churned subscriptions from being counted

## Troubleshooting

If Churn Rate shows 0% or no value:

1. **Check Subscription Status**: Verify you have subscriptions with:
   - Status 3 (Cancel) or 7 (Expire) with `end_date` in the period, OR
   - Status 2 (Success) with `end_date` in the past (expired but status not updated)

2. **Check Date Range**: Ensure the selected date range includes subscriptions that actually ended

3. **Check Filters**: Verify that applied filters (region, plan, gateway) aren't excluding all relevant subscriptions

4. **Check Active Subscriptions**: Users are only counted as churned if they have NO active subscriptions at the end date

5. **Database Query**: Use `getChurnRateQuery()` method in `Dashboard.php` to see the exact SQL queries being executed