{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Loading data and libraries"},{"metadata":{"trusted":true},"cell_type":"code","source":"#loading libraries\nimport numpy as np\nimport pandas as pd\nimport seaborn as sns\nfrom matplotlib import pyplot\nfrom plotnine import *\nfrom datetime import datetime\nimport calendar as cd\nimport gc","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#loading data\ndf_transactions = pd.read_csv('../input/kkbox-churn-transactions/transactions.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Exploring transactions datatable"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transactions.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transactions.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transactions.describe().round(2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transactions.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transactions.isna().any()","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"print(\"payment_method_id:\", df_transactions[\"payment_method_id\"].unique())\nprint()\nprint(\"plan_list_price\", df_transactions[\"plan_list_price\"].unique())\nprint()\nprint(\"is_auto_renew\", df_transactions[\"is_auto_renew\"].value_counts(normalize=True))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It seems like the subscription terms vary alot. There seem to be a number of payment methods and variety of plan types indicated by plan pricing.\n85% of customers seems to be on auto renew while 15% of customers seems to be on month to month package\nLets explore the data further with some visualizations"},{"metadata":{"trusted":true},"cell_type":"code","source":"#exploring payment plan days\nggplot(data=df_transactions, mapping= aes(x='factor(payment_plan_days)')) \\\n    + geom_bar() \\\n    + theme_bw() \\\n    + theme(axis_text_x = element_text(angle=90))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"- Majority of the customers are on a monthly plan (30/31 days) with some being on 7 days plans and there a a few customers that seem to be on a multi-period plan.\n- The customers with 0 days may be on the trial plan"},{"metadata":{"trusted":true},"cell_type":"code","source":"#exploring plan list price\nggplot(data=df_transactions, mapping= aes(x='factor(plan_list_price)')) \\\n    + geom_bar() \\\n    + theme_bw() \\\n    + theme(axis_text_x = element_text(angle=90))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"- The distribution seems very similar to the plan days and it suggests that the data is for billings\n- We need to standardize the plan pricing to either MRR or ARR. For the purpose of this analysis, I will use a very simplified approach for converting the billings to MRR/ARR.\n- We may also want to remove the customers on trial for this analysis"},{"metadata":{},"cell_type":"markdown","source":"# Calculating ARR and rentention rates"},{"metadata":{},"cell_type":"markdown","source":"In this example, I am going to calculate ARR using very simplified assumptions and then calculate the\nGross Dollar Retention (GDR) and Net Dollar Retention (NDR)"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transactions = df_transactions[df_transactions.plan_list_price > 1]\ndf_transactions = df_transactions[df_transactions.payment_plan_days > 6]\ndf_transactions[['plan_list_price', 'payment_plan_days']].describe().round(2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transactions['ARR'] = (df_transactions['plan_list_price'] / df_transactions['payment_plan_days']) * 365\ndf_transactions['ARR'].describe().round(2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transactions[['ARR']].describe().round(2)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We need to convert dates to dates and also see the contract length"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transactions.transaction_date = pd.to_datetime(df_transactions.transaction_date, format = '%Y%m%d')\ndf_transactions.membership_expire_date = pd.to_datetime(df_transactions.membership_expire_date, format = '%Y%m%d')\ndf_transactions.head(10)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now we need to create a record for each month. Ideally we should be doing this for only multiperiod billings and do not need to for monthly billing but we will run it on all customers"},{"metadata":{"trusted":true},"cell_type":"code","source":"#check for 2 customers. To be replaced by full transactions\n#df_user0 = df_transactions[df_transactions.msno == \"LUPRfoE2r3WwVWhYO/TqQhjrL/qP6CO+/ORUlr7yNc0=\"]\n#df_user1 = df_transactions[df_transactions.msno == \"YyO+tlZtAXYXoZhNr3Vg3+dfVQvrBVGO8j1mfqe4ZHc=\"]\n#df_user1 = df_user1.append(df_user0)\n#df_transactions = df_user1\ndf_transactions = df_transactions.sort_values(by=['msno', 'transaction_date'])\ndf_transactions = df_transactions.reset_index()\ndf_transactions.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transactions_g60 = df_transactions[df_transactions['payment_plan_days']>=60]\ndf_transactions_l60 = df_transactions[df_transactions['payment_plan_days']<60]\n#df_transaction_g30.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_transactions_l60 = df_transactions_l60[['msno', 'transaction_date', 'ARR']]\ndf_transactions_l60.columns = ['User', 'Date', 'ARR']\n#df_transactions_l60.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_ARR = pd.DataFrame(\n    [[t[1], d, t[10]] for t in df_transactions_g60.itertuples(index=False)\n     for d in pd.date_range(t[7], t[8], freq='MS')],\n    columns=['User', 'Date', 'ARR']\n)\ndf_ARR = df_ARR.append(df_transactions_l60)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#df_ARR = df_ARR[df_ARR['Date'].dt.year > 2015]\ndf_ARR.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_ARR = df_ARR.groupby(['User', 'Date'], as_index = False).max()\ndf_ARR = df_ARR.sort_values(by=['User', 'Date'])\n#df_ARR","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del df_transactions_g60\ndel df_transactions_l60\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del df_transactions\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#df_ARR_temp = pd.DataFrame(df_ARR['User'].unique(), columns = ['User'])\n#df_ARR_temp['ARR'] = 0\n#df_ARR_temp['mindate'] = df_ARR.Date.min()\n#df_ARR_temp['maxdate'] = df_ARR.Date.max()\n\n#df_ARR_temp = pd.DataFrame(\n#    [[t[0], d, t[1]] for t in df_ARR_temp.itertuples(index=False)\n#     for d in pd.date_range(t[2], t[3], freq='MS')],\n#    columns=['User', 'Date', 'ARR']\n#)\n#uploading the temp file as Kaggle kernel was having issues running the above code\ndf_ARR_temp = pd.read_csv('../input/df-arr-temp/df_ARR_temp.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#df_ARR = df_ARR.set_index(['User', 'Date'])\n#df_ARR = df_ARR.reindex(pd.MultiIndex.from_product(df_ARR.index.levels))\ndf_ARR = df_ARR.append(df_ARR_temp)\ndf_ARR['Date'] = df_ARR['Date'].values.astype('datetime64[M]')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#deleting df_ARR_temp\ndel df_ARR_temp\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_ARR = df_ARR.groupby(['User', 'Date'], as_index = False).max()\ndf_ARR = df_ARR.sort_values(by=['User', 'Date'])\n#df_ARR","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_ARR['ARR'] = df_ARR['ARR'].fillna(0)\ndf_ARR.reset_index(inplace = True)\ndf_ARR","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_ARR['cname'] = df_ARR.User.shift(1).fillna('abc')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#calculating Diff\ndf_ARR['Diff'] = df_ARR['ARR'].diff()\ndf_ARR['Diff'] = np.where(df_ARR['cname'] != df_ARR['User'], 0, df_ARR['Diff'])\ndf_ARR = df_ARR.drop(['cname'], axis = 1)\n\n#get opening ARR\ndf_ARR['Diff'] = df_ARR['Diff'].fillna(0)\ndf_ARR['Beg_ARR'] = df_ARR['ARR'] - df_ARR['Diff']\ndf_ARR['New'] = np.where(df_ARR['Beg_ARR'] == 0, np.where(df_ARR['Diff'] >0,df_ARR['Diff'], 0),0)\ndf_ARR['Lost'] = np.where(df_ARR['ARR'] == 0, np.where(df_ARR['Diff'] <0,df_ARR['Diff'], 0),0)\ndf_ARR['Upsell'] = np.where(df_ARR['Diff'] >0,df_ARR['Diff'] - df_ARR['New'], 0)\ndf_ARR['Downsell'] = np.where(df_ARR['Diff'] <0,df_ARR['Diff'] - df_ARR['Lost'], 0)\ndf_ARR['Check'] = df_ARR['Beg_ARR'] + df_ARR['New'] + df_ARR['Lost'] + df_ARR['Upsell'] + df_ARR['Downsell'] \\\n    - df_ARR['ARR']\n\ndf_ARR.head(5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Always good to confirm that check totals work!"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_ARR['Check'].describe().round(2)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Calculating Retention\nNow we will calculate the gross and net retention"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_ARR['GDR'] = df_ARR['Beg_ARR'] + df_ARR['Lost'] + df_ARR['Downsell']\ndf_ARR['NDR'] = df_ARR['GDR'] + df_ARR['Upsell']\ndf_ARR['Date'] = df_ARR['Date'].values.astype('datetime64[M]')\n\ndf_ARR_summ = df_ARR.groupby(['Date'], as_index = False).sum()\ndf_ARR_summ = df_ARR_summ.sort_values(by=['Date'])\n\n\ndf_ARR_summ['GDRpct'] = df_ARR_summ['GDR'] / df_ARR_summ['Beg_ARR']\ndf_ARR_summ['NDRpct'] = df_ARR_summ['NDR'] / df_ARR_summ['Beg_ARR']\ndf_ARR_summ.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Plotting retention visually"},{"metadata":{"trusted":true},"cell_type":"code","source":"ax1 = sns.set_style(style=None, rc=None )\nfig, ax1 = pyplot.subplots(figsize=(25,8))\ng = sns.lineplot(data = df_ARR_summ['NDRpct'], marker='o', sort = False, ax=ax1, color = \"b\")\ng = sns.lineplot(data = df_ARR_summ['GDRpct'], marker='o', sort = False, ax=ax1, color = \"r\")\nax2 = ax1.twinx()\nx_dates = df_ARR_summ['Date'].dt.strftime('%Y-%m').sort_values().unique()\ng = sns.barplot(data = df_ARR_summ, x=x_dates, y='ARR', alpha = 0.5, ax=ax2, color = 'y')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The GDR and NDR are closely tracked suggesting that the Company does not upsell alot of existing customers. There seem to some exceptions. We will now explore the months where the churn was high (i.e. retention was low). The dataset probably does not have full data for Mar17, so we will ignore that for now."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_ARR['GDRpct'] = df_ARR['GDR'] / df_ARR['Beg_ARR']\ndf_ARR['NDRpct'] = df_ARR['NDR'] / df_ARR['Beg_ARR']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#deleting df_ARR_summ\ndel df_ARR_summ\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_ARR['NDRpctcat'] = np.where(df_ARR['NDRpct']< 1, \"Check\", \"OK\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ax1 = sns.set_style(style=None, rc=None )\nfig, ax1 = pyplot.subplots(figsize=(25,8))\ng = sns.scatterplot(ax=ax1, data=df_ARR, x = 'ARR', y = 'NDRpct', hue='NDRpctcat', s = 100)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There seems to be 1 outlier that should ideally be removed from analysis/inspected further. All the customers below 100% NDR should be looked into further as these are either reducing service or cancelling subscription. \nThe chart does not look great. ggplot2 creates better looking charts but seem to have performance issues in Python implementation. The sns scatterplot can be made better but you get the gist!"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_ARR_atdate = df_ARR.loc[(df_ARR['Date'] == '2015-04-01') & (df_ARR['Diff']<0)]\ndf_ARR_atdate[['User', 'Diff']].round(0)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"A drilldown into customers either lost or downsell in 2015-04-01 is generated as an example. In real world, we would want to drill further into the reason of loss for these customers.\nWe can save the list of these unique customers and then explore the user logs file later. That is a massive file and may need to be loaded via SQL/SQLAlchemy so let me know if anyone is interested in drilling into that further. Once we understand the dataset and customer profiles, we can set to create ML model to predict churn."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_ARR_1504 = df_ARR_atdate[\"User\"].unique()\ndf_ARR_1504 = pd.Series(df_ARR_1504)\ndf_ARR_1504.to_csv('ARR_1504.csv', index = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# deleting the\ndel df_ARR\ndel df_ARR_1504\ngc.collect()","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}