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:
- Leads — crm_lead
- Custom fields of leads — crm_lead_uf
- Lead history — crm_lead_status_history
- Products in lead — crm_lead_product_row
Examples:
Datasets
To start analyzing data on a dashboard, select a dataset and create a chart based on it.
Leads — crm_lead: Includes basic information from standard lead fields. Learn how many leads were created in a month and which employees worked with them.
Leads — 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 |
| Type identifier and email | String | |
| IM | Type identifier and messengers | String |
Custom fields of leads — 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.
Custom fields of leads — 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 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 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 |
Products in lead — crm_lead_product_row: Provides information on products in leads from standard fields. See the products items added to leads, their quantity, and amount.
Products in lead — 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:
- Open the Charts tab.
- Click + Chart.
- Select the dataset from the list.
- Choose a format for showing the data: big number, table, line chart, or others.
- 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:
- Go to SQL > SQL Lab.
- Select the bitrix24 scheme.
- 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 custom fields of leads (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
IDfrom thecrm_leaddataset - 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
IDfrom thecrm_leaddataset - 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_dealconnects thecrm_leadtable to thecrm_dealtableON crm_lead.ID = crm_deal.LEAD_IDadds a connection condition: the lead ID in thecrm_lead (ID)table must match the lead ID in thecrm_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 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.