Custom Sales Reports for 1C-Bitrix: Analytics and Dashboards

Imagine: 5,000 orders monthly, managers spending 15 hours exporting data from Bitrix and building reports in Excel. Up to 30% of data lost due to manual copying. Standard Bitrix reports don't show profit per manager, ABC analysis of products, or conversion by channel. Custom analytics drastically reduces manual processing time and associated costs.

We build custom sales reports turnkey — from ORM queries to admin dashboard and Excel export. In 5 years we've completed over 50 projects for Bitrix stores. Each report speeds up decision-making 10x faster than manual export.

Limitations of Standard Bitrix Reports

The built-in sale module outputs a flat order table with minimal grouping. Sales management requires segmentation by manager with returns, breakdown by product categories, average check dynamics, repeat purchase analysis. Standard reports can't link data from different tables — orders, basket, payments, order properties — into a single analytical query. Estimates show up to 80% of manager time is spent on manual data handling. Without custom analytics, business relies on guesswork, not numbers.

How to Build a Revenue Report by Manager?

The most demanded report — revenue by period with manager breakdown. Task: show how much each manager brought in, from how many orders, average check, and cancellation rate. Data is collected via ORM query to OrderTable grouped by manager and month. Metrics calculated: revenue (excluding cancellations), cancelled amount, order count, average check, conversion to completed order, processing speed.

Example ORM query (without filter):

use Bitrix\Sale\Internals\OrderTable; use Bitrix\Main\Entity\ExpressionField; $result = OrderTable::getList([ 'select' => [ 'RESPONSIBLE_ID', 'MONTH' => new ExpressionField('MONTH', "DATE_TRUNC('month', %s)", ['DATE_INSERT']), 'REVENUE' => new ExpressionField('REVENUE', 'SUM(CASE WHEN %s NOT IN (\'F\', \'CA\') THEN %s ELSE 0 END)', ['STATUS_ID', 'PRICE']), 'CANCELLED' => new ExpressionField('CANCELLED', 'SUM(CASE WHEN %s IN (\'F\', \'CA\') THEN %s ELSE 0 END)', ['STATUS_ID', 'PRICE']), 'ORDER_COUNT' => new ExpressionField('ORDER_COUNT', 'COUNT(%s)', ['ID']), 'AVG_CHECK' => new ExpressionField('AVG_CHECK', 'AVG(CASE WHEN %s NOT IN (\'F\', \'CA\') THEN %s END)', ['STATUS_ID', 'PRICE']), ], 'filter' => [ '>=DATE_INSERT' => DateTime::createFromPhp(new \DateTime('first day of this month')), ], 'group' => ['RESPONSIBLE_ID', 'MONTH'], 'order' => ['MONTH' => 'ASC', 'REVENUE' => 'DESC'], ]); 

Manager metrics we calculate:

• Revenue (excluding cancellations and returns)
• Cancelled amount — indicates quality of work
• Average check — comparison across managers
• Order count — workload
• Conversion from "New" to "Completed" — COUNT(status=F) / COUNT(*) by status
• Average processing speed — AVG(DATE_STATUS - DATE_INSERT)

On one project we reduced report build time from 15 seconds to 2 by adding proper indexes.

How to Optimize Report Performance?

On a store with 50,000+ orders, ORM grouping queries take 3-10 seconds. Solutions:

• Materialized views (PostgreSQL) or summary table recalculated by agent once per hour
• Mandatory indexes: (DATE_INSERT, STATUS_ID) on b_sale_order, (ORDER_ID, PRODUCT_ID) on b_sale_basket
• Report cache in Bitrix managed_cache with TTL 1 hour, invalidated on order change

ABC Analysis and Its Implementation in Bitrix

ABC analysis shows which products and categories generate revenue and which are dead weight. Typically 20% of products bring 80% of revenue. Implemented via window function SUM() OVER (ORDER BY revenue DESC) or programmatically after fetching. Sort products by descending revenue, calculate running total, assign category: A (80% revenue), B (15%), C (5%).

Structure: JOIN BasketTable with OrderTable (filter paid orders) and catalog infoblock. Group by sections with aggregation: revenue, units sold, average selling price, average discount.

Visualization and Dashboard

Reports are placed on a custom page in /local/admin/. Visualization stack:

• Chart.js — line charts for dynamics, bar charts for manager comparison
• HTML tables with sorting — for detailed data
• PhpSpreadsheet — export to xlsx with formatting, totals formulas, auto-width columns

Dashboard is built as a single-page with tabs: "Total Revenue", "By Manager", "By Product", "ABC Analysis". Data loaded via AJAX endpoint, filter parameters (period, manager, category) passed in the request.

Integration with External Systems

For a complete picture, integration with 1C Trade Management/ERP, CRM, or telephony is often needed. We connect payment data via REST API, sync counterparty directories, add conversion funnel by traffic channels. For conversion report we use sale module data and order custom properties, showing the customer journey from lead to payment.

What's Included in the Work

• Analytics: define metrics, agree on slices
• Backend: ORM queries, optimization, caching
• Frontend: dashboard, graphs, filters
• Export: Excel templates, formatting
• Testing: verification on real data, load testing
• Documentation for reports and 3 months support

Development Timeline

Typically 1-2 weeks depending on number of reports and metric complexity. Stages: analytics (1-2 days), backend (3-5 days), frontend (2-3 days), export (1 day), testing (1-2 days). Result — a dashboard in admin panel with export, replacing manual export and Excel processing.

Contact us to discuss your metrics and get a consultation. Request custom report development — we'll prepare a commercial proposal within a day.