{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":10038,"databundleVersionId":180088,"sourceType":"competition"}],"dockerImageVersionId":30646,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"## 1. Import libraries & data","metadata":{"execution":{"iopub.status.busy":"2024-02-03T17:16:32.310371Z","iopub.execute_input":"2024-02-03T17:16:32.311014Z","iopub.status.idle":"2024-02-03T17:16:32.319617Z","shell.execute_reply.started":"2024-02-03T17:16:32.310823Z","shell.execute_reply":"2024-02-03T17:16:32.317608Z"}}},{"cell_type":"markdown","source":">**Reference:**\n\n- Terms Used in Google Universal Analytics & this dataset:    \nhttps://support.google.com/analytics/answer/3437719?hl=en","metadata":{}},{"cell_type":"code","source":"import os\nimport json\nimport numpy as np \nimport pandas as pd\nimport seaborn as sns \nimport matplotlib as mpl\nimport scipy.stats as st\nimport plotly.express as px\nimport plotly.graph_objs as go\nimport matplotlib.pyplot as plt\nimport matplotlib.ticker as ticker\n\nfrom tqdm import tqdm\nfrom plotly import tools\nfrom pandas import json_normalize\n\nimport xgboost as xgb\nfrom sklearn.metrics import mean_squared_error\n\nimport warnings\nwarnings.filterwarnings('ignore')\n\n# Create a list of colors \ncolors = ['b', 'g', 'r', 'c', 'm', 'y', 'k']\n\nsns.set_style('whitegrid')\nplt.rcParams['figure.figsize'] = 10, 6\npd.options.display.float_format = '{:,.0f}'.format\nnp.set_printoptions(formatter={'float': '{: 0.0f}'.format})","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:37:14.863226Z","iopub.execute_input":"2024-02-03T23:37:14.863659Z","iopub.status.idle":"2024-02-03T23:37:16.320982Z","shell.execute_reply.started":"2024-02-03T23:37:14.863621Z","shell.execute_reply":"2024-02-03T23:37:16.319622Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"- Read csv and flatten json fields:   \nhttps://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields\n\n- Take string as an input and create a json object with the specified string as object name   \nhttps://towardsdatascience.com/why-and-how-to-use-pandas-with-large-data-9594dda2ea4c","metadata":{}},{"cell_type":"code","source":"# Extract features from 'device', 'geoNetwork', 'totals', 'trafficSource’. \njson_columns = ['device', 'geoNetwork','totals', 'trafficSource']\n\ndef load_dataframe(csv_path):\n    path = csv_path\n    df = pd.read_csv(path, converters={column: json.loads for column in json_columns}, \n                     dtype={'fullVisitorId': 'str'})\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\n    return df","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:37:16.351404Z","iopub.execute_input":"2024-02-03T23:37:16.352165Z","iopub.status.idle":"2024-02-03T23:37:16.362504Z","shell.execute_reply.started":"2024-02-03T23:37:16.352131Z","shell.execute_reply":"2024-02-03T23:37:16.359978Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = load_dataframe('/kaggle/input/ga-customer-revenue-prediction/train.csv')\ntest = load_dataframe('/kaggle/input/ga-customer-revenue-prediction/test.csv')","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:37:16.363885Z","iopub.execute_input":"2024-02-03T23:37:16.364261Z","iopub.status.idle":"2024-02-03T23:40:06.071068Z","shell.execute_reply.started":"2024-02-03T23:37:16.364227Z","shell.execute_reply":"2024-02-03T23:40:06.069846Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Store the dataframes as csv files\ntrain.to_csv('train_df.csv')\ntest.to_csv('test_df.csv')","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:40:06.072634Z","iopub.execute_input":"2024-02-03T23:40:06.073059Z","iopub.status.idle":"2024-02-03T23:40:53.568074Z","shell.execute_reply.started":"2024-02-03T23:40:06.073022Z","shell.execute_reply":"2024-02-03T23:40:53.566738Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Read csv files\ntrain = pd.read_csv('train_df.csv', dtype={'fullVisitorId': 'str'}, index_col=0)\n\ntest = pd.read_csv('test_df.csv', dtype={'fullVisitorId': 'str'}, index_col=0)\n\nprint(train.shape)\nprint(test.shape)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:40:53.569685Z","iopub.execute_input":"2024-02-03T23:40:53.570155Z","iopub.status.idle":"2024-02-03T23:41:17.321009Z","shell.execute_reply.started":"2024-02-03T23:40:53.570106Z","shell.execute_reply":"2024-02-03T23:41:17.319299Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:17.322292Z","iopub.execute_input":"2024-02-03T23:41:17.322652Z","iopub.status.idle":"2024-02-03T23:41:17.351354Z","shell.execute_reply.started":"2024-02-03T23:41:17.322622Z","shell.execute_reply":"2024-02-03T23:41:17.350011Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.info()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:17.353024Z","iopub.execute_input":"2024-02-03T23:41:17.353451Z","iopub.status.idle":"2024-02-03T23:41:18.601275Z","shell.execute_reply.started":"2024-02-03T23:41:17.353420Z","shell.execute_reply":"2024-02-03T23:41:18.599905Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:18.604583Z","iopub.execute_input":"2024-02-03T23:41:18.605281Z","iopub.status.idle":"2024-02-03T23:41:18.627690Z","shell.execute_reply.started":"2024-02-03T23:41:18.605257Z","shell.execute_reply":"2024-02-03T23:41:18.626741Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 2. Data Preprocessing","metadata":{}},{"cell_type":"markdown","source":"### 2.1 Remove Constant Features\n\nFeatures that have only one unique value are often referred to as constant features or constant columns.   \n\nThese features do not provide any variability in the data, and as a result, they typically do not contribute    \nuseful information to the model. We might as well drop these features.","metadata":{}},{"cell_type":"code","source":"train.columns","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:18.628838Z","iopub.execute_input":"2024-02-03T23:41:18.629218Z","iopub.status.idle":"2024-02-03T23:41:18.640572Z","shell.execute_reply.started":"2024-02-03T23:41:18.629189Z","shell.execute_reply":"2024-02-03T23:41:18.639563Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Keep 'totals_bounces' column\ntrain['totals_bounces'].unique()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:18.643595Z","iopub.execute_input":"2024-02-03T23:41:18.644133Z","iopub.status.idle":"2024-02-03T23:41:18.661029Z","shell.execute_reply.started":"2024-02-03T23:41:18.644087Z","shell.execute_reply":"2024-02-03T23:41:18.659602Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train['totals_bounces'].fillna(0, inplace=True)\ntrain['totals_bounces'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:18.662245Z","iopub.execute_input":"2024-02-03T23:41:18.662603Z","iopub.status.idle":"2024-02-03T23:41:18.684018Z","shell.execute_reply.started":"2024-02-03T23:41:18.662576Z","shell.execute_reply":"2024-02-03T23:41:18.682958Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# check unique values in train columns\ntrain.nunique()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:18.685197Z","iopub.execute_input":"2024-02-03T23:41:18.685490Z","iopub.status.idle":"2024-02-03T23:41:21.938779Z","shell.execute_reply.started":"2024-02-03T23:41:18.685465Z","shell.execute_reply":"2024-02-03T23:41:21.937376Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Identify constant features\nconstant_features = train.columns[train.nunique() == 1]\n\n# Remove constant features\ntrain.drop(columns=constant_features, inplace=True)\ntrain.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:21.940792Z","iopub.execute_input":"2024-02-03T23:41:21.941204Z","iopub.status.idle":"2024-02-03T23:41:25.377800Z","shell.execute_reply.started":"2024-02-03T23:41:21.941165Z","shell.execute_reply":"2024-02-03T23:41:25.376274Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Keep 'totals_bounces' column\ntest['totals_bounces'].unique()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:25.379703Z","iopub.execute_input":"2024-02-03T23:41:25.380128Z","iopub.status.idle":"2024-02-03T23:41:25.394002Z","shell.execute_reply.started":"2024-02-03T23:41:25.380089Z","shell.execute_reply":"2024-02-03T23:41:25.392623Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test['totals_bounces'].fillna(0, inplace=True)\ntest['totals_bounces'].value_counts()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:25.395584Z","iopub.execute_input":"2024-02-03T23:41:25.395936Z","iopub.status.idle":"2024-02-03T23:41:25.416297Z","shell.execute_reply.started":"2024-02-03T23:41:25.395908Z","shell.execute_reply":"2024-02-03T23:41:25.414929Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# check unique values in test columns\ntest.nunique()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:25.417856Z","iopub.execute_input":"2024-02-03T23:41:25.418201Z","iopub.status.idle":"2024-02-03T23:41:28.274965Z","shell.execute_reply.started":"2024-02-03T23:41:25.418167Z","shell.execute_reply":"2024-02-03T23:41:28.273581Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Identify constant features\nconstant_features = test.columns[test.nunique() == 1]\n\n# Remove constant features\ntest.drop(columns=constant_features, inplace=True)\ntest.shape","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:28.276447Z","iopub.execute_input":"2024-02-03T23:41:28.276762Z","iopub.status.idle":"2024-02-03T23:41:31.370278Z","shell.execute_reply.started":"2024-02-03T23:41:28.276738Z","shell.execute_reply":"2024-02-03T23:41:31.369093Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 2.2 Find Percentage of missing values in the columns\n\n- credits:   \nhttps://stackoverflow.com/questions/51070985/find-out-the-percentage-of-missing-values-in-each-column-in-the-given-dataset","metadata":{}},{"cell_type":"code","source":"# train_df - Check the percentage of missing values in each column\npercent_missing_train = train.isnull().sum() * 100 / len(train)\n\nmissing_value_train = pd.DataFrame({'percent_missing': percent_missing_train})\nmissing_value_train.sort_values(by='percent_missing', ascending=False, inplace=True)\n\n# Columns with percent_missing > 0\nmissing_value_train_filtered = missing_value_train.loc[missing_value_train['percent_missing'] > 0]\nmissing_value_train_filtered","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:31.371763Z","iopub.execute_input":"2024-02-03T23:41:31.372056Z","iopub.status.idle":"2024-02-03T23:41:32.040712Z","shell.execute_reply.started":"2024-02-03T23:41:31.372025Z","shell.execute_reply":"2024-02-03T23:41:32.039705Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# test_df - Check the percentage of missing values in each column\npercent_missing_test = test.isnull().sum() * 100 / len(test)\n\nmissing_value_test = pd.DataFrame({#'column_name': test_df.columns,\n                                 'percent_missing': percent_missing_test})\nmissing_value_test.sort_values(by='percent_missing', ascending=False, inplace=True)\n\n# Columns with percent_missing > 0\nmissing_value_test_filtered = missing_value_test.loc[missing_value_test['percent_missing'] > 0]\nmissing_value_test_filtered","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:32.041937Z","iopub.execute_input":"2024-02-03T23:41:32.042309Z","iopub.status.idle":"2024-02-03T23:41:32.615988Z","shell.execute_reply.started":"2024-02-03T23:41:32.042273Z","shell.execute_reply":"2024-02-03T23:41:32.615255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 2.3 Columns to Drop","metadata":{}},{"cell_type":"code","source":"train.columns","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:32.617148Z","iopub.execute_input":"2024-02-03T23:41:32.617587Z","iopub.status.idle":"2024-02-03T23:41:32.624257Z","shell.execute_reply.started":"2024-02-03T23:41:32.617561Z","shell.execute_reply":"2024-02-03T23:41:32.623293Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cols_to_drop = ['geoNetwork_continent', 'geoNetwork_subContinent', \n                'geoNetwork_metro', 'geoNetwork_city', 'geoNetwork_networkDomain', \n                'trafficSource_campaign', 'trafficSource_keyword',\n                'trafficSource_referralPath', 'trafficSource_adwordsClickInfo.page',\n                'trafficSource_adwordsClickInfo.slot', 'trafficSource_adwordsClickInfo.gclId',\n                'trafficSource_adContent', 'visitId', 'visitStartTime']","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:32.626156Z","iopub.execute_input":"2024-02-03T23:41:32.626485Z","iopub.status.idle":"2024-02-03T23:41:32.635734Z","shell.execute_reply.started":"2024-02-03T23:41:32.626458Z","shell.execute_reply":"2024-02-03T23:41:32.634487Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = train.copy()\n\ndf_train = df_train.drop(columns=cols_to_drop, axis=1)\ndf_train","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:32.637594Z","iopub.execute_input":"2024-02-03T23:41:32.637960Z","iopub.status.idle":"2024-02-03T23:41:33.087878Z","shell.execute_reply.started":"2024-02-03T23:41:32.637937Z","shell.execute_reply":"2024-02-03T23:41:33.086284Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.info()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:33.089402Z","iopub.execute_input":"2024-02-03T23:41:33.090145Z","iopub.status.idle":"2024-02-03T23:41:33.495485Z","shell.execute_reply.started":"2024-02-03T23:41:33.090063Z","shell.execute_reply":"2024-02-03T23:41:33.493868Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.describe().T","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:33.497135Z","iopub.execute_input":"2024-02-03T23:41:33.497596Z","iopub.status.idle":"2024-02-03T23:41:33.616799Z","shell.execute_reply.started":"2024-02-03T23:41:33.497562Z","shell.execute_reply":"2024-02-03T23:41:33.615598Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test = test.copy()\n\ndf_test = df_test.drop(columns=cols_to_drop, axis=1)\ndf_test","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:33.618297Z","iopub.execute_input":"2024-02-03T23:41:33.618683Z","iopub.status.idle":"2024-02-03T23:41:34.028877Z","shell.execute_reply.started":"2024-02-03T23:41:33.618651Z","shell.execute_reply":"2024-02-03T23:41:34.027617Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test.info()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:34.036247Z","iopub.execute_input":"2024-02-03T23:41:34.036710Z","iopub.status.idle":"2024-02-03T23:41:34.349504Z","shell.execute_reply.started":"2024-02-03T23:41:34.036679Z","shell.execute_reply":"2024-02-03T23:41:34.348355Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test.describe().T","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:34.351029Z","iopub.execute_input":"2024-02-03T23:41:34.351414Z","iopub.status.idle":"2024-02-03T23:41:34.451641Z","shell.execute_reply.started":"2024-02-03T23:41:34.351372Z","shell.execute_reply":"2024-02-03T23:41:34.450259Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 2.4 Impute Missing Values","metadata":{}},{"cell_type":"code","source":"# numerical columns fillna\ndf_train['totals_pageviews'].fillna(0, inplace=True)\ndf_train['totals_transactionRevenue'].fillna(0, inplace=True)\n\ndf_test['totals_pageviews'].fillna(0, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:34.453023Z","iopub.execute_input":"2024-02-03T23:41:34.453496Z","iopub.status.idle":"2024-02-03T23:41:34.466752Z","shell.execute_reply.started":"2024-02-03T23:41:34.453453Z","shell.execute_reply":"2024-02-03T23:41:34.465434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.columns","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:34.468731Z","iopub.execute_input":"2024-02-03T23:41:34.469991Z","iopub.status.idle":"2024-02-03T23:41:34.477028Z","shell.execute_reply.started":"2024-02-03T23:41:34.469949Z","shell.execute_reply":"2024-02-03T23:41:34.476064Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Replace 'nan', 'NaN', '(not set)' in both dataframes\ncolumns_to_process = ['device_browser', 'device_operatingSystem', 'trafficSource_source', \n                      'trafficSource_medium', 'trafficSource_adwordsClickInfo.adNetworkType']\n\nvalues_to_replace = ['nan', 'NaN', '(not set)', np.nan]\nreplacement_value = 'others'\n\nfor column in columns_to_process:\n    df_train[column].replace(values_to_replace, replacement_value, inplace=True)\n\nfor column in columns_to_process:\n    df_test[column].replace(values_to_replace, replacement_value, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:34.477967Z","iopub.execute_input":"2024-02-03T23:41:34.478262Z","iopub.status.idle":"2024-02-03T23:41:35.299614Z","shell.execute_reply.started":"2024-02-03T23:41:34.478233Z","shell.execute_reply":"2024-02-03T23:41:35.298778Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train['trafficSource_adwordsClickInfo.adNetworkType'].unique()\n","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:35.300423Z","iopub.execute_input":"2024-02-03T23:41:35.300672Z","iopub.status.idle":"2024-02-03T23:41:35.360844Z","shell.execute_reply.started":"2024-02-03T23:41:35.300650Z","shell.execute_reply":"2024-02-03T23:41:35.358748Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Find the mismatched columns and the target variable\ntrain_columns = set(df_train.columns)\ntest_columns = set(df_test.columns)\nmismatched_columns = train_columns.symmetric_difference(test_columns)\n\nprint(\"Columns that don't match:\")\nprint(mismatched_columns)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:35.363784Z","iopub.execute_input":"2024-02-03T23:41:35.364210Z","iopub.status.idle":"2024-02-03T23:41:35.371977Z","shell.execute_reply.started":"2024-02-03T23:41:35.364176Z","shell.execute_reply":"2024-02-03T23:41:35.370335Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 3. 'channelGrouping' & 'device' columns","metadata":{}},{"cell_type":"code","source":"device_columns = ['channelGrouping', 'device_browser', 'device_operatingSystem', \n                  'device_isMobile', 'device_deviceCategory']\n\nfig, ax = plt.subplots(figsize=(15, 8))\n\nfor i, col in enumerate(device_columns, 1):\n    plt.subplot(2, 3, i)\n    \n    ax = sns.countplot(y=df_train[col], order=df_train[col].value_counts()[:10].index, )\n    plt.title(f'Count of {col}')\n\n    total = len(df_train[col])\n\n    for c in ax.containers:\n        percentages = [f'{(v / total) * 100:.2f}%' for v in c.datavalues]\n        ax.bar_label(c, labels=percentages, fmt='%s', padding=1)\n\nplt.tight_layout()\nplt.show()\n\n# Explicitly close the figure to remove overlapping axes\nplt.close(fig)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:35.374153Z","iopub.execute_input":"2024-02-03T23:41:35.374836Z","iopub.status.idle":"2024-02-03T23:41:38.114289Z","shell.execute_reply.started":"2024-02-03T23:41:35.374760Z","shell.execute_reply":"2024-02-03T23:41:38.113471Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.1 Channels","metadata":{}},{"cell_type":"code","source":"# Define the aggregation functions for each column\naggregations = {'fullVisitorId': 'count',\n                'totals_pageviews': 'sum',\n                'totals_transactionRevenue': 'sum'\n}","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:38.115614Z","iopub.execute_input":"2024-02-03T23:41:38.116134Z","iopub.status.idle":"2024-02-03T23:41:38.120561Z","shell.execute_reply.started":"2024-02-03T23:41:38.116099Z","shell.execute_reply":"2024-02-03T23:41:38.119573Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_channels = df_train.groupby('channelGrouping').agg(aggregations\n                                ).sort_values(by='totals_transactionRevenue', ascending=False)\n\ndf_channels","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:38.121581Z","iopub.execute_input":"2024-02-03T23:41:38.121834Z","iopub.status.idle":"2024-02-03T23:41:38.295570Z","shell.execute_reply.started":"2024-02-03T23:41:38.121810Z","shell.execute_reply":"2024-02-03T23:41:38.294598Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create subplots\nfig, axes = plt.subplots(nrows=1, ncols=3, figsize=(15, 5))\n\n# Plot each column in a separate subplot\nfor i, col in enumerate(df_channels.columns):\n    df_channels[col].sort_values().plot(kind='barh', ax=axes[i], \n                                        color=colors)\n    axes[i].set_title(col)\n    axes[i].set_xlabel('Count')\n\n    total = df_channels[col].sum()\n\n    for c in axes[i].containers:\n        percentages = [f'{(v / total) * 100:.2f}%' for v in c.datavalues]\n        axes[i].bar_label(c, labels=percentages, fmt='%s', padding=1)\n\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:38.296977Z","iopub.execute_input":"2024-02-03T23:41:38.297613Z","iopub.status.idle":"2024-02-03T23:41:39.344565Z","shell.execute_reply.started":"2024-02-03T23:41:38.297578Z","shell.execute_reply":"2024-02-03T23:41:39.343228Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":">**Observations:**\n\n- Organic Search brings about the highest number of users & pageviews, but\n- Referral brings in close to half of the revenues.\n- Direct ranks the 3rd both in number of users & pageviews, yet second only to   \n    Referral in terms of generating revenues as users know pretty much what they want.\n- Social Media is not as effective in generating revenues as in generating the traffics.\n- Paid Search has got what the advertisers have paid for: equal amount of traffics, pageviews   \n    & revenues\n- Affiliates are not that much effective compared with other channels above,","metadata":{}},{"cell_type":"markdown","source":"### 3.2 'device_browser'","metadata":{}},{"cell_type":"code","source":"df_browser = df_train.groupby('device_browser').agg(aggregations\n                ).sort_values(by='totals_transactionRevenue', ascending=False).head(10)\n\ndf_browser","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:39.346495Z","iopub.execute_input":"2024-02-03T23:41:39.346960Z","iopub.status.idle":"2024-02-03T23:41:39.512362Z","shell.execute_reply.started":"2024-02-03T23:41:39.346925Z","shell.execute_reply":"2024-02-03T23:41:39.510947Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create subplots\nfig, axes = plt.subplots(nrows=1, ncols=3, figsize=(15, 5))\n\n# Plot each column in a separate subplot\nfor i, col in enumerate(df_browser.columns):\n    df_browser[col].sort_values().plot(kind='barh', ax=axes[i], color=colors)\n    axes[i].set_title(col)\n    axes[i].set_xlabel('Count')\n\n    total = df_browser[col].sum()\n\n    for c in axes[i].containers:\n        percentages = [f'{(v / total) * 100:.2f}%' for v in c.datavalues]\n        axes[i].bar_label(c, labels=percentages, fmt='%s', padding=1)\n\nplt.suptitle('Browsers')\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:39.513853Z","iopub.execute_input":"2024-02-03T23:41:39.514237Z","iopub.status.idle":"2024-02-03T23:41:40.478501Z","shell.execute_reply.started":"2024-02-03T23:41:39.514198Z","shell.execute_reply":"2024-02-03T23:41:40.477537Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":">**Observations:**\n   \n- Not suprising, Chrome is the powerhouse in all 3 fields, especially in    \n    generating the revenues, close to 90 percent of the market share.\n- Surprisingly, both Safari & Firefox outperforms Internet Explorer & Edge    \n    in all 3 fields.\n","metadata":{}},{"cell_type":"markdown","source":"### 3.3 'device_operatingSystem'","metadata":{}},{"cell_type":"code","source":"df_os = df_train.groupby('device_operatingSystem').agg(aggregations\n                ).sort_values(by='totals_transactionRevenue', ascending=False).head(10)\n\ndf_os","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:40.479642Z","iopub.execute_input":"2024-02-03T23:41:40.479904Z","iopub.status.idle":"2024-02-03T23:41:40.642876Z","shell.execute_reply.started":"2024-02-03T23:41:40.479882Z","shell.execute_reply":"2024-02-03T23:41:40.641922Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create subplots\nfig, axes = plt.subplots(nrows=1, ncols=3, figsize=(15, 5))\n\n# Plot each column in a separate subplot\nfor i, col in enumerate(df_os.columns):\n    df_os[col].sort_values().plot(kind='barh', ax=axes[i], color=colors)\n    axes[i].set_title(col)\n    axes[i].set_xlabel('Count')\n\n    total = df_os[col].sum()\n\n    for c in axes[i].containers:\n        percentages = [f'{(v / total) * 100:.2f}%' for v in c.datavalues]\n        axes[i].bar_label(c, labels=percentages, fmt='%s', padding=1)\n\nplt.suptitle('Operating Systems')\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:40.644244Z","iopub.execute_input":"2024-02-03T23:41:40.644511Z","iopub.status.idle":"2024-02-03T23:41:41.582492Z","shell.execute_reply.started":"2024-02-03T23:41:40.644488Z","shell.execute_reply":"2024-02-03T23:41:41.581232Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.4 'device_isMobile'","metadata":{}},{"cell_type":"code","source":"df_mobile = df_train.groupby('device_isMobile').agg(aggregations\n                ).sort_values(by='totals_transactionRevenue', ascending=False)\n\ndf_mobile","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:41.583675Z","iopub.execute_input":"2024-02-03T23:41:41.583960Z","iopub.status.idle":"2024-02-03T23:41:41.695744Z","shell.execute_reply.started":"2024-02-03T23:41:41.583938Z","shell.execute_reply":"2024-02-03T23:41:41.694825Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create subplots\nfig, axes = plt.subplots(nrows=1, ncols=3, figsize=(15, 5))\n\n# Flatten the 2D array of axes\naxes = axes.flatten()\n\n# Plot each column in a separate subplot\nfor i, col in enumerate(df_mobile.columns):\n    df_mobile[col].sort_values().plot(kind='barh', ax=axes[i], color=colors)\n    axes[i].set_title(col)\n    axes[i].set_xlabel('Count')\n\n    total = df_mobile[col].sum()\n\n    for c in axes[i].containers:\n        percentages = [f'{(v / total) * 100:.2f}%' for v in c.datavalues]\n        axes[i].bar_label(c, labels=percentages, fmt='%s', padding=1)\n\nplt.suptitle('Mobile vs Non-Mobile')\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:41.696887Z","iopub.execute_input":"2024-02-03T23:41:41.697165Z","iopub.status.idle":"2024-02-03T23:41:42.435041Z","shell.execute_reply.started":"2024-02-03T23:41:41.697142Z","shell.execute_reply":"2024-02-03T23:41:42.433843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3.5 'device.deviceCategory'","metadata":{}},{"cell_type":"code","source":"df_category = df_train.groupby('device_deviceCategory').agg(aggregations\n                ).sort_values(by='totals_transactionRevenue', ascending=False).head(10)\n\ndf_category","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:42.436830Z","iopub.execute_input":"2024-02-03T23:41:42.437216Z","iopub.status.idle":"2024-02-03T23:41:42.604544Z","shell.execute_reply.started":"2024-02-03T23:41:42.437178Z","shell.execute_reply":"2024-02-03T23:41:42.603063Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create subplots\nfig, axes = plt.subplots(nrows=1, ncols=3, figsize=(15, 5))\n\n# Plot each column in a separate subplot\nfor i, col in enumerate(df_category.columns):\n    df_category[col].sort_values().plot(kind='barh', ax=axes[i], color=colors)\n    axes[i].set_title(col)\n    axes[i].set_xlabel('Count')\n\n    total = df_category[col].sum()\n\n    for c in axes[i].containers:\n        percentages = [f'{(v / total) * 100:.2f}%' for v in c.datavalues]\n        axes[i].bar_label(c, labels=percentages, fmt='%s', padding=1)\n\nplt.suptitle('Device Category')\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:42.606093Z","iopub.execute_input":"2024-02-03T23:41:42.606405Z","iopub.status.idle":"2024-02-03T23:41:43.359973Z","shell.execute_reply.started":"2024-02-03T23:41:42.606380Z","shell.execute_reply":"2024-02-03T23:41:43.358694Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":">**Observations:**\n\ndesktop & non-mobile devices\n- still dominate in bring in traffics, pageviews & revenues","metadata":{}},{"cell_type":"markdown","source":"## 4. 'geoNetwork' columns","metadata":{}},{"cell_type":"code","source":"geo_columns = ['geoNetwork_continent', 'geoNetwork_subContinent', 'geoNetwork_country', \n               'geoNetwork_region', 'geoNetwork_metro', 'geoNetwork_city', 'geoNetwork_networkDomain']\n\nfig, ax = plt.subplots(figsize=(20, 8))\n\nfor i, col in enumerate(geo_columns, 1):\n    plt.subplot(2, 4, i)\n    \n    ax = sns.countplot(y=train[col], order=train[col].value_counts()[:10].index,  )\n    plt.title(f'Count of {col}')    \n    \n    total = len(train[col])\n\n    for c in ax.containers:\n        percentages = [f'{(v / total) * 100:.2f}%' for v in c.datavalues]\n        ax.bar_label(c, labels=percentages, fmt='%s', padding=1)\n\nplt.tight_layout()\nplt.show()\n\n# Explicitly close the figure to remove overlapping axes\nplt.close(fig)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:43.361619Z","iopub.execute_input":"2024-02-03T23:41:43.362827Z","iopub.status.idle":"2024-02-03T23:41:48.187379Z","shell.execute_reply.started":"2024-02-03T23:41:43.362790Z","shell.execute_reply":"2024-02-03T23:41:48.186081Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 4.1 Global Analysis","metadata":{}},{"cell_type":"code","source":"df_country = df_train.groupby('geoNetwork_country').agg(aggregations\n                    ).sort_values(by=['totals_transactionRevenue'], \n                                  ascending=False).reset_index()\ndf_country.head(10)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:48.188957Z","iopub.execute_input":"2024-02-03T23:41:48.189329Z","iopub.status.idle":"2024-02-03T23:41:48.368902Z","shell.execute_reply.started":"2024-02-03T23:41:48.189300Z","shell.execute_reply":"2024-02-03T23:41:48.367791Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create basic choropleth map\nfig = px.choropleth(df_country, locationmode='country names', locations='geoNetwork_country', \n                    color=df_country['totals_transactionRevenue'], color_continuous_scale='Mint', \n                    hover_name='geoNetwork_country',  hover_data={'totals_transactionRevenue':':,.0f'},\n                    title='Revenue by Country')\n\nfig.update_layout(width=800, height=400, autosize=False, \n                  margin=dict(l=30, r=30, t=50, b=50, pad=10))\n\nfig.show(renderer='notebook')\n# fig.write_html(\"interactive_map.html\")","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:48.370735Z","iopub.execute_input":"2024-02-03T23:41:48.371559Z","iopub.status.idle":"2024-02-03T23:41:48.889138Z","shell.execute_reply.started":"2024-02-03T23:41:48.371497Z","shell.execute_reply":"2024-02-03T23:41:48.888205Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 4.2 US Analysis","metadata":{}},{"cell_type":"code","source":"df_us = df_train[df_train['geoNetwork_country'] == 'United States'].reset_index(drop=True)\ndf_us","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:48.890239Z","iopub.execute_input":"2024-02-03T23:41:48.890496Z","iopub.status.idle":"2024-02-03T23:41:49.104166Z","shell.execute_reply.started":"2024-02-03T23:41:48.890474Z","shell.execute_reply":"2024-02-03T23:41:49.103286Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Add a column with state abbreviations\nstate_abbreviations = {'Alabama': 'AL', 'Alaska': 'AK', 'Arizona': 'AZ', 'Arkansas': 'AR', 'California': 'CA',\n                       'Colorado': 'CO', 'Connecticut': 'CT', 'Delaware': 'DE', 'Florida': 'FL', 'Georgia': 'GA',\n                       'Hawaii': 'HI', 'Idaho': 'ID', 'Illinois': 'IL', 'Indiana': 'IN', 'Iowa': 'IA',\n                       'Kansas': 'KS', 'Kentucky': 'KY', 'Louisiana': 'LA', 'Maine': 'ME', 'Maryland': 'MD',\n                       'Massachusetts': 'MA', 'Michigan': 'MI', 'Minnesota': 'MN', 'Mississippi': 'MS',\n                       'Missouri': 'MO', 'Montana': 'MT', 'Nebraska': 'NE', 'Nevada': 'NV', 'New Hampshire': 'NH',\n                       'New Jersey': 'NJ', 'New Mexico': 'NM', 'New York': 'NY', 'North Carolina': 'NC',\n                       'North Dakota': 'ND', 'Ohio': 'OH', 'Oklahoma': 'OK', 'Oregon': 'OR', 'Pennsylvania': 'PA',\n                       'Rhode Island': 'RI', 'South Carolina': 'SC', 'South Dakota': 'SD', 'Tennessee': 'TN',\n                       'Texas': 'TX', 'Utah': 'UT', 'Vermont': 'VT', 'Virginia': 'VA', 'Washington': 'WA',\n                       'West Virginia': 'WV', 'Wisconsin': 'WI', 'Wyoming': 'WY', 'District of Columbia': 'DC' }\n\ndf_us['stateCode'] = df_us['geoNetwork_region'].map(state_abbreviations)\ndf_us.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:49.105212Z","iopub.execute_input":"2024-02-03T23:41:49.105450Z","iopub.status.idle":"2024-02-03T23:41:49.155558Z","shell.execute_reply.started":"2024-02-03T23:41:49.105429Z","shell.execute_reply":"2024-02-03T23:41:49.154615Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_states = df_us.groupby(['geoNetwork_region', 'stateCode']).agg(aggregations).sort_values(\n                                            by='totals_transactionRevenue', ascending=False).reset_index()\ndf_states.head(10)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:49.156618Z","iopub.execute_input":"2024-02-03T23:41:49.156964Z","iopub.status.idle":"2024-02-03T23:41:49.284714Z","shell.execute_reply.started":"2024-02-03T23:41:49.156936Z","shell.execute_reply":"2024-02-03T23:41:49.283608Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create basic choropleth map\nfig = px.choropleth(df_states, locationmode='USA-states', locations='stateCode',\n                    color=df_states['totals_transactionRevenue'], hover_name='geoNetwork_region', \n                    hover_data={'totals_transactionRevenue':':,.0f'}, \n                    title='Revenue by State', color_continuous_scale='Mint')\n                  \nfig.update_layout(width=800, height=400, autosize=False, geo_scope='usa',\n                  margin=dict(l=30, r=30, t=50, b=50, pad=10))\n\n# For interactive display in a Jupyter Notebook:\nfig.show(renderer='notebook')\n# fig.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:49.286190Z","iopub.execute_input":"2024-02-03T23:41:49.286518Z","iopub.status.idle":"2024-02-03T23:41:49.382097Z","shell.execute_reply.started":"2024-02-03T23:41:49.286487Z","shell.execute_reply":"2024-02-03T23:41:49.380773Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 5. 'totals' Columns","metadata":{}},{"cell_type":"code","source":"totals_columns = ['totals_hits', 'totals_pageviews', 'totals_transactionRevenue']\n\nfig, ax = plt.subplots(nrows=2, ncols=3, figsize=(12, 6))\n\nfor i, col in enumerate(totals_columns):\n    plt.subplot(2, 3, i + 1)\n    df_train[col].hist(bins=20, color=colors[i])\n    plt.title(f'Distribution of {col}')\n\n    plt.subplot(2, 3, i + 4)\n    df_train[col].hist(bins=20, log=True, color=colors[i])\n    plt.title(f'Distribution of {col} (log-scaled)')\n    \nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:49.383793Z","iopub.execute_input":"2024-02-03T23:41:49.384121Z","iopub.status.idle":"2024-02-03T23:41:51.321936Z","shell.execute_reply.started":"2024-02-03T23:41:49.384097Z","shell.execute_reply":"2024-02-03T23:41:51.320618Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 6. 'trafficSource' Columns","metadata":{}},{"cell_type":"code","source":"traffic_columns = ['trafficSource_campaign', 'trafficSource_source', 'trafficSource_medium', \n                   'trafficSource_keyword', 'trafficSource_referralPath', \n                   'trafficSource_adwordsClickInfo.page', 'trafficSource_adwordsClickInfo.slot', \n                   'trafficSource_adwordsClickInfo.adNetworkType', 'trafficSource_adContent']\n\nfig, ax = plt.subplots(figsize=(18, 12))\n\nfor i, col in enumerate(traffic_columns, 1):\n    plt.subplot(3, 3, i)\n    \n    # Exclude 'others' & '(not set)' from the specified column\n    filtered_df = train[(train[col] != 'others') & \n                        (train[col] != '(not provided)') & \n                        (train[col] != '(not set)')]\n    \n    ax = sns.countplot(y=filtered_df[col], order=filtered_df[col].value_counts()[:7].index)\n    plt.title(f'Count of {col}')\n\n    total = len(train[col])\n\n    for c in ax.containers:\n        percentages = [f'{(v / total) * 100:.2f}%' for v in c.datavalues]\n        ax.bar_label(c, labels=percentages, fmt='%s', padding=1)\n\nplt.tight_layout()\nplt.show()\n\n# Explicitly close the figure to remove overlapping axes\nplt.close(fig)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:51.323445Z","iopub.execute_input":"2024-02-03T23:41:51.323825Z","iopub.status.idle":"2024-02-03T23:41:58.628654Z","shell.execute_reply.started":"2024-02-03T23:41:51.323795Z","shell.execute_reply":"2024-02-03T23:41:58.627172Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In this section, we have excluded 'others' & '(not set)' from the specified columns for they comprise    \nan overwhelming large part of the data in this section.\n\nWe are only interested in the foloowing features:   \n\n- 'trafficSource.source'    \n- 'trafficSource.medium'   ","metadata":{}},{"cell_type":"markdown","source":"### 6.1 'trafficSource.source'\n\n- The source of the traffic source. Could be the name of the search engine, etc.\n- Top 10 sources are:   \n    'google', 'youtube.com', '(direct)', 'mall.googleplex.com', 'Partners',   \n    'analytics.google.com', 'dfa', 'google.com', 'm.facebook.com', 'baidu'.      \n    google: 44% of the trafficsource source","metadata":{}},{"cell_type":"code","source":"df_source = df_train.groupby('trafficSource_source').agg(aggregations\n                ).sort_values(by='totals_transactionRevenue', ascending=False).head(10)\n\ndf_source","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:58.630077Z","iopub.execute_input":"2024-02-03T23:41:58.630499Z","iopub.status.idle":"2024-02-03T23:41:58.800612Z","shell.execute_reply.started":"2024-02-03T23:41:58.630467Z","shell.execute_reply":"2024-02-03T23:41:58.799365Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create subplots\nfig, axes = plt.subplots(nrows=1, ncols=3, figsize=(15, 5))\n\n# Plot each column in a separate subplot\nfor i, col in enumerate(df_source.columns):\n    df_source[col].sort_values().plot(kind='barh', ax=axes[i], color=colors)\n    axes[i].set_title(col)\n    axes[i].set_xlabel('Count')\n\n    total = df_source[col].sum()\n\n    for c in axes[i].containers:\n        percentages = [f'{(v / total) * 100:.2f}%' for v in c.datavalues]\n        axes[i].bar_label(c, labels=percentages, fmt='%s', padding=1)\n\nplt.suptitle('TrafficSource Source')\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:58.802870Z","iopub.execute_input":"2024-02-03T23:41:58.803312Z","iopub.status.idle":"2024-02-03T23:41:59.746732Z","shell.execute_reply.started":"2024-02-03T23:41:58.803275Z","shell.execute_reply":"2024-02-03T23:41:59.745843Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":">**Observations:**\n\n'trafficSource_source'    \n    \n- 'google' brings in most of the users & pageviews, while 'mall.googleplex.com' generates most of the revenues.\n- '(direct)' ranks second in all 3 fields. No wonder again, users know what they want & type the urls in the browser.\n- 'dfa': DFA source refers to DoubleClick For Advertisers, which is the old brand name for Google Marketing Platform. ","metadata":{}},{"cell_type":"markdown","source":"### 6.2 'trafficSource_medium'\n\n- The medium of the traffic source. \"organic\", \"cpc\", \"referral\", etc,   \n- Top 6 sources are:   \n    'organic', 'referral', '(none)', 'cpc', 'affiliate', 'cpm', etc.   \n    organic: 42% of the traffucesource medium","metadata":{}},{"cell_type":"code","source":"df_medium = df_train.groupby('trafficSource_medium').agg(aggregations\n                ).sort_values(by='totals_transactionRevenue', ascending=False).head(10)\n\ndf_medium","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:59.748024Z","iopub.execute_input":"2024-02-03T23:41:59.748587Z","iopub.status.idle":"2024-02-03T23:41:59.913483Z","shell.execute_reply.started":"2024-02-03T23:41:59.748555Z","shell.execute_reply":"2024-02-03T23:41:59.912433Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create subplots\nfig, axes = plt.subplots(nrows=1, ncols=3, figsize=(15, 5))\n\n# Plot each column in a separate subplot\nfor i, col in enumerate(df_medium.columns):\n    df_medium[col].sort_values().plot(kind='barh', ax=axes[i], color=colors)\n    axes[i].set_title(col)\n    axes[i].set_xlabel('Count')\n\n    total = df_medium[col].sum()\n\n    for c in axes[i].containers:\n        percentages = [f'{(v / total) * 100:.2f}%' for v in c.datavalues]\n        axes[i].bar_label(c, labels=percentages, fmt='%s', padding=1)\n\nplt.suptitle('TrafficSource Medium')\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:41:59.914362Z","iopub.execute_input":"2024-02-03T23:41:59.914618Z","iopub.status.idle":"2024-02-03T23:42:00.810589Z","shell.execute_reply.started":"2024-02-03T23:41:59.914597Z","shell.execute_reply":"2024-02-03T23:42:00.808369Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":">**Observations:**\n\n- \"organic\" bring about most of the sessions & pageviews, it is   \n    \"referral\" that generates most of the revenues. \n- \"cpc\": Cost Per Click. Plays an important role in all 3 fields.\n- '(none)': means Analytics doesn't have any referral information for these users.","metadata":{}},{"cell_type":"markdown","source":"#### 6.3 Source & Medium","metadata":{}},{"cell_type":"code","source":"df_source_medium = df_train.groupby(['trafficSource_source', 'trafficSource_medium']).agg(aggregations)\n\n# df_source_medium.sort_values(by='revenue', ascending=False).head(10)\ndf_source_medium","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:00.812056Z","iopub.execute_input":"2024-02-03T23:42:00.812397Z","iopub.status.idle":"2024-02-03T23:42:01.069430Z","shell.execute_reply.started":"2024-02-03T23:42:00.812373Z","shell.execute_reply":"2024-02-03T23:42:01.068002Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create subplots\nfig, axes = plt.subplots(nrows=1, ncols=3, figsize=(15, 5))\n# Flatten the 2D array of axes\n# axes = axes.flatten()\n\n# Plot each column in a separate subplot\nfor i, col in enumerate(df_source_medium.columns):\n    df_source_medium[col].sort_values(ascending=False)[:10].plot(kind='barh', ax=axes[i], color=colors)\n    axes[i].set_title(col)\n    axes[i].set_xlabel('Count')\n\n    total = df_source_medium[col].sum()\n\n    for c in axes[i].containers:\n        percentages = [f'{(v / total) * 100:.2f}%' for v in c.datavalues]\n        axes[i].bar_label(c, labels=percentages, fmt='%s', padding=1)\n\nplt.suptitle('TrafficSource Source & Medium')\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:01.070807Z","iopub.execute_input":"2024-02-03T23:42:01.071191Z","iopub.status.idle":"2024-02-03T23:42:02.341451Z","shell.execute_reply.started":"2024-02-03T23:42:01.071164Z","shell.execute_reply":"2024-02-03T23:42:02.340482Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":">**Observations:**\n\n- 'google organic' generates most of the traffics, the rererrals from    \n    'mall.googleplex.com' generates most of the revenue.\n- 'direct none': again, Analytics does not have any information about users,   \n    yet are dominant in all 3 fields on average after 'google organic'.","metadata":{}},{"cell_type":"markdown","source":"## 7. Revenue = 0 vs Revenue > 0","metadata":{}},{"cell_type":"code","source":"# revenue = 0 transactions\ndf_revenue_zero = df_train[df_train['totals_transactionRevenue'] == 0]\ndf_revenue_zero.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:02.342757Z","iopub.execute_input":"2024-02-03T23:42:02.343821Z","iopub.status.idle":"2024-02-03T23:42:02.549501Z","shell.execute_reply.started":"2024-02-03T23:42:02.343781Z","shell.execute_reply":"2024-02-03T23:42:02.548388Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# revenue > 0 transactions\ndf_revenue_nonzero = df_train[df_train['totals_transactionRevenue'] > 0].reset_index(drop=True)\ndf_revenue_nonzero.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:02.550687Z","iopub.execute_input":"2024-02-03T23:42:02.550992Z","iopub.status.idle":"2024-02-03T23:42:02.581665Z","shell.execute_reply.started":"2024-02-03T23:42:02.550968Z","shell.execute_reply":"2024-02-03T23:42:02.580576Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"non_zero_transact_pct = len(df_revenue_nonzero) / len(df_train) * 100\nprint(f'Non-Zero Transaction Percentage: {non_zero_transact_pct:.02f}%')\n\nzero_transact_pct = len(df_revenue_zero) / len(df_train) * 100\nprint(f'Zero Transaction Percentage: {zero_transact_pct:.02f}%')","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:02.584708Z","iopub.execute_input":"2024-02-03T23:42:02.585186Z","iopub.status.idle":"2024-02-03T23:42:02.591887Z","shell.execute_reply.started":"2024-02-03T23:42:02.585151Z","shell.execute_reply":"2024-02-03T23:42:02.590670Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":">***Observations:***\n\n- 98.73% of the traffics (sessions, pageviews) does not generate revenues at all.","metadata":{}},{"cell_type":"code","source":"# Filter the DataFrame for zero revenue\n# df_revenue_zero = df_train[df_train['totals.transactionRevenue'] == 0]\n\n# List of columns to loop through\ncolumns_to_loop = ['channelGrouping', 'trafficSource_source', 'trafficSource_medium']\n\n# Create subplots\nfig, axs = plt.subplots(1, 3, figsize=(15, 5))\n\n# Loop through each column and subplot\nfor i, column in enumerate(columns_to_loop):\n    # Create the aggregated DataFrame\n    df_column_revenue_zero = df_revenue_zero.groupby(column).agg(\n        count=(column, 'count') ).sort_values(by='count', ascending=False).head(10)\n\n    # Calculate the percentage\n    total_count = df_column_revenue_zero['count'].sum()\n    df_column_revenue_zero['percentage'] = (df_column_revenue_zero['count'] / total_count) * 100\n\n    # Plot the horizontal bar chart in the current subplot\n    bar_chart = df_column_revenue_zero['count'].plot(kind='barh', ax=axs[i], color=colors)\n\n    # Add labels and title\n    axs[i].set_xlabel(f'Count of Zero Transactions ({column})')\n    axs[i].set_ylabel(column)\n    axs[i].set_title(f'Top 10 {column} with Zero Transactions')\n\n    # Display percentages on the right side of the bars\n    for j, patch in enumerate(bar_chart.patches):\n        width = patch.get_width()\n        percentage = df_column_revenue_zero['percentage'].iloc[j]\n        axs[i].text(width + 1000, patch.get_y() + patch.get_height() / 2,\n                    f'{percentage:.2f}%', ha='left', va='center', fontsize=10)\n\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:02.593312Z","iopub.execute_input":"2024-02-03T23:42:02.593652Z","iopub.status.idle":"2024-02-03T23:42:03.736716Z","shell.execute_reply.started":"2024-02-03T23:42:02.593625Z","shell.execute_reply":"2024-02-03T23:42:03.735448Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Filter the DataFrame for non-zero revenue\n# df_revenue_nonzero = df_train[df_train['totals_transactionRevenue'] > 0]\n\n# List of columns to loop through\ncolumns_to_loop = ['channelGrouping', 'trafficSource_source', 'trafficSource_medium']\n\n# Plot the bar chart\nfig, ax = plt.subplots(3, 1, figsize=(10, 9))\n\n# Loop through each column\nfor i, col in enumerate(columns_to_loop):\n    # Create the aggregated DataFrame\n    df_column_revenue_nonzero = df_revenue_nonzero.groupby(col).agg(\n        count=(col, 'count')\n    ).sort_values(by='count', ascending=False).head(10)\n\n    # Calculate the percentage\n    total_count = df_column_revenue_nonzero['count'].sum()\n    df_column_revenue_nonzero['percentage'] = (df_column_revenue_nonzero['count'] / total_count) * 100\n\n    bar_chart = df_column_revenue_nonzero['count'].plot(kind='bar', rot=0, ax=ax[i], color=colors)\n\n    # Add labels and title\n    ax[i].set_ylabel(f'Count of Non-Zero Transactions ({col})')\n    ax[i].set_xlabel(col)\n    ax[i].set_title(f'Top 10 {col} with Non-Zero Transactions')\n\n    # Display percentages on top of the bars\n    for j, patch in enumerate(bar_chart.patches):\n        height = patch.get_height()\n        percentage = df_column_revenue_nonzero['percentage'].iloc[j]\n        ax[i].text(patch.get_x() + patch.get_width() / 2,\n                height + 1000, f'{percentage:.2f}%', ha='center', va='bottom', fontsize=10)\n\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:03.738515Z","iopub.execute_input":"2024-02-03T23:42:03.738839Z","iopub.status.idle":"2024-02-03T23:42:04.623415Z","shell.execute_reply.started":"2024-02-03T23:42:03.738815Z","shell.execute_reply":"2024-02-03T23:42:04.622114Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Filter the DataFrame for zero revenue\n# df_revenue_zero = df_train[df_train['totals_transactionRevenue'] == 0]\n\n# List of columns to loop through\ncolumns_to_loop = ['channelGrouping', 'trafficSource_source', 'trafficSource_medium']\n\n# Loop through each column\nfor col in columns_to_loop:\n    # Create the aggregated DataFrame\n    df_column_revenue_zero = df_revenue_zero.groupby(col).agg(\n        count=(col, 'count')\n    ).sort_values(by='count', ascending=False).head(10)\n\n    # Calculate the percentage\n    total_count = df_column_revenue_zero['count'].sum()\n    df_column_revenue_zero['percentage'] = (df_column_revenue_zero['count'] / total_count) * 100\n\n    # Plot the bar chart\n    fig, ax = plt.subplots(figsize=(12, 4))\n    # Flatten the 2D array of axes\n    # axes = axes.flatten()\n\n    bar_chart = df_column_revenue_zero['count'].plot(kind='bar', rot=0, ax=ax, color=colors)\n\n    # Add labels and title\n    ax.set_ylabel(f'Count of Zero Transactions ({col})')\n    ax.set_xlabel(col)\n    ax.set_title(f'Top 10 {col} with Zero Transactions')\n\n    # Display percentages on top of the bars\n    for i, patch in enumerate(bar_chart.patches):\n        height = patch.get_height()\n        percentage = df_column_revenue_zero['percentage'].iloc[i]\n        ax.text(patch.get_x() + patch.get_width() / 2,\n                height + 1000, f'{percentage:.2f}%', ha='center', va='bottom', fontsize=12)\n\n    plt.tight_layout()\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:04.624982Z","iopub.execute_input":"2024-02-03T23:42:04.625306Z","iopub.status.idle":"2024-02-03T23:42:05.875469Z","shell.execute_reply.started":"2024-02-03T23:42:04.625277Z","shell.execute_reply":"2024-02-03T23:42:05.874182Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":">**Observations:**\n\n- Most of the zero-revenue transacations are from 'organic search' or 'search google'\n- while most of the non-zero transactions are from 'mall.googleplex.com' & 'referral',    \n    '(direct)' plays an important role in bringing about traffics & revenues.","metadata":{}},{"cell_type":"markdown","source":"## 8. User & Session Analysis","metadata":{}},{"cell_type":"markdown","source":"### 8.1 User Analysis","metadata":{}},{"cell_type":"code","source":"df_visitors = df_train.groupby('fullVisitorId').agg(aggregations).sort_values(\n                    by='totals_transactionRevenue', ascending=False )\n\ndf_visitors.drop('fullVisitorId', axis=1).head(10)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:05.877088Z","iopub.execute_input":"2024-02-03T23:42:05.877415Z","iopub.status.idle":"2024-02-03T23:42:08.669676Z","shell.execute_reply.started":"2024-02-03T23:42:05.877386Z","shell.execute_reply":"2024-02-03T23:42:08.668482Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Users with non-zero revenue transactions\ndf_users_revenue = df_revenue_nonzero.groupby('fullVisitorId').agg(aggregations\n                ).sort_values(by='totals_transactionRevenue', ascending=False)\n\ndf_users_revenue.drop('fullVisitorId', axis=1)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:08.670925Z","iopub.execute_input":"2024-02-03T23:42:08.671169Z","iopub.status.idle":"2024-02-03T23:42:08.709169Z","shell.execute_reply.started":"2024-02-03T23:42:08.671148Z","shell.execute_reply":"2024-02-03T23:42:08.707934Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Distribution of non-zero revenue transactions\nnon_zero = df_train[df_train['totals_transactionRevenue'] > 0] \\\n                 ['totals_transactionRevenue'].reset_index(drop=True)\n    \nfig, ax = plt.subplots(figsize=(10, 5))\n\nsns.histplot(non_zero, kde=True, bins=30, log_scale=True)\nticks_x = ticker.FuncFormatter(lambda x, pos: '{:,.0f}'.format(x/1000))\nax.xaxis.set_major_formatter(ticks_x)\n\nplt.title('Distribution of Non-Zero Revenue Transactions')\nplt.xlabel('Transaction Revenue in Thousands')\n\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:08.710630Z","iopub.execute_input":"2024-02-03T23:42:08.710981Z","iopub.status.idle":"2024-02-03T23:42:09.640020Z","shell.execute_reply.started":"2024-02-03T23:42:08.710954Z","shell.execute_reply":"2024-02-03T23:42:09.638587Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Users with zero revenue transactions\ndf_user_revenue_zero = df_revenue_zero.groupby('fullVisitorId').agg(aggregations\n                ).sort_values(by='totals_transactionRevenue', ascending=False)\n\ndf_user_revenue_zero.drop('fullVisitorId', axis=1)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:09.641405Z","iopub.execute_input":"2024-02-03T23:42:09.641800Z","iopub.status.idle":"2024-02-03T23:42:12.122794Z","shell.execute_reply.started":"2024-02-03T23:42:09.641767Z","shell.execute_reply":"2024-02-03T23:42:12.122080Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_user_revenue_zero_pct = round(len(df_user_revenue_zero['totals_transactionRevenue']) / \n                                 len(df_train['totals_transactionRevenue']) * 100, 2)\nprint(f'Percentage of users witn zero-revenue trasactions:\\n {df_user_revenue_zero_pct}%')","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:12.123906Z","iopub.execute_input":"2024-02-03T23:42:12.124672Z","iopub.status.idle":"2024-02-03T23:42:12.131403Z","shell.execute_reply.started":"2024-02-03T23:42:12.124646Z","shell.execute_reply":"2024-02-03T23:42:12.129886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 8.2 Sessions","metadata":{}},{"cell_type":"code","source":"# Non-zero revenue sessions\ndf_session_revenue = df_revenue_nonzero.groupby('sessionId').agg(\n                count=('sessionId', 'count'),\n                pageviews=('totals_pageviews', 'sum'),\n                revenue=('totals_transactionRevenue', 'sum')\n                ).sort_values(by='revenue', ascending=False)\n\ndf_session_revenue","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:12.132884Z","iopub.execute_input":"2024-02-03T23:42:12.133304Z","iopub.status.idle":"2024-02-03T23:42:12.178982Z","shell.execute_reply.started":"2024-02-03T23:42:12.133265Z","shell.execute_reply":"2024-02-03T23:42:12.177335Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Sessions with zero revenue transactions\ndf_session_revenue_zero = df_revenue_zero.groupby('sessionId').agg(\n                sessions=('sessionId', 'count'),\n                pageviews=('totals_pageviews', 'sum'),\n                revenue=('totals_transactionRevenue', 'sum')\n                ).sort_values(by='revenue', ascending=False)\n\ndf_session_revenue_zero","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:12.180474Z","iopub.execute_input":"2024-02-03T23:42:12.180914Z","iopub.status.idle":"2024-02-03T23:42:15.842554Z","shell.execute_reply.started":"2024-02-03T23:42:12.180877Z","shell.execute_reply":"2024-02-03T23:42:15.841597Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_session_revenue_zero_pct = round(len(df_session_revenue_zero['revenue']) / \n                                    len(df_train['totals_transactionRevenue']) * 100, 2)\n\nprint(f'Percentage of sessions witn zero-revenue trasactions:\\n {df_session_revenue_zero_pct}%')","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:15.843941Z","iopub.execute_input":"2024-02-03T23:42:15.844244Z","iopub.status.idle":"2024-02-03T23:42:15.851595Z","shell.execute_reply.started":"2024-02-03T23:42:15.844218Z","shell.execute_reply":"2024-02-03T23:42:15.850111Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 9. Time Analysis","metadata":{}},{"cell_type":"markdown","source":"### 9.1 Extract 'Year', 'Month', 'Month_Num' & 'Day' from 'date' column","metadata":{}},{"cell_type":"code","source":"# Extract 'Year', 'Month', 'Month_Num' & 'Day' from 'date'\ndef create_features(data):\n    \"\"\"\n    Create time series features based on time series index.\n    \"\"\"\n\n    # data = data.copy()\n    \n    # Convert 'date' column to datetime series \n    data['date'] = pd.to_datetime(data['date'], format='%Y%m%d')\n\n    # Extract 'Year', 'Month', 'Month_Num' & 'Day' from 'Order Date'\n    data['Year'] = data.date.dt.year\n    data['Month'] = data.date.dt.strftime('%b') # abbrev month name\n    data['Month_Num'] = data.date.dt.month\n    data['Day'] = data.date.dt.day\n\n    # Extract 'Weekday' & 'Week Day Num'\n    data['Weekday'] = data['date'].dt.strftime('%a')\n    data['Weekday_Num'] = data.date.dt.day_of_week + 1 # Week start on Monday as 0, so + 1\n\n    return data","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:15.853095Z","iopub.execute_input":"2024-02-03T23:42:15.853404Z","iopub.status.idle":"2024-02-03T23:42:15.862768Z","shell.execute_reply.started":"2024-02-03T23:42:15.853380Z","shell.execute_reply":"2024-02-03T23:42:15.861537Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = df_train.copy()\n\ncreate_features(df_train)\n\ndf_train = df_train[df_train['date'] != '2017-08-01']\ndf_train.tail()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:15.864169Z","iopub.execute_input":"2024-02-03T23:42:15.864473Z","iopub.status.idle":"2024-02-03T23:42:23.656772Z","shell.execute_reply.started":"2024-02-03T23:42:15.864449Z","shell.execute_reply":"2024-02-03T23:42:23.655542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 9.2 Daily Transactions","metadata":{}},{"cell_type":"code","source":"import matplotlib.ticker as ticker","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:23.658080Z","iopub.execute_input":"2024-02-03T23:42:23.658402Z","iopub.status.idle":"2024-02-03T23:42:23.663483Z","shell.execute_reply.started":"2024-02-03T23:42:23.658378Z","shell.execute_reply":"2024-02-03T23:42:23.661905Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_daily = df_train.groupby('date').agg(aggregations)\ndf_daily","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:23.665392Z","iopub.execute_input":"2024-02-03T23:42:23.665718Z","iopub.status.idle":"2024-02-03T23:42:23.786689Z","shell.execute_reply.started":"2024-02-03T23:42:23.665683Z","shell.execute_reply":"2024-02-03T23:42:23.785932Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(3, 1, figsize=(15, 6), sharex=True)\n\nticks_y = ticker.FuncFormatter(lambda x, pos: '{:,.0f}'.format(x/1000000))\n\nsns.lineplot(data=df_daily, x='date', y='totals_transactionRevenue', color='g', label='Revenue in Millions', ax=ax[0])\n# plt.gca(ax=ax[0]).yaxis.set_major_formatter(ticks_y)\nax[0].yaxis.set_major_formatter(ticks_y)\n\n# sns.lineplot(data=df_daily, x='date', y='totals.hits', color='b', label='Hits', ax=ax[1])\nsns.lineplot(data=df_daily, x='date', y='totals_pageviews', color='b', label='Pageviews', ax=ax[1])\n\nsns.lineplot(data=df_daily, x='date', y='fullVisitorId', color='k', label='Visitors', ax=ax[2])\n\nplt.legend()\nplt.suptitle('Daily Visitors, Pageviews & Revenue')\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:23.787740Z","iopub.execute_input":"2024-02-03T23:42:23.788519Z","iopub.status.idle":"2024-02-03T23:42:25.148789Z","shell.execute_reply.started":"2024-02-03T23:42:23.788487Z","shell.execute_reply":"2024-02-03T23:42:25.147439Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 9.3 Weekly Anslysis","metadata":{}},{"cell_type":"code","source":"df_week = df_train.groupby(['Weekday', 'Weekday_Num']).agg(aggregations)\n\ndf_week.sort_values(by='Weekday_Num', inplace=True)\ndf_week ","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:25.150467Z","iopub.execute_input":"2024-02-03T23:42:25.150951Z","iopub.status.idle":"2024-02-03T23:42:25.367017Z","shell.execute_reply.started":"2024-02-03T23:42:25.150915Z","shell.execute_reply":"2024-02-03T23:42:25.366089Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig, ax = plt.subplots(1, 3, figsize=(15, 4), sharex=False)\n\nsns.lineplot(data=df_week, x='Weekday', y='fullVisitorId', color='k', label='Visitors', ax=ax[0])\nsns.lineplot(data=df_week, x='Weekday', y='totals_pageviews', color='b', label='Pageviews', ax=ax[1])\n\nticks_y = ticker.FuncFormatter(lambda x, pos: '{:,.0f}'.format(x/1000000))\nsns.lineplot(data=df_week, x='Weekday', y='totals_transactionRevenue', color='g', label='Revenuev in Millions', ax=ax[2])\nax[2].yaxis.set_major_formatter(ticks_y)\n\nplt.legend()\nplt.suptitle('Weekly Visitors, Pageview & Revenue')\n\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:25.375920Z","iopub.execute_input":"2024-02-03T23:42:25.376872Z","iopub.status.idle":"2024-02-03T23:42:26.035273Z","shell.execute_reply.started":"2024-02-03T23:42:25.376842Z","shell.execute_reply":"2024-02-03T23:42:26.033931Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 9.4 Monthly Analysis","metadata":{}},{"cell_type":"code","source":"df_train_dates = df_train.set_index(df_train['date']).drop('date', axis=1)\n\n# months = df_train_dates['Month'].unique()\nmonths = ['Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul']\n\n# Set up subplots\nfig, axes = plt.subplots(nrows=len(months), ncols=1, \n                         figsize=(10, 3*len(months)))\n\n# fig.suptitle('Monthly Revenues in Millions')\n\n# Plot for each year\nfor i, month in enumerate(months):\n    df_monthly = df_train_dates[df_train_dates['Month'] == month]\n    df_monthly_rev = df_monthly['totals_transactionRevenue']\n\n    # Set subplot title\n    axes[i].set_title(f'Monthly Revenues in Millions - {month}')\n\n    ticks_y = ticker.FuncFormatter(lambda x, pos: '{:,.0f}'.format(x/1000000))\n\n    # Plot on the i-th subplot\n    # df_month_rev.plot(kind='line', ax=axes[i], rot=0)\n    sns.lineplot(data=df_monthly_rev, ax=axes[i], legend=True)\n    axes[i].yaxis.set_major_formatter(ticks_y)\n\n    # Set y-axis scale\n    # axes[i].set_yscale('log') \n\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:26.036826Z","iopub.execute_input":"2024-02-03T23:42:26.037194Z","iopub.status.idle":"2024-02-03T23:42:48.050639Z","shell.execute_reply.started":"2024-02-03T23:42:26.037167Z","shell.execute_reply":"2024-02-03T23:42:48.049287Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_month = df_train.groupby(['Year', 'Month', 'Month_Num']).agg(aggregations)\ndf_month = df_month[df_month.index != '2017-08-01']\n\ndf_month.sort_values(by=['Year', 'Month_Num'], inplace=True)\ndf_month = df_month.reset_index()\n\ndf_month","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:48.052040Z","iopub.execute_input":"2024-02-03T23:42:48.052398Z","iopub.status.idle":"2024-02-03T23:42:48.288043Z","shell.execute_reply.started":"2024-02-03T23:42:48.052367Z","shell.execute_reply":"2024-02-03T23:42:48.287172Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# # Create a 'month' column with the first day of each month\n# df_month['month'] = pd.to_datetime(dict(year=df_month.Year, month=df_month.Month_Num, day=int(1)))\n\n# # Move 'Date' column to the front\n# df_month.insert(0, 'month', df_month.pop('month'))\n\n# df_month = df_month.sort_values(by=['Year', 'Month_Num'])\n\n# df_month.head()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:48.289392Z","iopub.execute_input":"2024-02-03T23:42:48.289954Z","iopub.status.idle":"2024-02-03T23:42:48.294393Z","shell.execute_reply.started":"2024-02-03T23:42:48.289924Z","shell.execute_reply":"2024-02-03T23:42:48.293513Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(10, 4))\n\nticks_y = ticker.FuncFormatter(lambda x, pos: '{:,.0f}'.format(x/1000000))\n\nsns.lineplot(data=df_month, x='Month', y='totals_transactionRevenue')\n\nplt.gca().yaxis.set_major_formatter(ticks_y)\nplt.title('Revenue in Million by Month')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:48.295862Z","iopub.execute_input":"2024-02-03T23:42:48.296201Z","iopub.status.idle":"2024-02-03T23:42:48.586286Z","shell.execute_reply.started":"2024-02-03T23:42:48.296173Z","shell.execute_reply":"2024-02-03T23:42:48.585372Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## 10. Machine Learning Model","metadata":{}},{"cell_type":"code","source":"import lightgbm as lgb \nfrom sklearn.model_selection import train_test_split\nfrom sklearn.preprocessing import LabelEncoder, StandardScaler","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:48.587301Z","iopub.execute_input":"2024-02-03T23:42:48.587666Z","iopub.status.idle":"2024-02-03T23:42:49.501981Z","shell.execute_reply.started":"2024-02-03T23:42:48.587636Z","shell.execute_reply":"2024-02-03T23:42:49.501181Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 10.1 Data Transformation","metadata":{}},{"cell_type":"code","source":"### Create calendar columns in test dataset\ndf_test = df_test.copy()\n\ncreate_features(df_test)\n# df_test.tail()","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:49.502928Z","iopub.execute_input":"2024-02-03T23:42:49.504070Z","iopub.status.idle":"2024-02-03T23:42:57.069212Z","shell.execute_reply.started":"2024-02-03T23:42:49.504005Z","shell.execute_reply":"2024-02-03T23:42:57.068022Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_set = df_train.drop(columns=['Year', 'Month', 'Weekday'], axis=1).reset_index(drop=True)\ntest_set = df_test.drop(columns=['Year', 'Month', 'Weekday'], axis=1).reset_index(drop=True)\n\nprint(train_set.shape, test_set.shape)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:57.070292Z","iopub.execute_input":"2024-02-03T23:42:57.071299Z","iopub.status.idle":"2024-02-03T23:42:57.670276Z","shell.execute_reply.started":"2024-02-03T23:42:57.071269Z","shell.execute_reply":"2024-02-03T23:42:57.668973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# numeric_columns = train_set.select_dtypes(include='number').columns\nnumeric_cols = [c for c in train_set.columns if c.startswith('total') and c != 'totals_transactionRevenue']\nnumeric_cols","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:57.671650Z","iopub.execute_input":"2024-02-03T23:42:57.671964Z","iopub.status.idle":"2024-02-03T23:42:57.679631Z","shell.execute_reply.started":"2024-02-03T23:42:57.671938Z","shell.execute_reply":"2024-02-03T23:42:57.678572Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in numeric_cols:\n\n    # Check for and handle missing values\n    train_set[col].fillna(0, inplace=True) \n    test_set[col].fillna(0, inplace=True)\n\n    sc = StandardScaler()\n    # Fit the StandardScaler on the combined data (train and test)\n    sc.fit(pd.concat([train_set[[col]], test_set[[col]]], axis=0, ignore_index=True))\n\n    # Transform the values in both train and test sets\n    train_set[col] = sc.transform(train_set[[col]])\n    test_set[col] = sc.transform(test_set[[col]])","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:57.681291Z","iopub.execute_input":"2024-02-03T23:42:57.681572Z","iopub.status.idle":"2024-02-03T23:42:57.765233Z","shell.execute_reply.started":"2024-02-03T23:42:57.681550Z","shell.execute_reply":"2024-02-03T23:42:57.764090Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# categorical_columns = train_set.select_dtypes(include='object').columns\n\n## exclude columns for normalization or label-encoding\ncols_excluded = ['date', 'visitNumber']\n\ncategorical_cols = [c for c in train_set.columns if c not in cols_excluded and not c.startswith('total')]\ncategorical_cols","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:57.766619Z","iopub.execute_input":"2024-02-03T23:42:57.766921Z","iopub.status.idle":"2024-02-03T23:42:57.775068Z","shell.execute_reply.started":"2024-02-03T23:42:57.766892Z","shell.execute_reply":"2024-02-03T23:42:57.773594Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Convert date to UNIX timestamp\ntrain_set['date'] = train['date'].astype(int)\ntest_set['date'] = test['date'].astype(int)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:57.777094Z","iopub.execute_input":"2024-02-03T23:42:57.777567Z","iopub.status.idle":"2024-02-03T23:42:57.822142Z","shell.execute_reply.started":"2024-02-03T23:42:57.777490Z","shell.execute_reply":"2024-02-03T23:42:57.821154Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in categorical_cols:\n\n    le = LabelEncoder()\n    # Fit the LabelEncoder on the combined data (train and test)\n    le.fit(pd.concat([train_set[col], test_set[col]], axis=0, ignore_index=True))\n    \n    # Transform the values in both train and test sets\n    train_set[col] = le.transform(train_set[col])\n    test_set[col] = le.transform(test_set[col])","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:42:57.824712Z","iopub.execute_input":"2024-02-03T23:42:57.824979Z","iopub.status.idle":"2024-02-03T23:43:22.598555Z","shell.execute_reply.started":"2024-02-03T23:42:57.824957Z","shell.execute_reply":"2024-02-03T23:43:22.597477Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X = train_set.drop('totals_transactionRevenue', axis=1)\ny = train_set['totals_transactionRevenue']\n\nX_train, X_test, y_train, y_test = train_test_split( X, y, test_size=0.20, random_state=42)\nprint(X_train.shape, X_test.shape, y_train.shape, y_test.shape)","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:43:22.599935Z","iopub.execute_input":"2024-02-03T23:43:22.600263Z","iopub.status.idle":"2024-02-03T23:43:23.073609Z","shell.execute_reply.started":"2024-02-03T23:43:22.600238Z","shell.execute_reply":"2024-02-03T23:43:23.072752Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"params = {'num_leaves': 38,              \n          'min_data_in_leaf': 50,\n          'objective': 'regression',     \n          'max_depth': -1,                \n          'learning_rate': 0.1,           \n          'verbose': -1,\n          'predict_disable_shape_check': [True]\n          }\n\ntrain_data = lgb.Dataset(X_train, y_train)\ntest_data = lgb.Dataset(X_test, y_test)\n\nmodel = lgb.train(params,\n                train_data,\n                20000,\n                valid_sets=[train_data, test_data],\n                callbacks=[lgb.early_stopping(stopping_rounds=500), lgb.log_evaluation(50)])","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:43:23.074846Z","iopub.execute_input":"2024-02-03T23:43:23.075651Z","iopub.status.idle":"2024-02-03T23:43:45.773229Z","shell.execute_reply.started":"2024-02-03T23:43:23.075621Z","shell.execute_reply":"2024-02-03T23:43:45.772507Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"preds = model.predict(test_set, num_iteration=model.best_iteration)\npreds","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:43:45.774367Z","iopub.execute_input":"2024-02-03T23:43:45.774854Z","iopub.status.idle":"2024-02-03T23:43:45.941626Z","shell.execute_reply.started":"2024-02-03T23:43:45.774827Z","shell.execute_reply":"2024-02-03T23:43:45.940889Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_set[\"PredictedRevenue\"] = preds\ntest_set","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:43:45.943075Z","iopub.execute_input":"2024-02-03T23:43:45.943702Z","iopub.status.idle":"2024-02-03T23:43:46.232403Z","shell.execute_reply.started":"2024-02-03T23:43:45.943666Z","shell.execute_reply":"2024-02-03T23:43:46.231462Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lgb_params = {'objective': 'regression',\n              'metric': 'rmse',\n              'num_leaves': 50,\n              'learning_rate': 0.01,\n              'bagging_fraction': 0.75,\n              'feature_fraction': 0.8,\n              'bagging_frequency': 5,\n              'force_row_wise': True,  \n              'verbose': -1,\n              'predict_disable_shape_check': True  \n}\n    \nlgb_train = lgb.Dataset(X_train, label=y_train)\nlgb_val = lgb.Dataset(X_test, label=y_test)\nmodel_1 = lgb.train(lgb_params, lgb_train, \n                  num_boost_round=700, \n                  valid_sets=[lgb_val], \n                  callbacks=[lgb.early_stopping(stopping_rounds=500), \n                             lgb.log_evaluation(50), ])","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:43:46.233945Z","iopub.execute_input":"2024-02-03T23:43:46.234283Z","iopub.status.idle":"2024-02-03T23:44:13.352392Z","shell.execute_reply.started":"2024-02-03T23:43:46.234255Z","shell.execute_reply":"2024-02-03T23:44:13.351065Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"preds_1 = model_1.predict(test_set.loc[:, test_set.columns != 'PredictedRevenue'], \n                          num_iteration=model.best_iteration, )\npreds_1","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:44:13.353909Z","iopub.execute_input":"2024-02-03T23:44:13.354277Z","iopub.status.idle":"2024-02-03T23:44:13.549940Z","shell.execute_reply.started":"2024-02-03T23:44:13.354248Z","shell.execute_reply":"2024-02-03T23:44:13.548786Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_set[\"PredictedRevenue\"] = preds_1\ntest_set","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:44:13.551228Z","iopub.execute_input":"2024-02-03T23:44:13.551628Z","iopub.status.idle":"2024-02-03T23:44:13.792357Z","shell.execute_reply.started":"2024-02-03T23:44:13.551595Z","shell.execute_reply":"2024-02-03T23:44:13.791203Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from lightgbm import LGBMRegressor","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:58:17.277403Z","iopub.execute_input":"2024-02-03T23:58:17.277851Z","iopub.status.idle":"2024-02-03T23:58:17.284669Z","shell.execute_reply.started":"2024-02-03T23:58:17.277814Z","shell.execute_reply":"2024-02-03T23:58:17.283837Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"lgb_params = {'num_leaves' : 50, 'learning_rate' : 0.01, \n              'bagging_fraction' : 0.75, 'feature_fraction' : 0.8, \n              'bagging_frequency' : 5, 'force_row_wise': [True], \n              'verbose': -1,}\n    \nmodel_2 = LGBMRegressor(**lgb_params, n_estimators=700)\nmodel_2.fit(X_train, y_train, \n          eval_set=[(X_test, y_test)])","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:58:18.589060Z","iopub.execute_input":"2024-02-03T23:58:18.589459Z","iopub.status.idle":"2024-02-03T23:59:32.593462Z","shell.execute_reply.started":"2024-02-03T23:58:18.589435Z","shell.execute_reply":"2024-02-03T23:59:32.592112Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"preds_2 = model_2.predict(test_set.loc[:, test_set.columns != 'PredictedRevenue'], \n                          num_iteration=model.best_iteration, )\npreds_2","metadata":{"execution":{"iopub.status.busy":"2024-02-03T23:59:32.598025Z","iopub.execute_input":"2024-02-03T23:59:32.598393Z","iopub.status.idle":"2024-02-03T23:59:32.901387Z","shell.execute_reply.started":"2024-02-03T23:59:32.598365Z","shell.execute_reply":"2024-02-03T23:59:32.900559Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_set[\"PredictedRevenue\"] = preds_2\ntest_set","metadata":{"execution":{"iopub.status.busy":"2024-02-04T00:05:18.269240Z","iopub.execute_input":"2024-02-04T00:05:18.271633Z","iopub.status.idle":"2024-02-04T00:05:18.526356Z","shell.execute_reply.started":"2024-02-04T00:05:18.271563Z","shell.execute_reply":"2024-02-04T00:05:18.525384Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":">***Obervations:***\n\nThe third model is a simplified version of model 2 and achieves the same results.\n","metadata":{}},{"cell_type":"markdown","source":">***More to do:***\n\n- Performance Metrics: Evaluate the models using appropriate performance metrics for regression tasks. \n\n- Cross-Validation: Cross-validation involves training the model on different subsets of the training data    \n    and evaluating its performance on validation sets.\n\n- Hyperparameters: Consider tuning hyperparameters to find the best combination for your specific dataset.\n\n- Feature Engineering: Check if any differences in feature engineering or data preprocessing might be    \n    influencing the model performance. \n\n","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}