Data Science: How to download Bitrix CRM with Python

Igor Comune
3 min readNov 13, 2022

--

Bitrix24
  1. The reposity of this post
  2. My Github
  3. For all my posts

Intro

In my actual work, another problem showed up. Our managers requested a series of (Power BI) dashboards with our data from Bitrix24.

We find this link (it’s in BR-portuguese). In this link they show you how to connect PBI to the Data Base. But we found 2 problems:

  1. The base document is in Russian;
  2. PBI Service (online) can not auto update the data;

There was another problem, we wanted to bring the entire Bitrix24 data base to our dataware house.

Solution: Download the Data Base using Python

In order to acomplish this task it was required to investigate how the base file given by Bitrix24 download the data:

The following query is in M Language:

= (bx24_entity_name as text) as table =>
let
response = Web.Contents(
"https://" & #"Server address",
[
RelativePath = "bitrix/tools/biconnector/pbi.php",
Query = [
//token = #"Secret key",
table = bx24_entity_name
],
Content = Json.FromValue([dateRange = [
startDate = Date.AddYears(Date.From(DateTime.LocalNow()),-1),
endDate = Date.From(DateTime.LocalNow())],
key = #"Secret key"
]
)
]
),
jd = Json.Document(response)
in
Table.FromRows(
List.Skip(jd),//data: >=1 row (index 0 based)
List.First(jd)//header: 0 row (index 0 based)
)

This code requires 3 variables:

  1. #”Server Adress”: businessname.bitrix24.com.br
  2. #”Secret Key”: a_very_long_key
  3. #”bx_entity_name”: Entity name is the name of the table.

Entities (link in BR-Portuguese):

  • crm_deal — deals
  • crm_lead — leads
  • crm_deal_uf — custom deals fields
  • crm_lead_uf — custom leads fields
  • crm_deal_stage_history —deals history
  • crm_lead_status_history — leads history
  • telephony_call — calls
  • crm_company — companies
  • crm_contact — contacts
  • socialnetwork_group — projects
  • crm_activity — activity

To understand which information each of them brings, you can access this link.

Translating M Language to Python

M Language is not my strongest skill, so before trying to directly write a Python request, I used Insomnia, but you can use Postman or whatever program or web app you want to test the requests.

To make it easier for you:

“https://#”Server address”.bitrix24.com.br/bitrix/tools/biconnector/pbi.php?token=#”Secret key”&table=bx24_entity_name”

Just replace the variables with the values and that it is, you can already send a request and the return will be a JSON.

In Python:

import pandas as pd
from requests import get

# insert here the entity names (tables) that you want to download
tables = ['crm_deal', 'crm_lead', 'crm_deal_uf']

def ta_bitrix(tables):
# iterating of the variable "tables"

for table in tables:
# printing the table name
print(f'Table: {table}')

# request, replace # with the variable
req = get(f'https://##########.bitrix24.com.br/bitrix/tools/biconnector/pbi.php?token=############&table={table}')

# transforming the JSON into a Pandas Data Frame
df = pd.DataFrame(req.json())

# renaming the columns using the first row
df.columns = df.iloc[0]

# dropping the first line, which was used to rename the columns
df = df.drop(index=df.index[0], axis=0)

# returning the table as csv
df.to_csv(f'{table}.csv')

# execute the code
ta_bitrix(tables)

If you want, you access my GitHub account, here’s the link.

From this code, you can decide whether you upload it to your data warehouse or you keep it locally.

Thank-you,

Igor Comune | LinkedIn

--

--

Igor Comune
Igor Comune

Written by Igor Comune

An "under construction" Data Scientist with a love for numbers and analysis!