Bitrix24 and Tableau Integration
Director asks for a sales funnel report for the quarter. Manager exports data from B24 to Excel, cleans duplicates, builds a pivot table, manually calculates conversion by stage. Two days later the data is obsolete — need to repeat. B24 has built-in analytics, but for complex slices (cohort analysis, cross-reports by channels and managers, LTV dynamics), it's insufficient. Tableau solves this, but data still needs to be delivered.
Solution Architecture
Tableau doesn't connect to B24 directly — there's no native connector. The link works through an intermediate storage:
B24 REST API → ETL script → PostgreSQL/MySQL → Tableau Desktop/Server → dashboard
An ETL script (in Python, Node.js, or PHP) extracts data from B24 via REST API, transforms it, and loads into a relational database. Tableau connects to this database like any other data source.
Alternative — Tableau Web Data Connector (WDC). This is a JavaScript application that Tableau calls to get data. WDC calls middleware, which requests data from B24 REST API and returns it in a format Tableau understands. Good for small volumes, but for serious analytics a intermediate database is more reliable.
What Data We Extract
B24 REST API provides access to main CRM entities and more:
| Entity | API Method | Key Fields |
|---|---|---|
| Leads | crm.lead.list |
Status, source, UTM tags, sum, responsible, creation date |
| Deals | crm.deal.list |
Stage, funnel, sum, close date, contact, company |
| Contacts | crm.contact.list |
Name, company, type, source |
| Companies | crm.company.list |
Name, industry, revenue, type |
| Product rows | crm.item.productrow.list |
Product, quantity, price, discount, deal link |
| Activities | crm.activity.list |
Type (call, email, meeting), date, duration |
| Tasks | tasks.task.list |
Status, assignee, deadline, time spent |
| Telephony | voximplant.statistic.get |
Duration, type, recording, CRM link |
Custom fields (UF_CRM_*) are exported automatically — ETL script requests field list via crm.deal.fields and includes them in export.
ETL Process
Extract. Script calls REST API with pagination. B24 returns maximum 50 records per request for most methods. With 10,000 deals — 200 requests. At 2 requests per second limit — 100 seconds. For speed, use batch requests (up to 50 calls in one batch), reducing time tenfold.
Incremental export: after first full load, script requests only records changed after last run (filter >DATE_MODIFY). This cuts data volume and execution time.
Transform. Data from B24 comes in specific format:
- Deal stages — as codes (
C1:NEW,C1:WON). Script substitutes human-readable names from reference (crm.status.list). - Multiple fields (phones, emails) — arrays of objects. Script expands into separate columns or rows.
- Dates — ISO 8601 format with portal timezone. Convert to UTC.
- Relations (contact → company → deal) — denormalized: each deal gets contact and company fields.
Load. Data is written to PostgreSQL (or MySQL). Table structure:
-
crm_deals— deals with denormalized fields -
crm_leads— leads -
crm_activities— activities with deal links -
crm_products— product rows -
dim_stages— stage reference -
dim_sources— source reference -
dim_users— employees (responsible, managers)
"Fact + reference" structure is standard for BI. Tableau builds relations between tables and allows any slicing.
Tableau Dashboards
Typical dashboards built on B24 data:
Sales funnel. Conversion between stages, average deal cycle, sum by stages. Filters: period, manager, funnel, source. Horizontal funnel with absolute numbers and conversion percentages.
Manager analytics. Deal count, closed sum, average check, lead-to-deal conversion, average time per stage. Manager ranking by KPI. Drill-down to specific deals.
Lead sources. ROI by channel: UTM tags → leads → deals → sum. Channel comparison by conversion and average check. Dynamics by month.
Cohort analysis. Leads grouped by creation month, tracking conversion to deal and payment in subsequent months. Shows if lead quality and sales team performance improve.
Activity. Calls, emails, meetings by day and manager. Activity correlation with closed deals. Pattern detection: how many touchpoints needed to close a deal.
Automatic Updates
ETL script runs on schedule:
- Hourly — incremental export of changed records. Dashboards in Tableau Server auto-update per linked schedule for extract refresh.
- Nightly — full reload for consistency guarantee.
- Manual — button in middleware for forced update before important meeting.
Tableau Server/Online supports scheduled extract refresh — automatic data update from connected database at specified time.
What We Deploy
- ETL script for extracting data from B24 REST API into relational database
- Intermediate database structure optimized for BI analytics
- Tableau connection to intermediate database
- Set of standard dashboards: funnel, managers, sources, cohorts, activity
- Automatic update schedule configuration
- Incremental export to minimize B24 API load