Setting up data sending to Yandex.DataLens from 1C-Bitrix

Configuring Data Export to Yandex DataLens from 1C-Bitrix

DataLens is a BI tool from Yandex with the ability to build dashboards based on data from various sources: PostgreSQL, ClickHouse, Google Sheets, Yandex.Metrica. For an e-commerce store on Bitrix, DataLens enables combining data from the Bitrix database (orders, products, users) with data from Metrica (behavior, traffic sources) and building reports that cannot be obtained from either system alone.

Direct PostgreSQL Connection to DataLens

The cleanest approach: DataLens connects directly to the Bitrix database through a PostgreSQL connector. No additional development required—DataLens queries data directly from tables b_sale_order, b_catalog_price, b_iblock_element and others.

Requirements: the database must be accessible from Yandex cloud (white IP or VPN). In DataLens settings: "Create connection → PostgreSQL" → specify host, port (5433 by default for Bitrix), database name, user and password.

For security, create a separate PostgreSQL user with read-only permissions on the required tables:

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;

Data Mart: Intermediate Table

Direct queries to Bitrix tables in DataLens are inefficient: the b_sale_order table can contain millions of rows, and DataLens executes queries each time the dashboard is opened. It's better to create a data mart—an aggregated table that updates on schedule:

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);

Update the data mart through a Bitrix agent every hour:

// In init.php
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;
}

Connecting Yandex.Metrica as a Second Source

DataLens can connect to Yandex.Metrica directly—this is the key advantage for data consolidation. In DataLens: "Create connection → Yandex.Metrica" → enter counter ID and OAuth token.

After creating two connections (PostgreSQL + Metrica) in DataLens, you can create a dataset with a JOIN query: for example, link order_date from the orders data mart with the date from Metrica and see revenue and traffic on the same chart—it immediately becomes clear which advertising campaigns drive actual revenue, not just clicks.

Exporting Events to ClickHouse through Bitrix

For more sophisticated analytics, events from Bitrix (adding to cart, checkout initiation, abandoned orders) are sent to ClickHouse through Kafka or directly through ClickHouse's HTTP API. DataLens connects to ClickHouse and builds sales funnels in real-time. This is already the level of EnterprisAnalytics, but architecturally Bitrix is ready for it—event handlers for the sale module and background sending through a queue are needed.