{"cells": [{"metadata": {"_uuid": "1b8a1685a0946b99075cf5a0caa34c8d3df3f875", "_cell_guid": "faf7acee-5e01-400b-8a78-f9d62b002d53"}, "cell_type": "markdown", "source": ["I am trying identify churn from previous months. I know the organiser's algorithm is unkown but we can try to make an  educated guess and go with it. Below I make an attempt in the hope that it will be challenged and improved. \n", "\n", "There are a few benefits in having churn from previous months: \n", "- Understand how the data set was generated\n", "- Create features for the provided train set\n", "- Generate additional train set\n", "\n", "Re the first point, thinking about previous periods' churn got me thinking that transaction and log data perhaps should use  different time thresholds for the provided train vs test sets. Predicting March churners using transaction data up to only February means that the model trained on Feb churners should be based on transaction data up to Jan. I have not seen any baseline model taking that into account - am I missing something?\n", "\n", "Re the last point, I would like to train a model on previous years' (2016, 2015) churn for March to factor in the potential cyclicality \n"]}, {"metadata": {"_uuid": "0d9a2988eae044feee7e3460f7c8fb53c0724e50", "_cell_guid": "9cbbffbf-b391-4910-9a9e-d03a70cfd92e"}, "cell_type": "markdown", "source": ["For convenience I include the organiser's rules for churn\n", "\n", "The churn/renewal definition can be tricky due to KKBox's subscription model. Since the majority of KKBox's subscription length is 30 days, a lot of users re-subscribe every month. The key fields to determine churn/renewal are transaction date, membership expiration date, and is_cancel. Note that the is_cancel field indicates whether a user actively cancels a subscription. Note that a cancellation does not imply the user has churned. A user may cancel service subscription due to change of service plans or other reasons. **The criteria of \"churn\" is no new valid service subscription within 30 days after the current membership expires.**\n", "\n", "The train and the test data are selected from users whose membership expire within a certain month. The train data consists of users whose subscription expires within the month of February 2017, and the test data is with users whose subscription expires within the month of March 2017. This means we are looking at user churn or renewal roughly in the month of March 2017 for train set, and the user churn or renewal roughly in the month of April 2017. Train and test sets are split by transaction date, as well as the public and private leaderboard data."]}, {"metadata": {"_uuid": "bf429ebd4688b3e105f716476b342a8841b4fbb4", "collapsed": true, "_cell_guid": "52e1475b-4f53-4fd5-bb0e-1399068e2634"}, "outputs": [], "cell_type": "code", "source": ["import numpy as np # linear algebra\n", "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n", "trans = pd.read_csv('../input/transactions.csv')"], "execution_count": 1}, {"metadata": {"_uuid": "6acf3d54e8a8f1245348519362bc80089c7a0998", "collapsed": true, "_cell_guid": "2b17b636-e43c-4f08-a9ac-7c25e5ada312"}, "cell_type": "markdown", "source": ["Let's take the second user from the train set -  'QA7uiXy8vIbUSPOkCf9RwQ3FsT8jVq2OxDr8zqa7bRQ='\n", "\n", "I also take another random one because the function for churn identification requires at least two users"]}, {"metadata": {"_uuid": "a0f1c2e4635da8f9aabe4c0eb1b52135dac1e6fb", "collapsed": true, "_cell_guid": "e0a33a81-d410-4b18-8b81-a889765a637d"}, "outputs": [], "cell_type": "code", "source": ["sample = trans.loc[trans.msno.isin(['QA7uiXy8vIbUSPOkCf9RwQ3FsT8jVq2OxDr8zqa7bRQ=','waLDQMmcOu2jLDaV1ddDkgCrB/jl6sD66Xzs0Vqax1Y=']),:].copy(deep=True)"], "execution_count": 2}, {"metadata": {"_uuid": "02ca2d295162298b7b32caec54ad9a3068370934", "_cell_guid": "de401d5e-7fde-4c83-bec5-90d53052ad7a"}, "cell_type": "markdown", "source": ["Here's my modest attempts at identifying churn from previous months. I know it's not good and potentially shit, so please let me know any suggestions on how to improve it"]}, {"metadata": {"_uuid": "ff57cf205445ffee8663b8905ff4c08fa6097a97", "collapsed": true, "_cell_guid": "9ee4b5e9-bd78-4ebf-a442-e81a81f2238c"}, "outputs": [], "cell_type": "code", "source": ["def identify_churn(trans):\n", "    trans[\"transaction_date\"] = pd.to_datetime(trans[\"transaction_date\"], format='%Y%m%d')\n", "    trans[\"membership_expire_date\"] = pd.to_datetime(trans[\"membership_expire_date\"], format='%Y%m%d')\n", "    trans = trans.sort_values(by=['msno', 'transaction_date']).reset_index(drop=True)\n", "    trans[\"next_trans\"] =trans.groupby(\"msno\")[\"transaction_date\"].shift(-1)\n", "    trans[\"day_diff\"] = trans.groupby(\"msno\").apply(lambda trans: trans[\"next_trans\"] - trans[\"membership_expire_date\"]).reset_index(drop=True)\n", "    threshold = pd.Timedelta('31 days')\n", "    trans[\"churn_flag\"] = trans[\"day_diff\"]>threshold\n", "    #trans['churn_date'] = trans[\"membership_expire_date\"] + pd.Timedelta('31 days')\n", "    return trans\n", "sample = identify_churn(sample)"], "execution_count": 3}, {"metadata": {"_uuid": "218d66c1751baa8b2cea3d39303970797850032b", "_cell_guid": "79aeb72e-484c-4236-852c-89bed7157aac"}, "cell_type": "markdown", "source": ["Below is what happens for the selected user. Column 'churn_flag' should be True when a transaction indicates that she churned. It happens on transaction dated '2016-01-31'. The expiration date becomes '2016-03-21', and it takes her until '2016-05-05', i.e. more than a month later, to renew her subscription.\n", "\n", "By the way, this example user confuses me. Train set say she churned in Feb. By definition there should be \"no new valid service subscription within 30 days after the [Feb 2017] membership expires\". The transaction corresponding to membership expiring in Feb is '2016-12-31'. However another transaction happens a month later to renew the subscription to March. So I don't understand why she would be labeled as a Feb churner. Any ideas?\n", "\n"]}, {"metadata": {"_uuid": "c373386d39a5e10299f6c9566ebea9423d52fe4d", "_cell_guid": "9fa3f6e3-ded2-414f-a391-aec158e5438a"}, "outputs": [], "cell_type": "code", "source": ["sample[sample.msno=='QA7uiXy8vIbUSPOkCf9RwQ3FsT8jVq2OxDr8zqa7bRQ=']"], "execution_count": 4}], "nbformat": 4, "metadata": {"language_info": {"pygments_lexer": "ipython3", "codemirror_mode": {"name": "ipython", "version": 3}, "version": "3.6.3", "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python"}, "kernelspec": {"name": "python3", "display_name": "Python 3", "language": "python"}}, "nbformat_minor": 1}