{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":" Objective:\n 1.To explore the given dataset and find the insights .\n 2.Building a Model to predict revenue per customer."},{"metadata":{"trusted":true,"_uuid":"f667c695a91a6d298090aae655803ba4c953670e"},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport os\nprint(os.listdir(\"../input\"))\nimport json\nfrom pandas.io.json import json_normalize\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport lightgbm as lgb\nimport lightgbm as lgb\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.model_selection import KFold\nimport time\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e1ed3fcbd6145b61cd9d3e1778b3690efc5948ae"},"cell_type":"code","source":"from IPython.core.interactiveshell import InteractiveShell\nInteractiveShell.ast_node_interactivity='all'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ed7ec9e6db860ed72f8c832aad83be402f65e17b"},"cell_type":"markdown","source":"Loading the file,as some of the columns in the file are in JSON format, we will need to coverts them into csv"},{"metadata":{"trusted":true,"_uuid":"0e2dd31da3dff8abc35f570d10c7a28b259754f7"},"cell_type":"code","source":"def load_dataset(path,nrows=None):\n    cols=['channelGrouping', 'date', 'device', 'fullVisitorId', 'geoNetwork',\n        'socialEngagementType', 'totals', 'trafficSource', 'visitId','customDimensions',\n        'visitNumber', 'visitStartTime']\n    json_cols=['device','geoNetwork', 'totals', 'trafficSource']\n    df = pd.read_csv(path, \n                     converters={column: json.loads for column in json_cols}, \n                     dtype={'fullVisitorId': 'str'}, # Important!!\n                     nrows=nrows, usecols=cols)\n    for column in json_cols:\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    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9a807aa816137dc456d5147dc40bbf684eb009c9"},"cell_type":"code","source":"%%time\ntrain=load_dataset('../input/train_v2.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e5aa8df150f43a0b16a4f30769224ce5505a5f02"},"cell_type":"code","source":"%%time\ntest=load_dataset('../input/test_v2.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"82577de958996b61d483d2caa53798ba73017e3c"},"cell_type":"code","source":"#Understanding the data and features in the test and train \n#We can see that in all there are 59 features in training_set, while test set has 58 features with 'trafficSource.campaignCode' missing in it.    \ntrain.shape\ntest.shape\n\ntrain.head(5)\ntest.head(5)\n\ntrain.columns.difference(test.columns)\ntest.columns.difference(train.columns)\ntrain.dtypes\n\ndel train['trafficSource.campaignCode']\ndata.head(5)\ndata.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"65bce1bc8a9c6c1a9b7162ab0f6f694e756d56ed"},"cell_type":"code","source":"# if we see the features below has only one distinct value, which can't be useful for training the model, hence dropping the columns.\nfor cols in train.columns:\n    if(train[cols].nunique() == 1):\n        train[cols].value_counts()\n        del train[cols]\n\nfor cols in test.columns:\n    if(test[cols].nunique() == 1):\n        del test[cols]\n        \n#Assigning the values'Train' and 'Test' to train and test data.\ntrain['set']='Train'\ntest['set']='Test'\n\n# Combining the train and test set for analyses, proprocessing and exploratory data analysis and then dividing back as given\ndata= pd.concat([train,test],axis=0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"105070df94040cdd50079dbac571da9a3e22d353"},"cell_type":"code","source":"data.drop('customDimensions',inplace=True,axis=1)\ndata.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f133b6bc1213ec1b9cd6e25df1076b0db1cf414c"},"cell_type":"code","source":"check=pd.to_datetime(data['date'])\ndates=data['date'].apply(lambda x: pd.to_datetime(str(x),unit='s'))\n                    \nhr=dates.dt.hour\n#hr.value_counts()\nmint=dates.dt.minute\n#mint.value_counts().plot(kind='bar',color='orange')\nsec=dates.dt.second\n#sec.value_counts()\n#dates.value_counts()\nhr_year= pd.pivot_table(data=data,index=['year'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"10a1e59f798c3f4b839ee3d96c7b9882421a6fc7"},"cell_type":"code","source":"#finding the percentage of missing values in columns\nfor col in data.columns:\n    if data[col].isnull().sum() > 0:\n        rate = data[col].isnull().sum() * 100 / data.shape[0]\n        print(f'Column {col} has {rate:.4f}% missing values.')\n    if data[col].dtype == 'object':\n        if (data[col] == 'not available in demo dataset').sum() > 0:\n            rate = (data[col] == 'not available in demo dataset').sum() * 100 / data.shape[0]\n            print(f'Column {col} has {rate:.4f}% values not available in dataset.')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ca7cddb3c413c73fbb3768011841c3348c551b59"},"cell_type":"code","source":"# the data type of the date variable is object, changing it to date. Lets us break the date in Day, Date, Weeks and check for the revenue generated\nimport datetime\ndata['date']=data['date'].apply(lambda x: pd.to_datetime(str(x),format='%Y%m%d'))\ndata['year']=data['date'].dt.year\ndata['month']=data['date'].dt.month\ndata['day']=data['date'].dt.day\ndata['weekday']=data['date'].dt.weekday","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"787588ff2cc2059947445540f86cc4a97761b37a"},"cell_type":"code","source":"data.head(4)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8c030d83ffdc44e667fbcfde850aa732c673b7e4"},"cell_type":"code","source":"#Analysing the response variable\n# we can see that the target variable, almost 98.9 percent of values are null. i.e. the distribution of the data is imbalanced\nnot_null=pd.notnull(data['totals.transactionRevenue']).sum()\nprint(f'No of records generating the transactions : {not_null}')\nnull_val=round(pd.isnull(data['totals.transactionRevenue']).sum()/data.shape[0],4)\nprint(f'No of records with Null transaction : {null_val}')\n\ndata['totals.transactionRevenue']=data['totals.transactionRevenue'].fillna('0')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ae5dfb86e5eb05e11d2333e6973d5612498b4523"},"cell_type":"code","source":"plt.subplots_adjust(wspace=2)\nplt.figure(figsize=(15,5))\n#Most of the search for gstore are organic search and Social search.\nplt.subplot(1,2,1)\ndata['channelGrouping'].value_counts().plot(kind='bar',color='orange')\n\n# We can see that the referral channel, has more total revenew\n# if we compare both the graphs, we can see the number of visits through channel 'Organic search' and ' Social' are high, but the revenue generated by them is less\n# The average revenuw generated from Referral is high\ndata['totals.transactionRevenue']=data['totals.transactionRevenue'].astype('int')\nplt.subplot(1,2,2)\nchannelwise_renew= pd.pivot_table(data=data,index='channelGrouping',values='totals.transactionRevenue',aggfunc='mean').reset_index()\nchannelwise_renew=channelwise_renew.sort_values(by='totals.transactionRevenue',ascending=False)\nsns.barplot(data=channelwise_renew,x='channelGrouping',y='totals.transactionRevenue',color='orange')\nplt.xticks(rotation=90)\nplt.show();\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"53314d6c8c3b987113f73488e0986fb1b4e9b315"},"cell_type":"code","source":"# From the below graphs, we can see visualize the percentage of the Channel, generating the revenue\nplt.subplots_adjust(wspace=2)\nplt.figure(figsize=(15,5))\nrenew_count=data[data['totals.transactionRevenue']!=0]['channelGrouping'].value_counts().sort_index()\nno_renew_count=data[data['totals.transactionRevenue']==0]['channelGrouping'].value_counts().sort_index()\nno_renew_count=no_renew_count.rename(cols=['index','NoTrans'])\nreve_cnt= pd.concat([renew_count,no_renew_count],axis=1)\nreve_cnt=reve_cnt.rename(columns={'channelGrouping':'Revenue',0:'NoRevenue'}).apply(lambda x: x/x.sum()*100,axis=1)\nreve_cnt=reve_cnt.reset_index()\nplt.subplot(1,3,1)\nsns.barplot(data=reve_cnt,x='index',y='NoRevenue',color='orange')\nplt.xticks(rotation=90)\nplt.xlabel('Channel')\nplt.subplot(1,3,2)\nsns.barplot(data=reve_cnt,x='index',y='Revenue',color='red')\nplt.xticks(rotation=90)\nplt.xlabel('Channel')\nplt.subplot(1,3,3)\nsns.barplot(data=reve_cnt,x='index',y='NoRevenue',color='orange')\nsns.barplot(data=reve_cnt,x='index',y='Revenue',color='red')\nplt.xticks(rotation=90)\nplt.xlabel('Channel')\nplt.show();\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"47c49d2dada746a1c4fbbd1d820956501b17413d"},"cell_type":"code","source":"#checking the revenue growth yearwise, we can see that revenue has been decreased from 2016 to 2018 and the same goes with the channels.\nplt.subplots_adjust(wspace=2)\nplt.figure(figsize=(15,8))\nplt.subplot(1,2,1)\ndata.groupby('year')['totals.transactionRevenue'].mean().plot(kind='bar',color='orange')\nplt.subplot(1,2,2)\nyear_revenew=pd.pivot_table(data=data,index=['year','channelGrouping'],values='totals.transactionRevenue',aggfunc='mean').reset_index()\nsns.barplot(data=year_revenew,x='year',y='totals.transactionRevenue',hue='channelGrouping')\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c9ee83d30b3c32bdd288c1706c73728cac0124e5"},"cell_type":"code","source":"# We can see the spikes in the total revenuw generated and could also see that bigger transaction amount have increased the variance in some months\n# If we see the revenue generated in 2016 and 2017, there can be pattern where the revenue is gradually decreasing from august till november and then slowly \n# increasing\nplt.subplots_adjust(wspace=2)\nplt.figure(figsize=(15,8))\nplt.subplot(1,2,1)\nyear_revenew=pd.pivot_table(data=data,index=['month','year'],values='totals.transactionRevenue',aggfunc='mean').reset_index()\nsns.barplot(data=year_revenew,x='month',y='totals.transactionRevenue',color='orange')\nax=plt.subplot(1,2,2)\nsns.lineplot(data=year_revenew,x='month',y='totals.transactionRevenue',hue='year',markers=True, style='year')\nax.grid(b=True, which='major', color='w', linewidth=1.0)\nplt.xticks(range(0,13))\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"09c336231a39aa836bbdf51f2f0343d861acc9fa"},"cell_type":"code","source":"#Deleting the repeated columsn\n# We can see that there are spikes in April'2017, and later in July'2018, may be due to some offers, need to check\nplt.figure(figsize=(15,8))\nasp=data.groupby('date')['totals.transactionRevenue'].mean().plot()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"155735b932ee1d6115b965f4cd0e9f5bb78d9e3e"},"cell_type":"code","source":"year_revenew=pd.pivot_table(data=data,index='weekday',values='totals.transactionRevenue',aggfunc='mean').reset_index()\nsns.lineplot(data=year_revenew,x='weekday',y='totals.transactionRevenue')\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f4dc1f91ae7cc3ce8e108ff411b8869e0739ba57"},"cell_type":"code","source":"# deleting columns with unique values\ndata.columns\ndel data['visitId']\n#del data['visitStartTime'] -----can be usefull\ndel data['visitNumber']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"32de4cb635dbccf3bc619b9133fe915252299bd4"},"cell_type":"code","source":"#We can see that most of the browser are not specified as the cookies are disabled . \n# Firefox and Chrome, brings out most the total revenue, this can help us to increase the revenue by concentrating on this browser.\n#data['device.browser'].value_counts()\n\nbrowz=data.groupby('device.browser')['totals.transactionRevenue'].mean().sort_index()\nbrowz=pd.DataFrame(browz)\nbrowz[browz['totals.transactionRevenue']>0].sort_values(ascending=False, by='totals.transactionRevenue').plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"50360ac1af78ba2b4af4f458be813e2e565e7b38"},"cell_type":"code","source":"#data['trafficSource.source'].value_counts()\ndata.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"357dc09117e3dfc2f0bbd32afeb791cbe3f1ba36"},"cell_type":"code","source":"# If we see with the total revenue generated most of it comes from Desktop,but we can see that there are outliers in revenue amount\n# Grouping the categories, channel and revenue, most of the revenue is generated through the display accessed through the desktop application\nplt.subplots_adjust(wspace=2)\nplt.figure(figsize=(15,6))\nchanel_device=pd.pivot_table(data=data,index=['channelGrouping','device.deviceCategory'],values='totals.transactionRevenue',aggfunc='mean').reset_index()\nplt.subplot(1,2,1)\nsns.barplot(data=chanel_device,x='device.deviceCategory',y='totals.transactionRevenue')\nplt.xlabel('DeviceCategory')\nplt.subplot(1,2,2)\ndata['device.deviceCategory'].value_counts()\nsns.barplot(data=chanel_device,x='channelGrouping',y='totals.transactionRevenue',hue='device.deviceCategory')\nplt.xticks(rotation=90)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"daeb3c00352144417837dbcd91436c0a81c300c7"},"cell_type":"code","source":"# Total revenue generated through the mobile is low as compared to the Desktop\ndata['device.isMobile'].value_counts()\nround(data.groupby('device.isMobile')['totals.transactionRevenue'].mean(),2).plot(kind='bar')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f2f92bcc813788f982f22491ff67e4633a83172f"},"cell_type":"code","source":"# We can see that total revenue generated by operating system through which the Gstore is accessed is 'Windows' and 'MAC'.\n# But there is variance and outliers, that can be seen in blow graphs.\n\nplt.subplots_adjust(hspace=5)\nplt.figure(figsize=(20,6))\nplt.subplot(1,3,1)\ndata['device.operatingSystem'].value_counts().plot(kind='bar',color='orange')\nplt.subplot(1,3,2)\nop_device=pd.pivot_table(data=data,index=['device.deviceCategory','device.operatingSystem','channelGrouping'],values='totals.transactionRevenue',aggfunc='mean').reset_index()\nop_device1=op_device[op_device['totals.transactionRevenue']>0]\nsns.barplot(data=op_device1,x='device.operatingSystem',y='totals.transactionRevenue',hue='device.deviceCategory',dodge=False)\nplt.xticks(rotation=90)\nplt.subplot(1,3,3)\nsns.barplot(data=op_device1,x='device.operatingSystem',y='totals.transactionRevenue',hue='channelGrouping',dodge=False)\nplt.legend(loc='upper left')\nplt.xticks(rotation=90)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ee12238af5172b38ea208dd425f81656d8774bca"},"cell_type":"code","source":"# Checking the revenue generated city wise\n#data['geoNetwork.city'].value_counts()\nplt.figure(figsize=(24,6))\ncity_rev=pd.DataFrame(data.groupby('geoNetwork.city')['totals.transactionRevenue'].mean()).reset_index()\ncity_rev1=city_rev[city_rev['totals.transactionRevenue']>0].sort_values(by='totals.transactionRevenue',ascending=False)\nsns.barplot(data=city_rev1.head(50),x='geoNetwork.city',y='totals.transactionRevenue',color='orange')\nplt.xticks(rotation=90)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2cfa9a83535849ca8f4ac6f9eae8e016e45acef2"},"cell_type":"code","source":"# average revenue comes from Americas\ndata.columns\nplt.subplots_adjust(hspace=5)\nplt.figure(figsize=(15,6))\nplt.subplot(1,2,1)\n#data.groupby(['geoNetwork.country','geoNetwork.city'])['totals.transactionRevenue'].mean()\ndata.groupby('geoNetwork.continent')['totals.transactionRevenue'].mean().plot(kind='bar',color='orange')\nplt.subplot(1,2,2)\ncountry_rev=pd.DataFrame(data.groupby('geoNetwork.country')['totals.transactionRevenue'].mean()).reset_index()\ncountry_rev=country_rev[country_rev['totals.transactionRevenue']>0].sort_values(by='totals.transactionRevenue',ascending=False)\nsns.barplot(data=country_rev.head(15),x='geoNetwork.country',y='totals.transactionRevenue', color='orange')\nplt.xticks(rotation=90)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"153b794f6d1703e97e9991c51c2e98d3263bd542"},"cell_type":"code","source":"#data.columns\n#data['geoNetwork.metro'].value_counts()\nplt.figure(figsize=(15,6))\nmetro_rev=pd.DataFrame(data.groupby('geoNetwork.metro')['totals.transactionRevenue'].mean()).reset_index()\nmetro_rev=metro_rev[metro_rev['totals.transactionRevenue']>0].sort_values(by='totals.transactionRevenue',ascending=False)\nsns.barplot(data=metro_rev.head(30),x='geoNetwork.metro',y='totals.transactionRevenue',color='orange')\nplt.xticks(rotation=90)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bbeb4d41c2a6713f77901b3ded6d5515742a9930"},"cell_type":"code","source":"#data.columns\n#data['geoNetwork.region'].value_counts()\nplt.figure(figsize=(15,6))\nnet_reg=pd.pivot_table(data=data, index=['geoNetwork.region'],values='totals.transactionRevenue',aggfunc='mean').reset_index()\nnet_reg=net_reg[net_reg['totals.transactionRevenue']>0].sort_values(by='totals.transactionRevenue',ascending=False)\nsns.barplot(data=net_reg.head(40),x='geoNetwork.region',y='totals.transactionRevenue',color='orange')\nplt.xticks(rotation=90)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d58d04452035f18734d457b9fc8ceaa9ddeb1746"},"cell_type":"code","source":"data['totals.hits']=data['totals.hits'].fillna(1) \ndata['totals.hits']=data['totals.hits'].astype('Int64')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"94bfe98bd61d2a0c4cbcf8f9ad8ef0d8f4f5a539"},"cell_type":"code","source":"# We can see that some relation coming out from hits and total revenue, if the hits are more, there is high chances transaction\nplt.figure(figsize=(10,5))\n#data['totals.hits'].value_counts()\n#data['totals.hits'].isnull().sum()\nhits_rev=pd.pivot_table(data=data,index='totals.hits',values='totals.transactionRevenue',aggfunc='mean')\nhits_rev=hits_rev.reset_index().sort_values(by='totals.hits',ascending=False)\nhits_rev1=hits_rev[hits_rev['totals.transactionRevenue']>0].sort_values(by='totals.hits',ascending=False)\nsns.scatterplot(data=hits_rev1,x='totals.hits',y='totals.transactionRevenue')\nplt.xticks(rotation=90)\nplt.ylim(-10,1.683176e+08)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"297728f5b2b8a07a308f6d711c20e96c218a2201"},"cell_type":"code","source":"hits_rev['totals.transactionRevenue'].min()\nhits_rev['totals.transactionRevenue'].max()\nhits_rev.quantile([0.0,0.50,0.75,0.9])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5068f63e3a483238e7eeb33e7326fdb2da35083f"},"cell_type":"code","source":"#filling by1\ndata['totals.pageviews']=data['totals.pageviews'].fillna(1) \ndata['totals.pageviews']=data['totals.pageviews'].astype('Int64')\ndata['totals.pageviews'].dtypes\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1bc162602a32318780f4afd284ae0b2507a31d76"},"cell_type":"code","source":"# We can also see that with the increase in the pageview, the transactionRevenue also increases.\nplt.figure(figsize=(10,5))\n#data.columns\n#data['totals.pageviews'].value_counts()\npage_rev=pd.pivot_table(data=data,index='totals.pageviews',values='totals.transactionRevenue',aggfunc='mean')\npage_rev=page_rev.reset_index().sort_values(by='totals.pageviews',ascending=False)\n#hits_rev1=hits_rev[hits_rev['totals.transactionRevenue']>0].sort_values(by='totals.hits',ascending=False)\nsns.scatterplot(data=page_rev,x='totals.pageviews',y='totals.transactionRevenue')\nplt.xticks(rotation=90)\nplt.ylim(-10,1.683176e+08)\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1bf7f16960b07a7648152344c307fa1af35ddca6"},"cell_type":"code","source":"# Higher the sessionQualityDim, there are high chances transaction and generate the revenue\ndata['totals.sessionQualityDim']= data['totals.sessionQualityDim'].fillna(0)\ndata['totals.sessionQualityDim']=data['totals.sessionQualityDim'].astype('Int64')\n#data.groupby('totals.sessionQualityDim')['totals.transactionRevenue'].mean()\nplt.figure(figsize=(10,5))\nsession_rev= pd.pivot_table(data=data,index='totals.sessionQualityDim',values='totals.transactionRevenue',aggfunc='mean').reset_index()\n#session_rev\nsns.scatterplot(data=session_rev,x='totals.sessionQualityDim',y='totals.transactionRevenue')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4eebb1c2a8981a7fce217cae9abd31dc31000f78"},"cell_type":"code","source":"#fillinh NA values with mode 5\n#data['totals.timeOnSite'].isnull().sum()\ndata['totals.timeOnSite']=data['totals.timeOnSite'].fillna('5')\ndata['totals.timeOnSite']=data['totals.timeOnSite'].astype('Int64')\nplt.figure(figsize=(10,5))\nsite_rev= pd.pivot_table(data=data,index='totals.timeOnSite',values='totals.transactionRevenue',aggfunc='mean').reset_index()\nsns.scatterplot(data=site_rev,x='totals.timeOnSite',y='totals.transactionRevenue')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"25fd6241a93ab92c28ff1d6b251a1750f71b26de"},"cell_type":"code","source":"# Total Transaction revenue is generated through the Referral.\ndata['totals.totalTransactionRevenue']=data['totals.totalTransactionRevenue'].fillna(0)\ndata['totals.totalTransactionRevenue']=data['totals.totalTransactionRevenue'].astype('Int64')\ndata.groupby('channelGrouping')['totals.totalTransactionRevenue'].mean().plot(kind='bar',color='orange')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7c52c52b8a31083123e002cb8fe4ad0e6660b4b7"},"cell_type":"code","source":"data['totals.transactions'].value_counts()\ndata['totals.transactions']=data['totals.transactions'].fillna('0')\ndata['totals.transactions']=data['totals.transactions'].astype('Int64')\nplt.figure(figsize=(10,5))\n#data.groupby('totals.transactions')['totals.transactionRevenue'].mean().plot(kind='bar')\ntotal_trans= pd.pivot_table(data=data,index=['totals.transactions','channelGrouping'],values='totals.transactionRevenue',aggfunc='mean').reset_index()\nsns.barplot(data=total_trans,x='totals.transactions',y='totals.transactionRevenue',hue='channelGrouping',dodge=False)\nplt.legend(loc='upper right')\nplt.show();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8b647e3e032e1cb2525a9c3ec27584bfd5d3b834"},"cell_type":"code","source":"visit_trans=pd.pivot_table(data=data, index='channelGrouping',values='totals.transactions',aggfunc='count').reset_index()\nvisit_trans=visit_trans.sort_values(by='totals.transactions',ascending=False)\nsns.barplot(data=visit_trans,x='channelGrouping',y='totals.transactions',color='orange')\nplt.xticks(rotation=90)\nplt.show();\n#visit_trans","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ec424e7a0394e36e15069795ae1c39ed2be43132"},"cell_type":"code","source":"visit_trans1=pd.pivot_table(data=data, index='totals.transactions',values='totals.transactionRevenue',aggfunc='mean').reset_index()\nsns.barplot(data=visit_trans1,x='totals.transactions',y='totals.transactionRevenue',color='orange')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4d46e18fd9ecb13830300f7b5f449071bc4c18dd"},"cell_type":"code","source":"# Traffic Source :\n# Revenue generated through the adContent, is Paid Search.\n\ndata['trafficSource.adContent']=data['trafficSource.adContent'].fillna('No Add Content')\nplt.figure(figsize=(16,5))\nplt.subplots_adjust(hspace=5)\nplt.subplot(1,2,1)\n\nadd_rev=pd.DataFrame(data.groupby('trafficSource.adContent')['totals.transactionRevenue'].mean()).reset_index()\nadd_rev=add_rev.sort_values(by='totals.transactionRevenue',ascending=False)\nadd_rev1=add_rev[add_rev['totals.transactionRevenue']>0]\nsns.barplot(data=add_rev1,x='trafficSource.adContent',y='totals.transactionRevenue',color='blue')\nplt.xticks(rotation=90)\nplt.subplot(1,2,2)\nadd_chan_rev=pd.DataFrame(data.groupby(['trafficSource.adContent','channelGrouping'])['totals.transactionRevenue'].mean()).reset_index()\nadd_chan_rev=add_chan_rev.sort_values(by='totals.transactionRevenue',ascending=False)\nadd_chan_rev1=add_chan_rev[add_chan_rev['totals.transactionRevenue']>0]\nsns.barplot(data=add_chan_rev1,x='trafficSource.adContent',y='totals.transactionRevenue',hue='channelGrouping',dodge=False)\nplt.xticks(rotation=90)\nplt.legend(loc='upper right')\nplt.show();\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e723f601543e7eba0f0e276cc62a2e3779b3f81e"},"cell_type":"code","source":"data['trafficSource.adwordsClickInfo.adNetworkType'].value_counts()/data.shape[0]\ndata.groupby(['trafficSource.adwordsClickInfo.adNetworkType','channelGrouping'])['totals.transactionRevenue'].mean()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"04ebfbf4e13850844e627260c7d410d93ee55927"},"cell_type":"code","source":"data['trafficSource.adwordsClickInfo.adNetworkType']=data['trafficSource.adwordsClickInfo.adNetworkType'].fillna('No Network')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fc0100ae6d44c8f060b83d65046fda2f86f6a49b"},"cell_type":"code","source":"del data['trafficSource.adwordsClickInfo.gclId']\ndata['trafficSource.adwordsClickInfo.adNetworkType'].value_counts()\ndata['trafficSource.adwordsClickInfo.page']=data['trafficSource.adwordsClickInfo.page'].fillna('99')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3c98a6b25534e150277e19843a26e1fdefafd203"},"cell_type":"code","source":"# Top slot ADD, have more impact on Revenue\ndata['trafficSource.adwordsClickInfo.slot'].value_counts()\ndata['trafficSource.adwordsClickInfo.slot'].isnull().sum()\ndata.groupby('trafficSource.adwordsClickInfo.slot')['totals.transactionRevenue'].mean().plot(kind='bar')\ndata['trafficSource.adwordsClickInfo.slot']=data['trafficSource.adwordsClickInfo.slot'].fillna('No Add Slot')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6a4a316d00ac65e24e8f5d30e65761bba85ac401"},"cell_type":"code","source":"#data['trafficSource.keyword'].value_counts()\n#data['trafficSource.keyword'].isnull().sum()\nkey_word=pd.DataFrame(data.groupby(['trafficSource.keyword'])['totals.transactionRevenue'].mean()).reset_index()\nkey_word=key_word.sort_values(by='totals.transactionRevenue',ascending=False)\n#del data['trafficSource.keyword']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c1859e9e3d84a1592b06f2d0365ea4710fc1317a"},"cell_type":"code","source":"plt.figure(figsize=(10,5))\ndata['trafficSource.medium'].isnull().sum()\nmed=pd.DataFrame(data.groupby(['trafficSource.medium','channelGrouping'])['totals.transactionRevenue'].mean()).reset_index()\nmed=med.sort_values(by='totals.transactionRevenue',ascending=False)\nsns.barplot(data=med,x='trafficSource.medium',y='totals.transactionRevenue',hue='channelGrouping',dodge=False)\nplt.legend(loc='upper right')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"39f6fdd537582a7a8b7c0467008cb45e3753e35f"},"cell_type":"code","source":"data['trafficSource.referralPath']=data['trafficSource.referralPath'].fillna('No Reference Path')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6081ab527258d177678e1dce78b8eca7a764158d"},"cell_type":"code","source":"#data['trafficSource.source'].value_counts()\n#data.isnull().sum()\n#del data['trafficSource.adContent']\ndef process_device_b(data1):\n    i = str(data1)\n    if '_' in i:\n        return 'other'\n    elif '.' in i:\n        return 'other'\n    elif len(i)>25:\n        return 'other'\n    else:\n        return i\n    \ndata['device.browser']=data['device.browser'].apply(process_device_b)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3e7ad28e304d21b448c3f5a7a01cc84faf84d8ef"},"cell_type":"code","source":"from nltk.stem import PorterStemmer\n\nimport re\n\ndef key_word(key):\n    low_key=str(key).lower()\n    #low_key=stemmer.stem(low_key)\n    if 'goo' in low_key or 'google' in low_key or 'googl' in low_key or 'stor' in low_key:\n        return 'google'\n    if 'merchandi' in low_key:\n        return 'merchandise'\n    if 'youtube' in low_key or 'yo' in low_key or 'yuotub' in low_key or 'yuo tub' in low_key or 'tub' in low_key or 'yutob' in low_key:\n        return 'youtube'\n    if bool(re.search(r'\\d', low_key)):\n        return 'other'\n    if 'tshirt' in low_key or 't shirt' in low_key or 't-shirt' in low_key:\n        return 'tshirt'\n    if 'shirts' in low_key or 'shirt' in low_key:\n        return 'shirts'\n    if 'lamp' in low_key or 'lamps' in low_key:\n        return 'lamps'\n    if 'apparel' in low_key:\n        return 'apparel'\n    if len(low_key)>28:\n        return 'other'\n    if 'note' in low_key:\n        return 'notebook'\n    if 'android' in low_key:\n        return 'android'\n    else:\n        return low_key\n\n\ndata['trafficSource.keyword']=data['trafficSource.keyword'].apply(key_word)    \n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dc1eeb55db2b134418474694692df6560d3eef35"},"cell_type":"code","source":"def traffic_feature(source_value):\n    if 'google' in source_value:\n        return 'google'\n    elif 'yahoo' in source_value:\n        return 'yahoo'\n    elif 'facebook' in source_value:\n        return 'facebook'\n    elif 'youtube' in source_value:\n        return 'youtube'\n    elif 'pinterest' in source_value:\n        return 'pinterest'\n    elif 'baidu' in source_value:\n        return 'baidu'\n    elif 'reddit' in source_value:\n        return 'reddit'\n    elif 'wow' in source_value:\n        return 'wow'\n    elif len(source_value)>20:\n        return 'other'\n    elif 'bing' in source_value:\n        return 'bing'\n    else:\n        return source_value\n\ndata['trafficSource.source']=data['trafficSource.source'].apply(traffic_feature)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1f1f6063f09d9995e9928b646d29cfad78016f08"},"cell_type":"code","source":"data.head(5)\n#data['device.browser'].value_counts()\n# About 54 percent of the values are not available in dataset\n\nlen(data[data['geoNetwork.metro']=='not available in demo dataset'])/data.shape[0]*100\n#device.operatingSystem","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ed037ddfa4b8bb2ccc769d23988c8e9d2e5ec460"},"cell_type":"code","source":"data.groupby(['geoNetwork.networkDomain','channelGrouping'])['channelGrouping'].count()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"347a9f5b7463ca4f9b876e2742b1476aa79ca607"},"cell_type":"code","source":"drop_cols = ['trafficSource.referralPath', 'trafficSource.adContent', 'trafficSource.adwordsClickInfo.slot', 'trafficSource.adwordsClickInfo.page',\n            'trafficSource.adwordsClickInfo.adNetworkType','visitStartTime','device.isMobile']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bcd86e4d50529a5676ceeca3354b684b89571d80"},"cell_type":"code","source":"#data.drop(['visitStartTime','device.isMobile'])\ndata.head(10) #trafficSource.keyword","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fa378dfb64879897244b00a06951c4fac075de5e"},"cell_type":"code","source":"cols=['channelGrouping','year', 'month', 'day', 'weekday','device.browser', 'totals.hits','totals.pageviews','totals.sessionQualityDim',\n'device.deviceCategory','device.operatingSystem', 'geoNetwork.city', 'geoNetwork.continent','totals.timeOnSite',\n'geoNetwork.country', 'geoNetwork.metro', 'geoNetwork.networkDomain','trafficSource.adwordsClickInfo.slot','geoNetwork.region', 'geoNetwork.subContinent',\n'totals.totalTransactionRevenue','totals.transactions','trafficSource.campaign','trafficSource.keyword','trafficSource.medium','trafficSource.source','set','totals.transactionRevenue']\n\ndata=data[cols]\ndata.head(5)\ndata.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"405862d240abe77245ea278364f0138cf9010cb0"},"cell_type":"code","source":"from sklearn.preprocessing import LabelEncoder\ndata.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8172d0ab32ab37726a07150d13006ae5bbc1069a"},"cell_type":"code","source":"for i, t in data.dtypes.iteritems():\n    if t == object:\n        data[i] = pd.factorize(data[i])[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9f41e5f47203b833eea37f5985c5aa553315c510"},"cell_type":"code","source":"# spliting back the dataset into train and test set\ndata.shape\ntrain_df=data[data['set']==0]\ntest_df=data[data['set']==1]\ndel train_df['set']\ntrain_df.shape\ndel test_df['set']\ntest_df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"591e62c9f865a26740309d73dfc37c6e4c9dd4e4"},"cell_type":"code","source":"train_x=train_df.iloc[:,:-1]\ntrain_y=train_df.iloc[:,-1]\ntest_x=test_df.iloc[:,:-1]\ntest_y=test_df.iloc[:,-1]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b6ca48ba16e75bd9e7b71f1091eb836898bcf6dc"},"cell_type":"code","source":"train_x.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"feebcf63985d1fc81622b58608e9a992760a7855"},"cell_type":"code","source":"param = { 'objective':'regression',\n         'max_depth': -1,\n         'learning_rate':0.005,\n         \"min_child_samples\":40,\n         \"boosting\":\"gbdt\",\n         \"feature_fraction\":0.8,\n         \"bagging_freq\":1,\n         \"bagging_fraction\":0.8 ,\n         \"bagging_seed\": 3,\n         \"metric\": 'rmse',\n         \"lambda_l1\": 1,\n         'lambda_l2': 1,\n         \"verbosity\": -1}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fa359b7d560c1f583755f340fc1d1e30ac96ebbf"},"cell_type":"code","source":"train_y = train_y.apply(lambda x: np.log(x) if x > 0 else x)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a6f133b4abcd529f7a7622737fa4e1e821b08e03"},"cell_type":"code","source":"folds = KFold(n_splits=5, shuffle=True, random_state=15)\noof = np.zeros(len(train_df))\npredictions = np.zeros(len(test_df))\nstart = time.time()\nfeatures = list(train_df.columns)\nfeature_importance_df = pd.DataFrame()\n\nfor fold_, (trn_idx, val_idx) in enumerate(folds.split(train_x.values, train_y.values)):\n    trn_data = lgb.Dataset(train_x.iloc[trn_idx], label=train_y.iloc[trn_idx])\n    val_data = lgb.Dataset(train_x.iloc[val_idx], label=train_y.iloc[val_idx])\n    \n    num_round = 10000\n    clf = lgb.train(param, trn_data, num_round, valid_sets = [trn_data, val_data], verbose_eval=500 ,early_stopping_rounds = 500)\n    oof[val_idx] = clf.predict(train_x.iloc[val_idx].values, num_iteration=clf.best_iteration)\n    \n    fold_importance_df = pd.Series()\n    fold_importance_df[\"feature\"] = features\n    fold_importance_df[\"importance\"] = clf.feature_importance()\n    fold_importance_df[\"fold\"] = fold_ + 1\n    feature_importance_df = pd.concat([feature_importance_df, fold_importance_df], axis=0)\n    \n    predictions += clf.predict(test_x.values, num_iteration=clf.best_iteration) / folds.n_splits","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b0e675f511f7c2440ed8fb459e87d8c149108b8f"},"cell_type":"code","source":"print(\"CV score: {:<8.5f}\".format(mean_squared_error(oof, train_y)**0.5))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e2bb910af9237847e07230646c2378fb8d5bf794"},"cell_type":"code","source":"submission = pd.DataFrame()\n\nsubmission['fullVisitorId'] = test['fullVisitorId']\nsubmission['PredictedLogRevenue'] = predictions\n\nsubmit_file = submission[['fullVisitorId', 'PredictedLogRevenue']].groupby('fullVisitorId').sum().reset_index()\nsubmit_file.to_csv('submit_group.csv',index=False)\n","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}