Track Opportunity Sales Stage History

I was building out an environment for a sales demo last week where one of the requirements was that the prospect wanted to only track sales stages for opportunities. They also wanted to be able to track the amount of time an opportunity had been sitting in any particular sales stage. I’ve heard this request before so I had my solution ready, which I will discuss in this article. This solution is like an audit solution that can be used to track other things as well, for example if you wanted to track ownership of records like cases. (In my environment I auto assign cases to teams first, and managers or users belonging to that team can assign cases to other or themselves. Read the article on how to set that up here.) This solution is using a real time workflows, but can also work by using Power Automate.

Solution

I am going to create a new entity in Dynamics 365 that will capture the information of the sales stage related to an opportunity. I will create a real-time workflow that will create a record each time the business process flow of the opportunity changes to a different stage. In that record it will capture the previous stage, the current sales stage, when it entered that sales stage and when it exited the sales stage. The duration field will automatically calculate the total duration the opportunity spent in a sales stage.

The first thing we will have to do is build a new entity, which I am going to call ‘new_salesstagehistory’. The display name for this entity is ‘Sales Stage History’. I made this an organization owned entity but obviously you can choose user or team ownership if that better fits your needs. I added several fields to the entity:

The opportunity field is needed to tie the record back to the opportunity. We will also need to create a lookup field to this new entity (called Sales Stage History Lookup) on the opportunity entity. This will us allow to update the ‘active’ Sales Stage History record once the opportunity moves to the next stage. The duration field is a calculated field where we will track the actual amount of time (in hours) an opportunity has been in a particular sales stage.

Once the entity has been created, the fields have been added to the new entity and the lookup field to the new Sales Stage History entity has been added to the opportunity entity, we can start building the real-time workflow. I called this workflow ‘Opportunity Stage History’. The entity I am going to run this off is obviously the opportunity entity. I select to start this workflow after a record field changes and I pick the ‘pipeline’ field on the opportunity. (this field is automatically updated to the category of the related stage (which can be found in the business process flow) each time an opportunity moves to another stage in the business process flow. If you execute the workflow to run as the user of the record, make sure that you give them rights to the new entity (otherwise the workflow will not be able to create the records.) NOTE: Workflows that get executed as ‘The user who made changes to the the record’ uses the security that is assigned to that user!

The first step in the workflow is a condition step. This is where we will have two sets of conditions. One condition is where the lookup field to the ‘Sales Stage History’ entity on the opportunity record is blank, and the other where it has data and where we’re going to make sure that the stage in the Sales Stage History doesn’t equal the sales stage in the pipeline stage field. As noted before, the data in the pipeline field comes from the category related to the stage in the business process flow. The same category can be used for different stages, which is why I put this condition in my workflow. Below is an image of the logic of the workflow that I will be putting together for this scenario.

I am going to start with the fist condition, which is if the Sales Stage History Lookup field on the opportunity is blank. This means this is the first Sales Stage History record that we will need to create in this step. The step under the condition will be to create a Sales Stage History Record, where we will hardcode the ‘Previous’ field to New Record, we’ll set the ‘New’ field to the Pipeline field on the opportunity and we’ll set the from (start date and time this sales stage was entered) to the execution time of the process. Lastly we need to make sure we tie the record back to the opportunity. The next step is to update the Sales Stage History Lookup field on the opportunity with the record we created in the previous step.
Under the second condition (mentioned above) we will update fields of the existing Sales Stage History record that is populated on the opportunity record.

The only field we have to update is the ‘To’ field and we will enter the process execution time in this field. Then we will add a ‘create’ step where we will create a new Sales Stage History Record. In the ‘Previous’ field we will enter data from the ‘New’ field of the Sales Stage History lookup field on the opportunity. We will enter data from the ‘Pipeline Phase’ field on the opportunity in the ‘New’ field and we will enter the process execution time in the ‘From’ field. Lastly we need to enter the opportunity in the opportunity lookup field as shown in the image below. The last step will be where we will update the ‘Sales Stage History Lookup’ field on the Opportunity with the record we created in the previous step.

Once the workflow is created, make sure to activate it and get ready to test it! I hope you enjoyed this post! Be sure to check in again next week for a new topic or subscribe here to never miss another post!