fredag 19 oktober 2012

Sales Pipeline report

The whole concept Sales Pipeline was a bit fuzzy for me not very long ago, especially in CRM. There are no obvious places that the sales pipeline stages get set when you look at the Opportunity form. Unfortunately it gets worse when you start looking at the Sales Pipeline Report

I found it quite strange that the sales pipeline stage was a string field on the opportunity form, well not ON the form per se but among the fields in the entity, that is supposed to be set by a workflow for example. I didn't see the logic with this field. Why would you want a string field for set names, I found it more reasonable to have a multiple choice field where the step names would be pre determined. It would be obvious that it doesn't matter at all.

The funny thing with this field is that in CRM 4.0, the only thing it really does is showing the pipeline stage on the lower right corner of the opportunity form, that's it. It has no other relevance to anything at all. In CRM 2011 that field is the default horizontal category of the Sales Pipeline chart, which makes this even more confusing since the Sales Pipeline report is available in CRM 2011 too.

Now, what is the "problem" with the Sales Pipeline report? The problem is that the Sales Pipeline report doesn't use the stepname field at all. The Sales Pipeline report pipeline stepnames is taken from the phase names of the workflow that has run on the opportunity. Further, the sales process that you filter the report on is the name of the workflow that has run on the opportunity. But it gets even a bit more complicated, to count the workflow as a sales process, the workflow needs to have phases in it.

I will try to make this a bit more comprehensive. The Sales Pipeline report has a filter option that is called "Sales process". This is actually any workflow that has phases in it and is run on any opportunity. Further, the pipeline step names are the names of these phases in the workflow. That doesn't seem too complicated.
There is of course another thing that makes this a bit of a problem. The sales pipeline report doesn't sort out opportunities, it sorts out the workflows that has been active. This means that if you have run a workflow several times on an opportunity, for example if the workflows exits and you restart it manually. When you do this a new instance of that workflow is stored in the CRM database, which is completely understandable since you want to be able to see what workflows has been active on an object.

The Sales Pipeline report sees this and in the scope of the report every workflow that has been active is a valid record that should be shown on the report. The result is that you have an opportunity that has a calculated value of €100000 and you have run the sales workflow five times on that opportunity for whatever reason, the shown value on the report is €500000, which will make the opportunity value of the company very good. It will however mess up the total result of the company since even if they win the opportunity the value will only be a fifth of the calculated value of what have been reported in the pipeline.

This is not much of a problem if you are having a sales process that doesn't fork or almost always runs in a straight line and never has to restarts.  It does however mess up your reports if you would have a split sales process workflow or if you restart the sales workflow for some reason, in our case we needed to do this to be able to change the sales person responsible of the opportunity so that the new responsible would get tasks created.

I started to sketch up a solution where old workflows would be deleted which would keep the Sales Pipeline report correct but it would delete the workflows so the history would be lost. The final solution to this problem was to set up a paralell workflow which only would keep track of the sales pipeline steps which kept the sales process in a neat single straight flow based on the stepname field giving it the correct phase in the pipeline report.

You can probably edit the pipeline report to match something else than the phase name of the workflow but the data sets for the report is in no way simple so I considered it to consume too much time for this problem.

Rickard Norström
Developer at CRM-Konsulterna  

Inga kommentarer:

Skicka en kommentar