Developing a KPI dashboard for a 1C-Bitrix administrator

Developing a KPI Dashboard for the 1C-Bitrix Administrator

The standard 1C-Bitrix admin panel displays technical metrics — server load, errors, module status. Business KPIs are absent: today's revenue, funnel conversion, and average order value per manager are tracked in separate systems or in spreadsheets. A business metrics dashboard embedded directly in 1C-Bitrix eliminates this gap.

Dashboard Architecture

The dashboard is implemented as a dedicated page in the admin panel: /bitrix/admin/kpi_dashboard.php. Alternatively, as a public page in a restricted section — for manager access without /bitrix/admin/ permissions.

Data is aggregated directly from the database via SQL — this is faster than sequential 1C-Bitrix API calls for each widget. Visualization uses Chart.js or ApexCharts, loaded as JavaScript dependencies.

Key SQL Queries for KPIs

Revenue over a period, broken down by day:

SELECT
    DATE(o.date_insert) AS day,
    COUNT(o.id)         AS orders_count,
    SUM(o.price)        AS revenue,
    AVG(o.price)        AS avg_check
FROM b_sale_order o
WHERE
    o.lid = 's1'
    AND o.canceled = 'N'
    AND o.date_insert >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(o.date_insert)
ORDER BY day;

Cart-to-order conversion:

WITH baskets AS (
    SELECT DATE(date_insert) AS day, COUNT(DISTINCT fuser_id) AS basket_users
    FROM b_sale_basket
    WHERE site_id = 's1' AND date_insert >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY DATE(date_insert)
),
orders AS (
    SELECT DATE(date_insert) AS day, COUNT(DISTINCT user_id) AS order_users
    FROM b_sale_order
    WHERE lid = 's1' AND date_insert >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY DATE(date_insert)
)
SELECT b.day,
       b.basket_users,
       COALESCE(o.order_users, 0) AS order_users,
       ROUND(COALESCE(o.order_users, 0)::NUMERIC / NULLIF(b.basket_users, 0) * 100, 2) AS conversion
FROM baskets b
LEFT JOIN orders o ON o.day = b.day
ORDER BY b.day;

Top products by revenue:

SELECT
    ie.name AS product_name,
    SUM(bi.quantity) AS qty_sold,
    SUM(bi.price * bi.quantity) AS revenue
FROM b_sale_basket_item bi
JOIN b_sale_order_shipment_item si ON si.basket_id = bi.id
JOIN b_sale_order o ON o.id = bi.order_id
JOIN b_iblock_element ie ON ie.id = bi.product_id
WHERE o.canceled = 'N' AND o.date_insert >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY ie.id, ie.name
ORDER BY revenue DESC
LIMIT 20;

PHP Widget Implementation

Data is served via an AJAX endpoint and rendered with Chart.js:

// /local/ajax/kpi_data.php
if (!$USER->IsAdmin()) {
    header('HTTP/1.1 403 Forbidden');
    exit;
}

$widget = $_GET['widget'] ?? '';
$dateFrom = $_GET['date_from'] ?? date('Y-m-d', strtotime('-30 days'));
$dateTo   = $_GET['date_to']   ?? date('Y-m-d');

$db = \Bitrix\Main\Application::getConnection();

switch ($widget) {
    case 'revenue_chart':
        $data = $db->query("
            SELECT DATE(date_insert) as day,
                   SUM(price) as revenue,
                   COUNT(*) as cnt
            FROM b_sale_order
            WHERE lid = ? AND canceled = 'N'
              AND DATE(date_insert) BETWEEN ? AND ?
            GROUP BY DATE(date_insert)
            ORDER BY day
        ", [SITE_ID, $dateFrom, $dateTo])->fetchAll();
        break;

    case 'top_products':
        // ... see query above
        break;
}

header('Content-Type: application/json');
echo json_encode(['success' => true, 'data' => $data ?? []]);

// Initialize the revenue chart
fetch('/local/ajax/kpi_data.php?widget=revenue_chart&date_from=2024-01-01')
    .then(r => r.json())
    .then(({ data }) => {
        const ctx = document.getElementById('revenueChart').getContext('2d');
        new Chart(ctx, {
            type: 'bar',
            data: {
                labels: data.map(d => d.day),
                datasets: [{
                    label: 'Revenue',
                    data: data.map(d => d.revenue),
                    backgroundColor: '#4F81BD',
                }]
            },
            options: { responsive: true, plugins: { legend: { display: false } } }
        });
    });

Case Study: Dashboard for an Electronics E-Commerce Store

A project with 4 managers handling 50–80 orders per day. The business owner wanted real-time visibility into revenue, conversion, and manager workload. A dashboard with 6 widgets was implemented:

1. Revenue today / yesterday / one week ago (numerical and percentage comparison)
2. 30-day revenue chart (bar chart)
3. Funnel: visitors → carts → orders (data from 1C-Bitrix + Yandex.Metrica API)
4. Top 10 products by revenue for the period
5. Manager workload: number of active orders per manager
6. Open orders older than 3 days (requiring attention)

The dashboard refreshes every 5 minutes via setInterval. Heavy aggregate queries are cached for 5 minutes in the b_cache_tag table with invalidation triggered by order status changes.

Development time: 12 working days (SQL queries, PHP backend, dashboard layout, Metrica integration).

Timeline