{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.12","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"**1 Project Overview**\nIn this project, we will explore a dataset containing sales data to predict revenue per customer using a neural network model. The data is sourced from Google Merchandise Store.","metadata":{}},{"cell_type":"markdown","source":"**2 Import Libraries**\nImporting these libraries serves several essential purposes in data analysis and modeling:","metadata":{}},{"cell_type":"code","source":"import os\nimport json\nimport numpy as np\nimport pandas as pd\nfrom pandas import json_normalize\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n%matplotlib inline\nfrom plotly import tools\nimport plotly.offline as py\npy.init_notebook_mode(connected=True)\nimport plotly.graph_objs as go\n\n#import tensorflow as tf\nfrom tensorflow import keras\nfrom tensorflow.keras import layers\nfrom sklearn import model_selection, preprocessing, metrics\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.preprocessing import StandardScaler\n\npd.options.mode.chained_assignment = None\npd.options.display.max_columns = 999","metadata":{"execution":{"iopub.status.busy":"2023-09-13T21:01:54.373993Z","iopub.execute_input":"2023-09-13T21:01:54.374473Z","iopub.status.idle":"2023-09-13T21:02:07.289868Z","shell.execute_reply.started":"2023-09-13T21:01:54.374431Z","shell.execute_reply":"2023-09-13T21:02:07.288411Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n**3 Data Preparation**\nFirst, we will load the data and perform an initial exploration to understand its structure and content.\n\n**Dataset Overview:**\n\nThis dataset comprises two primary files:\n\n1. **train.csv**\n2. **test.csv**\n\nEach row within these datasets corresponds to a single visit made to the store. Our primary objective is to predict the natural logarithm of the total transaction value for each user.\n\n**Key Data Fields:**\n\n1. **fullVisitorId:** A distinct identifier for every Google Merchandise Store user.\n2. **channelGrouping:** Indicates the medium through which the user accessed the store.\n3. **date:** Specifies the date of the user's store visit.\n4. **device:** Contains specifications about the device used by the visitor to access the store.\n5. **geoNetwork:** Offers geographical information about the user.\n6. **sessionId:** Provides a unique identifier for each store visit.\n7. **socialEngagementType:** Denotes the type of user engagement, categorized as either \"Socially Engaged\" or \"Not Socially Engaged.\"\n8. **totals:** Contains aggregated values stemming from each session.\n9. **trafficSource:** Presents information regarding the session's traffic source.\n10. **visitId:** Serves as an identifier for each session. It is typically part of the value stored in the _utmb cookie. This identifier is unique to each user. For an entirely unique ID, a combination of fullVisitorId and visitId is recommended.\n11. **visitNumber:** Signifies the session number for each user, with a value of 1 indicating the user's initial session.\n12. **visitStartTime:** Records the timestamp, represented in POSIX time, marking the commencement of the session.\n\n\n","metadata":{}},{"cell_type":"markdown","source":"**4 Load the CSV dataset** It's important to note that certain fields are structured in JSON format, and we need to convert all the json fields in the file to a flattened csv format. It achieves by parse each JSON column into a separate DataFrame (column_as_df), rename its columns to include the original column name as a prefix, drop the original JSON column from the DataFrame, and merge the parsed DataFrame back into the original DataFrame (df), resulting in a DataFrame ready for analysis and modeling.","metadata":{}},{"cell_type":"code","source":"def load_df(csv_path='/kaggle/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'},\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\ntrain_df = load_df()\ntest_df = load_df(\"/kaggle/input/ga-customer-revenue-prediction/train.csv\")\n\ntrain_df.head()\n","metadata":{"execution":{"iopub.status.busy":"2023-09-11T20:35:04.845840Z","iopub.execute_input":"2023-09-11T20:35:04.846663Z","iopub.status.idle":"2023-09-11T20:38:39.657192Z","shell.execute_reply.started":"2023-09-11T20:35:04.846626Z","shell.execute_reply":"2023-09-11T20:38:39.655920Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**5 Data Investigation** First we can see there's several features in the training set contain null values. Let's compile a list of these features, excluding columns with missing values, as well as those columns have constant values (same values, usually won't give much imformation) throughout the dataset. \nAlso, for sessionId (A unique identifier for this visit to the store) can be removed since it doesn't give any information.\n\n","metadata":{}},{"cell_type":"code","source":"null = []\nfor n in train_df.columns:\n    if train_df[n].nunique(dropna=False) == 1:\n        null.append(n)\n\nnull","metadata":{"execution":{"iopub.status.busy":"2023-09-11T20:38:39.659238Z","iopub.execute_input":"2023-09-11T20:38:39.659862Z","iopub.status.idle":"2023-09-11T20:38:49.341270Z","shell.execute_reply.started":"2023-09-11T20:38:39.659830Z","shell.execute_reply":"2023-09-11T20:38:49.340138Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"columns_to_drop = null + ['sessionId']\ntrain_df.drop(columns=columns_to_drop, inplace=True)\ntrain_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-09-11T20:38:49.342784Z","iopub.execute_input":"2023-09-11T20:38:49.343248Z","iopub.status.idle":"2023-09-11T20:38:49.917430Z","shell.execute_reply.started":"2023-09-11T20:38:49.343213Z","shell.execute_reply":"2023-09-11T20:38:49.916270Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Then for the rest of the data:** ","metadata":{}},{"cell_type":"code","source":"import itertools\n\ncolumns = sorted(train_df.columns.tolist())\ngrouped_columns = [list(group) for key, group in itertools.groupby(columns, lambda x: x[0])]\n\nfor group in grouped_columns:\n    print(\", \".join(group))\n","metadata":{"execution":{"iopub.status.busy":"2023-09-11T20:38:49.919926Z","iopub.execute_input":"2023-09-11T20:38:49.920296Z","iopub.status.idle":"2023-09-11T20:38:49.926657Z","shell.execute_reply.started":"2023-09-11T20:38:49.920264Z","shell.execute_reply":"2023-09-11T20:38:49.925573Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**1）.We'll prove the 80/20 rule: for many businesses–only a small percentage of customers produce most of the revenue.**","metadata":{}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\n\ngdf = train_df.groupby(\"fullVisitorId\")[\"totals.transactionRevenue\"].sum().reset_index()\n\ngdf[\"totals.transactionRevenue\"] = pd.to_numeric(gdf[\"totals.transactionRevenue\"], errors=\"coerce\")\n\ngdf = gdf.dropna(subset=[\"totals.transactionRevenue\"])\n\ngdf[\"totals.transactionRevenue\"] = np.sort(gdf[\"totals.transactionRevenue\"])\n\ntotal_customers = gdf.shape[0]\nrevenue_customers = gdf[gdf[\"totals.transactionRevenue\"] > 0].shape[0]\npercentage_revenue_customers = (revenue_customers / total_customers) * 100\n\nprint(\"Percentage of customers producing revenue: {:.2f}%\".format(percentage_revenue_customers))\n","metadata":{"execution":{"iopub.status.busy":"2023-09-11T20:38:49.928151Z","iopub.execute_input":"2023-09-11T20:38:49.928851Z","iopub.status.idle":"2023-09-11T20:38:53.051675Z","shell.execute_reply.started":"2023-09-11T20:38:49.928820Z","shell.execute_reply":"2023-09-11T20:38:53.050509Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**2）.We will exam the following for their potential impact on revenue:**\n\n**(1)device-related data**(device.browser, device.deviceCategory, device.isMobile, device.operatingSystem)\n\n**(2)geoNetwork-related data** (geoNetwork.city, geoNetwork.continent, geoNetwork.country, geoNetwork.metro, geoNetwork.networkDomain, geoNetwork.region, geoNetwork.subContinent)\n\n**(3)traffic source-related data**(trafficSource.adContent, trafficSource.adwordsClickInfo.adNetworkType, trafficSource.adwordsClickInfo.gclId, trafficSource.adwordsClickInfo.isVideoAd, trafficSource.adwordsClickInfo.page, trafficSource.adwordsClickInfo.slot, trafficSource.campaign, trafficSource.campaignCode, trafficSource.isTrueDirect, trafficSource.keyword, trafficSource.medium, trafficSource.referralPath, trafficSource.source)\n\n**(4)visitor engagement metrics**(totals.bounces, totals.hits, totals.newVisits, totals.pageviews, totals.transactionRevenue） \n","metadata":{}},{"cell_type":"code","source":"import plotly.graph_objs as go\nfrom plotly.subplots import make_subplots\nimport plotly.offline as py\n\ntrain_df['totals.transactionRevenue'] = pd.to_numeric(train_df['totals.transactionRevenue'], errors='coerce')\n\ndef horizontal_bar_chart(cnt_srs, color):\n    trace = go.Bar(\n        y=cnt_srs.index[::-1],\n        x=cnt_srs.values[::-1],\n        showlegend=False,\n        orientation='h',\n        marker=dict(\n            color=color,\n        ),\n    )\n    return trace\n\n# Device Browser\ncnt_srs_browser = train_df.groupby('device.browser')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_browser.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_browser = cnt_srs_browser.sort_values(by=\"count\", ascending=False)\ntrace1 = horizontal_bar_chart(cnt_srs_browser[\"count\"].head(10), 'rgba(50, 171, 96, 0.6)')\ntrace2 = horizontal_bar_chart(cnt_srs_browser[\"count of non-zero revenue\"].head(10) / cnt_srs_browser[\"count\"].head(10), 'rgba(50, 171, 96, 0.6)')\n\n# Device Category\ncnt_srs_category = train_df.groupby('device.deviceCategory')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_category.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_category = cnt_srs_category.sort_values(by=\"count\", ascending=False)\ntrace3 = horizontal_bar_chart(cnt_srs_category[\"count\"].head(10), 'rgba(71, 58, 131, 0.8)')\ntrace4 = horizontal_bar_chart(cnt_srs_category[\"count of non-zero revenue\"].head(10) / cnt_srs_category[\"count\"].head(10), 'rgba(71, 58, 131, 0.8)')\n\n# Device isMobile\ncnt_srs_is_mobile = train_df.groupby('device.isMobile')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_is_mobile.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_is_mobile = cnt_srs_is_mobile.sort_values(by=\"count\", ascending=False)\ntrace5 = horizontal_bar_chart(cnt_srs_is_mobile[\"count\"].head(10), 'rgba(255, 123, 22, 0.6)')\ntrace6 = horizontal_bar_chart(cnt_srs_is_mobile[\"count of non-zero revenue\"].head(10) / cnt_srs_is_mobile[\"count\"].head(10), 'rgba(255, 123, 22, 0.6)')\n\n# Operating system\ncnt_srs_os = train_df.groupby('device.operatingSystem')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_os.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_os = cnt_srs_os.sort_values(by=\"count\", ascending=False)\ntrace7 = horizontal_bar_chart(cnt_srs_os[\"count\"].head(10), 'rgba(246, 78, 139, 0.6)')\ntrace8 = horizontal_bar_chart(cnt_srs_os[\"count of non-zero revenue\"].head(10) / cnt_srs_os[\"count\"].head(10), 'rgba(246, 78, 139, 0.6)')\n\n# Creating a subplot with 4 rows and 2 columns\nfig = make_subplots(rows=4, cols=2, vertical_spacing=0.04, \n                    subplot_titles=[\"Device Browser - Count\", \"Device Browser - Non-zero Revenue Count Ratio\",\n                                    \"Device Category - Count\",  \"Device Category - Non-zero Revenue Count Ratio\",\n                                    \"Device isMobile - Count\", \"Device isMobile - Non-zero Revenue Count Ratio\",\n                                    \"Device OS - Count\", \"Device OS - Non-zero Revenue Count Ratio\"])\n\n# Adding traces to the subplot\nfig.add_trace(trace1, row=1, col=1)\nfig.add_trace(trace2, row=1, col=2)\nfig.add_trace(trace3, row=2, col=1)\nfig.add_trace(trace4, row=2, col=2)\nfig.add_trace(trace5, row=3, col=1)\nfig.add_trace(trace6, row=3, col=2)\nfig.add_trace(trace7, row=4, col=1)\nfig.add_trace(trace8, row=4, col=2)\n\n# Updating layout\nfig.update_layout(height=1000, width=1000, paper_bgcolor='rgb(233,233,233)', title=\"Device Plots\")\n\n# Displaying the plot\npy.iplot(fig, filename='device-plots')\n","metadata":{"execution":{"iopub.status.busy":"2023-09-11T20:38:53.053767Z","iopub.execute_input":"2023-09-11T20:38:53.054493Z","iopub.status.idle":"2023-09-11T20:38:54.108024Z","shell.execute_reply.started":"2023-09-11T20:38:53.054450Z","shell.execute_reply":"2023-09-11T20:38:54.107143Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"from device-related data we see:\n\n1.Device browser: Chrome has higher percentage of non-zero revenue counts.\n\n2.Device category: desktop has higher percentage of non-zero revenue counts.\n\n3.No-mobile has higher percentage of non-zero revenue counts.\n\n4.Chrome OS has higher percentage of non-zero revenue counts.\n","metadata":{}},{"cell_type":"code","source":"import plotly.graph_objs as go\nfrom plotly.subplots import make_subplots\nimport plotly.offline as py\n\ndef horizontal_bar_chart(cnt_srs, color):\n    trace = go.Bar(\n        y=cnt_srs.index[::-1],\n        x=cnt_srs.values[::-1],\n        showlegend=False,\n        orientation='h',\n        marker=dict(\n            color=color,\n        ),\n    )\n    return trace\n\n# GeoNetwork City\ncnt_srs_city = train_df.groupby('geoNetwork.city')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_city.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_city = cnt_srs_city.sort_values(by=\"count\", ascending=False)\ntrace1 = horizontal_bar_chart(cnt_srs_city[\"count\"].head(10), 'rgba(50, 171, 96, 0.6)')\ntrace2 = horizontal_bar_chart(cnt_srs_city[\"count of non-zero revenue\"].head(10) / cnt_srs_city[\"count\"].head(10), 'rgba(50, 171, 96, 0.6)')\n\n# GeoNetwork Continent\ncnt_srs_continent = train_df.groupby('geoNetwork.continent')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_continent.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_continent = cnt_srs_continent.sort_values(by=\"count\", ascending=False)\ntrace3 = horizontal_bar_chart(cnt_srs_continent[\"count\"].head(10), 'rgba(71, 58, 131, 0.8)')\ntrace4 = horizontal_bar_chart(cnt_srs_continent[\"count of non-zero revenue\"].head(10) / cnt_srs_continent[\"count\"].head(10), 'rgba(71, 58, 131, 0.8)')\n\n# GeoNetwork Country\ncnt_srs_country = train_df.groupby('geoNetwork.country')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_country.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_country = cnt_srs_country.sort_values(by=\"count\", ascending=False)\ntrace5 = horizontal_bar_chart(cnt_srs_country[\"count\"].head(10), 'rgba(255, 123, 22, 0.6)')\ntrace6 = horizontal_bar_chart(cnt_srs_country[\"count of non-zero revenue\"].head(10) / cnt_srs_country[\"count\"].head(10), 'rgba(255, 123, 22, 0.6)')\n\n# GeoNetwork Metro\ncnt_srs_metro = train_df.groupby('geoNetwork.metro')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_metro.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_metro = cnt_srs_metro.sort_values(by=\"count\", ascending=False)\ntrace7 = horizontal_bar_chart(cnt_srs_metro[\"count\"].head(10), 'rgba(246, 78, 139, 0.6)')\ntrace8 = horizontal_bar_chart(cnt_srs_metro[\"count of non-zero revenue\"].head(10) / cnt_srs_metro[\"count\"].head(10), 'rgba(246, 78, 139, 0.6)')\n\n# GeoNetwork Network Domain\ncnt_srs_network_domain = train_df.groupby('geoNetwork.networkDomain')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_network_domain.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_network_domain = cnt_srs_network_domain.sort_values(by=\"count\", ascending=False)\ntrace9 = horizontal_bar_chart(cnt_srs_network_domain[\"count\"].head(10), 'rgba(128, 0, 0, 0.6)')\ntrace10 = horizontal_bar_chart(cnt_srs_network_domain[\"count of non-zero revenue\"].head(10) / cnt_srs_network_domain[\"count\"].head(10), 'rgba(128, 0, 0, 0.6)')\n\n# GeoNetwork Region\ncnt_srs_region = train_df.groupby('geoNetwork.region')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_region.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_region = cnt_srs_region.sort_values(by=\"count\", ascending=False)\ntrace11 = horizontal_bar_chart(cnt_srs_region[\"count\"].head(10), 'rgba(0, 0, 128, 0.6)')\ntrace12 = horizontal_bar_chart(cnt_srs_region[\"count of non-zero revenue\"].head(10) / cnt_srs_region[\"count\"].head(10), 'rgba(0, 0, 128, 0.6)')\n\n# GeoNetwork SubContinent\ncnt_srs_subcontinent = train_df.groupby('geoNetwork.subContinent')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_subcontinent.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_subcontinent = cnt_srs_subcontinent.sort_values(by=\"count\", ascending=False)\ntrace13 = horizontal_bar_chart(cnt_srs_subcontinent[\"count\"].head(10), 'rgba(0, 128, 0, 0.6)')\ntrace14 = horizontal_bar_chart(cnt_srs_subcontinent[\"count of non-zero revenue\"].head(10) / cnt_srs_subcontinent[\"count\"].head(10), 'rgba(0, 128, 0, 0.6)')\n\n# Creating a subplot with 7 rows and 2 columns\nfig = make_subplots(rows=7, cols=2, vertical_spacing=0.04, \n                    subplot_titles=[\"GeoNetwork City - Count\", \"GeoNetwork City - Non-zero Revenue Count Ratio\",\n                                    \"GeoNetwork Continent - Count\",  \"GeoNetwork Continent - Non-zero Revenue Count Ratio\",\n                                    \"GeoNetwork Country - Count\", \"GeoNetwork Country - Non-zero Revenue Count Ratio\",\n                                    \"GeoNetwork Metro - Count\", \"GeoNetwork Metro - Non-zero Revenue Count Ratio\",\n                                    \"GeoNetwork Network Domain - Count\", \"GeoNetwork Network Domain - Non-zero Revenue Count Ratio\",\n                                    \"GeoNetwork Region - Count\", \"GeoNetwork Region - Non-zero Revenue Count Ratio\",\n                                    \"GeoNetwork SubContinent - Count\", \"GeoNetwork SubContinent - Non-zero Revenue Count Ratio\"])\n\n# Adding traces to the subplot\nfig.add_trace(trace1, row=1, col=1)\nfig.add_trace(trace2, row=1, col=2)\nfig.add_trace(trace3, row=2, col=1)\nfig.add_trace(trace4, row=2, col=2)\nfig.add_trace(trace5, row=3, col=1)\nfig.add_trace(trace6, row=3, col=2)\nfig.add_trace(trace7, row=4, col=1)\nfig.add_trace(trace8, row=4, col=2)\nfig.add_trace(trace9, row=5, col=1)\nfig.add_trace(trace10, row=5, col=2)\nfig.add_trace(trace11, row=6, col=1)\nfig.add_trace(trace12, row=6, col=2)\nfig.add_trace(trace13, row=7, col=1)\nfig.add_trace(trace14, row=7, col=2)\n\n# Updating layout\nfig.update_layout(height=1800, width=1000, paper_bgcolor='rgb(233,233,233)', title=\"GeoNetwork Plots\")\n\n# Displaying the plot\npy.iplot(fig, filename='geonetwork-plots')\n","metadata":{"execution":{"iopub.status.busy":"2023-09-11T20:38:54.109575Z","iopub.execute_input":"2023-09-11T20:38:54.110492Z","iopub.status.idle":"2023-09-11T20:38:56.460195Z","shell.execute_reply.started":"2023-09-11T20:38:54.110457Z","shell.execute_reply":"2023-09-11T20:38:56.459023Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"from geoNetwork-related data we see:\n\n1.City: New York has higher percentage of non-zero revenue counts.\n\n2.Continent: we can see that America has both higher number of counts as well as highest percentage of non-zero revenue counts. Though Asia and Europe has high number of counts, the percentage of non-zero revenue counts from these continents are comparatively low.\n\n3.If the network domain is \"unknown.unknown\" rather than \"(not set)\", then the number of counts with non-zero revenue tend to be lower.\n\n\n\n","metadata":{}},{"cell_type":"code","source":"import plotly.graph_objs as go\nfrom plotly.subplots import make_subplots\nimport plotly.offline as py\n\ndef horizontal_bar_chart(cnt_srs, color):\n    trace = go.Bar(\n        y=cnt_srs.index[::-1],\n        x=cnt_srs.values[::-1],\n        showlegend=False,\n        orientation='h',\n        marker=dict(\n            color=color,\n        ),\n    )\n    return trace\n\n# TrafficSource adContent\ncnt_srs_adcontent = train_df.groupby('trafficSource.adContent')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_adcontent.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_adcontent = cnt_srs_adcontent.sort_values(by=\"count\", ascending=False)\ntrace1 = horizontal_bar_chart(cnt_srs_adcontent[\"count\"].head(10), 'rgba(50, 171, 96, 0.6)')\ntrace2 = horizontal_bar_chart(cnt_srs_adcontent[\"count of non-zero revenue\"].head(10) / cnt_srs_adcontent[\"count\"].head(10), 'rgba(50, 171, 96, 0.6)')\n\n# TrafficSource adwordsClickInfo adNetworkType\ncnt_srs_adnetworktype = train_df.groupby('trafficSource.adwordsClickInfo.adNetworkType')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_adnetworktype.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_adnetworktype = cnt_srs_adnetworktype.sort_values(by=\"count\", ascending=False)\ntrace3 = horizontal_bar_chart(cnt_srs_adnetworktype[\"count\"].head(10), 'rgba(71, 58, 131, 0.8)')\ntrace4 = horizontal_bar_chart(cnt_srs_adnetworktype[\"count of non-zero revenue\"].head(10) / cnt_srs_adnetworktype[\"count\"].head(10), 'rgba(71, 58, 131, 0.8)')\n\n# TrafficSource adwordsClickInfo gclId\ncnt_srs_gclid = train_df.groupby('trafficSource.adwordsClickInfo.gclId')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_gclid.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_gclid = cnt_srs_gclid.sort_values(by=\"count\", ascending=False)\ntrace5 = horizontal_bar_chart(cnt_srs_gclid[\"count\"].head(10), 'rgba(255, 123, 22, 0.6)')\ntrace6 = horizontal_bar_chart(cnt_srs_gclid[\"count of non-zero revenue\"].head(10) / cnt_srs_gclid[\"count\"].head(10), 'rgba(255, 123, 22, 0.6)')\n\n# TrafficSource adwordsClickInfo isVideoAd\ncnt_srs_isvideoad = train_df.groupby('trafficSource.adwordsClickInfo.isVideoAd')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_isvideoad.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_isvideoad = cnt_srs_isvideoad.sort_values(by=\"count\", ascending=False)\ntrace7 = horizontal_bar_chart(cnt_srs_isvideoad[\"count\"].head(10), 'rgba(246, 78, 139, 0.6)')\ntrace8 = horizontal_bar_chart(cnt_srs_isvideoad[\"count of non-zero revenue\"].head(10) / cnt_srs_isvideoad[\"count\"].head(10), 'rgba(246, 78, 139, 0.6)')\n\n# TrafficSource adwordsClickInfo page\ncnt_srs_adpage = train_df.groupby('trafficSource.adwordsClickInfo.page')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_adpage.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_adpage = cnt_srs_adpage.sort_values(by=\"count\", ascending=False)\ntrace9 = horizontal_bar_chart(cnt_srs_adpage[\"count\"].head(10), 'rgba(128, 0, 0, 0.6)')\ntrace10 = horizontal_bar_chart(cnt_srs_adpage[\"count of non-zero revenue\"].head(10) / cnt_srs_adpage[\"count\"].head(10), 'rgba(128, 0, 0, 0.6)')\n\n# TrafficSource adwordsClickInfo slot\ncnt_srs_adslot = train_df.groupby('trafficSource.adwordsClickInfo.slot')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_adslot.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_adslot = cnt_srs_adslot.sort_values(by=\"count\", ascending=False)\ntrace11 = horizontal_bar_chart(cnt_srs_adslot[\"count\"].head(10), 'rgba(0, 128, 128, 0.6)')\ntrace12 = horizontal_bar_chart(cnt_srs_adslot[\"count of non-zero revenue\"].head(10) / cnt_srs_adslot[\"count\"].head(10), 'rgba(0, 128, 128, 0.6)')\n\n# TrafficSource campaign\ncnt_srs_campaign = train_df.groupby('trafficSource.campaign')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_campaign.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_campaign = cnt_srs_campaign.sort_values(by=\"count\", ascending=False)\ntrace13 = horizontal_bar_chart(cnt_srs_campaign[\"count\"].head(10), 'rgba(0, 0, 128, 0.6)')\ntrace14 = horizontal_bar_chart(cnt_srs_campaign[\"count of non-zero revenue\"].head(10) / cnt_srs_campaign[\"count\"].head(10), 'rgba(0, 0, 128, 0.6)')\n\n# TrafficSource campaignCode\ncnt_srs_campaigncode = train_df.groupby('trafficSource.campaignCode')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_campaigncode.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_campaigncode = cnt_srs_campaigncode.sort_values(by=\"count\", ascending=False)\ntrace15 = horizontal_bar_chart(cnt_srs_campaigncode[\"count\"].head(10), 'rgba(128, 0, 128, 0.6)')\ntrace16 = horizontal_bar_chart(cnt_srs_campaigncode[\"count of non-zero revenue\"].head(10) / cnt_srs_campaigncode[\"count\"].head(10), 'rgba(128, 0, 128, 0.6)')\n\n# TrafficSource isTrueDirect\ncnt_srs_istrue = train_df.groupby('trafficSource.isTrueDirect')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_istrue.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_istrue = cnt_srs_istrue.sort_values(by=\"count\", ascending=False)\ntrace17 = horizontal_bar_chart(cnt_srs_istrue[\"count\"].head(10), 'rgba(0, 128, 0, 0.6)')\ntrace18 = horizontal_bar_chart(cnt_srs_istrue[\"count of non-zero revenue\"].head(10) / cnt_srs_istrue[\"count\"].head(10), 'rgba(0, 128, 0, 0.6)')\n\n# TrafficSource keyword\ncnt_srs_keyword = train_df.groupby('trafficSource.keyword')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_keyword.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_keyword = cnt_srs_keyword.sort_values(by=\"count\", ascending=False)\ntrace19 = horizontal_bar_chart(cnt_srs_keyword[\"count\"].head(10), 'rgba(0, 0, 0, 0.6)')\ntrace20 = horizontal_bar_chart(cnt_srs_keyword[\"count of non-zero revenue\"].head(10) / cnt_srs_keyword[\"count\"].head(10), 'rgba(0, 0, 0, 0.6)')\n\n# TrafficSource medium\ncnt_srs_medium = train_df.groupby('trafficSource.medium')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_medium.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_medium = cnt_srs_medium.sort_values(by=\"count\", ascending=False)\ntrace21 = horizontal_bar_chart(cnt_srs_medium[\"count\"].head(10), 'rgba(128, 128, 0, 0.6)')\ntrace22 = horizontal_bar_chart(cnt_srs_medium[\"count of non-zero revenue\"].head(10) / cnt_srs_medium[\"count\"].head(10), 'rgba(128, 128, 0, 0.6)')\n\n# TrafficSource referralPath\ncnt_srs_referral = train_df.groupby('trafficSource.referralPath')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_referral.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_referral = cnt_srs_referral.sort_values(by=\"count\", ascending=False)\ntrace23 = horizontal_bar_chart(cnt_srs_referral[\"count\"].head(10), 'rgba(0, 0, 128, 0.6)')\ntrace24 = horizontal_bar_chart(cnt_srs_referral[\"count of non-zero revenue\"].head(10) / cnt_srs_referral[\"count\"].head(10), 'rgba(0, 0, 128, 0.6)')\n\n# TrafficSource source\ncnt_srs_source = train_df.groupby('trafficSource.source')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_source.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_source = cnt_srs_source.sort_values(by=\"count\", ascending=False)\ntrace25 = horizontal_bar_chart(cnt_srs_source[\"count\"].head(10), 'rgba(128, 0, 0, 0.6)')\ntrace26 = horizontal_bar_chart(cnt_srs_source[\"count of non-zero revenue\"].head(10) / cnt_srs_source[\"count\"].head(10), 'rgba(128, 0, 0, 0.6)')\n\n# Creating a subplot with 13 rows and 2 columns\nfig = make_subplots(rows=13, cols=2, vertical_spacing=0.04, \n                    subplot_titles=[\"adContent - Count\", \"adContent - Non-zero Revenue Count Ratio\",\n                                    \"adwordsClickInfo adNetworkType - Count\", \"adwordsClickInfo adNetworkType - Non-zero Revenue Count Ratio\",\n                                    \"adwordsClickInfo gclId - Count\", \"adwordsClickInfo gclId - Non-zero Revenue Count Ratio\",\n                                    \"adwordsClickInfo isVideoAd - Count\", \"adwordsClickInfo isVideoAd - Non-zero Revenue Count Ratio\",\n                                    \"adwordsClickInfo page - Count\", \"adwordsClickInfo page - Non-zero Revenue Count Ratio\",\n                                    \"adwordsClickInfo slot - Count\", \"adwordsClickInfo slot - Non-zero Revenue Count Ratio\",\n                                    \"campaign - Count\", \"campaign - Non-zero Revenue Count Ratio\",\n                                    \"campaignCode - Count\", \"campaignCode - Non-zero Revenue Count Ratio\",\n                                    \"isTrueDirect - Count\", \"isTrueDirect - Non-zero Revenue Count Ratio\",\n                                    \"keyword - Count\", \"keyword - Non-zero Revenue Count Ratio\",\n                                    \"medium - Count\", \"medium - Non-zero Revenue Count Ratio\",\n                                    \"referralPath - Count\", \"referralPath - Non-zero Revenue Count Ratio\",\n                                    \"source - Count\", \"source - Non-zero Revenue Count Ratio\"])\n\n# Adding traces to the subplot\nfig.add_trace(trace1, row=1, col=1)\nfig.add_trace(trace2, row=1, col=2)\nfig.add_trace(trace3, row=2, col=1)\nfig.add_trace(trace4, row=2, col=2)\n#fig.add_trace(trace5, row=3, col=1)\n#fig.add_trace(trace6, row=3, col=2)\nfig.add_trace(trace7, row=4, col=1)\nfig.add_trace(trace8, row=4, col=2)\nfig.add_trace(trace9, row=5, col=1)\nfig.add_trace(trace10, row=5, col=2)\nfig.add_trace(trace11, row=6, col=1)\nfig.add_trace(trace12, row=6, col=2)\nfig.add_trace(trace13, row=7, col=1)\nfig.add_trace(trace14, row=7, col=2)\nfig.add_trace(trace15, row=8, col=1)\nfig.add_trace(trace16, row=8, col=2)\nfig.add_trace(trace17, row=9, col=1)\nfig.add_trace(trace18, row=9, col=2)\nfig.add_trace(trace19, row=10, col=1)\nfig.add_trace(trace20, row=10, col=2)\nfig.add_trace(trace21, row=11, col=1)\nfig.add_trace(trace22, row=11, col=2)\nfig.add_trace(trace23, row=12, col=1)\nfig.add_trace(trace24, row=12, col=2)\nfig.add_trace(trace25, row=13, col=1)\nfig.add_trace(trace26, row=13, col=2)\n\n# Updating layout\nfig.update_layout(height=3000, width=1200, paper_bgcolor='rgb(233,233,233)', title=\"TrafficSource Plots\")\n\n# Displaying the plot\npy.iplot(fig, filename='trafficsource-plots')\n","metadata":{"execution":{"iopub.status.busy":"2023-09-11T20:38:56.462282Z","iopub.execute_input":"2023-09-11T20:38:56.463053Z","iopub.status.idle":"2023-09-11T20:38:58.842314Z","shell.execute_reply.started":"2023-09-11T20:38:56.463006Z","shell.execute_reply":"2023-09-11T20:38:58.841220Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In the traffic source analysis, it's notable that while YouTube appears frequently in the dataset, the instances where it contributes to non-zero revenue are quite rare.\n\nOn the other hand, Google Plex stands out with a significantly high ratio of non-zero revenue counts compared to its overall count in the traffic source plot.\n\nWhen examining traffic source mediums, it's interesting to observe that the \"referral\" medium exhibits a greater number of non-zero revenue counts compared to the \"organic\" medium.","metadata":{}},{"cell_type":"code","source":"import plotly.graph_objs as go\nfrom plotly.subplots import make_subplots\nimport plotly.offline as py\n\ndef horizontal_bar_chart(cnt_srs, color):\n    trace = go.Bar(\n        y=cnt_srs.index[::-1],\n        x=cnt_srs.values[::-1],\n        showlegend=False,\n        orientation='h',\n        marker=dict(\n            color=color,\n        ),\n    )\n    return trace\n\n# totals.bounces\ncnt_srs_bounces = train_df.groupby('totals.bounces')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_bounces.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_bounces = cnt_srs_bounces.sort_values(by=\"count\", ascending=False)\ntrace1 = horizontal_bar_chart(cnt_srs_bounces[\"count\"].head(60), 'rgba(50, 171, 96, 0.6)')\ntrace2 = horizontal_bar_chart(cnt_srs_bounces[\"count of non-zero revenue\"].head(60) / cnt_srs_bounces[\"count\"].head(60), 'rgba(50, 171, 96, 0.6)')\n\n# totals.hits\ncnt_srs_hits = train_df.groupby('totals.hits')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_hits.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_hits = cnt_srs_hits.sort_values(by=\"count\", ascending=False)\ntrace3 = horizontal_bar_chart(cnt_srs_hits[\"count\"].head(60), 'rgba(71, 58, 131, 0.8)')\ntrace4 = horizontal_bar_chart(cnt_srs_hits[\"count of non-zero revenue\"].head(60) / cnt_srs_hits[\"count\"].head(60), 'rgba(71, 58, 131, 0.8)')\n\n# totals.newVisits\ncnt_srs_newVisits = train_df.groupby('totals.newVisits')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_newVisits.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_newVisits = cnt_srs_newVisits.sort_values(by=\"count\", ascending=False)\ntrace5 = horizontal_bar_chart(cnt_srs_newVisits[\"count\"].head(60), 'rgba(255, 123, 22, 0.6)')\ntrace6 = horizontal_bar_chart(cnt_srs_newVisits[\"count of non-zero revenue\"].head(60) / cnt_srs_newVisits[\"count\"].head(60), 'rgba(255, 123, 22, 0.6)')\n\n# totals.pageviews\ncnt_srs_pageviews = train_df.groupby('totals.pageviews')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_pageviews.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_pageviews = cnt_srs_pageviews.sort_values(by=\"count\", ascending=False)\ntrace7 = horizontal_bar_chart(cnt_srs_pageviews[\"count\"].head(60), 'rgba(246, 78, 139, 0.6)')\ntrace8 = horizontal_bar_chart(cnt_srs_pageviews[\"count of non-zero revenue\"].head(60) / cnt_srs_pageviews[\"count\"].head(60), 'rgba(246, 78, 139, 0.6)')\n\n# totals.transactionRevenue\ncnt_srs_revenue = train_df.groupby('totals.transactionRevenue')['totals.transactionRevenue'].agg(['size', 'count', 'mean'])\ncnt_srs_revenue.columns = [\"count\", \"count of non-zero revenue\", \"mean\"]\ncnt_srs_revenue = cnt_srs_revenue.sort_values(by=\"count\", ascending=False)\ntrace9 = horizontal_bar_chart(cnt_srs_revenue[\"count\"].head(60), 'rgba(0, 128, 128, 0.6)')\ntrace10 = horizontal_bar_chart(cnt_srs_revenue[\"count of non-zero revenue\"].head(60) / cnt_srs_revenue[\"count\"].head(60), 'rgba(0, 128, 128, 0.6)')\n\n# Creating a subplot with 5 rows and 2 columns\nfig = make_subplots(rows=5, cols=2, vertical_spacing=0.04, \n                    subplot_titles=[\"totals.bounces - Count\", \"totals.bounces - Non-zero Revenue Count Ratio\",\n                                    \"totals.hits - Count\", \"totals.hits - Non-zero Revenue Count Ratio\",\n                                    \"totals.newVisits - Count\", \"totals.newVisits - Non-zero Revenue Count Ratio\",\n                                    \"totals.pageviews - Count\", \"totals.pageviews - Non-zero Revenue Count Ratio\",\n                                    \"totals.transactionRevenue - Count\", \"totals.transactionRevenue - Non-zero Revenue Count Ratio\"])\n\n# Adding traces to the subplot\nfig.add_trace(trace1, row=1, col=1)\nfig.add_trace(trace2, row=1, col=2)\nfig.add_trace(trace3, row=2, col=1)\nfig.add_trace(trace4, row=2, col=2)\nfig.add_trace(trace5, row=3, col=1)\nfig.add_trace(trace6, row=3, col=2)\nfig.add_trace(trace7, row=4, col=1)\nfig.add_trace(trace8, row=4, col=2)\nfig.add_trace(trace9, row=5, col=1)\nfig.add_trace(trace10, row=5, col=2)\n\n# Updating layout\nfig.update_layout(height=1000, width=900, paper_bgcolor='rgb(233,233,233)', title=\"Totals Plots\")\n\n# Displaying the plot\npy.iplot(fig, filename='totals-plots')\n","metadata":{"execution":{"iopub.status.busy":"2023-09-11T20:38:58.843791Z","iopub.execute_input":"2023-09-11T20:38:58.844139Z","iopub.status.idle":"2023-09-11T20:38:59.577981Z","shell.execute_reply.started":"2023-09-11T20:38:58.844109Z","shell.execute_reply":"2023-09-11T20:38:59.576881Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"As the number of hits or pageviews per visitor transaction increases, we observe a significant rise in the ratio of non-zero revenue occurrences.","metadata":{}},{"cell_type":"markdown","source":"**6. Baseline Model:**\n\nNow let us build a baseline model on this dataset. Before we start building models, let us look at the variable names which are there in train dataset and not in test dataset.","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nfrom sklearn import preprocessing\nimport datetime\n\ntrain_df['date'] = pd.to_datetime(train_df['date'], format='%Y%m%d')\n\n# Impute 0 for missing target values\ntrain_df[\"totals.transactionRevenue\"].fillna(0, inplace=True)\ntrain_y = train_df[\"totals.transactionRevenue\"].values\ntrain_id = train_df[\"fullVisitorId\"].values\ntest_id = test_df[\"fullVisitorId\"].values\n\n# label encode the categorical variables and convert the numerical variables to float\ncat_cols = [\"channelGrouping\", \"device.browser\", \n            \"device.deviceCategory\", \"device.operatingSystem\", \n            \"geoNetwork.city\", \"geoNetwork.continent\", \n            \"geoNetwork.country\", \"geoNetwork.metro\",\n            \"geoNetwork.networkDomain\", \"geoNetwork.region\", \n            \"geoNetwork.subContinent\", \"trafficSource.adContent\", \n            \"trafficSource.adwordsClickInfo.adNetworkType\", \n            \"trafficSource.adwordsClickInfo.gclId\", \n            \"trafficSource.adwordsClickInfo.page\", \n            \"trafficSource.adwordsClickInfo.slot\", \"trafficSource.campaign\",\n            \"trafficSource.keyword\", \"trafficSource.medium\", \n            \"trafficSource.referralPath\", \"trafficSource.source\",\n            'trafficSource.adwordsClickInfo.isVideoAd', 'trafficSource.isTrueDirect']\nfor col in cat_cols:\n    print(col)\n    lbl = preprocessing.LabelEncoder()\n    lbl.fit(list(train_df[col].values.astype('str')) + list(test_df[col].values.astype('str')))\n    train_df[col] = lbl.transform(list(train_df[col].values.astype('str')))\n    test_df[col] = lbl.transform(list(test_df[col].values.astype('str')))\n\nnum_cols = [\"totals.hits\", \"totals.pageviews\", \"visitNumber\", \"visitStartTime\", 'totals.bounces',  'totals.newVisits']    \nfor col in num_cols:\n    train_df[col] = train_df[col].astype(float)\n    test_df[col] = test_df[col].astype(float)\n\n# Split the train dataset into development and valid based on time \ndev_df = train_df[train_df['date'] <= datetime.datetime(2017, 5, 31)]\nval_df = train_df[train_df['date'] > datetime.datetime(2017, 5, 31)]\ndev_y = np.log1p(dev_df[\"totals.transactionRevenue\"].values)\nval_y = np.log1p(val_df[\"totals.transactionRevenue\"].values)\n\ndev_X = dev_df[cat_cols + num_cols] \nval_X = val_df[cat_cols + num_cols] \ntest_X = test_df[cat_cols + num_cols] \n\n\n\nimport lightgbm as lgb\ndef run_lgb(train_X, train_y, val_X, val_y, test_X):\n    params = {\n        \"objective\" : \"regression\",\n        \"metric\" : \"rmse\", \n        \"num_leaves\" : 30,\n        \"min_child_samples\" : 100,\n        \"learning_rate\" : 0.1,\n        \"bagging_fraction\" : 0.7,\n        \"feature_fraction\" : 0.5,\n        \"bagging_frequency\" : 5,\n        \"bagging_seed\" : 2018,\n        \"verbosity\" : -1\n    }\n    \n    lgtrain = lgb.Dataset(train_X, label=train_y)\n    lgval = lgb.Dataset(val_X, label=val_y)\n    model = lgb.train(params, lgtrain, 1000, valid_sets=[lgval], early_stopping_rounds=100, verbose_eval=100)\n    \n    pred_test_y = model.predict(test_X, num_iteration=model.best_iteration)\n    pred_val_y = model.predict(val_X, num_iteration=model.best_iteration)\n    return pred_test_y, model, pred_val_y\n\n# Training the model #\npred_test, model, pred_val = run_lgb(dev_X, dev_y, val_X, val_y, test_X)\n\n\nfrom sklearn import metrics\npred_val[pred_val < 0] = 0\nval_pred_df = pd.DataFrame({\"fullVisitorId\": val_df[\"fullVisitorId\"].values})\nval_pred_df[\"transactionRevenue\"] = val_df[\"totals.transactionRevenue\"].values\nval_pred_df[\"PredictedRevenue\"] = np.expm1(pred_val)\n\n# RMSE\nrmse = np.sqrt(metrics.mean_squared_error(np.log1p(val_pred_df[\"transactionRevenue\"].values), np.log1p(val_pred_df[\"PredictedRevenue\"].values)))\nprint(\"Root Mean Squared Error (RMSE):\", rmse)\n\n\nsub_df = pd.DataFrame({\"fullVisitorId\":test_id})\npred_test[pred_test<0] = 0\nsub_df[\"PredictedLogRevenue\"] = np.expm1(pred_test)\nsub_df = sub_df.groupby(\"fullVisitorId\")[\"PredictedLogRevenue\"].sum().reset_index()\nsub_df.columns = [\"fullVisitorId\", \"PredictedLogRevenue\"]\nsub_df[\"PredictedLogRevenue\"] = np.log1p(sub_df[\"PredictedLogRevenue\"])\nsub_df.to_csv(\"baseline_lgb.csv\", index=False)","metadata":{"execution":{"iopub.status.busy":"2023-09-11T20:38:59.582644Z","iopub.execute_input":"2023-09-11T20:38:59.583155Z","iopub.status.idle":"2023-09-11T20:43:04.954090Z","shell.execute_reply.started":"2023-09-11T20:38:59.583111Z","shell.execute_reply":"2023-09-11T20:43:04.952665Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport lightgbm as lgb\nfrom sklearn.model_selection import train_test_split\nimport warnings\n\n# Suppress warnings\nwarnings.filterwarnings(\"ignore\")\n\n# Loading required libraries\nimport pandas as pd\nimport numpy as np\nimport json\nfrom datetime import datetime\nimport lightgbm as lgb\nimport warnings\nfrom sklearn.model_selection import train_test_split\n\n# Suppress warnings\nwarnings.filterwarnings(\"ignore\")\n\n# Defining types of extracted attributes\ndtypes = {\n    \"fullVisitorId\": str,\n    \"channelGrouping\": str,\n    \"date\": str,\n    \"device\": str,\n    \"geoNetwork\": str,\n    \"socialEngagementType\": str,\n    \"totals\": str,\n    \"trafficSource\": str,\n    \"visitId\": int,\n    \"visitNumber\": int,\n    \"visitStartTime\": int,\n    \"hits\": str,\n    \"customDimensions\": str\n}\n\n# Data extraction\nprint(\"Data Extraction\")\ntr0 = pd.read_csv('/kaggle/input/ga-customer-revenue-prediction/train.csv', dtype=dtypes)\nte0 = pd.read_csv('/kaggle/input/ga-customer-revenue-prediction/train.csv', dtype=dtypes)\n\n# Data parsing\ndef flatten_json(df, col_name):\n    df = df.copy()\n    df[col_name] = df[col_name].apply(lambda x: json.loads(x))\n    df = pd.concat([df.drop([col_name], axis=1), df[col_name].apply(pd.Series)], axis=1)\n    return df\n\nprint(\"Data Parsing\")\ntr0 = flatten_json(tr0, 'device')\ntr0 = flatten_json(tr0, 'geoNetwork')\ntr0 = flatten_json(tr0, 'trafficSource')\ntr0 = flatten_json(tr0, 'totals')\n\n# Define useful columns\n#good_cols = [\"channelGrouping\", \"date\", \"fullVisitorId\", \"visitId\", \"visitNumber\", \"visitStartTime\", \"browser\",\n #            \"deviceCategory\", \"isMobile\", \"operatingSystem\", \"city\", \"continent\", \"country\", \"metro\", \"networkDomain\",\n  #           \"region\", \"subContinent\", \"bounces\", \"hits\", \"newVisits\", \"pageviews\", \"sessionQualityDim\", \"timeOnSite\",\n   #          \"totalTransactionRevenue\", \"transactionRevenue\", \"transactions\", \"adContent\", \"adwordsClickInfo.adNetworkType\",\n    #         \"adwordsClickInfo.gclId\", \"adwordsClickInfo.isVideoAd\", \"adwordsClickInfo.page\", \"adwordsClickInfo.slot\",\n     #        \"campaign\", \"isTrueDirect\", \"keyword\", \"medium\", \"referralPath\", \"source\"]\ngood_cols = [\"channelGrouping\", \"device.browser\", \n            \"device.deviceCategory\", \"device.operatingSystem\", \n            \"geoNetwork.city\", \"geoNetwork.continent\", \n            \"geoNetwork.country\", \"geoNetwork.metro\",\n            \"geoNetwork.networkDomain\", \"geoNetwork.region\", \n            \"geoNetwork.subContinent\", \"trafficSource.adContent\", \n            \"trafficSource.adwordsClickInfo.adNetworkType\", \n            \"trafficSource.adwordsClickInfo.gclId\", \n            \"trafficSource.adwordsClickInfo.page\", \n            \"trafficSource.adwordsClickInfo.slot\", \"trafficSource.campaign\",\n            \"trafficSource.keyword\", \"trafficSource.medium\", \n            \"trafficSource.referralPath\", \"trafficSource.source\",\n            'trafficSource.adwordsClickInfo.isVideoAd', 'trafficSource.isTrueDirect']\n\n# Combine tables and convert to dataframe\ntr = tr0[good_cols].append(te0[good_cols], sort=False)\ntr.reset_index(drop=True, inplace=True)\n\n# Convert date column to datetime\ntr[\"date\"] = pd.to_datetime(tr[\"date\"], format=\"%Y%m%d\", errors=\"coerce\")\n\n# Convert columns to integers\nint_cols = [\"visitNumber\", \"visitStartTime\", \"bounces\", \"hits\", \"newVisits\", \"pageviews\", \"transactionRevenue\", \"transactions\"]\ntr[int_cols] = tr[int_cols].fillna(0).astype(int)\n\n# Replace missing transactionRevenue with 0\ntr[\"transactionRevenue\"].fillna(0, inplace=True)\n\n# Function to get time frame with features\ndef get_time_frame_with_features(data, k):\n    tf = data[(data[\"date\"] >= data[\"date\"].min() + pd.DateOffset(days=168 * (k - 1))) &\n              (data[\"date\"] < data[\"date\"].min() + pd.DateOffset(days=168 * k))].copy()\n    tf_fvid = data[(data[\"date\"] >= data[\"date\"].min() + pd.DateOffset(days=168 * k + 46)) &\n                   (data[\"date\"] < data[\"date\"].min() + pd.DateOffset(days=168 * k + 46 + 62))][\"fullVisitorId\"].unique()\n    tf_returned = tf[tf[\"fullVisitorId\"].isin(tf_fvid)].copy()\n    tf_tst = data[(data[\"fullVisitorId\"].isin(tf_returned[\"fullVisitorId\"])) &\n                   (data[\"date\"] >= data[\"date\"].min() + pd.DateOffset(days=168 * k + 46)) &\n                   (data[\"date\"] < data[\"date\"].min() + pd.DateOffset(days=168 * k + 46 + 62))].copy()\n\n    tf_target = tf_tst.groupby(\"fullVisitorId\")[\"transactionRevenue\"].sum().apply(lambda x: np.log1p(x))\n    tf_target = tf_target.reset_index()\n    tf_target.columns = [\"fullVisitorId\", \"target\"]\n    tf_target[\"ret\"] = 1\n\n    tf_nonret = pd.DataFrame(data={\"fullVisitorId\": tf[~tf[\"fullVisitorId\"].isin(tf_fvid)][\"fullVisitorId\"].unique(),\n                                   \"target\": 0,\n                                   \"ret\": 0})\n\n    tf_target = pd.concat([tf_target, tf_nonret], sort=False)\n\n    tf_maxdate = tf[\"date\"].max()\n    tf_mindate = tf[\"date\"].min()\n\n    tf = tf.groupby(\"fullVisitorId\").agg({\n        \"channelGrouping\": \"max\",\n        \"first_ses_from_the_period_start\": lambda x: (x.min() - tf_mindate).astype(int),\n        \"last_ses_from_the_period_end\": lambda x: (tf_maxdate - x.max()).astype(int),\n        \"interval_dates\": lambda x: (x.max() - x.min()).astype(int),\n        \"unique_date_num\": \"nunique\",\n        \"maxVisitNum\": \"max\",\n        \"browser\": \"max\",\n        \"operatingSystem\": \"max\",\n        \"deviceCategory\": \"max\",\n        \"continent\": \"max\",\n        \"subContinent\": \"max\",\n        \"country\": \"max\",\n        \"region\": \"max\",\n        \"metro\": \"max\",\n        \"city\": \"max\",\n        \"networkDomain\": \"max\",\n        \"source\": \"max\",\n        \"medium\": \"max\",\n        \"isVideoAd_mean\": lambda x: np.mean(x),\n        \"isMobile\": lambda x: np.mean(x),\n        \"isTrueDirect\": lambda x: np.mean(x),\n        \"bounce_sessions\": lambda x: np.sum(x),\n        \"hits_sum\": \"sum\",\n        \"hits_mean\": \"mean\",\n        \"hits_min\": \"min\",\n        \"hits_max\": \"max\",\n        \"hits_median\": \"median\",\n        \"hits_sd\": \"std\",\n        \"pageviews_sum\": \"sum\",\n        \"pageviews_mean\": \"mean\",\n        \"pageviews_min\": \"min\",\n        \"pageviews_max\": \"max\",\n        \"pageviews_median\": \"median\",\n        \"pageviews_sd\": \"std\",\n        \"session_cnt\": \"size\",\n        \"transactionRevenue\": \"sum\",\n        \"transactions\": \"sum\"\n    }).reset_index()\n\n    tf = tf.merge(tf_target, on=\"fullVisitorId\", how=\"left\")\n\n    return tf\n\n# Getting parts of train-set\nprint(\"Get 1st train part\")\ntr1 = get_time_frame_with_features(tr, 1)\nprint(\"Get 2nd train part\")\ntr2 = get_time_frame_with_features(tr, 2)\nprint(\"Get 3rd train part\")\ntr3 = get_time_frame_with_features(tr, 3)\nprint(\"Get 4th train part\")\ntr4 = get_time_frame_with_features(tr, 4)\n\n# Costruction of the test-set (by analogy as train-set)\nprint(\"Get test\")\ntr5 = tr[tr[\"date\"] >= '2018-05-01'].copy()\ntr5_maxdate = tr5[\"date\"].max()\ntr5_mindate = tr5[\"date\"].min()\n\ntf = tr5.groupby(\"fullVisitorId\").agg({\n    \"channelGrouping\": \"max\",\n    \"first_ses_from_the_period_start\": lambda x: (x.min() - tr5_mindate).astype(int),\n    \"last_ses_from_the_period_end\": lambda x: (tr5_maxdate - x.max()).astype(int),\n    \"interval_dates\": lambda x: (x.max() - x.min()).astype(int),\n    \"unique_date_num\": \"nunique\",\n    \"maxVisitNum\": \"max\",\n    \"browser\": \"max\",\n    \"operatingSystem\": \"max\",\n    \"deviceCategory\": \"max\",\n    \"continent\": \"max\",\n    \"subContinent\": \"max\",\n    \"country\": \"max\",\n    \"region\": \"max\",\n    \"metro\": \"max\",\n    \"city\": \"max\",\n    \"networkDomain\": \"max\",\n    \"source\": \"max\",\n    \"medium\": \"max\",\n    \"isVideoAd_mean\": lambda x: np.mean(x),\n    \"isMobile\": lambda x: np.mean(x),\n    \"isTrueDirect\": lambda x: np.mean(x),\n    \"bounce_sessions\": lambda x: np.sum(x),\n    \"hits_sum\": \"sum\",\n    \"hits_mean\": \"mean\",\n    \"hits_min\": \"min\",\n    \"hits_max\": \"max\",\n    \"hits_median\": \"median\",\n    \"hits_sd\": \"std\",\n    \"pageviews_sum\": \"sum\",\n    \"pageviews_mean\": \"mean\",\n    \"pageviews_min\": \"min\",\n    \"pageviews_max\": \"max\",\n    \"pageviews_median\": \"median\",\n    \"pageviews_sd\": \"std\",\n    \"session_cnt\": \"size\",\n    \"transactionRevenue\": \"sum\",\n    \"transactions\": \"sum\"\n}).reset_index()\n\ntr5[\"target\"] = np.nan\ntr5[\"ret\"] = np.nan\ntr5 = pd.concat([tr5, tf], sort=False)\n\n# Combine all pieces and convert the types\ntrain_all = pd.concat([tr1, tr2, tr3, tr4, tr5], sort=False)\ntrain_all[\"interval_dates\"] = train_all[\"interval_dates\"].astype(int)\ntrain_all[\"last_ses_from_the_period_end\"] = train_all[\"last_ses_from_the_period_end\"].astype(int)\ntrain_all[\"first_ses_from_the_period_start\"] = train_all[\"first_ses_from_the_period_start\"].astype(int)\n\n# Filter train and test from combined dataframe\ntrain = train_all[~train_all[\"target\"].isna()].copy()\ntest = train_all[train_all[\"target\"].isna()].copy()\n\n# Parameters of \"isReturned\" classifier\nparam_lgb2 = {\n    \"objective\": \"binary\",\n    \"max_bin\": 256,\n    \"learning_rate\": 0.01,\n    \"num_leaves\": 15,\n    \"bagging_fraction\": 0.9,\n    \"feature_fraction\": 0.8,\n    \"min_data\": 1,\n    \"bagging_freq\": 1,\n    \"metric\": \"binary_logloss\"\n}\n\n# Parameters of \"How_Much_Returned_Will_Pay\" regressor\nparam_lgb3 = {\n    \"objective\": \"regression\",\n    \"max_bin\": 256,\n    \"learning_rate\": 0.01,\n    \"num_leaves\": 9,\n    \"bagging_fraction\": 0.9,\n    \"feature_fraction\": 0.8,\n    \"min_data\": 1,\n    \"bagging_freq\": 1,\n    \"metric\": \"rmse\"\n}\n\n# Training and prediction of models: Averaging of 10 [Classificator*Regressor] values\nX_train = train.drop([\"fullVisitorId\", \"ret\", \"target\"], axis=1)\ny_train = train[\"ret\"]\nX_test = test.drop([\"fullVisitorId\", \"ret\", \"target\"], axis=1)\n\npr_lgb_sum = 0\nprint(\"Training and predictions\")\nfor i in range(10):\n    print(f\"Iteration number {i}\")\n    dtrain_all = lgb.Dataset(X_train, label=y_train)\n    lgb_model1 = lgb.train(param_lgb2, dtrain_all, num_boost_round=1200)\n    pr_lgb = lgb_model1.predict(X_test)\n\n    dtrain_ret = lgb.Dataset(X_train[y_train == 1], label=train[train[\"ret\"] == 1][\"target\"])\n    lgb_model2 = lgb.train(param_lgb3, dtrain_ret, num_boost_round=368)\n    pr_lgb_ret = lgb_model2.predict(X_test)\n\n    pr_lgb_sum += pr_lgb * pr_lgb_ret\n\npr_final2 = pr_lgb_sum / 10\n\n# Writing final predictions to CSV\nsubmission = pd.DataFrame({\n    \"fullVisitorId\": test[\"fullVisitorId\"],\n    \"PredictedLogRevenue\": pr_final2\n})\n\nsubmission.to_csv(\"tst4.csv\", index=False)\n","metadata":{"execution":{"iopub.status.busy":"2023-09-13T21:43:07.517070Z","iopub.execute_input":"2023-09-13T21:43:07.517559Z"},"trusted":true},"execution_count":null,"outputs":[]}],"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"}}