{"cells":[{"metadata":{"_uuid":"f6554fdbc9f80eda9e354582124f46ddcd36c476"},"cell_type":"markdown","source":"RFM is an old technique of customer segmentation to evaluate customer loyalty.R stands for Recency , F for Frequency and M for Monetary value.\nThis kernel is based on the article by Susan Li at https://towardsdatascience.com/find-your-best-customers-with-customer-segmentation-in-python-61d602f9eee6\n\n**RFM Score Calculations**\n\n**RECENCY (R):** Days since last purchase\n\n**FREQUENCY (F):** Total number of purchases\n\n**MONETARY VALUE (M):** Total money this customer spent"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport lightgbm as lgb\nfrom sklearn.model_selection import KFold,StratifiedKFold, RepeatedKFold\nimport warnings\nimport time\nimport sys\nimport datetime\nfrom datetime import timedelta\nimport gc\nfrom sklearn.metrics import mean_squared_error\nfrom scipy.stats.mstats import mode\nfrom functools import reduce\nwarnings.simplefilter(action='ignore', category=FutureWarning)\npd.set_option('display.max_columns', 500)\nimport os\nprint(os.listdir(\"../input\"))\n","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"As mentioned in my kernel https://www.kaggle.com/rajeshcv/feature-engineering-on-multiple-reference-dates there are multiple reference dates . Recency of transactions for  a credit card  will be based on this refernce date of the credit card"},{"metadata":{"_uuid":"82152fb5ff35730a26f54f69516a49b68602f3ac"},"cell_type":"markdown","source":"To avoid negative values for purchase amount 0.75 is added to purchase_amount . This is rounded value of the minimum purchase_amount which is 0.7486"},{"metadata":{"trusted":true,"_uuid":"b181c6c2e3c3ff5824f30df52da7a76bd87cd822"},"cell_type":"code","source":"train = pd.read_csv(\"../input/elo-merchant-category-recommendation/train.csv\")\ntest = pd.read_csv(\"../input/elo-merchant-category-recommendation/test.csv\")\nhistory =pd.read_csv(\"../input/elo-merchant-category-recommendation/historical_transactions.csv\",parse_dates=['purchase_date'])\nnew =pd.read_csv(\"../input/elo-merchant-category-recommendation/new_merchant_transactions.csv\",parse_dates=['purchase_date'])\ncardreference = pd.read_csv(\"../input/feature-engineering-on-multiple-reference-dates/Cardreferencedate.csv\",parse_dates=['reference_date'])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dcdda19b7bc316dae43d62a98fd91d57ddd7359f"},"cell_type":"code","source":"history=history.loc[history.authorized_flag==\"Y\",]\nhistory.purchase_amount += 0.75\nnew.purchase_amount += 0.75","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9b1427540a21234438ee038fa274489b79719011"},"cell_type":"code","source":"cardrfm = history.groupby('card_id').agg({'card_id': 'count','purchase_date': 'max','purchase_amount': 'sum'})\ncardrfm.rename(columns={'card_id' : 'frequency','purchase_date': 'date_recency','purchase_amount': 'value'},inplace=True)\ncardrfm = pd.merge(cardrfm,cardreference.iloc[:,0:2],on='card_id',how='left')\ncardrfm['recency'] = cardrfm['reference_date'] - cardrfm['date_recency']\ncardrfm.recency= cardrfm.recency/(24*np.timedelta64(1, 'h')) # to convert to day fractions\ncardrfm.drop(columns=['date_recency','reference_date'],inplace=True)\ncardrfm.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"24bb52085af9677b1cf6866866d4fcdc9bff5514"},"cell_type":"markdown","source":"Susan Li in her article divides Recency , Frequency and Monetary value in four quantiles for the puropse of customer segmentation.\nBut in our case the target customer Loyalty scores have  values from -33.219 to 17.965 with a  median value close to 0. \nHence we need to consider more quantiles "},{"metadata":{"trusted":true,"_uuid":"6a6066b21d8cac00dc497b96459a1619d0c3587d"},"cell_type":"code","source":"print('Target value minimum',train.target.min())\nprint('Target value maximum',train.target.max())\nprint('Target value median',train.target.median())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a661b183f7a5df6462b4a7a7a2f486c1ad57f7d8"},"cell_type":"code","source":"train.target.quantile(q=[0.011,0.05,0.25,0.5,0.75,0.95,0.989])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9bdc4e58c46aa41e2b235bda1c9a97d61488c862"},"cell_type":"markdown","source":"These quantiles seems to be reasonable as it sepeartes most of the target value groups.. Assigning this quantiles to cardrm variables as below."},{"metadata":{"trusted":true,"_uuid":"d24db5177264c1e2510226334d4a98f36251b7e1"},"cell_type":"code","source":"quantiles = cardrfm.quantile(q=[0.011,0.05,0.25,0.5,0.75,0.95,0.989])\nquantiles = quantiles.to_dict()\nquantiles","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"277f39b80f4cb9cbc65d2693c9544d2b8b9eda67"},"cell_type":"markdown","source":"Functions Rscore is for assigning the recency quantile and RMScore is for assigning  frequency and monetary value uantiles.\nNote the assigning of quantile of frequency and value is reverse to recency as lower the recency the better but higher the frequency and value the better from the perspective of customer loyalty."},{"metadata":{"trusted":true,"_uuid":"4ae5bbfa890f55187f79fa17fcd5293a6a94888f"},"cell_type":"code","source":"def RScore(x,p,d):\n    if x <= d[p][0.011]:\n        return 1\n    elif x <= d[p][0.050]:\n        return 2\n    elif x <= d[p][0.25]: \n        return 3\n    elif x <= d[p][0.5]:\n        return 4\n    elif x <= d[p][0.75]:\n        return 5\n    elif x <= d[p][0.95]:\n        return 6\n    elif x <= d[p][0.989]:\n        return 7\n    else:\n        return 8\n    \ndef FMScore(x,p,d):\n    if x <= d[p][0.011]:\n        return 8\n    elif x <= d[p][0.050]:\n        return 7\n    elif x <= d[p][0.25]: \n        return 6\n    elif x <= d[p][0.5]:\n        return 5\n    elif x <= d[p][0.75]:\n        return 4\n    elif x <= d[p][0.95]:\n        return 3\n    elif x <= d[p][0.989]:\n        return 2\n    else:\n        return 1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"de54bd83f082721ab22b4b15d6c1505a00a0c053"},"cell_type":"code","source":"cardrfm['r_quantile'] = cardrfm['recency'].apply(RScore, args=('recency',quantiles))\ncardrfm['f_quantile'] = cardrfm['frequency'].apply(FMScore, args=('frequency',quantiles))\ncardrfm['v_quantile'] = cardrfm['value'].apply(FMScore, args=('value',quantiles))\ncardrfm['RFMindex'] = cardrfm.r_quantile.map(str)+cardrfm.f_quantile.map(str)+cardrfm.v_quantile.map(str)                       \ncardrfm['RFMScore'] = cardrfm.r_quantile+cardrfm.f_quantile+cardrfm.v_quantile \ncardrfm.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"344ae151405262e7e2855e601b57272a30078c59"},"cell_type":"markdown","source":"RFMindex is obtained by combining the recency ,frequency and value quantiles whereas RFMScore is by adding all three together . For RFM score equal weights are given to all 3 and will help in having a continous scores in a range. RFM index will be sparse. RFMindex will be convertred into contionous value s from 0 as below."},{"metadata":{"trusted":true,"_uuid":"ff8f32c3e8062c1e0c545c98b234a47d09b6c36b"},"cell_type":"code","source":"cardrfm.RFMindex= cardrfm.RFMindex.astype(int)\nRFMindex=pd.DataFrame(np.unique(np.sort(cardrfm.RFMindex)),columns=['RFMindex'])\nRFMindex.index=RFMindex.index.set_names(['RFMIndex'])\nRFMindex.reset_index(inplace=True)\ncardrfm =pd.merge(cardrfm,RFMindex,on='RFMindex',how='left')\ncardrfm.drop(columns=\"RFMindex\",inplace=True) \ncardrfm.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b950600b267e13a23770c1591cc837e4eeacf975"},"cell_type":"markdown","source":"We will apply the RFM to new merchant transactions also "},{"metadata":{"trusted":true,"_uuid":"de09b4deb35bdf1396a104362523805835bf7d09"},"cell_type":"code","source":"cardrfm_new = new.groupby('card_id').agg({'card_id': 'count','purchase_date': 'max','purchase_amount': 'sum'})\ncardrfm_new.rename(columns={'card_id' : 'frequency_new','purchase_date': 'date_recency','purchase_amount': 'value_new'},inplace=True)\ncardrfm_new = pd.merge(cardrfm_new,cardreference.iloc[:,0:2],on='card_id',how='left')\ncardrfm_new['recency_new'] = cardrfm_new['reference_date'] - cardrfm_new['date_recency'] + datetime.timedelta(days=61)\ncardrfm_new.recency_new= cardrfm_new.recency_new/(24*np.timedelta64(1, 'h')) # to convert to day fractions\ncardrfm_new.drop(columns=['date_recency','reference_date'],inplace=True)\nnewquantiles = cardrfm_new.quantile(q=[0.011,0.05,0.25,0.5,0.75,0.95,0.989])\nnewquantiles = newquantiles.to_dict()\nnewquantiles","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1837e618eb56588314a29603acadc0ed2696839a"},"cell_type":"code","source":"cardrfm_new['rnew_quantile'] = cardrfm_new['recency_new'].apply(RScore, args=('recency_new',newquantiles))\ncardrfm_new['fnew_quantile'] = cardrfm_new['frequency_new'].apply(FMScore, args=('frequency_new',newquantiles))\ncardrfm_new['vnew_quantile'] = cardrfm_new['value_new'].apply(FMScore, args=('value_new',newquantiles))\ncardrfm_new['RFMnewindex'] = cardrfm_new.rnew_quantile.map(str)+cardrfm_new.fnew_quantile.map(str)+cardrfm_new.vnew_quantile.map(str)                       \ncardrfm_new['RFMnewScore'] = cardrfm_new.rnew_quantile+cardrfm_new.fnew_quantile+cardrfm_new.vnew_quantile \ncardrfm_new.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fd24cd89f133f5c1342862609f356e76b62890f1"},"cell_type":"code","source":"cardrfm_new.RFMnewindex= cardrfm_new.RFMnewindex.astype(int)\nRFMnewindex=pd.DataFrame(np.unique(np.sort(cardrfm_new.RFMnewindex)),columns=['RFMnewindex'])\nRFMnewindex.index=RFMnewindex.index.set_names(['RFMnewIndex'])\nRFMnewindex.reset_index(inplace=True)\ncardrfm_new =pd.merge(cardrfm_new,RFMnewindex,on='RFMnewindex',how='left')\ncardrfm_new.drop(columns=\"RFMnewindex\",inplace=True) \ncardrfm_new.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bd53002762478facb7a58a3859ef2601831b7e60"},"cell_type":"markdown","source":"An interaction varaible feature_comb is created from feature_1,feature_2 ,feature_3 and is made continous as below"},{"metadata":{"trusted":true,"_uuid":"5990c117d253eb14f8dc432fec43a90cb73182f8"},"cell_type":"code","source":"for df in [train,test]:\n    df['first_active_month'] = pd.to_datetime(df['first_active_month'])\n    df['year'] = df['first_active_month'].dt.year\n    df['month'] = df['first_active_month'].dt.month\n    df['weekday'] = df['first_active_month'].dt.weekday\n    df['feature_comb'] = df.feature_1.map(str) + df.feature_2.map(str) + df.feature_3.map(str)\n    df['feature_comb']= df['feature_comb'].astype(int)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2de3f6887cb1aaa0f5802b25f3e7a6099f3bf337"},"cell_type":"code","source":"featureindex=pd.DataFrame(np.unique(np.sort(train['feature_comb'])),columns=['feature_comb'])\nfeatureindex.index=featureindex.index.set_names(['feature_comb_index'])\nfeatureindex.reset_index(inplace=True)\ntrain =pd.merge(train,featureindex,on='feature_comb',how='left')\ntrain.drop(columns=\"feature_comb\",inplace=True) \ntest =pd.merge(test,featureindex,on='feature_comb',how='left')\ntest.drop(columns=\"feature_comb\",inplace=True)\ntest.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"be8632b590d55b9689f8b2f7f96530f6a6898ff8"},"cell_type":"code","source":"train_df= pd.merge(train,cardrfm,on='card_id',how='left')\ntrain_df= pd.merge(train_df,cardrfm_new,on='card_id',how='left')\ntrain_df= pd.merge(train_df,cardreference,on='card_id',how='left')\ntrain_df['frequency_new_hist'] =train_df.frequency_new/train_df.frequency\ntrain_df['value_new_hist'] =train_df.value_new/train_df.value\ntrain_df['recency_new_hist'] =train_df.recency_new/train_df.recency\ntrain_df['elapsedtime']= (train_df['reference_date'] - train_df['first_active_month']).dt.days\ntest_df= pd.merge(test,cardrfm,on='card_id',how='left')\ntest_df= pd.merge(test_df,cardrfm_new,on='card_id',how='left')\ntest_df= pd.merge(test_df,cardreference,on='card_id',how='left')\ntest_df['frequency_new_hist'] =test_df.frequency_new/test_df.frequency\ntest_df['value_new_hist'] =test_df.value_new/test_df.value\ntest_df['recency_new_hist'] =test_df.recency_new/test_df.recency\ntest_df['elapsedtime']= (test_df['reference_date'] - test_df['first_active_month']).dt.days\ntrain_df['first_active_month'] = pd.DatetimeIndex(train_df['first_active_month']).\\\n                                      astype(np.int64) * 1e-9\ntrain_df['reference_date'] = pd.DatetimeIndex(train_df['reference_date']).\\\n                                      astype(np.int64) * 1e-9\ntest_df['first_active_month'] = pd.DatetimeIndex(test_df['first_active_month']).\\\n                                      astype(np.int64) * 1e-9\ntest_df['reference_date'] = pd.DatetimeIndex(test_df['reference_date']).\\\n                                    astype(np.int64) * 1e-9\ntest_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8639005ea85d56b0deb436d29e759fb8eecb7519"},"cell_type":"code","source":"train_df.to_csv(\"trainrfm.csv\",index=False)\ntest_df.to_csv(\"testrfm.csv\",index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e859c3d11764731515d4a87c7fa557ef2885148b"},"cell_type":"code","source":"target = train_df.target\ntrain_df= train_df.drop(['card_id','target'],axis=1)\ncard_id = test_df['card_id']\ntest_df= test_df.drop(['card_id'],axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d244a4b35543234fce31b18997aefdbe51acde9c"},"cell_type":"code","source":"features = [c for c in train_df.columns if c not in ['card_id','target']]\ncategorical_feats = ['feature_1','feature_2','feature_3','feature_comb_index','year', 'month','weekday','category_month_lag','r_quantile','f_quantile','v_quantile','RFMScore','RFMIndex','RFMIndex','rnew_quantile','fnew_quantile','vnew_quantile','RFMnewScore','RFMnewIndex','RFMIndex']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"90cb5471373df41ada46283e0806e5ce8bafd0bf"},"cell_type":"markdown","source":"**Lets build a model based on this few features to test if RFM is of any importance.**"},{"metadata":{"trusted":true,"_uuid":"6c98311e7a52a5831480401ab78b41efda77708c"},"cell_type":"code","source":"folds = StratifiedKFold(n_splits=5, shuffle=True, random_state=15)\noof1 = np.zeros(len(train_df))\npredictions1 = np.zeros(len(test_df))\nstart = time.time()\nfeature_importance_df1 = pd.DataFrame()\n\nfor fold_, (trn_idx, val_idx) in enumerate(folds.split(train_df.values,train_df['RFMScore'].values)):\n     print(\"fold n°{}\".format(fold_))\n     trn_data = lgb.Dataset(train_df.iloc[trn_idx][features], label=target.iloc[trn_idx], categorical_feature=categorical_feats)\n     val_data = lgb.Dataset(train_df.iloc[val_idx][features], label=target.iloc[val_idx], categorical_feature=categorical_feats)\n\n     num_round = 10000\n#     params= param\n     params ={\n                 'task': 'train',\n                 'boosting': 'goss',\n                 'objective': 'regression',\n                 'metric': 'rmse',\n                 'learning_rate': 0.01,\n                 'subsample': 0.9855232997390695,\n                 'max_depth': 7,\n                 'top_rate': 0.9064148448434349,\n                 'num_leaves': 63,\n                 'min_child_weight': 41.9612869171337,\n                 'other_rate': 0.0721768246018207,\n                 'reg_alpha': 9.677537745007898,\n                 'colsample_bytree': 0.5665320670155495,\n                 'min_split_gain': 9.820197773625843,\n                 'reg_lambda': 8.2532317400459,\n                 'min_data_in_leaf': 21,\n                 'verbose': -1,\n                 'seed':int(2**fold_),\n                 'bagging_seed':int(2**fold_),\n                 'drop_seed':int(2**fold_)}\n     \n    \n     clf1 = lgb.train(params, trn_data, num_round, valid_sets = [trn_data, val_data], verbose_eval=-1, early_stopping_rounds = 200)\n     oof1[val_idx] = clf1.predict(train_df.iloc[val_idx][features], num_iteration=clf1.best_iteration)\n    \n     fold_importance_df1 = pd.DataFrame()\n     fold_importance_df1[\"feature\"] = features\n     fold_importance_df1[\"importance\"] = clf1.feature_importance()\n     fold_importance_df1[\"fold\"] = fold_ + 1\n     feature_importance_df1 = pd.concat([feature_importance_df1, fold_importance_df1], axis=0)\n    \n     predictions1 += clf1.predict(test_df[features], num_iteration=clf1.best_iteration) / folds.n_splits\nprint(\"CV score: {:<8.5f}\".format(mean_squared_error(oof1, target)**0.5))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ecfd2e821f9a7e42258ce258d4b316504e7841d7"},"cell_type":"code","source":"cols = (feature_importance_df1[[\"feature\", \"importance\"]]\n         .groupby(\"feature\")\n         .mean()\n         .sort_values(by=\"importance\", ascending=False)[:1000].index)\n\nbest_features1 = feature_importance_df1.loc[feature_importance_df1.feature.isin(cols)]\n\nplt.figure(figsize=(14,25))\nsns.barplot(x=\"importance\",\n             y=\"feature\",\n             data=best_features1.sort_values(by=\"importance\",\n                                            ascending=False))\nplt.title('LightGBM Features (avg over folds)')\nplt.tight_layout()\nplt.savefig('lgbm_importances1.png')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d60d276b7a6b60d31a74eda51ff04f03e69d004f"},"cell_type":"markdown","source":"**RFM features are on top in  importance as per the model. **"}],"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}