1
$\begingroup$

I have a subscription based business dataset which looks like this:

Company    RenewalMonth    Year   Month  Metrics
 ABC          10           2018    1     ...
 DEF           1           2018    1     ...
 GHI           7           2018    1     ...
 ABC          10           2018    2     ...
 DEF           1           2018    2     ...
 GHI           7           2018    2     ...
 ABC          10           2018    3     ...
 DEF           1           2018    3     ...
 GHI           7           2018    3     ...
 ABC          10           2018    4     ...
 DEF           1           2018    4     ...
 GHI           7           2018    4     ...
 ABC          10           2018    5     ...
 DEF           1           2018    5     ...
 GHI           7           2018    5     ...

and so on, there around 10k accounts and I have their data usage per month for the last 5 years.

Here the RenewalMonth represents the month each year the renewal takes place for that account. Year and Month represents the aggregated usage parameters in that year and month, usage metrics consists of parameters such as sessions, content, region, products etc.

I am building a Churn model, but since renewal month of each account is not same, this posses a unique problem. If I aggregate the measures in year 2017, and use that as a train data to predict on 2018, it takes in assumption that the renewal of each account happens on 1st of January 2018 as I am predicting taking in to account last 12 months of data.

But since the renewal happens in different months, the other way is to find rolling 12 months usage of each account and then map it for prediction. For example, there is an account 'xyz' whose renewal happens in November, I will map its data for the last 12 months of usage, use that as test data, and my train data would contain the rolling 12 months of data for all those accounts for which renewal has already happen, that means any account whose renewal falls before November. But this is a very big task as there are about 10000 accounts and finding individual rolling means of those accounts is very difficult.

Could someone help me map this logic to create a rolling 12 months churn prediction model?

|cite|improve this question
$\endgroup$

closed as unclear what you're asking by usεr11852, Michael Chernick, kjetil b halvorsen, mkt, mdewey Feb 7 at 10:17

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.