Every week, managers spend up to 4 hours manually exporting reports from Bitrix24. The head of sales expects a funnel summary — but data has to be gathered from Excel, CRM, and email. We automate this process: we set up a direct ETL pipeline from Bitrix24 into your BI system. Dashboards update automatically, saving significant time and resources for a sales department. Typical savings: $2,000 per month in manual reporting time.
Problems We Solve
Manual data export and transformation. One request from a manager takes 1–2 hours to prepare. If breakdown by managers or periods is needed — another hour. Typical loss: up to 4 hours per week per manager. We automate the entire process, achieving 80% reduction in reporting time.
Limitations of the built-in BI connector. It is available in the "Professional" plan and above, but has a fixed set of fields — no custom fields, no derived metrics. Updates are once per hour. This is insufficient for real-time dashboards. For Bitrix24 Power BI integration, we recommend REST API.
Complexity with custom data. Custom fields in Bitrix24 have technical IDs (UF_CRM_1678901234), and stage/assignee IDs need to be replaced with names. During transformation, we convert everything to a readable format and compute required metrics.
Incremental Loading: How It Works
Incremental loading is key to performance. Instead of fetching all records each time, we only request those with DATE_MODIFY later than the last successful sync. For the initial load, we do a full fetch with pagination of 50 records (REST API limit). Typical volume: up to 50,000 deals per sync, execution time under 5 minutes. Incremental loading is 10x faster than full loading for large data volumes.
Why REST API Over BI Connector?
Let's compare export methods for Bitrix24 BI connector:
| Method | Flexibility | Speed | Update | Cost |
|---|---|---|---|---|
| REST API | Full control: any fields, any transformations | Depends on volume (up to 2 requests/sec) | Configurable (from 5 minutes) | Free, but requires developer |
| BI connector | Only fixed set of fields | Instant for available datasets | Once per hour | Included in Professional plan |
| Webhooks | Only events, no history | Instant | Real-time | Free |
REST API gives 3x more available fields than the BI connector and allows building any reports. We use it in 80% of projects.
ETL Pipeline: Extract, Transform, Load
Extract. A PHP (8.1+) script calls methods crm.deal.list, crm.lead.list, crm.activity.list. We use date-modified filter — only updated records are fetched. Error handling: retry on timeouts, logging.
Transform. Raw data contains IDs, dates in different formats, and multiple fields. At this stage:
- Replace stage/assignee IDs with names from reference tables;
- Normalize dates to ISO format;
- Unfold multiple fields (e.g., contacts) into separate rows;
- Compute derived metrics: deal duration, stage-to-stage conversion.
Load. Transformed data is written to PostgreSQL or ClickHouse. Table structure: fact tables (deals, activities) and reference tables (managers, stages). For deleted records — periodic ID reconciliation.
Example pipeline configuration via cron
# Every hour: incremental load deals 0 * * * * php /path/etl/deals.php --incremental >> /var/log/etl.log 2>&1 # Every Sunday at 3:00: full sync of reference tables 0 3 * * 0 php /path/etl/dictionaries.php --full >> /var/log/etl.log 2>&1 How to Set Up Export in 5 Steps
- Audit of current reports and data sources.
- Selection of the optimal export method (REST API / BI connector / combination).
- Development of ETL pipeline with incremental loading and error handling.
- Design of warehouse structure for required reports.
- Connection of BI system and creation of basic dashboards.
Update Frequency
The pipeline runs on a schedule: cron, Airflow, or Bitrix24's built-in scheduler. Typical frequency is every 30–60 minutes for operational dashboards, once per day for analytics. We configure failure monitoring and notifications via Telegram/Slack.
Typical Dashboards and Metrics
| Dashboard | Metrics |
|---|---|
| Sales funnel | Stage conversion, average deal size, deal duration |
| Manager activity | Calls, emails, meetings per period |
| Plan vs fact | Revenue plan fulfillment by department |
These sales metrics dashboards provide real-time insights.
What's Included
- Audit of current reports and data sources
- Selection of optimal export method (REST API / BI connector / combination)
- Development of ETL pipeline with incremental loading and error handling
- Warehouse structure design for required reports
- Sync schedule and monitoring setup
- BI system connection and basic dashboard creation
- Field mapping and data structure documentation
- Team training (optional)
- 30-day post-launch support
Timelines and Guarantees
A standard project takes 5 to 15 business days. Pricing is individually assessed after audit. We guarantee stable pipeline operation: automatic retries on failure, monitoring, configuration backups. Our experience includes over 50 Bitrix24 integrations, including with 1C, CommerceML, and REST API. Significant cost savings after implementation — over $2,000 per month for mid-size sales teams.
Checklist of Common Mistakes
- Ignoring deleted records (need ID reconciliation)
- Exceeding REST API limits (need request queue)
- No monitoring: failures can go unnoticed
- Skipping custom fields in transformation
From Bitrix24 documentation: "REST API allows exporting up to 50 records per request, but with pagination you can get up to 50,000 records per sync."
Request a consultation — we'll assess your Bitrix24 data export to BI scenario within a day and provide a roadmap. Write to us, and your Bitrix24 data will speak the language of your business.