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?