如何根据月度收益和损失数据确定平均寿命 [英] How can I determine average life from monthly gain and loss data

问题描述

全部，



非常感谢您阅读我的问题！



我有一个数据集告诉我我每个月都会赢得多少新客户，以及我失去了多少客户。我想估算这个数据集的平均客户生命周期。



为了扩大我的问题：



我可以将数据集限制为那些来过去的客户，我有一个开始日期和结束日期。然后平均寿命是微不足道的。但那些尚未离开的忠实客户呢！它们不影响这个寿命计算，所以我可能低估了。



换句话说，如果两个客户一年前加入我的话。一个月后立即离开，但另一个留在我身边。我不满意一种算法告诉我平均寿命是一个月。但是我如何对待其他客户？



谢谢！



Jon



我尝试了什么：



我最好的想法是在一段时间内（可能是每月）计算损失数并除以那个月的客户总数。我可以对此度量应用移动平均值或某种算法来进一步细化它。但我没有的是这种方法的明确理由！ （但至少编码起来很简单！）



当我的销售不稳定时，我尝试过的其他事情都会犯规。如果销售数量不变或者客户数量大致不变，我的方法有效，但在高增长或下降的情况下会有所下降。

All,

many thanks for reading my problem!

I have a dataset that tells me each month how many new clients I have won, and how many I have lost. I want to estimate average client lifetime from this data-set.

To expand a little on my issue:

I could limit the dataset to those clients who have come and gone, for whom I have a start date and an end date. It is then trivial to take an average lifetime. But what about those loyal clients who haven't left yet! They are not affecting this lifetime calculation, so I am probably underestimating.

Put another way, if two clients joined me a year ago. One left promptly after one month but the other has remained with me. I'm not happy with an algorithm that tells me the average lifetime is one month. But how do I treat the other client?

Thanks!

Jon

What I have tried:

My best idea is to take the number of losses in a time-period (probably monthly) and divide that by the total number of clients in that month. I can apply a moving average or some-such algorithm to this measure to refine it more. But what I don't have is a clear justification for this approach! (But at least it's simple to code up!)

Other things I've tried fall foul when my sales are not constant. I have approaches that work if sales numbers are constant or the client numbers are roughly constant, but fall over in the cases of high growth or decline.

推荐答案

首先，我分别汇集两种客户类型：具有固定终身价值的那些客户类型和那些终身作为客户的客户类型仍然是开放式的。他们真的不能以任何好的方式兼容数据。一个月的新客户，平均十年中的一个，即使两个客户仍然是客户，也会产生糟糕的结果。您需要对可能永远不会关闭其帐户的客户的不存在的截止日期做出假设。



您还没有为此提供语言，所以我将为忠诚的客户提供两种选择。



如果使用SQL，当您请求截止日期时，请使用



ISNULL（'截止日期字段名称'，GETDATE（））



在您选择时检索日期（无论您通过何种方式确定日期差异）你现在可以平均包含缺失值的生命。

OR

如果从你拥有它的任何地方检索数据后再对它进行检测，那么你需要在数组中对没有日期的元素进行类似的替换。



现在，如第一段所述，我没有看到任何有效的方法（1）混合两种类型的客户端，以及（2）我没有看到一种合法的方法来平均实际上开放的数值。



最好的希望，也许是直方图显示，特别是对于开放式组。我可以想象一个人可以比较你可以生成的两种直方图，并得出一些结论。

First, I'd pool the two client types separately: those who've a fixed lifetime value and those who's lifetime as a client is still open-ended. They're really not compatible data in any good way. A new client for a month, averaged with one of ten years gives a poor result, even if both are still clients. You need to make an assumption about the non-existent closing dates of clients that may never close their account.

You've not given a language for this, so I'll offer two options for the loyal customers.

If using SQL, when you request the closing date, use

ISNULL('closing date field name', GETDATE())

in your select when retrieving the date (by whatever means you determine the date difference) and you can now average the life that includes the missing values.
OR
If doing this with the data set after it is retrieved from wherever you have it, then you need to do a similar replacement in the array for elements without a date.

Now, as in the first paragraph, I don't see any valid way of (1) mixing the two types of clients, and (2) I don't see a legitimate way of averaging values that are actually open-ended in their magnitude.

Best hope, perhaps, is a histogram display, particularly for the open-ended group. I can visualize that one can compare the two flavors of histogram you could generate and come up with some conclusions.

这篇关于如何根据月度收益和损失数据确定平均寿命的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！