{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"### This kernel is a simple illustration of the previous data leak, forking @Ankit's kernel \"Story Of A Leak\"."},{"metadata":{"_uuid":"e51ec0e7d0566a0b936f5d054b2d36fb7fa4b71a"},"cell_type":"markdown","source":"We can download some leaked data from demo account of Google Merchandise Account (see [here](https://support.google.com/analytics/answer/6367342?hl=en)). In particular, we can find from User Explorer the purchase history of any user (identified by *Client ID*, or *cid*), but here will simply use @Ankit's data  (with a note that it has a problem to be addressed later).\n\nLet's load the external 'leaked' data. We will use only the train set for illusration."},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"import os\nimport json\nimport pandas as pd\nfrom pandas.io.json import json_normalize\n\n#using 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    df = pd.read_csv(csv_path, \n                     converters={column: json.loads for column in JSON_COLUMNS}, \n                     dtype={'fullVisitorId': 'str'}, # Important!!\n                     nrows=nrows)\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 {os.path.basename(csv_path)}. Shape: {df.shape}\")\n    return df\n    \ntrain = load_df()\n\n# Keep only a subset of columns for illustration\ndf_train = train[['fullVisitorId','date','sessionId','visitStartTime','totals.transactionRevenue']]\n\n#Load external data\ntrain_store_1 = pd.read_csv('../input/exported-google-analytics-data/Train_external_data.csv', low_memory=False, skiprows=6, dtype={\"Client Id\":'str'})\ntrain_store_2 = pd.read_csv('../input/exported-google-analytics-data/Train_external_data_2.csv', low_memory=False, skiprows=6, dtype={\"Client Id\":'str'})\ndf_extra = pd.concat([train_store_1, train_store_2], sort=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5093bad0c6c4cb387d447846dcbbaf263c04a051"},"cell_type":"markdown","source":"`df_train` is our training set, and `df_extra` is the external data corresponding to training set's time period.\n\nLet's take a look at the data first."},{"metadata":{"trusted":true,"_uuid":"f9e524b9295d0cb4a5509584e71c2b904b43a876"},"cell_type":"code","source":"df_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"582a529e95a13b5ded9f5e94f674410f27be80e9"},"cell_type":"code","source":"df_extra.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7d05925399c58103501839c06104e1b15522a02e"},"cell_type":"markdown","source":"The part after `_` in `sessionId` in `df_train` is called `visitId`, which is the timestamp of the 1st session of a user. This `visitId` also shows up in  `Client ID`, after a dot `.`.\n\nA few notes:\n1. The part before the `_` in `sessionId` is `fullVisitorId`, or `fid`. We will use this terminology onward.\n2. In general, `Client ID` can take any format, but by default it is a numerical string (randomly generated to distinguish a cookie) and`visitId` joined by dot in between.\n3. Since `visitId` is a timestamp down to second precision, it is rare to see two sessions having same `visitId`. When this happens, we can further cross-check other information of the session, e.g. PV, location etc.\n\nTherefore, given a `sessionId`, we can split it to get the `fullVisitorId` and `visitId`, where `visitId` can be referenced to the `Client ID` in external data, giving us the revenue of a `fullVisitorId`.\n\nReference [here](https://support.google.com/analytics/answer/3437719?hl=en).\n\n\nLets extract `visitId` from both tables."},{"metadata":{"trusted":true,"_uuid":"bd9f6473ba357d017db942a910b8d88110f4cf25"},"cell_type":"code","source":"def get_visitId_by_cid(cid):\n    return cid.split('.')[1]\ndef get_visitId_by_sid(sid):\n    return sid.split('_')[1]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0fc7b841c9b3dc8722fe4d248bbcbfea3da2ed45"},"cell_type":"code","source":"df_train['visitId'] = df_train['sessionId'].apply(get_visitId_by_sid)\ndf_extra['visitId'] = df_extra['Client Id'].apply(get_visitId_by_cid)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"81f3614606f7edb5620048cb4c3a2a5b0cc7331d"},"cell_type":"code","source":"df_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ea37c9884e845520fadbf2aca2452bdf3b65371f"},"cell_type":"code","source":"df_extra.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"483334682d627861de37ff1220c25c9a25a71292"},"cell_type":"markdown","source":"Joining these tables together by `visitId`..."},{"metadata":{"trusted":true,"_uuid":"a27531d6870d2af36b6f8016ed774ee7f37de614"},"cell_type":"code","source":"df_joint = df_train.merge(df_extra, how='inner', left_on='visitId', right_on='visitId')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e1e05584b6e3693d60570a8e539e2fd6ac0f7d19"},"cell_type":"code","source":"df_joint.head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"efc39f815e5c6b73e73e00e634b01d6526ef5c65"},"cell_type":"markdown","source":"You can see that lots of sessions do not have any revenue in train set, but external data says otherwise. Part of the reason is the train data is on session level but external data is on user level, so I should have group-sum the revenue by `fullVisitorId` before joining. Another possibility is the organiser filtered out some  purchases, or multiple users share the same `visitId`, or there exists other problems in our method. But you get the idea. \n\nAnother puzzle is that the revenue from two sides do not match. Let's take a closer look:"},{"metadata":{"trusted":true,"_uuid":"99159abedc91c12b5997723243334ffc5c52f0ad"},"cell_type":"code","source":"df_joint['totals.transactionRevenue'] = pd.to_numeric(df_joint['totals.transactionRevenue']) / 10**6  # convert to float USD. Should have done earlier.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"989db8788b25a7782812a4ebbd719cacea6cc7c6"},"cell_type":"code","source":"# Truncate the table for better view\ndf_trun = df_joint[df_joint['totals.transactionRevenue'] > 0.0][['date', 'Client Id', 'fullVisitorId', 'visitId', 'totals.transactionRevenue', 'Revenue', 'Transactions']]  # take a look at all rows with non-zero revenue ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4d7b21d8200a3b087f51a4a0f496f9b6a7d46dfc"},"cell_type":"code","source":"df_trun.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"314ba7a5698c2509c22a4942201ca95e4988cbb4"},"cell_type":"markdown","source":"Notice that `Revenue` looks close to the double of `totals.transactionRevenue`,  and a lot of `Transactions` are taking value of 2 (even numbers). In fact, this is a well-know issue of GA, where sampling is used if you query a lot of data at same time. This problem affects @Ankit's data too. You can verify this by searching for a Client ID in the User Exploer view, for example `446817349.1472832496` on Sep 2nd 2016, and it will show the correct, unsampled value ($ 117.34). \n\nAlso note that we did not touch the topic of shipping cost and tax, and they may (not) affect the revenues in the two tables. I will leave you to explore.\n\nThat's it. Hope you like it."}],"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}