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. Charts can reveal which ads attract customers and which products sell more frequently. You can also blend datasets, for example, to combine data about deals with custom fields.
In this article, we'll explore datasets for deals, how to blend them, and how to create charts.
Datasets:
- Deals — crm_deal
- Custom fields of deals — crm_deal_uf
- Deal history — crm_deal_stage_history
- Products in deal — crm_deal_product_row
Examples:
Datasets
Deals — crm_deal: Includes basic information from standard deal fields. Learn how many deals an employee has closed, how quickly, and which deals are still in progress.
Deals — crm_deal
| Field | Description | Field type |
|---|---|---|
| ID | Unique key | Number |
| DATE_CREATE | Deal creation date and time | Date |
| CREATED_BY_ID | ID of the user who created the deal | Number |
| CREATED_BY | ID and name of the user who created the deal | String |
| CREATED_BY_NAME | Name of the user who created the deal | String |
| DATE_MODIFY | Deal change date and time | Date |
| MODIFY_BY_ID | ID of the user who last updated the deal | Number |
| MODIFIED_BY | ID and name of the user who last updated the deal | String |
| MODIFIED_BY_NAME | Name of the user who last modified the deal | String |
| ASSIGNED_BY_ID | ID of the user who was assigned as responsible for the deal | Number |
| ASSIGNED_BY | ID and name of the user who was assigned as responsible for the deal | String |
| ASSIGNED_BY_DEPARTMENT | Responsible person's department | String |
| MOVED_BY_ID | ID of the user who changed the stage | Number |
| MOVED_BY | ID and name of the user who changed the stage | String |
| MOVED_BY_NAME | Name of the user who changed the stage | String |
| MOVED_TIME | Stage change date and time | Date |
| LEAD_ID | ID of the lead converted into the deal | Number |
| COMPANY_NAME | Company name | String |
| COMPANY_ID | Company ID | Number |
| COMPANY | Company name and ID | String |
| CONTACT_ID | Contact ID | Number |
| CONTACT_NAME | Contact name | String |
| CONTACT | Contact name and ID | String |
| OPENED | Shows whether the deal is available to all employees ("Y") or not ("N") | String |
| TITLE | Deal name | String |
| CRM_PRODUCT | CRM product name and ID (one or multiple) | String |
| CATEGORY | Deal pipeline name and ID | String |
| STAGE_ID | Stage ID | String |
| STAGE | Stage name and ID | String |
| STAGE_SEMANTIC_ID | Status type ID: "F": "(failed) - processed unsuccessfully", "S": "(success) - processed successfully", "P": "(processing) - deal in progress" | String |
| STAGE_SEMANTIC | Status type, semantic type description (e.g., "Success", "Failure", "In progress") | String |
| IS_NEW | Shows whether the deal is in the first stage ("Y") or not ("N") | String |
| IS_RECURRING | Shows whether the deal is recurring ("Y") or not ("N") | String |
| IS_RETURN_CUSTOMER | Shows whether the deal is repeat inquiry ("Y") or not ("N") | String |
| CLOSED | Shows whether the deal is in a final stage ("Y") or not ("N") | String |
| TYPE_ID | Deal type | String |
| OPPORTUNITY_ACCOUNT | Amount in reporting currency | Number |
| OPPORTUNITY | Expected amount | Number |
| IS_MANUAL_OPPORTUNITY | Shows whether the deal amount was entered manually ("Y") or not ("N") | String |
| TAX_VALUE | Tax | Number |
| TAX_VALUE_ACCOUNT | Tax in reporting currency | Number |
| CURRENCY_ID | Currency | String |
| ACCOUNT_CURRENCY_ID | Reporting currency | String |
| PROBABILITY | Deal success probability (scoring) | Number |
| COMMENTS | Comments in the deal | String |
| BEGINDATE | Auto completes with the deal creation date | Date |
| CLOSEDATE | Proposed closing date of the deal; by default, it's set 7 days after the deal creation date | Date |
| LOCATION_ID | Customer location | Number |
| SOURCE_ID | Source ID | String |
| SOURCE | Source name and ID | String |
| SOURCE_DESCRIPTION | Source description | String |
| ORIGINATOR_ID | External system where the deal was created | String |
| ORIGIN_ID | Deal ID in the external system where the deal was created | String |
| ADDITIONAL_INFO | Additional information in the deal | String |
| UTM_SOURCE | Ad source, UTM_SOURCE in the deal form | String |
| UTM_MEDIUM | Ad medium, UTM_MEDIUM in the deal form | String |
| UTM_CAMPAIGN | Ad campaign, UTM_CAMPAIGN in the deal form | String |
| UTM_CONTENT | Ad content, UTM_CONTENT in the deal form | String |
| UTM_TERM | Ad term, UTM_TERM in the deal form | String |
| BANK_DETAIL_ID | Bank details ID | Number |
| CRM_PRODUCT_ID | Identifiers of products in the deal form | Number |
| CRM_PRODUCT_COUNT | Product quantity as seen in the deal form | String |
| WEBFORM_ID | CRM form ID | Number |
| WEBFORM_NAME | CRM form name | String |
| WEBFORM | CRM form name and ID | String |
Custom fields of deals — crm_deal_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 deals — crm_deal_uf
| Field | Description | Field type |
|---|---|---|
| DEAL_ID | Deal ID | Number |
| DATE_CREATE | Deal creation date and time | Date |
| CLOSEDATE | Proposed closing date of the deal | Date |
UF_CRM_"field ID"
|
Custom field value. The field ID is assigned during creation in the format UF_CRM_123456789.
|
String |
Deal history — crm_deal_stage_history: Contains data on deal progress from standard fields: date, deal status, responsible users' name, and more. Use it to analyze a deal's journey from start to finish.
Deal history — crm_deal_stage_history
| Field | Description | Field type |
|---|---|---|
| ID | Unique key | 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 |
| DEAL_ID | Deal ID, identifier of the entity in which the stage has changed; in this case, the deal ID | Number |
| DATE_CREATE | Record creation date and time. Date and time of the deal getting on the stage | Date |
| START_DATE | Stage start date; the values of the crm_deal_stage_history.START_DATE and crm_deal.BEGINDATE fields are equal | Date |
| END_DATE | Stage end date; the values of the crm_deal_stage_history.END_DATE and crm_deal.CLOSEDATE fields are equal | Date |
| ASSIGNED_BY_NAME | Name of the user who was assigned as responsible for the deal | String |
| ASSIGNED_BY_ID | ID of the user who was assigned as responsible for the deal | Number |
| ASSIGNED_BY | ID and name of the user who was assigned as responsible for the deal | String |
| ASSIGNED_BY_DEPARTMENT | Responsible person's department | String |
| STAGE_SEMANTIC_ID | Stage type identifier: "F": "(failed) - processed unsuccessfully", "S": "(success) - processed successfully", "P": "(processing) - deal in processing" | String |
| STAGE_SEMANTIC | Stage type: "Failure", "Success", or "In Progress" | String |
| STAGE_ID | Stage ID | String |
| STAGE | Stage ID and name | String |
| STAGE_NAME | Stage name | String |
| CATEGORY_ID | Deal pipeline ID | String |
| CATEGORY_NAME | Deal pipeline name | String |
| CATEGORY | Deal pipeline | String |
Products in deal — crm_deal_product_row: Provides information on products in deals from standard fields. See the products items added to deals, their quantity, and amount.
Products in deal — crm_deal_product_row
| Field | Description | Field type |
|---|---|---|
| ID | Unique key | Number |
| DEAL_ID | Deal ID | Number |
| DEAL_DATE_CREATE | Deal creation time | Date |
| DEAL_CLOSEDATE | Deal completion time | 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 deal ("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 |
| CATEGORY | Deal pipeline name and ID (e.g., [2] Delivery) | String |
| CATEGORY_NAME | Deal pipeline name (e.g., Delivery) | String |
| PARENT | Product section (e.g., Yogurts) | String |
| SUPERPARENT | Parent product section (e.g., Dairy products) | String |
| SUPERSUPERPARENT | Parent of parent product section (e.g., Perishable products) | String |
How to create a chart
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 deal 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 the sample query.
Example of an SQL query
Here's an SQL query to extract data from two datasets: deals (crm_deal) and custom fields of deals (crm_deal_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_deal.ID AS "Deal ID",
crm_deal.TITLE AS "Deal name",
crm_deal.DATE_CREATE AS "Deal creation date",
crm_deal.OPPORTUNITY AS "Deal amount",
crm_deal_uf.UF_CRM_1723105561632 AS "Custom field"
FROM
crm_deal
INNER JOIN
crm_deal_uf ON crm_deal.ID = crm_deal_uf.DEAL_ID
WHERE
crm_deal_uf.UF_CRM_1723105561632 IS NOT NULL AND crm_deal_uf.UF_CRM_1723105561632 <> ''
SELECT: Chooses which data to extract. For example, the SELECT crm_deal query.ID AS "Deal ID" part does the following:
- Selects the deal
IDfrom thecrm_dealdataset - Assigns it the name
Deal ID - Displays the result in a separate column
FROM: Specifies the main table for the query, such as the crm_deal table containing deal information.
INNER JOIN: Connects different tables to combine their data. For example, crm_deal_uf ON crm_deal.ID = crm_deal_uf.DEAL_ID links standard and custom fields by deal 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 in deals: deals (crm_deal), custom fields (crm_deal_uf), deal history (crm_deal_stage_history), and products in deals (crm_deal_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 deals with custom fields. To do this, write an SQL query, save the result as a dataset, and create a chart.