Building a Video Analytics Dashboard with Chart.js

Understanding how trending videos perform across regions is crucial for a multi-region platform. Here's how I built an analytics dashboard for TopVideoHub using Chart.js and PHP.

Data Collection

First, we need to track metrics per region over time:

CREATE TABLE analytics_daily (
    id INTEGER PRIMARY KEY,
    date TEXT NOT NULL,
    region TEXT NOT NULL,
    total_videos INTEGER DEFAULT 0,
    total_views BIGINT DEFAULT 0,
    avg_views INTEGER DEFAULT 0,
    top_category_id INTEGER,
    unique_channels INTEGER DEFAULT 0,
    cross_region_count INTEGER DEFAULT 0,
    UNIQUE(date, region)
);

A daily cron job populates this table:

function recordDailyAnalytics(\PDO $db): void {
    $date = date('Y-m-d');
    $regions = ['US','GB','JP','KR','TW','SG','VN','TH','HK'];

    foreach ($regions as $region) {
        $stats = $db->query(
            "SELECT 
                COUNT(*) as total_videos,
                COALESCE(SUM(v.view_count), 0) as total_views,
                COALESCE(AVG(v.view_count), 0) as avg_views,
                COUNT(DISTINCT v.channel_id) as unique_channels
             FROM video_regions vr
             JOIN videos v ON v.id = vr.video_id
             WHERE vr.region = ?
               AND DATE(vr.fetched_at) = ?",
            [$region, $date]
        )->fetch(\PDO::FETCH_ASSOC);

        // Count videos trending in multiple regions
        $crossRegion = $db->query(
            "SELECT COUNT(*) FROM (
                SELECT vr.video_id
                FROM video_regions vr
                WHERE vr.region = ? AND DATE(vr.fetched_at) = ?
                AND vr.video_id IN (
                    SELECT video_id FROM video_regions
                    GROUP BY video_id HAVING COUNT(DISTINCT region) > 1
                )
            )",
            [$region, $date]
        )->fetchColumn();

        $db->query(
            "INSERT OR REPLACE INTO analytics_daily 
             (date, region, total_videos, total_views, avg_views, unique_channels, cross_region_count)
             VALUES (?, ?, ?, ?, ?, ?, ?)",
            [$date, $region, $stats['total_videos'], $stats['total_views'],
             (int)$stats['avg_views'], $stats['unique_channels'], $crossRegion]
        );
    }
}

API Endpoint

Serve analytics data as JSON:

// GET /api/analytics?period=30&metric=total_views
function analyticsEndpoint(\PDO $db): void {
    $period = min((int)($_GET['period'] ?? 30), 90);
    $metric = $_GET['metric'] ?? 'total_videos';

    $allowed = ['total_videos', 'total_views', 'avg_views',
                'unique_channels', 'cross_region_count'];
    if (!in_array($metric, $allowed, true)) {
        http_response_code(400);
        echo json_encode(['error' => 'Invalid metric']);
        return;
    }

    $data = $db->query(
        "SELECT date, region, {$metric} as value
         FROM analytics_daily
         WHERE date >= DATE('now', '-{$period} days')
         ORDER BY date, region"
    )->fetchAll(\PDO::FETCH_ASSOC);

    // Group by region for Chart.js datasets
    $grouped = [];
    foreach ($data as $row) {
        $grouped[$row['region']][] = [
            'x' => $row['date'],
            'y' => (int)$row['value'],
        ];
    }

    header('Content-Type: application/json');
    header('Cache-Control: public, max-age=3600');
    echo json_encode($grouped);
}

Chart.js Dashboard

<div class="dashboard">
    <div class="chart-container">
        <canvas id="regionChart"></canvas>
    </div>
    <div class="chart-controls">
        <select id="metricSelect">
            <option value="total_videos">Total Videos</option>
            <option value="total_views">Total Views</option>
            <option value="unique_channels">Unique Channels</option>
            <option value="cross_region_count">Cross-Region Videos</option>
        </select>
        <select id="periodSelect">
            <option value="7">7 Days</option>
            <option value="30" selected>30 Days</option>
            <option value="90">90 Days</option>
        </select>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js@4/dist/chart.umd.min.js"></script>
<script>
const regionColors = {
    US: '#4e79a7', GB: '#f28e2b', JP: '#e15759',
    KR: '#76b7b2', TW: '#59a14f', SG: '#edc948',
    VN: '#b07aa1', TH: '#ff9da7', HK: '#9c755f'
};

let chart = null;

async function loadChart() {
    const metric = document.getElementById('metricSelect').value;
    const period = document.getElementById('periodSelect').value;

    const response = await fetch(
        `/api/analytics?metric=${metric}&period=${period}`
    );
    const data = await response.json();

    const datasets = Object.entries(data).map(([region, points]) => ({
        label: region,
        data: points,
        borderColor: regionColors[region],
        backgroundColor: regionColors[region] + '20',
        fill: false,
        tension: 0.3,
        pointRadius: 2,
    }));

    if (chart) chart.destroy();

    chart = new Chart(document.getElementById('regionChart'), {
        type: 'line',
        data: { datasets },
        options: {
            responsive: true,
            scales: {
                x: { type: 'time', time: { unit: 'day' } },
                y: { beginAtZero: true }
            },
            plugins: {
                legend: { position: 'bottom' },
                title: { display: true, text: `${metric} by Region` }
            }
        }
    });
}

document.getElementById('metricSelect').addEventListener('change', loadChart);
document.getElementById('periodSelect').addEventListener('change', loadChart);
loadChart();
</script>

This dashboard on TopVideoHub helps visualize trends like Korean content's cross-border reach and Japan's content insularity through real data rather than assumptions.