{"cells":[{"metadata":{"_uuid":"9a620d80d7da0dc7b851405e479a4af6aa13f442"},"cell_type":"markdown","source":"# Google Analytics Customer Revenue Prediction"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\n\nimport matplotlib.pyplot as plt\nimport plotly.offline as py\nimport plotly.graph_objs as go\npy.init_notebook_mode(connected=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"85b8e77759c7e72f0ff6fc5fedba244d3bf6c7d2"},"cell_type":"markdown","source":"Define a function to load the CSV files"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"%%time\n# credit to https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields/notebook\ndef load_df(csv_path='../input/ga-customer-revenue-prediction/train.csv', nrows=None):\n    JSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\n    \n    json = __import__('json')\n    json_normalize = pd.io.json.json_normalize\n    df = pd.read_csv(\n        csv_path,\n        converters={ column: json.loads for column in JSON_COLUMNS },\n        dtype={ 'fullVisitorId': 'str', 'visitId': 'str' },\n        nrows=nrows\n    )\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 {csv_path}. Shape: {df.shape}')\n    return df\n\n# df_train = load_df()\n# df_test = load_df('../input/ga-customer-revenue-prediction/test.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"990f3123d878b2f24b24b83479120d23206c4a4d"},"cell_type":"code","source":"# %%time\n# df_train.to_csv('train-flattened.csv', index=False)\n# df_test.to_csv('test-flattened.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"50aab3cdd4d3f89bf8ebf52b1dcd9e9b8725c154"},"cell_type":"code","source":"%%time\n# these have been saved earlier\ndf_train = pd.read_csv('../input/ga-store-customer-revenue/train-flattened.csv', dtype={ 'fullVisitorId': str, 'visitId': str, 'trafficSource.campaignCode': str },)\ndf_test = pd.read_csv('../input/ga-store-customer-revenue/test-flattened.csv', dtype={ 'fullVisitorId': str, 'visitId': str },)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dafcc5be45eb214e5033a1fb343d3481935e6b17"},"cell_type":"markdown","source":"## Target variable\n`totals.transactionRevenue`\n1. There are 903653 records in the train set, 804684 in the test set\n2. Only 11515 records resulted in revenue, the remaining 892138 records had zero revenue.\n3. Of these, only 9996 visitors (out of 714167 unique visitors) had non-zero revenue. This translates to: only 1.4% of visitors bought something.\n4. From 2016-08 to 2017-08, GStore in this dataset had \\$1,540,071  in revenue, or \\$4,219 per day. With a min spend of \\$0.01, max of \\$23,129, mean of \\$133, and median of \\$49"},{"metadata":{"trusted":true,"_uuid":"eb3ea4c1e7e8cf5d0a7cf40f2de941ba84df988d","_kg_hide-output":false},"cell_type":"code","source":"df_train['totals.transactionRevenue'] = df_train['totals.transactionRevenue'].astype(float)\ndf_train['totals.transactionRevenue'].notnull().sum()\nnp.sum(df_train.groupby('fullVisitorId')['totals.transactionRevenue'].sum() > 0)\ndf_train['totals.transactionRevenue'].sum() / 1000000\n(df_train['totals.transactionRevenue'][df_train['totals.transactionRevenue'] > 0] / 1000000).agg(['min', 'max', 'mean', 'median'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"022504207494a43177e2771a3c8b93c7bb28853e"},"cell_type":"markdown","source":"## Predictors\n1. There are 54 predictos in the train set and 52 in the test set.\n2. Predictors not present in the test set can be removed\n3. Predictors where values are the same for all records can also be removed"},{"metadata":{"trusted":true,"_uuid":"672248d1446e233ddf88501c0b7acd41cbad4ed6"},"cell_type":"code","source":"columns = set(df_train.columns).intersection((set(df_test.columns))) # columns present in both train and test\nprint('Predictors that can be removed:', end=' ')\nfor i in df_train.columns[df_train.nunique(dropna=False) == 1].tolist() + df_test.columns[df_test.nunique(dropna=False) == 1].tolist():\n    if (i in columns):\n        print(i, end=', ')\n        columns.remove(i)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5aff90e1bb9d5392248d0b5cc78a87a771f3e6c3"},"cell_type":"code","source":"def count(var, p=True):\n    tmp = pd.concat([\n        df_train[var].value_counts(dropna=False),\n        df_train[df_train['totals.transactionRevenue'] > 0][var].value_counts(dropna=False),\n        df_test[var].value_counts(dropna=False),\n    ], keys=['train', 'train with revenue', 'test'], axis=1, sort=False)\n    tmp['train %'] = np.round(tmp['train'] / tmp['train'].sum() * 100, 1)\n    tmp['train with revenue %'] = np.round(tmp['train with revenue'] / tmp['train with revenue'].sum() * 100, 1)\n    tmp['test %'] = np.round(tmp['test'] / tmp['test'].sum() * 100, 1)\n    if p: print(tmp.shape)\n    return(tmp)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"83bdf6b57c0e44b2e15d347673c5fe196a410070"},"cell_type":"markdown","source":"### channelGrouping\n- There are 8 unique levels\n- The majority is \"Organic Search\""},{"metadata":{"trusted":true,"_uuid":"3ea270a3f66854eefb31283a9a4389764934e64f","_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"count('channelGrouping')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"98ec6d1b9171777de85b5e05b8dd164e009c897b"},"cell_type":"markdown","source":"### device\n\n#### browser\n- There are 129 of them in both train and test\n- The most popular is Chrome (68.7%), followed by Safari, then Firefox.\n- Some of them are present only in the train or test set and the counts are very low.\n\n#### device.deviceCategory\n- Desktops take up ~ 3 quarters of the train set, but only about 1/3rd in the test set\n- Desktops contributed more than 90% of the total revenue\n\n#### device.operatingSystem\n- 24 levels in total\n- 6 of them: Windows, macintosh, Android, iOS, Linux and Chrome constituted to more than 99% of the market"},{"metadata":{"trusted":true,"_uuid":"47d916e250c931c99e447bcf09edc3c73efa56ed","_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"count('device.browser')\ncount('device.deviceCategory')\ncount('device.operatingSystem')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9b429593d45ccfd154738f7574f888e85cfba2bd"},"cell_type":"markdown","source":"### geoNetwork\n#### geoNetwork.continent, geoNetwork.subContinent\n   - 5 continents + unset\n   - 23 subContinents\n   - America constituted to nearly half of the traffic and contributed to  more than 95% of the total revenues\n   - Europe and Asia had  also nearly half of the traffic, but their combined revenues were 2% of the total revenues\n\n#### geoNetwork.country, geoNetwork.region, geoNetwork.city, geoNetwork.metro\n- 228 cities, 483 regions, 956 cities, 123 metros\n- US has 40% traffic, but ~95% revenue\n- 483 Region: highest traffic came from \"not available\" (56%), followed by California (12%). But the \"not available\" only had ~40% revenue, while California had ~29% revenue. Interestingly, New York also had high revenue (13%) relative to its traffic (2.9%).\n- 956 citites. The city identified as \"not available\" has the highest traffic (56%) with ~ 39% revenue. The next in line is Mountain View with 4.5% traffic, but ~11% revenue.\n- Similary, metro of \"not available\" and \"not set\" have the highest combined traffic (~78%), but only ~42% revenue. Whereas San Francisco-Oakland-San Jose and New York have ~14% traffic but 39% revennue.\n\n#### geoNetwork.networkDomain\n- 41982 network domains\n- 40%  are \"not set\" and \"unknown\", but they contributed to ~61% of the total revenue"},{"metadata":{"trusted":true,"_uuid":"24c81f4180c8166c07b73ca13598299864ed3622","_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"count('geoNetwork.continent')\ncount('geoNetwork.subContinent')\ncount('geoNetwork.country')\ncount('geoNetwork.region')\ncount('geoNetwork.city')\ncount('geoNetwork.metro')\ncount('geoNetwork.networkDomain')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3c2a0805bf160da4b79f12cbb4a271d57ea67025"},"cell_type":"markdown","source":"### trafficSource\n- 77 trafficSource.adContent. Most of them are NaN\n- 4 trafficSource.adwordsClickInfo.adNetworkType. Again most are NaN\n- 59009 trafficSource.adwordsClickInfo.gclId: google click identifier. Very high cardiality, again mostly NaN\n- 2 trafficSource.adwordsClickInfo.isVideoAd. Mostly NaN \n- 12 trafficSource.adwordsClickInfo.page. Mostly NaN\n- 4 trafficSource.adwordsClickInfo.slot. Most NaN\n- 35 trafficSource.campaign. Most of the revenue came from \"not set\". Most of the leels are from the test set.\n- 2 trafficSource.isTrueDirect. NaN had 70% traffic but also did 39% revenue.\n- 5393 trafficSource.keyword: More than half are NaN, the others are mostly \"not provided\"\n- 7 trafficSource.medium. Most revenues came from \"referral\" which makes senses: customers are mote likely to purchase based on referral. However, \"affiliate\" has a rather low revenue given its traffic.\n- 3179 trafficSource.referralPath. Most revenues (53%) came from NaN, 45% from \"/\"\n- 500 trafficSource.source. No NaN. Google took up 44% trafic and made 34% revenues. Youtube did badly, took up 23.5% traffic but generated 0.1% revenue. The best is mall.googleplex.com. It has only 7.3% traffic but generated 44.3% revenues. \"Direct\" aslo generated a heavy slice of revenue (17.7%) with only 15.8% traffic.\n"},{"metadata":{"trusted":true,"_uuid":"7bd154461db34a9bd19873a7faedb0bb499e55b4","_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"count('trafficSource.adContent')\ncount('trafficSource.adwordsClickInfo.adNetworkType')\ncount('trafficSource.adwordsClickInfo.gclId')\ncount('trafficSource.adwordsClickInfo.isVideoAd')\ncount('trafficSource.adwordsClickInfo.page')\ncount('trafficSource.adwordsClickInfo.slot')\ncount('trafficSource.campaign')\ncount('trafficSource.isTrueDirect')\ncount('trafficSource.keyword')\ncount('trafficSource.medium')\ncount('trafficSource.referralPath')\ncount('trafficSource.source')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a0508c94f28b25ec0394827fb053360fc0165673"},"cell_type":"markdown","source":"### totals\n- 2 totals.bounces. Half are 1, half are NaN. It makes senses that when bounce is 0, there is no revenue.\n- totals.hits range from 1 to 500. There is no revenue with just 1 hit. \n- totals.pageviews range from 1 to 500 with NaN. Again, no revenue with just 1 or NaN.\n- totals.newVisits: 1 (true) or NaN (not a new visit). There are more new visits (78%) compared to not-new visits (22%). It makes senses that new visits have lower revenue (39%) compared to non-new visits (61%)"},{"metadata":{"trusted":true,"_uuid":"3ec54b5b092a54c37459477174a4bd567375fea6","_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"count('totals.bounces')\ncount('totals.hits')\ncount('totals.pageviews')\ncount('totals.newVisits')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2b9c22aadb1173b849f2ce0191641046b480281e"},"cell_type":"markdown","source":"### Various Id\n From 903653 records, there are:\n - 902755 sessionId\n - 886303 visitId\n - 714167  fullVisitorId\n \nNone of them have null value. Some of the ids are present in more than 1 record.\n\nSome of the Ids are present in both train & test set.\n- 5 sessionId are in both\n- 5 visitId are in both\n- 7679 fullVisitorId are in both\n- Of these 7679,  about 7% (N = 536) spent money. The combined amount is \\$245,847\n- In comparison, 706448 are only in the train set. Of them, about 1.34% (N = 9460) spent money. The combined amount is \\$1,294,224.\n- So maybe fullVisitorId is a good predictor?"},{"metadata":{"trusted":true,"_uuid":"242497ede3f42adef3b7f24c9934d6640dc83e05"},"cell_type":"code","source":"for i in ['sessionId', 'visitId', 'fullVisitorId']:\n    print(i, 'Nunique:', df_train[i].nunique(), '. Null:', df_train[i].isnull().sum())\n    print('Common between train and test set:', len(set(df_train[i]).intersection(set(df_test[i]))))\n\n# fullVisitorId present in both train and test set\ntmp = list(set(df_train['fullVisitorId']).intersection(set(df_test['fullVisitorId'])))\n# the revenue of these fullVisitorId\ntmp = df_train[df_train['fullVisitorId'].isin(tmp)].groupby('fullVisitorId')['totals.transactionRevenue'].sum()\nprint('Number of fullVisitorId who spent money:', len(tmp[tmp > 0]), '. Total amount spent:', tmp.sum() / 1e6)\n\n# fullVisitorId only present in train\ntmp = list(set(df_train['fullVisitorId']).difference(set(df_test['fullVisitorId'])))\n# the revenue of these fullVisitorId\ntmp = df_train[df_train['fullVisitorId'].isin(tmp)].groupby('fullVisitorId')['totals.transactionRevenue'].sum()\nprint('Number of fullVisitorId only in the train set:', len(tmp), '. Number who spent money:', len(tmp[tmp > 0]), '. Total amount spent:', tmp.sum() / 1e6)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"58562bd09853f90738d2c40f0398f867acd4ddcf"},"cell_type":"markdown","source":"Among the 714167 fullVisitorId\n- 620675 (~87%) only appeared once. 3120 (0.5%) had revenue. Total revenue is \\$237,084 (~15.4% of the total revenue).\n- The remaining 93492 fullVisitorId (~13%) appeared more than once and they made up the remaining ~85% of the total revenue.\n- 58711 (~8.2%) appearched twice. 2356 (4.0%) had revenue. Total spent is \\$216,988 (~14.1% of the total revenue).\n- 1 fullVisitorId appeared 255 times, but spent no money.\n- 1 fullVisitorId appeared 86 times, and spent money in 33 occassions, with a combined spent of \\$2,553.\n- 1 fullVisitorId appeared 278 times, and spent money in 16 occasssions, with a combined spent of \\$77,133. This is about 5% of the total revenue.\n\nIt appears the repeated fullVisitorId were more likely to purchase, up to about ~20 repeats"},{"metadata":{"trusted":true,"_uuid":"21c21752294051f4da4da08271fd26af8ff5b189"},"cell_type":"code","source":"tmp = df_train.groupby('fullVisitorId')['totals.transactionRevenue'].agg(['size', 'count', 'sum']).groupby('size').agg(['size', 'sum'])[[['count', 'size'], ['count', 'sum'], ['sum', 'sum']]]\ntmp.columns = ['size', 'count', 'revenue']\ntmp['revenue'] = tmp['revenue'] / 1e6\ntmp['size %'] = np.round(tmp['size'] / tmp['size'].sum() * 100, 2)\ntmp['count / size %'] = np.round(tmp['count'] / tmp['size'] * 100, 2)\ntmp['revenue %'] = np.round(tmp['revenue'] / tmp['revenue'].sum() * 100, 2)\ntmp","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"550d7d706c594857b7bf27161dd02ce62372b550"},"cell_type":"code","source":"plt.plot(tmp.index, tmp['count / size %']);\nplt.xlabel('Frequency of fullVisitorId')\nplt.ylabel('Percentage of number of transaction with revenue\\nover number of fullVisitorId')\nplt.xlim(0, 50)\nplt.ylim(0, 500)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7c3326dcb43b829cbf7943ae1a11c7cd91a91c6e"},"cell_type":"markdown","source":"### Date & visitStartTime\n- convert to datetime using `pd.to_datetime`\n- Most date and visitStartTime are similar, some conversion from epoch to datetime in visitStartTime resulted in a diference of -1 days, likely due to not using the correct timezone. But this doesn't really matter.\n- Create new columns: month, week, day, weekday, hour based on visitStartTime."},{"metadata":{"trusted":true,"_uuid":"0eed83ddf6a2d939ec9fd127ebbe2dc3fd5800b7"},"cell_type":"code","source":"tmp_date = pd.to_datetime(df_train['date'], format='%Y%m%d')\ntmp_visitStartTime = pd.to_datetime(df_train['visitStartTime'], unit='s')\n(tmp_date.dt.date - tmp_visitStartTime.dt.date).value_counts() # this shows most date and visitStartTime are the same\n\n# new columns\ndf_train['visitStartTime.month'] = tmp_visitStartTime.dt.month\ndf_train['visitStartTime.week'] = tmp_visitStartTime.dt.week\ndf_train['visitStartTime.day'] = tmp_visitStartTime.dt.day\ndf_train['visitStartTime.weekday'] = tmp_visitStartTime.dt.weekday\ndf_train['visitStartTime.hour'] = tmp_visitStartTime.dt.hour\n\n# do the same for test_df\ntmp_date = pd.to_datetime(df_test['date'], format='%Y%m%d')\ntmp_visitStartTime = pd.to_datetime(df_test['visitStartTime'], unit='s')\n(tmp_date.dt.date - tmp_visitStartTime.dt.date).value_counts() # this shows most date and visitStartTime are the same\n\ndf_test['visitStartTime.month'] = tmp_visitStartTime.dt.month\ndf_test['visitStartTime.week'] = tmp_visitStartTime.dt.week\ndf_test['visitStartTime.day'] = tmp_visitStartTime.dt.day\ndf_test['visitStartTime.weekday'] = tmp_visitStartTime.dt.weekday\ndf_test['visitStartTime.hour'] = tmp_visitStartTime.dt.hour","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f92efb58e656a947f5482d8df1fc7bab2c57f1bb"},"cell_type":"markdown","source":"## Plots"},{"metadata":{"trusted":true,"_uuid":"a1190d5133fa295aac53190f3dfd37d56b90dbf3"},"cell_type":"code","source":"def make_button(var, title = '', max_row = 10, df = df_train, target_var = 'totals.transactionRevenue'):\n    # create data for the 'updatemenus' used by plotly\n    # agg data from var ~ target_vaar into size, count, mean, median\n    # return dict()\n    tmp = df[[var, target_var]].fillna(value={ var: -1 }).groupby(var)[target_var].agg(['size', 'sum', 'mean', 'median']) # use fillna for var, as groupby(var) doesn't work with na\n    tmp = tmp.sort_values('size', ascending=False)[:max_row][::-1] # by defaul, take only the top 10 rows ordered by size\n    tmp = {\n        'x': [tmp['size'].values, tmp['sum'].values , tmp['mean'].values, tmp['median'].values],\n        'y': [[str(i) for i in tmp.index.tolist()]] * 4, # str(i) to convert all to string, because some of the indexes are True, False\n    }\n    title = title or var\n    return dict(args=[tmp, { 'title': title }], label=title, method='update') \n\n# plotting\n## data\ntmp = make_button('device.deviceCategory', 'Device Category')\nx = tmp['args'][0]['x']\ny = tmp['args'][0]['y'][0]\n\n## trace\ntraces = [None] * 4\ntraces[0] = (go.Bar(x=x[0], y=y, orientation='h'))\ntraces[1] = (go.Bar(x=x[1], y=y, orientation='h'))\ntraces[2] = (go.Bar(x=x[3], y=y, orientation='h', name='Median')) # median goes first, the resulting bar graph will place median at the bottom\ntraces[3] = (go.Bar(x=x[2], y=y, orientation='h', name='Mean'))\n\n## fig, subplot\nfig = __import__('plotly').tools.make_subplots(1, 3, subplot_titles=['Number of record', 'Total revenue', 'Mean & Median'])\nfor i in range(3): fig.append_trace(traces[i], 1, i + 1)\nfig.append_trace(traces[-1], 1, 3)\n\n## fig, layout\nfig.layout.title = tmp['args'][1]['title']\nfig.layout.showlegend = False\nfig.layout.updatemenus = list([\n    dict(\n        buttons=[make_button(i) for i in [\n            'device.deviceCategory', 'device.operatingSystem', 'device.browser', 'device.isMobile',\n            'geoNetwork.continent', 'geoNetwork.subContinent', 'geoNetwork.country', 'geoNetwork.region', 'geoNetwork.metro', 'geoNetwork.city', 'geoNetwork.networkDomain',\n            'trafficSource.medium', 'trafficSource.campaign', 'trafficSource.isTrueDirect', 'trafficSource.adContent', 'trafficSource.adwordsClickInfo.adNetworkType',\n        ]] + [make_button(i, max_row=31) for i in [\n            'totals.bounces', 'totals.newVisits', 'totals.hits', 'totals.pageviews',\n            'visitStartTime.month', 'visitStartTime.week', 'visitStartTime.day', 'visitStartTime.weekday', 'visitStartTime.hour'\n        ]],\n        direction = 'down',\n        showactive = True,\n        x = 0,\n        xanchor = 'left',\n        y = 1.25,\n        yanchor = 'top' \n    ),\n])\n\n## now plot\npy.iplot(fig)\n\n# clean up for memory\n%xdel make_button\n%xdel tmp\n%xdel traces\n%xdel fig","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7dc27b31e5891bd51fcfbd4a891e60c49dfc087b"},"cell_type":"markdown","source":"- **Device category**: desktop has a higher total revenue compared to mobile & tablet proporrtional to their traffic.\n- **Operating systerm**: Macintosh has a higher revenues even though it has lower traffic compared to Windoes. Chrome OS also has a high revenue relative to its traffic.\n- **Browser**: Chrome has the higest traffic and revenue. Safari has the 2nd highest traffic, but its revenue is comparatively lower based on its traffic.\n- **Is mobile**: Mobile traffic is just a litte bit over 1/3rd of non-mobile, but its revenue is only 1/10th of non-mobile.\n- **Continent & subContinent**: Americas has proportinally higher revenues compared to Asia & Europe. Revenues came mostly from Northern America even though other continents have high traffic.\n- **Country**: again most revenues came from US.\n- **Region**: revenues came mostly from \"not vailable in demo dataset\", followed by California. Interestingly, New York, Texas, Washington & Illinois all have high revenues relative to their traffic.\n- **City**: again revenues came mostly from \"not avilable in demo dataset\". New York, Mountain View, San Francisco, Los Angeles have high revenues relative to their traffic.\n- **Metro**: similar patterns to City.\n- **Network domain**: revneues come predominantly from \"(not set)\". The unknown.unknow has a relatively high traffic, but low revenues.\n- **Traffic source**: mall.googleplex.com generated most revenues, given that it has lower traffic compared to the other. Youtube did poorly given it had high traffic.\n- **Medium**: referral generated a higher revenue than the other. It's understandable. Affiliate generated a much less revenue based on its traffic.\n- **Keyword, campain**: \"(not provided)\", \"(not set)\" have the highest revenue. Nothing much can be said, many of them are \"code\".\n- **Ad content**: Google Merchandise Collection has the highest revenue. Google online store doesn't seem to fare well given its traffic.\n- **gclId**: Google Click Identifier, nothing much can be said.\n- **totals.hit** and **totals.pageviews**: Most only had 1 hit or 1 pageview. Revenues came in more after about ~10 hits or pageviews\n- **Time**:\n  - Nov had the highest traffic, but Dec had the highest purchases (Xmas?)\n  - Week 5, 6, 8, 9, 10, 11, 15, 16, 18, 19, 21 to 25: no traffic, no revenue, strange!\n  - The 4th day of a month had the lowest revenue, mmm\n  - Friday and Saturday had low revenue, people going out?\n  - From 5 AM to 12 PM, revenues were lower. However, time zones may be different."},{"metadata":{"_uuid":"1d28eb49a58654a4a1752afe897ff638cf370ffb"},"cell_type":"markdown","source":"## Feature engineering\n"},{"metadata":{"trusted":true,"_uuid":"639a36fad3688b689d9f8ba851826df7e7b65a0f"},"cell_type":"code","source":"# Convert target `totals.transactionRevenue` with log1p\ndf_train['totals.transactionRevenue'].fillna(0, inplace=True)\ny = np.log1p(df_train['totals.transactionRevenue'])\nplt.hist(y[y > 0], bins=50);","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"980fc124d4aeb4a6de3919a833cfcba37e06072c"},"cell_type":"code","source":"# fillna\nprint('Number of records which are null in the train and test set')\npd.concat([df_train[list(columns)].isnull().sum(0), df_test[list(columns)].isnull().sum(0)], keys=['train', 'test'], axis=1).sort_index()\n\n# fillna with 0\nfor i in ['totals.bounces', 'totals.newVisits', 'totals.pageviews', 'trafficSource.adwordsClickInfo.page']:\n    df_train[i].fillna(0, inplace=True)\n    df_test[i].fillna(0, inplace=True)\n\n# fillna with the word \"NONE\"\nfor i in ['trafficSource.adContent', 'trafficSource.adwordsClickInfo.adNetworkType', 'trafficSource.adwordsClickInfo.gclId', 'trafficSource.adwordsClickInfo.slot', 'trafficSource.keyword', 'trafficSource.referralPath']:\n    df_train[i].fillna('NONE', inplace=True)\n    df_test[i].fillna('NONE', inplace=True)\n\n# change True, False to 1, 0\ndf_train['trafficSource.adwordsClickInfo.isVideoAd'].replace({ np.nan: 1, False: 0 }, inplace=True); df_test['trafficSource.adwordsClickInfo.isVideoAd'].replace({ np.nan: 1, False: 0 }, inplace=True)\ndf_train['trafficSource.isTrueDirect'].replace({ np.nan: 0, True: 1 }, inplace=True); df_test['trafficSource.isTrueDirect'].replace({ np.nan: 0, True: 1 }, inplace=True)\ndf_train['device.isMobile'].replace({ True: 1, False: 0 }, inplace=True); df_test['device.isMobile'].replace({ True: 1, False: 0 }, inplace=True)\n\nprint('Number of records which are null in the train and test set after fillna')\npd.concat([df_train[list(columns)].isnull().sum(0), df_test[list(columns)].isnull().sum(0)], keys=['train', 'test'], axis=1).sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"97ca27fb5009d52d7d28fadf55a9bfd41f21437d"},"cell_type":"code","source":"%%time\nfrom scipy import sparse\nfrom sklearn.preprocessing import OneHotEncoder\n\ndef encode_oh(var, threshold = 0.0005, ohe = OneHotEncoder(handle_unknown='ignore')):\n    levels = df_train[var].value_counts()\n    levels = levels[levels / len(df_train) > 0.0005].index.values.reshape(-1, 1) # need to use the threshold, otherwise the dimensions explode and eat up all the memory\n    ohe.fit(levels)\n    train = ohe.transform(df_train[var].values.reshape(-1, 1))\n    test = ohe.transform(df_test[var].values.reshape(-1, 1))\n    feature_names = [var + '__' + i[3:] for i in ohe.get_feature_names()]\n    return train, test, feature_names\n\n# LabelBinarizer\n# def encode_lb(var, threshold = 0.0001, array = None):\n#     lb = LabelEncoder()\n#     # var, the var to be encoded    \n#     # thresold, default at 0.0001 or 0.01%. Keep columns where at least 0.01% of rows are not 0\n#     # array, prodive an array of categories to be fitted\n#     lb.fit(array or df_train[var])\n#     train = lb.transform(df_train[var])\n#     test = lb.transform(df_test[var])\n#     columns = [var + '__' + str(i) for i in lb.classes_]\n#     columns_to_keep = train.sum(0) / train.sum() > thresold\n#     train = train[:, columns_to_keep]\n#     test = test[:, columns_to_keep]\n#     columns = np.array(columns)[columns_to_keep]    \n#     return train, test, columns\n    \n# predictors\n\n# categorical variables + numeric variables\ntmp = [encode_oh(i) for i in [\n    'channelGrouping',\n    'device.browser', 'device.deviceCategory', 'device.operatingSystem', 'geoNetwork.city', 'geoNetwork.continent', 'geoNetwork.country', 'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region', 'geoNetwork.subContinent',\n    'trafficSource.adContent', 'trafficSource.adwordsClickInfo.adNetworkType', 'trafficSource.adwordsClickInfo.gclId', 'trafficSource.adwordsClickInfo.slot', 'trafficSource.campaign', 'trafficSource.keyword', 'trafficSource.medium', 'trafficSource.referralPath', 'trafficSource.source',\n]] + [(sparse.csr_matrix(df_train[i].values.reshape(-1, 1)), sparse.csr_matrix(df_test[i].values.reshape(-1, 1)), [i]) for i in [\n    'device.isMobile', 'totals.bounces', 'totals.hits', 'totals.newVisits', 'totals.pageviews', 'trafficSource.adwordsClickInfo.isVideoAd', 'trafficSource.adwordsClickInfo.page', 'trafficSource.isTrueDirect', 'visitNumber', 'visitStartTime.month', 'visitStartTime.week', 'visitStartTime.day', 'visitStartTime.weekday', 'visitStartTime.hour'\n]]\n\nX = sparse.hstack([i[0] for i in tmp]).tocsr()\nX_test = sparse.hstack([i[1] for i in tmp])\ncolumns = np.concatenate([i[2] for i in tmp])\n\n# # free up memory\n%xdel sparse\n%xdel encode_oh\n%xdel tmp","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4ea0eca90fe4e8665ef71745dfdee2491559ef84"},"cell_type":"code","source":"%%time\nfrom sklearn.preprocessing import LabelEncoder\ntmp_cats = [\n    'channelGrouping',\n    'device.browser', 'device.deviceCategory', 'device.operatingSystem', 'geoNetwork.city', 'geoNetwork.continent', 'geoNetwork.country', 'geoNetwork.metro', 'geoNetwork.networkDomain', 'geoNetwork.region', 'geoNetwork.subContinent',\n    'trafficSource.adContent', 'trafficSource.adwordsClickInfo.adNetworkType', 'trafficSource.adwordsClickInfo.gclId', 'trafficSource.adwordsClickInfo.slot', 'trafficSource.campaign', 'trafficSource.keyword', 'trafficSource.medium', 'trafficSource.referralPath', 'trafficSource.source',\n    'sessionId', 'visitId', 'fullVisitorId', # fullVisitorId could be useful\n]\ntmp_nums = ['device.isMobile', 'totals.bounces', 'totals.hits', 'totals.newVisits', 'totals.pageviews', 'trafficSource.adwordsClickInfo.isVideoAd', 'trafficSource.adwordsClickInfo.page', 'trafficSource.isTrueDirect', 'visitNumber', 'visitStartTime', 'visitStartTime.month', 'visitStartTime.week', 'visitStartTime.day', 'visitStartTime.weekday', 'visitStartTime.hour']\n\n# Xl: X using labelEncoder\nXl = df_train[tmp_cats + tmp_nums].copy()\nXl_test = df_test[tmp_cats + tmp_nums].copy()\n\nl = LabelEncoder()\nprint('Processing:', end=' ')\nfor i in tmp_cats:\n    print(i, end=', ')\n    # because labelencoder cannot encode unseen labels\n    Xl[i] = l.fit_transform(Xl[i]) + 1 # all existing labels will be +1\n    in_index = Xl_test[i].isin(l.classes_) # select rows in test_df where labels have been seen \n    Xl_test.loc[in_index, i] = l.transform(Xl_test[i][in_index]) + 1\n    Xl_test.loc[~in_index, i] = 0 # all unseen label will be coded 0\n#     l.fit(np.concatenate([Xl[i].values.astype(str), Xl_test[i].values.astype(str)]))\n#     Xl[i] = l.transform(Xl[i].values.astype(str))\n#     Xl_test[i] = l.transform(Xl_test[i].values.astype(str))\n\n# free up memory\n%xdel l\n%xdel tmp_cats\n%xdel tmp_nums","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bc53095a00da2981d63c808d48e199df0bd52a45"},"cell_type":"markdown","source":"## Saving data\nSave these data to be used later"},{"metadata":{"trusted":true,"_uuid":"92ab24d57b26ea3420d8fe6d4756e50aa0fb05ac"},"cell_type":"code","source":"%%time\nimport pickle\n\nfor filename, obj in [\n    ('df_train', df_train), ('df_test', df_test),\n    ('X', X), ('X_test', X_test),\n    ('Xl', Xl), ('Xl_test', Xl_test),\n]:\n    with open(filename + '.pickle', 'wb') as handle: pickle.dump(obj, handle, protocol=pickle.HIGHEST_PROTOCOL)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b63c16da2a27e3d6bf898d838aedd6a4d96a15fc"},"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}