{"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_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nfrom datetime import datetime\nimport matplotlib.pyplot as plt","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-08-20T02:55:38.857827Z","iopub.execute_input":"2022-08-20T02:55:38.858991Z","iopub.status.idle":"2022-08-20T02:55:38.865562Z","shell.execute_reply.started":"2022-08-20T02:55:38.858940Z","shell.execute_reply":"2022-08-20T02:55:38.864405Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Check customer lifetime value \n> how to calculate CLV ? \n>> find customer average purchase, purchase frequency, lifetime span ","metadata":{}},{"cell_type":"code","source":"customer_df=pd.read_csv('../input/h-and-m-personalized-fashion-recommendations/transactions_train.csv')","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:09:24.970888Z","iopub.execute_input":"2022-08-20T03:09:24.971652Z","iopub.status.idle":"2022-08-20T03:10:08.413011Z","shell.execute_reply.started":"2022-08-20T03:09:24.971611Z","shell.execute_reply":"2022-08-20T03:10:08.411706Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"customer_df['customer_id'] =\\\n    customer_df['customer_id'].apply(lambda x: int(x[-16:],16) ).astype('int64')","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:10:15.016644Z","iopub.execute_input":"2022-08-20T03:10:15.017060Z","iopub.status.idle":"2022-08-20T03:10:40.775499Z","shell.execute_reply.started":"2022-08-20T03:10:15.017026Z","shell.execute_reply":"2022-08-20T03:10:40.774418Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"customer_df['article_id'] = customer_df.article_id.astype('int32')\n","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:10:44.938317Z","iopub.execute_input":"2022-08-20T03:10:44.938770Z","iopub.status.idle":"2022-08-20T03:10:45.111488Z","shell.execute_reply.started":"2022-08-20T03:10:44.938730Z","shell.execute_reply":"2022-08-20T03:10:45.110206Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"customer_df","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:14:04.747607Z","iopub.execute_input":"2022-08-20T03:14:04.748159Z","iopub.status.idle":"2022-08-20T03:14:04.767393Z","shell.execute_reply.started":"2022-08-20T03:14:04.748120Z","shell.execute_reply":"2022-08-20T03:14:04.766536Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"customer_df.t_dat = pd.to_datetime(customer_df.t_dat, format = \"%Y-%m-%d\").sort_index()\n","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:44:14.870537Z","iopub.execute_input":"2022-08-20T03:44:14.871683Z","iopub.status.idle":"2022-08-20T03:44:15.830928Z","shell.execute_reply.started":"2022-08-20T03:44:14.871639Z","shell.execute_reply":"2022-08-20T03:44:15.829850Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"customer_df= customer_df.drop('sales_channel_id', axis =1 )","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:49:21.499736Z","iopub.execute_input":"2022-08-20T03:49:21.500540Z","iopub.status.idle":"2022-08-20T03:49:22.202661Z","shell.execute_reply.started":"2022-08-20T03:49:21.500500Z","shell.execute_reply":"2022-08-20T03:49:22.201296Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"customer_df","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:49:24.390675Z","iopub.execute_input":"2022-08-20T03:49:24.391283Z","iopub.status.idle":"2022-08-20T03:49:24.406123Z","shell.execute_reply.started":"2022-08-20T03:49:24.391248Z","shell.execute_reply":"2022-08-20T03:49:24.405270Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"customer_df.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-20T02:50:07.679792Z","iopub.execute_input":"2022-08-20T02:50:07.680181Z","iopub.status.idle":"2022-08-20T02:50:07.693253Z","shell.execute_reply.started":"2022-08-20T02:50:07.680149Z","shell.execute_reply":"2022-08-20T02:50:07.691393Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"customer_df['t_dat'].min()","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:50:08.640314Z","iopub.execute_input":"2022-08-20T03:50:08.640779Z","iopub.status.idle":"2022-08-20T03:50:08.757829Z","shell.execute_reply.started":"2022-08-20T03:50:08.640742Z","shell.execute_reply":"2022-08-20T03:50:08.756468Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"customer_df['t_dat'].max()","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:50:10.206053Z","iopub.execute_input":"2022-08-20T03:50:10.206897Z","iopub.status.idle":"2022-08-20T03:50:10.316241Z","shell.execute_reply.started":"2022-08-20T03:50:10.206849Z","shell.execute_reply":"2022-08-20T03:50:10.315392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Cacluate Recency, Frequency, total amount of purchase by customer for last three month ","metadata":{}},{"cell_type":"markdown","source":">> How to calculate CLV data? \n>>> Recency: How recently customer purchase \n>>> Frequency : how often customer purchase \n>>> Monetrary : How much customer purchase ","metadata":{}},{"cell_type":"code","source":"df = customer_df.loc[customer_df['t_dat'] > '2019-12-31']","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:50:39.851503Z","iopub.execute_input":"2022-08-20T03:50:39.852263Z","iopub.status.idle":"2022-08-20T03:50:40.456883Z","shell.execute_reply.started":"2022-08-20T03:50:39.852223Z","shell.execute_reply":"2022-08-20T03:50:40.455851Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:50:45.772705Z","iopub.execute_input":"2022-08-20T03:50:45.773148Z","iopub.status.idle":"2022-08-20T03:50:45.790669Z","shell.execute_reply.started":"2022-08-20T03:50:45.773110Z","shell.execute_reply":"2022-08-20T03:50:45.789395Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"order_df = df.groupby(['customer_id','article_id']).agg({'price': sum, 't_dat':max})","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:50:52.590101Z","iopub.execute_input":"2022-08-20T03:50:52.591323Z","iopub.status.idle":"2022-08-20T03:51:02.176428Z","shell.execute_reply.started":"2022-08-20T03:50:52.591269Z","shell.execute_reply":"2022-08-20T03:51:02.175133Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"order_df.head(300)","metadata":{"execution":{"iopub.status.busy":"2022-08-20T02:53:20.680806Z","iopub.execute_input":"2022-08-20T02:53:20.681557Z","iopub.status.idle":"2022-08-20T02:53:20.699025Z","shell.execute_reply.started":"2022-08-20T02:53:20.681519Z","shell.execute_reply":"2022-08-20T02:53:20.697852Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_data=order_df.head(10000)","metadata":{"execution":{"iopub.status.busy":"2022-08-20T02:53:27.803722Z","iopub.execute_input":"2022-08-20T02:53:27.804790Z","iopub.status.idle":"2022-08-20T02:53:27.813253Z","shell.execute_reply.started":"2022-08-20T02:53:27.804751Z","shell.execute_reply":"2022-08-20T02:53:27.811512Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_data","metadata":{"execution":{"iopub.status.busy":"2022-08-20T02:53:29.831737Z","iopub.execute_input":"2022-08-20T02:53:29.832146Z","iopub.status.idle":"2022-08-20T02:53:29.853068Z","shell.execute_reply.started":"2022-08-20T02:53:29.832110Z","shell.execute_reply":"2022-08-20T02:53:29.851702Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def groupby_mean(x) :\n    return x.mean()\ndef groupby_count(x):\n    return x.count()\ndef purchase_duration(x):\n    return (x.max()-x.min()).days\ndef avg_frequency(x):\n    return (x.max()-x.min()).days/x.count()\n\ngroupby_mean.__name__= 'avg'\ngroupby_count.__name__= 'Total_purchase_count'\npurchase_duration.__name__ ='purchase_duration'\navg_frequency.__name__ = 'purchase_frequency'\n\nsummary_df = sample_data.reset_index().groupby('customer_id').agg({'price':[groupby_mean, groupby_count], \n                                                                   \"t_dat\":[min,max, purchase_duration,avg_frequency]})","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:51:16.477533Z","iopub.execute_input":"2022-08-20T03:51:16.477985Z","iopub.status.idle":"2022-08-20T03:51:16.942442Z","shell.execute_reply.started":"2022-08-20T03:51:16.477946Z","shell.execute_reply":"2022-08-20T03:51:16.940845Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"summary_df","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:51:19.577946Z","iopub.execute_input":"2022-08-20T03:51:19.578394Z","iopub.status.idle":"2022-08-20T03:51:19.604665Z","shell.execute_reply.started":"2022-08-20T03:51:19.578331Z","shell.execute_reply":"2022-08-20T03:51:19.603512Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"summary_df.describe()","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:51:36.315203Z","iopub.execute_input":"2022-08-20T03:51:36.316833Z","iopub.status.idle":"2022-08-20T03:51:36.363487Z","shell.execute_reply.started":"2022-08-20T03:51:36.316790Z","shell.execute_reply":"2022-08-20T03:51:36.362415Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### plot the graph of CLV ","metadata":{}},{"cell_type":"code","source":"summary_df.columns =['_'.join(col).lower() for col in summary_df.columns]\nsummary_df= summary_df.loc[summary_df['t_dat_purchase_duration'] > 0]","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:51:46.437053Z","iopub.execute_input":"2022-08-20T03:51:46.437519Z","iopub.status.idle":"2022-08-20T03:51:46.445938Z","shell.execute_reply.started":"2022-08-20T03:51:46.437480Z","shell.execute_reply":"2022-08-20T03:51:46.444570Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ax =summary_df.groupby('price_total_purchase_count').count()['price_avg'][:20].plot(kind = 'bar', color ='red', figsize =(12,7),grid =True)\n\nax.set_ylabel('count')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:51:48.462396Z","iopub.execute_input":"2022-08-20T03:51:48.462811Z","iopub.status.idle":"2022-08-20T03:51:48.790911Z","shell.execute_reply.started":"2022-08-20T03:51:48.462773Z","shell.execute_reply":"2022-08-20T03:51:48.789679Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":">As we can see, most cuustomer purchase less than 8 purchases in 3 months. ","metadata":{}},{"cell_type":"code","source":"ax =summary_df['t_dat_purchase_frequency'].hist(bins = 20, color ='blue',rwidth =0.7, figsize =(12,7),grid =True)\n\nax.set_xlabel('avg number of days between purchase')\nax.set_ylabel('count')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:51:55.994549Z","iopub.execute_input":"2022-08-20T03:51:55.995359Z","iopub.status.idle":"2022-08-20T03:51:56.247488Z","shell.execute_reply.started":"2022-08-20T03:51:55.995299Z","shell.execute_reply":"2022-08-20T03:51:56.246311Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"> average customer purchase the products every 2 to 5 days in 3 month, whcih is pretty short cycle. ","metadata":{}},{"cell_type":"markdown","source":"### Predicting a month CLV ","metadata":{}},{"cell_type":"markdown","source":"In this section, we will predict CLV in 3 month.\nFirst, we will aggregate the data from transaction data from 2020-01-01 to 2020-09-22\nSecond, we will divide the data into chuncks of 3 months and set the last 3 months as target data for predict model and rest of data as the feature data.\n","metadata":{}},{"cell_type":"markdown","source":"#### 1. Aggregate 2020 transaction data","metadata":{}},{"cell_type":"code","source":"df_year = customer_df.loc[customer_df['t_dat'] > '2019-12-31']","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:52:07.448660Z","iopub.execute_input":"2022-08-20T03:52:07.449104Z","iopub.status.idle":"2022-08-20T03:52:07.992536Z","shell.execute_reply.started":"2022-08-20T03:52:07.449065Z","shell.execute_reply":"2022-08-20T03:52:07.991421Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_year.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:52:09.519994Z","iopub.execute_input":"2022-08-20T03:52:09.520432Z","iopub.status.idle":"2022-08-20T03:52:09.534534Z","shell.execute_reply.started":"2022-08-20T03:52:09.520395Z","shell.execute_reply":"2022-08-20T03:52:09.533451Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_year.sort_values(by='t_dat')","metadata":{"execution":{"iopub.status.busy":"2022-08-20T03:52:12.450629Z","iopub.execute_input":"2022-08-20T03:52:12.451023Z","iopub.status.idle":"2022-08-20T03:52:13.896543Z","shell.execute_reply.started":"2022-08-20T03:52:12.450992Z","shell.execute_reply":"2022-08-20T03:52:13.894933Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_year","metadata":{"execution":{"iopub.status.busy":"2022-08-20T02:49:41.726320Z","iopub.status.idle":"2022-08-20T02:49:41.726687Z","shell.execute_reply.started":"2022-08-20T02:49:41.726506Z","shell.execute_reply":"2022-08-20T02:49:41.726523Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_year['t_dat'].min()","metadata":{"execution":{"iopub.status.busy":"2022-08-20T02:56:28.802606Z","iopub.execute_input":"2022-08-20T02:56:28.803241Z","iopub.status.idle":"2022-08-20T02:56:28.843702Z","shell.execute_reply.started":"2022-08-20T02:56:28.803204Z","shell.execute_reply":"2022-08-20T02:56:28.842673Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_year['t_dat'].max()","metadata":{"execution":{"iopub.status.busy":"2022-08-20T02:56:32.168193Z","iopub.execute_input":"2022-08-20T02:56:32.168844Z","iopub.status.idle":"2022-08-20T02:56:32.209680Z","shell.execute_reply.started":"2022-08-20T02:56:32.168807Z","shell.execute_reply":"2022-08-20T02:56:32.208613Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### 2. divide the data into chunks of 3months ","metadata":{}},{"cell_type":"code","source":"clv_data =df_year.reset_index().groupby([\n    'customer_id', pd.Grouper(key= 't_dat',freq= 'MS')\n]).agg({\n    'price': [sum,groupby_mean,groupby_count],\n})\n\nclv_data.columns =['_'.join(col).lower() for col in clv_data.columns]\nclv_data = clv_data.reset_index()","metadata":{"execution":{"iopub.status.busy":"2022-08-20T04:09:31.479494Z","iopub.execute_input":"2022-08-20T04:09:31.480311Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"date_month ={ str(x)[:10]:'M_%s' % (i+1) for i, x in enumerate (sorted(clv_data.reset_index()['t_dat'].unique(),reverse =True))}\n\nclv_data['M']= clv_data['t_dat'].apply(lambda x: date_month[str(x)[:10]])","metadata":{"execution":{"iopub.status.busy":"2022-08-20T02:49:41.736781Z","iopub.status.idle":"2022-08-20T02:49:41.737168Z","shell.execute_reply.started":"2022-08-20T02:49:41.736970Z","shell.execute_reply":"2022-08-20T02:49:41.736988Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"clv_data['t_dat'].min()","metadata":{"execution":{"iopub.status.busy":"2022-08-20T04:07:42.726374Z","iopub.execute_input":"2022-08-20T04:07:42.727635Z","iopub.status.idle":"2022-08-20T04:07:42.742197Z","shell.execute_reply.started":"2022-08-20T04:07:42.727584Z","shell.execute_reply":"2022-08-20T04:07:42.741184Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}