{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n\nimport os\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.\n\nRAW_DATA = \"../input\"\nhist = pd.read_csv(f'{RAW_DATA}/historical_transactions.csv', low_memory=False, \n                     parse_dates=[\"purchase_date\"])\nnew =  pd.read_csv(f'{RAW_DATA}/new_merchant_transactions.csv', low_memory=False, \n                     parse_dates=[\"purchase_date\"])\n\ntrain = pd.read_csv(f'{RAW_DATA}/train.csv', low_memory=False, \n                     parse_dates=[\"first_active_month\"])\n\ntest = pd.read_csv(f'{RAW_DATA}/test.csv', low_memory=False, \n                     parse_dates=[\"first_active_month\"])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"## Purchase dates overlap"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"5bc0949329bbd8ef24bcaee313834c06e550bae1"},"cell_type":"code","source":"hist.purchase_date.min(), hist.purchase_date.max(),new.purchase_date.min(), new.purchase_date.max()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"aa4949518e22f9afb99b59e5ccc40bd185dc19cf"},"cell_type":"markdown","source":"Histoiric transactions start 2017-01-01 00:00:08 and end 2018-02-28 23:59:51   "},{"metadata":{"_uuid":"5834c365788724a6fe1c9b3b2ad37003824069a9"},"cell_type":"markdown","source":"### Checking time duration of the datasets in months"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"7fbf8b26e611b5cba904987023b5c9718b44bf2c"},"cell_type":"code","source":"from dateutil import rrule\nlen(list(rrule.rrule(rrule.MONTHLY, dtstart=hist.purchase_date.min(), until=hist.purchase_date.max())))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"251f5dab41a390eb8861fdb5d453e3e7bcb10c1b"},"cell_type":"code","source":"len(list(rrule.rrule(rrule.MONTHLY, dtstart=new.purchase_date.min(), until=new.purchase_date.max())))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"17a081704a89849a7c99467a0b703ede6b1b7964"},"cell_type":"markdown","source":"- Histoiric transactions start 2017-01-01 00:00:08 and goes on for 14 months\n- New transactions start on 2017-03-01 03:24:51 and goes on for 14 months\n\nIt looks like there is a lag of 2 months between the datasets."},{"metadata":{"_uuid":"0367e42389cd6e56ad1758f04238336588a3d516"},"cell_type":"markdown","source":"### Duration of the overlap between the datasets "},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"4dc6c53d3b78ffb42c1ea3bfd31857d766c3d9d0"},"cell_type":"code","source":"len(list(rrule.rrule(rrule.MONTHLY, dtstart=new.purchase_date.min(), until=hist.purchase_date.max())))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0c51c38a2d32074c57530d43cadc88ee9d9c7f0e"},"cell_type":"markdown","source":"## **Transactions per month**"},{"metadata":{"trusted":true,"_uuid":"e2663880f28160ff1aa7f5ff61662165bf6c4e30"},"cell_type":"code","source":"hist['month']= [x.strftime(\"%y-%m\") for x in hist.purchase_date]\ncounts = hist.month.value_counts().sort_index()\ncounts.plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"8b4b605ce5e1490bd67cb0800ce09d3adabec8cc"},"cell_type":"code","source":"new['month']= [x.strftime(\"%y-%m\") for x in new.purchase_date]\ncounts = new.month.value_counts().sort_index()\ncounts.plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7a62e4dd3223e7c22d05cfde0151a56997ed8fcf"},"cell_type":"markdown","source":"* Historic transaction count reaches maximun in December of 2017 and then starts to decline for two months.\n* New transactions count spikes in March of 2018, the month when historic tranactions are no longer collected.\nDoes it mean that the reference date used in the computation of the month lag is somewhere between December 1st 2017 to January 1st 2018?\n"},{"metadata":{"_uuid":"7153a054845f8f11083ef74f08236dbcc3a22718"},"cell_type":"markdown","source":"\n**Let's check the month lag values in the new and historic tranascations**"},{"metadata":{"trusted":true,"_uuid":"3ed0feccc1a3493c704ce7df20bff0a351188635"},"cell_type":"code","source":"hist.month_lag.min(), hist.month_lag.max() , new.month_lag.min(), new.month_lag.max()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"be66a4f9209aceddb646a009112283a53e86bb3f"},"cell_type":"markdown","source":"These numbers contradict the observation. It seems that the spike in sales in december of 2017 is due to seasonal demand.\nThe reference date seems to be February 1st 2018. Sales in historic transactions that happened during February of 2018 suppose to have month_lag == 0 \nTne month_lag for new transactions was not computed prior to  March 1st of 2018, hance the month lag is positive 1 and 2 for the new transactions \nSpike in sales reflected in the new_merchant transactions in March of 2018 seems to be due to promotion of new merchants that started on the reference date. \nThe spike in sales to new merchants perhaps scared ELO and that prompted this competition, perhaps because that the core merchants in the historic transactions took a serious hit.\n\nThis would be a nice conclusion, but it does not seem to hold given what you are going to see next."},{"metadata":{"_uuid":"b1a3b18028a026fa0923440737d0f732d1f8025d"},"cell_type":"markdown","source":"### **Number of new_merchant transactoins per day and per month **"},{"metadata":{"trusted":true,"_uuid":"6151f0ab58e142c16d4a49d02bbadfd6e7a7f8ac"},"cell_type":"code","source":"tmp = new.loc[new.month_lag == 1, 'purchase_date'].value_counts() < 100\nnew.loc[tmp.values, 'purchase_date'].min(), new.loc[tmp.values, 'purchase_date'].max()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"e75a4a7eed52fbdb7abd295022dacca500c16678"},"cell_type":"code","source":"pd.DataFrame(new.loc[new.month_lag == 1, 'month'].value_counts()).sort_index().plot(\n    title=\"Number of Transactions with month_lag == 1\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c741d58210e4be44cc8ef164dba75383a7cc3513"},"cell_type":"markdown","source":"###  **Number of card_id counts and  new_merchants transactions counts per month**"},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"ee81624c02d60ec197f33d89f56e94a11f4354ff"},"cell_type":"code","source":"new.groupby(['month', 'month_lag']).agg({'card_id': pd.Series.nunique }).T","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f0d044d8442795666c737f0011867a2d29a0741f"},"cell_type":"code","source":"cnts = new.groupby([ 'month_lag', 'month']).agg({'card_id': pd.Series.nunique, 'purchase_date' : 'count'})\ncnts.columns=['number of cards', 'transaction count']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a9b71439a23f363349710e9e948aa119a1eb2d51"},"cell_type":"code","source":"cnts.loc[1,:]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1f978e1a4ca64ede3f9262359a023dc533137af7"},"cell_type":"code","source":"cnts.loc[1,:].plot(title = \"monthly counts for month_lag=1\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c74fff6b53a813572a03a4535d1ca33c5bff45a4"},"cell_type":"markdown","source":"## **Card_id monthly salles in hist and new transactions**"},{"metadata":{"trusted":true,"_uuid":"cc0a08f68f07c971ca9720a1b6a8389859fdafc8"},"cell_type":"code","source":"new_card_ids = new.groupby([ 'month_lag', 'month']).agg({'card_id': list })\nhist_cards_monthly_purchase_count = hist.groupby(['card_id', 'month']).agg({'purchase_date': 'count'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ec1c1d85c91e39e95b0b9bfdbf55a3509fff5a35"},"cell_type":"code","source":"hist_cards_monthly_purchase_count.head(15).T","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b57feb290f468e601e549e7342169fa3eba999aa"},"cell_type":"code","source":"prom_cards = list(set(new_card_ids.loc[1].loc['17-03'].values[0]))\nhist_card_monthly_purchase_count.loc[prom_cards].T.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"03707875eaf91987712495d353018e781e6de1cf"},"cell_type":"code","source":"prom_cards = list(set(new_card_ids.loc[1].loc['17-07'].values[0]))\nhist_card_monthly_purchase_count.loc[prom_cards].T.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a37099f67b3a7a25c67bed6251711cff2782341c"},"cell_type":"markdown","source":"\n"},{"metadata":{"_uuid":"7eee31c7f6e48d6622b03a0abfb2787000d31b56"},"cell_type":"markdown","source":"New merchant transactions file contains transactions for cards that were selected for promotions that were suggesting new merchants to existing customers. ELO ran  promotions for new merchants at slow pace since March 1st of 2017 gradually increasing exposure. In March of 2017 number of cards with month_lag == 1 was 213. By January 1st of 2018 there were 18696 cards and by February of 2018  the promotion went out to 173574 card_ids\n\nOn March 1st 2018 the promotion was released to a broader population . Sales to new merchants spiked and historic merchants were on declining trend, but we do not have data in historic transactoins to substantiate that . I guess that this spike led  ELO to a decision that the loyalty score that they have created does not explain true customer loyalty behavior. There was a need for new way of computing loyalty score differently. ELO decided to do this competition. "},{"metadata":{"_uuid":"f1dd5826c368700dc4e840e266548379f3c3e4ba"},"cell_type":"markdown","source":"## Two month difference between the historic and new transactions datasets"},{"metadata":{"_uuid":"96aa896e3804c7cc60e9a4ed9abf64d5c642cc91"},"cell_type":"markdown","source":"The historic transactions file contain sales leading up to the month when promotion started, but omits data about historic transactions during the time period covered in new_transactions.  This explains the two months lag between historic_treansactions and new_transacitions.\n\nIt seems that two month lag between the datasets might be explained be a two month data collection window prior to promotion. The historic_transactions dataset was used for creating the initial model that led to creation of the first promotion. Once promotion was launched to a (selected) group of customers, the new_transactions dataset was started. The new_transactions dataset (perhaps) contains  data about all card_ids that were exposed to promotion and transactions outside of the core historic group that were a result of promotion were recorded. Hence the new_transaction dataset (perhaps) contains record of all card_ids that completed transactions as a result of promotion. \n\n"},{"metadata":{"_uuid":"7eee31c7f6e48d6622b03a0abfb2787000d31b56"},"cell_type":"markdown","source":"*Please upvote if you find this kernel mildly intersting or entertaining*"},{"metadata":{"trusted":true,"_uuid":"4c3becf27e26c6f5d7b2d137976b125cf075e5b5"},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}