Win Rate % by stage?
I also looked around here and it seems the "Power of One (https://success.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F9300000001pFV&fId=0D53000001GwrJW)" technique is highly recommended but there are no step-by-step explanation on how to implement it. Could someone help me out please?
and this is my CSF...
with an extra checkbox and a bit of dataloading, this should be straightforward.
* Create a checkbox "Past Stage 2"
* Use the report of Opportunity History that you created and include the Opportunity Id, then export it.
* Use Excel to filter for all Opps that have past stage 2.
* Mark the checkbox for said Opps.
* WIth the power of 1, now create a report on Opportunity. Group by "Past Stage 2". Create a formula and divide by group level vs. row count all.
Please find similler issue :
Here are some screen-shots for creating the formula:
The syntax for the custom summary formula in your Opportunity History report would be:
Filter the report by To Stage equals (any Stages that are greater than "Stage 2")
Thanks Steve! That helped a lot.
I'm using the "greater than stage 2" rule but it looks like what I have as stage 2 isn't actually set up as stage 2. Where in SFDC do I change the order of my stages?
My stages go from Identified Opportunity (stage 1) -> Business Case Complete (stage 2) -> Final Negotiations (stage 3) -> Verbal Agreement (stage 4) -> Won - Add to OB (stage 5) -> Closed
I only want to track the win % of deals that meet or exceed Stage 2.
Now that I'm doing some data integrity... it looks like the summary formula you gave me didn't work.
I see an opportunity in my report that hasn't been won yet but is included.
Did I embed the formula incorrectly? (via a seperate column)
I am really trying but it's not working :(
Also when you said "[apply the power of 1] for every one of your objects", what is an example of an object?
I think I may have set up the Power of 1 field incorrectly. I only did one for the Opportunity page (not per "object")
Did I not set it up correctly?
The formula you want to specify in the formula letter is just this...
...and that's it - nothing else.
Your CSF looks right...i think you just need to revisit your Powerof1 formula.
And in response to your question about the filter criteria, I was just using an example, but the "Greater Than" idea should work...you want to include all Opportunities, Won, Lost and pending...because you're saying show me the ratio of "Won" (By Stage) to "All" (By Stage)
The Opportunity Stages make up a business process within the Opportunity. Salesforce gives you some out-of-the box selections, but most people add or make changes to them.
You can go into the Stage field in the Opportunity object and click the Reorder button to move the selections up or down in the picklist, but training the Users on how that process works also goes along with making any changes - you want Users to select the correct Stage so your data is accurate.
A typical (or not so typical) Stage progression in an Opportunity might look like this, with the Probability % tied to each one increaing as you move up in Stage:
Prospecting Qualification Initial Meeting Needs Analysis Proposal Negotiation/Pricing Closed Won (or Closed Lost)
Closed Won Opps have a probablity of 100%, Closed Lost = 0%, and everything else is somewhere in the middle.
Thanks SteveD for the quick response - apologies I was slow to mine. I triple checked my formulas and they seem to be set up correctly. I also do have them inputted per object.
How would I apply the Power of 1 field to each report? I didn't do that... and I still see multiple of the same opportunities which skews the data. Any thoughts?
That looks great...I left mine as a Summary report - not nearly as much sizzle as yours.
Thank you SteveM and SteveD!
I went with SteveD's route as it worked for me and was more straightforward for a beginner like me. But SteveM's chart looks great... I will bookmark this solution and see if I can recreate that in the near future.
Thanks again all!
Actually, just to make sure. Could someone double check my logic / results?
I got the 42% Win Rate but I'm seeing more than 1 of the same Opportunities listed in my report. How do I apply the "Power of 1" Technique?
Did you create a custom summary formula in your report (for Win Rate) or is that a formula field on the Opportunity ?
Power of 1 added to report:
Custom Summary Formula for Win Rate:
I still feel like a beginner :( only started this job for a few months...
I think the problem with my report is that I don't know how to apply the "Power of 1" - I just drag it to the report and hope for the best? Thoughts?
I think you should try to tackle SteveMo's matrix report.
Thanks SteveD... that makes me feel a little better :)
SteveMo; how do I get the "current stage" field? I only see From Stage and To Stage. I think the reason I'm seeing duplicate opportunities is because I'm using the To Stage.
I went a little Justin Timberlake and "brought the Sexy back" to Opportunity History Reports
Tips & Tricks: Getting more out of Opportunity History Reports
So I set it up but I got an error message from the custom summary formula. Do you think it's because my Opportunity ID is different from yours?
Use the Insert button and the Custom Summary Menu to get the correct API Name of the Field from your own SF org and Report
I did the SUM of the Power of 1 divided by PARENTGROUPVAL From Stage Column Grand Summary.
However, SFDC didn't recognize the summary field which it had automated for me. Any ideas?
Opportunity.PowerOf1__c:SUM / PARENTGROUPVAL(Opportunity.PowerOf1__c:SUM, FROM_OPPORTUNITY_STAGE_NAME, COLUMN_GRAND_SUMMARY)
I forgot to add the "SUM" part before.
This is what I have currently:
PARENTGROUPVAL(Opportunity.Power_of_1__c:SUM, FROM_OPPORTUNITY_STAGE_NAME, COLUMN_GRAND_SUMMARY)
However, on my dashboard, I can't select the Win Rate % to show... just the SUM:
What am I doing wrong?
It should look like this:
X-Axis CSF (Win Rate %)
Y-Axis Opportunity Owner
Group By Stage
I don't have the custom Stage field that you're using, so I can't test it...but if that doesn't work, try it with Stage as the Y-Axis and grouping by Owner
I'm in a Lightning Now workshop today + tomorrow, so I'm not gonna be online much. SteveD can guide you in for landing.
Thanks both Steves!
SteveD, as seen from the last screenshot in my last post, I actually can't edit the X or Y Axis. This was the problem I had. Here's what happens when I add the Win Rate % in for a graph chart:
But I want a Leaderboard-style chart, not a graph chart. But my Leaderboard chart only shows the Quantity field, and not the Win % field.
Select Table as your Component Type
Put Win Rate % in the X-Axis
Put Owner in the Y-Axis
Also, I'm only able to change the axises for bar graphs... but I want to have a Leaderboard style chart. Is this possible? I feel like it must be
I think you might be clicking in the wrong place - try clicking on the arrow to the right of where it says Win Rate %, just under the Formatting tab in the Editor screen.
See below my Formatting tab:
I don't see Win Rate % anywhere?
Chart is still not showing any results for Win Rate %... despite me actually seeing it correctly in the report itself
But the way you have each Axis setup on your report chart looks correct.
You may have the groupings setup incorrectly in your matrix...try moving the Owner field to where you have the PowerOf1 field grouped (and vice-versa).
Do you mean regrouping it like this? This doesn't give me the Win Rate % by Rep, unfortunately.
If I move the PowerOf1 field to the left side, the Win Rate % doesn't work anymore.
Man, my report works but I just can't figure it out on the Dashboard...
I've got my report like this...
...my report Chart like this
...and my Dash component like this
...but I don't get any data
If I move "From Stage" from a Column grouping in the report to a Row grouping, then I get data...but I lose my Win Rate % CSF
Is that the same thing that's happening to you ?
Are you saying you were able to see the Win Rate % when you created it in the Dashboard?
You may need to create one report with the row and column groupings the way you want to display them for your Users - without a report chart...then create a second report that uses different groupings - and includes a report chart, and use that version of the report to drive your dashboard component.
You want the Formatting tab of your dashboard Component Editor to look like this...with the correct column names:
Sorry for the late response - I attempted this and failed and left it for another time... which is today but still can't figure it out. My Component Editor matches yours now but even when Win Rate % is selected as a column, I don't see anything on the dashboard.
As a refresher, I got my report working with the Win Rate % but can't get the Win Rate % to show on my dashboard as it does on my report:
Not sure I follow what you meant by creating two separate reports?
But...bottom line...the dashboard won't work until you setup the row and column groupings correctly.
You need to group your source report by Owner and by Win Rate %, then add a chart to that report that uses those groupings...then go back into the dashboard component and specify the columns that the Table needs to display.
How do I group my source report by Win Rate % without using Current Stage? Doesn't the Win Rate % formula have to have those fields in order to process the formula?
And I assume the Power of 1 / record count field is needed too.
But the report chart would need to be grouped by both Win Rate and Owner - you just want this, right ?
Could you please show me how your report looks like? and your Win Rate formula?
This is what mine looks like, and I can't recreate the Win Rate % if I remove any rows/columns so I'm not sure what I'm doing wrong:
Win Rate formula:
This is driving me crazy! :(
Thanks for the help.
and this is my CSF...
Changing my report from Matrix to Summary made a huge difference.
Ron's win rate is 9%, which I don't believe is true. I'm trying to calculate the Win Rate for opportunities after the "Business Case Complete" stage.
My report filters:
His opportunities within last 6 months:
He closed all his opportunities within the last 6 months... so shouldn't his win rate be 100%?
But I don't think you need anything more than this if you just want to show the ratio of "Won" Opportunities to all Opportunities owned...
WON:SUM / Opportunity.PowerOf1__c:SUM This formula calculation will be displayed in the report at the level you select: Grouping 1: Owner
Thanks so much!! I will stop bothering you now. Have a great weekend!
I am looking to get all opportunities by stage that were in pipeline during a past period say Q1, and find the win rate as of the end of that quarter. It should also include any open opportunities that had no stage change during that period.
So, I have taken all opportunities that were created during or earlier than Q1, and closed during or later than Q1.
We want Win rate to be calculated as sum of won opportunities out of all the opportunities (open/closed) during that time.
Win Rate %:
I believe this would tell how many of those opportunities are won at present, but we want to know the win rate as of end of Q1. Appreciate any help on how we can get that.
Opportunity Count is my power of one field.