{"cells":[{"metadata":{"_uuid":"f0f383056618671a00c1e6369d784015d498584f"},"cell_type":"markdown","source":"**Kernel Approach**\n\nThis kernel's objective is to give some data analysis and intuition around the Google Analytics Customer Revenue Prediction dataset. No Machine Learning model will be presented here, the first step to make a good model is to understand the data, and that's why we are here!\nAlso, as a market segmentation-like problem, we should know very well our public tendencies."},{"metadata":{"_uuid":"405e8cf4417058a7ef29f4111ebd7d4051afed3c"},"cell_type":"markdown","source":"**Competition dataset**\n\nIn this competition, as in many others we are given two datasets:\n* train.csv\n* test.csv\n\nBasically we are asked to use the data in train.csv to build a model and predict the Customer Revenue about the test.csv dataset, as better explained in the official challenge post [Challenge Description](https://www.kaggle.com/c/google-analytics-customer-revenue-prediction)."},{"metadata":{"_uuid":"a1697e03f622bebf38585893ec0558df1281a595"},"cell_type":"markdown","source":"**Reshaping the given dataset**\n\nAs mentioned in the challenge description, some fields of the datasets train.csv and test.csv are in json format, and for better data manipulation with DataFrames, we should make some type conversions first. For this task, we already have a [pretty nice kernel](https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields/notebook) built by [Julián Peller](https://www.kaggle.com/julian3833)."},{"metadata":{"trusted":true,"_uuid":"238c7ef5b302fb5659a082ed8b56e270172f3aa9"},"cell_type":"code","source":"import os\nimport json\nimport numpy as np\nimport pandas as pd\nfrom pandas.io.json import json_normalize\n\ndef load_df(csv_path='../input/train.csv', nrows=None):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    \n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'}, # Important!!\n                     nrows=nrows)\n    \n    for column in JSON_COLUMNS:\n        column_as_df = json_normalize(df[column])\n        column_as_df.columns = [f\"{column}.{subcolumn}\" for subcolumn in column_as_df.columns]\n        df = df.drop(column, axis=1).merge(column_as_df, right_index=True, left_index=True)\n    print(f\"Loaded {os.path.basename(csv_path)}. Shape: {df.shape}\")\n    return df\n\ndf_train = load_df()\ndf_test = load_df(\"../input/test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"41b6cdc5f1e8b800b3c8f2c8a6fdef10073376c5"},"cell_type":"code","source":"#Lets have a look at the data\ndf_train.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2ad01a05a94a3d5407d7cb605702bd08ac8202bd"},"cell_type":"markdown","source":"**Data Analisys**\n\nOne very important observation we have to keep in mind is that our visitors'Ids are unique, but the Id's rows in the datasets are not unique, as stated in the [Challange Data Description](https://www.kaggle.com/c/google-analytics-customer-revenue-prediction/data).\n> Both train.csv and test.csv contain the columns listed under Data Fields. Each row in the dataset is one visit to the store. Because we are predicting the log of the total revenue per user, be aware that not all rows in test.csv will correspond to a row in the submission, but all unique fullVisitorIds will correspond to a row in the submission.\n\nThis way, we need to group all the information by the users Id's. In this task I was heavily inspired by the [Simple Exploration+Baseline - GA Customer Revenue](https://www.kaggle.com/sudalairajkumar/simple-exploration-baseline-ga-customer-revenue) from [SRK](https://www.kaggle.com/sudalairajkumar), many of the plot analisys were similar to his, but I tried to simplify codewise the plot builds, and made some further comments on the results.\n\n"},{"metadata":{"trusted":true,"_uuid":"6d16f6770547d1ac108adbc5f2327e1db3c3a904"},"cell_type":"code","source":"df_train.columns\nprint('Is there more than one transaction by VisitorId in train dataset?',\n      len(df_train['fullVisitorId'])!=df_train['fullVisitorId'].nunique())\nprint('Is there more than one transaction by VisitorId in test dataset?',\n      len(df_test['fullVisitorId'])!=df_test['fullVisitorId'].nunique())\n#Confirming that we have more rows than unique Visitors Id's in both train and test datasets.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1805bb7a84e1eb95a69d858fe206e31b03ed91b2"},"cell_type":"code","source":"type(df_train['totals.transactionRevenue'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"85e942e1cc23c56f0913664e97d8cbdc303c47bc"},"cell_type":"code","source":"#Lets see how the transaction revenues behave among all unique users\nimport matplotlib.pyplot as plt\ndf_train[\"totals.transactionRevenue\"] = df_train[\"totals.transactionRevenue\"].astype('float')\ngdf = df_train.groupby(\"fullVisitorId\")[\"totals.transactionRevenue\"].sum().reset_index()\nplt.figure(figsize=(10,8))\nplt.scatter(range(gdf.shape[0]), np.sort(np.log1p(gdf[\"totals.transactionRevenue\"])))\nplt.xlabel('index Users', fontsize=12)\nplt.ylabel('TransactionRevenue', fontsize=12)\nplt.show()\n#By these two plots we can conclude that the majority of the TransactionRevenue comes from a very little portion of costumers.\n#Therefore, the marketing teams must direct carefully their effor on investments.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e6e9f2327952e0f55e942bf95d529a5a0ca8c045"},"cell_type":"code","source":"#Lets see a distribution plot of the target variable to confirm our hypothesis\nimport seaborn as sns\nimport warnings\nwarnings.filterwarnings('ignore')\nfig, ax = plt.subplots(figsize=(12, 8))\nsns.distplot(np.log1p(gdf[\"totals.transactionRevenue\"]),ax=ax)\n#As expected, we se that the majority of users don't contribute to the revenue.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"627eaa34d3d8434696ed332dbacd4a88d6e52c41"},"cell_type":"code","source":"print('\\n',pd.notnull(df_train[\"totals.transactionRevenue\"]).sum(),\n' Non-null revenue Instances ocurred, which represent',\n100*(pd.notnull(df_train[\"totals.transactionRevenue\"]).sum()/len(df_train)),'% of all Instances')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3893d732d732d3bc8ec29eb92c61483488e7bd79"},"cell_type":"code","source":"print('\\n',gdf[\"totals.transactionRevenue\"][gdf[\"totals.transactionRevenue\"]>0].count(),\n' Customers contribute with non-zero revenue which is equivalent to',\n100*(gdf[\"totals.transactionRevenue\"][gdf[\"totals.transactionRevenue\"]>0].count()/len(gdf)),'% of all Customers')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cd24b5f53948a8a97b6fb2d3db53e4b2f9ca6f1c"},"cell_type":"markdown","source":"**Dropping columns with constant values**\n\nSome of the features imported in the json-csv process have only one unique value, which doesn't give us any information, and it would be a problem for any ML model. So we should just drop them."},{"metadata":{"trusted":true,"_uuid":"21882880e9ca278ec825a8b662b14ec08fa7e95c"},"cell_type":"code","source":"dropcols = [c for c in df_train.columns if df_train[c].nunique()==1]\ndropcols_test = [c for c in df_test.columns if df_test[c].nunique()==1]\ndf_train = df_train.drop(dropcols,axis=1)\ndf_test = df_test.drop(dropcols_test,axis=1)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"**Build some percentage features**\n\nIt is usefull to build some extra features in order to have the percentage information, for example the % of instances in relation of a specific web browser in relation to all instances accounted for all web browsers:"},{"metadata":{"trusted":true,"_uuid":"0c42b7df8bf007b969f047cefccb270f62fb1431"},"cell_type":"code","source":"#Definition of the aggregation funtion, that we will use to build some % features\ndef aggregations(feature):\n    df = df_train.groupby(feature)['totals.transactionRevenue'].agg(['size', 'count'])\n    df.columns = [\"count of instances\", \"count of non-zero revenue\"]\n    df['percent of instances[%]'] = (df['count of instances']*100)/df['count of instances'].sum()\n    df['percent of non-zero revenue[%]'] = (df['count of non-zero revenue']*100)/df['count of non-zero revenue'].sum()\n    df = df.sort_values(by=\"percent of non-zero revenue[%]\", ascending=False)\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b27664618a49df450de8a4b30809a531cd978ea0"},"cell_type":"code","source":"# Device Browser Analisys:\ncnt_srs = aggregations('device.browser')\ntop_rev = cnt_srs[\"percent of non-zero revenue[%]\"].nunique()\ncnt_srs[:6].plot.barh(y=['percent of non-zero revenue[%]','percent of instances[%]'], rot=0,figsize=(12,9))\n#It is pretty clear that the majority of instances and non-zero revenues comes from chromes users.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8f0b4aea2ab8714652ff75c9fe88dad12592a408"},"cell_type":"code","source":"# Device Category (Desktop,...)\ncnt_cat = aggregations('device.deviceCategory')\ncnt_cat.plot.barh(y=['percent of non-zero revenue[%]','percent of instances[%]'], rot=0,figsize=(12,9))\n#On the device category front, desktop seem to have higher percentage of non-zero revenue counts compared to mobile devices.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f7109c6017589d3fdb3e162607eff5cde54021e7"},"cell_type":"code","source":"# Device Operating System (Windows,...)\ncnt_os = aggregations('device.operatingSystem')\ncnt_os[:8].plot.barh(y=['percent of non-zero revenue[%]','percent of instances[%]'], rot=0,figsize=(12,9))\n#In device operating system, although the number of counts is more from windows, \n#the number of counts where revenue is not zero is more for Macintosh.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2326390868c012ffd7e52bde22626b32bc83329a"},"cell_type":"markdown","source":"**Date Information:** Lets split the information in Years/Months/Days and split the analisys."},{"metadata":{"trusted":true,"_uuid":"8fd4ed938dd94de5953830a4f6086be8392f156b"},"cell_type":"code","source":"#Date Information: Lets split the information in Years/Months/Days and split the analisys.\ndf_train['year']= df_train['date'].astype(str).str[:4]\ndf_test['year']= df_test['date'].astype(str).str[:4]\ndf_train['month']= df_train['date'].astype(str).str[4:6]\ndf_test['month']= df_test['date'].astype(str).str[4:6]\ndf_train['day']= df_train['date'].astype(str).str[6:8]\ndf_test['day']= df_test['date'].astype(str).str[6:8]\ndf_train.drop('date',axis=1,inplace=True)\ndf_test.drop('date',axis=1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e3723c7fc5bd044587c54166e59e02cdd329aebc"},"cell_type":"code","source":"# Year Analisys\ncnt_y = aggregations('year')\ncnt_y.plot.barh(y=['percent of non-zero revenue[%]','percent of instances[%]'], rot=0,figsize=(12,9))\n#Slight differences beetween years, probably not worth to keep this information on dataset\ndf_train.drop('year',axis=1,inplace=True)\ndf_test.drop('year',axis=1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f0254cb2cbbe8737f556b232be99b4a5a5b46698"},"cell_type":"code","source":"# Months Analisys\ncnt_m = aggregations('month')\ncnt_m.plot.barh(y=['percent of non-zero revenue[%]','percent of instances[%]'], rot=0,figsize=(12,9))\n#Looks like at Dezember people are more likely to buy, most likely because of New Year parties like Christmas.\n#An interesting observation here is that at November there is a peak of instances, proably because people \n#are researching what they should buy in December.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8a9682f3205e0d78a8d59519f41efb48f6dfc661"},"cell_type":"code","source":"# Days of the month Analisys\ncnt_d = aggregations('day')\ncnt_d.plot.barh(y=['percent of non-zero revenue[%]','percent of instances[%]'], rot=0,figsize=(12,9))\n#Looks like every 12th day of the month ppl are more likely to buy.\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c16af7f45941b801db5b21aabaaab986aa8c9806"},"cell_type":"markdown","source":"**Regions Information:**"},{"metadata":{"_uuid":"ed18afaa6f060043e226807823509fde223572e1","trusted":true},"cell_type":"code","source":"# Continent Analysis\ncnt_con = aggregations('geoNetwork.continent')\ncnt_con.plot.barh(y=['percent of non-zero revenue[%]','percent of instances[%]'], rot=0,figsize=(12,9))\n#Clearly the Americas englobe the absolut majority of revenue. I was expecting the Europe to go on second, \n#but it is behind Asia.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0b48958ac1f856b1cfb165f5ba4b06c45b3c42fc"},"cell_type":"code","source":"# SubContinent Analysis\ncnt_scon = aggregations('geoNetwork.subContinent')\ncnt_scon[:12].plot.barh(y=['percent of non-zero revenue[%]','percent of instances[%]'], rot=0,figsize=(12,9))\n#Again, the absolute majority of revenue comes from North America, followed by South America, Eastern Asia and South Asia. \n#From forth position on there is almost no representativity.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7622afd20b93fa0c9e074787a833fdc262388963"},"cell_type":"markdown","source":"**Traffic Source Analisys:**"},{"metadata":{"trusted":true,"_uuid":"fb8e02ac31613e95e4d67f62c9c4c267076c1022"},"cell_type":"code","source":"#Traffic Source\ncnt_ts = aggregations('trafficSource.source')\ncnt_ts[:20].plot.barh(y=['percent of non-zero revenue[%]','percent of instances[%]'], rot=0,figsize=(12,9))\n#Google Search and Google Sales plataforms are domaining the counts and non-zero revenue.\n#It is interesting to see that Youtube has a lot of counts, but has a very low non-zero ratio revenue.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"26637578fbea2c8555308ce1e7fc1b9abbdde6d5"},"cell_type":"code","source":"#Traffic Source Medium:\ncnt_tsm = aggregations('trafficSource.medium')\ncnt_tsm.plot.barh(y=['percent of non-zero revenue[%]','percent of instances[%]'], rot=0,figsize=(12,9))\n#Even though \"organic users\" have more counts overall, the counts from referral were converted into non-zero \n#revenue in a higher ratio.\n#This shows us the big influence that referral has in sales.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8bfb4e9b3088b43b2b8367061f283977616eb0ce"},"cell_type":"markdown","source":"**Visitor Information Analisys:**"},{"metadata":{"trusted":true,"_uuid":"20bff571d4315d9933260b9cb4c248e22643d53b"},"cell_type":"code","source":"#Hits\ncnt_hit = aggregations('totals.hits').reset_index()\ncnt_hit['totals.hits'] = cnt_hit['totals.hits'].astype(int)\ncnt_hit.sort_values(by=['totals.hits'],inplace=True)\ncnt_hit.set_index('totals.hits',inplace=True)\nplt.figure(1,figsize=(12,6))\nplt.plot(cnt_hit.index,cnt_hit['count of instances'],'r',\n         markersize=4,linewidth=2,label='count of instances per total hits')\nplt.legend()\nplt.xlabel('totals.hits')\nplt.ylabel('count of instances')\n\nplt.figure(2,figsize=(12,6))\nplt.plot(cnt_hit.index,cnt_hit['count of non-zero revenue'],'b',\n         markersize=4,linewidth=2,label='count of non-zero revenue per total hits')\nplt.legend()\nplt.xlabel('totals.hits')\nplt.ylabel('count of non-zero revenue')\n#We can see that at low number of hits we have a low number of non-zero revenues, but as we increse the number \n#of hits we get more non-zero revenues, reaching its peak at around hits=25, and then decreasing the number of \n#non-zero revenues.\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"476e63edebca78b6407a8c8537ea5b496ee3e2ff"},"cell_type":"code","source":"#PageViews\ncnt_pv = aggregations('totals.pageviews').reset_index()\ncnt_pv['totals.pageviews'] = cnt_pv['totals.pageviews'].astype(int)\ncnt_pv.sort_values(by=['totals.pageviews'],inplace=True)\ncnt_pv.set_index('totals.pageviews',inplace=True)\n\ncnt_pv.sort_index(inplace=True)\nplt.figure(3,figsize=(12,6))\nplt.plot(cnt_pv.index[:100],cnt_pv['count of instances'][:100],'r',\n         markersize=4,linewidth=2,label='count of instances per page view')\nplt.legend()\nplt.xlabel('totals.hits')\nplt.ylabel('count of instances')\n\nplt.figure(4,figsize=(12,6))\nplt.plot(cnt_pv.index[:100],cnt_pv['count of non-zero revenue'][:100],'b',\n         markersize=4,linewidth=2,label='count of non-zero revenue per page view')\nplt.legend()\nplt.xlabel('totals.hits')\nplt.ylabel('count of instances')\n#So we have a very similar behaviour between totals.hits and totals.pageviews in relation to both instances \n#and non-zero revenues.\n#Most of users make very little instances, but the non-zero revenues comes users that acess the google plataform \n#more frenquently, around 20 times.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5aded34b5d1a63dc04a87e4fbbb8e095651ffc38"},"cell_type":"markdown","source":"**Last Considerations:**\n\nBefore actually start building a ML model, although now we have some good insights about our data, we still need to deal with missing data, clean the data properlly by dropping some columns and adding some others by feature engineering. So before I end this kernel analisys let's see the percentage of the missing data that we have on our left features:"},{"metadata":{"trusted":true,"_uuid":"69672217f105a414c0d58f246e54392c826d8bef"},"cell_type":"code","source":"#Missing Data on Train Dataset\ntotal = df_train.isnull().sum().sort_values(ascending=False)\npercent = (df_train.isnull().sum()/df_train.isnull().count()).sort_values(ascending=False)\nmissing_data = pd.concat([total, percent], axis=1, keys=['Total', 'Percent'])\nmissing_data.head(20)\n#So there are many features with more than 90% of missing data, so we should look carefully at them and decide wheter to Imput\n#this data or drop them.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"be9af4b6ef2ab574f6f2924c3fec72384f7fcbac"},"cell_type":"markdown","source":"**That's all for now!**\n\nThat's all for this kernel, I hope this analisys might be usefull to some of you, and in about a week I will make another kernel focused on ML models applied to this challenge. Thank's everyone!"},{"metadata":{"_uuid":"d2d5e1e5d83acbf1b5af3c30e036e08103906299"},"cell_type":"markdown","source":""}],"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}