Feature Request: Subscription Analytics Dashboard

[mustafaneguib]

GitHub Issue: Subscription Analytics Dashboard

Issue Type

Feature Enhancement

Priority

Medium

Effort Estimation

7-10 days (Large)

Labels

enhancement, frontend, backend, analytics, admin-panel, business-intelligence

---

Problem Statement

Currently, there is no centralized view of subscription metrics and trends. Administrators and business stakeholders cannot easily answer critical questions:

Business Questions Without Answers

1. Revenue: What's our MRR (Monthly Recurring Revenue)? ARR?
2. Growth: How many new subscriptions this month? Quarter?
3. Churn: How many users downgraded or cancelled?
4. Tier distribution: What percentage of users are on each tier?
5. Conversion: What's the upgrade rate from FREE to paid tiers?
6. Retention: What's the subscription renewal rate?
7. Engagement: Do higher tiers correlate with more usage?
8. Forecasting: What will revenue be next quarter?

Current Workaround

• Manual SQL queries against production database (risky, time-consuming)
• Spreadsheet exports updated weekly (stale data)
• No real-time visibility into subscription health
• Cannot identify trends until they become problems

Business Impact

• Poor decision making: Pricing changes made without data
• Missed opportunities: Can't identify upsell candidates
• Reactive management: Problems detected too late
• Investor reporting: Manual data compilation takes days

---

Current State

Implemented

• ✅ Subscription data stored in database
• ✅ Subscription history tracked
• ✅ User subscription management system

Missing

• ❌ Analytics aggregation service
• ❌ Metrics calculation engine
• ❌ Admin analytics dashboard UI
• ❌ Real-time charts and visualizations
• ❌ Export functionality (CSV, PDF)
• ❌ Scheduled reports
• ❌ Custom date range filtering

---

Proposed Solution

Dashboard Sections

1. Key Metrics Overview (Top of Dashboard)

┌─────────────────────────────────────────────────────────────┐
│  MRR: $45,230    ARR: $542,760    Active Subs: 1,234        │
│  ▲ +12.5%        ▲ +8.3%          ▲ +67 this month          │
│                                                              │
│  Churn Rate: 2.1%    Avg LTV: $1,850    ARPU: $36.67       │
│  ▼ -0.5%             ▲ +$120          ▲ +$2.30              │
└─────────────────────────────────────────────────────────────┘

2. Revenue Trend Chart

• Line chart showing MRR over time (last 12 months)
• Breakdown by tier (stacked area chart)
• Annotations for key events (new tier launched, price change)

3. Tier Distribution

• Pie chart showing % of users on each tier
• Bar chart comparing tier counts
• Trend: How has distribution changed over time?

4. Subscription Funnel

FREE → PRO → TEAM → BUSINESS → ENTERPRISE
5000    500    120      45          12

Conversion rates:
FREE → PRO: 10%
PRO → TEAM: 24%
TEAM → BUSINESS: 37.5%
BUSINESS → ENTERPRISE: 26.7%

5. Cohort Analysis

• Retention rates by signup month
• Heatmap showing % of users still subscribed N months later

6. Recent Activity Feed

• Last 50 subscription changes
• Real-time updates (WebSocket optional)

---

Technical Implementation

Architecture

Database → Analytics Service → Cache (Redis) → REST API → Dashboard UI
   ↓              ↓                   ↓            ↓            ↓
TypeORM    Aggregations         5min TTL      Express    Vue3/Chart.js
           Calculations                        JSON        Components

1. Analytics Service

File: backend/src/services/SubscriptionAnalyticsService.ts

import { DBDriver } from '../drivers/DBDriver.js';
import { EDataSourceType } from '../types/EDataSourceType.js';
import { DRAUserSubscription } from '../models/DRAUserSubscription.js';
import { DRASubscriptionTier } from '../models/DRASubscriptionTier.js';
import { getRedisClient } from '../config/redis.config.js';

export interface IAnalyticsMetrics {
    mrr: number; // Monthly Recurring Revenue
    arr: number; // Annual Recurring Revenue
    activeSubscriptions: number;
    churnRate: number; // % of users who cancelled
    averageLTV: number; // Lifetime Value
    arpu: number; // Average Revenue Per User
    growthRate: number; // Month-over-month growth %
}

export interface ITierDistribution {
    tierName: string;
    count: number;
    percentage: number;
    revenue: number;
}

export interface IRevenueByMonth {
    month: string; // YYYY-MM
    revenue: number;
    subscriptions: number;
}

export interface ICohortData {
    cohort: string; // YYYY-MM (signup month)
    month0: number; // Retention at month 0 (100%)
    month1: number; // Retention at month 1
    month2: number;
    month3: number;
    month6: number;
    month12: number;
}

export class SubscriptionAnalyticsService {
    private static instance: SubscriptionAnalyticsService;
    private redis = getRedisClient();
    
    private constructor() {}
    
    public static getInstance(): SubscriptionAnalyticsService {
        if (!SubscriptionAnalyticsService.instance) {
            SubscriptionAnalyticsService.instance = new SubscriptionAnalyticsService();
        }
        return SubscriptionAnalyticsService.instance;
    }
    
    /**
     * Get key subscription metrics
     * Cached for 5 minutes
     */
    async getMetrics(): Promise<IAnalyticsMetrics> {
        const cacheKey = 'analytics:metrics';
        const cached = await this.redis.get(cacheKey);
        
        if (cached) {
            return JSON.parse(cached);
        }
        
        const driver = await DBDriver.getInstance().getDriver(EDataSourceType.POSTGRESQL);
        if (!driver) throw new Error('PostgreSQL driver not available');
        
        const concreteDriver = await driver.getConcreteDriver();
        if (!concreteDriver) throw new Error('Failed to get PostgreSQL connection');
        
        const manager = concreteDriver.manager;
        
        // Calculate MRR (sum of all active subscriptions' monthly price)
        const mrrQuery = await manager
            .createQueryBuilder(DRAUserSubscription, 'sub')
            .leftJoinAndSelect('sub.subscription_tier', 'tier')
            .where('sub.is_active = :isActive', { isActive: true })
            .select('SUM(tier.price_per_month_usd)', 'total')
            .getRawOne();
        
        const mrr = parseFloat(mrrQuery?.total || '0');
        const arr = mrr * 12;
        
        // Count active subscriptions
        const activeSubscriptions = await manager.count(DRAUserSubscription, {
            where: { is_active: true }
        });
        
        // Calculate churn rate (last 30 days)
        const thirtyDaysAgo = new Date();
        thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);
        
        const cancelledLastMonth = await manager.count(DRAUserSubscription, {
            where: {
                is_active: false,
                cancelled_at: manager.createQueryBuilder()
                    .where('cancelled_at >= :date', { date: thirtyDaysAgo })
                    .getQuery()
            }
        });
        
        const churnRate = activeSubscriptions > 0 
            ? (cancelledLastMonth / activeSubscriptions) * 100 
            : 0;
        
        // Calculate average LTV (simplified: MRR / churn rate)
        const averageLTV = churnRate > 0 ? (mrr / (churnRate / 100)) : 0;
        
        // Calculate ARPU
        const arpu = activeSubscriptions > 0 ? mrr / activeSubscriptions : 0;
        
        // Calculate growth rate (compare to last month)
        const lastMonthMRR = await this.getMRRForMonth(
            new Date(new Date().setMonth(new Date().getMonth() - 1))
        );
        const growthRate = lastMonthMRR > 0 
            ? ((mrr - lastMonthMRR) / lastMonthMRR) * 100 
            : 0;
        
        const metrics: IAnalyticsMetrics = {
            mrr,
            arr,
            activeSubscriptions,
            churnRate: parseFloat(churnRate.toFixed(2)),
            averageLTV: parseFloat(averageLTV.toFixed(2)),
            arpu: parseFloat(arpu.toFixed(2)),
            growthRate: parseFloat(growthRate.toFixed(2))
        };
        
        // Cache for 5 minutes
        await this.redis.setex(cacheKey, 300, JSON.stringify(metrics));
        
        return metrics;
    }
    
    /**
     * Get tier distribution
     */
    async getTierDistribution(): Promise<ITierDistribution[]> {
        const cacheKey = 'analytics:tier-distribution';
        const cached = await this.redis.get(cacheKey);
        
        if (cached) {
            return JSON.parse(cached);
        }
        
        const driver = await DBDriver.getInstance().getDriver(EDataSourceType.POSTGRESQL);
        const manager = (await driver?.getConcreteDriver())?.manager;
        if (!manager) throw new Error('Database manager not available');
        
        const results = await manager
            .createQueryBuilder(DRAUserSubscription, 'sub')
            .leftJoinAndSelect('sub.subscription_tier', 'tier')
            .where('sub.is_active = :isActive', { isActive: true })
            .select('tier.tier_name', 'tierName')
            .addSelect('COUNT(*)', 'count')
            .addSelect('SUM(tier.price_per_month_usd)', 'revenue')
            .groupBy('tier.tier_name')
            .getRawMany();
        
        const total = results.reduce((sum, r) => sum + parseInt(r.count), 0);
        
        const distribution: ITierDistribution[] = results.map(r => ({
            tierName: r.tierName,
            count: parseInt(r.count),
            percentage: total > 0 ? (parseInt(r.count) / total) * 100 : 0,
            revenue: parseFloat(r.revenue || '0')
        }));
        
        // Cache for 5 minutes
        await this.redis.setex(cacheKey, 300, JSON.stringify(distribution));
        
        return distribution;
    }
    
    /**
     * Get revenue trend by month (last 12 months)
     */
    async getRevenueByMonth(months: number = 12): Promise<IRevenueByMonth[]> {
        const cacheKey = `analytics:revenue-by-month:${months}`;
        const cached = await this.redis.get(cacheKey);
        
        if (cached) {
            return JSON.parse(cached);
        }
        
        const driver = await DBDriver.getInstance().getDriver(EDataSourceType.POSTGRESQL);
        const manager = (await driver?.getConcreteDriver())?.manager;
        if (!manager) throw new Error('Database manager not available');
        
        const data: IRevenueByMonth[] = [];
        
        for (let i = months - 1; i >= 0; i--) {
            const date = new Date();
            date.setMonth(date.getMonth() - i);
            const month = date.toISOString().slice(0, 7); // YYYY-MM
            
            const mrr = await this.getMRRForMonth(date);
            const count = await this.getActiveSubscriptionCountForMonth(date);
            
            data.push({
                month,
                revenue: mrr,
                subscriptions: count
            });
        }
        
        // Cache for 1 hour
        await this.redis.setex(cacheKey, 3600, JSON.stringify(data));
        
        return data;
    }
    
    /**
     * Get cohort retention analysis
     */
    async getCohortAnalysis(): Promise<ICohortData[]> {
        const cacheKey = 'analytics:cohort-analysis';
        const cached = await this.redis.get(cacheKey);
        
        if (cached) {
            return JSON.parse(cached);
        }
        
        // Implement cohort analysis logic
        // This is complex and requires tracking user signup date vs subscription status over time
        // Simplified implementation for now
        
        const cohorts: ICohortData[] = [];
        
        // TODO: Implement full cohort analysis
        // For each signup month, calculate retention at 0, 1, 2, 3, 6, 12 months
        
        // Cache for 1 hour
        await this.redis.setex(cacheKey, 3600, JSON.stringify(cohorts));
        
        return cohorts;
    }
    
    /**
     * Helper: Get MRR for specific month
     */
    private async getMRRForMonth(date: Date): Promise<number> {
        const driver = await DBDriver.getInstance().getDriver(EDataSourceType.POSTGRESQL);
        const manager = (await driver?.getConcreteDriver())?.manager;
        if (!manager) return 0;
        
        const startOfMonth = new Date(date.getFullYear(), date.getMonth(), 1);
        const endOfMonth = new Date(date.getFullYear(), date.getMonth() + 1, 0);
        
        const result = await manager
            .createQueryBuilder(DRAUserSubscription, 'sub')
            .leftJoinAndSelect('sub.subscription_tier', 'tier')
            .where('sub.started_at <= :end', { end: endOfMonth })
            .andWhere('(sub.ends_at IS NULL OR sub.ends_at >= :start)', { start: startOfMonth })
            .andWhere('sub.is_active = :isActive', { isActive: true })
            .select('SUM(tier.price_per_month_usd)', 'total')
            .getRawOne();
        
        return parseFloat(result?.total || '0');
    }
    
    /**
     * Helper: Get active subscription count for specific month
     */
    private async getActiveSubscriptionCountForMonth(date: Date): Promise<number> {
        const driver = await DBDriver.getInstance().getDriver(EDataSourceType.POSTGRESQL);
        const manager = (await driver?.getConcreteDriver())?.manager;
        if (!manager) return 0;
        
        const startOfMonth = new Date(date.getFullYear(), date.getMonth(), 1);
        const endOfMonth = new Date(date.getFullYear(), date.getMonth() + 1, 0);
        
        return await manager
            .createQueryBuilder(DRAUserSubscription, 'sub')
            .where('sub.started_at <= :end', { end: endOfMonth })
            .andWhere('(sub.ends_at IS NULL OR sub.ends_at >= :start)', { start: startOfMonth })
            .andWhere('sub.is_active = :isActive', { isActive: true })
            .getCount();
    }
}

2. Analytics API Routes

File: backend/src/routes/admin/analytics.ts

import express from 'express';
import { SubscriptionAnalyticsService } from '../../services/SubscriptionAnalyticsService.js';

const router = express.Router();
const analyticsService = SubscriptionAnalyticsService.getInstance();

/**
 * GET /admin/analytics/metrics
 * Get key subscription metrics
 */
router.get('/metrics', async (req, res) => {
    try {
        const metrics = await analyticsService.getMetrics();
        return res.status(200).json({
            success: true,
            data: metrics
        });
    } catch (error: any) {
        console.error('[Analytics] Error fetching metrics:', error);
        return res.status(500).json({
            success: false,
            message: error.message || 'Failed to fetch metrics'
        });
    }
});

/**
 * GET /admin/analytics/tier-distribution
 * Get tier distribution data
 */
router.get('/tier-distribution', async (req, res) => {
    try {
        const distribution = await analyticsService.getTierDistribution();
        return res.status(200).json({
            success: true,
            data: distribution
        });
    } catch (error: any) {
        console.error('[Analytics] Error fetching tier distribution:', error);
        return res.status(500).json({
            success: false,
            message: error.message || 'Failed to fetch tier distribution'
        });
    }
});

/**
 * GET /admin/analytics/revenue-trend
 * Get revenue trend by month
 */
router.get('/revenue-trend', async (req, res) => {
    try {
        const months = parseInt(req.query.months as string) || 12;
        const trend = await analyticsService.getRevenueByMonth(months);
        return res.status(200).json({
            success: true,
            data: trend
        });
    } catch (error: any) {
        console.error('[Analytics] Error fetching revenue trend:', error);
        return res.status(500).json({
            success: false,
            message: error.message || 'Failed to fetch revenue trend'
        });
    }
});

/**
 * GET /admin/analytics/cohort-analysis
 * Get cohort retention analysis
 */
router.get('/cohort-analysis', async (req, res) => {
    try {
        const cohorts = await analyticsService.getCohortAnalysis();
        return res.status(200).json({
            success: true,
            data: cohorts
        });
    } catch (error: any) {
        console.error('[Analytics] Error fetching cohort analysis:', error);
        return res.status(500).json({
            success: false,
            message: error.message || 'Failed to fetch cohort analysis'
        });
    }
});

export default router;

3. Frontend Dashboard

File: frontend/pages/admin/analytics/subscriptions.vue

<template>
  <div class="subscription-analytics">
    <h2 class="text-3xl font-bold mb-8">Subscription Analytics</h2>
    
    <!-- Date Range Filter -->
    <div class="flex justify-between items-center mb-6">
      <div class="flex gap-3">
        <select v-model="state.dateRange" @change="loadData" class="px-4 py-2 border rounded">
          <option value="7d">Last 7 days</option>
          <option value="30d">Last 30 days</option>
          <option value="90d">Last 90 days</option>
          <option value="12m" selected>Last 12 months</option>
          <option value="all">All time</option>
        </select>
      </div>
      <div class="flex gap-3">
        <button @click="exportCSV" class="btn-secondary">
          <Icon name="carbon:download" />
          Export CSV
        </button>
        <button @click="exportPDF" class="btn-secondary">
          <Icon name="carbon:document-pdf" />
          Export PDF
        </button>
        <button @click="loadData" class="btn-primary">
          <Icon name="carbon:restart" />
          Refresh
        </button>
      </div>
    </div>
    
    <!-- Key Metrics Cards -->
    <div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-4 gap-6 mb-8">
      <MetricCard 
        title="MRR"
        :value="formatCurrency(state.metrics.mrr)"
        :change="state.metrics.growthRate"
        :loading="state.loading"
      />
      <MetricCard 
        title="ARR"
        :value="formatCurrency(state.metrics.arr)"
        :change="state.metrics.growthRate"
        :loading="state.loading"
      />
      <MetricCard 
        title="Active Subscriptions"
        :value="state.metrics.activeSubscriptions"
        :loading="state.loading"
      />
      <MetricCard 
        title="Churn Rate"
        :value="state.metrics.churnRate + '%'"
        :change="-0.5"
        :loading="state.loading"
        :inverse="true"
      />
    </div>
    
    <!-- Revenue Trend Chart -->
    <div class="bg-white p-6 rounded-lg shadow mb-8">
      <h3 class="text-xl font-semibold mb-4">Revenue Trend</h3>
      <LineChart 
        :data="state.revenueTrend" 
        :loading="state.loading"
        height="300px"
      />
    </div>
    
    <!-- Tier Distribution -->
    <div class="grid grid-cols-1 lg:grid-cols-2 gap-6 mb-8">
      <div class="bg-white p-6 rounded-lg shadow">
        <h3 class="text-xl font-semibold mb-4">Tier Distribution</h3>
        <PieChart 
          :data="state.tierDistribution" 
          :loading="state.loading"
        />
      </div>
      
      <div class="bg-white p-6 rounded-lg shadow">
        <h3 class="text-xl font-semibold mb-4">Revenue by Tier</h3>
        <BarChart 
          :data="state.tierDistribution" 
          :loading="state.loading"
        />
      </div>
    </div>
    
    <!-- Cohort Analysis -->
    <div class="bg-white p-6 rounded-lg shadow mb-8">
      <h3 class="text-xl font-semibold mb-4">Cohort Retention</h3>
      <CohortHeatmap 
        :data="state.cohortData" 
        :loading="state.loading"
      />
    </div>
    
    <!-- Recent Activity -->
    <div class="bg-white p-6 rounded-lg shadow">
      <h3 class="text-xl font-semibold mb-4">Recent Subscription Changes</h3>
      <ActivityFeed 
        :activities="state.recentActivities" 
        :loading="state.loading"
      />
    </div>
  </div>
</template>

<script setup lang="ts">
import { reactive, onMounted } from 'vue';

const state = reactive({
  loading: true,
  dateRange: '12m',
  metrics: {
    mrr: 0,
    arr: 0,
    activeSubscriptions: 0,
    churnRate: 0,
    averageLTV: 0,
    arpu: 0,
    growthRate: 0
  },
  revenueTrend: [],
  tierDistribution: [],
  cohortData: [],
  recentActivities: []
});

async function loadData() {
  state.loading = true;
  try {
    // Load all analytics data in parallel
    const [metrics, trend, distribution, cohorts] = await Promise.all([
      fetchMetrics(),
      fetchRevenueTrend(),
      fetchTierDistribution(),
      fetchCohortAnalysis()
    ]);
    
    state.metrics = metrics;
    state.revenueTrend = trend;
    state.tierDistribution = distribution;
    state.cohortData = cohorts;
  } catch (error) {
    console.error('Error loading analytics:', error);
  } finally {
    state.loading = false;
  }
}

async function fetchMetrics() {
  const response = await fetch('/admin/analytics/metrics', {
    headers: {
      'Authorization': `Bearer ${getAuthToken()}`,
      'Authorization-Type': 'auth'
    }
  });
  const result = await response.json();
  return result.data;
}

// ... other fetch functions ...

function formatCurrency(value: number): string {
  return new Intl.NumberFormat('en-US', {
    style: 'currency',
    currency: 'USD'
  }).format(value);
}

onMounted(() => {
  loadData();
});
</script>

4. Chart Components

File: frontend/components/admin/analytics/LineChart.vue

<template>
  <div class="line-chart">
    <canvas ref="chartCanvas"></canvas>
  </div>
</template>

<script setup lang="ts">
import { ref, watch, onMounted } from 'vue';
import Chart from 'chart.js/auto';

const props = defineProps<{
  data: any[];
  loading: boolean;
  height?: string;
}>();

const chartCanvas = ref<HTMLCanvasElement | null>(null);
let chartInstance: Chart | null = null;

function renderChart() {
  if (!chartCanvas.value || props.loading) return;
  
  if (chartInstance) {
    chartInstance.destroy();
  }
  
  const ctx = chartCanvas.value.getContext('2d');
  if (!ctx) return;
  
  chartInstance = new Chart(ctx, {
    type: 'line',
    data: {
      labels: props.data.map(d => d.month),
      datasets: [{
        label: 'Revenue',
        data: props.data.map(d => d.revenue),
        borderColor: 'rgb(102, 126, 234)',
        backgroundColor: 'rgba(102, 126, 234, 0.1)',
        tension: 0.4
      }]
    },
    options: {
      responsive: true,
      maintainAspectRatio: false,
      plugins: {
        legend: {
          display: false
        }
      }
    }
  });
}

watch(() => [props.data, props.loading], () => {
  renderChart();
});

onMounted(() => {
  renderChart();
});
</script>

---

Acceptance Criteria

Functional Requirements

• Dashboard displays key metrics: MRR, ARR, active subs, churn, LTV, ARPU
• Revenue trend chart shows last 12 months by default
• Tier distribution displayed as pie and bar charts
• Date range filter works (7d, 30d, 90d, 12m, all time)
• Refresh button reloads all data
• Export to CSV includes all metrics and trend data
• Export to PDF generates formatted report
• Loading states shown while fetching data
• Error handling with user-friendly messages
• Admin-only access enforced

Performance Requirements

• Dashboard loads in < 3 seconds
• Metrics cached for 5 minutes (reduce DB load)
• Revenue trend cached for 1 hour
• Charts render smoothly (60fps)
• Can handle 10,000+ subscriptions without performance degradation

Visual Requirements

• Responsive design (works on mobile/tablet)
• Charts use consistent color scheme
• Positive changes shown in green, negative in red
• Clear data labels on all charts
• Professional, polished appearance

---

Testing Requirements

Unit Tests

// Test analytics calculations
describe('SubscriptionAnalyticsService', () => {
    it('calculates MRR correctly', async () => {
        // Create subscriptions with known prices
        // Verify MRR calculation
    });
    
    it('calculates churn rate correctly', async () => {
        // Create/cancel subscriptions
        // Verify churn calculation
    });
});

Integration Tests

• Load dashboard → Verify all data fetched
• Change date range → Verify data updates
• Export CSV → Verify file contains correct data
• Test with 1000+ subscriptions → Verify performance

---

Files to Create/Modify

Backend

• Create: backend/src/services/SubscriptionAnalyticsService.ts
• Create: backend/src/routes/admin/analytics.ts
• Modify: backend/src/index.ts (register analytics routes)

Frontend

• Create: frontend/pages/admin/analytics/subscriptions.vue
• Create: frontend/components/admin/analytics/LineChart.vue
• Create: frontend/components/admin/analytics/PieChart.vue
• Create: frontend/components/admin/analytics/BarChart.vue
• Create: frontend/components/admin/analytics/CohortHeatmap.vue
• Create: frontend/components/admin/analytics/MetricCard.vue
• Create: frontend/stores/analytics.ts

---

Dependencies

Required

• Chart.js (^4.4.0) - Charting library
• Redis - Caching layer ✅

Optional

• jsPDF - PDF export
• Recharts - Alternative charting library
• D3.js - Advanced visualizations

---

Success Metrics

1. Usage: 80%+ of admins view dashboard weekly
2. Decision making: 3x faster pricing decisions
3. Reporting: Investor reports generated in minutes (vs days)
4. Performance: Dashboard loads in < 3 seconds

---

Related Issues/PRs

• Depends on: User Subscription Management (✅)
• Related to: Subscription History UI (provides data)
• Related to: Auto-Expiration Job (churn data)

---

Additional Notes

• Consider adding more advanced metrics (CAC, Payback Period, Net Dollar Retention)
• Implement scheduled email reports (daily/weekly/monthly)
• Add forecasting/prediction models
• Integrate with external BI tools (Looker, Tableau)