1C-Bitrix to Yandex DataLens: KPI-driven setup

A store owner on Bitrix faces scattered data: orders in b_sale_order, traffic in Yandex.Metrica, prices in a separate price list. Manually combining them in Excel takes hours and leads to errors. We connect Yandex DataLens directly to PostgreSQL, but without proper indexes and data marts, dashboards become slow. Recently, a client with a catalog of 500 000 products and 1 million orders complained about 30‑second queries. After creating a data mart and indexes, query time dropped to 0.2 seconds. In 70% of cases, a direct connection without indexes yields response times above 10 seconds. After optimization, query times are reduced by 85% on average.

Problems we solve

• Direct connection — straightforward access to tables b_sale_order, b_catalog_price, b_iblock_element. But every dashboard open triggers a query to the database. If a table contains more than 500 000 rows, speed drops. We solve this by creating indexes and limiting the selection.
• Data mart — aggregated tables updated on a schedule. Queries execute in fractions of a second, even on 1 million orders. However, the update delay of up to 60 minutes is not acceptable for everyone.
• ClickHouse — stream processing of events: orders, views, carts. Data freshness is near real‑time, but additional infrastructure is required.

Direct connection is 3× faster in freshness than a data mart, but a data mart is 10× faster in query speed. The choice depends on priorities. If you are unsure which option fits, get a consultation from an engineer.

Which data schema to choose?

For a store with fewer than 100 000 orders per month, direct connection works if the database has indexes. For volumes exceeding 500 000 records, a data mart reduces database load by 8–10×. ClickHouse is justified for real‑time analytics with dozens of events per second. We help select the schema after auditing your database.

How to prepare the Bitrix database for DataLens?

The cleanest option is a direct PostgreSQL connector. The database must be reachable from the Yandex cloud (white IP or VPN). We create a read‑only user:

CREATE USER datalens_ro WITH PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE bitrix TO datalens_ro;
GRANT USAGE ON SCHEMA public TO datalens_ro;
GRANT SELECT ON b_sale_order, b_sale_order_item,
                b_iblock_element, b_catalog_price,
                b_user TO datalens_ro;

Details in the official 1C-Bitrix database optimization documentation.

If the database is large (millions of orders), it is better to use a data mart. Here is an example of a daily sales mart:

CREATE TABLE datalens_orders_daily AS
SELECT
    DATE_TRUNC('day', DATE_INSERT)::DATE AS order_date,
    CURRENCY                              AS currency,
    COUNT(*)                              AS orders_count,
    SUM(PRICE)                            AS revenue,
    SUM(PRICE_DELIVERY)                   AS delivery_total,
    AVG(PRICE)                            AS avg_order_value
FROM b_sale_order
WHERE CANCELED = 'N'
GROUP BY 1, 2;

CREATE INDEX idx_datalens_od_date ON datalens_orders_daily(order_date);

function UpdateDataLensOrdersVitrine() {
    global $DB;
    $DB->Query("
        INSERT INTO datalens_orders_daily
        SELECT DATE_TRUNC('day', DATE_INSERT)::DATE, CURRENCY,
               COUNT(*), SUM(PRICE), SUM(PRICE_DELIVERY), AVG(PRICE)
        FROM b_sale_order
        WHERE CANCELED = 'N'
          AND DATE_INSERT >= NOW() - INTERVAL '2 days'
        GROUP BY 1, 2
        ON CONFLICT (order_date, currency) DO UPDATE
        SET orders_count = EXCLUDED.orders_count,
            revenue = EXCLUDED.revenue
    ", false, 'FILE', __LINE__);
    return \CAgent::DEFAULT_PERIOD;
}

What's included in the setup?

• Audit of the current database and performance.
• Selection of the connection method (direct, data mart, ClickHouse).
• Creation of a read‑only user and indexes.
• Writing agents to update data marts.
• Configuring the DataLens connection and creating datasets.
• Dashboard development tailored to your KPIs: sales funnel, cohort analysis, ABC product analysis.
• Documentation on the schema and update process.
• Employee training on using dashboards.
• 3‑month warranty support.

How to connect Yandex.Metrica as a second source?

DataLens connects directly to Metrica — you only need the counter number and an OAuth token. After creating two connections (PostgreSQL + Metrica), you can build a dataset with a JOIN on date: revenue and traffic on one chart. This shows which advertising campaigns actually generate money, not just clicks.

Typical mistakes when integrating DataLens with Bitrix

Work process

1. Analysis — we study your Bitrix structure, table composition, and required reports.
2. Design — we choose the connection method (direct, data mart, or ClickHouse) and design the schema.
3. Implementation — we configure the connection, create data marts or agents.
4. Testing — we verify data correctness and dashboard speed.
5. Deployment — we move to production and document.
6. Training — we show employees how to work with the dashboards.

Timeline

Estimated timeline: 5 to 15 business days, depending on complexity (data volume, number of sources, freshness requirements). The exact timeline is determined after auditing your database. Our experience: 15+ successful integrations of DataLens with Bitrix. Average manager time savings: 40%. With over 5 years in the industry and over 30 BI projects completed, we deliver reliable results. Warranty support: 3 months. Contact us for a cost and timeline estimate.

What Professional 1C-Bitrix Installation Includes

We start by checking innodb_buffer_pool_size. The default MySQL value (128 MB) is a death sentence for an online store with a catalog of 10,000+ items. We set 70–80% of available RAM on a dedicated server, 50% on VPS. This single setting speeds up the site by 2–3 times compared to the default. We'll assess your project in one day — get a consultation. Contact us to order turnkey installation with performance guarantee.

How to Choose Hosting and Edition for 1C-Bitrix Installation?

BitrixVM is a virtual machine with a pre-installed stack: nginx + Apache, PHP-FPM, MySQL/MariaDB, Sphinx, Push server. For VPS — the best start. Everything is already configured for Bitrix, including OPcache, log rotation, and firewall. Management via web panel on port 8890. Bitrix documentation recommends starting with BitrixVM for predictable performance.

VPS/VDS is the sweet spot. Minimum configuration for a medium online store: 2 vCPU, 4 GB RAM, SSD. Optimal: 4 vCPU, 8 GB RAM. OS: Ubuntu 22.04 or Debian 12. If not BitrixVM, we configure the stack manually for the task. Virtual hosting — only for business cards and landing pages. Requirements: PHP 8.0+, MySQL 5.7+ / MariaDB 10.0+, 512 MB RAM, .htaccess. 1C-Bitrix hosting partners guarantee compatibility. Dedicated server — for highload. Typical architecture: web server separate, database separate, Redis/Memcached separate. For Enterprise edition — web cluster with load balancer. Cloud (Yandex Cloud, VK Cloud, Selectel) — when load spikes: sales, seasonal peaks. Autoscaling via Managed Kubernetes or simple VM vertical scaling.

Choosing the edition is equally important. A common mistake: choosing "Small Business" for a store that grows to B2B with wholesale prices and three warehouses in six months. Upgrading to "Business" — pay the difference, data is not lost, but it's better to plan ahead. Our specialists select the edition for current tasks and with room for growth. For example, the "Business" license (about 35,000 RUB) pays off through multi-warehouse and 1C exchange, while the wrong choice can lead to a loss of up to 30,000 RUB monthly on excess resources.

What Server Settings Are Critical for 1C-Bitrix?

Web Server and PHP

nginx as reverse proxy + Apache (mod_php) or nginx + PHP-FPM directly. The second option saves memory — Apache is not needed. But some Bitrix modules use .htaccess, so for compatibility we sometimes keep Apache. nginx configuration: fastcgi_read_timeout 300 — for long operations (1C import), client_max_body_size 1024m — large file uploads. Block access to .settings.php, .settings_extra.php, bitrix/.settings.php — they contain database passwords. Rewrite rules from urlrewrite.php — Bitrix generates them, but with nginx + PHP-FPM they need to be duplicated. PHP 8.0–8.2 with extensions: mbstring, curl, gd, xml, json, opcache, redis/memcached. Key php.ini settings: opcache.memory_consumption=256, opcache.max_accelerated_files=20000, max_execution_time=300, memory_limit=512M, upload_max_filesize=100M, post_max_size=128M.

Database and Caching

MySQL/MariaDB. Key my.cnf parameters: innodb_buffer_pool_size — 70–80% RAM, innodb_log_file_size=256M, tmp_table_size=256M, max_heap_table_size=256M, thread_pool_size — number of CPU cores. Encoding utf8mb4 mandatory, otherwise emoji and special characters break. Redis is preferable to Memcached for Bitrix — supports persistent connections and is more reliable. In production, Redis handles concurrent writes three times faster than Memcached under typical load. Configure in .settings_extra.php:

'cache' => ['value' => ['type' => ['class_name' => '\\Bitrix\\Main\\Data\\CacheEngineRedis']]]
'session' => ['value' => ['mode' => 'default', 'handlers' => ['general' => ['type' => 'redis']]]]

sudo apt install redis-server
sudo systemctl enable redis

SSL, Email, and Cron

SSL — Let's Encrypt via certbot in 90% of cases. Redirect HTTP → HTTPS (301), HSTS, TLS 1.2/1.3, OCSP Stapling. In Bitrix, switch to HTTPS in the main module settings. Email: abandon mail() — connect SMTP (Yandex.Mail for domain, Mail.ru for Business). Be sure to configure SPF, DKIM, DMARC. Without SPF, emails go to spam. Test deliverability via mail-tester.com — score 9+/10. Cron: Bitrix agents switch to system cron — * * * * * /usr/bin/php /var/www/bitrix/modules/main/tools/cron_events.php. Schedule 1C exchange (15–60 min), search reindex, backups (mysqldump + rsync, rotation 7+4), temporary file cleanup.

Security and Administration

File system: owner www-data, directories 755, files 644, upload 775. nginx blocks access to configuration files. Enable Bitrix Proactive Protection — WAF, activity control (block after 5 failed attempts), kernel integrity check. For admin panel: two-factor authentication via Google Authenticator or OTP, restrict access by IP via nginx for paranoid.

How Long Does 1C-Bitrix Installation and Configuration Take?

Post-Installation Checklist

1. Performance Monitor (/bitrix/admin/perfmon_panel.php) — aim for 30+ points. Below 20 means serious configuration issues.
2. System Check — automatic check of all parameters. Red items must be fixed, yellow — case by case.
3. Security Scanner — check for typical vulnerabilities.
4. PageSpeed Insights — TTFB < 200ms on VPS, LCP < 2.5s.
5. Test 1C exchange — if integration is planned, verify CommerceML exchange before launch.

Additionally, check software versions, caching settings, cron operation, SSL certificate, SPF/DKIM/DMARC, access rights, delete default users and pages. For projects with 54-FZ, ensure fiscalization is configured via OFD provider.

Deliverables

• Fully configured server for 1C-Bitrix with MySQL, PHP, nginx optimization.
• Installed and activated license of the required edition.
• SSL certificate, email settings, cron and backups.
• Documentation: all configuration parameters, access credentials, cron tasks.
• Content manager training: how to log into admin panel, add products, upload images.
• Post-installation support for 30 days — consultations on settings.

Why Trust Professionals with Installation?

Incorrect installation means lost time and money. We've seen projects where a store on "Start" couldn't handle 50 visitors because innodb_buffer_pool_size wasn't configured. After migrating to VPS with correct configuration, the site "flew". Incorrect configuration can cost 30,000 RUB monthly due to excessive resource consumption. You get a ready-made architecture that scales. Order turnkey 1C-Bitrix installation — get a reliable platform for business growth. Contact us for a free consultation: we'll calculate the cost and time for your project. Over 7 years of experience, 120+ Bitrix projects implemented, including highload stores with million-item catalogs. Get in touch — we'll help configure Bitrix for your project.