{"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":"# Workshop 4: Google Analytics Customer Revenue Prediction\n\n\n\n### Contents of this Kernel\n\n1. Problem Statement  \n2. Dataset Understanding  \n3. Exploration  \n4. Visitor Profile  \n \n\n## 1. Problem Statement \n\n#### In this exercise https://www.kaggle.com/c/ga-customer-revenue-prediction , the aim is to analyze a Google Merchandise Store (also known as GStore, where Google swag is sold) customer dataset to predict revenue per customer. The exercise here is an explosition of the data and look into the GA data about the personas of the visitors and the analysis might lead to a better use of marketing budgets for those companies who choose to use data analysis on top of GA data. \n\n#### One of the skill here is learn a simple python code on how to convert the JSON format data usually collected over webs, mobile applications and IoT log files into a python data framework for analysis. \n\n### Questions:\n\n#### Q1: Identify the personna of the visitors who are contributing greater proportion of revenue to the merchant?\n#### Q2: Propose a A/B test experiment and formulate a hypothesis to test your obervations\n\n#### As the first step, lets load the required libraries.\n","metadata":{"_uuid":"163a1a9c9d4fc3f61c766fdf860af902bd8e762c"}},{"cell_type":"markdown","source":"Remember to download the text.csv (6G bit) from below link to the same directory for working in this Jupyter notebook exercises.\n\nSince github does not allow to upload file more than 2Gbit, the data file is placed on my Google drive as follows:\nhttps://drive.google.com/file/d/1euXsx5hfq0N5mowMyo3ecqEGcwzHalpT/view?usp=sharing\n","metadata":{}},{"cell_type":"code","source":"import os\nimport numpy as np \nimport pandas as pd \nimport json\nfrom pandas.io.json import json_normalize\nimport seaborn as sns \nimport matplotlib.pyplot as plt \nfrom plotly.offline import init_notebook_mode, iplot\nimport plotly.graph_objs as go\nfrom plotly import tools\ninit_notebook_mode(connected=True)","metadata":{"execution":{"iopub.status.busy":"2023-10-03T12:24:46.293621Z","iopub.execute_input":"2023-10-03T12:24:46.294289Z","iopub.status.idle":"2023-10-03T12:24:47.155165Z","shell.execute_reply.started":"2023-10-03T12:24:46.294151Z","shell.execute_reply":"2023-10-03T12:24:47.154216Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from scipy import stats\nfrom numpy import random\n\nimport time\nfrom datetime import datetime","metadata":{"execution":{"iopub.status.busy":"2023-10-03T12:24:47.156520Z","iopub.execute_input":"2023-10-03T12:24:47.156809Z","iopub.status.idle":"2023-10-03T12:24:47.160590Z","shell.execute_reply.started":"2023-10-03T12:24:47.156780Z","shell.execute_reply":"2023-10-03T12:24:47.159699Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%matplotlib inline\n\nimport plotly.offline as py\nfrom plotly.offline import init_notebook_mode, iplot\nimport plotly.graph_objs as go\nfrom plotly import tools\ninit_notebook_mode(connected=True)\n\ncolor = sns.color_palette()\nfrom plotly import subplots\nfrom plotly import version\n\npy.init_notebook_mode(connected=True)\n\nfrom sklearn import model_selection, preprocessing, metrics\n\npd.options.mode.chained_assignment = None\npd.options.display.max_columns = 999","metadata":{"execution":{"iopub.status.busy":"2023-10-03T12:24:47.162233Z","iopub.execute_input":"2023-10-03T12:24:47.162677Z","iopub.status.idle":"2023-10-03T12:24:47.373418Z","shell.execute_reply.started":"2023-10-03T12:24:47.162647Z","shell.execute_reply":"2023-10-03T12:24:47.372442Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"If your PC does not have plotly, you need to find way to install plotly to call in init_notebook_mode, iplot, plotly.graph_objs and tools","metadata":{"_uuid":"d53b5bdd24383e8eb78b163fa3047cd53febfc7d"}},{"cell_type":"markdown","source":"## 2. Understanding the Dataset\n\nThe data is shared in csv format. The csv files contains some filed with json objects. The description about dataset fields is given  https://www.kaggle.com/c/ga-customer-revenue-prediction/data \n\n","metadata":{"_uuid":"d53b5bdd24383e8eb78b163fa3047cd53febfc7d"}},{"cell_type":"markdown","source":"### 2.1 Dataset Preparation\n\nLets read the dataset in csv format and unwrap the json fields. Students can reference on https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.json_normalize.html \nand\nhttps://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields","metadata":{"_uuid":"d53b5bdd24383e8eb78b163fa3047cd53febfc7d"}},{"cell_type":"code","source":"# read in data\n\ndf = pd.read_csv(\"../input/ga-customer-revenue-prediction/test.csv\", nrows=10000)\ndf.shape","metadata":{"_uuid":"d53b5bdd24383e8eb78b163fa3047cd53febfc7d","execution":{"iopub.status.busy":"2023-10-03T12:24:47.374753Z","iopub.execute_input":"2023-10-03T12:24:47.375025Z","iopub.status.idle":"2023-10-03T12:24:47.667819Z","shell.execute_reply.started":"2023-10-03T12:24:47.374998Z","shell.execute_reply":"2023-10-03T12:24:47.666756Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# See the variables \ndf.head()\n","metadata":{"_uuid":"d53b5bdd24383e8eb78b163fa3047cd53febfc7d","execution":{"iopub.status.busy":"2023-10-03T12:24:47.668996Z","iopub.execute_input":"2023-10-03T12:24:47.669332Z","iopub.status.idle":"2023-10-03T12:24:47.698518Z","shell.execute_reply.started":"2023-10-03T12:24:47.669302Z","shell.execute_reply":"2023-10-03T12:24:47.697454Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# See the variables \ndf.dtypes","metadata":{"_uuid":"d53b5bdd24383e8eb78b163fa3047cd53febfc7d","execution":{"iopub.status.busy":"2023-10-03T12:24:47.699739Z","iopub.execute_input":"2023-10-03T12:24:47.700285Z","iopub.status.idle":"2023-10-03T12:24:47.708280Z","shell.execute_reply.started":"2023-10-03T12:24:47.700246Z","shell.execute_reply":"2023-10-03T12:24:47.707024Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Only \"device\", \"geoNetwork\", \"totals\", \"trafficSource\" are JSON objects whereas \"customDimensions\" and \"hits\" are different data nested dictionary objects. One will notice that the data strcuture of series of \"customDimensions\" and \"hits\" are more complicated, in this exercise, they are not included in the analysis.","metadata":{}},{"cell_type":"markdown","source":"#### Nested data: hits\n\"hits\" record all the pages footprints. The Google Analytics data variables description one can find some details about https://support.google.com/analytics/answer/3437719?hl=en. Google develops a BigQuery SQL API that one can access its GA data for further AB design testing, reference https://towardsdatascience.com/how-to-query-and-calculate-google-analytics-data-in-bigquery-cab8fc4f396 (which we will come back to look into it in lecture 8-9)","metadata":{}},{"cell_type":"code","source":"pd.set_option('display.max_columns', None)\n\ndef load_df(csv_path='../input/ga-customer-revenue-prediction/test.csv', nrows=None):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    \n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'}, # Important!!\n                     nrows=nrows)\n    \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    print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n    return df\n","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2023-10-03T12:24:47.709843Z","iopub.execute_input":"2023-10-03T12:24:47.710216Z","iopub.status.idle":"2023-10-03T12:24:47.720763Z","shell.execute_reply.started":"2023-10-03T12:24:47.710155Z","shell.execute_reply":"2023-10-03T12:24:47.719983Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.set_option('display.max_columns', None)\n\ndef load_df(csv_path='../input/ga-customer-revenue-prediction/train.csv', nrows=None):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    \n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'}, # Important!!\n                     nrows=nrows)\n    \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    print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n    return df\n","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2023-10-03T12:24:47.722050Z","iopub.execute_input":"2023-10-03T12:24:47.722648Z","iopub.status.idle":"2023-10-03T12:24:47.733613Z","shell.execute_reply.started":"2023-10-03T12:24:47.722613Z","shell.execute_reply":"2023-10-03T12:24:47.732696Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = load_df()\ntrain.shape","metadata":{"execution":{"iopub.status.busy":"2023-10-03T12:24:47.736090Z","iopub.execute_input":"2023-10-03T12:24:47.736416Z","iopub.status.idle":"2023-10-03T12:27:04.987110Z","shell.execute_reply.started":"2023-10-03T12:24:47.736384Z","shell.execute_reply":"2023-10-03T12:27:04.986317Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"os.getcwd()","metadata":{"execution":{"iopub.status.busy":"2023-10-03T12:27:04.988866Z","iopub.execute_input":"2023-10-03T12:27:04.989373Z","iopub.status.idle":"2023-10-03T12:27:04.995220Z","shell.execute_reply.started":"2023-10-03T12:27:04.989336Z","shell.execute_reply":"2023-10-03T12:27:04.994211Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.to_csv('train_flat.csv', index=False)","metadata":{"execution":{"iopub.status.busy":"2023-10-03T12:27:04.996700Z","iopub.execute_input":"2023-10-03T12:27:04.997094Z","iopub.status.idle":"2023-10-03T12:27:30.440278Z","shell.execute_reply.started":"2023-10-03T12:27:04.997051Z","shell.execute_reply":"2023-10-03T12:27:30.439246Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Reload Flattened Dataset Directly","metadata":{}},{"cell_type":"code","source":"train = pd.read_csv(r'./train_flat.csv', dtype={'fullVisitorId': 'str'}, nrows=100000)\ntrain.head()","metadata":{"ExecuteTime":{"end_time":"2018-09-28T13:05:21.031589Z","start_time":"2018-09-28T13:04:43.655796Z"},"execution":{"iopub.status.busy":"2023-10-03T12:27:30.441507Z","iopub.execute_input":"2023-10-03T12:27:30.441813Z","iopub.status.idle":"2023-10-03T12:27:34.459539Z","shell.execute_reply.started":"2023-10-03T12:27:30.441777Z","shell.execute_reply":"2023-10-03T12:27:34.458712Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.shape","metadata":{"execution":{"iopub.status.busy":"2023-10-03T12:27:34.460513Z","iopub.execute_input":"2023-10-03T12:27:34.460806Z","iopub.status.idle":"2023-10-03T12:27:34.467313Z","shell.execute_reply.started":"2023-10-03T12:27:34.460777Z","shell.execute_reply":"2023-10-03T12:27:34.466034Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.info()","metadata":{"execution":{"iopub.status.busy":"2023-10-03T12:27:34.468981Z","iopub.execute_input":"2023-10-03T12:27:34.469450Z","iopub.status.idle":"2023-10-03T12:27:34.678695Z","shell.execute_reply.started":"2023-10-03T12:27:34.469405Z","shell.execute_reply":"2023-10-03T12:27:34.677427Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Run the whole data may take hours depending the computing efficiency of your machine, it is suggested to limit to data size of 100000","metadata":{}},{"cell_type":"markdown","source":"### 2.2 Dataset Snapshot\n\nLets view the snapshot of the test dataset. ","metadata":{"_uuid":"673e7c7367a4a5980560baa125e43376d64cd5ff"}},{"cell_type":"code","source":"print (\"There are \" + str(train.shape[0]) + \" rows and \" + str(train.shape[1]) + \" raw columns in this dataset\")\n\nprint (\"Snapshot: \", train.head())","metadata":{"_kg_hide-input":true,"_uuid":"9e761c2ef85fd6c9c94d7a155d639d6a5424d209","execution":{"iopub.status.busy":"2023-10-03T12:27:34.680185Z","iopub.execute_input":"2023-10-03T12:27:34.680624Z","iopub.status.idle":"2023-10-03T12:27:34.706982Z","shell.execute_reply.started":"2023-10-03T12:27:34.680581Z","shell.execute_reply":"2023-10-03T12:27:34.705753Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 2.3 Missing Values Percentage\n\nFrom the snapshot we can observe that there are many missing values in the dataset. Let's plot the missing values percentage for columns having missing values. \n\n> The following graph shows only those columns having missing values, all other columns are fine. ","metadata":{"_uuid":"e4d55abc37cefb9a341b3271b1c6f703c347237e"}},{"cell_type":"code","source":"miss_per = {}\nfor k, v in dict(train.isna().sum(axis=0)).items():\n    if v == 0:\n        continue\n    miss_per[k] = 100 * float(v) / len(train)\n    \nimport operator \nsorted_x = sorted(miss_per.items(), key=operator.itemgetter(1), reverse=True)\nprint (\"There are \" + str(len(miss_per)) + \" columns with missing values\")\n\nkys = [_[0] for _ in sorted_x][::-1]\nvls = [_[1] for _ in sorted_x][::-1]\ntrace1 = go.Bar(y = kys, orientation=\"h\" , x = vls, marker=dict(color=\"#d6a5ff\"))\nlayout = go.Layout(title=\"Missing Values Percentage\", \n                   xaxis=dict(title=\"Missing Percentage\"), \n                   height=400, margin=dict(l=300, r=300))\nfigure = go.Figure(data = [trace1], layout = layout)\niplot(figure)","metadata":{"_kg_hide-input":true,"_uuid":"44dfb5f91100da2e89986be56c0ccab162e089e4","execution":{"iopub.status.busy":"2023-10-03T12:27:34.708236Z","iopub.execute_input":"2023-10-03T12:27:34.708515Z","iopub.status.idle":"2023-10-03T12:27:35.718373Z","shell.execute_reply.started":"2023-10-03T12:27:34.708487Z","shell.execute_reply":"2023-10-03T12:27:35.717054Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"> - So we can observe that there are some columns in the dataset having very large number of missing values. \n\n## 3. Exploration - Univariate Analysis \n\nLets perform the univariate analysis and plot some distributions of variables in the dataset\n\n### 3.1 Device Attributes\n\nLets plot the distribution of device attributes","metadata":{"_uuid":"129c51c109ffa9659c881549cf0b29568d3d2993"}},{"cell_type":"code","source":"device_cols = [\"device.browser\", \"device.deviceCategory\", \"device.operatingSystem\"]\n\ncolors = [\"#d6a5ff\", \"#fca6da\", \"#f4d39c\", \"#a9fcca\"]\ntraces = []\nfor i, col in enumerate(device_cols):\n    t = train[col].value_counts()\n    traces.append(go.Bar(marker=dict(color=colors[i]),orientation=\"h\", y = t.index[:15][::-1], x = t.values[:15][::-1]))\n\nfig = tools.make_subplots(rows=1, cols=3, subplot_titles=[\"Visits: Category\", \"Visits: Browser\",\"Visits: OS\"], print_grid=False)\nfig.append_trace(traces[1], 1, 1)\nfig.append_trace(traces[0], 1, 2)\nfig.append_trace(traces[2], 1, 3)\n\nfig['layout'].update(height=400, showlegend=False, title=\"Visits by Device Attributes\")\niplot(fig)\n\n## convert transaction revenue to float\ntrain[\"totals.transactionRevenue\"] = train[\"totals.transactionRevenue\"].astype('float')\n\ndevice_cols = [\"device.browser\", \"device.deviceCategory\", \"device.operatingSystem\"]\n\nfig = tools.make_subplots(rows=1, cols=3, subplot_titles=[\"Mean Revenue: Category\", \"Mean Revenue: Browser\",\"Mean Revenue: OS\"], print_grid=False)\n\ncolors = [\"red\", \"green\", \"purple\"]\ntrs = []\nfor i, col in enumerate(device_cols):\n    tmp = train.groupby(col).agg({\"totals.transactionRevenue\": \"mean\"}).reset_index().rename(columns={\"totals.transactionRevenue\" : \"Mean Revenue\"})\n    tmp = tmp.dropna().sort_values(\"Mean Revenue\", ascending = False)\n    tr = go.Bar(x = tmp[\"Mean Revenue\"][::-1], orientation=\"h\", marker=dict(opacity=0.5, color=colors[i]), y = tmp[col][::-1])\n    trs.append(tr)\n\nfig.append_trace(trs[1], 1, 1)\nfig.append_trace(trs[0], 1, 2)\nfig.append_trace(trs[2], 1, 3)\nfig['layout'].update(height=400, showlegend=False, title=\"Mean Revenue by Device Attributes\")\niplot(fig)","metadata":{"_kg_hide-input":true,"_uuid":"707893d0be746d4536b6252a26de7f8f357ea54c","execution":{"iopub.status.busy":"2023-10-03T12:27:35.719954Z","iopub.execute_input":"2023-10-03T12:27:35.720301Z","iopub.status.idle":"2023-10-03T12:27:36.053654Z","shell.execute_reply.started":"2023-10-03T12:27:35.720243Z","shell.execute_reply":"2023-10-03T12:27:36.052024Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"> - There is a significant difference in visits from mobile and tablets, but mean revenue for both of them is very close.  \n> - Interesting to note that maximum visits are from Chrome browser however maximum revenue is collected from visits throught firefox. \n> - Chrome OS users has generated maximum revenue though maximum visits are from windows and macintosh users  \n\n### 3.2 GeoNetwork Attributes ","metadata":{"_uuid":"fe8fe3d1a2019687fb4533a4a7f59f559becd992"}},{"cell_type":"code","source":"geo_cols = ['geoNetwork.city', 'geoNetwork.continent','geoNetwork.country',\n            'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region','geoNetwork.subContinent']\ngeo_cols = ['geoNetwork.continent','geoNetwork.subContinent']\n\ncolors = [\"#d6a5ff\", \"#fca6da\"]\nfig = tools.make_subplots(rows=1, cols=2, subplot_titles=[\"Visits : GeoNetwork Continent\", \"Visits : GeoNetwork subContinent\"], print_grid=False)\ntrs = []\nfor i,col in enumerate(geo_cols):\n    t = train[col].value_counts()\n    tr = go.Bar(x = t.index[:20], marker=dict(color=colors[i]), y = t.values[:20])\n    trs.append(tr)\n\nfig.append_trace(trs[0], 1, 1)\nfig.append_trace(trs[1], 1, 2)\nfig['layout'].update(height=400, margin=dict(b=150), showlegend=False)\niplot(fig)\n\n\ngeo_cols = ['geoNetwork.continent','geoNetwork.subContinent']\nfig = tools.make_subplots(rows=1, cols=2, subplot_titles=[\"Mean Revenue: Continent\", \"Mean Revenue: SubContinent\"], print_grid=False)\n\ncolors = [\"blue\", \"orange\"]\ntrs = []\nfor i, col in enumerate(geo_cols):\n    tmp = train.groupby(col).agg({\"totals.transactionRevenue\": \"mean\"}).reset_index().rename(columns={\"totals.transactionRevenue\" : \"Mean Revenue\"})\n    tmp = tmp.dropna().sort_values(\"Mean Revenue\", ascending = False)\n    tr = go.Bar(y = tmp[\"Mean Revenue\"], orientation=\"v\", marker=dict(opacity=0.5, color=colors[i]), x= tmp[col])\n    trs.append(tr)\n\nfig.append_trace(trs[0], 1, 1)\nfig.append_trace(trs[1], 1, 2)\nfig['layout'].update(height=450, margin=dict(b=200), showlegend=False)\niplot(fig)","metadata":{"_kg_hide-input":true,"_uuid":"2fcf66951ad315244c75b7f5bbf68efcb2b2b23e","execution":{"iopub.status.busy":"2023-10-03T12:27:36.055570Z","iopub.execute_input":"2023-10-03T12:27:36.056223Z","iopub.status.idle":"2023-10-03T12:27:36.225007Z","shell.execute_reply.started":"2023-10-03T12:27:36.056172Z","shell.execute_reply":"2023-10-03T12:27:36.223767Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tmp1 = train[\"geoNetwork.country\"].value_counts()\n\n# plotly globe credits - https://www.kaggle.com/arthurtok/generation-unemployed-interactive-plotly-visuals\ncolorscale = [[0, 'rgb(102,194,165)'], [0.005, 'rgb(102,194,165)'], \n              [0.01, 'rgb(171,221,164)'], [0.02, 'rgb(230,245,152)'], \n              [0.04, 'rgb(255,255,191)'], [0.05, 'rgb(254,224,139)'], \n              [0.10, 'rgb(253,174,97)'], [0.25, 'rgb(213,62,79)'], [1.0, 'rgb(158,1,66)']]\n\ndata = [ dict(\n        type = 'choropleth',\n        autocolorscale = False,\n        colorscale = colorscale,\n        showscale = True,\n        locations = tmp1.index,\n        z = tmp1.values,\n        locationmode = 'country names',\n        text = tmp1.values,\n        marker = dict(\n            line = dict(color = '#fff', width = 2)) )           ]\n\nlayout = dict(\n    height=500,\n    title = 'Visits by Country',\n    geo = dict(\n        showframe = True,\n        showocean = True,\n        oceancolor = '#222',\n        projection = dict(\n        type = 'orthographic',\n            rotation = dict(\n                    lon = 60,\n                    lat = 10),\n        ),\n        lonaxis =  dict(\n                showgrid = False,\n                gridcolor = 'rgb(102, 102, 102)'\n            ),\n        lataxis = dict(\n                showgrid = False,\n                gridcolor = 'rgb(102, 102, 102)'\n                )\n            ),\n        )\nfig = dict(data=data, layout=layout)\niplot(fig)\n\n\n","metadata":{"_kg_hide-input":true,"_uuid":"25e72963720b5dc6db0dfe8390dffec59f0f3dec","scrolled":true,"execution":{"iopub.status.busy":"2023-10-03T12:27:36.226324Z","iopub.execute_input":"2023-10-03T12:27:36.226604Z","iopub.status.idle":"2023-10-03T12:27:36.324850Z","shell.execute_reply.started":"2023-10-03T12:27:36.226578Z","shell.execute_reply":"2023-10-03T12:27:36.323708Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tmp1.head()\n#tmp1.shape\n#tmp1.dtypes\nprint(tmp1.index)\ntmp1['Hong Kong']","metadata":{"execution":{"iopub.status.busy":"2023-10-03T12:27:36.326327Z","iopub.execute_input":"2023-10-03T12:27:36.326801Z","iopub.status.idle":"2023-10-03T12:27:36.334784Z","shell.execute_reply.started":"2023-10-03T12:27:36.326758Z","shell.execute_reply":"2023-10-03T12:27:36.333591Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tmp2 = train.groupby([\"geoNetwork.country\"]).mean()[\"totals.transactionRevenue\"] \ntmp2 = tmp2.fillna(0)\ntmp2.head()\nprint(tmp2.index)\ntmp2['Hong Kong']\n#tmp2.dtypes","metadata":{"execution":{"iopub.status.busy":"2023-10-03T12:27:36.336047Z","iopub.execute_input":"2023-10-03T12:27:36.336434Z","iopub.status.idle":"2023-10-03T12:27:36.409305Z","shell.execute_reply.started":"2023-10-03T12:27:36.336401Z","shell.execute_reply":"2023-10-03T12:27:36.408234Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# plotly globe credits - https://www.kaggle.com/arthurtok/generation-unemployed-interactive-plotly-visuals\ncolorscale = [[0, 'rgb(102,194,165)'], [0.005, 'rgb(102,194,165)'], \n              [0.01, 'rgb(171,221,164)'], [0.02, 'rgb(230,245,152)'], \n              [0.04, 'rgb(255,255,191)'], [0.05, 'rgb(254,224,139)'], \n              [0.10, 'rgb(253,174,97)'], [0.25, 'rgb(213,62,79)'], [1.0, 'rgb(158,1,66)']]\n\ndata = [ dict(\n        type = 'choropleth',\n        autocolorscale = False,\n        colorscale = colorscale,\n        showscale = True,\n        locations = tmp2.index,\n        z = tmp2,\n        locationmode = 'country names',\n        text = tmp2,\n        marker = dict(\n            line = dict(color = '#fff', width = 2)) )           ]\n\nlayout = dict(\n    height=500,\n    title = 'Visits by Country',\n    geo = dict(\n        showframe = True,\n        showocean = True,\n        oceancolor = '#222',\n        projection = dict(\n        type = 'orthographic',\n            rotation = dict(\n                    lon = 60,\n                    lat = 10),\n        ),\n        lonaxis =  dict(\n                showgrid = False,\n                gridcolor = 'rgb(102, 102, 102)'\n            ),\n        lataxis = dict(\n                showgrid = False,\n                gridcolor = 'rgb(102, 102, 102)'\n                )\n            ),\n        )\nfig = dict(data=data, layout=layout)\niplot(fig)","metadata":{"_kg_hide-input":true,"_uuid":"25e72963720b5dc6db0dfe8390dffec59f0f3dec","execution":{"iopub.status.busy":"2023-10-03T12:27:36.411186Z","iopub.execute_input":"2023-10-03T12:27:36.411593Z","iopub.status.idle":"2023-10-03T12:27:36.455683Z","shell.execute_reply.started":"2023-10-03T12:27:36.411550Z","shell.execute_reply":"2023-10-03T12:27:36.454893Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.3 Traffic Attributes\n\nLets now plot the traffic attributes","metadata":{"_uuid":"8f70cf1087d649b881072d60340424e6cbf2e218"}},{"cell_type":"code","source":"fig = tools.make_subplots(rows=1, cols=2, subplot_titles=[\"TrafficSource Campaign (not-set removed)\", \"TrafficSource Medium\"], print_grid=False)\n\ncolors = [\"#d6a5ff\", \"#fca6da\", \"#f4d39c\", \"#a9fcca\"]\nt1 = train[\"trafficSource.campaign\"].value_counts()\nt2 = train[\"trafficSource.medium\"].value_counts()\ntr1 = go.Bar(x = t1.index, y = t1.values, marker=dict(color=colors[3]))\ntr2 = go.Bar(x = t2.index, y = t2.values, marker=dict(color=colors[2]))\ntr3 = go.Bar(x = t1.index[1:], y = t1.values[1:], marker=dict(color=colors[0]))\ntr4 = go.Bar(x = t2.index[1:], y = t2.values[1:])\n\nfig.append_trace(tr3, 1, 1)\nfig.append_trace(tr2, 1, 2)\nfig['layout'].update(height=400, margin=dict(b=100), showlegend=False)\niplot(fig)","metadata":{"_kg_hide-input":true,"_uuid":"e7c28dc60b66ba44f74739f08adad051d9590e4e","execution":{"iopub.status.busy":"2023-10-03T12:27:36.457336Z","iopub.execute_input":"2023-10-03T12:27:36.457840Z","iopub.status.idle":"2023-10-03T12:27:36.541083Z","shell.execute_reply.started":"2023-10-03T12:27:36.457804Z","shell.execute_reply":"2023-10-03T12:27:36.539931Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.4 Channel Grouping","metadata":{"_uuid":"fa5abc7d9e091a3d86a494f48ebfd938ea7ba57e"}},{"cell_type":"code","source":"tmp = train[\"channelGrouping\"].value_counts()\ncolors = [\"#8d44fc\", \"#ed95d5\", \"#caadf7\", \"#6161b7\", \"#7e7eba\", \"#babad1\"]\ntrace = go.Pie(labels=tmp.index, values=tmp.values, marker=dict(colors=colors))\nlayout = go.Layout(title=\"Channel Grouping\", height=400)\nfig = go.Figure(data = [trace], layout = layout)\niplot(fig, filename='basic_pie_chart')","metadata":{"_kg_hide-input":true,"_uuid":"2ae83984b56f3e49857a5157ca05829d98bbbb0b","execution":{"iopub.status.busy":"2023-10-03T12:27:36.542556Z","iopub.execute_input":"2023-10-03T12:27:36.542941Z","iopub.status.idle":"2023-10-03T12:27:36.600840Z","shell.execute_reply.started":"2023-10-03T12:27:36.542901Z","shell.execute_reply":"2023-10-03T12:27:36.599674Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.5 Visits by date, month and day","metadata":{"_uuid":"36a0751e6fe11161b8a3f189306e5803ddb2f735"}},{"cell_type":"code","source":"def _add_date_features(df):\n    df['date'] = df['date'].astype(str)\n    df[\"date\"] = df[\"date\"].apply(lambda x : x[:4] + \"-\" + x[4:6] + \"-\" + x[6:])\n    df[\"date\"] = pd.to_datetime(df[\"date\"])\n    \n    df[\"month\"]   = df['date'].dt.month\n    df[\"day\"]     = df['date'].dt.day\n    df[\"weekday\"] = df['date'].dt.weekday\n    return df \n\ntrain = _add_date_features(train)","metadata":{"_kg_hide-input":true,"_uuid":"f764642db712ee0433b7adce08f7fe75cd5c879e","execution":{"iopub.status.busy":"2023-10-03T12:27:36.601987Z","iopub.execute_input":"2023-10-03T12:27:36.602343Z","iopub.status.idle":"2023-10-03T12:27:36.803817Z","shell.execute_reply.started":"2023-10-03T12:27:36.602301Z","shell.execute_reply":"2023-10-03T12:27:36.803026Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tmp = train['date'].value_counts().to_frame().reset_index().sort_values('index')\ntmp = tmp.rename(columns = {\"index\" : \"dateX\", \"date\" : \"visits\"})\n\ntr = go.Scatter(mode=\"lines\", x = tmp[\"dateX\"].astype(str), y = tmp[\"visits\"])\nlayout = go.Layout(title=\"Visits by date\", height=400)\nfig = go.Figure(data = [tr], layout = layout)\niplot(fig)\n\n\ntmp = train.groupby(\"date\").agg({\"totals.transactionRevenue\" : \"mean\"}).reset_index()\ntmp = tmp.rename(columns = {\"date\" : \"dateX\", \"totals.transactionRevenue\" : \"mean_revenue\"})\ntr = go.Scatter(mode=\"lines\", x = tmp[\"dateX\"].astype(str), y = tmp[\"mean_revenue\"])\nlayout = go.Layout(title=\"MonthlyRevenue by date\", height=400)\nfig = go.Figure(data = [tr], layout = layout)\niplot(fig)","metadata":{"_kg_hide-input":true,"_uuid":"f764642db712ee0433b7adce08f7fe75cd5c879e","execution":{"iopub.status.busy":"2023-10-03T12:27:36.805097Z","iopub.execute_input":"2023-10-03T12:27:36.805718Z","iopub.status.idle":"2023-10-03T12:27:36.887215Z","shell.execute_reply.started":"2023-10-03T12:27:36.805673Z","shell.execute_reply":"2023-10-03T12:27:36.886006Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = tools.make_subplots(rows=1, cols=3, subplot_titles=[\"Visits by Month\", \"Visits by MonthDay\", \"Visits by WeekDay\"], print_grid=False)\ntrs = []\nfor i,col in enumerate([\"month\", \"day\", \"weekday\"]):\n    t = train[col].value_counts()\n    tr = go.Bar(x = t.index, marker=dict(color=colors[i]), y = t.values)\n    trs.append(tr)\n\nfig.append_trace(trs[0], 1, 1)\nfig.append_trace(trs[1], 1, 2)\nfig.append_trace(trs[2], 1, 3)\nfig['layout'].update(height=400, showlegend=False)\niplot(fig)\n\n\n\ntmp1 = train.groupby('month').agg({\"totals.transactionRevenue\" : \"mean\"}).reset_index()\ntmp2 = train.groupby('day').agg({\"totals.transactionRevenue\" : \"mean\"}).reset_index()\ntmp3 = train.groupby('weekday').agg({\"totals.transactionRevenue\" : \"mean\"}).reset_index()\n\nfig = tools.make_subplots(rows=1, cols=3, subplot_titles=[\"MeanRevenue by Month\", \"MeanRevenue by MonthDay\", \"MeanRevenue by WeekDay\"], print_grid=False)\ntr1 = go.Bar(x = tmp1.month, marker=dict(color=\"red\", opacity=0.5), y = tmp1['totals.transactionRevenue'])\ntr2 = go.Bar(x = tmp2.day, marker=dict(color=\"orange\", opacity=0.5), y = tmp2['totals.transactionRevenue'])\ntr3 = go.Bar(x = tmp3.weekday, marker=dict(color=\"green\", opacity=0.5), y = tmp3['totals.transactionRevenue'])\n\nfig.append_trace(tr1, 1, 1)\nfig.append_trace(tr2, 1, 2)\nfig.append_trace(tr3, 1, 3)\nfig['layout'].update(height=400, showlegend=False)\niplot(fig)","metadata":{"_kg_hide-input":true,"_uuid":"e43270a70ea838268a8005392bb86e7b13e2a0d1","execution":{"iopub.status.busy":"2023-10-03T12:27:36.890476Z","iopub.execute_input":"2023-10-03T12:27:36.890819Z","iopub.status.idle":"2023-10-03T12:27:37.045745Z","shell.execute_reply.started":"2023-10-03T12:27:36.890779Z","shell.execute_reply":"2023-10-03T12:27:37.044737Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.6 Visit Number Frequency","metadata":{"_uuid":"e9284994f822fde33ae679f78310604a80635fd6"}},{"cell_type":"code","source":"vn = train[\"visitNumber\"].value_counts()\ndef vn_bins(x):\n    if x == 1:\n        return \"1\" \n    elif x < 5:\n        return \"2-5\"\n    elif x < 10:\n        return \"5-10\"\n    elif x < 50:\n        return \"10-50\"\n    elif x < 100:\n        return \"50-100\"\n    else:\n        return \"100+\"\n    \nvn = train[\"visitNumber\"].apply(vn_bins).value_counts()\n\ntrace1 = go.Bar(y = vn.index[::-1], orientation=\"h\" , x = vn.values[::-1], marker=dict(color=\"#7af9ad\"))\nlayout = go.Layout(title=\"Visit Numbers Distribution\", \n                   xaxis=dict(title=\"Frequency\"),yaxis=dict(title=\"VisitNumber\") ,\n                   height=400, margin=dict(l=300, r=300))\nfigure = go.Figure(data = [trace1], layout = layout)\niplot(figure)","metadata":{"_kg_hide-input":true,"_uuid":"c1889e56a301f467e68a979d4b189ec7e10e3da1","execution":{"iopub.status.busy":"2023-10-03T12:27:37.047486Z","iopub.execute_input":"2023-10-03T12:27:37.047787Z","iopub.status.idle":"2023-10-03T12:27:37.113937Z","shell.execute_reply.started":"2023-10-03T12:27:37.047756Z","shell.execute_reply":"2023-10-03T12:27:37.112937Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 4. Visitor Profile \n\nLets create the visitor profile by aggregating the rows for every customer. \n\n### 4.1 Visitor Profile Snapshot","metadata":{"_uuid":"02b61a16d6323e34bf8ca1bed937cfc97f9ae872"}},{"cell_type":"code","source":"import warnings\nwarnings.simplefilter(action='ignore', category=FutureWarning)\n\nagg_dict = {}\nfor col in [\"totals.bounces\", \"totals.hits\", \"totals.newVisits\", \"totals.pageviews\", \"totals.transactionRevenue\"]:\n    train[col] = train[col].astype('float')\n    agg_dict[col] = \"sum\"\ntmp = train.groupby(\"fullVisitorId\").agg(agg_dict).reset_index()\ntmp.head()","metadata":{"_kg_hide-input":true,"_uuid":"46841c2028ea593e5d77538e7d9b6648cd1b5472","execution":{"iopub.status.busy":"2023-10-03T12:27:37.115366Z","iopub.execute_input":"2023-10-03T12:27:37.115755Z","iopub.status.idle":"2023-10-03T12:27:37.373492Z","shell.execute_reply.started":"2023-10-03T12:27:37.115714Z","shell.execute_reply":"2023-10-03T12:27:37.372067Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 4.2 Total Transactions Revenue","metadata":{"_uuid":"62a62c5484e808b19c79907c9f1590e95effc1f7"}},{"cell_type":"code","source":"non_zero = tmp[tmp[\"totals.transactionRevenue\"] > 0][\"totals.transactionRevenue\"]\nprint (\"There are \" + str(len(non_zero)) + \" visitors in the train dataset having non zero total transaction revenue\")\n\nplt.figure(figsize=(12,6))\nsns.distplot(non_zero)\nplt.title(\"Distribution of Non Zero Total Transactions\");\nplt.xlabel(\"Total Transactions\");","metadata":{"_kg_hide-input":true,"_uuid":"c60b224592c69e15e4e286d83cec1de595f317ab","execution":{"iopub.status.busy":"2023-10-03T12:27:37.375217Z","iopub.execute_input":"2023-10-03T12:27:37.375636Z","iopub.status.idle":"2023-10-03T12:27:37.681561Z","shell.execute_reply.started":"2023-10-03T12:27:37.375593Z","shell.execute_reply":"2023-10-03T12:27:37.680524Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Lets take the natural log on the transactions","metadata":{"_uuid":"e54cfc3f5751bb7e26bf46bac570e471809789c9"}},{"cell_type":"code","source":"plt.figure(figsize=(12,6))\nsns.distplot(np.log1p(non_zero))\nplt.title(\"Log Distribution of Non Zero Total Transactions\");\nplt.xlabel(\"Log - Total Transactions\");","metadata":{"_kg_hide-input":true,"_uuid":"e0e4b4c6a17803195d4c9f1ebedfddfb670a20a4","execution":{"iopub.status.busy":"2023-10-03T12:27:37.683385Z","iopub.execute_input":"2023-10-03T12:27:37.684207Z","iopub.status.idle":"2023-10-03T12:27:37.916789Z","shell.execute_reply.started":"2023-10-03T12:27:37.684156Z","shell.execute_reply":"2023-10-03T12:27:37.915996Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 4.3 Visitor Profile Attributes","metadata":{"_uuid":"30f66ab7332348bf1a6837dd272caf5090be0d8f"}},{"cell_type":"code","source":"def getbin_hits(x):\n    if x < 5:\n        return \"1-5\"\n    elif x < 10:\n        return \"5-10\"\n    elif x < 30:\n        return \"10-30\"\n    elif x < 50:\n        return \"30-50\"\n    elif x < 100:\n        return \"50-100\"\n    else:\n        return \"100+\"\n\ntmp[\"total.hits_bin\"] = tmp[\"totals.hits\"].apply(getbin_hits)\ntmp[\"totals.bounces_bin\"] = tmp[\"totals.bounces\"].apply(lambda x : str(x) if x <= 5 else \"5+\")\ntmp[\"totals.pageviews_bin\"] = tmp[\"totals.pageviews\"].apply(lambda x : str(x) if x <= 50 else \"50+\")\n\nt1 = tmp[\"total.hits_bin\"].value_counts()\nt2 = tmp[\"totals.bounces_bin\"].value_counts()\nt3 = tmp[\"totals.newVisits\"].value_counts()\nt4 = tmp[\"totals.pageviews_bin\"].value_counts()\n\nfig = tools.make_subplots(rows=2, cols=2, subplot_titles=[\"Total Hits per User\", \"Total Bounces per User\", \n                                                         \"Total NewVistits per User\", \"Total PageViews per User\"], print_grid=False)\n\ntr1 = go.Bar(x = t1.index[:20], y = t1.values[:20])\ntr2 = go.Bar(x = t2.index[:20], y = t2.values[:20])\ntr3 = go.Bar(x = t3.index[:20], y = t3.values[:20])\ntr4 = go.Bar(x = t4.index, y = t4.values)\n\nfig.append_trace(tr1, 1, 1)\nfig.append_trace(tr2, 1, 2)\nfig.append_trace(tr3, 2, 1)\nfig.append_trace(tr4, 2, 2)\n\nfig['layout'].update(height=700, showlegend=False)\niplot(fig)","metadata":{"_kg_hide-input":true,"_uuid":"15d9c375b9672344d4b320ed6cd918b3644c3cb0","execution":{"iopub.status.busy":"2023-10-03T12:27:37.918100Z","iopub.execute_input":"2023-10-03T12:27:37.918680Z","iopub.status.idle":"2023-10-03T12:27:38.139689Z","shell.execute_reply.started":"2023-10-03T12:27:37.918634Z","shell.execute_reply":"2023-10-03T12:27:38.138669Z"},"trusted":true},"execution_count":null,"outputs":[]}]}