{"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":"markdown","source":"## Objective","metadata":{}},{"cell_type":"markdown","source":"**Predicting revenue generated by each user** <br>\nThe goal with this report here is to show a clear way and necessary steps needed to be taken for similar analysis tasks. Hope you find it helpful!\n<br>\nThis is a notebook with explanation for basic analysis. <br> \nI would appreciate your comments and feedback, if you see some mistakes or have suggestions for improvement of this note book. Otherwise, if you find it helpful throw me an upvote, so I'll cheer my day! :-)","metadata":{}},{"cell_type":"markdown","source":"## 1. Data Prepration","metadata":{}},{"cell_type":"markdown","source":"### a. Loading libraries","metadata":{}},{"cell_type":"code","source":"import time\nimport json\nimport numpy as np\nimport pandas as pd\nfrom pandas import json_normalize\n\nimport datetime\n\nimport seaborn as sns\nimport matplotlib.pyplot as plt\n\nimport lightgbm as lgb\n\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.metrics import mean_squared_error\n\nimport warnings\nwarnings.filterwarnings('ignore')","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:35:51.866864Z","iopub.execute_input":"2021-10-08T12:35:51.867364Z","iopub.status.idle":"2021-10-08T12:35:54.906146Z","shell.execute_reply.started":"2021-10-08T12:35:51.867223Z","shell.execute_reply":"2021-10-08T12:35:54.905391Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### b. Loading the dataset","metadata":{}},{"cell_type":"markdown","source":"For loading the dataframe with json fields I have used a snippet of the code from anther notebook. You can find the link in the references.[2]","metadata":{}},{"cell_type":"code","source":"def load_df(csv_path, nrows = None):\n    json_cols = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    df = pd.read_csv(csv_path,\n                     #converters are dict of functions for converting values in certain columns. Keys can either be integers or column labels.\n                     #json.loads() method can be used to parse a valid JSON string and convert it into a Python Dictionary.\n                     #It is mainly used for deserializing native string, byte, or byte array which consists of JSON data into Python Dictionary.\n                     converters = {col: json.loads for col in json_cols},                                                                         \n                         dtype = {'fullVisitorId': 'str'}, # Important!!\n                         nrows = nrows)\n    for col in json_cols:\n        # for each column, flatten data frame such that the values of a single col are spread in different cols\n        # This will use subcol as names of flat_col.columns\n        flat_col = json_normalize(df[col])\n        # Name the columns in this flatten data frame as col.subcol for tracability\n        flat_col.columns = [f\"{col}.{subcol}\" for subcol in flat_col.columns]\n        # Drop the json_col and instead add the new flat_col\n        df = df.drop(col, axis = 1).merge(flat_col, right_index = True, left_index = True)\n    return df","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:35:54.909512Z","iopub.execute_input":"2021-10-08T12:35:54.909728Z","iopub.status.idle":"2021-10-08T12:35:55.043954Z","shell.execute_reply.started":"2021-10-08T12:35:54.909703Z","shell.execute_reply":"2021-10-08T12:35:55.043217Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"csv_train_path = '../input/ga-customer-revenue-prediction/train_v2.csv'\ncsv_test_path = '../input/ga-customer-revenue-prediction/test_v2.csv'","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:35:55.045184Z","iopub.execute_input":"2021-10-08T12:35:55.045577Z","iopub.status.idle":"2021-10-08T12:35:55.057220Z","shell.execute_reply.started":"2021-10-08T12:35:55.045540Z","shell.execute_reply":"2021-10-08T12:35:55.056607Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ntrain = load_df(csv_train_path, nrows = 120000)\ntest = load_df(csv_test_path, nrows = None)\ntrain.shape, test.shape","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:35:55.058641Z","iopub.execute_input":"2021-10-08T12:35:55.058896Z","iopub.status.idle":"2021-10-08T12:40:04.117308Z","shell.execute_reply.started":"2021-10-08T12:35:55.058865Z","shell.execute_reply":"2021-10-08T12:40:04.116593Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.loc[:2]","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:04.119614Z","iopub.execute_input":"2021-10-08T12:40:04.120363Z","iopub.status.idle":"2021-10-08T12:40:04.174947Z","shell.execute_reply.started":"2021-10-08T12:40:04.120325Z","shell.execute_reply":"2021-10-08T12:40:04.174276Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 2. Data Summery","metadata":{}},{"cell_type":"markdown","source":"### a. Data visuzalization","metadata":{}},{"cell_type":"code","source":"cols = train.columns\ncols","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:04.176204Z","iopub.execute_input":"2021-10-08T12:40:04.180656Z","iopub.status.idle":"2021-10-08T12:40:04.190625Z","shell.execute_reply.started":"2021-10-08T12:40:04.180617Z","shell.execute_reply":"2021-10-08T12:40:04.189776Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train['totals.transactionRevenue'] = train['totals.transactionRevenue'].astype('float')\n#To have it as a data frame with fullVisitorId and totals.transactionRevenue we need to reset_index(). Otherwise, it would return a serie\ntarget = train.groupby('fullVisitorId')['totals.transactionRevenue'].sum().reset_index()\ntarget[:4]","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:04.192055Z","iopub.execute_input":"2021-10-08T12:40:04.193349Z","iopub.status.idle":"2021-10-08T12:40:04.608485Z","shell.execute_reply.started":"2021-10-08T12:40:04.193306Z","shell.execute_reply":"2021-10-08T12:40:04.607822Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize = (10,5))\nax = sns.scatterplot(x = range(0, len(target)), y = np.log1p(target['totals.transactionRevenue']), color = 'g', marker = 'o')\nax.set_xlabel('User index')\nax.set_ylabel('Transaction revenue (log)')\nax.set_title('Natural logarithm of agregate revenue generated from each user')","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:04.612425Z","iopub.execute_input":"2021-10-08T12:40:04.613071Z","iopub.status.idle":"2021-10-08T12:40:05.265933Z","shell.execute_reply.started":"2021-10-08T12:40:04.613024Z","shell.execute_reply":"2021-10-08T12:40:05.265195Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.fullVisitorId.nunique()","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:05.267393Z","iopub.execute_input":"2021-10-08T12:40:05.267665Z","iopub.status.idle":"2021-10-08T12:40:05.321992Z","shell.execute_reply.started":"2021-10-08T12:40:05.267632Z","shell.execute_reply":"2021-10-08T12:40:05.320910Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"There are 4000 records for 3710 users.","metadata":{}},{"cell_type":"markdown","source":"### b. Visualization for users generating revenue (target = 1)","metadata":{}},{"cell_type":"code","source":"ax = sns.countplot(pd.cut(target['totals.transactionRevenue'],[-1,0,1e9]))\nax.set_xticklabels(['Users with 0$ transaction','Users with positive transacton'])\nax.set_xlabel('Revenue', fontsize = 14)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:05.323508Z","iopub.execute_input":"2021-10-08T12:40:05.323845Z","iopub.status.idle":"2021-10-08T12:40:05.653614Z","shell.execute_reply.started":"2021-10-08T12:40:05.323807Z","shell.execute_reply":"2021-10-08T12:40:05.652638Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"A very low percentage of users contribute in generating revenue. Let's have a closer look in them. To see how many they are and how do they contribute to the revenue generation.","metadata":{}},{"cell_type":"code","source":"temp_rev = target[target['totals.transactionRevenue'] != 0]\ntemp_rev.sort_values(by = 'totals.transactionRevenue', ascending = False)[:4]","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:05.655620Z","iopub.execute_input":"2021-10-08T12:40:05.655909Z","iopub.status.idle":"2021-10-08T12:40:05.671840Z","shell.execute_reply.started":"2021-10-08T12:40:05.655883Z","shell.execute_reply":"2021-10-08T12:40:05.670574Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('number of users with transaction:',len(temp_rev))\nprint('number of total users:',len(target))\nprint('number of total transactions:', len(train))\nprint(np.round(np.sum(target['totals.transactionRevenue'] != 0)*100 / len(target),2), 'percent of users generate revenue!!')","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:05.673112Z","iopub.execute_input":"2021-10-08T12:40:05.673533Z","iopub.status.idle":"2021-10-08T12:40:05.681796Z","shell.execute_reply.started":"2021-10-08T12:40:05.673469Z","shell.execute_reply":"2021-10-08T12:40:05.680845Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ax = sns.countplot(pd.cut(temp_rev['totals.transactionRevenue'],[0,1e7,0.5e8,1e9]))\nax.set_xticklabels(['0 - 10M','10M - 50M','50M - 1B'])\nax.set_xlabel('Total sum of transaction from users contributing to revenue generation')","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:05.683217Z","iopub.execute_input":"2021-10-08T12:40:05.683567Z","iopub.status.idle":"2021-10-08T12:40:05.885121Z","shell.execute_reply.started":"2021-10-08T12:40:05.683531Z","shell.execute_reply":"2021-10-08T12:40:05.884437Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"16 out of 24 users have had a transaction between 10-50M dollars","metadata":{}},{"cell_type":"code","source":"train_trans_rev = train[train['totals.transactionRevenue'] > 0]\ntrain_trans_non_rev = train[train['totals.transactionRevenue'].isna()]\nlen(train_trans_rev), len(train_trans_non_rev)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:05.888959Z","iopub.execute_input":"2021-10-08T12:40:05.889170Z","iopub.status.idle":"2021-10-08T12:40:06.360453Z","shell.execute_reply.started":"2021-10-08T12:40:05.889144Z","shell.execute_reply":"2021-10-08T12:40:06.359650Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### c. Some primary raw visualization\nTo see the distribution of different values for different features, I have added the count plot for some of the columns. Here, I first put all the columns from the trianing data, but then removed the ones that doesn't give an informative count plot. Therefore, only information about some of the columns are visualized here.","metadata":{}},{"cell_type":"code","source":"some_cols = ['customDimensions','channelGrouping', 'socialEngagementType', 'visitNumber',\n       'device.browser', 'device.operatingSystem', 'device.isMobile','device.deviceCategory',\n       'geoNetwork.continent', 'geoNetwork.subContinent', 'geoNetwork.country',\n       'geoNetwork.region', 'geoNetwork.metro', 'geoNetwork.city','geoNetwork.networkDomain', \n       'totals.visits', 'totals.sessionQualityDim', 'totals.newVisits', 'totals.timeOnSite', 'totals.hits',\n       'trafficSource.source', 'trafficSource.medium',\n       'trafficSource.isTrueDirect', 'trafficSource.referralPath', 'trafficSource.campaign'\n       ]\nfig, ax = plt.subplots(25, 2, figsize = (30,80))\n#fig.tight_layout()\nplt.subplots_adjust(left = 0.1,\n                    bottom = 0.1, \n                    right = 0.9, \n                    top = 0.9, \n                    wspace = 0.4, \n                    hspace = 0.4)\ni = 0\nfor col in some_cols:\n    sns.countplot(data = train_trans_rev, y = col, ax = ax[i,0], order = train_trans_rev[col].value_counts().iloc[:6].index) # Only the first top 6 value counts are shown\n    ax[i,0].tick_params(axis = 'both', which = 'major', labelsize = 16)\n    ax[i,0].set_ylabel(col, fontsize = 16)\n    sns.countplot(data = train_trans_non_rev, y = col, ax = ax[i,1], order = train_trans_non_rev[col].value_counts().iloc[:6].index) # Only the first top 6 value counts are shown\n    ax[i,1].tick_params(axis = 'both', which = 'major', labelsize = 16)\n    ax[i,1].set_ylabel('', fontsize = 16)\n    i += 1\nfig.suptitle('Revenue generating users [left] vs non revenue generating users [Right]', fontsize = 20)\nfig.subplots_adjust(top = 0.97)\n","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:06.361863Z","iopub.execute_input":"2021-10-08T12:40:06.362665Z","iopub.status.idle":"2021-10-08T12:40:17.824168Z","shell.execute_reply.started":"2021-10-08T12:40:06.362628Z","shell.execute_reply":"2021-10-08T12:40:17.823419Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### some insights from comparing the results between the users generating revenue vs other users\n- Users generating revenue use Macintosh more than other users\n- Their channel grouping is referral rather than organic search\n- They use more Chrome\n- They usually visit more than once\n- Their network domain is less unknown.unkown\n- They have higher time on site\n- They have higher session quality dimension(*)\n- Their total hits a lot higher\n- They connect less by mobile devices or tablets and use more desktop\n- They are mostly from US, rather than Europe, Asia or Africa\n- They are usually connecting from California and NewYork\n- They usually connect directly and less through google\n\n(*)For calculating session quality dimension, user engagement is evaluated for each session, and the resulting proximity to conversion is expressed as a score of 1-100 for each session during the date range, with 1 being the farthest from and 100 being the closest to a transaction.\n\nWe also see some columns with constant values. We'll remove them when doing the data cleaning.","metadata":{}},{"cell_type":"markdown","source":"### d. Visualizing missing values","metadata":{}},{"cell_type":"code","source":"missing_val = pd.DataFrame()\nfor col in cols:\n    na_count = train[col].isna().sum()\n    if na_count != 0:\n        missing_val.loc[col, 'NaN_val(%)'] = na_count/len(train)*100\nprint('Number of columns with missing values in train set:', len(missing_val))\nmissing_val.sort_values('NaN_val(%)', inplace = True)\nmissing_val[:5]","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:17.825134Z","iopub.execute_input":"2021-10-08T12:40:17.825351Z","iopub.status.idle":"2021-10-08T12:40:18.557898Z","shell.execute_reply.started":"2021-10-08T12:40:17.825323Z","shell.execute_reply":"2021-10-08T12:40:18.557064Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(figsize = (12, 8))\nsns.barplot(data = missing_val, x = 'NaN_val(%)', y = missing_val.index , ax = ax)\nax.set_xlabel('Missing Data (%)')\n_ = ax.bar_label(ax.containers[0])","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:18.559168Z","iopub.execute_input":"2021-10-08T12:40:18.559520Z","iopub.status.idle":"2021-10-08T12:40:19.024261Z","shell.execute_reply.started":"2021-10-08T12:40:18.559473Z","shell.execute_reply":"2021-10-08T12:40:19.023337Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We see some columns with more than 50% missing values. We'll later remove them in data cleaning. <br>\ntotals.newVisits shows to have only nan and 1 as values. This is not informative. We'll remove that too.\n","metadata":{}},{"cell_type":"markdown","source":"### e. Constant columns","metadata":{}},{"cell_type":"code","source":"const_cols = []\nfor col in cols:\n     if train[col].nunique() == 1: const_cols.append(col)\nprint('Number of columns with constant values:',len(const_cols))\nconst_cols","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:19.025468Z","iopub.execute_input":"2021-10-08T12:40:19.025817Z","iopub.status.idle":"2021-10-08T12:40:25.073792Z","shell.execute_reply.started":"2021-10-08T12:40:19.025779Z","shell.execute_reply":"2021-10-08T12:40:25.072986Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"*totals.bounces, totals.newVisits, trafficSourcs.isTrueDirect* were among the missing value data. Lets have a closer look to them. ","metadata":{}},{"cell_type":"code","source":"train['totals.bounces'].unique()","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:25.075208Z","iopub.execute_input":"2021-10-08T12:40:25.075509Z","iopub.status.idle":"2021-10-08T12:40:25.092870Z","shell.execute_reply.started":"2021-10-08T12:40:25.075458Z","shell.execute_reply":"2021-10-08T12:40:25.091836Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 3. Data Cleaning","metadata":{}},{"cell_type":"markdown","source":"### a. Defining the target ","metadata":{}},{"cell_type":"code","source":"y_train_ = train['totals.transactionRevenue']\ny_train_.fillna(0, inplace = True)\ny_train_.astype('float')","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:25.094476Z","iopub.execute_input":"2021-10-08T12:40:25.094810Z","iopub.status.idle":"2021-10-08T12:40:25.109361Z","shell.execute_reply.started":"2021-10-08T12:40:25.094775Z","shell.execute_reply":"2021-10-08T12:40:25.108573Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### b. Removing columns with missing values","metadata":{}},{"cell_type":"code","source":"missing_val[-4:]","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:25.110432Z","iopub.execute_input":"2021-10-08T12:40:25.111295Z","iopub.status.idle":"2021-10-08T12:40:25.121687Z","shell.execute_reply.started":"2021-10-08T12:40:25.111257Z","shell.execute_reply":"2021-10-08T12:40:25.120922Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This exact amount of threshold of Nan_value(%) for removing columns with missing values is chosen based on the plot from missing value. but roughly around 50% seems to be a reasonable choice. In the code block below I chose 48 as threshod. <br> Of course we should be carefull about which columns (features) we are removing. A way for keeping the columns is to replace the missing values with a reasonable replacement values. In this case for example we replaced the missing values in the transaction revevenues with 0.","metadata":{}},{"cell_type":"code","source":"for col in missing_val.index:\n    if missing_val.loc[col, 'NaN_val(%)'] > 48: \n        train.drop(col, axis = 1, inplace = True)\n    else: \n        train[col].fillna('0', inplace = True)\n        test[col].fillna('0', inplace = True)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:25.123128Z","iopub.execute_input":"2021-10-08T12:40:25.123431Z","iopub.status.idle":"2021-10-08T12:40:27.860510Z","shell.execute_reply.started":"2021-10-08T12:40:25.123398Z","shell.execute_reply":"2021-10-08T12:40:27.859674Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### c. Removing columns with constant values","metadata":{}},{"cell_type":"code","source":"for col in const_cols:\n    if col not in missing_val.index:\n        train.drop(col, axis = 1, inplace = True)\n\nprint('Number of columns in the training set',len(train.columns))","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:27.861779Z","iopub.execute_input":"2021-10-08T12:40:27.862043Z","iopub.status.idle":"2021-10-08T12:40:30.199023Z","shell.execute_reply.started":"2021-10-08T12:40:27.862012Z","shell.execute_reply":"2021-10-08T12:40:30.198239Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.info()","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:30.200114Z","iopub.execute_input":"2021-10-08T12:40:30.200359Z","iopub.status.idle":"2021-10-08T12:40:30.527135Z","shell.execute_reply.started":"2021-10-08T12:40:30.200325Z","shell.execute_reply":"2021-10-08T12:40:30.526311Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### d. Removing irrelavant features for building a model","metadata":{}},{"cell_type":"code","source":"irrelavant = ['fullVisitorId', 'visitId', 'trafficSource.campaign']\nfor col in irrelavant:\n    train.drop(col, axis = 1, inplace = True)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:30.528215Z","iopub.execute_input":"2021-10-08T12:40:30.529103Z","iopub.status.idle":"2021-10-08T12:40:30.763115Z","shell.execute_reply.started":"2021-10-08T12:40:30.529059Z","shell.execute_reply":"2021-10-08T12:40:30.762302Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 4. Feature Engineering and outliers","metadata":{}},{"cell_type":"markdown","source":"### a. Handling datetime data types\nLet's check the format of visitStartTime","metadata":{}},{"cell_type":"code","source":"train['visitStartTime']","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:30.764613Z","iopub.execute_input":"2021-10-08T12:40:30.764912Z","iopub.status.idle":"2021-10-08T12:40:30.773989Z","shell.execute_reply.started":"2021-10-08T12:40:30.764876Z","shell.execute_reply":"2021-10-08T12:40:30.772918Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for df in [train, test]:\n    print(df['visitStartTime'][0])\n    df['visitStartTime'] = pd.to_datetime(df['visitStartTime'], unit='s')\n    df['rec_dayofweek'] = df['visitStartTime'].dt.dayofweek\n    df['rec_hours'] = df['visitStartTime'].dt.hour\n    df['rec_dayofmonth'] = df['visitStartTime'].dt.day\n    print(df['visitStartTime'][0], df['rec_dayofweek'][0], train['rec_hours'][0], df['rec_dayofmonth'][0])\n    df.drop('visitStartTime', axis = 1, inplace = True)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:30.775685Z","iopub.execute_input":"2021-10-08T12:40:30.776063Z","iopub.status.idle":"2021-10-08T12:40:33.265217Z","shell.execute_reply.started":"2021-10-08T12:40:30.776026Z","shell.execute_reply":"2021-10-08T12:40:33.264438Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### b. Converting the categorical columns to numerical type\nIt is possible to use factorize method as well. Here we have oused label encoder to convert categorical data into numerical.","metadata":{}},{"cell_type":"code","source":"le = LabelEncoder()\nprint('Columns that are converted to numerical values with label encodedr:')\nfor col in train.columns:\n    if train[col].dtype == 'O':\n        print(col)\n        #print(col, train[col].unique())\n        train.loc[:, col] = le.fit_transform(train.loc[:, col])\n        test.loc[:, col] = le.fit_transform(test.loc[:, col])","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:33.266446Z","iopub.execute_input":"2021-10-08T12:40:33.266723Z","iopub.status.idle":"2021-10-08T12:40:56.067551Z","shell.execute_reply.started":"2021-10-08T12:40:33.266690Z","shell.execute_reply":"2021-10-08T12:40:56.066708Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in train.columns:\n    train[col] = train[col].astype('float')","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:56.069377Z","iopub.execute_input":"2021-10-08T12:40:56.069679Z","iopub.status.idle":"2021-10-08T12:40:56.107570Z","shell.execute_reply.started":"2021-10-08T12:40:56.069644Z","shell.execute_reply":"2021-10-08T12:40:56.106856Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.info()","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:56.109244Z","iopub.execute_input":"2021-10-08T12:40:56.109588Z","iopub.status.idle":"2021-10-08T12:40:56.136897Z","shell.execute_reply.started":"2021-10-08T12:40:56.109553Z","shell.execute_reply":"2021-10-08T12:40:56.136007Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We'll remove all the columns that are in test set but not in train set. ","metadata":{}},{"cell_type":"code","source":"fullvisitorid = []\nfor col in test.columns:\n    if col == 'fullVisitorId':\n        fullvisitorid = test[col] \n    if col not in train.columns:\n        test.drop(col, axis = 1, inplace = True)\ntest.info()","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:40:56.138575Z","iopub.execute_input":"2021-10-08T12:40:56.138916Z","iopub.status.idle":"2021-10-08T12:41:04.203079Z","shell.execute_reply.started":"2021-10-08T12:40:56.138875Z","shell.execute_reply":"2021-10-08T12:41:04.202355Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in test.columns:\n    test[col] = test[col].astype('float')","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:04.204806Z","iopub.execute_input":"2021-10-08T12:41:04.205225Z","iopub.status.idle":"2021-10-08T12:41:04.481650Z","shell.execute_reply.started":"2021-10-08T12:41:04.205189Z","shell.execute_reply":"2021-10-08T12:41:04.480903Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 5. Model Training","metadata":{}},{"cell_type":"markdown","source":"### Defining the model","metadata":{}},{"cell_type":"code","source":"model = lgb.LGBMRegressor(\n        num_leaves = 31,  #(default = 31) – Maximum tree leaves for base learners.\n        learning_rate = 0.03, #(default = 0.1) – Boosting learning rate. You can use callbacks parameter of fit method to shrink/adapt learning rate in training using \n                              #reset_parameter callback. Note, that this will ignore the learning_rate argument in training.\n        n_estimators = 1000, #(default = 100) – Number of boosted trees to fit.\n        subsample = .9, #(default = 1.) – Subsample ratio of the training instance.\n        colsample_bytree = .9, #(default = 1.) – Subsample ratio of columns when constructing each tree\n        random_state = 34\n)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:04.483196Z","iopub.execute_input":"2021-10-08T12:41:04.483479Z","iopub.status.idle":"2021-10-08T12:41:04.488696Z","shell.execute_reply.started":"2021-10-08T12:41:04.483444Z","shell.execute_reply":"2021-10-08T12:41:04.487745Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Splitting the train set and validation set\nSince in real cases we have access to the data up to a certain date, and then we should predict the transactions for future, we'll pick a date for splitting to data into training set and validaton set. We will later remove the column related to date.","metadata":{}},{"cell_type":"code","source":"train['date'].min(), train['date'].max()","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:04.490355Z","iopub.execute_input":"2021-10-08T12:41:04.490651Z","iopub.status.idle":"2021-10-08T12:41:04.500806Z","shell.execute_reply.started":"2021-10-08T12:41:04.490618Z","shell.execute_reply":"2021-10-08T12:41:04.500142Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"x_train = train[ train['date'] < 20171101 ]\nx_valid = train[ train['date'] >= 20171101 ]\nprint('Number of samples in train set:', len(x_train))\nprint('Number of samples in validation set:',len(x_valid))","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:04.502046Z","iopub.execute_input":"2021-10-08T12:41:04.502451Z","iopub.status.idle":"2021-10-08T12:41:04.532535Z","shell.execute_reply.started":"2021-10-08T12:41:04.502415Z","shell.execute_reply":"2021-10-08T12:41:04.531785Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"y_train_len = len(x_train)\ny_train = y_train_[:y_train_len]\ny_valid = y_train_[y_train_len:]","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:04.533935Z","iopub.execute_input":"2021-10-08T12:41:04.534344Z","iopub.status.idle":"2021-10-08T12:41:04.538881Z","shell.execute_reply.started":"2021-10-08T12:41:04.534308Z","shell.execute_reply":"2021-10-08T12:41:04.538131Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"x_train.drop('date', axis = 1, inplace = True)\nx_valid.drop('date', axis = 1, inplace = True)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:04.540193Z","iopub.execute_input":"2021-10-08T12:41:04.540625Z","iopub.status.idle":"2021-10-08T12:41:04.553909Z","shell.execute_reply.started":"2021-10-08T12:41:04.540588Z","shell.execute_reply":"2021-10-08T12:41:04.553196Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Trainnig the model","metadata":{}},{"cell_type":"markdown","source":"To make sure we have the same length for x_train and y_train as well as x_valid and y_valid","metadata":{}},{"cell_type":"code","source":"print(len(x_train), len(y_train))\nprint(len(x_valid), len(y_valid))","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:04.555053Z","iopub.execute_input":"2021-10-08T12:41:04.555521Z","iopub.status.idle":"2021-10-08T12:41:04.564402Z","shell.execute_reply.started":"2021-10-08T12:41:04.555497Z","shell.execute_reply":"2021-10-08T12:41:04.563457Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":" model.fit(\n        x_train, np.log1p(y_train),\n        eval_set = [(x_valid, np.log1p(y_valid))],\n        early_stopping_rounds = 50,\n        verbose = 100,\n        eval_metric = 'rmse'\n    )","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:04.566164Z","iopub.execute_input":"2021-10-08T12:41:04.566549Z","iopub.status.idle":"2021-10-08T12:41:05.230435Z","shell.execute_reply.started":"2021-10-08T12:41:04.566517Z","shell.execute_reply":"2021-10-08T12:41:05.229584Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Feature importance","metadata":{}},{"cell_type":"code","source":"feat_impr = pd.DataFrame()\nfeat_impr['feature'] = x_train.columns\nfeat_impr['importance'] = model.booster_.feature_importance(importance_type = 'gain')\nfeat_impr.sort_values(by = 'importance', ascending = False)[:10]","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:05.232007Z","iopub.execute_input":"2021-10-08T12:41:05.234668Z","iopub.status.idle":"2021-10-08T12:41:05.250900Z","shell.execute_reply.started":"2021-10-08T12:41:05.234635Z","shell.execute_reply":"2021-10-08T12:41:05.250059Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize = (8,5))\nsns.barplot(x = 'importance', y = 'feature', data = feat_impr.sort_values('importance', ascending = False)[:15])","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:05.256128Z","iopub.execute_input":"2021-10-08T12:41:05.256339Z","iopub.status.idle":"2021-10-08T12:41:05.576445Z","shell.execute_reply.started":"2021-10-08T12:41:05.256314Z","shell.execute_reply":"2021-10-08T12:41:05.575702Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 6. Inference\n### a. For each record (row) of data","metadata":{}},{"cell_type":"code","source":"valid_preds = model.predict(x_valid, num_iteration = model.best_iteration_)\nvalid_preds[valid_preds < 0] = 0","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:05.577887Z","iopub.execute_input":"2021-10-08T12:41:05.578122Z","iopub.status.idle":"2021-10-08T12:41:05.595414Z","shell.execute_reply.started":"2021-10-08T12:41:05.578091Z","shell.execute_reply":"2021-10-08T12:41:05.594793Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"mean_squared_error(np.log1p(y_valid), valid_preds)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:05.596679Z","iopub.execute_input":"2021-10-08T12:41:05.596929Z","iopub.status.idle":"2021-10-08T12:41:05.604588Z","shell.execute_reply.started":"2021-10-08T12:41:05.596897Z","shell.execute_reply":"2021-10-08T12:41:05.603776Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_preds = model.predict(test[x_train.columns], num_iteration = model.best_iteration_)\ntest_preds[test_preds < 0] = 0","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:05.605864Z","iopub.execute_input":"2021-10-08T12:41:05.606094Z","iopub.status.idle":"2021-10-08T12:41:05.905503Z","shell.execute_reply.started":"2021-10-08T12:41:05.606049Z","shell.execute_reply":"2021-10-08T12:41:05.904642Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"rec_submit = pd.concat([fullvisitorid, pd.Series(test_preds)], axis = 1)\nrec_submit.columns = ['fullVisitorId','PredictedLogRevenue']\nrec_submit","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:05.907166Z","iopub.execute_input":"2021-10-08T12:41:05.907437Z","iopub.status.idle":"2021-10-08T12:41:05.933392Z","shell.execute_reply.started":"2021-10-08T12:41:05.907403Z","shell.execute_reply":"2021-10-08T12:41:05.932651Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### b. For each User","metadata":{}},{"cell_type":"code","source":"user_submit = rec_submit.groupby('fullVisitorId').sum().reset_index()\nuser_submit","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:05.934871Z","iopub.execute_input":"2021-10-08T12:41:05.935128Z","iopub.status.idle":"2021-10-08T12:41:06.666288Z","shell.execute_reply.started":"2021-10-08T12:41:05.935095Z","shell.execute_reply":"2021-10-08T12:41:06.665444Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"user_submit.to_csv('submission.csv', index = False)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:06.667638Z","iopub.execute_input":"2021-10-08T12:41:06.667915Z","iopub.status.idle":"2021-10-08T12:41:07.810819Z","shell.execute_reply.started":"2021-10-08T12:41:06.667880Z","shell.execute_reply":"2021-10-08T12:41:07.809860Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Checking the format is right:","metadata":{}},{"cell_type":"code","source":"s = pd.read_csv('submission.csv')\ns","metadata":{"execution":{"iopub.status.busy":"2021-10-08T12:41:07.812252Z","iopub.execute_input":"2021-10-08T12:41:07.812580Z","iopub.status.idle":"2021-10-08T12:41:08.134426Z","shell.execute_reply.started":"2021-10-08T12:41:07.812541Z","shell.execute_reply":"2021-10-08T12:41:08.133596Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 7. References\n\n[1] https://www.kaggle.com/sudalairajkumar/simple-exploration-baseline-ga-customer-revenue/notebook <br>\n[2] https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields/notebook <br>\n[3] https://www.kaggle.com/ogrellier/teach-lightgbm-to-sum-predictions <br>\n[4] https://towardsdatascience.com/10-tricks-for-converting-numbers-and-strings-to-datetime-in-pandas-82a4645fc23d","metadata":{}}]}