Bitrix24Care

BI Builder datasets: companies and contacts

Datasets contain information on leads, deals, companies, and other items. Each item has multiple datasets that automatically include both standard and custom fields. Use these datasets to create charts and analyze business performance indicators.

In this article, we'll explore datasets for companies and contacts, how to blend them, and how to create charts.

Datasets:

Examples:


Datasets

To start analyzing data on a dashboard, select a dataset and create a chart based on it.

Company — crm_company: Includes basic information about companies, such as their names, industry, details, and responsible users.

Company — crm_company

Field Description Field type
ID Company ID Number
DATE_CREATE Company creation date and time Date
DATE_MODIFY Company change date and time Date
CREATED_BY_ID ID of the user who created the company Number
CREATED_BY ID and name of the user who created the company String
CREATED_BY_NAME Name of the user who created the company String
MODIFY_BY_ID ID of the user who last modified the company Number
MODIFIED_BY_NAME Name of the user who last modified the company String
MODIFIED_BY ID and name of the user who last modified the company String
ASSIGNED_BY_ID ID of the user who was assigned as responsible for the company Number
ASSIGNED_BY ID and name of the user who was assigned as responsible for the company String
ASSIGNED_BY_NAME Name of the user who was assigned as responsible for the company String
OPENED Shows whether the company is available to all employees ("Y") or not ("N") String
TITLE Company name String
ADDRESS_1 Address 1 String
ADDRESS_2 Address 2 String
ADDRESS_CITY City String
ADDRESS_POSTAL_CODE Zip/Postal code Number
ADDRESS_REGION State/Territory/Region String
ADDRESS_PROVINCE Area/District/Municipality String
ADDRESS_COUNTRY Country String
ADDRESS_COUNTRY_CODE Country code String
BANKING_DETAILS Banking details String
COMMENTS Comments String
COMPANY_TYPE_ID Company type ID String
COMPANY_TYPE_NAME Company type name String
COMPANY_TYPE Company type ID and name String
INDUSTRY_ID Industry ID String
INDUSTRY_NAME Industry name String
INDUSTRY Industry ID and name String
REVENUE Annual revenue Number
CURRENCY_ID Currency String
EMPLOYESS Number of employees String
LEAD_ID ID of the lead linked to the company Number
ORIGINATOR_ID External system String
ORIGIN_ID External identifier String
ORIGIN_VERSION Original version String
IS_MY_COMPANY My company property String
UTM_SOURCE Ad source String
UTM_MEDIUM Ad medium String
UTM_CAMPAIGN Ad campaign String
UTM_CONTENT Ad content String
UTM_TERM Ad term String
PHONE Phone number String
WEB URL String
EMAIL Email String
IM Messengers String

Company: custom fields — crm_company_uf: Contains data from custom fields you created, for example, customer categories, additional contact information, and others. This information can be added to your dashboards.

Company: custom fields — crm_company_uf

Field Description Field type
COMPANY_ID Company ID Number
DATE_CREATE Company creation date and time Date
UF_LOGO Company logo String
UF_CRM_"field ID" Custom field value. The field ID is assigned during creation in the format UF_CRM_123456789. String
UF_STAMP Company stamp String
UF_DIRECTOR_SIGN CEO's signature String
UF_ACCOUNTANT_SIGN Chief Accountant's signature String

Contact — crm_contact: Includes basic information from standard contact fields. Learn who created the contact, who is responsible for it, and which company it is linked to.

Contact — crm_contact

Field Description Field type
ID Contact ID Number
DATE_CREATE Contact creation date and time Date
DATE_MODIFY Contact change date and time Date
CREATED_BY_ID ID of the user who created the contact Number
CREATED_BY ID and name of the user who created the contact String
CREATED_BY_NAME Name of the user who created the contact String
MODIFY_BY_ID ID of the user who last modified the contact Number
MODIFIED_BY_NAME Name of the user who last modified the contact String
MODIFIED_BY ID and name of the user who last modified the contact String
ASSIGNED_BY_ID ID of the user who was assigned as responsible for the contact Number
ASSIGNED_BY ID and name of the user who was assigned as responsible for the contact String
ASSIGNED_BY_NAME Name of the user who was assigned as responsible for this contact String
OPENED Shows whether the contact is available to all employees ("Y") or not ("N") String
COMPANY_ID Company ID Number
SOURCE_ID Source ID String
SOURCE_DESCRIPTION Source description String
NAME First name String
LAST_NAME Last name String
SECOND_NAME Second name String
POST Position String
ADDRESS_1 Address 1 String
ADDRESS_2 Address 2 String
ADDRESS_CITY City String
ADDRESS_POSTAL_CODE Zip/Postal code Number
ADDRESS_REGION State/Territory/Region String
ADDRESS_PROVINCE Area/District/Municipality String
ADDRESS_COUNTRY Country String
ADDRESS_COUNTRY_CODE Country code String
COMMENTS Comments String
LEAD_ID ID of the lead linked with the contact Number
EXPORT Shows whether the contact can be exported ("Y") or not ("N") String
TYPE_ID Type ID (e.g., "CLIENT") String
ORIGINATOR_ID External system String
ORIGIN_ID External identifier String
ORIGIN_VERSION Original version String
BIRTHDATE Date of birth Date
HONORIFIC Salutation code (e.g., "HNR_EN_1") String
FACE_ID faceid ID Number
PHONE Phone number String
WEB URL String
EMAIL Email String
IM Messengers String
UTM_SOURCE Ad source String
UTM_MEDIUM Ad medium String
UTM_CAMPAIGN Ad campaign String
UTM_CONTENT Ad content String
UTM_TERM Ad term String

Contact: custom fields — crm_contact_uf: Contains data from custom fields you created, for example, the last call date, client status, and others. This information can be added to your dashboards.

Contact: custom fields — crm_contact_uf

Field Description Field type
CONTACT_ID Contact ID Number
DATE_CREATE Contact creation date and time Date
UF_CRM_"field ID" Custom field value. The field ID is assigned during creation in the format UF_CRM_123456789. String

How to create a chart

Use datasets to create charts and monitor employee performance. For example, the charts can show which companies your employees work with and who they communicate with.

Go to the BI Builder and follow these steps:

  1. Open the Charts tab.
  2. Click + Chart.
  3. Select the dataset from the list. If you don't see the one you need, click Add a dataset to create a new one.
    How to create a dataset
  4. Choose a format for showing the data: big number, table, line chart, or others.
  5. Click Create new chart.

How to blend datasets

Combine datasets to include both standard and custom company fields in your report.

Go to the BI Builder and do the following:

  1. Go to SQL > SQL Lab.
  2. Select the bitrix24 scheme.
  3. Enter the SQL query and click Run.

The new set includes information from the fields that you specified in the SQL query. You can save it as a dataset to create a chart for the dashboard.
Create a chart based on a virtual dataset

If you're blending datasets for the first time, refer to the sample query.

Example of an SQL query

Here's an SQL query to extract data from two datasets: companies (crm_company) and company custom fields (crm_company_uf). You can copy it and see how it works. Replace UF_CRM_"field ID" with your custom field before running the query.

SELECT 
    crm_company.TITLE AS "Company name",
    crm_company.ASSIGNED_BY_NAME AS "Responsible person",
    crm_company_uf.UF_CRM_1730983962844 AS "Custom field",
    crm_company.PHONE AS "Company phone number",
    crm_company.EMAIL AS "Company email address",
    crm_company.WEB AS "Company website"
FROM 
    crm_company
INNER JOIN 
    crm_company_uf ON crm_company.ID = crm_company_uf.COMPANY_ID
WHERE 
    crm_company_uf.UF_CRM_1730983962844 IS NOT NULL AND crm_company_uf.UF_CRM_1730983962844 <> ''

SELECT: Chooses which data to extract. For example, the SELECT crm_company.TITLE AS "Company name" part does the following:

  • Selects the company name TITLE from the crm_company dataset
  • Assigns it the name Company name
  • Displays the result in a separate column

FROM: Specifies the main table for the query, such as the crm_company table containing company information.

INNER JOIN: Connects datasets and bends information in them. The query crm_company_uf ON crm_company.ID = crm_company_uf.COMPANY_ID connects two datasets by company ID.

WHERE: Filters the query results to show the rows where the custom field is not empty.


In brief

  • Datasets contain information on leads, deals, companies, and other items.

  • Each item has multiple datasets that automatically include both standard and custom fields.

  • There are four datasets for companies and contacts: companies (crm_company), company custom fields (crm_company_uf), contacts (crm_contact), and contact custom fields (crm_contact_uf).

  • Use datasets to create charts and monitor employee performance. For example, the charts can show which companies your employees work with and who they communicate with.

  • Blend datasets to combine data from both standard and custom fields of contacts and companies in your report. To do this, write an SQL query, save the result as a dataset, and create a chart.

Was this information helpful?
Integration specialist assistance
That's not what I'm looking for
Complicated and incomprehensible text
The information is outdated
It's too short. I need more information
I don't like the way this tool works
Go to Bitrix24
Don't have an account? Create for free