calculating the value of a new customer

Hi there,

I have a business where users subscribe and pay a monthly fee. I am trying to determine the average value of a new customer based on the length of time that they subscribe. The problem is that most of the customers are still with us, so I am unsure how to calculate this. What we know:

Monthly fee
Date joined for all customers
Date unsubscribed for customers who have closed account.
% of customers who have closed their account

We have calculated the average length of a subscription by adding up all the days subscribed and dividing it by the total number of customers, but if we include all the customers who are still with us, the data is skewed down because it assumes that all our current customers closed their accounts on the day we did the calculation. If we exclude all our current customers it is skewed the other way because they form the bulk of the data.

I hope this is clear. So just to recap, we are trying to determine how long on average a customer will subscribe to our service. Is it possible with the data we have?

thanks in advance

How do you define "new" customer? If 'new' means subscribed today, then tomorrow it will be 'old' already, so you average subscription for 'new' customers will be 1 day )))

If 'new' means, for example, subscribed in the period from January to December 20XX (or to date), then you separate all these customers (just sort all the customers by the date of subscription) and do the calculation of the average subscription period for this selection only.

So, I suggest you (1) clarify the definition of 'new' (2) sort your data by date of subscription and select only 'new' customers according to your definiton in (1) and (3) do the average subscription period calculation again for this selection.

Thanks for the reply Volga. A "new" customer is someone who just subscribed. We are trying to assign a dollar value to the new customer, which is obviously more than the 1 month they pay for at the start. Essentially this will be used for a Google Analytics "Goal Completion" where you assign a dollar value to something a visitor does on your website - in this case subscribe to our service. But because the Goal is only completed once per customer when they first subscribe, the dollar value we apply for the goal needs to be based on the "average" length of subscription. In the past we have "guessed" that the average subscription would be 12 months and assigned a dollar value based on that, but now we have more data we were hoping to make a more accurate calculation.

Does that clarify what I am trying to do? I "think" your suggestion below would still have the problem where the average would be lower than the real figure because it would assume that all of current subscribers closed their accounts on the day the calculation was made.

I've attached a spreadsheet that may help clarify further what we are trying to do. You will see that the active subscribers have a "deactivated date" of today which skews the results, but anything else we would in there would simply be a guess, unless someone can help us come up with a formula of some sort.

Thanks again

Quote:

---

Originally Posted by BillyT

But because the Goal is only completed once per customer when they first subscribe, the dollar value we apply for the goal needs to be based on the "average" length of subscription. In the past we have "guessed" that the average subscription would be 12 months and assigned a dollar value based on that, but now we have more data we were hoping to make a more accurate calculation.

---

I think what you are trying to figure out is the expected length of subscription from a new customer right? To figure out, how much $$$ in subscription revenue a new customer will bring? Please confirm.

Furthermore, when you are talking about 'new customer value', do you mean exactly 'expected cash flow from a new customer'(=average lenght of subscriptions multiplied my monthly subscription rate) or a 'new customer acquisition cost' (how much you spend on each new customer ,to get them subscribed)? (or something else?)

Usually you compare the first with the second to figure out whether it is worth spending your marketing money on attracting new customers: if your customer acquisition cost is higher than what you expect to get from the new customer over the subscription lifetime, it is probably not a good idea to spend that much.

I must say I am not familiar with Google analytics or internet revenue models, but I am in finance ))) If there is something that does not make sense to you, let me know, I'll try to explain.

Quote:

---

Originally Posted by Volga

I think what you are trying to figure out is the expected length of subscription from a new customer right? To figure out, how much $$$ in subscription revenue a new customer will bring? Please confirm.

---

Exactly

Quote:

---

Originally Posted by Volga

when you are talking about 'new customer value', do you mean exactly 'expected cash flow from a new customer'(=average lenght of subscriptions multiplied my monthly subscription rate)

---

Right again. We are stuck on how to calculate the average length of a subscription when the bulk of our customers have NOT unsubscribed.

Thanks again

Quote:

---

Originally Posted by BillyT

We are stuck on how to calculate the average length of a subscription when the bulk of our customers have NOT unsubscribed.

---

That will be called forecasting - making predictions about the length of subscription periods in the future (future=starting from tomorrow) based on the past experience (past=time until today). Welcome to the club! you are not the only one stuck here ))) millions of businesses are working hard trying to preduct their revenues next month/next year based on how much they sold last month/last year.

To start you up on forecasting, may I suggest several tools:

- you will have to make some assumptions about the future, unless you have a cristal ball and can predict the future easily. By assumptions about the future I mean, for example, an assumption that your customers that enrolled in the past 12 months are representative of your future customer base (next 12 months, next 36 months etc - depends on your forecasting horizon). So, if you estimated some average length of subscription form the past 10 years, it will be valid for the next 10 years... (usually, with internet, there is no such consistency)

- usually such assumptions are based on either past history (if for eg Facebook signed up 1 million customers each month last year, we assume, they will keep signing them up at the same rate or at least not lower), or your expectation about the future (if you know your site is about football (soccer), it will be really hot during the World Cup so you should expect your subscriptions to increase during that time)

- the less history you have in your business, the less basis you have for making your assumptions. Your subscription data is pretty limited (2 year?) so be prepared that you will need to make more assumptions about your own expectation of the future rather than base your assumptions from the past subscription history.

- having said that, to make intelligent assumptions about the future and your subscription base in it, you need to understand key drivers of subscriptions. For eg, continuing on football site example, understand who are your subscribers, split them into categories, think about why they subscribe and why they unsubscribe, etc etc. how they use the site, etc etc.

Sorry that's a lot of words to say that, if you are trying to estimate future revenue from subscriptions (both existing and new customers), your historic data is very limited, and I don't think it will allow you to do that reliably. You yourself already realised that, right? So you will need to build a model of the future based on your own understanding of the revenues, its drivers, assumptions about the future that will impact the drivers and ultimately the revenue.

Thanks Volga - appreciate your time and the tips. We actually have more data than the sample I uploaded, so we will dig into it and make the best predictions we can.

Thanks again

Oh, if you have more historic data, then what I would normally do is

(1) make some sense of the historic data in order to use it in forecasting. For eg split customers into several groups so that you could analyse (and forecast) each distinct group separately. For eg: male/female, age groups, user type etc - you can see if there are patterns and use these patterns in your forecasting of the future subscriptions

(2) do several scenarios of the future subscriptions: optimistic (everything goes as well as now and even better), pessimistic (drop in average subscription rate and number of customers) and realistic ( somewhere in between). This will provide a more comprehensive picture of your future - you can then focus on 'realistic' and then do something to move it towards optimistic, but be prepared for any emergencies if it goes 'pessimistic'

Also, I think you should consider both subscription period and the size of the customer base. The model for subscription revenue is subscriptions X number of customers at this point in time
therefore you effectively do two forecasts: average subscription length and trend in the number of customers (if increasing and if yes, at what rate).

more great tips!

Thanks again Volga

No problem. I would actually look at customer base and changes in numbers of customers in order to forecast revenues, like this (attached). And you can do it by month.

Attachment 20722

good idea, and thanks for the clear example.