Bitrix24Care

BI Builder datasets: deals

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:

Examples:


Datasets

Deal — 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.

Deal — 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

Deal: custom fields — 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.

Deal: custom fields — 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: stage 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: stage 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

Deal: products — crm_deal_product_row: Provides information on products in deals from standard fields. See the products items added to deals, their quantity, and amount.

Deal: products — 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:

  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 deal 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: deals (crm_deal) and deal custom fields (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 ID from the crm_deal dataset
  • 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), deal custom fields (crm_deal_uf), deal stage 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.

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