Developing custom reports for the 1C-Bitrix online store

Developing Custom Reports for 1C-Bitrix E-Commerce

Standard analytics in the sale module of 1C-Bitrix covers basic scenarios — revenue for a period, order count, conversion by status. But once a business moves beyond "how much did we sell this month", problems arise. You need customer segmentation, manager analysis accounting for returns, margin breakdown by product category — and standard tools are no longer sufficient.

Custom reports solve this: full control over data selection, arbitrary grouping and aggregation, visualization in the needed format, and export for further analysis.

Built-in Sale Module Reports: What Exists and What's Missing

The sale module provides several built-in reports through the admin section: orders report, products report, status summary. They work through the bitrix:sale.report.construct component and use an internal report building mechanism.

Built-in report limitations:

• Fixed set of groupings — can't add arbitrary fields
• No cross-tables (pivot) — impossible to build a "product × region" matrix
• No cohort analysis and RFM segmentation
• Export only to CSV with limited formatting
• No ability to build composite metrics (LTV, average check accounting for returns, profitability)

For stores with thousands of orders monthly, these reports are objectively insufficient.

ORM Queries via OrderTable and Related Entities

The foundation of custom reports is Bitrix D7 ORM. Key tables:

ORM allows building queries with JOINs, grouping, and aggregate functions without writing raw SQL. Example — revenue by month with manager breakdown:

$result = OrderTable::getList([
    'select' => [
        'MONTH' => new ExpressionField('MONTH', "DATE_TRUNC('month', %s)", ['DATE_INSERT']),
        'RESPONSIBLE_ID',
        'TOTAL' => new ExpressionField('TOTAL', 'SUM(%s)', ['PRICE']),
        'CNT' => new ExpressionField('CNT', 'COUNT(%s)', ['ID']),
    ],
    'filter' => [
        '>=DATE_INSERT' => DateTime::createFromPhp(new \DateTime('2024-01-01')),
        '!STATUS_ID' => 'F', // exclude canceled
    ],
    'group' => ['MONTH', 'RESPONSIBLE_ID'],
    'order' => ['MONTH' => 'ASC'],
]);

For complex queries with subqueries and CTEs, sometimes you need to use $DB->Query() directly — ORM doesn't cover all SQL constructs.

Grouping and Aggregation: Typical Dimensions

Practice shows that businesses most often need the following dimensions:

By periods — day, week, month, quarter. Uses DATE_TRUNC in PostgreSQL or DATE_FORMAT in MySQL. Important to account for server timezone when aggregating by days.

By products and categories — JOIN with BasketTable and catalog infoblock. Here you need to carefully handle SKUs: trade offers (SKUs) reference the parent element through PROPERTY_CML2_LINK, and grouping should be by the parent product.

By managers — the RESPONSIBLE_ID field in orders. Useful to supplement with data from \Bitrix\Main\UserTable (name, department) and calculate not just revenue but also processed orders count, average check, cancellation percentage.

By regions — through order properties (city, region) or through location bindings (\Bitrix\Sale\Location\LocationTable). Bitrix location tree allows aggregating by levels — city → region → country.

Visualization and Export

Chart.js has proven itself well for displaying charts on a custom admin page — a lightweight library that doesn't require compilation. Connects via CDN or local file in /local/admin/. Data is passed as JSON array from PHP.

Typical visualization set for e-commerce dashboard:

• Line chart — revenue and order dynamics by period
• Bar chart — manager or category comparison
• Doughnut — payment method or status shares
• Heatmap (via chartjs-chart-matrix plugin) — activity by day of week and hours

Excel export is implemented via PhpSpreadsheet. Library is installed via Composer in /local/:

cd /home/bitrix/www/local
composer require phpoffice/phpspreadsheet

PhpSpreadsheet allows creating files with formatting, formulas, multiple sheets — full reports that accounting can use without additional processing.

Dashboard in Admin Panel

A custom page is placed in /local/admin/. For integration with the admin menu, use /local/admin/menu.php file or the OnBuildGlobalMenu event handler.

Typical dashboard structure:

/local/admin/
├── custom_dashboard.php      # entry point
├── reports/
│   ├── sales_by_period.php   # period reports
│   ├── rfm_analysis.php      # RFM analysis
│   └── manager_stats.php     # manager statistics
├── ajax/
│   └── report_data.php       # AJAX endpoint for charts
└── assets/
    ├── chart.min.js
    └── dashboard.css

Each page includes a prolog (require_once $_SERVER['DOCUMENT_ROOT'].'/bitrix/modules/main/include/prolog_admin_before.php') and uses the CAdminPage class for consistent formatting. For AJAX requests — separate endpoints with permission checking via $APPLICATION->GetGroupRight('sale').

Deep-Dive: RFM Customer Analysis Report

RFM analysis segments customers by three parameters:

• Recency — how long ago the last purchase was
• Frequency — purchase frequency over a period
• Monetary — total purchases over a period

Each parameter is rated on a scale from 1 to 5 (quintiles). The combination gives 125 segments, grouped into practically useful categories: "loyal", "sleeping", "lost", "promising new".

Building algorithm:

1. Select all customers with completed orders in the analysis period (usually 12 months)
2. For each customer calculate three metrics — last order date, order count, total sum
3. Distribute customers by quintiles for each metric. Quintiles are calculated via NTILE(5) OVER (ORDER BY ...) in SQL or programmatically via sorting and array division
4. Assign segment by R-F-M combination

SQL query for calculating base metrics:

SELECT
    o.USER_ID,
    MAX(o.DATE_INSERT) AS last_order_date,
    EXTRACT(DAY FROM NOW() - MAX(o.DATE_INSERT)) AS recency_days,
    COUNT(o.ID) AS frequency,
    SUM(o.PRICE) AS monetary
FROM b_sale_order o
WHERE o.STATUS_ID NOT IN ('F', 'CA')
  AND o.DATE_INSERT >= NOW() - INTERVAL '12 months'
  AND o.PAYED = 'Y'
GROUP BY o.USER_ID

Segment mapping. Not all 125 combinations are equally useful. In practice, a simplified table is used:

Display in dashboard. RFM report is shown as a table with segment filtering and customer list export. Separately — treemap visualization of customer distribution by segments (block size = customer count, color = monetary).

For automation, the report runs on schedule via Bitrix agent (CAgent). Result is cached to a separate table — recalculating for 50,000 customers takes 10-15 seconds, and doing this on every page load is impractical.

Stages of Custom Report Development

Custom reports are an investment in business manageability. Bitrix standard tools provide the start, but for data-driven decision-making, you need analytics tailored to your specific business process.