How to calculate an average cliff date?

Question

Please let me know if this question belongs elsewhere.

In my simple data set focused on sales pursuit opportunities, I have the following columns available.

Pursuit Name	Status(Open, Won, Lost)	Date Entered	Date Closed

I need to calculate the an average "cliff" date value of when a pursuit will likely either lose or never close. So if another opportunity has been open for x number of days, we can say (based on past opportunities) that this will either never close or will be lost.

My assumption is that this should be based on the difference between Date Entered and Date closed, but I'm unsure of the calculations needed to produce an average "Cliff date".

Is there another term aside from cliff date I should be using? Are there any ideas of how this can be calculated?

Thank you for your time.

Answer 1

You would indeed need to calculate the difference in days between the Date Entered and the Date Closed column. You can then either (1) group directly by the number of days and look at the percentage of records where the Status is equal to Open or Lost or (2) first create buckets and then perform the same step as in (1). You are then free to choose the percentage which you feel equals "likely" (50%, 80%, 99%?) and then use the difference in days as the threshold.