{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"

Google Analytics Customer Revenue Extensive EDA

\n\n\n\n# Content\n\n- Introduction \n- Prepare the data analysis \n -Load packages \n -Load the data \n- Data exploration \n -Missing data \n -Channel Grouping \n -Social Engagement Type \n -Device attributes \n -Geographical/Network attributes \n -Total attributes \n -Traffic source attributes \n -Date and time \n- Conclusions \n- References "},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"# Introduction \n\n## The competition\n\nIn this competition, Google Cloud and Kaggle partenered with [RStudio](http://www.rstudio.com) to challenge the Kagglers to analyze a Google Merchandise Store (also known as GStore, where Google swag is sold) customer dataset to predict revenue per customer. \n\nThe data is provided both in *.csv format and in BigQuery format.\n\nIn this Kernel we will use the data in *.csv format.\n\nThe data fields are as following:\n\n* **fullVisitorId** - A unique identifier for each user of the Google Merchandise Store.\n* **channelGrouping** - The channel via which the user came to the Store.\n* **date** - The date on which the user visited the Store.\n* **device** - The specifications for the device used to access the Store.\n* **geoNetwork** - This section contains information about the geography of the user.\n* **sessionId** - A unique identifier for this visit to the store.\n* **socialEngagementType** - Engagement type, either \"Socially Engaged\" or \"Not Socially Engaged\".\n* **totals** - This section contains aggregate values across the session.\n* **trafficSource** - This section contains information about the Traffic Source from which the session originated.\n* **visitId** - An identifier for this session. This is part of the value usually stored as the _utmb cookie. This is only unique to the user. For a completely unique ID, you should use a combination of fullVisitorId and visitId.\n* **visitNumber** - The session number for this user. If this is the first session, then this is set to 1.\n* **visitStartTime** - The timestamp (expressed as POSIX time).\n\nSome of the data fields are blobs with multiple attributes, as following: **device**, **geoNetwork**, **totals**, **trafficSource**.\n\nWe will need to predict the natural log of the sum of all transactions per user. For every user in the test set, the target is:\n\n$$target_{user}=\\sum_{i=0}^n {transaction_{user}}_i$$\n\n\n## This Kernel\n\nThis Kernel objective is to explore the dataset for [Google Analytics Customer Revenue Prediction competition](https://www.kaggle.com/c/google-analytics-customer-revenue-prediction). \n\nWe only use the data in *.csv format.\n\nFor the **predictive model**, a separate **Kernel** was developed: https://www.kaggle.com/gpreda/ga-customer-revenue-simple-lightgbm, with public **LB 1.6650**.\n\n\nGo to top\n"},{"metadata":{"_uuid":"381326d85972ea5af0b0e1c2973fecfbac5910fc"},"cell_type":"markdown","source":"# Prepare the data analysis \n\n\n\n# Load the packages \n\n\n"},{"metadata":{"trusted":true,"_uuid":"33b2659e93c24e2084c4c2c68ba8885e8722eff8","_kg_hide-input":true},"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport json\nfrom pandas.io.json import json_normalize\nimport datetime as dt\nimport seaborn as sns \nimport matplotlib.pyplot as plt \nimport plotly.graph_objs as go\nfrom plotly import tools\nfrom plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot\ninit_notebook_mode(connected=True)\nimport os\nIS_LOCAL=False\nif(IS_LOCAL):\n PATH=\"../google-analytics-customer-revenue-prediction/input/\" \nelse:\n PATH=\"../input/\"","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"69af3be78d13f7b8ee27c6c377d734539be52951"},"cell_type":"markdown","source":"# Load the data \n\n\nWe load first the data. Let's see what data files we have."},{"metadata":{"trusted":true,"_uuid":"22ff35d3ce891337a24d72cf0200a24312680033","_kg_hide-input":true},"cell_type":"code","source":"print(os.listdir(PATH))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"029b893a83fbf37dd2a16a27de63ba9f4f600519"},"cell_type":"markdown","source":"Let's check first the columns and types of `train.csv`."},{"metadata":{"trusted":true,"_uuid":"e2f0ca8326b40ac6ee6c2d34e3189f6481ded974","_kg_hide-input":true},"cell_type":"code","source":"onerow = pd.read_csv(PATH+'train.csv',nrows=1)\npd.concat([onerow.T, onerow.dtypes.T], axis=1, keys=['Example', 'Type'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0f6a3bd763c0322e313d48a2c146dbad32583eb5"},"cell_type":"markdown","source":"Columns **device**, **geoNetwork**, **totals**, **trafficSource** are of type **Objects** and are storing data in **json** format. We will create a function to read the data and creates separate columns for every element in the jsons. \n\nAs well, we will have to pay attention to the **fullVisitorID** field, which is an integer but we will have to read it as **str**, to not loose some of the prefixing **0** digits. \n\nLet's load **train** data. We will use the procedure described in [1] to flatten the json objects."},{"metadata":{"trusted":true,"_uuid":"b097dab70f29e23e9fc3a1f9c94f590172adab5a","_kg_hide-input":true},"cell_type":"code","source":"#the columns that will be parsed to extract the fields from the jsons\ncols_to_parse = ['device', 'geoNetwork', 'totals', 'trafficSource']\n\ndef read_parse_dataframe(file_name):\n #full path for the data file\n path = PATH + file_name\n #read the data file, convert the columns in the list of columns to parse using json loader,\n #convert the `fullVisitorId` field as a string\n data_df = pd.read_csv(path, \n converters={column: json.loads for column in cols_to_parse}, \n dtype={'fullVisitorId': 'str'})\n #parse the json-type columns\n for col in cols_to_parse:\n #each column became a dataset, with the columns the fields of the Json type object\n json_col_df = json_normalize(data_df[col])\n json_col_df.columns = [f\"{col}_{sub_col}\" for sub_col in json_col_df.columns]\n #we drop the object column processed and we add the columns created from the json fields\n data_df = data_df.drop(col, axis=1).merge(json_col_df, right_index=True, left_index=True)\n return data_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b8b435d118f5f40b77888205b06b63f21bab6f2e","_kg_hide-input":true},"cell_type":"code","source":"%%time\ntrain_df = read_parse_dataframe('train.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"02272ac4c2690608a327938d09c7cd615d961455"},"cell_type":"markdown","source":"Let's check now the dataset shape."},{"metadata":{"trusted":true,"_uuid":"f5239cf58d211406eb7ea5b4bdf6e99ba9825c8e","_kg_hide-input":true},"cell_type":"code","source":"print(\"Train set:\",train_df.shape[0],\" rows, \", train_df.shape[1],\"columns\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e5b751e087d04322812f262f5a6bb5f28b86bccf","_kg_hide-input":true},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"878df09f709f3ae90bcde423e8d5d762023bdcd2"},"cell_type":"markdown","source":"It seems that **sessionId** is the result of concatenating **fullVisitorId** with **visitId**. The field **visitStartTime** seems to be identical with **visitId** and also it is most probably the timestamp. Let's check if the value of first **visitId** is a timestamp.\n\n"},{"metadata":{"trusted":true,"_uuid":"3c3457182e705cecb80205e1578ff5ae35e721ed","_kg_hide-input":true},"cell_type":"code","source":"print(dt.datetime.fromtimestamp(train_df['visitId'][0]).isoformat())","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"324666aa0905fead28134f672a66a6ccc0c1375c"},"cell_type":"markdown","source":"We will explore these into more details in the following section. Let's for now just extract date and time from the date field."},{"metadata":{"trusted":true,"_uuid":"fa9cf3c4def68f77eee585fe3cfe7a627fa5992f","_kg_hide-input":true},"cell_type":"code","source":"def process_date_time(data_df):\n data_df['date'] = data_df['date'].astype(str)\n data_df[\"date\"] = data_df[\"date\"].apply(lambda x : x[:4] + \"-\" + x[4:6] + \"-\" + x[6:])\n data_df[\"date\"] = pd.to_datetime(data_df[\"date\"]) \n data_df[\"year\"] = data_df['date'].dt.year\n data_df[\"month\"] = data_df['date'].dt.month\n data_df[\"day\"] = data_df['date'].dt.day\n data_df[\"weekday\"] = data_df['date'].dt.weekday\n return data_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e29a81ce78243864073717a15b3f4aa8fb9554f2","_kg_hide-input":true},"cell_type":"code","source":"train_df = process_date_time(train_df)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"df5edaf5057e22639e1c0444c754dce31d46e986"},"cell_type":"markdown","source":"Let's check again the dataset shape."},{"metadata":{"trusted":true,"_uuid":"7e5abc6f9320da63a73f5391e0a6d1bb8cd7c764","_kg_hide-input":true},"cell_type":"code","source":"print(\"Train set:\",train_df.shape[0],\" rows, \", train_df.shape[1],\"columns\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c76669ac4ccdae660599897cf9f0c0f07eba1cd2"},"cell_type":"markdown","source":"Let's load also the test data. Then, let's process similarly teh test data."},{"metadata":{"trusted":true,"_uuid":"c683ca374755d131583a88f524053640af32bdef","_kg_hide-input":true},"cell_type":"code","source":"%%time\ntest_df = read_parse_dataframe('test.csv')\ntest_df = process_date_time(test_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0aea222d2f0f6b183b13d3030f11d76cb73845bf","_kg_hide-input":true},"cell_type":"code","source":"print(\"Test set:\",test_df.shape[0],\" rows, \", test_df.shape[1],\"columns\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"eaf368323e8499a1a23a255037fd8d2c2dd3b2dd"},"cell_type":"markdown","source":"# Data exploration \n"},{"metadata":{"_uuid":"047fc85b45285e5b7340b6805785d84c7747480d"},"cell_type":"markdown","source":"## Missing data\n\n\nLet's check if there are columns with missing data. We will only show the columns with missing data."},{"metadata":{"trusted":true,"_uuid":"6877af7ee6a3e4fae873787a3b23f3e63d1ca838","_kg_hide-input":true},"cell_type":"code","source":"def missing_data(data):\n total = data.isnull().sum().sort_values(ascending = False)\n percent = (data.isnull().sum()/data.isnull().count()*100).sort_values(ascending = False)\n df = pd.concat([total, percent], axis=1, keys=['Total', 'Percent'])\n return df.loc[~(df['Total']==0)]\nmissing_data(train_df)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c40c35f521c22f3257c061e3e2f3a653476eaf51"},"cell_type":"markdown","source":"Some of the columns in the train dataset (8) have >97% of the values missing, majority columns with missing values being from **trafficSource**. \n\nThese fields (especially the ones with high percent of missing values) are candidates to be droped when we will create a predictive model. \n\nLet's check the status for the test dataset.\n\n"},{"metadata":{"trusted":true,"_uuid":"e9e0ae1d03b7dd4235c8e87aaf194cf15174492b","_kg_hide-input":true},"cell_type":"code","source":"missing_data(test_df)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"629b0620f9bb46b41bfc1d81e4f073b1a9037cb3"},"cell_type":"markdown","source":"A part of the features with very high missing data percent in the **train** data have a lower percent of the missing data in the **test** set (~93%) and also there are some that are not appearing in the list with fields with missing values in the **test**. \n\n\nWe can also see that there are fields that does appears only in the **train** and set, for example **trafficSource_campaignCode** . We will have to consider these aspects when we will decide what features to drop and what features to keep for the predictive model.\n\n\nGo to top"},{"metadata":{"_uuid":"6b382cc20a7742d0c4013ad392896df5f2ff482f"},"cell_type":"markdown","source":"## Channel Grouping\n\nLet's check the channelGrouping data distribution. We will only show the first 30 categories, where more than that."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"d4a8cbb7883fbe7d9958b14d8d94ead9efcc06bf"},"cell_type":"code","source":"def get_categories(data, val):\n tmp = data[val].value_counts()\n return pd.DataFrame(data={'Number': tmp.values}, index=tmp.index).reset_index()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"492867b5db4867753884b2ad2bdd24109a62269a"},"cell_type":"code","source":"def draw_trace_bar(data, title, xlab, ylab,color='Blue'):\n trace = go.Bar(\n x = data.head(30)['index'],\n y = data.head(30)['Number'],\n marker=dict(color=color),\n text=data.head(30)['index']\n )\n data = [trace]\n\n layout = dict(title = title,\n xaxis = dict(title = xlab, showticklabels=True, tickangle=15,\n tickfont=dict(\n size=9,\n color='black'),), \n yaxis = dict(title = ylab),\n hovermode = 'closest'\n )\n fig = dict(data = data, layout = layout)\n iplot(fig, filename='draw_trace')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"69e7cc8eeeff4dce7d5e9067930741df9403b70e"},"cell_type":"code","source":"draw_trace_bar(get_categories(train_df,'channelGrouping'), \"Channel grouping\", \"Channel grouping\", \"Number\", \"Lightblue\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"908a5b8add477b61105311ef0e1afd35427bfc79"},"cell_type":"markdown","source":"Let's look into more details in the **channelGrouping** classes."},{"metadata":{"trusted":true,"_uuid":"6fa6367678c625b5a83dd2c5e5b5693371d8fd4f","_kg_hide-input":true},"cell_type":"code","source":"def get_feature_distribution(data, feature):\n # Get the count for each label\n label_counts = data[feature].value_counts()\n # Get total number of samples\n total_samples = len(data)\n # Count the number of items in each class\n for i in range(len(label_counts)):\n label = label_counts.index[i]\n count = label_counts.values[i]\n percent = int((count / total_samples) * 10000)/100\n print(\"{:<30s}: {} or {}%\".format(label, count, percent))\n\nget_feature_distribution(train_df,'channelGrouping')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"60030891daff3a6442b812adbbaca7d695d1eb80"},"cell_type":"markdown","source":"## Social Engagement Type\n\nLet's check the Social engagement type data distribution."},{"metadata":{"trusted":true,"_uuid":"8e1063b1361fa41f0a7c987cb381b250a72aada1","_kg_hide-input":true},"cell_type":"code","source":"get_feature_distribution(train_df,'socialEngagementType')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b2187b839080fd297dd1fb8fac581a3bec9030f7"},"cell_type":"markdown","source":"Only **Not Socially Engaged** type is present. For prediction this field is a very good candidate to be droped. \n\nGo to top"},{"metadata":{"_uuid":"e944f2af365895ab110be4bd069274ebd2177df5"},"cell_type":"markdown","source":"## Device attributes \n\nLet's check the device fields."},{"metadata":{"trusted":true,"_uuid":"d52429db1bd552f040b9b24c8dced3ccb1ec2738","_kg_hide-input":true},"cell_type":"code","source":"device_cols = train_df.columns[train_df.columns.str.contains('device')].T.tolist()\nprint(\"There are \",len(device_cols),\"columns with device attributes:\\n\",device_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0e50e8528b27cd6be717b4c228c8a45452c9ca70"},"cell_type":"markdown","source":"Before starting to plot the number of visits per various devices attributes, let's check if there are devices attributes that have a unique value."},{"metadata":{"trusted":true,"_uuid":"cb5c5281cba1dd533251b1c11bf293e8b4c7ba52","_kg_hide-input":true},"cell_type":"code","source":"const_device_cols = []\nfor i, col in enumerate(device_cols):\n if(len(train_df[col].value_counts())==1):\n const_device_cols.append(col)\nprint(\"There are \",len(const_device_cols),\"columns with unique value for device attributes:\\n\",const_device_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"667dcad710d47be05091f6e8b695e3db8baa0e99"},"cell_type":"markdown","source":"The columns with constant value will be droped for the model.\n\nWe will only show the number of visits for the devices attributes that have more than one value. "},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"8dec42f1d42e4e2af588b55884159dd1a8e62c3c"},"cell_type":"code","source":"def show_features(data,features):\n color = [\"red\", \"blue\", \"green\", \"magenta\", \"yellow\", \"lightblue\", \"gold\", \"tomato\", \"grey\",\n \"lightgreen\", \"red\", \"blue\", \"green\", \"magenta\", \"yellow\", \"brown\", \"grey\", \"tomato\"]\n for i,feature in enumerate(features):\n draw_trace_bar(get_categories(train_df,feature), \n feature, feature, \"Number\", color[i])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8238244aaa8a9b0d00d8f35c49c709b0c43e0b72","_kg_hide-input":true,"scrolled":false},"cell_type":"code","source":"var_cols = [item for item in device_cols if item not in const_device_cols]\nshow_features(train_df,var_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1622e43ea639a54f11bff9a8e4828774bdbe32b5"},"cell_type":"markdown","source":"The majority of the visits are using devices with Windows OS, Chrome browser, from a Desktop. From the mobile devices, majority are phones. \n\nThe most used OS are: Windows, Macintosh, Android, iOS and Linux. \nThe most used browsers are Chrome, Safari, Firefox, Internet Explorer and Edge. \nLet's check few of these features correlation.\n"},{"metadata":{"trusted":true,"_uuid":"37026bce94567a0eeea07d3fd8be85ed17d36951","_kg_hide-input":true},"cell_type":"code","source":"def plot_heatmap_count(data_df, feature1, feature2, feature3='channelGrouping', color=\"Greens\", title=\"\", height=16, width=16):\n tmp = data_df.groupby([feature1, feature2])[feature3].count()\n df1 = tmp.reset_index()\n matrix = df1.pivot(feature1, feature2, feature3)\n fig, (ax1) = plt.subplots(ncols=1, figsize=(width,height))\n sns.heatmap(matrix, \n xticklabels=matrix.columns,\n yticklabels=matrix.index,ax=ax1,linewidths=.1,linecolor='black',annot=True,cmap=color)\n plt.title(title, fontsize=14)\n plt.show()\n \ndef plot_heatmap_sum(data_df, feature1, feature2, feature3='channelGrouping', color=\"Greens\", title=\"\", height=16, width=16):\n tmp = data_df.groupby([feature1, feature2])[feature3].sum()\n df1 = tmp.reset_index()\n matrix = df1.pivot(feature1, feature2, feature3)\n fig, (ax1) = plt.subplots(ncols=1, figsize=(width,height))\n sns.heatmap(matrix, \n xticklabels=matrix.columns,\n yticklabels=matrix.index,ax=ax1,linewidths=.1,linecolor='black',annot=True,cmap=color)\n plt.title(title, fontsize=14)\n plt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8683b36ef0a7dac515b1c562bfd1ae941e47fd68","_kg_hide-input":true},"cell_type":"code","source":"plot_heatmap_count(train_df, 'device_browser', 'device_operatingSystem',color='Reds',title=\"Device Browsers vs. Device OS\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"321752fcbfa49bf31e52313988333058ee11e27a"},"cell_type":"markdown","source":"Chrome with Windows are the most frequent combination, followed by Chrome with Macintosh, Chrome with Android and Safari with Macintosh and Safari with iOS."},{"metadata":{"trusted":true,"_uuid":"2603ace5d5d00f9a972c0e0f366f09adf1b5dfc3","_kg_hide-input":true},"cell_type":"code","source":"plot_heatmap_count(train_df, 'device_browser','device_deviceCategory', color='Blues',title=\"Device Browser vs. Device Category\", height=12, width=8)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"03ee2da02e21af2d9f1b19c313ad4f09fc84ffa5"},"cell_type":"markdown","source":"Chrome on Desktop is the most frequent browser-category device combination, followed by Chrome with mobile and Safari with desktop and with mobile."},{"metadata":{"trusted":true,"_uuid":"1fa79dcf73e4f5e5464eb3412e6246c65054645f","_kg_hide-input":true},"cell_type":"code","source":"plot_heatmap_count(train_df, 'device_deviceCategory', 'device_isMobile', color='viridis',title=\"Device is mobile vs. Device Category\", width=6, height=4)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0ec53c323c2c49adeb6a2105db57f52b55451d52"},"cell_type":"markdown","source":"We can observe that there are both Desktop that appears as mobile (110) device and tablet (14) and mobile (150) set as not mobile. \n\nGo to top"},{"metadata":{"_uuid":"772a7d49bf794c52bf695e75da0488b8d0aab529"},"cell_type":"markdown","source":"# Geographic/Network attributes\n\nLet's check the geographical/network attributes. As we did with the devices attributes, we will first gather all columns with **geoNetwork** in the name."},{"metadata":{"trusted":true,"_uuid":"f87edc0b46e937a49f3b883c33d910230223ce6d"},"cell_type":"code","source":"geo_cols = train_df.columns[train_df.columns.str.contains('geoNetwork')].T.tolist()\nprint(\"There are \",len(geo_cols),\"columns with geoNetwork attributes:\\n\",geo_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d2ea5f83d2db109a4401a467e92849cd60fbd7b0"},"cell_type":"markdown","source":"Before starting to plot the number of visits per various geoNetwork attributes, let's check if there are geoNetwork attributes that have a unique value."},{"metadata":{"trusted":true,"_uuid":"9ac099091b46237adda2269175f100e4ff3b923b"},"cell_type":"code","source":"const_geo_cols = []\nfor i, col in enumerate(geo_cols):\n if(len(train_df[col].value_counts())==1):\n const_geo_cols.append(col)\nprint(\"There are \",len(const_geo_cols),\"columns with unique value for geoNetwork attributes:\\n\",const_geo_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cac87c9bdd89bf631fa5e642bda53a35d2a053f1"},"cell_type":"markdown","source":"These columns are candidates to be dropped from the model. For the rest of the columns, we show the number of the visits per each attribute. \nNote: We limit the number of shown values/categories to 50, showing the most numerous first."},{"metadata":{"trusted":true,"_uuid":"febf5c17f4c369041a5e7f12c28e3af578f2a9ad"},"cell_type":"code","source":"var_cols = [item for item in geo_cols if item not in const_geo_cols]\nshow_features(train_df,var_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e5682410fcb84b5ecbe2f460539145e6174cb659"},"cell_type":"markdown","source":"Not all the cities, network domains, metropolitan areas, continents are set. \nMost numerous cities are not available in the dataset, as well as most metropolitan areas or network domains. \nThe continent with the largest number of visits is America. The sub-continent with the largest number of visits is Northern America."},{"metadata":{"_uuid":"4d389da421bd97989d6e4cec886ea6750965a725"},"cell_type":"markdown","source":"Let's also show the geographical features on a plotly map. We will show the country feature distribution."},{"metadata":{"trusted":true,"_uuid":"ad499de37506da9a8bf59ee611a307105a48d930"},"cell_type":"code","source":"tmp = train_df['geoNetwork_country'].value_counts()\ncountry_visits = pd.DataFrame(data={'geoNetwork_country': tmp.values}, index=tmp.index).reset_index()\ncountry_visits.columns = ['Country', 'Visits']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a550ca3eeb5cf86eda5d3d406fa938a36bb0c152"},"cell_type":"code","source":"def plot_country_map(data, location, z, legend, title, colormap='Rainbow'):\n data = dict(type = 'choropleth', \n colorscale = colormap,\n autocolorscale = False,\n reversescale = False,\n locations = data[location],\n locationmode = 'country names',\n z = data[z], \n text = data[z],\n colorbar = {'title':legend})\n layout = dict(title = title, \n geo = dict(showframe = False, \n projection = {'type': 'natural earth'}))\n choromap = go.Figure(data = [data], layout=layout)\n iplot(choromap)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"170d6306c652dbc7c20945d4b78a8b4710c2336e"},"cell_type":"code","source":"plot_country_map(country_visits, 'Country', 'Visits', 'Visits', 'Visits per country')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e5adb4c4cd1445ae89da030e857835f5ed5f87a7"},"cell_type":"markdown","source":"\n\nGo to top"},{"metadata":{"_uuid":"87a4c356bf7b0781730abac2471e9918a605c158"},"cell_type":"markdown","source":"## Totals attributes \n\nLet's check the total attributes. "},{"metadata":{"trusted":true,"_uuid":"4c6a0ded3034ff6d3c6c6c5ec5a8e7f025acabfa"},"cell_type":"code","source":"tot_cols = train_df.columns[train_df.columns.str.contains('totals')].T.tolist()\nprint(\"There are \",len(tot_cols),\"columns with Totals attributes:\\n\",tot_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6696f203628a327dad8ced7aa6b5ed9387ef094e"},"cell_type":"markdown","source":"Let's check if there are columns with an unique value."},{"metadata":{"trusted":true,"_uuid":"0762692811b3ac90d58a7c519f1dff89f52a98c2"},"cell_type":"code","source":"const_tot_cols = []\nfor i, col in enumerate(tot_cols):\n if(len(train_df[col].value_counts())==1):\n const_tot_cols.append(col)\nprint(\"There are \",len(const_tot_cols),\"columns with unique value for Totals attributes:\\n\",const_tot_cols)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"83f0fda3c399bc4234ff47af9d2758fb94db3c0f"},"cell_type":"code","source":"var_cols = [item for item in tot_cols if item not in const_tot_cols]\nshow_features(train_df,var_cols,12,4)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b22ec6da73185965c675a13d6dbc221c84f79b1b"},"cell_type":"markdown","source":"For **totals_transactionRevenue**, let's also show the values distribution."},{"metadata":{"trusted":true,"_uuid":"b18d7bce6e8599b12ddf547e6d0e15908fdebc11"},"cell_type":"code","source":"train_df['totals_transactionRevenue'] = pd.to_numeric(train_df['totals_transactionRevenue'])\ndf = train_df[train_df['totals_transactionRevenue'] > 0]['totals_transactionRevenue']\nf, ax = plt.subplots(1,1, figsize=(16,4))\nplt.title(\"Distribution of totals: transaction revenue\")\nsns.kdeplot(df, color=\"green\")\nplt.tick_params(axis='both', which='major', labelsize=12)\nplt.ylabel('Density plot', fontsize=12)\nplt.xlabel('Transaction revenue', fontsize=12)\nlocs, labels = plt.xticks()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"016fcf538ec267cb7a2500f0fe7465b77e7fea62"},"cell_type":"markdown","source":"Let's check as well the log of the total transaction revenue."},{"metadata":{"trusted":true,"_uuid":"23517ba176d64518a557aed12e9df3c17dce816c"},"cell_type":"code","source":"plt.figure(figsize=(12,6))\nsns.distplot(np.log1p(df),color=\"darkgreen\",bins=50)\nplt.xlabel(\"Log(total transaction revenue)\");\nplt.title(\"Logarithmic distribution of total transaction revenue (non-zeros)\");","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"034aeff5a2db5936eaa75176d14810139b593002"},"cell_type":"markdown","source":"Let's show the distribution of visits per country, taking into consideration only the visits with non-zero transactions."},{"metadata":{"trusted":true,"_uuid":"b22dbcd77a5ba32e1369190a3db8a7a5008b6e07"},"cell_type":"code","source":"# select the visits with non-zero transaction revenue\nnon_zero = train_df[train_df['totals_transactionRevenue']>0]\ntmp = non_zero['geoNetwork_country'].value_counts()\ncountry_visits = pd.DataFrame(data={'geoNetwork_country': tmp.values}, index=tmp.index).reset_index()\ncountry_visits.columns = ['Country', 'Visits']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"eab4a78ffa310dc74f8926ad2cc1b08ce9ebaf6a"},"cell_type":"code","source":"plot_country_map(country_visits, 'Country', 'Visits', 'Visits', 'Visits with non zero transactions')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2e1376cbdec51b83124bd5660ebfe9837928aaf0"},"cell_type":"code","source":"# select the visits with non-zero transaction revenue and calculate the sums\nnon_zero = train_df[train_df['totals_transactionRevenue']>0]\ntmp = non_zero.groupby(['geoNetwork_country'])['totals_transactionRevenue'].sum()\ncountry_total = pd.DataFrame(data={'total': tmp.values}, index=tmp.index).reset_index()\ncountry_total.columns = ['Country', 'Total']\ncountry_total['Total'] = np.log1p(country_total['Total'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a82f356795fb54aacf03eef1ed2bef5ffdcff6d9"},"cell_type":"code","source":"plot_country_map(country_total, 'Country', 'Total', 'Total(log)', 'Total revenues per country (log scale)')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d969743089981236bd98f0b69239102d06e5262f"},"cell_type":"markdown","source":"We can observe that majority of the visits with non-zero transactions and most of the revenues are from US. \n\nLet's check the top 10 of the transaction revenue."},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"1761019153e7369df22a601834a2a0330864643f"},"cell_type":"code","source":"non_zero[['fullVisitorId','visitNumber', 'totals_transactionRevenue', 'channelGrouping']].sort_values(['totals_transactionRevenue', 'fullVisitorId'], ascending=[0,0]).head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dfef4addcfabaa2b1bdd24cabf40b647ee207408"},"cell_type":"markdown","source":"We can see that in top 10 by the total transaction revenue there is one fullVisitorId (1957458976293878100) that appears 5 times.\n\nLet's check also the top 10 of visit number."},{"metadata":{"trusted":true,"_uuid":"e3416b53974340b8191f7b34239fc9d563214bf5"},"cell_type":"code","source":"non_zero[['fullVisitorId','visitNumber', 'totals_transactionRevenue', 'channelGrouping']].sort_values(['visitNumber', 'totals_transactionRevenue'], ascending=[0,0]).head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"21e06085516563ad1270be965cd02dd7771094ba"},"cell_type":"markdown","source":"One fullVisitorId (1957458976293878100) ocupies all the positions in top 10 (by visitNumber).\n\nLet's calculate the sum of the transaction revenue per **channelGrouping**."},{"metadata":{"trusted":true,"_uuid":"822add84a22e75abb59e1adabf2932f7251837d0"},"cell_type":"code","source":"# select the visits with non-zero transaction revenue and calculate the sums\nnon_zero = train_df[train_df['totals_transactionRevenue']>0]\ntmp = non_zero.groupby(['channelGrouping', 'geoNetwork_subContinent'])['totals_transactionRevenue'].sum()\nchannel_total = pd.DataFrame(data={'total': tmp.values}, index=tmp.index).reset_index()\nchannel_total.columns = ['Channel', 'Subcontinent', 'Total']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1442828cdc8e29217d5ebb2221da1064ea61213a"},"cell_type":"code","source":"plot_heatmap_sum(non_zero, 'geoNetwork_subContinent','channelGrouping', 'totals_transactionRevenue','rainbow',\"Total transactions per channel and subcontinent\", width=16, height=6)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c03d55d590d7d75fbeaa85b779874ba25ba1af86"},"cell_type":"markdown","source":"Most of the transaction revenues are from Northern America, with Refferal, Direct and Organic Search channel.\n\nGo to top"},{"metadata":{"_uuid":"b748777535333cb2f07da0ab17c5ab57c573e337"},"cell_type":"markdown","source":"\n## Traffic Source attributes "},{"metadata":{"trusted":true,"_uuid":"323eb0d53a6fad2a4c53948933fb903a1967ae2f"},"cell_type":"code","source":"ts_cols = train_df.columns[train_df.columns.str.contains('trafficSource')].T.tolist()\nprint(\"There are \",len(ts_cols),\"columns with Totals attributes:\\n\",ts_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f95ecfc5ae3f6f9b90edc58337dc4e744c245c69"},"cell_type":"markdown","source":"Let's check the **trafficSource** attributes that have unique value."},{"metadata":{"trusted":true,"_uuid":"e415efe4ba210090f19edb6c3b078689644a34f7"},"cell_type":"code","source":"const_ts_cols = []\nfor i, col in enumerate(ts_cols):\n if(len(train_df[col].value_counts())==1):\n const_ts_cols.append(col)\nprint(\"There are \",len(const_ts_cols),\"columns with unique value for Traffic Source attributes:\\n\",const_ts_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"82b8255d23de99c4a37bbf832b3e07c92a746ab3"},"cell_type":"markdown","source":"We will plot only the attributes of Traffic Source with multiple categories."},{"metadata":{"trusted":true,"_uuid":"03de659eeab3f8e789c6fca1d3ed0da624329706"},"cell_type":"code","source":"var_cols = [item for item in ts_cols if item not in const_ts_cols]\nshow_features(train_df,var_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e19e026d2c2f4c0ec0e6b5aa705277c666229714"},"cell_type":"markdown","source":"Google Merchandise Collection is the most important adCountent traffic source. \n\nMajority of campaign attributes are not set. Majority of keywords are not provided. Organic and refferal stands for the majority of mediums. \nThe most important traffic source is google, followed by youtube.com. \n\n\n\nGo to top"},{"metadata":{"_uuid":"e4217b5eba1b21669d8acb4dee0906a0b8503f07"},"cell_type":"markdown","source":"## Date and time \n\n\nLet's check the distribution of values for date and time features. "},{"metadata":{"trusted":true,"_uuid":"e71fe865e414fc425f1fb71e2b366a5b45847e64"},"cell_type":"code","source":"var_cols = ['year','month','day','weekday']\nshow_features(train_df,var_cols)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"80493851475060087a8940911009dadb055a53d9"},"cell_type":"markdown","source":"Let's plot the number of visits vs. date and the amount of transaction revenues vs. date for the train set.\n\nFirst, let's show the number of visits finalized with a transaction per day."},{"metadata":{"trusted":true,"_uuid":"98e980703390655d5eb3366f497c56acbc9e3c47"},"cell_type":"code","source":"def plot_scatter_data(data, xtitle, ytitle, title, color='blue'):\n trace = go.Scatter(\n x = data.index,\n y = data.values,\n name=ytitle,\n marker=dict(\n color=color,\n ),\n mode='lines+markers'\n )\n data = [trace]\n layout = dict(title = title,\n xaxis = dict(title = xtitle), yaxis = dict(title = ytitle),\n )\n fig = dict(data=data, layout=layout)\n iplot(fig, filename='lines')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2491e1b82068f3085d4178ad99cf12b6bc787a03"},"cell_type":"code","source":"count_all = train_df.groupby('date')['totals_transactionRevenue'].agg(['size'])\ncount_all.columns = [\"Total\"]\ncount_all = count_all.sort_index()\nplot_scatter_data(count_all['Total'],'Date', 'Total','Total count of visits (including zero transactions)','green')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7ac202c99cb12521d17967d619c3a1777107ba22"},"cell_type":"markdown","source":"Then, let's see the number of visits with non-zero transactions per day."},{"metadata":{"trusted":true,"_uuid":"3ef1e8ced969e83f1213ce7e12eeb66878b23db2"},"cell_type":"code","source":"count_nonzero = train_df.groupby('date')['totals_transactionRevenue'].agg(['count'])\ncount_nonzero.columns = [\"Total\"]\ncount_nonzero = count_nonzero.sort_index()\nplot_scatter_data(count_nonzero['Total'],'Date', 'Total','Total non-zero transaction visits','darkblue')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2b6dbc947e816b7dba1944b798943d6faea3064f"},"cell_type":"markdown","source":"Let's plot the total amount of transactions per day."},{"metadata":{"trusted":true,"_uuid":"8a2053fc9cd1afef3ba301e158f498fbed7c248a"},"cell_type":"code","source":"total_nonzero = train_df.groupby('date')['totals_transactionRevenue'].agg(['sum'])\ntotal_nonzero.columns = [\"Total\"]\ntotal_nonzero = total_nonzero.sort_index()\nplot_scatter_data(total_nonzero['Total'],'Date', 'Total','Total non-zero transaction amounts','red')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fd9d105e9d20f653ba02970d802a929a9ff0198b"},"cell_type":"markdown","source":"Let's plot the total amount of non-zero transactions per day, grouped by **channelGrouping**."},{"metadata":{"trusted":true,"_uuid":"c1bd34f720d52420e811d78f23e855ed5a32c88f"},"cell_type":"code","source":"channels = list(train_df['channelGrouping'].unique())\ndata = []\nfor channel in channels:\n subset = train_df[train_df['channelGrouping']==channel]\n subset = subset.groupby('date')['totals_transactionRevenue'].agg(['sum'])\n subset.columns = [\"Total\"]\n subset = subset.sort_index()\n trace = go.Scatter(\n x = subset['Total'].index,\n y = subset['Total'].values,\n name=channel,\n mode='lines'\n )\n data.append(trace)\nlayout= go.Layout(\n title= 'Total amount of non-zero transactions per day, grouped by channel',\n xaxis = dict(title = 'Date'), yaxis = dict(title = 'Total'),\n showlegend=True,\n)\nfig = dict(data=data, layout=layout)\niplot(fig, filename='lines')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cac60e2fd17ec280e21354f9df505ae4a496a49d"},"cell_type":"markdown","source":"Let's plot the total amount of non-zero transactions per day, grouped by **device_operatingSystem**."},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"f09fd19438726fd1032ac49875a920b970799b17"},"cell_type":"code","source":"opsys = list(train_df['device_operatingSystem'].unique())\ndata = []\nfor os in opsys:\n subset = train_df[train_df['device_operatingSystem']==os]\n subset = subset.groupby('date')['totals_transactionRevenue'].agg(['sum'])\n subset.columns = [\"Total\"]\n subset = subset.sort_index()\n trace = go.Scatter(\n x = subset['Total'].index,\n y = subset['Total'].values,\n name=os,\n mode='lines'\n )\n data.append(trace)\nlayout= go.Layout(\n title= 'Total amount of non-zero transactions per day, grouped by OS',\n xaxis = dict(title = 'Date'), yaxis = dict(title = 'Total'),\n showlegend=True,\n)\nfig = dict(data=data, layout=layout)\niplot(fig, filename='lines')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6f22eaa671eed43564389e475975a67debacd7e1"},"cell_type":"markdown","source":"\nLet's plot now the number of visits per day in the test set."},{"metadata":{"trusted":true,"_uuid":"ba5bfaa31c18bc08a2d1abfee0cb115f6590756b"},"cell_type":"code","source":"total_test = test_df.groupby('date')['fullVisitorId'].agg(['count'])\ntotal_test.columns = [\"Total\"]\ntotal_test = total_test.sort_index()\nplot_scatter_data(total_test['Total'],'Date', 'Total','Total count of visits per day (test set)','magenta')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2d5fd145038b99319289a043e37f96d75132b48d"},"cell_type":"markdown","source":"Let's plot the average amount of transactions, grouped by **total_pageviews**."},{"metadata":{"_uuid":"740aa26b528831b82d690c50ce7c0ef55dd9c784"},"cell_type":"markdown","source":"# Conclusions \n\nFor the predictive model, there is this Kernel available: https://www.kaggle.com/gpreda/ga-customer-revenue-simple-lightgbm, using the results of this data analysis.\n\n"},{"metadata":{"_uuid":"28df9f70d54fbaeeedd47aba04f3176badacfc75"},"cell_type":"markdown","source":"# References \n\n[1] [Julián Peller](https://www.kaggle.com/julian3833), 1 - Quick start: read csv and flatten json fields, https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields \n[2] [Shivam Bansal](https://www.kaggle.com/shivamb), Exploratory Analysis - GA Customer Revenue, https://www.kaggle.com/shivamb/exploratory-analysis-ga-customer-revenue/ \n[3] [SRK](https://www.kaggle.com/sudalairajkumar), Simple Exploration+Baseline - GA Customer Revenue, https://www.kaggle.com/sudalairajkumar/simple-exploration-baseline-ga-customer-revenue \n\nGo to top"}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}