{"cells":[{"metadata":{"_uuid":"7b603c3873a8e20f1987a4ca18887f30a0f714ad"},"cell_type":"markdown","source":"## General information\nThis kernel is dedicated to EDA of Google Analytics Customer Revenue Prediction  competition as well as feature engineering. For now basic data is used and not data from BigQuery.\n\nIn this dataset we can see customers which went to Google Merchandise Store, info about them and their transactions. We need to predict the natural log of the sum of all transactions per user."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport json\nimport bq_helper\nfrom pandas.io.json import json_normalize\nimport seaborn as sns \nimport matplotlib.pyplot as plt\n%matplotlib inline\nplt.style.use('ggplot')\nimport lightgbm as lgb\n\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.model_selection import TimeSeriesSplit\nfrom sklearn.metrics import mean_squared_error","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"# https://www.kaggle.com/julian3833/1-quick-start-read-csv-and-flatten-json-fields\nJSON_COLUMNS = ['device', 'geoNetwork', 'totals', 'trafficSource']\ndef load_df(csv_path='../input/train.csv'):\n\n    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'})\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\n    return df\n        ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4432fc68b07ee992af552288974e52aa31c9fa41"},"cell_type":"code","source":"%%time\ntrain = load_df(\"../input/train.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9c8711b819c6f28a84b37ca0e5b1ceec84d65c74"},"cell_type":"code","source":"%%time\ntest = load_df(\"../input/test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f2dfe028ddcfccbfd0816802bf402d9274a61771"},"cell_type":"markdown","source":"## Data exploration"},{"metadata":{"_uuid":"61737ac2fac6a48027869220e6bf343ba84a0921"},"cell_type":"markdown","source":"Some of columns aren't available in this dataset, let's drop them."},{"metadata":{"trusted":true,"_uuid":"68282c259933877bd186a04779cce6ab51db2109"},"cell_type":"code","source":"cols_to_drop = [col for col in train.columns if train[col].nunique() == 1]\ntrain.drop(cols_to_drop, axis=1, inplace=True)\ntest.drop([col for col in cols_to_drop if col in test.columns], axis=1, inplace=True)\nprint(f'Dropped {len(cols_to_drop)} columns.')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"72709608bb0a2076b355a2d8324e513336815431"},"cell_type":"code","source":"# converting columns into more reasonable format\nfor col in ['visitNumber', 'totals.hits', 'totals.pageviews', 'totals.transactionRevenue']:\n    train[col] = train[col].astype(float)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ea6ecadd5f5ba04eac3fe4e048aa8fcddc066a38"},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a4e9f44ad434a9fa5a85f06ee8c3184d14db2cda"},"cell_type":"code","source":"for col in train.columns:\n    if train[col].isnull().sum() > 0:\n        rate = train[col].isnull().sum() * 100 / train.shape[0]\n        print(f'Column {col} has {rate:.4f}% missing values.')\n    if train[col].dtype == 'object':\n        if (train[col] == 'not available in demo dataset').sum() > 0:\n            rate = (train[col] == 'not available in demo dataset').sum() * 100 / train.shape[0]\n            print(f'Column {col} has {rate:.4f}% values not available in dataset.')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c1451a31fa28b2b2a2497fb8cfcba018a7afa2fe"},"cell_type":"markdown","source":"We can see several thing from this overview:\n- less than 2% of all transactions bring revenue. This percentage is more or less reasonable;\n- some columns have full representation only in BigQuery, so it is necessary to use it;\n- traffic sourse in some cases is unknown, we'll need to find a way to fill these values;\n- there several groups of features: visitor activity, geodata and device info, source of traffic;"},{"metadata":{"_uuid":"c25aae75645a80f9d1c517ab95f51094e838f117"},"cell_type":"markdown","source":"## Feature analysis"},{"metadata":{"_uuid":"8930a92f179ae66a72cf702a8d0ea059299ab785"},"cell_type":"markdown","source":"### Revenue"},{"metadata":{"trusted":true,"_uuid":"134174a89178ac3738ca2f82bb5eefc2cf3377e3"},"cell_type":"code","source":"plt.hist(np.log1p(train.loc[train['totals.transactionRevenue'].isna() == False, 'totals.transactionRevenue']));\nplt.title('Distribution of revenue');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"87556cb48ff9878101d975840f3a7e3e9edba34c"},"cell_type":"markdown","source":"Here we can see the distribution of transaction revenue. But it would be more useful to see a distribution of total revenue per user."},{"metadata":{"trusted":true,"_uuid":"c4bb8b91293b5b23f2a913e013cc003049287bdc"},"cell_type":"code","source":"grouped = train.groupby('fullVisitorId')['totals.transactionRevenue'].sum().reset_index()\ngrouped = grouped.loc[grouped['totals.transactionRevenue'].isna() == False]\nplt.hist(np.log(grouped.loc[grouped['totals.transactionRevenue'] > 0, 'totals.transactionRevenue']));\nplt.title('Distribution of total revenue per user');","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"64e0f05abdd49ce970219caf9e845fc447e86a0a"},"cell_type":"markdown","source":"The plots are quite similar."},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"585c83fa39d173fb073e65fca2214c36216f7487"},"cell_type":"code","source":"counts = train.loc[train['totals.transactionRevenue'] > 0, 'fullVisitorId'].value_counts()\nprint('There are {0} paying users ({1} total) in train data.'.format(len(counts), train['fullVisitorId'].nunique()))\nprint('{0} users ({1:.4f}% of paying) have 1 paid transaction.'.format(np.sum(counts == 1), 100 * np.sum(counts == 1) / len(counts)))\nprint('{0} users ({1:.4f}% of paying) have 2 paid transaction.'.format(np.sum(counts == 2), 100 * np.sum(counts == 2) / len(counts)))\nprint('')\nprint('Count of non-zero transactions per user:')\ncounts.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"344f339f03ba35367ccbd0aadf1efd4bb1a0b631"},"cell_type":"markdown","source":"Most paying users made only 1 transaction, but there are several users, who had a lot of transactions. Regular customers?"},{"metadata":{"trusted":true,"_uuid":"389b30983da6a2ddf980f8812249a48589291104","scrolled":false},"cell_type":"code","source":"train['totals.transactionRevenue'] = train['totals.transactionRevenue'].fillna(0)\ntrain['totals.transactionRevenue'] = np.log1p(train['totals.transactionRevenue'])\nsns.set(rc={'figure.figsize':(20, 16)})\ntrain_ = train.loc[train['totals.transactionRevenue'] > 0.0]\nsns.boxplot(x=\"device.deviceCategory\", y=\"totals.transactionRevenue\", hue=\"channelGrouping\",  data=train_)\nplt.title(\"Total revenue by device category and channel.\");\nplt.xticks(rotation='vertical')\nplt.legend(bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.)\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9165446b3d1c6f1d629df912249c5a7540a6b30f"},"cell_type":"markdown","source":"We can see that revenue comes mostly from desktops. Social, Affiliates and others aren't as profitable as other channels."},{"metadata":{"trusted":true,"_uuid":"181c3ce28f428d02f12b8c9002b8841ce026bfd2"},"cell_type":"code","source":"train['date'] = pd.to_datetime(train['date'].apply(lambda x: str(x)[:4] + '-' + str(x)[4:6] + '-' + str(x)[6:]))\ntest['date'] = pd.to_datetime(test['date'].apply(lambda x: str(x)[:4] + '-' + str(x)[4:6] + '-' + str(x)[6:]))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0c09bfd54139a40ce515b5d3c26ca04cf22ac7c2"},"cell_type":"code","source":"train_['totals.transactionRevenue'].min(), train.shape, train_.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"1f4bb2c008a3c0fd4365ee3f9cde8a425f596696"},"cell_type":"code","source":"train_ = train.loc[train['totals.transactionRevenue'] > 0]\nfig, ax1 = plt.subplots(figsize=(16, 8))\nplt.title(\"Trends of transactions number by paying and non-paying users\");\ntrain.groupby(['date'])['totals.transactionRevenue'].count().plot(color='brown')\nax1.set_ylabel('Transaction count', color='b')\nplt.legend(['Non-paying users'])\nax2 = ax1.twinx()\ntrain_.groupby(['date'])['totals.transactionRevenue'].count().plot(color='gold')\nax2.set_ylabel('Transaction count', color='g')\nplt.legend(['Paying users'], loc=(0.875, 0.9))\nplt.grid(False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"55437131df3e5475b03fc28ac4be3080075f0435"},"cell_type":"code","source":"fig, ax1 = plt.subplots(figsize=(16, 8))\nplt.title(\"Trends transaction count and total value by paying users\")\ntrain.groupby(['date'])['totals.transactionRevenue'].sum().plot(color='purple')\nax1.set_ylabel('Transaction count', color='b')\nplt.legend(['Transaction count'])\nax2 = ax1.twinx()\ntrain_.groupby(['date'])['totals.transactionRevenue'].count().plot(color='gold')\nax2.set_ylabel('Natural logarithm of sum of transactions', color='g')\nplt.legend(['Transaction sum'], loc=(0.875, 0.9))\nplt.grid(False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7b57d6463f180acbe70ae729d6e8afb43fd5301e"},"cell_type":"code","source":"print(f'First date in train set is {train[\"date\"].min()}. Last date in train set is {train[\"date\"].max()}.')\nprint(f'First date in test set is {test[\"date\"].min()}. Last date in test set is {test[\"date\"].max()}.')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a2b5d5fdd5aae2c6b51e91d9c06d874f683cf644"},"cell_type":"markdown","source":"It isn't surprising that trends of sum and count of paid transactions are almost similar. It is worth noticing that there are several periods when the number of non-paying users was significantly higher that the number of paying users, but it didin't influence total sums.\n\nTrain and test period don't intersect."},{"metadata":{"_uuid":"8e388d8356375b20e2f67b57e10a5403e38df522"},"cell_type":"markdown","source":"### Devices\n\nLet's see which devices bring most revenue!"},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"96c86609f9cb289ac639ba4a043b8a77b73d44dc"},"cell_type":"code","source":"fig, ax = plt.subplots(2, 2, figsize = (16, 12))\nprint('Mean revenue per transaction')\nsns.pointplot(x=\"device.browser\", y=\"totals.transactionRevenue\", hue=\"device.isMobile\", data=train_, ax = ax[0, 0])\nsns.pointplot(x=\"device.deviceCategory\", y=\"totals.transactionRevenue\", hue=\"device.isMobile\", data=train_, ax = ax[0, 1])\nsns.pointplot(x=\"device.operatingSystem\", y=\"totals.transactionRevenue\", hue=\"device.isMobile\", data=train_, ax = ax[1, 0])\nsns.pointplot(x=\"device.isMobile\", y=\"totals.transactionRevenue\", data=train_, ax = ax[1, 1])\nplt.xticks(rotation=30)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ccb4decd8cfea52e2db75695e99215b69330c307"},"cell_type":"markdown","source":"It seems that devices on Chrome OS and Macs bring most profit.\n\nThere is an interesting fact - there are desktop devices which are considered to be mobile devices. Is it an error?"},{"metadata":{"_uuid":"e14aee044f918c6adfc99d2232a5cc2049fa00f9"},"cell_type":"markdown","source":"### geoNetwork"},{"metadata":{"trusted":true,"_uuid":"8b5b58601aa7cee99f2c205a18ee481ed75c6fab"},"cell_type":"code","source":"def show_count_sum(df, col):\n    return df.groupby(col).agg({'totals.transactionRevenue': ['count', 'mean']}).sort_values(('totals.transactionRevenue', 'count'), ascending=False).head()\nshow_count_sum(train_, 'geoNetwork.subContinent')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b24b90b3422804043ca60589f3741c1bd1b4a35e"},"cell_type":"markdown","source":"Obviously most transactions come from America"},{"metadata":{"trusted":true,"_uuid":"0bb902651660a8f3871aa2203df2c5ffa8bff017"},"cell_type":"code","source":"show_count_sum(train_.loc[train_['geoNetwork.subContinent'] == 'Northern America'], 'geoNetwork.metro')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0d5b2bf5f082bc971672e71e345d1f49390fe0aa"},"cell_type":"code","source":"show_count_sum(train_.loc[train_['geoNetwork.subContinent'] == 'Northern America'], 'geoNetwork.networkDomain')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dce134066ac48f70da68bb5f9440aff1e58141ca"},"cell_type":"code","source":"show_count_sum(train_.loc[train_['geoNetwork.subContinent'] == 'Northern America'], 'geoNetwork.region')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"844482311810416eba65bf53cc9f5b8fc3726510"},"cell_type":"markdown","source":"Most transactions are from California or New York."},{"metadata":{"_uuid":"c4c3245dd4eb59dbd4b70fda9832b5a97669b7c6"},"cell_type":"markdown","source":"### Traffic source"},{"metadata":{"trusted":true,"_uuid":"5842dfbb6b947fa1157a74049dda33ba0046d67a"},"cell_type":"code","source":"show_count_sum(train_, 'trafficSource.medium')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"936b9879f75066a97ed46f7c0bbc226eb1731fdc"},"cell_type":"markdown","source":"Most of features related to traffic have a lot of missing values, so I'll skip them for now."},{"metadata":{"_uuid":"cacf0d069fa9bf61bf4e65e3a3cc66ae4d247d38"},"cell_type":"markdown","source":"## Feature engineering"},{"metadata":{"_uuid":"32899747432430098439444725fa058732e093f5"},"cell_type":"markdown","source":"At first let's create some features"},{"metadata":{"trusted":true,"_uuid":"a88ec51e08b5370ca22a97c438ec7af1350adc5e"},"cell_type":"code","source":"del grouped, counts, train_","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6df4dfaf9d16fffc65ea102e21b16f0c22f64d5f"},"cell_type":"code","source":"# time based\ntrain['month'] = train['date'].dt.month\ntrain['day'] = train['date'].dt.day\ntrain['weekday'] = train['date'].dt.weekday\n\ntrain['month_unique_user_count'] = train.groupby('month')['fullVisitorId'].transform('nunique')\ntrain['day_unique_user_count'] = train.groupby('day')['fullVisitorId'].transform('nunique')\n\ntest['month'] = test['date'].dt.month\ntest['day'] = test['date'].dt.day\ntest['weekday'] = test['date'].dt.weekday\n\ntest['month_unique_user_count'] = test.groupby('month')['fullVisitorId'].transform('nunique')\ntest['day_unique_user_count'] = test.groupby('day')['fullVisitorId'].transform('nunique')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9ee5c046e72b2064f55523fd081e3b66174422ab"},"cell_type":"code","source":"# device based\n\ntrain['browser_category'] = train['device.browser'] + train['device.deviceCategory']\ntrain['browser_operatingSystem'] = train['device.browser'] + train['device.operatingSystem']\n\ntest['browser_category'] = test['device.browser'] + test['device.deviceCategory']\ntest['browser_operatingSystem'] = test['device.browser'] + test['device.operatingSystem']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"4a0f302e3f94514a0fd3f810f32ce1176fbb3aa4"},"cell_type":"code","source":"train['visitNumber'] = np.log1p(train['visitNumber'])\ntest['visitNumber'] = np.log1p(test['visitNumber'])\n\ntrain['totals.hits'] = np.log1p(train['totals.hits'])\ntest['totals.hits'] = np.log1p(test['totals.hits'].astype(int))\n\ntrain['totals.pageviews'] = np.log1p(train['totals.pageviews'].fillna(0))\ntest['totals.pageviews'] = np.log1p(test['totals.pageviews'].astype(float).fillna(0))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0e56c9299685638d951a957a898f0ebe06023d93"},"cell_type":"markdown","source":"### Feature processing"},{"metadata":{"trusted":true,"_uuid":"32a91ae853c9487776f7d2b48821334a2e70035f"},"cell_type":"code","source":"num_cols = ['visitNumber', 'totals.hits', 'totals.pageviews', 'month_unique_user_count', 'day_unique_user_count']\nno_use = [\"visitNumber\", \"date\", \"fullVisitorId\", \"sessionId\", \"visitId\", \"visitStartTime\", 'totals.transactionRevenue', 'trafficSource.referralPath']\ncat_cols = [col for col in train.columns if col not in num_cols and col not in no_use]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"68f5dc8ea1ca30d36f37d43afa7ab0dccbd0ea70"},"cell_type":"code","source":"for col in cat_cols:\n    print(col)\n    lbl = LabelEncoder()\n    lbl.fit(list(train[col].values.astype('str')) + list(test[col].values.astype('str')))\n    train[col] = lbl.transform(list(train[col].values.astype('str')))\n    test[col] = lbl.transform(list(test[col].values.astype('str')))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3ffc62507a2adf81c8f2c1d4b9c65a7aac9dffe4"},"cell_type":"code","source":"train = train.sort_values('date')\nX = train.drop(no_use, axis=1)\ny = train['totals.transactionRevenue']\nX_test = test.drop([col for col in no_use if col in test.columns], axis=1)\n# I use TimeSeriesSplit as we have time series\ntscv = TimeSeriesSplit(n_splits=5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"58e6ff71d4671b1285c10a3d3ecfad927a9a95c7"},"cell_type":"code","source":"params = {\"objective\" : \"regression\", \"metric\" : \"rmse\",\n              \"num_leaves\" : 32, \"learning_rate\" : 0.05, \"bagging_fraction\" : 0.8, \"feature_fraction\" : 0.8, \"bagging_frequency\" : 5}\n\n# Cleaning and defining parameters for LGBM\nmodel = lgb.LGBMRegressor(**params, n_estimators = 20000, nthread = 4, n_jobs = -1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4630626020a7ade8c524654582a140832270aa61","scrolled":true},"cell_type":"code","source":"prediction = np.zeros(test.shape[0])\n\nfor fold_n, (train_index, test_index) in enumerate(tscv.split(X)):\n    print('Fold:', fold_n)\n    X_train, X_valid = X.iloc[train_index], X.iloc[test_index]\n    y_train, y_valid = y.iloc[train_index], y.iloc[test_index]\n    \n\n    model.fit(X_train, y_train, \n            eval_set=[(X_train, y_train), (X_valid, y_valid)], eval_metric='rmse',\n            verbose=100, early_stopping_rounds=100)\n    \n    y_pred = model.predict(X_test, num_iteration=model.best_iteration_)\n    prediction += y_pred\n\nprediction /= 5","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"691716d64502a3c43fd4fe826682ea59a7dc78dc"},"cell_type":"code","source":"lgb.plot_importance(model, max_num_features=30);","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"12ff6144f64ee25fc8bc0e0b65b6bd9574423811"},"cell_type":"code","source":"test[\"PredictedLogRevenue\"] = np.expm1(prediction)\nsub = test.groupby(\"fullVisitorId\").agg({\"PredictedLogRevenue\" : \"sum\"}).reset_index()\nsub[\"PredictedLogRevenue\"] = np.log1p(sub[\"PredictedLogRevenue\"])\nsub[\"PredictedLogRevenue\"] = sub[\"PredictedLogRevenue\"].apply(lambda x : 0.0 if x < 0 else x)\nsub[\"PredictedLogRevenue\"] = sub[\"PredictedLogRevenue\"].fillna(0.0)\nsub.to_csv(\"lgb.csv\", index=False)","execution_count":null,"outputs":[]}],"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}