{"cells":[{"metadata":{"_uuid":"16e11daef6619789de46d4937f0944070c5512a1"},"cell_type":"markdown","source":"## Google Analytics Customer revenue prediction"},{"metadata":{"_uuid":"75a3b5463d4e14df6cf66ce7d67dc569f8ce356c"},"cell_type":"markdown","source":"In this kernel, I have done exploratory data analysis for [Google Analytics customer revenue prediction challenge](https://www.kaggle.com/c/ga-customer-revenue-prediction). Here , we need to analyze a [Google Merchandise Store](https://www.googlemerchandisestore.com) (also known as GStore, where Google swag is sold) customer dataset to predict revenue per customer. \n\nDatasets provided for training and test are available both in csv as well as google bigquery datasets. Since, I have experience in using SQL for data analytics in professional life, I have tried my hands with __google big query__ for exploratory data analysis here.  I have used some simple SELECT sqls and GROUP BY and aggregage functions like SUM,MIN,MAX & AVG here.\n\n This kernel is inspired from [simple exploration kernel](https://www.kaggle.com/sudalairajkumar/simple-exploration-baseline-ga-customer-revenue) from [SRK](https://www.kaggle.com/sudalairajkumar). His kernels always have been a great source inspiration and learning for me!"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import os\nimport json\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport matplotlib.ticker as ticker\nimport seaborn as sns\ncolor = sns.color_palette()\nplt.style.use('bmh')\nplt.set_cmap('spring')\n%matplotlib inline\nimport bq_helper","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a45e7ed6ecfc13116895fda954591baade44df97"},"cell_type":"code","source":"\n#Here's how we can use the BQHelper library to pull datasets/tables from BigQuery\nga_bq_train = bq_helper.BigQueryHelper(active_project= \"kaggle-public-datasets\", \n                                       dataset_name = \"ga_train_set\")\nga_bq_test = bq_helper.BigQueryHelper(active_project= \"kaggle-public-datasets\", \n                                       dataset_name = \"ga_test_set\")\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e898a03c594492a55d6477ac13da1afe3a6fa35d"},"cell_type":"code","source":"ga_bq_train.list_tables()[:10]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"18ecc33a0f4759aee32c9c117f24d025da584393"},"cell_type":"markdown","source":"It seems dataset on kaggle is factored on the basis of date. Let's check what all columns are available :"},{"metadata":{"trusted":true,"_uuid":"285e7d497ae6617dda79053603011de0e924cf23"},"cell_type":"code","source":"#columns in train dataset\nga_bq_train.table_schema((ga_bq_train.list_tables()[0]))['name'].tolist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"60bbbb101bb613b68fd37fe53c1edb7386f49a2d"},"cell_type":"markdown","source":"There are 186 columns in train dataset which includes columns hidden in json as well."},{"metadata":{"trusted":true,"_uuid":"0ef18bb67d3ac6f11fb762f56ffd3a064933ed35"},"cell_type":"code","source":"#Let's check the size of train dataset total number of records\ntotal_train_query = \"\"\"SELECT  COUNT(*) AS COUNT\n  FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \"\"\"\ntotal_train = ga_bq_train.query_to_pandas(total_train_query)\nprint('Total number of records in training dataset :',total_train['COUNT'][0])\n\n#Let's check the size of test dataset total number of records\ntotal_test_query = \"\"\"SELECT  COUNT(*) AS COUNT\n  FROM `kaggle-public-datasets.ga_test_set.ga_sessions_*` \"\"\"\ntotal_test = ga_bq_test.query_to_pandas(total_test_query)\nprint('Total number of records in test dataset :',total_test['COUNT'][0])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4b6254d6110a321be77ba049d4136393cad53104"},"cell_type":"code","source":"#training data snapshot\nga_bq_train.head(ga_bq_train.list_tables()[0]) #this will show data in first table of bigquery dataset\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d73d0a0ef28609c1c7745fb191450e2e114e602d"},"cell_type":"markdown","source":"## Exploratory Data Analysis"},{"metadata":{"_uuid":"00035fb6aad62d3205f0932457d62e3ce39e3d32"},"cell_type":"markdown","source":"### transactionRevenue - the target variable"},{"metadata":{"trusted":true,"_uuid":"8102ae379c2459f928bdf9a15371bd12d7b01c63"},"cell_type":"code","source":"#exploration of Target Variable using BigQuery\n\ntotalrevenue_per_user_query = \"\"\"SELECT  fullVisitorId, coalesce(SUM( totals.transactionRevenue ),0) AS totalrevenue_per_user\n  FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \n  GROUP BY fullVisitorId\n\"\"\"\ntotalrevenue_per_user = ga_bq_train.query_to_pandas_safe(totalrevenue_per_user_query)\n#plot distribution of transactionRevenue\nplt.figure(figsize=(8,6))\n#scatter plot on natural log of totalrevenue per user\n#original code by : SRK kernel(https://www.kaggle.com/sudalairajkumar/simple-exploration-baseline-ga-customer-revenue)\n\nplt.scatter(range(totalrevenue_per_user.shape[0]), np.sort(np.log1p(totalrevenue_per_user[\"totalrevenue_per_user\"].values)))\nplt.xlabel('index', fontsize=12)\nplt.ylabel('totalRevenue', fontsize=12)\nplt.title('Distribution of totalrevenue per user')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"782e4e98c459107355cee7d4044e406da18a123f"},"cell_type":"markdown","source":"As mentioned in competetion overview and by [SRK](https://www.kaggle.com/sudalairajkumar) in his [kernel](https://www.kaggle.com/sudalairajkumar/simple-exploration-baseline-ga-customer-revenue), it is true that\n__\"The 80/20 rule has proven true for many businesses–only a small percentage of customers produce most of the revenue. As such, marketing teams are challenged to make appropriate investments in promotional strategies.\"__ . The 80/20 rule is nothing but says that 20% customers produce more than 80% of sales(transactionrevenue in our case). From above plot, it seems even much lower."},{"metadata":{"trusted":true,"_uuid":"733d5c0fd9e35aec0d3194a9e1cadc7be0ebead5"},"cell_type":"markdown","source":"### date"},{"metadata":{"_uuid":"65f611e2bf1bbc22bc3aae2695e155c534357b5c"},"cell_type":"markdown","source":"As we have seen above train and test dataset is grouped into tables by dates. Let's check the time period of data available in train and test dataset using column date and bigquery."},{"metadata":{"trusted":true,"_uuid":"95ef916db0233c449a78a837bb1d5873739abed1"},"cell_type":"code","source":"traindate_query = \"\"\"SELECT MIN(date) as startdate,MAX(date) as enddate \n    FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*`\"\"\"\ntraindate_result =  ga_bq_train.query_to_pandas_safe(traindate_query)\nprint('Data in training set is for date',traindate_result['startdate'].iloc[0],'to',traindate_result['enddate'].iloc[0])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"49ad8d6b4e53dea0631f2e40870705f669f83796"},"cell_type":"markdown","source":"One year of data is present in training dataset(1st August,2016 to 1st August,2017)."},{"metadata":{"trusted":true,"_uuid":"58b2677bb529974ba7e174e68b55c1b8fec88987"},"cell_type":"code","source":"testdate_query = \"\"\"SELECT MIN(date) as startdate,MAX(date) as enddate \n    FROM `kaggle-public-datasets.ga_test_set.ga_sessions_*`\"\"\"\ntestdate_result =  ga_bq_test.query_to_pandas_safe(testdate_query)\nprint('Data in test set is for date',testdate_result['startdate'].iloc[0],'to',testdate_result['enddate'].iloc[0])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6e002c15110dbaffdc660162ff55591a0f809813"},"cell_type":"markdown","source":"Around 10 months of data is present in test dataset from August 2017 to April 2018."},{"metadata":{"_uuid":"93f3f07de710ddffd54ea5c646c2c95e71367b38"},"cell_type":"markdown","source":"Here,we need to parse date column to bring it in proper format(yyyy-mm-dd) for visualization first. For it, there is a built-in function [PARSE_DATE](https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#parse_date) in google bigquery. Let's use it and convert date into proper format."},{"metadata":{"trusted":true,"_uuid":"bff5445a57860d97f274cbc8526b52c82570b94c"},"cell_type":"code","source":"revenue_per_date_query = \"\"\"SELECT  PARSE_DATE('%Y%m%d',date) AS DATE,COUNT(*) AS VISIT_COUNT ,coalesce(SUM( totals.transactionRevenue ),0) AS totalrevenue,\ncoalesce(AVG( totals.transactionRevenue ),0) AS avgrevenue\n  FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \n  GROUP BY date\n\"\"\"\nrevenue_per_date = ga_bq_train.query_to_pandas_safe(revenue_per_date_query)\n\nfor i,col in enumerate(['VISIT_COUNT','totalrevenue','avgrevenue']):\n    #fig,axes = plt.subplots(3,1)\n    revenue_per_date.plot(x='DATE',y=col,figsize=(8,6))\n    if col=='VISIT_COUNT' :\n        plt.title('Visits count per day')\n    else :\n        plt.title('Distribution of ' + col + ' per date')\n    plt.xlabel('DATE', fontsize=12)\n    plt.ylabel(col, fontsize=12)\n    ","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e612c2eba791e775cd0fe9067d2c045c5be217bb"},"cell_type":"markdown","source":"In the first plot for visit count, we can see that visits starts increasing from after Oct 2016 and go to its highest peak near by Chritsmas. But this peak doesn't convert into revenue as per total revenue and avg revenue plots. Instead revenue attains its peak in first quarter of 2017 in february and april."},{"metadata":{"trusted":true,"_uuid":"b41207f4d4954a84d5c10612ce3cbfc799d2e09e","_kg_hide-input":true},"cell_type":"code","source":"def categorical_countplot(feature):\n    #this function extract usage count of feature passed using BigQuery and visualize the usage of top 10 feature values based on their counts\n    separate_feat = feature.split('.')[1]\n    query = \"\"\"SELECT \"\"\" + feature + \"\"\", COUNT(*) AS COUNT,coalesce(SUM( totals.transactionRevenue ),0) AS TotalRevenue,\n    coalesce(AVG( totals.transactionRevenue ),0) AS AvgRevenue\n      FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \n      GROUP BY \"\"\" + feature + \"\"\"\n      ORDER BY COUNT(*) DESC\"\"\"\n    feature_count = ga_bq_train.query_to_pandas_safe(query)\n    print('Total number of ' ,separate_feat, ' :',len(feature_count[separate_feat]))\n    #let's visualize the usage of top 10 feature categories using barplot\n    plt.figure(figsize=(16,6))\n    for i,col in enumerate(['COUNT','TotalRevenue','AvgRevenue']) :\n        ax = plt.subplot(1,3,i+1)\n        sns.barplot(x=separate_feat,y=col,data=feature_count.head(10))\n        if col=='COUNT' :\n            plt.title('Visits count per '  + separate_feat)\n        else :\n            plt.title(col + ' per ' + separate_feat)\n        plt.xticks(rotation=90)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0bf3f83cdb4125bae9f296bc8a5613fb14c34fee"},"cell_type":"markdown","source":"### Browser "},{"metadata":{"trusted":true,"_uuid":"96f0198996f508480e43483823ffba56837c51d0"},"cell_type":"code","source":"# exploration of browser variable\ncategorical_countplot('device.browser')\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"290c29fe34f32e55595a09a0bd26c3fa0506cc2e"},"cell_type":"markdown","source":"Most widely used browser is __Google Chrome__, then comes Safari and firefox. Other browsers have very low usage. Total revenue for Google chrome is highest but __average revenue(non-zero revenue) for Firefox is highest__! Seems user visits  from firefox are quality visits(generating revenue)."},{"metadata":{"_uuid":"1de1775160d534657adeb0710cda12ed8209a3ac"},"cell_type":"markdown","source":"### Operating System"},{"metadata":{"trusted":true,"_uuid":"c97bd18d1ac87a56f0b2abbe99a6c984885a1b32"},"cell_type":"code","source":"# exploration of operating system\ncategorical_countplot('device.operatingSystem')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b63ee7e262237dce866917281981566cb03739a0"},"cell_type":"markdown","source":"__Microsoft Windows__ is leading OS __used__ here but __Mac__ users are __generating more revenue__ than others. Average revenue(__non-zero revenue__) per visit is highest for __Chrome OS__."},{"metadata":{"_uuid":"c60a6241759c2d3c91ae585ec6fe973d18f6a8bc"},"cell_type":"markdown","source":"### deviceCategory"},{"metadata":{"trusted":true,"_uuid":"f7e175f07a2e8804740bc7b047f696dd470cbe88"},"cell_type":"code","source":"categorical_countplot('device.deviceCategory')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6505208e09bbf31c7b3c2725759af0f7cee74410"},"cell_type":"markdown","source":"Bar plots are uniform and shows same pattern for usage,totalrevenue and avgrevenue. Most of the visits are from __Desktop computers__ here. So previous graph for operating system also makes sense that usage of Windows and Macintosh OS are significantly higher as comparison to others."},{"metadata":{"trusted":true,"_uuid":"fa462556dae9c8552f4ff9039a6aea627531fd02"},"cell_type":"markdown","source":"#### Continent"},{"metadata":{"trusted":true,"_uuid":"d156bb0355c7d7c0106866efddaac794575912a4"},"cell_type":"code","source":"categorical_countplot('geoNetwork.continent')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b108109208dd09e4ee25522d82e4e3808617b7e8"},"cell_type":"markdown","source":"Visit counts and totalrevenue is highest from __Americas__ but avgrevenue per visit is significantly higher from __Africa__.Also, number of visits from asia and europe are high but they are contibuting much towards generting revenue."},{"metadata":{"_uuid":"0ea05323ef5b6d6136cb6e02349ae2f0b0b4a2a1"},"cell_type":"markdown","source":"### Sub-continent"},{"metadata":{"trusted":true,"_uuid":"36c7727964b1148fb6eb590c77ce4f1b64de0734"},"cell_type":"code","source":"categorical_countplot('geoNetwork.subContinent')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"21b035488402c45d4cd60be4143d73b22fcc5786"},"cell_type":"markdown","source":"Visits are coming mostly from North America. Also, North America is major revenue contributor(seems more than 90%) . But more quality visits(non-zero revenue) are coming from South America and Eastern Asia."},{"metadata":{"_uuid":"17a1c0bf9e34ccd6b18c257660753c4548df9c26"},"cell_type":"markdown","source":"### Country"},{"metadata":{"trusted":true,"_uuid":"a782c94f18b5b115c6a40238769afccd2ff02efb"},"cell_type":"code","source":"categorical_countplot('geoNetwork.country')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"577bd0ba7b4b8117ab17da602927c7a363a9e47a"},"cell_type":"markdown","source":"From graphs above, we can say that United States is cash-cow for Google. Revenue contributed by United States alone constitutes more than 90% of total revenue genearated by all countries.\nSurprisingly, more non-zero revenue visits are coming from Japan as very less number of visits are coming from Japan."},{"metadata":{"_uuid":"faf4986aacc8408e9e03cd4b6a9bfcb9456c39fa"},"cell_type":"markdown","source":"### Traffic Source"},{"metadata":{"trusted":true,"_uuid":"08b5f4ce0900f9f6898a65959597d440210fcccf"},"cell_type":"code","source":"categorical_countplot('trafficSource.source')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e7ff76aa2a9ba0a119f82da78288b9d225ea317f"},"cell_type":"markdown","source":"__Google and youtube__(also owned by google) themselves are major source of visits to its merchandising store. However, __mail.googleplex.com__(seems google own mail server used by its employess, I tried visited it !) is major contributor of revenue.So, __folks working in Google itself are buying more from their merchandise store__. Also, facebook and baidu are not making any significant contributions to revenue(may be google is placing their ads on them!)."},{"metadata":{"_uuid":"82f6fe4506a5f5cb7c2ef2314a416933d1ca64c9"},"cell_type":"markdown","source":"### Traffic medium"},{"metadata":{"trusted":true,"_uuid":"8e61341d6757f45427f846e396cf6dd5bd3967b5"},"cell_type":"code","source":"categorical_countplot('trafficSource.medium')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7777a8688229081f87d9f6f5c92fe493e29d842a"},"cell_type":"markdown","source":"Here in training dataset, 7 Traffic mediums are used. Organic is generating more visits then any other mediums. __Organic__ traffic is Traffic from search engine results that is earned, not paid! I got it from this [link](https://www.smartbugmedia.com/blog/what-is-the-difference-between-direct-and-organic-search-traffic-sources). But __Referral__(traffic that occurs when a user finds you through a site other than a major search engine) traffic is generating more  revenue than other traffic mediums. Although, __CPM__(click-per-impressions) is generating most of the revenue for google merchandise store."},{"metadata":{"trusted":true,"_uuid":"935609e464247f1fc460e943bee39ef8ad169a72"},"cell_type":"markdown","source":"### hits"},{"metadata":{"trusted":true,"_uuid":"53cd4ed85e722f95530a38a2058a5239e3915015"},"cell_type":"code","source":"hits_query = \"\"\"SELECT totals.hits as hits, COUNT(*) AS COUNT,coalesce(SUM( totals.transactionRevenue ),0) AS TotalRevenue,\ncoalesce(AVG( totals.transactionRevenue ),0) AS AvgRevenue\n  FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \n  GROUP BY totals.hits ORDER BY totals.hits\"\"\"\n\nhits_count = ga_bq_train.query_to_pandas_safe(hits_query)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"9627fa1bb00bd80b8f53234af4ae4605ef62b071"},"cell_type":"code","source":"#visits per hit\nplt.figure(figsize=(16,8))\nsns.barplot(x='hits',y='COUNT',data=hits_count.head(50),color='green')\nplt.title('Visits count per hits')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"2c2c6f258ceaa56519204de9d70cdf0b9937c924"},"cell_type":"code","source":"#effect of hits on total revenue and mean revenue\nplt.figure(figsize=(16,8))\nfor i,col in enumerate(['TotalRevenue','AvgRevenue']) :\n        ax = plt.subplot(1,2,i+1)\n        sns.scatterplot(x='hits',y=col,data=hits_count)\n        plt.title(col + ' per hits')\n        scale_y = 1e6\n        ticks_y = ticker.FuncFormatter(lambda x, pos: '{0:g}'.format(x/scale_y))\n        ax.yaxis.set_major_formatter(ticks_y)\n        plt.xticks(rotation=90)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c1506458b6a1a12039e9ef79d58b1cf7ea53782b"},"cell_type":"markdown","source":"We can see that as number of hits increases, the number of visits decreases.Almost, same pattern is observed in graph for total revenue and average revenue.\n\nTotalrevenue steeply decreases with increase in number of hits."},{"metadata":{"_uuid":"d9b58a42c08821f66ee202ba42bec86c49830711"},"cell_type":"markdown","source":"### pageviews"},{"metadata":{"trusted":true,"_uuid":"c2449c8d9f5c76ffa50f4a0784cb49ff75ce1b18"},"cell_type":"code","source":"pageviews_query = \"\"\"SELECT CAST(totals.pageviews as INT64) as pageviews, COUNT(*) AS COUNT,coalesce(SUM( totals.transactionRevenue ),0) AS TotalRevenue,\ncoalesce(AVG( totals.transactionRevenue ),0) AS AvgRevenue\n  FROM `kaggle-public-datasets.ga_train_set.ga_sessions_*` \n  GROUP BY totals.pageviews ORDER BY totals.pageviews\"\"\"\n\npageviews_count = ga_bq_train.query_to_pandas_safe(pageviews_query)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"923921e5edef07e4c8e61bb8f4ecf7a576eb9a75"},"cell_type":"code","source":"#pageviews counts\nplt.figure(figsize=(16,8))\nsns.barplot(x='pageviews',y='COUNT',data=pageviews_count.head(30),color='blue')\nplt.title('Visit count per pageviews')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"a77a58afb3825878c416bb9e15bdc871f03b3138"},"cell_type":"code","source":"#effect of pageviews on total revenue and mean revenue\nplt.figure(figsize=(16,8))\nfor i,col in enumerate(['TotalRevenue','AvgRevenue']) :\n        ax = plt.subplot(1,2,i+1)\n        sns.scatterplot(x='pageviews',y=col,data=pageviews_count)\n        plt.title(col + ' per pageviews')\n        scale_y = 1e6\n        ticks_y = ticker.FuncFormatter(lambda x, pos: '{0:g}'.format(x/scale_y))\n        ax.yaxis.set_major_formatter(ticks_y)\n        plt.xticks(rotation=90)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d04501db4b53cc2bf94cb0b8e6eb015f69d4727f"},"cell_type":"markdown","source":"Here also, similar to hits, same trend is observed i.e. visit count decreases as number of pageviews increaese. Same is the case with graphs for effect of pageviews on totalrevenue and average revenue. "},{"metadata":{"_uuid":"a85700baadd90ece94fc2d3f1340c89e043008a3"},"cell_type":"markdown","source":"Next thing, I will try to do some feature engineering using bigquery SQL! Also, any suggestions/feedbacks are most welcome!"}],"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}