This is literally the ONLY video and explanation that has been any help in figuring this out and getting me closer to be able To determine how often each source channel leads to closed won opportunities by stage My only question is about how to calculate the percentage of opps who enter at a particular stage and then go through additional stages before they close . Thank you!!!!!!!!!
Hey there. Thanks for the kind words! This is tricky, you could group opportunity history records by Opportunity name and then use the record count sum on each grouping level. This will tell you if the stage changed only once or multiple times before being closed. I'm not sure if that's exactly what you're looking for but I think it'll get you closer.
Thank you! What kind of things would you want to see in an advanced reporting course? Advanced features or more advanced builds showing the steps to create marketing dashboards, etc.
@@Rotive sorry didn't realize you responded! I've seen some interesting things done like row level formulas that reference different rows in the report itself for conversion reporting for example. Thanks again for the video!
i have a question in my opportunity stage when i create this report i see there is a backward change suppose from closed won > Need analysis or Estimate stage, why this is showing and if so how to solve this issue
@Rotive I'm trying to track how long sales reps have their opportunities open. Is there a report anyone can recommend that would show me how to track how long does it take an Opportunity to be created to when it is closed (closed-won or closed lost, doesn't matter). I want it to look like this Opportunity Stage Conversion report but instead of the percentages of the converted states, it shows me the duration. I know there's an option to add Age as a Column, but it's not giving me the information I think I want.
Thanks for a wonderful tutorial to do the conversion history report for opportunities. I want to add the grouping by owner to see the conversion history for each owner. when I add that in the group by owner column, percentage formula doesn’t work in dashboard. Got any advice on how to fix this issue
You're welcome! It's tough to say without seeing it. I would double check the summary formula settings to make sure it's grouping at the level you need. Choose All Summary Levels to be safe and try experimenting with selecting a specific group. The formula in the example uses the ParentGroup in it. You've now created a new Parent Group (Opportunity Owner). If all else fails, you can use the original report and add a dashboard filter to filter the results by owner.
It super useful thanks! Just 1 question, it shows Warning "The report name can't be null." when I was creating the formula. How am I suppose to fix it?
Hi Theresa, Salesforce wants you to name and save the report before you create a formula. Exit the formula builder and make sure you hit the Save button or edit the name in the upper left hand corner.
@@Rotive This is showing the percentages based on the number of opportunities that progressed. Just wondering if we can show the total no. of opportunities in each stage and the number of leads converted to opportunities. Thanks
What would the formula be if I wanted to see the conversion rate from Created to Closed Won overall, regardless of the stages hit in between? This would have to be inclusive of all Closed Won opps, not just those that became Closed Won directly after Create.
Hey! It's very similar actually. You just don't need the Opportunity History report type. You can use a normal Opportunity report type for it. Check out our video on it: th-cam.com/video/Co6g0C5VuLw/w-d-xo.html
How would you report on snapshot data if you wanted to see how many opportunities were in a given stage on a week over week basis? Would you need to create a flow to snapshot opportunities every week? I can't figure out what report type to use here. History doesn't really do the trick, since it excludes opportunities that aren't changing.
Hey, You identified the exact problem with the history report type for this use case. You'll need to create a snapshot report. Custom Object + Flow that runs weekly. You can have the schedule flow run daily and then check if it's a Monday or Friday, etc. and then just end if it's the wrong day.
Another option is to create date fields on the opportunity that automatically update through Flow, when an opportunity goes in and out of a stage. That might be easier than the snapshot report.
How can I set it up such that I'm only looking at those that move forward in the stage process, in order to get stage duration for each stage (presuming only a linear sales process)?
Adding the stage duration field to the report is the first step, but then you'll need to add some filters to exclude any stage changes that are going backwards. If you're not worried about that, just group by From Stage and then include the average calculation for Stage Duration.
This is super helpful! Is it possible to make the same report but on the lead object? I don't see the "From stage" and "To Stage" in the Lead History report type
I think you can, but you may need to setup Lead History Field tracking first by going to Object Manger > Lead > and clicking on History Tracking in the upper right. Then make sure you select Lead Status as one of the fields to track moving forward
Great video! Thank you for a clear and simple explanation. Just wondering, can you recommend a chart that would be able to depict these conversion percents well?
Thank you for the comment! Glad you found it valuable. I would use a bar chart to easily compare the stages visually. That way you can get an intuitive understanding of what at typical conversion is for you and if there are any anomalies.
Thank you for this super useful video, you explained it so well. I am getting this warning while creating the formula - You must select a grouping context to use any report summary function. Can you help me with this?
Sure, It's saying that the function you're using in the formula requires an input that it hasn't been given. You have likely grouped your report by a field and maybe have multiple groups. You need to add the field the report is grouped by to the function for it the work. Does that help?
10 หลายเดือนก่อน
Very valuable information. Thank you for the time and effort you put in making these video's. I have one question: Is there a way to add region information to this report? My company works in different country's and I want to see this data for different country's. Opportunity Report shows by default only opportunity data and not the related account data, were I can find country data.
Hey Rob, great question! You should be able to create a custom report type for opportunity history and opportunities and accounts to give you all fields across those three levels. If that doesn't work for you, you could create a formula field on the Opportunity that references the region or country value on the Account. Creating a report is the best option because it avoids the need to create that extra field.
Great Video. Very clearly explained! I'm trying to build a report that shows the actual Probability (to close won) % has been. I.e. How many opportunities in Quote stage make it to closed one, and not the just making it to the next stage. This is to validate the Probability % currently assigned in salesforce to each Forecast category. The probability %'s assigned were someone's guestimate, and we'd like to see what is currently happening to get to aa more accurate weighted forecast. appreciate any and all guidance!
I actually just created this report for a client! You start with the report in the video but then you can use the Won checkbox on the Opportunity record to count up how many opportunities that were in that stage have made it to Won. I would also filter out any currently Open opportunities to make sure you're not skewing your percentages with deals that just haven't been closed yet.
Hi! I have a question. Is there a way to make an opportunity report that shows last weeks stage and then this weeks stage? My boss wants to see if statuses are being changed week to week. So even if last weeks stage was “lead” and this weeks stage is still “lead” we want to see that. Or if last weeks was “lead” and this week was “proposal issued” we want to see that as well.
Hey Mikka, Try making a report based on Opportunity History. Every time a stage is updated, Salesforce saves a record that shows you want the previous stage was and what the current stage is with a date/time stamp. I'd try building the report based on that. It will show you what's changed in the last week. If that doesn't get you the answer you're looking for, you can consider creating a snapshot report but these are much more complicated.
It's up to you on how you want to filter out those history records. You could include the records that show people moving backwards through the funnel. This certainly does sometimes happen.
Brian, this video is GREAT! Thank you. My only complaint is that I am looking to show progression through stages all the way to Closed/Won. I am sure it's something in the formula or a simple column/layout change, but I've been banging my head against a wall for days trying to get it. For example, the output of this report shows me that 27 Opportunities went from Diagnose to Design, but then I need to go down to the Design subtotal and look for those that went to Procurement, then down to the next grouping to get contract validation, etc. Do you have a video that shows a Closed/Won rate from each stage? Thank you for anything you can offer.
Hey Halcyon14, thanks for the comment! I would change the report filters so the TO Stage was only Closed/Won and keep the groupings as is. That will show you many deals moved from a stage to won.
When I do this, all The percentages show 100% because it is only showing the opps that went from A From stage to Closed/won - therefore they all close/won 100% of the time. I may be misunderstanding!
@@zapotheque4113hey! I know it’s been awhile but were you able to figure this out, I’m trying to do the same thing, but can’t find anything on it, thanks!
Great video! Love the explanation :) One question tho...doesn't this 'double count' single opportunities that may have gone forward a stage, then back, then forward again? (e.g. From A -> B, then B -> A, then again A -> B). This essentially creates 2 history records in the same Opportunity, potentially inflating percentage numbers.
Hey Jonathan, you are absolutely right. This will count every time an opportunity moves from one stage to another. Hopefully you don't have too many opportunities going through he same stage more than once. If you do, you can do some more work to group the records by Opportunity to essentially count the unique opps to get rid of that noise.
Hey Quinn, Yes! You can create a similar report to the one in the video. Just add the Stage Duration field. I believe it represents the number of days the opportunity was in that stage. You can then group by stage to get an average and filter as needed.
Awesome! Clearly articulated and very simple to do. I'd been looking for a video like this everywhere. Thank you for this! 👏
Glad it was helpful! Thanks for the comment. We appreciate it :D
I literally never comment on TH-cam but I just have to say thank you, this is incredibly clear and useful!!!!!!!!!!!!!!!!!
Thank you for the comment! So glad it was valuable for you.
This video was super clear and useful. I immediately gave you a subscribe hoping to get more content like this!
Thanks Mike! Glad you found it helpful. I appreciate the comment.
This was great! Just followed along literally step by step in my salesforce instance
That's awesome!
This is literally the ONLY video and explanation that has been any help in figuring this out and getting me closer to be able
To determine how often each source channel leads to closed won opportunities by stage
My only question is about how to calculate the percentage of opps who enter at a particular stage and then go through additional stages before they close .
Thank you!!!!!!!!!
Hey there. Thanks for the kind words!
This is tricky, you could group opportunity history records by Opportunity name and then use the record count sum on each grouping level. This will tell you if the stage changed only once or multiple times before being closed. I'm not sure if that's exactly what you're looking for but I think it'll get you closer.
This was an awesome video! Currently in the process of building out dashboards for the business and this one will be valued!
Glad it was helpful! Thank you for the comment.
Really excellent Brian!
Thank you very much!
This was so well done, seriously great job! I wish you had an intermediate / advanced reporting course, but just wanted to say thanks!
Thank you! What kind of things would you want to see in an advanced reporting course? Advanced features or more advanced builds showing the steps to create marketing dashboards, etc.
@@Rotive sorry didn't realize you responded! I've seen some interesting things done like row level formulas that reference different rows in the report itself for conversion reporting for example. Thanks again for the video!
Deserves more likes - thank you!
Thanks Greg! You hear that everybody? Smash that like button!
i have a question in my opportunity stage when i create this report i see there is a backward change suppose from closed won > Need analysis or Estimate stage, why this is showing and if so how to solve this issue
Very useful video! Thanks for the clear and concise explanation 🎉
You betcha! Thanks for your feedback!
Thanks for sharing! Nice report! I would even add time period and conversion target comparison!
Thanks for the comment! That's a great idea. Good to see how these conversion rates change over time.
@Rotive I'm trying to track how long sales reps have their opportunities open. Is there a report anyone can recommend that would show me how to track how long does it take an Opportunity to be created to when it is closed (closed-won or closed lost, doesn't matter). I want it to look like this Opportunity Stage Conversion report but instead of the percentages of the converted states, it shows me the duration. I know there's an option to add Age as a Column, but it's not giving me the information I think I want.
Thank you so much for posting this video!
My pleasure! Let us know if there's any other reports you'd like us to create 😊
This was super helpful, well done video also.
Glad you liked it! Let us know if there's anything else you'd like us to cover
Thanks for a wonderful tutorial to do the conversion history report for opportunities. I want to add the grouping by owner to see the conversion history for each owner. when I add that in the group by owner column, percentage formula doesn’t work in dashboard. Got any advice on how to fix this issue
You're welcome! It's tough to say without seeing it. I would double check the summary formula settings to make sure it's grouping at the level you need. Choose All Summary Levels to be safe and try experimenting with selecting a specific group. The formula in the example uses the ParentGroup in it. You've now created a new Parent Group (Opportunity Owner). If all else fails, you can use the original report and add a dashboard filter to filter the results by owner.
It super useful thanks! Just 1 question, it shows Warning
"The report name can't be null." when I was creating the formula. How am I suppose to fix it?
Hi Theresa, Salesforce wants you to name and save the report before you create a formula. Exit the formula builder and make sure you hit the Save button or edit the name in the upper left hand corner.
@@Rotive Thank you so much!
@@Rotive This is showing the percentages based on the number of opportunities that progressed. Just wondering if we can show the total no. of opportunities in each stage and the number of leads converted to opportunities. Thanks
What would the formula be if I wanted to see the conversion rate from Created to Closed Won overall, regardless of the stages hit in between? This would have to be inclusive of all Closed Won opps, not just those that became Closed Won directly after Create.
Hey! It's very similar actually. You just don't need the Opportunity History report type. You can use a normal Opportunity report type for it. Check out our video on it: th-cam.com/video/Co6g0C5VuLw/w-d-xo.html
You sir, are a legend. Subscribed
Appreciate it, Chelsea!
How would you report on snapshot data if you wanted to see how many opportunities were in a given stage on a week over week basis? Would you need to create a flow to snapshot opportunities every week? I can't figure out what report type to use here. History doesn't really do the trick, since it excludes opportunities that aren't changing.
Hey, You identified the exact problem with the history report type for this use case. You'll need to create a snapshot report. Custom Object + Flow that runs weekly. You can have the schedule flow run daily and then check if it's a Monday or Friday, etc. and then just end if it's the wrong day.
Another option is to create date fields on the opportunity that automatically update through Flow, when an opportunity goes in and out of a stage. That might be easier than the snapshot report.
How can I set it up such that I'm only looking at those that move forward in the stage process, in order to get stage duration for each stage (presuming only a linear sales process)?
Adding the stage duration field to the report is the first step, but then you'll need to add some filters to exclude any stage changes that are going backwards. If you're not worried about that, just group by From Stage and then include the average calculation for Stage Duration.
This is super helpful! Is it possible to make the same report but on the lead object? I don't see the "From stage" and "To Stage" in the Lead History report type
I think you can, but you may need to setup Lead History Field tracking first by going to Object Manger > Lead > and clicking on History Tracking in the upper right. Then make sure you select Lead Status as one of the fields to track moving forward
Great video! Thank you for a clear and simple explanation. Just wondering, can you recommend a chart that would be able to depict these conversion percents well?
Thank you for the comment! Glad you found it valuable. I would use a bar chart to easily compare the stages visually. That way you can get an intuitive understanding of what at typical conversion is for you and if there are any anomalies.
Thank you for this super useful video, you explained it so well.
I am getting this warning while creating the formula -
You must select a grouping context to use any report summary function.
Can you help me with this?
Sure, It's saying that the function you're using in the formula requires an input that it hasn't been given. You have likely grouped your report by a field and maybe have multiple groups. You need to add the field the report is grouped by to the function for it the work. Does that help?
Very valuable information. Thank you for the time and effort you put in making these video's. I have one question: Is there a way to add region information to this report? My company works in different country's and I want to see this data for different country's. Opportunity Report shows by default only opportunity data and not the related account data, were I can find country data.
Hey Rob, great question! You should be able to create a custom report type for opportunity history and opportunities and accounts to give you all fields across those three levels. If that doesn't work for you, you could create a formula field on the Opportunity that references the region or country value on the Account. Creating a report is the best option because it avoids the need to create that extra field.
THANK YOU SO MUCH!!!!!!
You are welcome!
Great Video. Very clearly explained! I'm trying to build a report that shows the actual Probability (to close won) % has been. I.e. How many opportunities in Quote stage make it to closed one, and not the just making it to the next stage. This is to validate the Probability % currently assigned in salesforce to each Forecast category. The probability %'s assigned were someone's guestimate, and we'd like to see what is currently happening to get to aa more accurate weighted forecast. appreciate any and all guidance!
I actually just created this report for a client! You start with the report in the video but then you can use the Won checkbox on the Opportunity record to count up how many opportunities that were in that stage have made it to Won. I would also filter out any currently Open opportunities to make sure you're not skewing your percentages with deals that just haven't been closed yet.
@@Rotive Fabulous!! many thanks!
Hi! I have a question. Is there a way to make an opportunity report that shows last weeks stage and then this weeks stage? My boss wants to see if statuses are being changed week to week. So even if last weeks stage was “lead” and this weeks stage is still “lead” we want to see that. Or if last weeks was “lead” and this week was “proposal issued” we want to see that as well.
Hey Mikka, Try making a report based on Opportunity History. Every time a stage is updated, Salesforce saves a record that shows you want the previous stage was and what the current stage is with a date/time stamp. I'd try building the report based on that. It will show you what's changed in the last week. If that doesn't get you the answer you're looking for, you can consider creating a snapshot report but these are much more complicated.
So this suggests that Opportunity Process always go forward in Stages right ?
It's up to you on how you want to filter out those history records. You could include the records that show people moving backwards through the funnel. This certainly does sometimes happen.
Useful, thanks!
You're welcome!
Brian, this video is GREAT! Thank you. My only complaint is that I am looking to show progression through stages all the way to Closed/Won. I am sure it's something in the formula or a simple column/layout change, but I've been banging my head against a wall for days trying to get it. For example, the output of this report shows me that 27 Opportunities went from Diagnose to Design, but then I need to go down to the Design subtotal and look for those that went to Procurement, then down to the next grouping to get contract validation, etc. Do you have a video that shows a Closed/Won rate from each stage? Thank you for anything you can offer.
Hey Halcyon14, thanks for the comment!
I would change the report filters so the TO Stage was only Closed/Won and keep the groupings as is. That will show you many deals moved from a stage to won.
When I do this, all
The percentages show 100% because it is only showing the opps that went from
A From stage to Closed/won - therefore they all close/won 100% of the time. I may be misunderstanding!
@@zapotheque4113hey! I know it’s been awhile but were you able to figure this out, I’m trying to do the same thing, but can’t find anything on it, thanks!
Great video! Love the explanation :)
One question tho...doesn't this 'double count' single opportunities that may have gone forward a stage, then back, then forward again? (e.g. From A -> B, then B -> A, then again A -> B). This essentially creates 2 history records in the same Opportunity, potentially inflating percentage numbers.
Hey Jonathan, you are absolutely right. This will count every time an opportunity moves from one stage to another. Hopefully you don't have too many opportunities going through he same stage more than once. If you do, you can do some more work to group the records by Opportunity to essentially count the unique opps to get rid of that noise.
Thank you!
You're welcome!
Thank you thank you thank you thank you
Thanks for watching! We appreciate it.
Have you ever created a report that shows time in each stage?
Hey Quinn, Yes! You can create a similar report to the one in the video. Just add the Stage Duration field. I believe it represents the number of days the opportunity was in that stage. You can then group by stage to get an average and filter as needed.