# Business Analytics Cards and Graphs Logic (Current Runtime) This document explains, one by one: - which card/chart uses which backend method - data source table/index - applied conditions (filters/status/date/app scope) - formula used to calculate values Source files used for this mapping: - `app/Http/Controllers/Analytics/BusinessAnalyticsApiController.php` - `app/Services/BusinessAnalyticsService.php` - `app/Services/BusinessAnalyticsSupportService.php` - `app/Services/Elasticsearch/BusinessAnalyticsElasticsearchService.php` - `public/js/business-analytics.js` - `resources/views/filament/pages/business-analytics/partials/dashboards.blade.php` --- ## 1. Runtime Data Flow 1. UI filter values are stored in hidden config element `#business-analytics-data-config`. 2. Frontend builds widget URLs from this config and calls: - `/admin/business-analytics/widgets/kpi` - `/admin/business-analytics/widgets/charts/mrr-arr-trends` - `/admin/business-analytics/widgets/charts/churn-recovery` - `/admin/business-analytics/widgets/charts/customer-segments` 3. Controller methods call `BusinessAnalyticsService`. 4. Service decides data source: - ES path if enabled+configured (`analyticsEs() != null`) - otherwise SQL path (DB queries on subscription/package tables) --- ## 2. Global Filters and Scoping ### Filters sent from UI - `date_range_start`, `date_range_end` - `package_mode` (`all|prime|ott|promotional`) - `currency` (`INR|USD`) - `segment_filter` (mainly for customer segments chart) - segment package params (`segment_packages`, `top_packages`) for pie chart modal ### Date range default - API default is last 6 months if date params are missing. ### App scope (important) - For KPI/charts widgets, `app_id` is forced from logged-in user (`Auth::user()->app_id`) by `BusinessAnalyticsSupportService::extractFilters`. - So URL `app_id` does not control KPI/chart data in these APIs. --- ## 3. KPI Cards Logic (One by One) All KPI values come from: - `BusinessAnalyticsApiController::getKpiData` - `BusinessAnalyticsService::calculateKpisForPeriod` The API also computes `% change` by comparing current period with previous same-length period. ### 3.1 Total Revenue Field in response: `total_revenue.value` SQL source table: - `tbl_customer_subscriptions` Conditions (SQL path): - `status IN (2, 7)` - `created_at >= startOfDay(startDate)` - `created_at < endOfDay(endDate)` - optional `app_id` - optional package mode filter (EXISTS on `tbl_packages`) - optional currency filter Formula: - `SUM(CASE WHEN price > 0 THEN price ELSE 0 END)` ES path equivalent: - sum of `orders.price` with status/date/package/currency filters. --- ### 3.2 Product Activations Field in response: `product_activations.value` SQL source table: - `tbl_customer_subscriptions` Conditions: - same as Total Revenue query Formula: - `COUNT(DISTINCT sms_cust_id)` ES path equivalent: - reverse_nested cardinality on root `sms_cust_id`. --- ### 3.3 MRR (Monthly Recurring Revenue) Field in response: `mrr.value` SQL source tables: - `tbl_customer_subscriptions t` - `tbl_packages tp` (join by `sms_package_id`) Conditions (SQL path): - `t.status IN ('2','7')` - `t.auto_renew = '1'` - subscription overlap with selected window: - `t.start_date <= endDate` - and (`t.end_date IS NULL OR t.end_date >= startDate`) - optional `app_id` on both subscription and package - optional package mode filter - optional currency filter Formula (SQL path): - Convert package price to monthly normalized value by period: - day/week/month/year using `period_interval` - Sum normalized monthly values. ES path behavior: - Uses `orders.price` with monthly baseline script (index mapping does not expose full period metadata in nested orders). - Also enforces `orders.subscription.auto_renew = 1` for recurring-only scope. --- ### 3.4 ARR (Annual Recurring Revenue) Field in response: `arr.value` SQL source and conditions: - same base query as MRR. Formula (SQL path): - Convert package price to annualized value using period + interval normalization. ES path behavior: - Uses script on `orders.price` with yearly multiplier from monthly baseline. - Also enforces `orders.subscription.auto_renew = 1` for recurring-only scope. --- ### 3.5 Churn Rate Field in response: `churn.value` SQL source table: - `tbl_customer_subscriptions` Numerator (churned in selected range): - distinct customers where: - `status IN (3,7)` - `end_date BETWEEN startDate AND endDate` - and EXISTS an overlapping active subscription record (`status=2`) for same customer in period - package mode filters applied to both churn and active-exists checks Denominator (active anytime in selected range): - distinct customers where: - `status = 2` - overlap with period (`start_date <= endDate`, `end_date >= startDate` with null-safe checks) - package mode filter applied Formula: - `churn_rate = (churned / active_anytime) * 100` ES path: - Distinct-customer counts via nested subscription status/date fields, then same formula. --- ### 3.6 Average LTV Field in response: `ltv.value` SQL source table: - `tbl_customer_subscriptions` Conditions: - `price IS NOT NULL` - `price > 0` - `created_at BETWEEN startOfDay(startDate) AND endOfDay(endDate)` - optional `app_id` - optional package mode filter - optional currency filter Formula: - `SUM(price) / COUNT(DISTINCT sms_cust_id)` (rounded) ES path: - revenue sum over filtered orders / distinct root customers. --- ## 4. Graph Logic (One by One) ### 4.1 MRR and ARR Trends chart Endpoint: - `/admin/business-analytics/widgets/charts/mrr-arr-trends` Backend: - `BusinessAnalyticsService::getMrrArrChartData` Bucketing: - if same month OR ~35 days window => weekly buckets - otherwise monthly buckets Data generation: - For each bucket range, MRR/ARR is calculated using same recurring logic as KPI (with filters). SQL path: - iterates subscriptions + package period metadata to add contribution in overlapping buckets. ES path: - per-bucket calls into ES-backed MRR/ARR method. - recurring ES call includes `orders.subscription.auto_renew = 1`. --- ### 4.2 Churn Rate chart Endpoint: - `/admin/business-analytics/widgets/charts/churn-recovery` Backend: - `BusinessAnalyticsService::getChurnRecoveryChartData` Logic: - Build month labels from selected range. - For each month: - active distinct customers (status=2, overlap) - churned distinct customers (status IN 3,7 and end_date in month, with prior/overlap active exists) - `rate = churned / active * 100` Note: - response currently contains only churn dataset (recovery dataset is empty). --- ### 4.3 Customer Segments chart (pie) Endpoint: - `/admin/business-analytics/widgets/charts/customer-segments` Backend: - `BusinessAnalyticsService::getCustomerSegmentsChartData` SQL source tables: - `tbl_customer_subscriptions t` - `tbl_packages tp` Conditions (SQL path): - `tp.publish = 1` - `tp.status = 1` - `t.status IN (2,7)` - `t.created_at BETWEEN startDate AND endDate` - optional `t.app_id` - optional `package_mode` - optional `currency` - optional selected package IDs (`segment_packages`) - optional `top_packages` limit if no explicit selection Formula: - `SUM(t.price) GROUP BY sms_package_id` - sorted by revenue descending - chart renders top packages (and "Other" group in default mode) Extra: - `segment_filter` can narrow to exact package id/name match. --- ## 5. Shared Helper Conditions ### Package Mode filter - Valid modes: `prime`, `ott`, `promotional` (else treated as all/no filter). - SQL path: - EXISTS subquery on `tbl_packages` with: - `pm.status = 1` - package id match - `LOWER(TRIM(pm.package_mode)) = mode` - `pm.app_id = current app` (if app present) - ES path: - resolves package ids from `tbl_packages` for mode and applies `terms` on `orders.sms_package_id`. ### Currency filter - Valid values: `INR`, `USD` (else no filter). - SQL: `UPPER(COALESCE(currency,'INR')) = ?` - ES: `term orders.currency = ?` --- ## 6. Why Mismatch Can Happen (Important) 1. **ES vs SQL path differences** - Runtime may be ES-first (`DASHBOARD_DATA_SOURCE=es`). - ES MRR/ARR uses baseline script due to mapping limitations, SQL uses exact package period metadata. - ES MRR/ARR now also filters `orders.subscription.auto_renew = 1`; older ES docs missing this field will be excluded until backfilled/defaulted. 2. **App scope confusion** - KPI/chart APIs force logged-in app_id, URL app_id does not override. 3. **Date basis differences by metric** - Revenue/activations/LTV/segments use `created_at` window. - Recurring and churn rely on subscription overlap and end_date logic. 4. **Status semantics differ per metric** - recurring/revenue often use `2,7` - churn uses `3,7` numerator and `2` denominator. --- ## 7. Quick Debug Checklist 1. Confirm which source path is active: - `DASHBOARD_DATA_SOURCE` in `.env` 2. Confirm UI filter values in config element: - `#business-analytics-data-config` 3. Inspect request URLs generated in frontend: - `buildAnalyticsParams()` / `buildSegmentsChartUrl()` 4. Check API response payload keys: - `data.mrr`, `data.arr`, `data.total_revenue`, `data.datasets`, etc. 5. If SQL path is used and app is debug mode, check query logs from: - `BusinessAnalyticsSupportService::withQueryLog(...)` --- ## 8. Exact Queries (app_id=1004, date_range=today, package_mode=all, ES path) ```http GET /admin/business-analytics/widgets/kpi?date_range_start=2026-06-08&date_range_end=2026-06-08&package_mode=all¤cy=INR&segment_filter=all&app_id= GET /admin/business-analytics/widgets/charts/mrr-arr-trends?date_range_start=2026-06-08&date_range_end=2026-06-08&package_mode=all¤cy=INR&segment_filter=all&app_id= GET /admin/business-analytics/widgets/charts/churn-recovery?date_range_start=2026-06-08&date_range_end=2026-06-08&package_mode=all¤cy=INR&segment_filter=all&app_id= GET /admin/business-analytics/widgets/charts/customer-segments?date_range_start=2026-06-08&date_range_end=2026-06-08&package_mode=all¤cy=INR&segment_filter=all&app_id= ``` ```json // KPI -> Revenue + Product Activations (current day) { "size": 0, "query": { "bool": { "must": [ { "term": { "app_id": 1004 } }, { "nested": { "path": "orders", "query": { "bool": { "must": [ { "bool": { "should": [ { "terms": { "orders.order_status": [2, 7] } }, { "nested": { "path": "orders.subscription", "query": { "terms": { "orders.subscription.subscription_status": [2, 7] } } } } ], "minimum_should_match": 1 } }, { "bool": { "should": [ { "range": { "orders.order_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } }, { "nested": { "path": "orders.subscription", "query": { "range": { "orders.subscription.subscription_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } } } } ], "minimum_should_match": 1 } }, { "term": { "orders.currency": "INR" } } ] } } } } ] } }, "aggs": { "orders_nested": { "nested": { "path": "orders" }, "aggs": { "filtered": { "filter": { "bool": { "must": [ { "bool": { "should": [ { "terms": { "orders.order_status": [2, 7] } }, { "nested": { "path": "orders.subscription", "query": { "terms": { "orders.subscription.subscription_status": [2, 7] } } } } ], "minimum_should_match": 1 } }, { "bool": { "should": [ { "range": { "orders.order_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } }, { "nested": { "path": "orders.subscription", "query": { "range": { "orders.subscription.subscription_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } } } } ], "minimum_should_match": 1 } }, { "term": { "orders.currency": "INR" } } ] } }, "aggs": { "total_revenue": { "sum": { "field": "orders.price" } }, "to_root": { "reverse_nested": {}, "aggs": { "product_activations": { "cardinality": { "field": "sms_cust_id" } } } } } } } } } } ``` ```json // KPI + MRR/ARR trend bucket -> MRR/ARR (current day; same must filters, different aggs) { "size": 0, "query": { "bool": { "must": [ { "term": { "app_id": 1004 } }, { "nested": { "path": "orders", "query": { "bool": { "must": [ { "bool": { "should": [ { "terms": { "orders.order_status": [2, 7] } }, { "nested": { "path": "orders.subscription", "query": { "terms": { "orders.subscription.subscription_status": [2, 7] } } } } ], "minimum_should_match": 1 } }, { "term": { "orders.subscription.auto_renew": 1 } }, { "bool": { "should": [ { "range": { "orders.order_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } }, { "nested": { "path": "orders.subscription", "query": { "range": { "orders.subscription.subscription_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } } } } ], "minimum_should_match": 1 } }, { "term": { "orders.currency": "INR" } } ] } } } } ] } }, "aggs": { "orders_nested": { "nested": { "path": "orders" }, "aggs": { "filtered": { "filter": { "bool": { "must": [ { "bool": { "should": [ { "terms": { "orders.order_status": [2, 7] } }, { "nested": { "path": "orders.subscription", "query": { "terms": { "orders.subscription.subscription_status": [2, 7] } } } } ], "minimum_should_match": 1 } }, { "term": { "orders.subscription.auto_renew": 1 } }, { "bool": { "should": [ { "range": { "orders.order_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } }, { "nested": { "path": "orders.subscription", "query": { "range": { "orders.subscription.subscription_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } } } } ], "minimum_should_match": 1 } }, { "term": { "orders.currency": "INR" } } ] } }, "aggs": { "mrr": { "sum": { "script": { "source": "double price = 0.0; if (doc.containsKey('orders.price') && !doc['orders.price'].empty) { price = doc['orders.price'].value; } double interval = 30.44; if (params.mode == 'mrr') { return price * (30.44 / interval); } if (params.mode == 'arr') { return price * (365.25 / interval); } return price;", "params": { "mode": "mrr" } } } }, "arr": { "sum": { "script": { "source": "double price = 0.0; if (doc.containsKey('orders.price') && !doc['orders.price'].empty) { price = doc['orders.price'].value; } double interval = 30.44; if (params.mode == 'mrr') { return price * (30.44 / interval); } if (params.mode == 'arr') { return price * (365.25 / interval); } return price;", "params": { "mode": "arr" } } } } } } } } } } ``` ```json // KPI churn denominator (active customers in day overlap) { "size": 0, "query": { "bool": { "must": [ { "term": { "app_id": 1004 } }, { "nested": { "path": "orders", "query": { "bool": { "must": [ { "nested": { "path": "orders.subscription", "query": { "term": { "orders.subscription.subscription_status": 2 } } } }, { "bool": { "should": [ { "bool": { "must_not": [ { "exists": { "field": "orders.subscription.start_date" } } ] } }, { "range": { "orders.subscription.start_date": { "lte": "2026-06-08" } } } ], "minimum_should_match": 1 } }, { "bool": { "should": [ { "bool": { "must_not": [ { "exists": { "field": "orders.subscription.end_date" } } ] } }, { "range": { "orders.subscription.end_date": { "gte": "2026-06-08" } } } ], "minimum_should_match": 1 } } ] } } } } ] } }, "aggs": { "customers": { "cardinality": { "field": "sms_cust_id" } } } } ``` ```json // KPI churn numerator (churned customers in day) { "size": 0, "query": { "bool": { "must": [ { "term": { "app_id": 1004 } }, { "nested": { "path": "orders", "query": { "bool": { "must": [ { "nested": { "path": "orders.subscription", "query": { "terms": { "orders.subscription.subscription_status": [3, 7] } } } }, { "nested": { "path": "orders.subscription", "query": { "range": { "orders.subscription.end_date": { "gte": "2026-06-08", "lte": "2026-06-08" } } } } } ] } } } } ] } }, "aggs": { "customers": { "cardinality": { "field": "sms_cust_id" } } } } ``` ```json // KPI LTV (current day) { "size": 0, "query": { "bool": { "must": [ { "term": { "app_id": 1004 } }, { "nested": { "path": "orders", "query": { "bool": { "must": [ { "bool": { "should": [ { "range": { "orders.order_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } }, { "nested": { "path": "orders.subscription", "query": { "range": { "orders.subscription.subscription_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } } } } ], "minimum_should_match": 1 } }, { "range": { "orders.price": { "gt": 0 } } }, { "term": { "orders.currency": "INR" } } ] } } } } ] } }, "aggs": { "orders_nested": { "nested": { "path": "orders" }, "aggs": { "filtered": { "filter": { "bool": { "must": [ { "bool": { "should": [ { "range": { "orders.order_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } }, { "nested": { "path": "orders.subscription", "query": { "range": { "orders.subscription.subscription_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } } } } ], "minimum_should_match": 1 } }, { "range": { "orders.price": { "gt": 0 } } }, { "term": { "orders.currency": "INR" } } ] } }, "aggs": { "revenue": { "sum": { "field": "orders.price" } }, "to_root": { "reverse_nested": {}, "aggs": { "customers": { "cardinality": { "field": "sms_cust_id" } } } } } } } } } } ``` ```json // Customer Segments chart (today) { "size": 0, "query": { "bool": { "must": [ { "term": { "app_id": 1004 } }, { "nested": { "path": "orders", "query": { "bool": { "must": [ { "bool": { "should": [ { "range": { "orders.order_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } }, { "nested": { "path": "orders.subscription", "query": { "range": { "orders.subscription.subscription_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } } } } ], "minimum_should_match": 1 } }, { "exists": { "field": "orders.sms_package_id" } }, { "term": { "orders.currency": "INR" } } ] } } } } ] } }, "aggs": { "orders_nested": { "nested": { "path": "orders" }, "aggs": { "filtered": { "filter": { "bool": { "must": [ { "bool": { "should": [ { "range": { "orders.order_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } }, { "nested": { "path": "orders.subscription", "query": { "range": { "orders.subscription.subscription_created_at": { "gte": "2026-06-08T00:00:00", "lte": "2026-06-08T23:59:59" } } } } } ], "minimum_should_match": 1 } }, { "exists": { "field": "orders.sms_package_id" } }, { "term": { "orders.currency": "INR" } } ] } }, "aggs": { "packages": { "terms": { "field": "orders.sms_package_id", "size": 100 }, "aggs": { "revenue": { "sum": { "field": "orders.price" } }, "sample": { "top_hits": { "size": 1, "_source": { "includes": ["orders.package_name"] } } } } } } } } } } } ```