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

Question

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.

Answer 1

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

Answer 2

After a lot of research and thought, I've concluded that in some cases, you just can't determine the answer to this question.

Suppose you discover a new species of tortoise on an island, and you want to estimate their average lifespan. You can't really know whether the sample group you're looking at is representative of their typical total lifespan unless you are able to outlive all tortoises. Even if you tag them all and find that 50% are dead when you return the island 5 years later, you can't tell if there is a small cohort that lives exceptionally long, skewing the average. The only way to know for sure is to outlive them all.

What I found in my case is that I could plot the average lifespan of all subscriptions up to various historical years, and see that the average was increasing linearly over time, probably due to this cohort of loyal customers. It was not exponential or asymptotic; there was no curve that flattened out that I could easily project to a maximum or minimum value. Of course, it's possible there really is a curve, but we just don’t see it yet; maybe we need another 20 years of data!

(Now, if there was more of a curve to the slope as it went on far into the future, we might find that it approaches an asymptote. We could use curve fitting to determine the function and predict the future average subscription length. But of course, there could be huge effects in the future that would alter it, such as the business becoming uncompetitive, the economy declining, or whatever.)

Either way, if we want to make a projection of subscription lifespan, and expected revenue, we have to limit the projection by asking, “How far in the future should we project?” In my case, I was able to do that for 10 years in the future, and base my projections on that. I could say, "10 years from now, the average length of all subscriptions will be X, and therefore if we acquire a new subscription now, we can expect that it will live on average X years (plus some bias toward recent averages.)"