Closing Rate by Deal Stage


Hey guys,


We are wanting to analyze what our close ratios look like (%) from a certain stage to closing. For example, we have 5 stages in our sales cycle and we want to see "if we put a deal into the 1st stage of the sales cycle, what percentage of those deals do we close?" and another example "if we get them to the final stage (5) of our sales cycle, what is the % that we close the deal?


  • Valiant

    A couple of questions around your dataset.


    Do you have historical data of your deals or is it only current state? 


    Also, can you speak to the stage order vs status of the deals. Can deals move back and forward or only forward?  


    Is stage_order_nr = 5 only for 'won' deals?


    If your stages are sequential (only going 1,2,3,4,5) then you can do something that basically goes like this:

    SUM( All won deals)
    SUM( All deals where stage >= 1)
    * 100

    You then build a CASE WHEN for each deal stage where each WHEN is changing the '>= 1' value above to 2, 3, 4 etc... for reach deal stage. That gives you the % of deals won once a deal hits that particular stage.


    Hope that helps,



    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • ST_-Superman-_

    Can you provide some more clarity to your sales cycle here?  It looks like stage 5 is "Won", stage 4 is "Lost", what are the names of your stages?  Does every deal that is won go through each stage (except lost)?  


    i.e. can a deal go from stage 2 straight to won?


    Do you have a historical data set?  You would need to be able to see the history of every deal in order to do this.  

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user04886

    Sorry I didn't respond earlier!


    So our stage order is actually a text word but in the database it is coded as 1, 2, 3, 4, 5. 1 is lead identified, 2 is contact made, 3 is meeting set, 4 is proposal and 5 is in negotiation. We are trying to see what our win ratios are as a % of deals. 


    For example, let’s say we have 10 total deals in a data set. Let’s say:


    2 got to stage 1 and was lost

    2 got to stage 2 and 2 were lost

    1 got to stage 3 and 1 was lost

    1 got to stage 4 and 1 was lost

    4 got to stage 5, 3 were won and 1 was lost (wins will always be in this category, means they have gone through the sales cycle)


    that would mean closing rates would be:


    Stage 1- 30% 3 wins overall, 10 total made it past this stage

    Stage 2- 33% 3 wins overall, 9 total made it past this stage

    Stage 3- 50% 3 wins overall, 6 total made it past this stage

    Stage 4- 60% 3 wins overall, 5 total made it past this stage

    Stage 5- 75% 3 wins overall, 4 total made it past this stage


    Does this make sense?