# Business Analytics Page - All SQL Queries

This document contains all SQL queries used in the Business Analytics page for:
- **6 KPI Cards**: MRR, ARR, Churn Rate, LTV, Product Activations, Time to Recovery
- **3 Graphs**: MRR & ARR Trends, Churn Recovery, Customer Segments

---

## 6 KPI CARDS QUERIES

### 1. MRR (Monthly Recurring Revenue) Card

**Purpose**: Calculate Monthly Recurring Revenue by converting all subscription prices to monthly equivalents.

**Query**:
```sql
SELECT
    SUM(
        CASE LOWER(COALESCE(tp.period, ''))
            WHEN 'day' THEN 
                tp.price / (tp.period_interval / 30.44)
            WHEN 'week' THEN 
                tp.price / ((tp.period_interval * 7) / 30.44)
            WHEN 'month' THEN 
                tp.price / tp.period_interval
            WHEN 'year' THEN 
                tp.price / (tp.period_interval * 12)
            ELSE 0
        END
    ) AS total_mrr
FROM tbl_customer_subscriptions as t
LEFT JOIN tbl_packages as tp ON tp.id = t.package_id
WHERE t.status IN ('2', '7')  -- Active subscriptions
    AND t.start_date <= :endDate
    AND t.autorenew = '1'
    AND (t.end_date IS NULL OR t.end_date >= :startDate)
    AND t.app_id = :appId
    AND tp.app_id = :appId
```

**Key Points**:
- Converts prices to monthly equivalents based on period type (day/week/month/year)
- Only includes active subscriptions (status = 2 or 7)
- Only includes subscriptions with autorenew = 1
- Filters by app_id for multi-tenant isolation

---

### 2. ARR (Annual Recurring Revenue) Card

**Purpose**: Calculate Annual Recurring Revenue by converting all subscription prices to annual equivalents.

**Query**:
```sql
SELECT
    SUM(
        CASE LOWER(COALESCE(tp.period, ''))
            WHEN 'day' THEN 
                tp.price / (tp.period_interval / 365.25)
            WHEN 'week' THEN 
                tp.price / ((tp.period_interval * 7) / 365.25)
            WHEN 'month' THEN 
                tp.price / ((tp.period_interval * 30.44) / 365.25)
            WHEN 'year' THEN 
                tp.price / tp.period_interval
            ELSE 0
        END
    ) AS total_arr
FROM tbl_customer_subscriptions as t
LEFT JOIN tbl_packages as tp ON tp.id = t.package_id
WHERE t.status IN ('2', '7')  -- Active subscriptions
    AND t.start_date <= :endDate
    AND t.autorenew = '1'
    AND (t.end_date IS NULL OR t.end_date >= :startDate)
    AND t.app_id = :appId
    AND tp.app_id = :appId
```

**Key Points**:
- Converts prices to annual equivalents based on period type
- Uses same filters as MRR query
- Uses 365.25 days for accurate year calculation

---

### 3. Churn Rate Card

**Purpose**: Calculate churn rate as (Churned users / Active users anytime in period) × 100

**Query 1 - Active Anytime in Period**:
```sql
SELECT COUNT(DISTINCT subscriber_id) AS active_cnt
FROM tbl_customer_subscriptions t
WHERE t.app_id = :appId
    AND t.status = 2
    AND (t.start_date IS NULL OR t.start_date <= :endDate)
    AND (t.end_date IS NULL OR t.end_date >= :startDate)
```

**Query 2 - Churned Users in Period**:
```sql
SELECT COUNT(DISTINCT c.subscriber_id) AS churn_cnt
FROM tbl_customer_subscriptions c
WHERE c.app_id = :appId
    AND c.status IN (3, 7)  -- Churned/Expired
    AND c.end_date BETWEEN :startDate AND :endDate
    AND EXISTS (
        SELECT 1
        FROM tbl_customer_subscriptions s
        WHERE s.app_id = :appId
            AND s.subscriber_id = c.subscriber_id
            AND s.status = 2
            AND (s.start_date IS NULL OR s.start_date <= :endDate)
            AND (s.end_date IS NULL OR s.end_date >= :startDate)
    )
```

**Calculation**:
```
Churn Rate = (churn_cnt / active_cnt) × 100
```

**Key Points**:
- Uses "Active Anytime in Period" methodology (not "Active at Start")
- Only counts users who were active AND then churned
- Status 3 = Churned, Status 7 = Expired

---

### 4. LTV (Lifetime Value) Card

**Purpose**: Calculate average lifetime value for churned subscribers.

**Query**:
```sql
SELECT
    COALESCE(
        ROUND(
            SUM(ltv_per_subscriber.ltv_till_today) / NULLIF(COUNT(*), 0),
            2
        ),
        0
    ) AS avg_ltv_per_churned_subscriber
FROM (
    SELECT
        currently_churned.subscriber_id,
        COALESCE(SUM(t.price), 0) AS ltv_till_today
    FROM (
        SELECT subscriber_id
        FROM (
            SELECT
                s.subscriber_id,
                s.status,
                ROW_NUMBER() OVER (
                    PARTITION BY s.subscriber_id
                    ORDER BY
                        COALESCE(s.end_date, s.start_date, s.updated_at, s.created_at) DESC,
                        s.id DESC
                ) AS rn
            FROM tbl_customer_subscriptions s
            WHERE s.app_id = :appId
                AND COALESCE(s.created_at::date, s.start_date::date) <= :endDate
        ) latest_subscription
        WHERE rn = 1
            AND status IN (3, 7)  -- Churned/Expired
    ) currently_churned
    JOIN tbl_customer_subscriptions t
        ON t.app_id = :appId
        AND t.subscriber_id = currently_churned.subscriber_id
    WHERE t.price IS NOT NULL
        AND COALESCE(t.created_at::date, t.start_date::date) <= :endDate
    GROUP BY currently_churned.subscriber_id
) ltv_per_subscriber
```

**Key Points**:
- Only calculates LTV for subscribers whose latest subscription status is churned/expired
- Sums all historical subscription prices for each churned subscriber
- Uses window functions (ROW_NUMBER) to find latest subscription per subscriber
- PostgreSQL syntax (uses ::date casting)

---

### 5. Product Activations Card

**Purpose**: Count unique subscribers who had subscriptions created in the date range.

**Query**:
```sql
SELECT COUNT(*) as count
FROM (
    SELECT DISTINCT subscriber_id
    FROM tbl_customer_subscriptions
    WHERE app_id = :appId 
        AND status IN (2, 7)  -- Active subscriptions
        AND created_at::date BETWEEN :startDate AND :endDate
) t
```

**Key Points**:
- Counts distinct subscribers (not subscriptions)
- Only includes active subscriptions (status = 2 or 7)
- Filters by created_at date within range
- PostgreSQL syntax (uses ::date casting)

---

### 6. Time to Recovery Card

**Purpose**: Calculate average time (in days) between failed payment and successful recovery.

**PostgreSQL Query**:
```sql
SELECT AVG(EXTRACT(EPOCH FROM (recovered.created_at - failed.created_at)) / 3600) as avg_hours
FROM tbl_customer_subscriptions as failed
JOIN tbl_customer_subscriptions as recovered 
    ON failed.order_id = recovered.order_id
    AND recovered.created_at > failed.created_at
    AND recovered.status = 2
WHERE failed.status IN (3, 7)  -- Failed/Churned
    AND failed.created_at BETWEEN :startDate AND :endDate
    AND failed.app_id = :appId
    AND recovered.app_id = :appId
```

**MySQL Query**:
```sql
SELECT AVG(TIMESTAMPDIFF(HOUR, failed.created_at, recovered.created_at)) as avg_hours
FROM tbl_customer_subscriptions as failed
JOIN tbl_customer_subscriptions as recovered 
    ON failed.order_id = recovered.order_id
    AND recovered.created_at > failed.created_at
    AND recovered.status = 2
WHERE failed.status IN (3, 7)  -- Failed/Churned
    AND failed.created_at BETWEEN :startDate AND :endDate
    AND failed.app_id = :appId
    AND recovered.app_id = :appId
```

**Calculation**:
```
Time to Recovery (days) = avg_hours / 24
```

**Key Points**:
- Joins failed subscriptions with recovered subscriptions by order_id
- Only includes payments that were actually recovered (recovered.status = 2)
- Converts hours to days for display

---

## 3 GRAPHS QUERIES

### 1. MRR & ARR Trends Graph

**Purpose**: Show MRR and ARR trends over time (week-wise or month-wise based on date range).

**Logic**:
- If date range is within same month OR spans ≤35 days: Show week-wise data
- Otherwise: Show month-wise data

**Week-wise Calculation** (repeats MRR/ARR queries for each week):
```sql
-- For each week in range, run:
SELECT
    SUM(
        CASE LOWER(COALESCE(tp.period, ''))
            WHEN 'day' THEN tp.price / (tp.period_interval / 30.44)
            WHEN 'week' THEN tp.price / ((tp.period_interval * 7) / 30.44)
            WHEN 'month' THEN tp.price / tp.period_interval
            WHEN 'year' THEN tp.price / (tp.period_interval * 12)
            ELSE 0
        END
    ) AS total_mrr
FROM tbl_customer_subscriptions as t
LEFT JOIN tbl_packages as tp ON tp.id = t.package_id
WHERE t.status IN ('2', '7')
    AND t.start_date <= :weekEndDate
    AND t.autorenew = '1'
    AND (t.end_date IS NULL OR t.end_date >= :weekStartDate)
    AND t.app_id = :appId
    AND tp.app_id = :appId
```

**Month-wise Calculation** (repeats MRR/ARR queries for each month):
```sql
-- For each month in range, run same query as above with month start/end dates
```

**Key Points**:
- Uses same calculation methods as MRR/ARR cards
- Iterates over time periods (weeks or months) and calculates MRR/ARR for each
- Returns arrays of MRR and ARR values for Chart.js

---

### 2. Churn Recovery Graph

**Purpose**: Show churn rate trends over time (month-wise).

**Query** (repeats Churn Rate calculation for each month):
```sql
-- For each month in range, run:

-- Step 1: Active Anytime in Period
SELECT COUNT(DISTINCT subscriber_id) AS active_cnt
FROM tbl_customer_subscriptions t
WHERE t.app_id = :appId
    AND t.status = 2
    AND (t.start_date IS NULL OR t.start_date <= :monthEndDate)
    AND (t.end_date IS NULL OR t.end_date >= :monthStartDate)

-- Step 2: Churned Users in Period
SELECT COUNT(DISTINCT c.subscriber_id) AS churn_cnt
FROM tbl_customer_subscriptions c
WHERE c.app_id = :appId
    AND c.status IN (3, 7)
    AND c.end_date BETWEEN :monthStartDate AND :monthEndDate
    AND EXISTS (
        SELECT 1
        FROM tbl_customer_subscriptions s
        WHERE s.app_id = :appId
            AND s.subscriber_id = c.subscriber_id
            AND s.status = 2
            AND (s.start_date IS NULL OR s.start_date <= :monthEndDate)
            AND (s.end_date IS NULL OR s.end_date >= :monthStartDate)
    )

-- Step 3: Calculate Churn Rate = (churn_cnt / active_cnt) × 100
```

**Key Points**:
- Uses same calculation as Churn Rate card
- Calculates churn rate for each month in the date range
- Returns array of churn rate values for Chart.js line chart

---

### 3. Customer Segments Graph

**Purpose**: Show distribution of customers by package category (Basic, Standard, Premium).

**Query**:
```sql
SELECT 
    tp.package_category_type,
    COUNT(DISTINCT t.subscriber_id) as subscriber_count
FROM tbl_customer_subscriptions t
LEFT JOIN tbl_packages tp ON tp.id = t.package_id
WHERE t.status = 2  -- Active subscriptions
    AND t.subscriber_id IS NOT NULL
    AND tp.package_category_type IN (1, 2, 3)  -- 1=Basic, 2=Standard, 3=Premium
    AND t.start_date <= :endDate
    AND (t.end_date >= :startDate OR t.end_date IS NULL)
    AND tp.app_id = :appId
GROUP BY tp.package_category_type
```

**Mapping**:
- `package_category_type = 1` → Basic
- `package_category_type = 2` → Standard
- `package_category_type = 3` → Premium

**Key Points**:
- Counts distinct active subscribers by package category
- Only includes active subscriptions (status = 2)
- Returns counts for each segment for Chart.js pie chart

---

## COMMON PARAMETERS

All queries use these common parameters:
- `:appId` - Application ID for multi-tenant isolation (from `Auth::user()->app_id`)
- `:startDate` - Start date of the date range filter
- `:endDate` - End date of the date range filter

## STATUS CODES

- `status = 2` - Active/Successful subscription
- `status = 3` - Churned subscription
- `status = 7` - Expired/Failed subscription

## NOTESeries filter by `app_id` for multi-tenant data isolation
2. Date ranges are applied consistently across all queries
3. PostgreSQL-specific syntax is used for date casting (`::date`) and window functions
4. Queries are optimized for memory efficiency using raw SQL instead of Eloquent
5. Results are cached for 5 minutes (300 seconds) to improve performance
6. All calculations match the Dashboard page calculations for consistency

---

**File Location**: `app/Filament/Pages/AnalyticsAndAdmin/BusinessAnalytics.php`
**API Controller**: `app/Http/Controllers/Analytics/BusinessAnalyticsApiController.php`