{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"Hello All! \nThis Notebook starts with doing an EDA of Google Analytics Customer Revenue Prediction. Have not yet got to building the model","metadata":{}},{"cell_type":"markdown","source":"I also built a Tableau Dashboard to accompany the data and help visualize it. \nhttps://public.tableau.com/app/profile/cameron.pearl/viz/GoogleAnalyticsDashboard_16343112945570/Dashboard1?publish=yes","metadata":{}},{"cell_type":"code","source":"\nimport seaborn as sns\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport pandas as pd\nimport os\nimport zipfile\nimport json\nfrom statsmodels.stats.proportion import proportions_ztest\nimport plotly.express as px\n!pip install 'fsspec>=0.3.3'\nimport dask.dataframe as dd\n#https://towardsdatascience.com/loading-large-datasets-in-pandas-11bdddd36f7b\nfrom pandas.io.json import json_normalize","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:40:46.328369Z","iopub.execute_input":"2021-10-12T17:40:46.328655Z","iopub.status.idle":"2021-10-12T17:40:56.020449Z","shell.execute_reply.started":"2021-10-12T17:40:46.328585Z","shell.execute_reply":"2021-10-12T17:40:56.019229Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n\n# Code copied from https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields\n#Altered to read csv by chunks to speed up loading time\ndef load_df(csv_path='../input/ga-customer-revenue-prediction/train.csv', nrows=None):\n    \n    chunk_list=[]\n    chunksize = 10 ** 6\n\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    for chunk in pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'}, # Important!!\n                     nrows=nrows,chunksize=chunksize):\n    #    processed_chunk = chunk_processing(chunk) # process the chunks with chunk_processing() function\n        chunk_list.append(chunk) # append the chunks to a list\n    df = pd.concat(chunk_list) # concatenate the list to a dataframe\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,chunksize=chunksize)\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","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:40:56.032975Z","iopub.execute_input":"2021-10-12T17:40:56.033199Z","iopub.status.idle":"2021-10-12T17:40:56.04755Z","shell.execute_reply.started":"2021-10-12T17:40:56.033171Z","shell.execute_reply":"2021-10-12T17:40:56.046947Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df=load_df()","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:40:58.236765Z","iopub.execute_input":"2021-10-12T17:40:58.23704Z","iopub.status.idle":"2021-10-12T17:42:29.365197Z","shell.execute_reply.started":"2021-10-12T17:40:58.237018Z","shell.execute_reply":"2021-10-12T17:42:29.364657Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"percent_missing = df.isnull().sum() * 100 / len(df)\nmissing_value_df = pd.DataFrame({\n                                 'percent_missing': percent_missing,\n                                 'unique_values':df.nunique()})\nmissing_value_df.reset_index(inplace=True)\nmissing_value_df.sort_values(by='percent_missing',ascending=False)\n# Inferences\n# campaign  code can be dropped\n#columns with constant values and no na's can be dropped \n#totals.bounces and \ttotals.newVisits\tand\ttotals.pageviews\tand transaction values are all missing zeros' so those can be imputed","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:42:43.81897Z","iopub.execute_input":"2021-10-12T17:42:43.819155Z","iopub.status.idle":"2021-10-12T17:43:05.553912Z","shell.execute_reply.started":"2021-10-12T17:42:43.819132Z","shell.execute_reply":"2021-10-12T17:43:05.55279Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def init_transform(df):\n\n  df.drop(missing_value_df[(missing_value_df['unique_values']==1) & (missing_value_df['percent_missing']==0)]['index'].tolist(),axis=1,inplace=True)\n\n  df['totals.bounces'] = df['totals.bounces'].fillna(0)\n  df['totals.newVisits'] = df['totals.newVisits'].fillna(0)\n  df['totals.pageviews'] = df['totals.pageviews'].fillna(0)\n\n\n  df['totals.transactionRevenue'] = df['totals.transactionRevenue'].fillna(0)\n  df.drop(['trafficSource.campaignCode','trafficSource.adwordsClickInfo.isVideoAd'],axis=1,inplace=True)\n  df['totals.bounces']=pd.to_numeric(df['totals.bounces'])\n  df['totals.transactionRevenue']=pd.to_numeric(df['totals.transactionRevenue'])\n  df['totals.newVisits']=pd.to_numeric(df['totals.newVisits'])\n  df['totals.pageviews']=pd.to_numeric(df['totals.pageviews'])\n  df['date']=pd.to_datetime(df['date'],format='%Y%m%d')\n  return df\n","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:43:05.585812Z","iopub.execute_input":"2021-10-12T17:43:05.586528Z","iopub.status.idle":"2021-10-12T17:43:05.603941Z","shell.execute_reply.started":"2021-10-12T17:43:05.58646Z","shell.execute_reply":"2021-10-12T17:43:05.60334Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df=init_transform(df)","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:43:05.604783Z","iopub.execute_input":"2021-10-12T17:43:05.605922Z","iopub.status.idle":"2021-10-12T17:43:15.38234Z","shell.execute_reply.started":"2021-10-12T17:43:05.60586Z","shell.execute_reply":"2021-10-12T17:43:15.381811Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#creating a function which takes in a dataframe and variable to group by \n#returns a summary table of metrics\n#Direct conversions is people who are trafficked to Gstore through the group variable and convert in that session\n#Eventual Conversion is a new visitor who either converts in that session or in a future sessions (could be through a different channel)\n#Eventual Conversions measures how good a grouping variable is at building brand awareness and getting the foot in the door\n#Including the raw numbers for conversions for proportion test\n#Percent New sessions is the percent of sessions for the group that are new visitors\ndef describe(tempdf,group,origdf=None):\n\n\n  percent_rev_channel=tempdf.groupby(group).agg({'totals.transactionRevenue': 'sum','totals.bounces':'sum','trafficSource.medium':'count','totals.newVisits': 'sum','totals.pageviews':'median'}).reset_index()\n  percent_rev_channel.rename(columns={\"trafficSource.medium\": \"sessions\"}, inplace = True)\n  percent_rev_channel[\"PerOfSessions\"]=100*  percent_rev_channel['sessions']/ percent_rev_channel['sessions'].sum()\n  percent_rev_channel[\"PerNewSessions\"]=100*percent_rev_channel['totals.newVisits']/percent_rev_channel['sessions']\n  percent_rev_channel['totals.transactionRevenue']=100*percent_rev_channel['totals.transactionRevenue'] / percent_rev_channel['totals.transactionRevenue'].sum()\n  percent_rev_channel['totals.bounces']=100*percent_rev_channel['totals.bounces'] / percent_rev_channel['sessions']\n  directConvert=tempdf.groupby(group)['totals.transactionRevenue'].apply(lambda x: (x>0).sum()).reset_index(name='DirectConversions')\n  percent_rev_channel=percent_rev_channel.merge(directConvert)\n  print(\"check1\")\n  percent_rev_channel['DirectConversionRate']=100*(percent_rev_channel['DirectConversions']/percent_rev_channel['sessions'])\n  newFunnel=tempdf[(tempdf['totals.newVisits']==1)][['fullVisitorId',group]]\n  if origdf is not None:\n    salesPerCust=origdf[origdf['totals.transactionRevenue']>0].groupby('fullVisitorId')['totals.transactionRevenue'].sum().reset_index()\n  else:\n    salesPerCust=tempdf[tempdf['totals.transactionRevenue']>0].groupby('fullVisitorId')['totals.transactionRevenue'].sum().reset_index()\n  print(\"check2\")\n  salesPerCust=salesPerCust.merge(newFunnel)\n  convertedCusts=salesPerCust.groupby(group)['fullVisitorId'].count().reset_index()\n  percent_rev_channel=percent_rev_channel.merge(convertedCusts)\n  percent_rev_channel['eventual_conversions']=percent_rev_channel['fullVisitorId']\n  percent_rev_channel['fullVisitorId']=100*percent_rev_channel['fullVisitorId'] / percent_rev_channel['totals.newVisits']\n  percent_rev_channel.rename(columns={'totals.bounces':'bounceRate','totals.transactionRevenue':'PerOfRev','fullVisitorId': \"eventualConversionRate\",'totals.pageviews':\"avgPageViewsPerSession\"}, inplace = True)\n  \n  return percent_rev_channel\n","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:43:15.383356Z","iopub.execute_input":"2021-10-12T17:43:15.384104Z","iopub.status.idle":"2021-10-12T17:43:15.394975Z","shell.execute_reply.started":"2021-10-12T17:43:15.38408Z","shell.execute_reply":"2021-10-12T17:43:15.39398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Describing pageviews and transactions to get a better idea of two important measures of conversions/engagement\n\nprint(df[['totals.transactionRevenue','totals.pageviews']].describe())\nprint(\"skew\")\nprint(df[['totals.transactionRevenue','totals.pageviews']].skew())\nprint(\"kurtosis\")\nprint(df[['totals.transactionRevenue','totals.pageviews']].kurtosis())\n\nplt.figure(figsize=(8,6))\nplt.scatter(range(df.shape[0]), np.sort(np.log1p(df[\"totals.transactionRevenue\"].values)))\nplt.xlabel('index', fontsize=12)\nplt.ylabel('TransactionRevenue', fontsize=12)\nplt.show()\n# Both Page transaction revenue and pageviews seem to have extreme values towards the tails of \n#their distributions (heavy tails) and will be investigated for outliers\n\n","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:43:15.396833Z","iopub.execute_input":"2021-10-12T17:43:15.397058Z","iopub.status.idle":"2021-10-12T17:43:17.754206Z","shell.execute_reply.started":"2021-10-12T17:43:15.397036Z","shell.execute_reply":"2021-10-12T17:43:17.753478Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Summary metrics by channelGrouping\n\ndf2=describe(df,'channelGrouping')\ncolors = sns.color_palette('pastel')[0:5]\nplt.pie(df2['PerOfRev'], labels = df2['channelGrouping'], colors = colors, autopct='%.0f%%')\nplt.title(\"% of Revenue by Channel\", bbox={'facecolor':'0.8', 'pad':5})\nplt.show()\nplt.pie(df2['sessions'], labels = df2['channelGrouping'], colors = colors, autopct='%.0f%%')\nplt.title(\"% of Sessions by Channel\", bbox={'facecolor':'0.8', 'pad':5})\nplt.show()\n\n#% of sessions that are new visitors\nplt.figure(figsize=(10,4))\nsns.barplot(data=df2,x='channelGrouping',y='PerNewSessions')\n\ndf2\n#insights and the so what \n#1.No traffic is coming through the \"Other\" Channel\n\n#2.While social drives 25% of sessions it drives less than 1% of revenue\n#Not only does it drive less than 1% ands has the second lowest direct conversion rate and eventual conversion rate\n#it also has the highest percentage of new visitor session at 92%\n#So What? Who Cares?: Insight: Huge amount of potential new visitors who make it into the first part into the funnel but are not pursuaded to come back\n#Questions: Why are they not coming back and how do we keep those customers engaged? Are the ads on social sites not targeting the right kind of customers? \n\n#3 Display and Refferal's bring in the most relative revenue for the amount of traffic that is directed to them\n#They also have the highest eventual conversion rate so they do a good job converting new visitors as well\n#For Display I wonder what the cost for the ad is to actually see if its worth it\n#Affiliates has almost 3 times the amount of sessions as display but 5 times less revenue\n","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:43:17.755946Z","iopub.execute_input":"2021-10-12T17:43:17.7568Z","iopub.status.idle":"2021-10-12T17:43:19.46746Z","shell.execute_reply.started":"2021-10-12T17:43:17.756773Z","shell.execute_reply":"2021-10-12T17:43:19.466632Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Now time to set up my null hypothesis. H0=The proportion of conversions between Display and Referral are the same\n#H1=They are not the same\n#Alpha=.05\n#p-value=7.17002128960124e-21\n#we therefore reject H1 so referral conversion rate is statistically higher than display\n#For the record this is why R is better for stat I can do more than a 2 sample proportion test in R\nstat, pval = proportions_ztest(df2[df2['channelGrouping'].isin([\"Referral\",\"Display\"])]['eventual_conversions'], df2[df2['channelGrouping'].isin([\"Referral\",\"Display\"])]['totals.newVisits'], .05)\nprint(stat,pval)","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:43:19.46888Z","iopub.execute_input":"2021-10-12T17:43:19.469083Z","iopub.status.idle":"2021-10-12T17:43:19.478739Z","shell.execute_reply.started":"2021-10-12T17:43:19.469056Z","shell.execute_reply":"2021-10-12T17:43:19.477393Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df3=df[df['channelGrouping'].isin(['Display','Paid Search'])]\ndf3['trafficSource.campaign']=df3.apply(lambda x: x['channelGrouping']+ \" \"+x['trafficSource.campaign'] if x['channelGrouping']==\"Display\" else x['trafficSource.campaign'],axis=1)\nprint(df3.groupby(['trafficSource.campaign']).agg({'date':['max','min']}))\n\ndf3=describe(df3,'trafficSource.campaign',df)\n\nplt.pie(df3['PerOfRev'], labels = df3['trafficSource.campaign'], colors = colors, autopct='%.0f%%')\nplt.title(\"% of Revenue by Channel\", bbox={'facecolor':'0.8', 'pad':5})\nplt.show()\nplt.pie(df3['sessions'], labels = df3['trafficSource.campaign'], colors = colors, autopct='%.0f%%')\nplt.title(\"% of Sessions by Channel\", bbox={'facecolor':'0.8', 'pad':5})\nplt.show()\n#Inferences\n#1. All the campaigns seemed to be live for similar points of time except for retail which sessions only covered 1 month\n#Was it only live for 1 month or longer because sessions are based on if the ad was clicked not if it was shown?\n#2.AW-Apparel and retail both had the lowest number of sessions. \n#If we had data regarding number of impressions we could see whether it was a low ctr relative to the amount of times it was seen\n#Assuming relatively equal number of impressions for each ad campaign this data would suggest unrelevant ads, people who buy apparel\n#or retail might not associate G-Store with those categories. It would also be interesting to see categories of items purchased at the store\n#3. Among the Paid Search Category AW-Dynamic Search Ads Whole Site had the highest amount of sessions and conversion rate \n#Again I would like to see the number of impressions \n#would be it might be cost efficient to only pay for AW Dynamic for paid search....\n#4.While display has the lowest percentage of visitors being new visitors they have the highest eventual conversion rate\n#Follow up question would be how to make display ads more enticing for new visitors to click. \n#Converts both new and existing users","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:43:19.480274Z","iopub.execute_input":"2021-10-12T17:43:19.480616Z","iopub.status.idle":"2021-10-12T17:43:20.436674Z","shell.execute_reply.started":"2021-10-12T17:43:19.480592Z","shell.execute_reply":"2021-10-12T17:43:20.4356Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df4=describe(df[~df['trafficSource.adwordsClickInfo.slot'].isnull()],'trafficSource.adwordsClickInfo.slot',df)\nprint(df[~df['trafficSource.adwordsClickInfo.slot'].isnull()].groupby(['trafficSource.campaign','trafficSource.adwordsClickInfo.slot'])['sessionId'].count())\nstat, pval = proportions_ztest(df4['eventual_conversions'], df4['totals.newVisits'], .05)\nprint(stat,pval)\ndf4\n\n#Investigating Adslot \n#Every campaign is offered in both RHS and TOP\n#Seems top has a much higher percent of sessions and a significantly higher conversion rate conversions\n#Makes sense because Google Discontinued RHS Ads later\n#https://www.simplilearn.com/google-removes-right-side-ads-how-it-affects-online-marketers-article#2_why_google_removed_right_side_ads\n","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:43:20.437892Z","iopub.execute_input":"2021-10-12T17:43:20.438165Z","iopub.status.idle":"2021-10-12T17:43:20.828734Z","shell.execute_reply.started":"2021-10-12T17:43:20.438132Z","shell.execute_reply":"2021-10-12T17:43:20.828097Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#On average newUsers will make their first purchase on their second visit\nnewUsers=df[df['totals.newVisits']==1]['fullVisitorId'].unique().tolist()\ndf[(df['fullVisitorId'].isin(newUsers)) & (df['totals.transactionRevenue']>0)].groupby('fullVisitorId')['visitNumber'].min().reset_index().visitNumber.mean()","metadata":{"execution":{"iopub.status.busy":"2021-10-12T17:43:20.829657Z","iopub.execute_input":"2021-10-12T17:43:20.829897Z","iopub.status.idle":"2021-10-12T17:43:22.367859Z","shell.execute_reply.started":"2021-10-12T17:43:20.829867Z","shell.execute_reply":"2021-10-12T17:43:22.365214Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndescribe(df,'device.deviceCategory',df)","metadata":{"execution":{"iopub.status.busy":"2021-10-12T18:06:54.37564Z","iopub.execute_input":"2021-10-12T18:06:54.376976Z","iopub.status.idle":"2021-10-12T18:06:56.666455Z","shell.execute_reply.started":"2021-10-12T18:06:54.376937Z","shell.execute_reply":"2021-10-12T18:06:56.665459Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Looks like most people purchase in the evening with the lowest purchases happening in the morning\ndf['visitStartTime']=df['visitStartTime'].apply(lambda x: pd.Timestamp(x,unit='s'))\n(df.assign(startHour=lambda x: x['visitStartTime'].dt.hour)).groupby(['startHour'])['totals.transactionRevenue'].sum().reset_index().pipe(lambda x: sns.barplot(data=x,x='startHour',y='totals.transactionRevenue'))\n(df.assign(startHour=lambda x: x['visitStartTime'].dt.hour)).groupby(['startHour'])\n","metadata":{"execution":{"iopub.status.busy":"2021-10-12T18:06:56.667645Z","iopub.execute_input":"2021-10-12T18:06:56.668017Z","iopub.status.idle":"2021-10-12T18:07:02.167912Z","shell.execute_reply.started":"2021-10-12T18:06:56.667993Z","shell.execute_reply":"2021-10-12T18:07:02.166945Z"},"trusted":true},"execution_count":null,"outputs":[]}]}