1

I want to create a report on Stage conversion percentage of opportunity per sales person. Like if a salesperson have 10 Opps in first stage and only 6 forward to second stage then from 6 only 4 forward to third stage, only one go to fourth stage. So I would like to calculate sales person stage conversion and see in which stage he is losing more opps so we can help him on that stage.

I created matrix report on opportunity history table and grouped by Opportunity owner, from stage ---To stage. Created a count field on opportunity to count 1 and create a custom formula Opportunity.Count__c:SUM / PARENTGROUPVAL(Opportunity.Count__c:SUM, FROM_OPPORTUNITY_STAGE_NAME, COLUMN_GRAND_SUMMARY) But it is showing wrong calculation. Please find below screen shots:

enter image description here

enter image description here

Please help me to get correct formula.

Regards

0

As you move from column to column, you have to use the value of the previous column compared to the current column. If using percent, it's 100*(Count at New Stage - Count at Prev Stage)/(Count at Prev Stage). You have to do that for each column.

You'll then see at which stage your users were having difficulty converting to the next stage. I've seen this report done in a number of different ways and this is just one variation. Some only look at comparing each stage to close-won.

Edit

It sticks in my mind that you may need to use (PrevValue - CurrentValue)/(PrevValue). Also, If you want them to sum to 100 percent, then you're barking up the wrong tree. You're looking at a totally different number or percentage here. If the last column of data is from your next to last stage to closed-won and you're comparing with it to having began for example, at prospecting (that column being zero since none convert from a previous value or 100% convert from zero depending on how you view it), you'd need to factor this into account. The last column before your grand total would be (closed-won - next to last stage)/(next to last stage).

The Grand Total would need to factor in the total opportunities converted to closed-won, probably by multiplying everything by (closed-won)/(Prospecting), assuming every Opportunity begins at Prospecting. I hope that makes sense. Otherwise, the final number can't be expected to total to 100% unless you somehow normalize your data. I've not run any numbers, but off-hand, I think that's the approach you'd need to take. If what you really want to know is which stage doesn't get converted, then a guage should tell you or any kind of a bar chart. You'll readily see the peak.

Let's see if we can create a simple example:

Opp Records by User
Prospecting  |  Estimating  |  Negotiating  |   Closed-Won  |  Total
    4        |     2        |     2         |      1        |    1
    6        |     4        |     3         |      2        |    2
    2        |     2        |     2         |      1        |    1
--------------------------------------------------------------------
    12       |     8        |     6         |      4        |    30


Stage Conversions:
Prospecting  |  Estimating  |  Negotiating  |   Closed-Won  |  Total
    0        |    66%       |     75%       |      66%      |   207%

Stage Conversions:
Prospecting  |  Estimating  |  Negotiating  |   Closed-Won  |  Total
    0        |    66%       |     75%       |      66%      |   207%
   40%       |    27%       |     20%       |      13%      |   100%
^-- Factor for Total Records
    0        |    18%       |     15%       |      9%       |    42%

If you remove the 12 records in prospecting that account for 40% and don't include that in the total, you get numbers that look more like the following:

Stage Conversions:
Prospecting  |  Estimating  |  Negotiating  |   Closed-Won  |  Total
    0        |    66%       |     75%       |      66%      |   207%
    0%       |    44%       |     33%       |      22%      |   100%
^-- Factor for Total Records
    0        |    29%       |     25%       |      15%      |    69%

As you can see, with this type of report, the numbers just aren't inclined to come out to 100% for you. They also show you that you can manipulate numbers to show pretty much whatever you want them to show. Be careful of how you interpret reports.

3
  • You are certainly right but I am not able to put formula in report. It's giving me wrong calculation. @crmprogdev – Russel baker 1 May 9 '16 at 5:09
  • Yes, You are right. I tried to create this report from many ways but did not get exact result. however, I know that this report I can get from Opportunity history. But unfortunately I can't create any formula field to take From stage and To stage and report CSF have some limitation so we can not use it to get desire result. @crmprogdev Thanks for your help. – Russel baker 1 May 15 '16 at 7:46
  • I have seen others create custom fields to hold dates where each stage change occurs, then another custom field for each one that uses a formula to calculate the number of days in each stage that is held in Opportunity History. There are a number of posts on this in the forum. Perhaps this approach will provide the results you seek while providing additional insight (stuck opps, days to conversion, etc). This would also allow calculation of opps lost from stage to stage. BTW, you may need to bucket opps with days in each stage for this calculation rather than doing prev values. – crmprogdev May 15 '16 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.