Close Rate By Stage %

user04886

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?

 

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?

 

Screen Shot 2018-06-25 at 2.31.40 PM.png

Property_Ninja

Hello,

 

You would probably have to write 5 different beast modes and they would look something like below. You still have the ability to use all five calculations in the same chart if you drag the beast modes into the card "Y Axis" and "Series."

 

 

--Stage 1 Conversion

COUNT(CASE WHEN Status = 'won' then `Deal Title` end)
/
COUNT(CASE WHEN stage_order_nr >= 1 then `Deal Title` end)

--Stage 2 Conversion

COUNT(CASE WHEN Status = 'won' then `Deal Title` end)
/
COUNT(CASE WHEN stage_order_nr >= 2 then `Deal Title` end)

--Stage 3 Conversion

COUNT(CASE WHEN Status = 'won' then `Deal Title` end)
/
COUNT(CASE WHEN stage_order_nr >= 3 then `Deal Title` end)

--Stage 4 Conversion

COUNT(CASE WHEN Status = 'won' then `Deal Title` end)
/
COUNT(CASE WHEN stage_order_nr >= 4 then `Deal Title` end)

--Stage 5 Conversion

COUNT(CASE WHEN Status = 'won' then `Deal Title` end)
/
COUNT(CASE WHEN stage_order_nr >= 5 then `Deal Title` end)

Let me know if this works.

 

 

Thanks,

 

Brian

 

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