{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"markdown","source":"# This is going to be epic... sit back, relax and enjoy!"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"Following my previous kernel (https://www.kaggle.com/raddar/towards-de-anonymizing-the-data-some-insights) I was able to reveal that `target` is transformed using log function, and the raw target can be reversed with `2**target` transformation. Moreover, I speculated that the true target is a ratio of `product_sum`. \n\nThis kernel is all about explaining, what kind of ratio we are working with, and why the problem is so hard!\n\nAnd this is going to blow your mind!"},{"metadata":{"trusted":true,"_uuid":"c0a44f47ecbe2aad8b4ae42b956aed8488256cfe"},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\npd.set_option('display.float_format', '{:.10f}'.format)\n\ntrain = pd.read_csv('../input/train.csv')\nhistorical_transactions = pd.read_csv('../input/historical_transactions.csv')\nnew_merchant_transactions = pd.read_csv('../input/new_merchant_transactions.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"53d05684583d5f89d18970f2ef8165c02cf94c60"},"cell_type":"markdown","source":"Let's apply transformations as in previous kernel:"},{"metadata":{"trusted":true,"_uuid":"54faa11f14d59a628ed6b973d513abae23a39076"},"cell_type":"code","source":"new_merchant_transactions['purchase_amount_new'] = np.round(new_merchant_transactions['purchase_amount'] / 0.00150265118 + 497.06,2)\nhistorical_transactions['purchase_amount_new'] = np.round(historical_transactions['purchase_amount'] / 0.00150265118 + 497.06,2)\ntrain['target_raw'] = 2**train['target']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"56a537d617838900e4e8ab6a53d61abbe476c680"},"cell_type":"markdown","source":"Now in order to prove that target is a meaningful ratio, I was looking for some easy cases, such as `card_id` with only one `merchant_id`, with no transactions in`new_merchant_transaction_table`. This helps to isolate the problem and analyze it more thoroughly. \n\nWith some manual exploration, I found that `merchant_id=M_ID_fc7d7969c3` is a perfect candidate to work with."},{"metadata":{"trusted":true,"_uuid":"88dac09fbbd2349117a1f65df85f562376dd4635"},"cell_type":"code","source":"merchant_counts = historical_transactions.groupby(['card_id'])['merchant_id'].nunique().reset_index(name = 'merchant_n')\none_merchant = merchant_counts[merchant_counts['merchant_n']==1].reset_index(drop=True)\ndat = historical_transactions.loc[historical_transactions['card_id'].isin(one_merchant['card_id'])]\ndat = dat.loc[~dat['card_id'].isin(new_merchant_transactions['card_id'])]\ndat = dat.loc[dat.merchant_id=='M_ID_fc7d7969c3'].reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"65657780e8f2e745f93c84bad9efad763aafada1"},"cell_type":"markdown","source":"Let's take a look at a random `card_id` to inspect the transactional history:"},{"metadata":{"trusted":true,"_uuid":"deab46558283ed16c1a40ff95cd98f5d11402dd7"},"cell_type":"code","source":"historical_transactions[historical_transactions.card_id=='C_ID_b3c7ff9e19'].sort_values('purchase_date')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"aca2c16263481f6e4dcc366d8d83b4371c5485e2"},"cell_type":"code","source":"new_merchant_transactions[new_merchant_transactions.card_id=='C_ID_b3c7ff9e19'].sort_values('purchase_date')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e4342b4f2cbf41c70e74a662ec6dfa52c86f9783"},"cell_type":"markdown","source":"The payments are happeninig roughly on the same day of month (maybe recurring credit card payments). We also observe a `purchase_amount` increase from 29.9 to 37.9."},{"metadata":{"_uuid":"9e256892a06bcc8fbff62a05645cb71d1e80dd76"},"cell_type":"markdown","source":"So why the `merchant_id=M_ID_fc7d7969c3` is so special? It turns out it is subscribtion based merchant - most likely internet payments???The `purchase_amount` is distributed in a few very distinct categories:"},{"metadata":{"trusted":true,"_uuid":"1c32b202b9eadf600134cd3fec7527ab32ba94c7"},"cell_type":"code","source":"historical_transactions.loc[historical_transactions.merchant_id=='M_ID_fc7d7969c3'].groupby('purchase_amount_new')['card_id'].count()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"75299db6cad88f8b94d563e9c370cdcab34f2dec"},"cell_type":"markdown","source":"This is a very useful information - remember - the hypothesis was that target is based on some kind of ratios, and the `purchase_amount` ratio seems like a very reasonable candidate."},{"metadata":{"_uuid":"d32b03466d226314cfc589283a37b028ea9274cf"},"cell_type":"markdown","source":"At this point let's summarise what we know:\n\n- merchant is selling subscribtion based products\n- card_id is automatically charged monthly\n- the price for the subscribtion can change (most likely due to upselling)\n- there are 5 products with prices of 19.90, 22.90, 27.90, 29.90 and 37.90; the 1.00 is probably subscribtion activation fee"},{"metadata":{"_uuid":"f6e8304c6c7c782594741a7bc65acbaf10211a15"},"cell_type":"markdown","source":"Now let's try to make some correlations with train `target`:"},{"metadata":{"trusted":true,"_uuid":"f1f89927004c1658110d6dba59cb4679e61a64e3"},"cell_type":"code","source":"dat = dat.merge(train, on = 'card_id')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c7ef2092308831fb8ab4aed44c3c8880ab4c4449"},"cell_type":"code","source":"dat.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d870d962e4318d67fd1b7918fd2bd8c7a1a06d64"},"cell_type":"code","source":"dat.groupby('target_raw')['card_id'].count().reset_index(name='n')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"92cfcdc44f1b25cc2f2d2add8dd1b178239457e0"},"cell_type":"markdown","source":"It seems most of the target values for this specific `card_id` group is concentrated in `target_raw=1`. This is great news, because we were able to somehow cluster this specific group of cards just by some ad hoc rules (single merchant, specific merchant, no new merchant transactions).\n\nLet's take a look at `card_id``s, which `target_raw` is equal to 1:"},{"metadata":{"trusted":true,"_uuid":"eb332bbaa8d150fa7568c8ff57eed7c0d50946b7"},"cell_type":"code","source":"dat.loc[dat['target_raw']==1,['card_id','purchase_date','purchase_amount_new','target_raw']].sort_values(['card_id','purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"576fe6a093d7f8e68cc2d03b0a4fd8d56bf8d5a5"},"cell_type":"markdown","source":"So there is some fluctuation in `purchase_amount`, therefore it is still unclear why the `target_raw==1`."},{"metadata":{"_uuid":"ab21e8563ad69a5a91726cb5570ef09edc6cb507"},"cell_type":"markdown","source":"However, the most interesting part lies not in `target_raw=1`, but actually in other float numbers!\n\nLet's take a step back first and revisit the `purchase_amount` values we extracted earlier, and let's calculate possible ratios based on these values:"},{"metadata":{"trusted":true,"_uuid":"3c68421560f74c063dc8f377127ada42cad7e100"},"cell_type":"code","source":"prices = [19.90, 22.90, 27.90, 29.90, 37.90]\nsorted({ i/j for j in prices for i in prices})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d25084ea7f694c139f1b124c777e69822b0c8829"},"cell_type":"markdown","source":"These numbers are not telling anything at the moment.\n\nBut there is the catch - let's revisit the previous table, were we calculated unique `target_raw` values (compare the list above with the table below).\n\nHint: if you looked closely you may find that some of the list values overlap!"},{"metadata":{"trusted":true,"_uuid":"5fb4feb74095f509fe6eced85876b8eca4dc6e88"},"cell_type":"code","source":"dat.groupby('target_raw')['card_id'].count().reset_index(name='n')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3ad181afe21c9c0faa760a3976805d84d606a213"},"cell_type":"markdown","source":"You will find the values like 0.713261, 1.21834, 1.35842 appearing in both objects!"},{"metadata":{"_uuid":"fe574e01467be21938ab7e573f0a5971b752cc74"},"cell_type":"markdown","source":"This is pretty amazing find and at this point I am 100% sure that the target is a ratio of change in `purchase_amount`.\n\nHowever, there is still a question to ask - how exactly this ratio is calculated?\n\nLet's take this `card_id` for example:"},{"metadata":{"trusted":true,"_uuid":"6b510315eaec9842564f1b2a2b455904e867b960"},"cell_type":"code","source":"dat[dat.card_id=='C_ID_2c8d99614f'].sort_values('purchase_date')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ebf375b3259330d279398a4e6a1e3408b5d08357"},"cell_type":"markdown","source":"If we excluded the subscribtion activation fees (1.00), you would expect the `target_raw` to be 1 (as `purchase_amount` is constant...)\n\nHowever the `target_raw` is 1.2183406082, which in fact is equivalent to `27.90/22.90`.\n\nAmazing! Now we definetly know that the `purchase_amount` for this `card_id` has changed, and it is very likely happened in the future (`month_lag = 1` or `month_lag = 2`). This information we do not observe in `new_merchant_transactions` table, because organizers have excluded that on purpose in data preparation stage - if we would have this information, we could easily track down how target has been calculated, and 0.000 RMSE score would be possible.\n\nWhat does this all mean for `target` calculation?\n\nThe answer is simple:\n## target is equal to the ratio of money spent in the future divided by money spent in the past!\n\nI guess this transfers to all merchants and to all cards in the dataset, but the calculations are a bit harder, as more merchants are involved."},{"metadata":{"_uuid":"9c95653db7c0c67fbb1eaab724e6af26905cab11"},"cell_type":"markdown","source":"# Bonus\n\nNow as we know that we are working with future/history ratios, the outlier value (-33.xxxxx) is actually the meaning of a user not spending a single cent in the future on the historical merchants - mystery solved!\n\nIf i was an organizer, I would have made it to be ~ -10 or so, as RMSE is so sensitive regarding this outler'ish value...\n\nSadly, this also means that these outliers cannot be predicted with the given historical information...\n\n\n\nKnowing all this I can say for sure that the data we have is in fact **real data** and not **simulated one**! In case I am wrong on this one - well done Elo - you did a marvelous job in simulation part (would be hard to make a clean generator like that!)"},{"metadata":{"_uuid":"1cea63e81ad8225e7f8bc3dd453e21363cd48694"},"cell_type":"markdown","source":"# Things that are still not clear..."},{"metadata":{"_uuid":"f18c5403f4a3b9577807a30bc9b372d79ea0d019"},"cell_type":"markdown","source":"Now as we definetly know that target is a ratio of future and past transaction amounts, there are still some unaswered questions, mainly:\n\n- which months are taken into calculation? `month_lag=1` / `month_lag=0`, `month_lag=2` / `month_lag=0`, `month_lag=1` / `min(month_lag=0, month_lag=-1)`, etc. All these are all valid options...\n- does `new_merchant_transactions` have any influence for the `target`?"},{"metadata":{"_uuid":"1b2f790adffb328a67c21c787149eec320d9f910"},"cell_type":"markdown","source":"# What's next?\n\nSo what you can do with this kind of information. Firstly, the rate of `purchase_amount` monthly change for (`card_id, merchant_id`) tuple should be the key features in your models, i.e. average change ratio of (5->4, 4->3, 3->2, 2->1, 1->0); Or features such as `sum(future_purchase_amount)/sum(purchase_amount_lag_-1)`, etc.\n\nThis also allows building new intermediate models, like predicting the historic merchant performance in `month_lag=1` and `month_lag=2` and stacking its predictions to your main model.\n\nThese are the most impactful things I can think of right now, but there could always be more!"},{"metadata":{"_uuid":"57e5163377d66b96d512f59d368fb65fc23a12e1"},"cell_type":"markdown","source":"# Thank you!\n\nif you liked the content don't forget to upvote!"},{"metadata":{"_uuid":"7f4ce7a16ad14fec520e02a7a1e9fbb19c251dc3"},"cell_type":"markdown","source":""},{"metadata":{"_uuid":"38400eaeccce721397de958fbf04fc8988aae7a3"},"cell_type":"markdown","source":""},{"metadata":{"_uuid":"82ff2c548b395043c534599448b88d93dca52e0a"},"cell_type":"markdown","source":""},{"metadata":{"_uuid":"da0cbdcf1a733d0989a15809cc769462bec4bd8c"},"cell_type":"markdown","source":""},{"metadata":{"_uuid":"138ce3292143fbac8bb8566a71d52d04fdbe6ed7"},"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}