0
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ 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.) $\endgroup$ – Jordan Rieger Oct 27 '17 at 16:08
0
$\begingroup$

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

$\endgroup$
  • $\begingroup$ Can you add more information as to why Kaplan-Meier curve would be suitable and how? $\endgroup$ – Jon Oct 27 '17 at 23:27
  • $\begingroup$ I read up on this at vassarstats.net/survival01.html and en.wikipedia.org/wiki/Kaplan%E2%80%93Meier_estimator, but I think it's not quite what I need. It sounds like KM lets you estimate the survival rate for a specific time period. What I want is similar, but with a "limit approaching infinity" to account for all the subjects that survive till the end of the sample period and may survive longer. I realize that ultimately you can never know, because you can't tell if those subjects will die the next day or live forever. But there must be a way to extrapolate from the curve. $\endgroup$ – Jordan Rieger Nov 1 '17 at 21:56
0
$\begingroup$

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.)"

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.