We’ve come to part 3/3 in the Smarter Sales Process article trilogy. In the earlier posts we talked about customizing the lead qualification process and using calculated fields for opportunity estimated revenue, to get more out of Dynamics CRM 2015 than what the standard sales related functionality offers. To close things off, let’s have a look at how we can gain more insight into the data in our sales opportunity pipeline and particularly how it has developed over a period of time.
Capture the Sales Pipeline Trend
With the data that Dynamics CRM collects about sales opportunities we can easily draw charts about how many deals were won or lost at any given time, presenting these as a monthly trend of both estimated and actual revenue. It’s also very simple to visualize the current contents of our sales pipeline by looking at the open opportunity records via standard visualizations like the… well, pipeline chart, obviously!
What we can’t do quite so easily is to present how the sales pipeline has developed over time. For example, has the number of opportunities in a particular stage of the sales process gone up/down, or how the total estimated revenue from open opportunities is developing. An average CRM user may not understand why such a visualization wouldn’t be included in the application by default, but for a system customizer that knows the data model and behavior of Dynamics CRM this should be fairly obvious. The fundamental difference between closed opportunities and open opportunities is that for the former we have a permanent record of when they were closed and with what values, whereas the records that are currently in an open status represent transient data. It will change over time, based on the future actions that CRM users will take.
So, what’s the problem with such data? The fact that an open opportunity may have been open last week or even ten weeks ago makes it impossible for us to draw a chart that would show a weekly number of records, since only a single record exists in the database, even though it might need to appear in the bar for each week in a chart showing the size of the pipeline on a weekly level. While technically we would have the information needed to project the number of opportunities that have been open at a given time by looking at their creation date, this would be a more complex exercise than what the ASP.NET charts in Dynamics CRM allow us to draw (although I wouldn’t be surprised if CRM Chart Guy would prove me wrong on this one).
At the end of the day CRM is an operational system focused on managing individual records and transactions, which means it doesn’t bother archiving copies of records in their historical state. Sure, we have the audit log that will keep a record of the individual changes to tracked fields, but that’s data which isn’t accessible for reporting. But the question to ask is: if we wanted to capture such historical data for our analysis purposes, could we do that with Dynamics CRM? Sure we could! In fact, already back in 2008 when CRM didn’t yet have a built-in auditing capability, CRM MVP Guy Riddle showed us how the use of custom entities and workflows allowed us to build our very own audit log feature to capture changes for record field values.
Sure, our use case here is a bit different, since we’re not looking to only capture entries on when a record changes. To provide us visibility into how the sales pipeline has developed over time, we would need to capture a snapshot of the pipeline status at predetermined intervals.
Scheduling Snapshots of CRM Data
One of my favorite features in CRM 2015 version is Rollup Fields, which I’ve already covered in a number of earlier posts on this blog (including the gotchas you need to be aware of). This feature also comes in handy if we want to build a custom snapshot entity to store the count or sum of records related to it. In this scenario for monitoring sales pipeline development, which I presented in my MSDynamicsWorld.com webcast “A Non-Developer’s Guide to Smarter Sales Processes in Microsoft Dynamics CRM 2015”, what we’ll do is make a 1:N relationship between our custom Snapshot entity and the opportunity entity. This in turn will allow us to create Rollup Fields that will summarize the count and revenue of the related opportunities onto the Snapshot record. By having a snapshot per each stage of our sales process, we will get the attributes needed for drawing the kind of chart shown above, to visualize the trend of opportunity count and estimated revenue development per week.
The detailed steps for the required customizations can be found from the following SlideShare presentation:
The one missing ingredient that we still need to think about is how to automate the capture of these snapshots. What Dynamics CRM still doesn’t offer out-of-the-box is the ability to schedule recurring workflow processes in an easy way, to perform an automated task every X days. Luckily there are workarounds for scheduling such bulk data processing tasks with using nothing but the CRM platform, and one of the best solution’s I’ve come across is the Scheduling recurring Dynamics CRM workflows with FetchXML solution from Lucas Alexander. I’ve already shown you how to use this solution for monitoring Rollup Field Values with workflows and the same logic can be applied in this scenario, too. Only this time we don’t send a weekly email blast to CRM users, rather we’ll just create new snapshot records to store the opportunity count and total estimated revenue per sales stage.
Alright, that concludes my Smarter Sales Process for CRM 2015 series, at least for now. As mentioned at the start, do check out part 1 and part 2, as well as the YouTube recording of the live demos if you’re interested for more details on the topic. Hopefully these examples have given you some new ideas on what kind of solutions you can build with the Dynamics CRM 2015 customization tools. If you’ve got any thoughts on what kind of no-code customization scenarios you’d be interested in seeing in the Surviving CRM blog in the future, please feel free to leave a comment!
Hi Jukka
By coincidence I actually recently blogged about a new feature in SQL Server called temporal tables coming in SQL Server 2016: https://rule30.wordpress.com/2015/06/04/temporal-tables-coming-in-sql-server-2016/ that solves exactly this problem.
A Temporal Table will track all data movement in a given SQL Server table and provides syntax for querying the data at any given point in time
I know this would obviously be a more SQL Server centric solution but it would really be a case of just switching in on and writing a report.
Might raise some questions about MS support however as it certainly requires a DDL change within the database.
Cheers
Thanks for the tip, Simon! Sounds like a very useful feature to be introduced in SQL Server and I hope there will eventually be some features developed also in Microsoft’s business applications that will leverage this upcoming capability. Looking at Dynamics CRM, historically there hasn’t been a very aggressive pace at which these type of features from related products would have been incorporated into CRM platform features, but in the brave new cloud world maybe things will develop much faster now. Having improvements announced already for better Full Text Search gives some hope that we may not always have to live with the traditional constraints of CRM being focused only on managing structured data (vs. unstructured document and message content) or showing only the current state of business records.
Can you please provide additional details on how to setup the filter on a subgrid when I am using on-premise crm 2015. I can not get this to work.
Maureen, the Opportunities subgrid on the Process Stage entity form should work with the default settings once you add it onto the form. Set the data source as “Only Related Records”, entity as “Opportunities (Sales Stage)” (the relationship you’ve created when adding the Process Stage lookup field onto the Opportunity entity) and default view to “Open Opportunities”. If you’re not seeing the related records in the subgrid, then check that you have a workflow in place to populate the aforementioned lookup field when the processid or stageid fields of the Opportunity record change (i.e. when the BPF stage is updated).
Hi Jukka,
Thanks for posting this nice solution. I need a help about this solution.
I try to implement this to our CRM Online (2015). I can not make relation on rollup field Estimated Revenue with Opportunities. When I created a field and set to rollup, I can’t select “Opportunities (Regarding)”. It is not listed. Only a short list of Entity listed (Activities-Regarding, Appointments-regarding…)
Did I miss something when Entity (Process Stage) be created?
Could you please give additional details about Entity/rollup field creating?
Thanks alot
Murat
Murat, you’ll need to add a 1:N relationship between the Process Stage entity and opportunity. This is only implied on slide 10 with the arrow between the entities, but essentially this is how rollup fields in CRM work: they can calculate data from related child entities (or from a self-referential hierarchy of the same entity). What this also means is that you really do need the workflow process to link all opportunities with the correct Process Stage record in slide 13, since otherwise there wouldn’t be any data to roll up.
Thanks alot Jukka,
Now I could create the relation. I will now try to create a workflow to link the opportunities with the Process Stage.
best regards
Murat
Jukka,
Can you please give some details about how to use/apply the open source code from Alexander?
I couldn’t figure out how to use the code for snapshotting..?
Thanks
Murat
Murat, the original blog post from Lucas seems to have moved to a different URL. I suggest you review it for the details on how the solution works: Scheduling recurring Dynamics CRM workflows with FetchXML.
Once you have installed this solution to your CRM, you’ll need to construct the FetchXML query shown on slide 18 of my presentation, to retrieve the target records for a workflow, meaning the Process Stage records. Then you need to build the workflow that you wish to run in a recurring fashion, which is the creation of a Snapshot record copy of the values found on the Process Stage record at the time of the workflow execution, as shown on slide 19. The final step puts these two together, where you use the solution from Lucas by creating a Scheduled Process record that runs your aforementioned workflow for all the records found by the FetchXML query at an interval you specify on the Scheduled Process record fields (slide 16).
Jukka,
Thanks for sharing the information.
Does this solution apply to CRM 2015 Online..? I could create the plugin but I am not able to register it.
Netiher to production nor to sandbox..!
The Custom plugin is not listed in plugin registration tool under the assembly.!
Best regards
Murat
Jukka,
I could register to CRM Online 2015. Dismiss above comment please…
regards
Murat
Hi Jukka,
Thanks for this article. Read it a while back and today I had time to sit down and test it. I can report that it works equally well on 2015 Online and On-Prem. – including Lucas Alexanders 2013 solution.
Now I want to find a way to display WHICH opportunities moved in the pipeline between each snapshot. And then something about Days spent in Sales Phase. Any hints?
Marianne, I’m actually building a solution lie this for a customer right now, so this might be the topic of a future blog post. In short, to catch the movement of specific opportunities between the phases you would need to produce “snapshots” of the individual opportunities, rather than the total pipeline from each phase. In the current project I’m creating an “Opportunity Log” record each time the opportunity receives a positive sales gate decision from the sales directors. I have the fields “previous phase” and “next phase” on this record, as well as a “days in stage” field for storing the pipeline velocity metrics.
You’d need to trigger the creation of this Log entry from the phase change event (in my case there’s a set of gate decision fields to be filled at the same time), create the Log as a child entity of the parent opportunity and then copy the values from this parent in the workflow process. Getting the previous & next phase information requires a bit of creativity in the design, but you could utilize a pattern like the one I’ve showed in an earlier blog post. As for the “days in stage”, this comes from a calculated field on the opportunity that is automatically populated from the formula “today’s date minus the last sales phase update” (you’ll need to populate the latter field in your worklfow process). At the time of the Log record creation I simply copy the current value from the opportunity to the child record in the workflow.
The fields on the Opportunity Log records will essentially give you the ability to analyze several metrics about your pipeline, like “average days spent in phase X”, via the standard CRM charts. In addition, you’ll have the history of each sales opportunity available for the users to view in a much more easier to access format than the generic Audit Log data (which isn’t accessible in Advanced Find or charts).
Thank you for the post. It would be perfect for my organisation. Unfortunately, I am stuck at loading LucasWorkflowTools.dll Assembly to register the plugin. Is anyone else having this problem? I am using CRM Online 2016 Update 1.
The error message I get is:
Inner Exception: System.NotSupportedException: An attempt was made to load an assembly from a network location which would have caused the assembly to be sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable CAS policy by default, so this load may be dangerous. If this load is not intended to sandbox the assembly, please enable the loadFromRemoteSources switch. See http://go.microsoft.com/fwlink/?LinkId=155569 for more information.
Lana, I see that Lucas has already responded to you in his original blog post, so I’ll just link it here for future reference: Scheduling recurring Dynamics CRM workflows with FetchXML.
Thank you Jukka, I did not see Lucas’s response. I will try his solution.
Hi Jukka, the solution works for me perfectly when I only have one record created in Scheduled Process entity. When I have 2 Scheduled process records created with different frequency and next run date, the solution seem to generate lots of records per each record returned by the FetchXML query. Am I doing something wrong? I would like to scheduled more than one process on recurring basis.