Broadly, I'm trying to answer the question, "What is the value of a single new customer to our business?" This will help us determine how much we're willing to spend on marketing or discounts to acquire new customers.
The challenge in answering that question is that some of our customers subscribe to a single service plan from us, while other customers subscribe to multiple service plans. So to narrow it down, a better question is, "What is the average amount of revenue we can expect from a single service plan sold to a single customer?" However, to further complicate that question, we have some customers who subscribe to the service for only one year and never renew, while we have other customers who joined us almost 20 years ago, when the business first started, and are still subscribed.
Our historical data is pretty good: I can tell you how many customers we've ever had, how much they paid for their service plans, and when those plans begun and ended. My background is software development, so I'm strong in SQL and typical report queries. I could easily write a query against our data to answer the question, "What is the average amount of revenue we can expect over the next X years from a single service plan sold to a single customer?" as long as X is less than the number of years our business has been around. But how can I possibly account for the customers who have kept their service plans since day 1, and may indeed keep them forever? Or for customers that join us tomorrow and then never leave us?
I know that, on average, when a new service plan first comes up for renewal, there is a 55% chance that a customer will renew it and a 45% chance that they will let it expire. I have only a basic understanding of statistics and calculus, but I feel like there should be some sort of curve that I could plot and then extrapolate out to an asymptotic zero. I feel that if someone more knowledgeable in this area could point me to the right concepts, I could figure out how to apply them to my case.
To make this question easier to understand, I will give an alternate scenario that would not pose a problem for me: if our service plan maximum length was much less than the length of our business and data set. For example, if plans could only ever last 5 years, then I could simply exclude all plans that have begun in the last 5 years, and use the data to determine average plan lengths.
Edit for clarification:
Suppose we have been in business for 10 years and have the following 20 historical customer subscription lengths (for those active up to the current year 2017, I will denote whether they are ongoing or cancelled):
- 10 years, 2007 to 2017 (ongoing)
- 10 years, 2007 to 2017 (cancelled)
- 9 years, 2008 to 2017 (ongoing)
- 8 years, 2008 to 2016
- 6 years, 2009 to 2015
- 5 years, 2012 to 2017 (ongoing)
- 4 years, 2008 to 2012
- 4 years, 2008 to 2012
- 3 years, 2009 to 2012
- 3 years, 2007 to 2010
- 3 years, 2011 to 2014
- 2 years, 2009 to 2011
- 2 years, 2010 to 2012
- 2 years, 2015 to 2017 (ongoing)
- 2 years, 2013 to 2015
- 1 year, 2008 to 2009
- 1 year, 2016 to 2017 (ongoing)
- 1 year, 2013 to 2014
- 1 year, 2012 to 2013
- 1 year, 2016 to 2017 (cancelled)
Obviously we have far more than 20 subscriptions in our real data set, but I think this set illustrates my problem: How do you model for subscriptions that have never ended, and may never end? I could simply take the average length of all subscriptions (3.4 years), but that would be underestimating the length because there are some subscriptions that are still active, whose lengths will increase the next time I look at the data.