# 26. Business Analytics Dashboard — KPI & Insights Hub

> لوحة تحليلات الأعمال — مركز مؤشرات الأداء والرؤى التشغيلية

**Status:** ✅ Phase 1 implemented (April 3, 2026)

---

## ✅ Implementation Log

### Phase 1: Foundation + All 5 Tabs (April 3, 2026)
- **Service**: `src/Service/AnalyticsDashboardService.php` — 6 query methods: `getOverviewStats()`, `getSignupTrend()`, `getFeatureUsageBreakdown()`, `getAiSuccessRate()`, `getUserStats()`, `getRevenueStats()`, `getEngagementStats()`, `getAiOpsStats()`
- **Controller**: `src/Controller/AnalyticsDashboardController.php` — admin-only (`ROLE_ADMIN`), 5 API endpoints under `/jim19ud83/dashboard/api/`
- **Template**: `templates/admin/analytics/dashboard.html.twig` — modern responsive template with Chart.js, lazy-loaded tabs, auto-refresh every 5 min
- **URL**: `/jim19ud83/dashboard`
- **Tabs delivered**:
  - 📊 Overview — DAU, signups, MRR, AI ops, credits, success rate, latency, total users + signup trend line chart + feature usage donut
  - 👥 Users — Total/DAU/WAU/MAU, stickiness (DAU/MAU), top countries, top returning users (30d)
  - 🔥 Engagement — search queries/unique searchers/avg results, top queries (24h), feature adoption by unique users, social posts
  - 💰 Revenue — MRR, trial count, trial→paid conversion, subscription tier breakdown donut + detail table with utilization %
  - 🤖 AI Ops — weekly/monthly budget vs limits with progress bars, operations by type with success rate + latency + cost, model usage, error trend chart, top errors

### Bug Fixes (April 3, 2026)
1. **Column `locale` not found** — `fos_user` uses `preferred_locale`, not `locale`. Fixed in user stats query.
2. **Column `model` not found** — `playground_usage_log` uses `model_used`, not `model`. Fixed in AI ops query.
3. **Cache race condition on deploy** — `rm -rf var/cache/prod/` partially failed because Apache held file handles. Manually cleared with `sudo rm -rf var/cache/prod/ && cache:warmup`.

---

## Overview

A comprehensive, modern analytics dashboard at `/jim19ud83/dashboard` that consolidates all business metrics into a single, mobile-friendly interface. Designed as the primary decision-making tool for the Shamra Academia team — covering user growth, engagement, revenue, feature adoption, and operational health.

The dashboard is built to be **expandable** — new sections/widgets can be added as the product evolves. Every metric links to actionable insights.

### Key Principles

| Principle | Description |
|-----------|-------------|
| **KPI-first** | Every section leads with the most important number, then provides drill-down |
| **Trend-aware** | Every metric shows direction (↑↓) and comparison to previous period |
| **Mobile-ready** | Responsive grid that works on phone screens |
| **Real-time** | Data fetched via AJAX, auto-refreshes every 5 minutes |
| **Expandable** | Tab-based architecture — new tabs can be added without touching existing ones |
| **Admin-only** | Restricted to users with admin role |

---

## Dashboard Sections (Tabs)

### Tab 1: Overview (نظرة عامة)
**Purpose**: Executive summary — the 5 numbers you check every morning.

**KPI Cards (top row):**
| KPI | Source | Calculation |
|-----|--------|-------------|
| **DAU** (Daily Active Users) | `fos_user.last_login` | Users who logged in today |
| **New Signups Today** | `fos_user.created_at` | Registrations today vs yesterday (↑↓%) |
| **MRR** (Monthly Recurring Revenue) | `playground_subscription` | Active paid subscriptions × tier price |
| **AI Operations Today** | `playground_usage_log` | Total AI calls today vs yesterday |
| **Credits Consumed Today** | `playground_subscription` | Total credits used today |

**Charts:**
- **User Signups Trend** — 30-day line chart (daily registrations)
- **Revenue Trend** — 30-day line chart (daily MRR or new payments)
- **Feature Usage Breakdown** — Donut chart (chat, OCR, playground, related work, AI detection)
- **System Health** — Success rate of AI calls (last 24h), avg latency

---

### Tab 2: Users (المستخدمون)
**Purpose**: Understanding who's using the platform and how.

**KPI Cards:**
| KPI | Calculation |
|-----|-------------|
| **Total Users** | COUNT(fos_user) |
| **WAU** (Weekly Active Users) | Users who logged in this week |
| **MAU** (Monthly Active Users) | Users who logged in this month |
| **DAU/MAU Ratio** | Stickiness metric — how often monthly users return daily |

**Sections:**
- **Registration Funnel** — Signups per day (7d, 30d, 90d toggle)
- **User Retention Cohort** — Users who return within 1d, 7d, 30d of registration
- **Top Returning Users** — Top 20 users by login frequency (last 30 days)
- **Geographic Distribution** — Users by country (from `fos_user.country`)
- **Language Split** — Arabic vs English interface users
- **Study Degree Distribution** — PhD, Masters, Bachelors, etc.
- **Registration Source** — Organic, social login, referral

---

### Tab 3: Engagement (التفاعل)
**Purpose**: What are users doing? Which features get used?

**KPI Cards:**
| KPI | Calculation |
|-----|-------------|
| **Avg Sessions/User/Day** | Login count / unique users (last 7d) |
| **Feature Adoption Rate** | % of users who used each product at least once |
| **CSAT Score** | Avg satisfaction rating across all contexts |

**Sections:**
- **Feature Usage Heatmap** — Which features are used most (by operation type from `playground_usage_log`)
- **Top Visited Pages (24h)** — From `search_query_log` + page view data
- **Search Activity** — Queries/day, CTR%, zero-result rate, top queries
- **Social Feed Activity** — Posts/day, shares, comments
- **Gamification** — Points distribution, streaks, badge achievements
- **Research Chat Usage** — Questions/day, avg papers cited, satisfaction

---

### Tab 4: Revenue & Subscriptions (الإيرادات والاشتراكات)
**Purpose**: Money metrics and conversion insights.

**KPI Cards:**
| KPI | Calculation |
|-----|-------------|
| **MRR** | Sum of active subscription tier prices |
| **Trial → Paid Conversion** | % of trial users who upgrade |
| **Churn Rate** | Subscriptions cancelled or expired this month / total |
| **ARPU** | Average Revenue Per User (MRR / active subscribers) |

**Sections:**
- **Subscription Tier Breakdown** — Pie chart of users per tier
- **Trial Users at Risk** — Trial users with <10% credits remaining who haven't upgraded
- **Abandoned Payments** — Stripe checkout sessions started but not completed (requires Stripe API)
- **Revenue by Feature** — Credits consumed × credit cost by feature type
- **Upgrade/Downgrade History** — Subscription tier changes over time
- **Credit Consumption Rate** — Avg credits used per day per tier (are users running out?)

---

### Tab 5: Content & Index (المحتوى والفهرسة)
**Purpose**: Health of the research database.

**KPI Cards:**
| KPI | Calculation |
|-----|-------------|
| **Total Arabic Papers** | ES index `arabic_research` doc count |
| **Total English Papers** | ES index `english_research` doc count |
| **Papers Added This Week** | New docs indexed in last 7 days |

**Sections:**
- **Index Health** — ES cluster status, disk usage, node health
- **Search Quality** — Avg results per query, zero-result rate, CTR by position
- **Top Search Queries (24h)** — Most searched terms today
- **Queries Without Clicks** — Users who searched but didn't click any result
- **Content Gaps** — Zero-result queries grouped by topic (what content are users looking for that we don't have?)

---

### Tab 6: AI Operations (عمليات الذكاء الاصطناعي)
**Purpose**: AI service health, costs, and usage patterns.

**KPI Cards:**
| KPI | Calculation |
|-----|-------------|
| **Total AI Calls Today** | Count from `playground_usage_log` |
| **Success Rate** | % of calls where `success = true` |
| **Avg Latency** | Mean `latency_ms` of successful calls |
| **Daily AI Cost ($)** | Sum of token costs from usage log |

**Sections:**
- **Operations by Type** — Bar chart of calls per operation (chat, generate, OCR, etc.)
- **Model Usage** — GPT-5.2 vs GPT-4o-mini vs Mistral breakdown
- **Error Rate Trend** — 7-day failure rate line chart
- **Budget Monitor** — Daily/weekly/monthly spend vs budget limits
- **Top Error Messages** — Most common failure reasons
- **Latency Percentiles** — P50, P90, P99 response times

---

### Tab 7: Satisfaction (رضا المستخدمين)
**Purpose**: How happy are users? Where are pain points?

**KPI Cards:**
| KPI | Calculation |
|-----|-------------|
| **Overall CSAT** | Avg rating across all contexts |
| **NPS Score** | Net Promoter Score from CSAT data |
| **Low-rated Features** | Features with avg rating < 3.0 |

**Sections:**
- **CSAT by Feature** — Bar chart of avg satisfaction per context (search, playground, OCR, etc.)
- **CSAT Trend** — 30-day rolling average
- **Recent Feedback Comments** — Latest user comments from CSAT submissions
- **Feature-specific Issues** — Low-rated comments grouped by feature

---

## Technical Architecture

### Route & Controller
- **URL**: `/jim19ud83/dashboard`
- **Controller**: `src/Controller/AnalyticsDashboardController.php`
- **Auth**: Admin role check (same as existing admin routes)
- **API prefix**: `/jim19ud83/dashboard/api/`

### API Endpoints
| Endpoint | Purpose | Cache |
|----------|---------|-------|
| `/api/overview` | KPI cards + summary charts | 5 min |
| `/api/users` | User stats, cohorts, demographics | 5 min |
| `/api/engagement` | Feature usage, search, social | 5 min |
| `/api/revenue` | MRR, subscriptions, conversions | 15 min |
| `/api/content` | Index health, search quality | 15 min |
| `/api/ai-ops` | AI operations, costs, errors | 5 min |
| `/api/satisfaction` | CSAT scores, feedback | 15 min |

### Data Sources
| Source | Table/Service | Available |
|--------|---------------|-----------|
| User registrations | `fos_user` | ✅ |
| User logins | `fos_user.last_login` | ✅ |
| Subscriptions | `playground_subscription` | ✅ |
| AI usage | `playground_usage_log` | ✅ |
| Search queries | `search_query_log` | ✅ |
| Search clicks | `search_click_log` | ✅ |
| OCR jobs | `ocr_job` | ✅ |
| Related work | `related_work_project` | ✅ |
| CSAT feedback | `csat_feedback` | ✅ |
| Email tracking | `email_tracker` | ✅ |
| Social posts | `profile_post` | ✅ |
| ES index stats | Elasticsearch API | ✅ |
| Stripe data | Stripe API (webhook/sync) | 🔄 Phase 2 |
| Page views | Not tracked yet | ❌ Phase 3 |

### Frontend
- **Template**: `templates/admin/analytics/dashboard.html.twig`
- **JS**: Inline, Chart.js for charts, fetch API for data loading
- **CSS**: Inline, CSS Grid for responsive layout
- **Auto-refresh**: Every 5 minutes via setInterval

---

## Implementation Phases

### Phase 1: Foundation + Overview Tab
1. Create `AnalyticsDashboardController` with admin auth
2. Create main template with tab navigation framework
3. Implement Overview tab — 5 KPI cards + 4 charts
4. Add API endpoints for overview data
5. Add route to header admin section

### Phase 2: Users + Engagement Tabs
6. User registration trends, demographics, retention
7. Feature usage breakdown, search analytics
8. Top pages, returning users

### Phase 3: Revenue + AI Operations
9. MRR calculation, subscription breakdown, trial conversion
10. AI cost tracking, error rates, latency monitoring
11. Budget alerts

### Phase 4: Content + Satisfaction
12. ES index health, search quality metrics
13. CSAT by feature, feedback review
14. Content gap analysis

### Phase 5: Advanced
15. Stripe API integration for abandoned payments
16. User cohort analysis
17. Page view tracking (new middleware)
18. Export to CSV/PDF
19. Email digest of KPIs (daily/weekly)

---

## Files To Create

| File | Purpose |
|------|---------|
| `src/Controller/AnalyticsDashboardController.php` | Routes + API endpoints |
| `src/Service/AnalyticsDashboardService.php` | Data aggregation queries |
| `templates/admin/analytics/dashboard.html.twig` | Main template |

## Files To Modify

| File | Change |
|------|--------|
| `templates/header.html.twig` | Add admin dashboard link |
| `config/services.yaml` | Register service (if needed) |