# How do you model the average value of a customer subscription based on historical data that is ongoing up till the present?

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.

• I've added some fake sample data to try to clarify the question. I don't really think it's too broad, in fact I'm sure any expert in statistics would have come across such a question before. E.g. "Forecast the average life span of this new species of animal, given only 10 years of data, where a minority of animals have been alive for the entire 10 years." I think I just don't know the terminology. If someone could suggest something like "oh, you need to use a Poisson distribution" then I could research that concept and figure out how to apply it to my problem. (Poisson is just an example.) Commented Oct 27, 2017 at 16:08

Not my expertise, but per your comment, look into Kaplan-Meier estimation, which is a common form of survival analysis.