Bitrix24Care

BI Builder datasets: leads

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 leads, 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.

Lead — crm_lead: Includes basic information from standard lead fields. Learn how many leads were created in a month and which employees worked with them.

Lead — crm_lead

Field Description Field type
ID Unique key Number
DATE_CREATE Lead creation date and time Date
CREATED_BY_ID ID of the user who created the lead Number
CREATED_BY ID and name of the user who created the lead String
DATE_MODIFY Lead change date and time Date
MODIFY_BY_ID ID of the user who last updated the lead Number
MODIFIED_BY ID and name of the user who last updated the lead String
MODIFIED_BY_NAME Name of the user who last modified the lead String
ASSIGNED_BY_ID ID of the user who was assigned as responsible for the lead Number
ASSIGNED_BY_NAME Name of the user who was assigned as responsible for the lead String
ASSIGNED_BY ID and name of the user who was assigned as responsible for the lead String
ASSIGNED_BY_DEPARTMENT Responsible person's department String
COMPANY_ID Company ID Number
COMPANY Company name and ID String
COMPANY_NAME Company name String
COMPANY_TITLE Company name from the lead field String
CONTACT_ID Contact ID Number
CONTACT_NAME Contact name String
CONTACT Contact name and ID String
OPENED Shows whether the lead is available to all employees ("Y") or not ("N") String
TITLE Lead title String
NAME First name in the lead form String
LAST_NAME Last name in the lead form String
SECOND_NAME Second name in the lead form String
FULL_NAME Full name in the lead form String
POST Position String
STATUS_ID Status ID String
STATUS_NAME Status name String
STATUS Status name and ID String
STATUS_DESCRIPTION Status description (reasons for moving to the status) String
STATUS_SEMANTIC_ID Status type identifier: "F": "(failed) - processed unsuccessfully", "S": "(success) - processed successfully", "P": "(processing) - lead in processing" String
STATUS_SEMANTIC Status type ("Success" or "Failure") String
IS_RETURN_CUSTOMER Shows whether the lead is repeat ("Y") or not ("N") String
OPPORTUNITY Expected amount Number
CURRENCY_ID Currency String
COMMENTS Comments in the lead form String
SOURCE_ID Source ID String
SOURCE_NAME Source name String
SOURCE Source name and ID String
SOURCE_DESCRIPTION Source description String
ORIGINATOR_ID External system where the lead was created String
ORIGIN_ID Lead ID in the external system where the lead was created String
UTM_SOURCE Ad source, UTM_SOURCE in the lead form String
UTM_MEDIUM Ad medium, UTM_MEDIUM in the lead form String
UTM_CAMPAIGN Ad campaign, UTM_CAMPAIGN in the lead form String
UTM_CONTENT Ad content, UTM_CONTENT in the lead form String
UTM_TERM Ad term, UTM_TERM in the lead form String
CRM_PRODUCT_ID Identifiers of products in the lead form String
CRM_PRODUCT CRM product name and ID (one or multiple) String
CRM_PRODUCT_COUNT Product quantity as seen in the lead form String
CRM_PRODUCT_NAME Product name in the lead form String
ADDRESS_1 Address in the lead form String
ADDRESS_2 Address in the lead form String
ADDRESS_CITY City String
ADDRESS_POSTAL_CODE Zip/Postal code String
ADDRESS_REGION Region String
ADDRESS_PROVINCE Area String
ADDRESS_COUNTRY Country String
ADDRESS_COUNTRY_CODE Country code String
DATE_CLOSED Lead closing date and time Date
BIRTHDATE Date of birth Date
HONORIFIC Salutation String
PHONE Type identifier and phone number String
WEB Type identifier and URL String
EMAIL Type identifier and email String
IM Type identifier and messengers String

Lead: custom fields — crm_lead_uf: Contains data from custom fields you created, for example, delivery time or order number. This information can be added to your dashboards.

Lead: custom fields — crm_lead_uf

Field Description Field type
LEAD_ID Lead ID Number
DATE_MODIFY Lead change date Date
DATE_CREATE Lead creation date Date
DATE_CLOSED Lead closing date Date
UF_CRM_"field ID" Custom field value. The field ID is assigned during creation in the format UF_CRM_123456789. String

Lead: status history — crm_lead_status_history: Contains data on lead status change from standard fields. Use it to analyze a lead's journey from creation to the final stage.

Lead: status history — crm_lead_status_history

Field Description Field type
ID Unique key of the record Number
TYPE_ID Record type: 1 for entity creation, 2 for transfer to the intermediate stage, and 3 for transfer to the final stage Number
LEAD_ID Lead ID Number
DATE_CREATE Record creation date and time Date
ASSIGNED_BY_ID ID of the user who was assigned as responsible for the lead Number
ASSIGNED_BY ID and name of the user who was assigned as responsible for the lead String
ASSIGNED_BY_NAME Name of the user who was assigned as responsible for the lead String
ASSIGNED_BY_DEPARTMENT Responsible person's department String
STATUS_SEMANTIC_ID Status type identifier: "F" (failed), "S" (success), or "P" (in progress) String
STATUS_SEMANTIC Status type: success, failed, or in progress String
STATUS_ID Status ID String
STATUS Status name and ID String
STATUS_NAME Status name String

Lead: products — crm_lead_product_row: Provides information on products in leads from standard fields. See the products items added to leads, their quantity, and amount.

Lead: products — crm_lead_product_row

Field Description Field type
ID Unique key of the record Number
LEAD_ID Lead ID Number
LEAD_DATE_MODIFY Lead change date Date
LEAD_DATE_CREATE Lead creation date Date
LEAD_DATE_CLOSED Lead closing date Date
PRODUCT Product name and ID String
PRODUCT_ID Product ID Number
PRODUCT_NAME Product name String
PRICE Product price Number
PRICE_EXCLUSIVE Discounted price without tax Number
PRICE_NETTO Price without discount or tax Number
PRICE_BRUTTO Price with tax, but without discount Number
QUANTITY Product quantity Number
DISCOUNT_TYPE Discount name and ID String
DISCOUNT_TYPE_ID Discount ID Number
DISCOUNT_TYPE_NAME Discount name String
DISCOUNT_RATE Discount percentage value Number
DISCOUNT_SUM Discount amount Number
TAX_RATE Tax percentage rate Number
TAX_INCLUDED Shows whether tax is included in price ("Y") or not ("N") String
CUSTOMIZED Shows whether the product was modified manually after being added to the lead ("Y") or not ("N") String
MEASURE Unit of measurement name and ID String
MEASURE_CODE Unit of measurement ID Number
MEASURE_NAME Unit of measurement name String
SORT Sorting order Number

How to create a chart

Use datasets to create charts and check business performance. Go to the BI Builder and follow these steps:

  1. Open the Charts tab.
  2. Click + Chart.
  3. Select the dataset from the list.
  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 lead 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 these sample queries.

Example of an SQL query: blend leads and custom fields

Here's an SQL query to extract data from two datasets: leads (crm_lead) and lead custom fields (crm_lead_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_lead.ID AS "Lead ID",
   crm_lead.TITLE AS "Lead name",
   crm_lead.DATE_CREATE AS "Lead creation date",
   crm_lead.OPPORTUNITY AS "Lead amount",
   crm_lead_uf.UF_CRM_1701433962427 AS "Custom field"
FROM 
   crm_lead
INNER JOIN 
   crm_lead_uf ON crm_lead.ID = crm_lead_uf.LEAD_ID
WHERE 
   crm_lead_uf.UF_CRM_1701433962427 IS NOT NULL 
   AND crm_lead_uf.UF_CRM_1701433962427 <> ''

SELECT: Chooses which data to extract. For example, the SELECT crm_lead query.ID AS "Lead ID" part does the following:

  • Selects the lead ID from the crm_lead dataset
  • Assigns it the name Lead ID
  • Displays the result in a separate column

FROM: Specifies the main table for the query, such as the crm_lead table containing lead information.

INNER JOIN: Connects different tables to combine their data. For example, crm_lead_uf ON crm_lead.ID = crm_lead_uf.LEAD_ID links standard and custom fields by lead ID.

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

Example of an SQL query: view leads converted to deals

To see how many leads have turned into deals, extract data from two datasets: leads (crm_lead) and deals (crm_deal). Here's an SQL query that shows converted leads and their corresponding deals from the past 30 days. You can copy it and see how it works.

SELECT
   crm_lead.ID AS "Lead ID",
   crm_lead.TITLE AS "Lead name",
   crm_lead.DATE_CREATE AS "Lead creation date",
   crm_deal.ID AS "Deal ID",
   crm_deal.TITLE AS "Deal name",
   crm_deal.DATE_CREATE AS "Deal creation date"
FROM
   crm_lead
INNER JOIN
   crm_deal ON crm_lead.ID = crm_deal.LEAD_ID
WHERE
   crm_deal.DATE_CREATE >= date_add('day', -30, current_date)

SELECT: Chooses which data to extract. For example, the SELECT crm_lead query.ID AS "Lead ID" part does the following:

  • Selects the lead ID from the crm_lead dataset
  • Assigns it the name Lead ID
  • Displays the result in a separate column

FROM: Specifies the main table for the query, such as the crm_lead table containing lead information.

INNER JOIN: Connects different tables to combine their data. For example, crm_lead_uf ON crm_lead.ID = crm_lead_uf.LEAD_ID does the following:

  • INNER JOIN crm_deal connects the crm_lead table to the crm_deal table
  • ON crm_lead.ID = crm_deal.LEAD_ID adds a connection condition: the lead ID in the crm_lead (ID) table must match the lead ID in the crm_deal (LEAD_ID) table. This lets you connect each converted lead to its corresponding deal.

WHERE: Filters the query results to meet the specified conditions. For example, crm_deal.DATE_CREATE >= date_add('day', -30, current_date) does the following:

  • Calculates the date 30 days ago from today using the date_add ('day', -30, current_date) function
  • Compares the deal creation date (crm_deal.DATE_CREATE) to the calculated date
  • Selects only deals created in the last 30 days. This shows leads converted into deals in the past month.

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 in leads: leads (crm_lead), custom fields (crm_lead_uf), lead status history (crm_lead_status_history), and products in leads (crm_lead_product_row).

  • Use these datasets to create charts and analyze business performance indicators. Charts can reveal which ads attract customers and which products sell more frequently.

  • Blend datasets to combine data about leads with custom fields. 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