Lots of Bitrix24 users would like to get Deal status history data to their Power BI reports. Unfortunately official Bitrix24 REST-Api doesn't allow to download this information, you can only get a current Deal status. There are some custom solutions, for example you can save every status change event somewhere (for example to Bitrix4 Lists or to the custom field in the Deal itself), but these types of solutions will contain only new data (which was appeared after the setting said solution).
We provide the universal solution which allows you to get the data for all time. But it is mandatory to invite IT-Solution system account to your portal and enable the administration access to that account. (If you can't set that account as administrator, you can at least enable CRM Deals access for it).
To get Deals status history information follow the instruction below:
1. Invite to your Bitrix24 portal an employee using email powerbi@it-solution.ru either as a partner (this option will not affect the max count of portal users and administrators), or as a regular employee (this will affect the max count of portal users and administrators and you must enable the administration access to that user after the registration).
2. Wait until the registration is complete (to this moment the process is done manually, but it will be automated in future). Our team will complete the registration (work time 12PM - 8PM +3GMT (Moscow time)), and you will get a message on your Bitrix24 portal (the message would be received by the user who had send an invite).
If you fired that account at some point, please hire it back and give it administration access or grant Bitrix24 partner's rights.
3. If you added IT-Solution account as Bitrix24 partner, don't do this step.
If you added IT-Solution account as a regular employee, assign administration permissions to it.
If the two-step autorization is enabled on your portal, IT-Solution account will not be able to login.
4. Reload the Power BI Integration application page on your portal. Open the Download settings tab and make sure that Load Deal status history checkmark appeared.
5. Enable Load Deal status history and clear the cache if you have it. Click Save button.
Deal status data would be gathered every day at 11 PM +3 GMT (Moscow time) since the moment you enabled settings at the step 5. The uploading process ends later at night, so it's recommended to refresh your data in Power BI at the morning after.
6. Do that step the next morning after you completed the step 5, otherwise you'll get only one empty string, because the IT-Solution system account didn't complete gathering the data from your portal yet.
Open your template using Power BI Desktop, click Transform data-New Source-Blank Query
7. Insert the GetData query (don't forget to put "=" first) and type "history_stages" parameter in the brackets. Your end result should be: =GetData("history_stages")
8. Click ENTER to see the list of records. If there is just one string, please contact us for the support.
9. Click Convert to table on the Transform tab.
10. Do not change anything and just click OK at the pop-window that will appear.
11. Click the button with arrows at the column header. Disable "Use original column name as prefix" checkmark. Click Load more link. Click Ok after all that.
12. Convert columns "date_start" and "date_end" to the Date/Time/Timezone type by right clicking the column header and selecting Change Type->Date/Time/Timezone
13. Double click to the current name of your query to rename the table as you please. For this instruction the "Deal status history" name will be used.
14. Click Close and Apply Then go to the Model tab, click Manage relationships
15. If there were any unwanted relationship automatically created, you can disable those.
16. Create the new relationship between the tables "Deals" and "Deal status history" (the one was created at step 13)
Fields description:
id - unique identificator of Deals status history changes
deal_id - unique identificator of the Deal which had changed it's status to the stage_name
date_start - time when Deal's status was changed to the new status stage_name
date_end - time when Deal's status left the status stage_name
stage_name - non-unique name of the Deal's current status when the time was date_start.
stage_id - unique identificator of Deal's status. If this value is empty that means you don't have any deals with that specific status at the current moment (for example, the status was renamed, or deleted, or the whole deal category was removed or blocked because of the payment plan change, etc.)
Deal status history is included to the free records limit of the application!
Done and done! Now you can create reports based on the Deals status history data, as shown on the example below. There you can see one deal, and how long has it been in each status. You can also create a report showing an average time that a deal spends in each status.