{"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":" # ****Google Analytics Customer Revenue Prediction****","metadata":{}},{"cell_type":"markdown","source":"### The 80/20 rule has proven true for many businesses–only a small percentage of customers produce most of the revenue. As such, marketing teams are challenged to make appropriate investments in promotional strategies.\n\nThis notebook is analysis for a Google Merchandise Store (also known as GStore, where Google swag is sold) customer dataset to predict revenue per customer. Hopefully, the outcome will be more actionable operational changes and a better use of marketing budgets for those companies who choose to use data analysis on top of GA data.\n\nFor each fullVisitorId in the test set, predict the natural log of their total revenue in PredictedLogRevenue, noting that one customer may have number of transactions in data.\n \n####  IMPORTANT: Due to the formatting of fullVisitorId you must load the Id's as strings in order for all Id's to be properly unique!\n\n#### There are multiple columns which contain JSON blobs of varying depth\n\n","metadata":{}},{"cell_type":"markdown","source":"## Importing Libraries","metadata":{}},{"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport json\nimport gc\nimport sys\nimport math\n\nfrom pandas.io.json import json_normalize\nfrom datetime import datetime\n\nimport os\nprint(os.listdir(\"../input/ga-customer-revenue-prediction\"))","metadata":{"execution":{"iopub.status.busy":"2022-07-25T17:36:41.565075Z","iopub.execute_input":"2022-07-25T17:36:41.565624Z","iopub.status.idle":"2022-07-25T17:36:41.572300Z","shell.execute_reply.started":"2022-07-25T17:36:41.565591Z","shell.execute_reply":"2022-07-25T17:36:41.571285Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":" As data exceeds million records that we may go through memory problems, Notice that data contains number of non important features due to their null values that exceed 90%, so I didn't load them from the beginning and only selected and loaded the valid features.","metadata":{}},{"cell_type":"code","source":"#function to load load data and normalize the json data columns\n\ngc.enable()\n  \nfeatures = ['channelGrouping', 'date', 'fullVisitorId','socialEngagementType', 'visitId',\\\n       'visitNumber', 'visitStartTime', 'device.browser',\\\n       'device.deviceCategory', 'device.isMobile', 'device.operatingSystem', \\\n       'geoNetwork.city', 'geoNetwork.continent', 'geoNetwork.country',\\\n       'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region',\\\n       'geoNetwork.subContinent', 'totals.visits', 'totals.hits',\\\n       'totals.newVisits', 'totals.pageviews', 'totals.transactionRevenue','totals.totalTransactionRevenue',\\\n       'trafficSource.adContent', 'trafficSource.campaign', 'trafficSource.medium', \\\n       'trafficSource.source', 'customDimensions']\n\n  \n        \ndef load_df(csv_path):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    ans = pd.DataFrame()\n    dfs = pd.read_csv(csv_path, sep=',',\n            converters={column: json.loads for column in JSON_COLUMNS}, \n            dtype={'fullVisitorId': 'str'}, \n            chunksize=100000)\n    \n    for df in dfs:\n        df.reset_index(drop=True, inplace=True)\n        for column in JSON_COLUMNS:\n            column_as_df = json_normalize(df[column])\n            column_as_df.columns = [f\"{column}.{subcolumn}\" for subcolumn in column_as_df.columns]\n            df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)\n\n       \n        use_df = df[features]\n        del df\n        gc.collect()\n        ans = pd.concat([ans, use_df], axis=0).reset_index(drop=True)\n   \n    return ans","metadata":{"execution":{"iopub.status.busy":"2022-07-25T17:36:41.753547Z","iopub.execute_input":"2022-07-25T17:36:41.754145Z","iopub.status.idle":"2022-07-25T17:36:41.765936Z","shell.execute_reply.started":"2022-07-25T17:36:41.754108Z","shell.execute_reply":"2022-07-25T17:36:41.764978Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#load data and checking time interval of both train and test\n\ndf_train = load_df('../input/ga-customer-revenue-prediction/train_v2.csv')\ndf_test = load_df('../input/ga-customer-revenue-prediction/test_v2.csv')\n\nprint('train date:', min(df_train['date']), 'to', max(df_train['date']))\nprint('test date:', min(df_test['date']), 'to', max(df_test['date']))","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:10:16.991231Z","iopub.execute_input":"2022-07-25T18:10:16.991587Z","iopub.status.idle":"2022-07-25T18:23:25.272577Z","shell.execute_reply.started":"2022-07-25T18:10:16.991556Z","shell.execute_reply":"2022-07-25T18:23:25.271457Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#reduce memory usage by adjust the reserved size of datatypes as per need. \n\ndef reduce_mem_usage(df):\n    \"\"\" iterate through all the columns of a dataframe and modify the data types to reduce memory usage.        \n    \"\"\"\n    start_mem = df.memory_usage().sum() \n    print('Initial Memory usage of dataframe is {:.2f} MB'.format(start_mem))\n    \n    for col in df.columns:\n        col_type = df[col].dtype\n        \n        if col_type != object:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)\n        #else:\n            #df[col] = df[col].astype('category')\n\n    end_mem = df.memory_usage().sum() \n    print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))\n    print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n    return df\n\n\ndf_train = reduce_mem_usage(df_train)\ndf_test = reduce_mem_usage(df_test)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:06.218298Z","iopub.execute_input":"2022-07-25T18:30:06.219264Z","iopub.status.idle":"2022-07-25T18:30:06.320765Z","shell.execute_reply.started":"2022-07-25T18:30:06.219214Z","shell.execute_reply":"2022-07-25T18:30:06.319784Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Exploratory Data Analysis","metadata":{}},{"cell_type":"code","source":"print(df_train.shape)\nprint(df_test.shape)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:11.695742Z","iopub.execute_input":"2022-07-25T18:30:11.696330Z","iopub.status.idle":"2022-07-25T18:30:11.701521Z","shell.execute_reply.started":"2022-07-25T18:30:11.696292Z","shell.execute_reply":"2022-07-25T18:30:11.700463Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.columns","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:14.953480Z","iopub.execute_input":"2022-07-25T18:30:14.953858Z","iopub.status.idle":"2022-07-25T18:30:14.961607Z","shell.execute_reply.started":"2022-07-25T18:30:14.953827Z","shell.execute_reply":"2022-07-25T18:30:14.960441Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.dtypes","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:20.341792Z","iopub.execute_input":"2022-07-25T18:30:20.342765Z","iopub.status.idle":"2022-07-25T18:30:20.352082Z","shell.execute_reply.started":"2022-07-25T18:30:20.342694Z","shell.execute_reply":"2022-07-25T18:30:20.351030Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.select_dtypes(include='object').columns","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:25.247052Z","iopub.execute_input":"2022-07-25T18:30:25.247498Z","iopub.status.idle":"2022-07-25T18:30:26.594169Z","shell.execute_reply.started":"2022-07-25T18:30:25.247450Z","shell.execute_reply":"2022-07-25T18:30:26.593003Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.select_dtypes(exclude='object').columns","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:30.578776Z","iopub.execute_input":"2022-07-25T18:30:30.579802Z","iopub.status.idle":"2022-07-25T18:30:30.613599Z","shell.execute_reply.started":"2022-07-25T18:30:30.579740Z","shell.execute_reply":"2022-07-25T18:30:30.612572Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:33.817396Z","iopub.execute_input":"2022-07-25T18:30:33.817969Z","iopub.status.idle":"2022-07-25T18:30:33.845346Z","shell.execute_reply.started":"2022-07-25T18:30:33.817936Z","shell.execute_reply":"2022-07-25T18:30:33.844270Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Checking and Filling Nulls","metadata":{}},{"cell_type":"code","source":"#check columns contain nulls with their null percentage\nnull_percentage = pd.DataFrame()\nfor col in df_train.columns:\n    if df_train[col].isnull().sum() > 0:\n        null_percentage.loc[col,'NullPercentage'] = (df_train[col].isnull().sum())/len(df_train) * 100 \nprint(null_percentage)\n        ","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:37.480447Z","iopub.execute_input":"2022-07-25T18:30:37.480833Z","iopub.status.idle":"2022-07-25T18:30:42.651230Z","shell.execute_reply.started":"2022-07-25T18:30:37.480802Z","shell.execute_reply":"2022-07-25T18:30:42.650014Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Note that the 98.9% null percentage in our target column 'totals.transactionRevenue' is not a problem, as null means zero revenue for this transaction, which proves what is documented in the competition overview \"only 20% only from customers making the revenue\"\n\n'totals.totalTransactionRevenue' is a duplicated column, so I'll drop it.\n\n'trafficSource.adContent' contains to much null so I'll drop it as well.\n\n'totals.newVisits' & 'totals.pageviews' could be imputed.","metadata":{}},{"cell_type":"code","source":"df_train['totals.transactionRevenue'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:42.653290Z","iopub.execute_input":"2022-07-25T18:30:42.653647Z","iopub.status.idle":"2022-07-25T18:30:42.716502Z","shell.execute_reply.started":"2022-07-25T18:30:42.653610Z","shell.execute_reply":"2022-07-25T18:30:42.715475Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#As this is a duplicate column\ndf_train.drop('totals.totalTransactionRevenue', axis=1, inplace=True)\ndf_test.drop('totals.totalTransactionRevenue', axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:42.717844Z","iopub.execute_input":"2022-07-25T18:30:42.718661Z","iopub.status.idle":"2022-07-25T18:30:43.999829Z","shell.execute_reply.started":"2022-07-25T18:30:42.718625Z","shell.execute_reply":"2022-07-25T18:30:43.998787Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.drop('trafficSource.adContent', axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:45.934379Z","iopub.execute_input":"2022-07-25T18:30:45.935074Z","iopub.status.idle":"2022-07-25T18:30:47.246905Z","shell.execute_reply.started":"2022-07-25T18:30:45.935035Z","shell.execute_reply":"2022-07-25T18:30:47.245790Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#fill null in 'totals.pageviews' by mode\nprint(df_train['totals.pageviews'].isnull().sum())\nprint(df_train['totals.pageviews'].dtype)\n\nprint(df_train['totals.pageviews'] .mode())\nprint((df_train[\"totals.pageviews\"]=='1').sum())\ndf_train['totals.pageviews'] = df_train['totals.pageviews'].fillna(1)\n\ndf_train['totals.pageviews'] = df_train['totals.pageviews'].astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:47.665331Z","iopub.execute_input":"2022-07-25T18:30:47.665671Z","iopub.status.idle":"2022-07-25T18:30:49.979809Z","shell.execute_reply.started":"2022-07-25T18:30:47.665642Z","shell.execute_reply":"2022-07-25T18:30:49.978649Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#fill null in 'totals.newVisits' by mode\nprint(df_train['totals.newVisits'].isnull().sum())\nprint(df_train['totals.newVisits'].dtype)\n\nprint(df_train['totals.newVisits'] .mode())\nprint((df_train[\"totals.newVisits\"]=='1').sum())\ndf_train['totals.pageviews'] = df_train['totals.pageviews'].fillna(1)\n\ndf_train['totals.pageviews'] = df_train['totals.pageviews'].astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:49.981951Z","iopub.execute_input":"2022-07-25T18:30:49.982617Z","iopub.status.idle":"2022-07-25T18:30:50.448476Z","shell.execute_reply.started":"2022-07-25T18:30:49.982579Z","shell.execute_reply":"2022-07-25T18:30:50.446444Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"##### Check number of customers in train dataset from whole visits/transactions number","metadata":{}},{"cell_type":"code","source":"unique_customers_no = df_train['fullVisitorId'].nunique()\ntotal_customers_no = df_train['visitId'].count()\nprint(\"No of unique customers {} from {} total customers\".format(unique_customers_no , total_customers_no))","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:50.689368Z","iopub.execute_input":"2022-07-25T18:30:50.689741Z","iopub.status.idle":"2022-07-25T18:30:51.326510Z","shell.execute_reply.started":"2022-07-25T18:30:50.689692Z","shell.execute_reply":"2022-07-25T18:30:51.325415Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"##### Check least and highest transaction revenue","metadata":{}},{"cell_type":"code","source":"df_train['totals.transactionRevenue'] = df_train['totals.transactionRevenue'].astype(float)\ndf_train['totals.transactionRevenue'] = df_train['totals.transactionRevenue'].fillna(0)\n\nprint(df_train['totals.transactionRevenue'].sort_values().unique()[1])\nprint(df_train['totals.transactionRevenue'].sort_values().unique()[-1])\n","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:30:54.559490Z","iopub.execute_input":"2022-07-25T18:30:54.560160Z","iopub.status.idle":"2022-07-25T18:30:56.162127Z","shell.execute_reply.started":"2022-07-25T18:30:54.560121Z","shell.execute_reply":"2022-07-25T18:30:56.161072Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"##### Check number and percentage of customers that make revenue","metadata":{}},{"cell_type":"code","source":"target = df_train.groupby('fullVisitorId')[['totals.transactionRevenue']].sum().reset_index()\n\ncustomers_making_revenue = (target[\"totals.transactionRevenue\"]>0).sum()\n\nprint(\"No of different customers with no zero revenue= {} from total {} customers\".format(customers_making_revenue,unique_customers_no))\nprint(\"Percentage of customers with no zero revenue = \",round(customers_making_revenue/unique_customers_no*100 , 2))    \n","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:31:01.505508Z","iopub.execute_input":"2022-07-25T18:31:01.505885Z","iopub.status.idle":"2022-07-25T18:31:05.312117Z","shell.execute_reply.started":"2022-07-25T18:31:01.505855Z","shell.execute_reply":"2022-07-25T18:31:05.311091Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"##### Check top 5 customers as per monetary (asses in revenue)","metadata":{}},{"cell_type":"code","source":"target.sort_values(by='totals.transactionRevenue' , ascending=False).head()","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:31:05.314045Z","iopub.execute_input":"2022-07-25T18:31:05.314680Z","iopub.status.idle":"2022-07-25T18:31:05.466639Z","shell.execute_reply.started":"2022-07-25T18:31:05.314641Z","shell.execute_reply":"2022-07-25T18:31:05.465692Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.set_theme(style=\"darkgrid\")\nax = sns.countplot(x=pd.cut( df_train['totals.transactionRevenue'], [-10,0,2e11]) )\nax.set_xticklabels([\"0$ revenue customers\" , \"revenue customers\"])\nax.set_xlabel('Revenue' , fontsize=16 , color='Black')\nax.set_ylabel('Number of Customers' , fontsize=16 , color='Black')","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:31:09.536274Z","iopub.execute_input":"2022-07-25T18:31:09.536629Z","iopub.status.idle":"2022-07-25T18:31:09.821611Z","shell.execute_reply.started":"2022-07-25T18:31:09.536599Z","shell.execute_reply":"2022-07-25T18:31:09.820757Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train2 = df_train.copy()","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:31:09.948313Z","iopub.execute_input":"2022-07-25T18:31:09.948657Z","iopub.status.idle":"2022-07-25T18:31:10.808848Z","shell.execute_reply.started":"2022-07-25T18:31:09.948625Z","shell.execute_reply":"2022-07-25T18:31:10.807762Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Visualize to check effect of some features on: transaction number,  transaction with revenue number, average revenue","metadata":{}},{"cell_type":"code","source":"df_train2['totals.transactionRevenue'] = df_train2['totals.transactionRevenue'].replace(0,np.nan)\n\n\ndev_category = df_train2.groupby('device.deviceCategory')[['totals.transactionRevenue']].agg(['size', 'count', 'mean'])\ndev_category.columns = ['total transactions no','non zero transactions no','average revenue']\n\ndev_os = df_train2.groupby('device.operatingSystem')[['totals.transactionRevenue']].agg(['size', 'count', 'mean'])\ndev_os.columns = ['total transactions no','non zero transactions no','average revenue']\ndev_os = dev_os.sort_values(by='total transactions no', ascending=False)\n\ndev_browser = df_train2.groupby('device.browser')[['totals.transactionRevenue']].agg(['size', 'count', 'mean'])\ndev_browser.columns = ['total transactions no','non zero transactions no','average revenue']\ndev_browser = dev_browser.sort_values(by='total transactions no', ascending=False)\n\n\n#plt.figure(figsize=(12,4))\nfig , ax = plt.subplots(3 , 3 , figsize=(15,8))\nplt.subplot(3,3,1)\nsns.barplot(y=dev_category.index , x=dev_category['total transactions no'],palette=\"viridis\")\nplt.subplot(3,3,2)\nsns.barplot(y=dev_category.index , x=dev_category['non zero transactions no'],palette=\"viridis\").set(ylabel=None)\nplt.subplot(3,3,3)\nsns.barplot(y=dev_category.index , x=dev_category['average revenue'],palette=\"viridis\").set(ylabel=None)\n\nplt.subplot(3,3,4)\nsns.barplot(y=dev_os.index[0:6] , x=dev_os['total transactions no'].head(6) ,palette=\"viridis\")\nplt.subplot(3,3,5)\nsns.barplot(data=dev_os.head(5), y=dev_os.index[0:6], x=dev_os['non zero transactions no'].head(6), palette=\"viridis\").set(ylabel=None)\nplt.subplot(3,3,6)\nsns.barplot(data=dev_os.head(5), y=dev_os.index[0:6] , x=dev_os['average revenue'].head(6), palette=\"viridis\").set(ylabel=None)\n\nplt.subplot(3,3,7)\nsns.barplot(y=dev_browser.index[0:6] , x=dev_browser['total transactions no'].head(6) ,palette=\"viridis\")\nplt.subplot(3,3,8)\nsns.barplot(y=dev_browser.index[0:6], x=dev_os['non zero transactions no'].head(6), palette=\"viridis\").set(ylabel=None)\nplt.subplot(3,3,9)\nsns.barplot(y=dev_browser.index[0:6] , x=dev_os['average revenue'].head(6), palette=\"viridis\").set(ylabel=None)\n\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:31:13.641557Z","iopub.execute_input":"2022-07-25T18:31:13.642645Z","iopub.status.idle":"2022-07-25T18:31:16.777613Z","shell.execute_reply.started":"2022-07-25T18:31:13.642596Z","shell.execute_reply":"2022-07-25T18:31:16.776658Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"geo_continent = df_train2.groupby('geoNetwork.continent')[['totals.transactionRevenue']].agg(['size', 'count', 'mean'])\ngeo_continent.columns = ['total transactions no','non zero transactions no','average revenue']\ngeo_continent = geo_continent.sort_values(by='total transactions no', ascending=False)\n\ngeo_country = df_train2.groupby('geoNetwork.country')[['totals.transactionRevenue']].agg(['size', 'count', 'mean'])\ngeo_country.columns = ['total transactions no','non zero transactions no','average revenue']\ngeo_country = geo_country.sort_values(by='total transactions no', ascending=False)\n\ngeo_city = df_train2.groupby('geoNetwork.city')[['totals.transactionRevenue']].agg(['size', 'count', 'mean'])\ngeo_city.columns = ['total transactions no','non zero transactions no','average revenue']\ngeo_city = geo_city.sort_values(by='total transactions no', ascending=False)\n\ngeo_region = df_train2.groupby('geoNetwork.region')[['totals.transactionRevenue']].agg(['size', 'count', 'mean'])\ngeo_region.columns = ['total transactions no','non zero transactions no','average revenue']\ngeo_region = geo_region.sort_values(by='total transactions no', ascending=False)\n\ngeo_network = df_train2.groupby('geoNetwork.networkDomain')[['totals.transactionRevenue']].agg(['size', 'count', 'mean'])\ngeo_network.columns = ['total transactions no','non zero transactions no','average revenue']\ngeo_network = geo_network.sort_values(by='total transactions no', ascending=False)\n\n\n#plt.figure(figsize=(12,4))\nfig , ax = plt.subplots(5 , 3 , figsize=(15,40))\nplt.subplot(5,3,1)\nsns.barplot(y=geo_continent.index , x=geo_continent['total transactions no'],color=\"magenta\")\nplt.subplot(5,3,2)\nsns.barplot(y=geo_continent.index , x=geo_continent['non zero transactions no'],color=\"magenta\").set(ylabel=None)\nplt.subplot(5,3,3)\nsns.barplot(y=geo_continent.index , x=geo_continent['average revenue'],color=\"magenta\").set(ylabel=None)\n\nplt.subplot(5,3,4)\nsns.barplot(y=geo_country.index[0:10] , x=geo_country['total transactions no'].head(10) ,color=\"cyan\")\nplt.subplot(5,3,5)\nsns.barplot(y=geo_country.index[0:10], x=geo_country['non zero transactions no'].head(10), color=\"cyan\").set(ylabel=None)\nplt.subplot(5,3,6)\nsns.barplot(y=geo_country.index[0:10] , x=geo_country['average revenue'].head(10), color=\"cyan\").set(ylabel=None)\n\nplt.subplot(5,3,7)\nsns.barplot(y=geo_city.index[0:10] , x=geo_city['total transactions no'].head(10) ,color=\"salmon\")\nplt.subplot(5,3,8)\nsns.barplot(y=geo_city.index[0:10], x=geo_city['non zero transactions no'].head(10), color=\"salmon\").set(ylabel=None)\nplt.subplot(5,3,9)\nsns.barplot(y=geo_city.index[0:10] , x=geo_city['average revenue'].head(10), color=\"salmon\").set(ylabel=None)\n\nplt.subplot(5,3,10)\nsns.barplot(y=geo_region.index[0:10] , x=geo_region['total transactions no'].head(10) ,color=\"green\")\nplt.subplot(5,3,11)\nsns.barplot(y=geo_region.index[0:10], x=geo_region['non zero transactions no'].head(10), color=\"green\").set(ylabel=None)\nplt.subplot(5,3,12)\nsns.barplot(y=geo_region.index[0:10] , x=geo_region['average revenue'].head(10), color=\"green\").set(ylabel=None)\n\nplt.subplot(5,3,13)\nsns.barplot(y=geo_network.index[0:10] , x=geo_network['total transactions no'].head(10) ,color=\"orange\")\nplt.subplot(5,3,14)\nsns.barplot(y=geo_network.index[0:10], x=geo_network['non zero transactions no'].head(10), color=\"orange\").set(ylabel=None)\nplt.subplot(5,3,15)\nsns.barplot(y=geo_network.index[0:10] , x=geo_network['average revenue'].head(10), color=\"orange\").set(ylabel=None)\n\n\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:31:17.677880Z","iopub.execute_input":"2022-07-25T18:31:17.678312Z","iopub.status.idle":"2022-07-25T18:31:23.100504Z","shell.execute_reply.started":"2022-07-25T18:31:17.678277Z","shell.execute_reply":"2022-07-25T18:31:23.099650Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tf_source = df_train2.groupby('trafficSource.source')[['totals.transactionRevenue']].agg(['size', 'count', 'mean'])\ntf_source.columns = ['total transactions no','non zero transactions no','average revenue']\ntf_source = tf_source.sort_values(by='total transactions no', ascending=False)\n\ntf_medium = df_train2.groupby('trafficSource.medium')[['totals.transactionRevenue']].agg(['size', 'count', 'mean'])\ntf_medium.columns = ['total transactions no','non zero transactions no','average revenue']\ntf_medium = tf_medium.sort_values(by='total transactions no', ascending=False)\n\n\nfig , ax = plt.subplots(2 , 3 , figsize=(15,10))\nplt.subplot(2,3,1)\nsns.barplot(y=tf_source.index[0:10] , x=tf_source['total transactions no'].head(10), palette=\"Spectral\")\nplt.subplot(2,3,2)\nsns.barplot(y=tf_source.index[0:10] , x=tf_source['non zero transactions no'].head(10), palette=\"Spectral\").set(ylabel=None)\nplt.subplot(2,3,3)\nsns.barplot(y=tf_source.index[0:10] , x=tf_source['average revenue'].head(10), palette=\"Spectral\").set(ylabel=None)\n\nplt.subplot(2,3,4)\nsns.barplot(y=tf_medium.index[0:10] , x=tf_medium['total transactions no'].head(10) ,palette=\"Spectral\")\nplt.subplot(2,3,5)\nsns.barplot(y=tf_medium.index[0:10], x=tf_medium['non zero transactions no'].head(10), palette=\"Spectral\").set(ylabel=None)\nplt.subplot(2,3,6)\nsns.barplot(y=tf_medium.index[0:10] , x=tf_medium['average revenue'].head(10), palette=\"Spectral\").set(ylabel=None)\n\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:31:28.437326Z","iopub.execute_input":"2022-07-25T18:31:28.438159Z","iopub.status.idle":"2022-07-25T18:31:30.306651Z","shell.execute_reply.started":"2022-07-25T18:31:28.438126Z","shell.execute_reply":"2022-07-25T18:31:30.305777Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Insight Found:","metadata":{}},{"cell_type":"markdown","source":"##### - Most transaction are done through desktop,then mobile in 2nd level then tablet in 3rd\n##### - Customers using Machintosh OS make the most non zero revenue transaction numbers\n##### - While talking about average revenue numbers, customers using windows makes the most revenue.\n##### - Chrome & Safari browsers contribute in the most transactions number.\n##### - Edge in addition to Chrome & Safari contributes in the most revenue.\n\n##### - Americas continent has the most transactions number while Africa helps with the most revenue.\n##### - Asia & Europe almost equal.\n##### - USA participates with the most transactions while Japan with the most revenue.\n\n##### - Most transactions made from people access through google, direct and youtube\n##### - Most revenue made from perople accessed through dfa","metadata":{}},{"cell_type":"markdown","source":"## Check for constant columns","metadata":{}},{"cell_type":"code","source":"#df_train.select_dtypes(exclude='object').columns\n\nremain_features= ['visitNumber', 'device.isMobile', 'totals.pageviews','socialEngagementType','channelGrouping'\n                    ,'geoNetwork.metro','totals.visits', 'totals.hits','totals.newVisits', \n                    'trafficSource.campaign', 'customDimensions' ]\n\nfor col in remain_features:\n    if len(df_train[col].unique()) == 1:\n        print(\"{} is constant\".format(col))\n","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:31:40.472321Z","iopub.execute_input":"2022-07-25T18:31:40.472665Z","iopub.status.idle":"2022-07-25T18:31:42.171916Z","shell.execute_reply.started":"2022-07-25T18:31:40.472634Z","shell.execute_reply":"2022-07-25T18:31:42.170869Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(df_train['socialEngagementType'].unique())\nprint(df_train['totals.visits'].unique())","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:31:43.455348Z","iopub.execute_input":"2022-07-25T18:31:43.455768Z","iopub.status.idle":"2022-07-25T18:31:43.678453Z","shell.execute_reply.started":"2022-07-25T18:31:43.455711Z","shell.execute_reply":"2022-07-25T18:31:43.677505Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#drop columns that contain constant values.\ndf_train.drop('socialEngagementType',axis=1, inplace=True)\ndf_train.drop('totals.visits',axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:31:47.112176Z","iopub.execute_input":"2022-07-25T18:31:47.112517Z","iopub.status.idle":"2022-07-25T18:31:49.819275Z","shell.execute_reply.started":"2022-07-25T18:31:47.112483Z","shell.execute_reply":"2022-07-25T18:31:49.818238Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.pairplot(df_train, diag_kind=\"hist\")\n","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:31:49.821351Z","iopub.execute_input":"2022-07-25T18:31:49.821765Z","iopub.status.idle":"2022-07-25T18:34:19.414567Z","shell.execute_reply.started":"2022-07-25T18:31:49.821710Z","shell.execute_reply":"2022-07-25T18:34:19.413738Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.columns","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:34:19.416206Z","iopub.execute_input":"2022-07-25T18:34:19.417987Z","iopub.status.idle":"2022-07-25T18:34:19.424740Z","shell.execute_reply.started":"2022-07-25T18:34:19.417944Z","shell.execute_reply":"2022-07-25T18:34:19.423922Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train['totals.hits']= df_train['totals.hits'].astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:34:34.647432Z","iopub.execute_input":"2022-07-25T18:34:34.647818Z","iopub.status.idle":"2022-07-25T18:34:36.120959Z","shell.execute_reply.started":"2022-07-25T18:34:34.647785Z","shell.execute_reply":"2022-07-25T18:34:36.119975Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(df_train['totals.newVisits'].value_counts())\nprint('-'*30)\nprint(df_train['visitNumber'].value_counts()[:10])\nprint('-'*30)\nprint(df_train['device.isMobile'].value_counts()[:10])","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:34:37.701395Z","iopub.execute_input":"2022-07-25T18:34:37.701763Z","iopub.status.idle":"2022-07-25T18:34:37.888927Z","shell.execute_reply.started":"2022-07-25T18:34:37.701712Z","shell.execute_reply":"2022-07-25T18:34:37.887762Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(df_train['totals.hits'].value_counts()[:10])\nprint('-'*30)\nprint(df_train['totals.pageviews'].value_counts()[:10])\n","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:34:43.645508Z","iopub.execute_input":"2022-07-25T18:34:43.645883Z","iopub.status.idle":"2022-07-25T18:34:43.673884Z","shell.execute_reply.started":"2022-07-25T18:34:43.645851Z","shell.execute_reply":"2022-07-25T18:34:43.672951Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Transform & Dealing with dates","metadata":{}},{"cell_type":"code","source":"print(df_train['visitStartTime'][0])\ndf_train['visitStartTime'] = pd.to_datetime(df_train['visitStartTime'], unit='s')\nprint(df_train['visitStartTime'][0])\ndf_train['vst_dayofweek'] = df_train['visitStartTime'].dt.dayofweek\ndf_train['vst_hours'] = df_train['visitStartTime'].dt.hour\ndf_train['vst_dayofmonth'] = df_train['visitStartTime'].dt.day\nprint(df_train['vst_dayofweek'][0], df_train['vst_hours'][0], df_train['vst_dayofmonth'][0])\ndf_train.drop('visitStartTime', axis = 1, inplace = True)\n    ","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:34:47.009435Z","iopub.execute_input":"2022-07-25T18:34:47.010102Z","iopub.status.idle":"2022-07-25T18:34:48.855180Z","shell.execute_reply.started":"2022-07-25T18:34:47.010065Z","shell.execute_reply":"2022-07-25T18:34:48.853945Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"format_str = '%Y%m%d'\ndf_train['formated_date'] = df_train['date'].apply(lambda x: datetime.strptime(str(x), format_str))\ndf_train['year'] = df_train['formated_date'].apply(lambda x:x.year)\ndf_train['month'] = df_train['formated_date'].apply(lambda x:x.month)\ndf_train['quarterMonth'] = df_train['formated_date'].apply(lambda x:x.day//8)\ndf_train['day'] = df_train['formated_date'].apply(lambda x:x.day)\ndf_train['weekday'] = df_train['formated_date'].apply(lambda x:x.weekday())\n\ndf_train.drop(['date','formated_date'], axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:34:49.677234Z","iopub.execute_input":"2022-07-25T18:34:49.677574Z","iopub.status.idle":"2022-07-25T18:36:00.597347Z","shell.execute_reply.started":"2022-07-25T18:34:49.677543Z","shell.execute_reply":"2022-07-25T18:36:00.596344Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#drop ID columns as they're irrelevant features\nirrelavant_features = ['fullVisitorId', 'visitId']\nfor col in irrelavant_features:\n    df_train.drop(col, axis = 1, inplace = True)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:36:16.458904Z","iopub.execute_input":"2022-07-25T18:36:16.459493Z","iopub.status.idle":"2022-07-25T18:36:19.016908Z","shell.execute_reply.started":"2022-07-25T18:36:16.459450Z","shell.execute_reply":"2022-07-25T18:36:19.015837Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import lightgbm as lgb\n\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.metrics import mean_squared_error\n\nimport warnings\nwarnings.filterwarnings('ignore')\n","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:36:21.034872Z","iopub.execute_input":"2022-07-25T18:36:21.036063Z","iopub.status.idle":"2022-07-25T18:36:21.042970Z","shell.execute_reply.started":"2022-07-25T18:36:21.036016Z","shell.execute_reply":"2022-07-25T18:36:21.041894Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Transform categorical features to numerical using Label Encoding method","metadata":{}},{"cell_type":"code","source":"%%time\nle = LabelEncoder()\nprint('Categorical columns that will be converted:')\nfor col in df_train.columns:\n    if df_train[col].dtype == 'O':\n        print(col)\n        #print(col, train[col].unique())\n        df_train.loc[:, col] = le.fit_transform(df_train.loc[:, col])\n    ","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:36:25.528980Z","iopub.execute_input":"2022-07-25T18:36:25.529570Z","iopub.status.idle":"2022-07-25T18:36:49.490839Z","shell.execute_reply.started":"2022-07-25T18:36:25.529534Z","shell.execute_reply":"2022-07-25T18:36:49.489782Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Repeat same analysis on test data","metadata":{}},{"cell_type":"code","source":"print('No of columns in test set ',len(df_test.columns))\nprint('Columns in train and not in test are: ',set(df_train)-set(df_test))\n\nnull_percentage = pd.DataFrame()\nfor col in df_test.columns:\n    if df_test[col].isnull().sum() > 0:\n        null_percentage.loc[col,'NullPercentage'] = (df_test[col].isnull().sum())/len(df_test) * 100 \nprint(null_percentage)\n\n#drop or fill","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:37:00.329518Z","iopub.execute_input":"2022-07-25T18:37:00.329877Z","iopub.status.idle":"2022-07-25T18:37:01.775629Z","shell.execute_reply.started":"2022-07-25T18:37:00.329847Z","shell.execute_reply":"2022-07-25T18:37:01.774432Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(df_test['totals.pageviews'].isnull().sum())\nprint(df_test['totals.pageviews'].dtype)\n\nprint(df_test['totals.pageviews'] .mode())\nprint((df_test[\"totals.pageviews\"]=='1').sum())\n\ndf_test['totals.pageviews'] = df_test['totals.pageviews'].fillna(1)\n\ndf_test['totals.pageviews'] = df_test['totals.pageviews'].astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:37:04.870841Z","iopub.execute_input":"2022-07-25T18:37:04.871474Z","iopub.status.idle":"2022-07-25T18:37:05.457597Z","shell.execute_reply.started":"2022-07-25T18:37:04.871436Z","shell.execute_reply":"2022-07-25T18:37:05.456628Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(df_test['totals.newVisits'].isnull().sum())\nprint(df_test['totals.newVisits'].dtype)\n\nprint(df_test['totals.newVisits'] .mode())\nprint((df_test[\"totals.newVisits\"]=='1').sum())\ndf_test['totals.pageviews'] = df_test['totals.pageviews'].fillna(1)\n\ndf_test['totals.pageviews'] = df_test['totals.pageviews'].astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:37:08.907357Z","iopub.execute_input":"2022-07-25T18:37:08.908361Z","iopub.status.idle":"2022-07-25T18:37:09.022589Z","shell.execute_reply.started":"2022-07-25T18:37:08.908313Z","shell.execute_reply":"2022-07-25T18:37:09.021505Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test.drop('totals.transactionRevenue', axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:37:10.417539Z","iopub.execute_input":"2022-07-25T18:37:10.418393Z","iopub.status.idle":"2022-07-25T18:37:10.737479Z","shell.execute_reply.started":"2022-07-25T18:37:10.418345Z","shell.execute_reply":"2022-07-25T18:37:10.736375Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in df_test.columns:\n    if df_test.nunique == 1:\n        print(\"{} is constant\".format(col))\n        df_test.drop(col, axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:37:12.642583Z","iopub.execute_input":"2022-07-25T18:37:12.643208Z","iopub.status.idle":"2022-07-25T18:37:12.648676Z","shell.execute_reply.started":"2022-07-25T18:37:12.643168Z","shell.execute_reply":"2022-07-25T18:37:12.647602Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train['totals.hits']= df_train['totals.hits'].astype(int)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:37:14.759476Z","iopub.execute_input":"2022-07-25T18:37:14.760569Z","iopub.status.idle":"2022-07-25T18:37:14.774853Z","shell.execute_reply.started":"2022-07-25T18:37:14.760520Z","shell.execute_reply":"2022-07-25T18:37:14.773462Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(df_test['visitStartTime'][0])\ndf_test['visitStartTime'] = pd.to_datetime(df_test['visitStartTime'], unit='s')\nprint(df_test['visitStartTime'][0])\ndf_test['vst_dayofweek'] = df_test['visitStartTime'].dt.dayofweek\ndf_test['vst_hours'] = df_test['visitStartTime'].dt.hour\ndf_test['vst_dayofmonth'] = df_test['visitStartTime'].dt.day\nprint(df_test['vst_dayofweek'][0], df_test['vst_hours'][0], df_test['vst_dayofmonth'][0])\ndf_test.drop('visitStartTime', axis = 1, inplace = True)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:37:18.268549Z","iopub.execute_input":"2022-07-25T18:37:18.268904Z","iopub.status.idle":"2022-07-25T18:37:18.701584Z","shell.execute_reply.started":"2022-07-25T18:37:18.268875Z","shell.execute_reply":"2022-07-25T18:37:18.700630Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"format_str = '%Y%m%d'\ndf_test['formated_date'] = df_test['date'].apply(lambda x: datetime.strptime(str(x), format_str))\ndf_test['year'] = df_test['formated_date'].apply(lambda x:x.year)\ndf_test['month'] = df_test['formated_date'].apply(lambda x:x.month)\ndf_test['quarterMonth'] = df_test['formated_date'].apply(lambda x:x.day//8)\ndf_test['day'] = df_test['formated_date'].apply(lambda x:x.day)\ndf_test['weekday'] = df_test['formated_date'].apply(lambda x:x.weekday())\n\ndf_test.drop(['date','formated_date'], axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:37:22.251538Z","iopub.execute_input":"2022-07-25T18:37:22.252147Z","iopub.status.idle":"2022-07-25T18:37:40.532450Z","shell.execute_reply.started":"2022-07-25T18:37:22.252110Z","shell.execute_reply":"2022-07-25T18:37:40.531474Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"irrelavant_features = ['fullVisitorId', 'visitId', 'socialEngagementType' , 'totals.visits']\nfor col in irrelavant_features:\n    df_test.drop(col, axis = 1, inplace = True)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:37:43.318366Z","iopub.execute_input":"2022-07-25T18:37:43.319335Z","iopub.status.idle":"2022-07-25T18:37:44.600892Z","shell.execute_reply.started":"2022-07-25T18:37:43.319283Z","shell.execute_reply":"2022-07-25T18:37:44.599574Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"le = LabelEncoder()\nprint('Categorical columns that will be converted:')\nfor col in df_test.columns:\n    if df_test[col].dtype == 'O':\n        print(col)\n        #print(col, train[col].unique())\n        df_test.loc[:, col] = le.fit_transform(df_test.loc[:, col])","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:37:46.570814Z","iopub.execute_input":"2022-07-25T18:37:46.571382Z","iopub.status.idle":"2022-07-25T18:37:53.122638Z","shell.execute_reply.started":"2022-07-25T18:37:46.571347Z","shell.execute_reply":"2022-07-25T18:37:53.121645Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('Columns in train and not in test are: ',set(df_train)-set(df_test))","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:38:00.726338Z","iopub.execute_input":"2022-07-25T18:38:00.726966Z","iopub.status.idle":"2022-07-25T18:38:00.732443Z","shell.execute_reply.started":"2022-07-25T18:38:00.726929Z","shell.execute_reply":"2022-07-25T18:38:00.731265Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We made sure that there's no extra columns in train data than test data except our target 'totals.transactionReveue'","metadata":{}},{"cell_type":"markdown","source":"## Modelling","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":"2022-07-25T18:38:12.745028Z","iopub.execute_input":"2022-07-25T18:38:12.746004Z","iopub.status.idle":"2022-07-25T18:38:12.751631Z","shell.execute_reply.started":"2022-07-25T18:38:12.745957Z","shell.execute_reply":"2022-07-25T18:38:12.750618Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"##### I'll divide data to achieve almost 60% train - 20% validation and 20% test","metadata":{}},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"print(len(df_train) , len(df_test))\nprint(len(df_test) / len(df_train) * 100)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:38:18.102746Z","iopub.execute_input":"2022-07-25T18:38:18.103179Z","iopub.status.idle":"2022-07-25T18:38:18.115003Z","shell.execute_reply.started":"2022-07-25T18:38:18.103138Z","shell.execute_reply":"2022-07-25T18:38:18.112844Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(len(df_train) - len(df_test) )","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:38:21.831411Z","iopub.execute_input":"2022-07-25T18:38:21.831803Z","iopub.status.idle":"2022-07-25T18:38:21.837906Z","shell.execute_reply.started":"2022-07-25T18:38:21.831766Z","shell.execute_reply":"2022-07-25T18:38:21.836797Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"y = df_train['totals.transactionRevenue']\n","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:38:24.298192Z","iopub.execute_input":"2022-07-25T18:38:24.299161Z","iopub.status.idle":"2022-07-25T18:38:24.304778Z","shell.execute_reply.started":"2022-07-25T18:38:24.299113Z","shell.execute_reply":"2022-07-25T18:38:24.303535Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"y","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:38:26.881127Z","iopub.execute_input":"2022-07-25T18:38:26.881471Z","iopub.status.idle":"2022-07-25T18:38:26.889553Z","shell.execute_reply.started":"2022-07-25T18:38:26.881441Z","shell.execute_reply":"2022-07-25T18:38:26.888526Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.drop('totals.transactionRevenue' , axis=1, inplace=True)\n","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:38:29.189382Z","iopub.execute_input":"2022-07-25T18:38:29.189760Z","iopub.status.idle":"2022-07-25T18:38:30.007055Z","shell.execute_reply.started":"2022-07-25T18:38:29.189707Z","shell.execute_reply":"2022-07-25T18:38:30.006037Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X = df_train\nX.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:38:32.179115Z","iopub.execute_input":"2022-07-25T18:38:32.179460Z","iopub.status.idle":"2022-07-25T18:38:32.186005Z","shell.execute_reply.started":"2022-07-25T18:38:32.179431Z","shell.execute_reply":"2022-07-25T18:38:32.185085Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train = X[:1306748]\nX_val = X[1306749:]\n\ny_train = y[:1306748]\ny_val = y[1306749:]\n\nprint(X_train.shape , y_train.shape)\nprint(X_val.shape , y_val.shape)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:38:34.815291Z","iopub.execute_input":"2022-07-25T18:38:34.815685Z","iopub.status.idle":"2022-07-25T18:38:34.822391Z","shell.execute_reply.started":"2022-07-25T18:38:34.815654Z","shell.execute_reply":"2022-07-25T18:38:34.821335Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":" model.fit(\n        X_train, np.log1p(y_train),\n        eval_set = [(X_val, np.log1p(y_val))],\n        early_stopping_rounds = 100,\n        verbose = 100,\n        eval_metric = 'rmse'\n    )","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:38:37.671235Z","iopub.execute_input":"2022-07-25T18:38:37.671591Z","iopub.status.idle":"2022-07-25T18:39:45.027103Z","shell.execute_reply.started":"2022-07-25T18:38:37.671559Z","shell.execute_reply":"2022-07-25T18:39:45.026314Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Check features Importance","metadata":{}},{"cell_type":"code","source":"features_importance = pd.DataFrame()\nfeatures_importance['feature'] = X_train.columns\nfeatures_importance['importance'] = model.booster_.feature_importance(importance_type = 'gain')\nfeatures_importance.sort_values(by = 'importance', ascending = False)[:10]","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:40:54.752745Z","iopub.execute_input":"2022-07-25T18:40:54.753759Z","iopub.status.idle":"2022-07-25T18:40:54.770270Z","shell.execute_reply.started":"2022-07-25T18:40:54.753702Z","shell.execute_reply":"2022-07-25T18:40:54.769131Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"predictions = model.predict(X_val, num_iteration = model.best_iteration_)\npredictions[predictions < 0] = 0","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:41:00.879228Z","iopub.execute_input":"2022-07-25T18:41:00.879967Z","iopub.status.idle":"2022-07-25T18:41:08.579581Z","shell.execute_reply.started":"2022-07-25T18:41:00.879927Z","shell.execute_reply":"2022-07-25T18:41:08.578780Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"mean_squared_error(np.log1p(y_val), predictions)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:41:08.581657Z","iopub.execute_input":"2022-07-25T18:41:08.582359Z","iopub.status.idle":"2022-07-25T18:41:08.595334Z","shell.execute_reply.started":"2022-07-25T18:41:08.582320Z","shell.execute_reply":"2022-07-25T18:41:08.594593Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_predictions = pd.DataFrame()\ntest_predictions = model.predict(df_test[X_train.columns], num_iteration = model.best_iteration_)\ntest_predictions[test_predictions < 0] = 0\ntest_predictions","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:41:08.596776Z","iopub.execute_input":"2022-07-25T18:41:08.597429Z","iopub.status.idle":"2022-07-25T18:41:17.866289Z","shell.execute_reply.started":"2022-07-25T18:41:08.597390Z","shell.execute_reply":"2022-07-25T18:41:17.865552Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_predictions = pd.DataFrame(test_predictions)\ntest_predictions.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-25T18:41:17.870052Z","iopub.execute_input":"2022-07-25T18:41:17.871663Z","iopub.status.idle":"2022-07-25T18:41:17.880615Z","shell.execute_reply.started":"2022-07-25T18:41:17.871636Z","shell.execute_reply":"2022-07-25T18:41:17.879475Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_Ids = pd.read_csv('../input/ga-customer-revenue-prediction/test_v2.csv', usecols=['fullVisitorId'])\ntest_Ids['fullVisitorId']= test_Ids['fullVisitorId'].astype(str)\ntest_Ids.dtypes","metadata":{"execution":{"iopub.status.busy":"2022-07-25T19:22:00.223597Z","iopub.execute_input":"2022-07-25T19:22:00.224289Z","iopub.status.idle":"2022-07-25T19:23:28.926554Z","shell.execute_reply.started":"2022-07-25T19:22:00.224252Z","shell.execute_reply":"2022-07-25T19:23:28.925616Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission = pd.concat([test_Ids , test_predictions] , axis=1)\nsubmission.columns = ['fullVisitorId','PredictedLogRevenue']\nsubmission.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T19:28:17.472279Z","iopub.execute_input":"2022-07-25T19:28:17.472637Z","iopub.status.idle":"2022-07-25T19:28:17.494588Z","shell.execute_reply.started":"2022-07-25T19:28:17.472608Z","shell.execute_reply":"2022-07-25T19:28:17.493763Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission = submission.groupby('fullVisitorId').sum().reset_index()\nsubmission.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T19:28:40.606293Z","iopub.execute_input":"2022-07-25T19:28:40.606643Z","iopub.status.idle":"2022-07-25T19:28:41.113702Z","shell.execute_reply.started":"2022-07-25T19:28:40.606614Z","shell.execute_reply":"2022-07-25T19:28:41.112670Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission.to_csv('submit.csv', index=False)","metadata":{"execution":{"iopub.status.busy":"2022-07-25T19:28:54.250120Z","iopub.execute_input":"2022-07-25T19:28:54.250842Z","iopub.status.idle":"2022-07-25T19:28:55.336766Z","shell.execute_reply.started":"2022-07-25T19:28:54.250794Z","shell.execute_reply":"2022-07-25T19:28:55.335772Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Credits of \n#####  -Memory reduce idea & method to \"Customer Revenue Prediction V2 : playground notebook\"\n#####  -Date transorm and tuning of some model parameters to: \"Basics of Google Analytics notebook\"\n","metadata":{}},{"cell_type":"markdown","source":"### Finally you can read more about Lightgbm from here:\n[https://lightgbm.readthedocs.io/en/latest/pythonapi/lightgbm.LGBMRegressor.html](http://)\n[https://machinelearningmastery.com/light-gradient-boosted-machine-lightgbm-ensemble/](http://)","metadata":{}}]}