{"cells":[{"metadata":{"_uuid":"4d8fc8b88b458ac60ddfdf99781ff9f17502d8e1"},"cell_type":"markdown","source":"Here's a map of user flow for the training data. It's similar to the [Users Flow](https://support.google.com/analytics/answer/1709395) report shown in Google Analytics. I chose a few demographic features and  mapped arrival through the first few visits. The Sankey Diagram is a great tool for this purpose. It shows the connections across features in addition to the totals seen in a series of bar charts.\n\nI used this as the basis for a 'user-level' data model, shown below."},{"metadata":{"trusted":true,"_uuid":"725d1f3b83b8dae65991509ef6312fa00b444185","_kg_hide-input":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\npd.options.display.max_rows = 500\npd.options.display.max_columns = 100\nimport holoviews as hv\nhv.extension('bokeh')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"afb6f52824697bbef725e5af9922ba9d3b4712dd"},"cell_type":"markdown","source":"### Extract and Prepare\nWe need to deal with the JSON info to get the features of interest. Lucky for us, Pandas does the hard work and it only takes a few lines of code. The output is a nice Pandas dataframe with the unpacked data and a couple of added features."},{"metadata":{"trusted":true,"_uuid":"051c9934ce1d72fd9208702505954e703a0f941c","_kg_hide-input":true},"cell_type":"code","source":"# get base data\ncols = ['fullVisitorId', 'geoNetwork', 'device', 'channelGrouping', 'visitNumber', 'totals', ]\ntrain = pd.read_csv('../input/train.csv', usecols=cols, dtype={'fullVisitorId': str})\ntrain = train[cols]\n\n# unpack json\njsoncols = ['geoNetwork', 'device', 'totals']\ndef unpack(df):\n    for jc in jsoncols:  # parse json\n        flat_df = pd.DataFrame(df.pop(jc).apply(pd.io.json.loads).values.tolist())\n        flat_df.columns = ['{}_{}'.format(jc, c) for c in flat_df.columns]\n        df = df.join(flat_df)\n    return df\ntrain = unpack(train)\n\n# prep\ntrain['revcat'] = np.where(train.totals_transactionRevenue.isnull(), 0, 1) \ntrain.loc[train.visitNumber > 5, 'visitNumber'] = 5\ntrain['visitNumber'] = 'Visit' + train.visitNumber.astype(str) \ntrain.sort_values(['fullVisitorId', 'visitNumber'], inplace=True)\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b47ce784712dabd61d7a3d3458a453416a7bdb74"},"cell_type":"markdown","source":"### Reshape and Graph\nNow we can group by various features and build an array for plotting. You'll see there are two kinds of features in the Sankey. Features toward the left are relatively static from visit to visit. That is, a user will mostly (not always) come from one continent, with one device type, through one channel, etc. Features toward the right are the 'per visit' features. Here I look at simple outcomes - did the user (1) exit, never to return, (2) make a purchase, or (3) return without purchasing."},{"metadata":{"trusted":true,"_uuid":"7b3b4ebf599a52034bfd0e82c31312beced548a5","_kg_hide-input":true,"scrolled":false},"cell_type":"code","source":"# get mostly static features\nstatcols = ['geoNetwork_continent', 'device_deviceCategory', 'channelGrouping', 'visitNumber']\ntrain1 = train[['fullVisitorId'] + statcols].copy()\ntrain1 = train1.groupby('fullVisitorId').first()\ngraph = []\nfor n in range(len(statcols)-1):\n    ngroups = train1.groupby([statcols[n], statcols[n+1]], as_index=True).size()\n    ngroups = ngroups.reset_index()\n    graph.append(ngroups)\nflow_nd1 = np.concatenate(graph)\n\n\n# get per visit features - code is a bit ugly...\ntrain2 = train[['fullVisitorId', 'visitNumber', 'revcat']]\ntrain2 = train2.groupby(['fullVisitorId', 'visitNumber'])['revcat'].max().reset_index()\ndepth = 5\nevcols = ['visitNumber'] + [\"event\"+str(n) for n in range(1,depth)]\n\nfor n in range(1,depth):\n    train2[evcols[n]] = np.where(train2.revcat.shift(-n+1) == 1, \"Purchase\"+str(n), \"NextVisit\"+str(n))\n    train2.loc[(train2.fullVisitorId != train2.fullVisitorId.shift(-n)) & (train2.revcat.shift(-n+1) == 0), \n               evcols[n]] = \"Exit\"+str(n)\ntrain2 = train2.groupby('fullVisitorId').first().drop('revcat', axis=1)\n\nfor n in range(1,depth-1):\n    train2.loc[train2[evcols[n]] == \"Exit\"+str(n), evcols[n+1]] = \"Exit\"+str(n+1)\n\ngraph = []\nfor n in range(1,depth):\n    ngroups = train2.groupby(evcols[(n-1):(n+1)], as_index=True).size()\n    ngroups = ngroups.reset_index()\n    graph.append(ngroups)\n    graph\nflow_nd2 = np.concatenate(graph)\n\n# combine and graph\nflow = np.vstack((flow_nd1, flow_nd2))\ndisplay(flow[0:5])\nhv.Sankey(flow).options(width=800, height=500)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3419ba06e20963a0dd729f49da6b1f30f613c6a0"},"cell_type":"markdown","source":"By comparing various features and visit outcomes you can get a good high-level view of user behavior. \n"},{"metadata":{"_uuid":"fe0969609ec77d9f3b65fada9410a8d7156eaa32"},"cell_type":"markdown","source":"### Create Data Model\nWe can use the Flow Diagram to create a data model. This is a baseline model with just a few added features. If you choose to use the 'date' feature, take a look at the conversion function. It uses a dictionary for lookups that's much faster than pandas parsing for the 730 dates we have here."},{"metadata":{"trusted":true,"_uuid":"e7192e2fe1cd730cecea33b59839bfb59088747b","_kg_hide-input":true},"cell_type":"code","source":"%reset -f","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"2799f29a1668e403afb84b714bc0ef43341cc1ab","_kg_hide-output":true,"_kg_hide-input":true},"cell_type":"code","source":"#%% Starting over - I'll integrate the code later\nimport gc\nimport numpy as np\nimport pandas as pd\n\njson_cols = ['device', 'geoNetwork', 'totals', 'trafficSource']\nnan_list = [\"not available in demo dataset\",\n            \"unknown.unknown\",\n            \"(not provided)\",\n            \"(not set)\"] \nnan_dict = {nl:np.nan for nl in nan_list}\n\n\n#%% get date features (fast)\ndef date_conv(df):\n    # make a lookup table\n    datevals = pd.date_range(start='2016-08-01', end='2018-04-30')\n    datekeys = datevals.astype(str)\n    datekeys = [d.replace('-', '') for d in datekeys]\n    datedict = dict(zip(datekeys, datevals))\n    # lookup\n    df['date'] = df.date.map(datedict)\n    return df\n\n\n#%% unpack\ndef unpack(df):\n    df.drop('sessionId', axis=1, inplace=True)\n    for jc in json_cols:  # parse json\n        flat_df = pd.DataFrame(df.pop(jc).apply(pd.io.json.loads).values.tolist())\n        flat_df.columns = ['{}_{}'.format(jc, c) for c in flat_df.columns]\n        df = df.join(flat_df)\n    ad_df = df.pop('trafficSource_adwordsClickInfo').apply(pd.Series) # handle dict column\n    ad_df.columns = ['tS_adwordsCI_{}'.format(c) for c in ad_df.columns]\n    df = df.join(ad_df)\n    return df\n\n\n#%% process raw data\ndef clean(df):\n    df.drop(['tS_adwordsCI_targetingCriteria', 'totals_visits', 'socialEngagementType'], \n        axis=1, inplace=True)\n    df.replace(nan_dict, inplace=True) # convert disguised NaNs\n    df.dropna(axis=1, how='all', inplace=True) \n    for col in df.columns:\n        if 'totals' in col: # chnage to numeric\n            df[col] = pd.to_numeric(df[col])\n    df.totals_bounces.fillna(value=0, inplace=True)\n    df.totals_newVisits.fillna(value=0, inplace=True)\n    df.trafficSource_isTrueDirect.fillna(value=False, inplace=True)\n    df.tS_adwordsCI_isVideoAd.fillna(value=True, inplace=True)\n    return df\n\n\n#%% main function\ndef allprep(file, numrows=None):\n    df = pd.read_csv(file, dtype={'fullVisitorId': str, 'date': str}, nrows=numrows)\n#     df = date_conv(df)\n    df = unpack(df)\n    df = clean(df)\n    return df\n\n\n#%% run raw data\ntrain = allprep('../input/train.csv')\ntrain['totals_transactionRevenue'].fillna(value=0, inplace=True)\n\ntest = allprep('../input/test.csv')\ntest['totals_transactionRevenue'] = -99\n\ntt = pd.concat([train, test], ignore_index=True, sort=False)\n\ndel train\ndel test\ngc.collect()\n\ncols = list(tt)  # move target to front\ncols.insert(0, cols.pop(cols.index('totals_transactionRevenue')))\ntt = tt.reindex(columns= cols)\n\n\n#reduce some memory (not much)\ntt['totals_newVisits'] =  tt.totals_newVisits.astype(int)\ntt['totals_bounces'] = tt.totals_bounces.astype(int)\ntt['totals_pageviews'].fillna(0, inplace=True)\ntt['totals_pageviews'] = tt.totals_pageviews.astype(int)\n\n# drop for now - later maybe encode\ntt.drop(['device_isMobile', \n'trafficSource_campaignCode', 'tS_adwordsCI_gclId'], axis=1, inplace=True)\n\n#make revenue bins (manual for now)\n# cutpts = [0, 25, 100, 200, 1000, 24000]\ntt['totals_transactionRevenue'] = tt.totals_transactionRevenue/1e06\ntt['revcat'] = 0\ntt.loc[tt.totals_transactionRevenue > 0 , 'revcat'] = 1\n# tt.loc[tt.totals_transactionRevenue > 100 , 'revcat'] = 2\n\n\n# work datetime columns\ntt['visitStartTime'] = pd.to_datetime(tt['visitStartTime'], unit='s')\ntt['month'] = tt.visitStartTime.dt.month\ntt['weekyear'] = tt.visitStartTime.dt.weekofyear\ntt['dayyear'] = tt.visitStartTime.dt.dayofyear\ntt['daymonth'] = tt.visitStartTime.dt.day\ntt['weekday'] = tt.visitStartTime.dt.weekday\ntt['hour'] = tt.visitStartTime.dt.hour\n# tt['dayisdate'] = tt.date.dt.date == tt.visitStartTime.dt.date\n\n# tt['localhour'] based on time zone\n# fix geos\n\n\n# add more features\ntt['hitsperpage'] = tt.totals_hits/tt.totals_pageviews\ntt['adgoogle'] = np.where(tt.trafficSource_adContent.str.contains('google', case=False, regex=False), \n    True, False)\n\n\n# fix index\ntt['dupevisit'] = tt[['fullVisitorId', 'visitId']].duplicated(keep='last')\ntt['newvisitId'] = tt.visitStartTime\ntt.set_index(['fullVisitorId', 'newvisitId'], inplace=True, verify_integrity=True)\n\n\n# clean up columns\ntt.drop(['visitStartTime', 'visitId', 'date'], axis=1, inplace=True)\ncols = list(tt)  # move binned target to front\ncols.insert(0, cols.pop(cols.index('revcat')))\ntt = tt.reindex(columns= cols)\n\n\n#%% get static values \nstatcols = ['channelGrouping',\n            'device_browser',\n            'device_deviceCategory',\n            'device_operatingSystem',\n            'geoNetwork_city',\n            'geoNetwork_continent',\n            'geoNetwork_country',\n            'geoNetwork_metro',\n            'geoNetwork_networkDomain',\n            'geoNetwork_region',\n            'geoNetwork_subContinent']\n\ndyncols = ['visitNumber',\n            'totals_hits',\n            'totals_pageviews',\n            'trafficSource_isTrueDirect',\n            'trafficSource_medium',\n            'trafficSource_referralPath',\n            'trafficSource_source',\n            'tS_adwordsCI_isVideoAd',\n            'month',\n            'weekyear',\n            'dayyear',\n            'daymonth',\n            'weekday',\n            'hour',\n            'hitsperpage']\n\n\ndef reshape(df):\n\n    # convert booleans for fillna later\n    bools = ['tS_adwordsCI_isVideoAd', 'trafficSource_isTrueDirect']\n    for b in bools:\n        df[b] = df[b].astype(int)\n\n\n    #%% get revenue and revcat summed\n    dfrevs = df[['revcat', 'totals_transactionRevenue']]\n    aggdict = {'revcat': ['max'], \n                'totals_transactionRevenue': ['sum']}\n    dfrevs = dfrevs.groupby(level=0).agg(aggdict)\n    dfrevs.columns = pd.Index([e[0] + \"_\" + e[1] for e in \n        dfrevs.columns.tolist()])\n    dfstats = df.groupby(level=0)[statcols].first()\n\n    #%% get dynamic values by visit\n\n    # time differences\n    dfdyns = df[dyncols].sort_index().reset_index()\n\n    dfdyns.loc[dfdyns.visitNumber > 12, 'visitNumber'] = 12 # are we losing something here?\n    # dfdyns['hours_sincelast'] = dfdyns.newvisitId.diff().apply(lambda x: x.seconds/3600)\n    dfdyns['hours_sincelast'] = (dfdyns.newvisitId - dfdyns.newvisitId.shift()).apply(lambda x: x.total_seconds())\n    # dfdyns['hours_sincelast'] = pd.to_timedelta(dfdyns.hours_sincelast)\n\n    dfdyns.loc[dfdyns.fullVisitorId != dfdyns.fullVisitorId.shift(), 'hours_sincelast'] = 0\n    dfdyns['firstvis'] = dfdyns.groupby('fullVisitorId')['newvisitId'].transform('first')\n    dfdyns['days_sincefirst'] = (dfdyns.newvisitId - dfdyns.firstvis).apply(lambda x: x.seconds/3600/24)\n    dfdyns.drop(['newvisitId', 'firstvis'], axis=1, inplace=True)\n\n    # reshape into months as columns\n    dfdyns = dfdyns.groupby(['fullVisitorId', 'visitNumber']).first().unstack() # loses datetimecol\n    dfdyns.columns = pd.Index([e[0] + \"_\" + str(e[1]) for e in dfdyns.columns.tolist()]) \n\n    #%% join\n    df2 = dfrevs.join(dfstats, sort=False)\n    df2 = df2.join(dfdyns, sort=False)\n    return df2\n\n\n#%% from features\n# tt = pd.read_parquet('./input/tt1a.parq')\ntrain = tt[tt.totals_transactionRevenue >= 0]\ntest = tt[tt.totals_transactionRevenue < 0]\nprint(train.shape, test.shape)\n\ntrain = reshape(train)\ntest = reshape(test)\n\ntt3 = pd.concat([train, test], sort=False)\n\ndel tt\ndel train\ndel test\ngc.collect()\n\n# fill nas\nfor c in tt3.columns:\n    if tt3[c].dtype == 'object':\n        tt3[c].fillna('none', inplace=True)\n    else:  \n        tt3[c].fillna(-1, inplace=True)\n\n# tt3.to_parquet('./input/tt1a.parq')\n\n\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a9ef0f7e397bca865236366bf0653f579dea1358"},"cell_type":"markdown","source":"### LightGBM\nThe data structure is probably better suited to other methods. 'll stick with LightGBM here to provide a more direct comparison to other public models."},{"metadata":{"trusted":true,"_uuid":"42ea09c15d7ab50f11e78b8e8b3c3e2fcf7bcfbd","_kg_hide-input":true,"_kg_hide-output":true,"scrolled":true},"cell_type":"code","source":"#%%\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.model_selection import train_test_split, KFold, StratifiedKFold\nfrom sklearn.metrics import roc_auc_score, mean_squared_error\nimport lightgbm as lgb\n\n\n#%% encode string categories\nle = LabelEncoder()\ncatcols = tt3.select_dtypes(['bool', 'object']).columns\nfor c in catcols:\n    tt3[c] = le.fit_transform(tt3[c])\n\n\n# scale as needed\n\n\n#%%\ndef splitdata(df):\n    train = df[df.totals_transactionRevenue_sum >= 0]\n    X = train.drop(['totals_transactionRevenue_sum', 'revcat_max'], axis=1)\n    y = np.log1p(train.totals_transactionRevenue_sum * 1e06)\n    y_strat = train.month_1 # month-based folds\n    test = df[df.totals_transactionRevenue_sum < 0]\n    X_test = test.drop(['totals_transactionRevenue_sum', 'revcat_max'], axis=1)\n    return X, y, y_strat, X_test\n\nX, y, y_strat, X_test = splitdata(tt3)\n\ndel tt3\ngc.collect()\n\n#%% lightgbm\n\nparams = {'objective': 'regression',\n        'metric': 'rmse',\n        'boosting': 'gbdt',\n        'learning_rate': 0.02, \n        'num_leaves': 55, \n        # 'min_data_inleaf': 1000,  \n        'feature_fraction': 0.88, \n        'lambda_l1': 10,  \n        'lambda_l2': 10,\n        'n_jobs': 3}\n\n\n        # 'bagging_fraction': 0.95,\n        # 'bagging_freq': 20, \n        # 'predict_contrib': True, # for shap values\n        # 'max_depth': 5}  #7       # setting this causes hang-ups\n         # 'min_child_weight': 35,\n        # 'min_split_gain': 0.01,       \n\noof_preds = np.zeros_like(y, dtype=float)\ncv = StratifiedKFold(n_splits=3, shuffle=True, random_state=44)  \nfor i, [trn_idx, val_idx] in enumerate(cv.split(X, y_strat)):  # stratify by month of first visit\n    print(\"starting fold {}\".format(i+1))\n    X_train, X_val = X.iloc[trn_idx], X.iloc[val_idx]\n    y_train, y_val = y.iloc[trn_idx], y.iloc[val_idx]\n\n    trainDS = lgb.Dataset(X_train, label=y_train.values)\n    valDS = lgb.Dataset(X_val, label=y_val.values, reference=trainDS)\n\n    evalnums = {}\n    lmod = lgb.train(params, trainDS, num_boost_round=2000, early_stopping_rounds=40,\n        valid_sets=[trainDS, valDS], evals_result=evalnums, verbose_eval=20)\n\n    oof_preds[val_idx] = lmod.predict(X_val)\n\nmean_squared_error(y, oof_preds)**0.5","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"1233794190c8d5b69c8134356cf0fd45e82f3e96"},"cell_type":"code","source":"score = mean_squared_error(y, np.clip(oof_preds, 0, None))**0.5\nprint('RMSE {}'.format(score))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true,"_uuid":"8452deb5c5b8ca6329f6e870a40086da9bdf3db8"},"cell_type":"code","source":"\n\n\n#%%\ntrainDS = lgb.Dataset(X, label=y.values)\n\ntest_preds=np.zeros(X_test.shape[0], dtype=float)\nfor i in range(3):\n    lmodfull = lgb.train(params, trainDS, num_boost_round=1150, \n        valid_sets=[trainDS], evals_result=evalnums, verbose_eval=50)\n    test_preds += lmodfull.predict(X_test)/3\n\n\n\n#%% submit\nsub = pd.read_csv('../input/sample_submission.csv', index_col='fullVisitorId')\nsub.head()\nsub['PredictedLogRevenue'] = np.clip(test_preds, 0, None)\nsub.to_csv('subtimewiselgb002.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"93fccdb5b1eaa3189c520a036a71395cd06873a3"},"cell_type":"markdown","source":"So for only a few features and no tuning this concept seems to be useful. The script shown here has a  CV of  1.580  and should give a Public LB in the mid 1.477's. Good luck!"}],"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"},"varInspector":{"cols":{"lenName":16,"lenType":16,"lenVar":40},"kernels_config":{"python":{"delete_cmd_postfix":"","delete_cmd_prefix":"del ","library":"var_list.py","varRefreshCmd":"print(var_dic_list())"},"r":{"delete_cmd_postfix":") ","delete_cmd_prefix":"rm(","library":"var_list.r","varRefreshCmd":"cat(var_dic_list()) "}},"types_to_exclude":["module","function","builtin_function_or_method","instance","_Feature"],"window_display":false}},"nbformat":4,"nbformat_minor":1}