{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport plotly.express as px","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-04-01T13:51:27.732662Z","iopub.execute_input":"2022-04-01T13:51:27.733108Z","iopub.status.idle":"2022-04-01T13:51:29.263440Z","shell.execute_reply.started":"2022-04-01T13:51:27.733070Z","shell.execute_reply":"2022-04-01T13:51:29.262043Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"articles = pd.read_parquet('../input/hm-fashion-recommendation-parquet/articles.parquet')\nsales = pd.read_parquet('../input/hm-fashion-recommendation-parquet/sales.parquet')\ncustomers = pd.read_parquet('../input/hm-fashion-recommendation-parquet/customers.parquet')","metadata":{"execution":{"iopub.status.busy":"2022-04-01T13:54:47.732711Z","iopub.execute_input":"2022-04-01T13:54:47.733846Z","iopub.status.idle":"2022-04-01T13:54:51.828948Z","shell.execute_reply.started":"2022-04-01T13:54:47.733777Z","shell.execute_reply":"2022-04-01T13:54:51.828228Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This notebook explores how we can easily narrow down the search for the relevant articles for a week.\n\n# Product launches\n\nThere are 105.542 articles in the data. However, not all of them are sold every week. There are about 150-400 new product launches every week.","metadata":{}},{"cell_type":"code","source":"first_product_sales = sales.merge(articles).groupby('product_code', as_index=False).agg(first_sale=('week', 'min'))","metadata":{"execution":{"iopub.status.busy":"2022-03-29T08:30:05.739648Z","iopub.execute_input":"2022-03-29T08:30:05.74082Z","iopub.status.idle":"2022-03-29T08:30:20.956067Z","shell.execute_reply.started":"2022-03-29T08:30:05.740768Z","shell.execute_reply":"2022-03-29T08:30:20.954991Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# first 5 weeks are not representative\npx.bar(first_product_sales[first_product_sales.first_sale>5].groupby('first_sale', as_index=False).size(), x='first_sale', y='size', title='New Products per week')","metadata":{"execution":{"iopub.status.busy":"2022-03-29T08:30:20.957814Z","iopub.execute_input":"2022-03-29T08:30:20.958133Z","iopub.status.idle":"2022-03-29T08:30:22.089528Z","shell.execute_reply.started":"2022-03-29T08:30:20.958092Z","shell.execute_reply":"2022-03-29T08:30:22.088832Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Every week we have about 400-1.000 new articles of which about 50% are new product launches and 50% are new colors/prints/patterns for existing products.","metadata":{}},{"cell_type":"code","source":"first_article_sales = sales.merge(articles).groupby(['article_id', 'product_code'], as_index=False).agg(first_sale=('week', 'min')).merge(first_product_sales.rename(columns={'first_sale': 'first_product_sale'}))","metadata":{"execution":{"iopub.status.busy":"2022-03-29T08:30:22.091305Z","iopub.execute_input":"2022-03-29T08:30:22.091546Z","iopub.status.idle":"2022-03-29T08:30:38.835933Z","shell.execute_reply.started":"2022-03-29T08:30:22.091506Z","shell.execute_reply":"2022-03-29T08:30:38.834996Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"first_article_sales['new_product']=first_article_sales.first_sale==first_article_sales.first_product_sale","metadata":{"execution":{"iopub.status.busy":"2022-03-29T08:30:38.837514Z","iopub.execute_input":"2022-03-29T08:30:38.83784Z","iopub.status.idle":"2022-03-29T08:30:38.845469Z","shell.execute_reply.started":"2022-03-29T08:30:38.837797Z","shell.execute_reply":"2022-03-29T08:30:38.84475Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# first 5 weeks are not representative\npx.bar(first_article_sales[first_article_sales.first_sale>5].groupby(['first_sale', 'new_product'], as_index=False).size(), x='first_sale', y='size', color='new_product', title='New Articles per week')","metadata":{"execution":{"iopub.status.busy":"2022-03-29T08:30:38.846821Z","iopub.execute_input":"2022-03-29T08:30:38.847207Z","iopub.status.idle":"2022-03-29T08:30:38.94749Z","shell.execute_reply.started":"2022-03-29T08:30:38.847163Z","shell.execute_reply":"2022-03-29T08:30:38.943697Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This matches quite nicely the 995 new articles without sales transactions in the data.","metadata":{}},{"cell_type":"code","source":"outer_join = articles.merge(first_article_sales, how = 'outer', indicator = True)\nnew_articles_without_transactions = outer_join[~(outer_join._merge == 'both')].drop('_merge', axis = 1)\nlen(new_articles_without_transactions)","metadata":{"execution":{"iopub.status.busy":"2022-03-29T08:30:38.949042Z","iopub.execute_input":"2022-03-29T08:30:38.949288Z","iopub.status.idle":"2022-03-29T08:30:39.085448Z","shell.execute_reply.started":"2022-03-29T08:30:38.949259Z","shell.execute_reply":"2022-03-29T08:30:39.084886Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Relevant articles per week","metadata":{}},{"cell_type":"markdown","source":"About of the 14.000-25.000 articles of the 105.542 are sold every week. However, it's not obvious which ones have been discontinued or are currently out of stock to narrow down the search.","metadata":{}},{"cell_type":"code","source":"px.bar(sales.groupby('week', as_index=False).agg(unique_articles=('article_id', pd.Series.nunique)), x='week', y='unique_articles', title='Number of unique articles per week')","metadata":{"execution":{"iopub.status.busy":"2022-03-29T08:30:39.086386Z","iopub.execute_input":"2022-03-29T08:30:39.086809Z","iopub.status.idle":"2022-03-29T08:30:42.124006Z","shell.execute_reply.started":"2022-03-29T08:30:39.086774Z","shell.execute_reply":"2022-03-29T08:30:42.123183Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Most likely the articles that are sold in one week have been sold in the week before. Let's check that distribution.","metadata":{}},{"cell_type":"code","source":"sales_per_week = sales.groupby(['article_id', 'week'], as_index=False).agg(unit_sales=('price', 'size')).sort_values('week')\nsales_per_week['last_purchase_week'] = sales_per_week.groupby('article_id').week.diff()\nsales_per_week.loc[sales_per_week.last_purchase_week.isna(), 'last_purchase_week'] = 0\nsales_per_week['last_purchase_week_bin'] = pd.cut(sales_per_week.last_purchase_week, bins=[0, 1, 2, 3, 4, 5, 105], labels=['new', '1 week before', '2 weeks before', '3 weeks before', '4 weeks before', '>=5 weeks before'], right=False)","metadata":{"execution":{"iopub.status.busy":"2022-03-29T08:30:42.125231Z","iopub.execute_input":"2022-03-29T08:30:42.125461Z","iopub.status.idle":"2022-03-29T08:31:06.900172Z","shell.execute_reply.started":"2022-03-29T08:30:42.125434Z","shell.execute_reply":"2022-03-29T08:31:06.89922Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sales_per_week = sales_per_week.groupby(['week', 'last_purchase_week_bin']).agg(unit_sales=('unit_sales', 'sum'), article_count=('article_id', pd.Series.nunique)).reset_index()\nsales_per_week['unit_sales_pct'] = sales_per_week['unit_sales']/sales_per_week.groupby('week').unit_sales.transform('sum')*100","metadata":{"execution":{"iopub.status.busy":"2022-03-29T08:31:06.902156Z","iopub.execute_input":"2022-03-29T08:31:06.902381Z","iopub.status.idle":"2022-03-29T08:31:07.193824Z","shell.execute_reply.started":"2022-03-29T08:31:06.902353Z","shell.execute_reply":"2022-03-29T08:31:07.192877Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Indeed most of the articles have been sold in the week before.","metadata":{}},{"cell_type":"code","source":"px.bar(sales_per_week, x='week', y='article_count', color='last_purchase_week_bin', title='Number of unique articles per week')","metadata":{"execution":{"iopub.status.busy":"2022-03-29T08:31:07.195247Z","iopub.execute_input":"2022-03-29T08:31:07.195593Z","iopub.status.idle":"2022-03-29T08:31:07.291001Z","shell.execute_reply.started":"2022-03-29T08:31:07.195547Z","shell.execute_reply":"2022-03-29T08:31:07.289989Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The spike in articles sold in week 85 that had not been sold in the previous 4 weeks are likely \"offline articles\" that could not be sold due to the COVID19 lockdown and are not available in the online store.\n\nLooking at the actual sales figures, the articles that were last sold a few weeks ago become even more irrelevant.","metadata":{}},{"cell_type":"code","source":"px.bar(sales_per_week, x='week', y='unit_sales', color='last_purchase_week_bin', title='Unit sales per week grouped by week of last purchase')","metadata":{"execution":{"iopub.status.busy":"2022-03-29T08:35:15.09977Z","iopub.execute_input":"2022-03-29T08:35:15.100492Z","iopub.status.idle":"2022-03-29T08:35:15.190621Z","shell.execute_reply.started":"2022-03-29T08:35:15.100448Z","shell.execute_reply":"2022-03-29T08:35:15.18981Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"In fact, this week's new articles and those sold in the previous three weeks already account for more than 99% of all sales. Normally it should be enough to look at only those 20k articles instead of all 105k articles.","metadata":{}},{"cell_type":"code","source":"tmp = (sales_per_week[sales_per_week.week>5].groupby('last_purchase_week_bin').mean()).reset_index()\ntmp['unit_sales_pct_cum'] = tmp['unit_sales_pct'].cumsum()\ntmp['article_count_cum'] = tmp['article_count'].cumsum().astype('int')\ntmp[['last_purchase_week_bin', 'unit_sales_pct', 'unit_sales_pct_cum', 'article_count_cum']]","metadata":{"execution":{"iopub.status.busy":"2022-03-28T21:54:25.071055Z","iopub.execute_input":"2022-03-28T21:54:25.07134Z","iopub.status.idle":"2022-03-28T21:54:25.09591Z","shell.execute_reply.started":"2022-03-28T21:54:25.071309Z","shell.execute_reply":"2022-03-28T21:54:25.094776Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"px.bar(sales_per_week, x='week', y='unit_sales_pct', color='last_purchase_week_bin', title='Relative sales grouped by week of last purchase')","metadata":{"execution":{"iopub.status.busy":"2022-03-28T21:49:35.003847Z","iopub.execute_input":"2022-03-28T21:49:35.004736Z","iopub.status.idle":"2022-03-28T21:49:35.108567Z","shell.execute_reply.started":"2022-03-28T21:49:35.004645Z","shell.execute_reply":"2022-03-28T21:49:35.107156Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Looking at new customers\n\nReducing the number of relevant customers is a little harder, as the number of customers that purchase today and haven't purchased anything in the last 4 weeks is significant and there are a lot of customers in the data.","metadata":{}},{"cell_type":"code","source":"len(customers)","metadata":{"execution":{"iopub.status.busy":"2022-04-01T13:55:02.416682Z","iopub.execute_input":"2022-04-01T13:55:02.416961Z","iopub.status.idle":"2022-04-01T13:55:02.422349Z","shell.execute_reply.started":"2022-04-01T13:55:02.416931Z","shell.execute_reply":"2022-04-01T13:55:02.421449Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sales_per_week = sales.groupby(['customer_id', 'week'], as_index=False).agg(unit_sales=('price', 'size')).sort_values('week')\nsales_per_week['last_purchase_week'] = sales_per_week.groupby('customer_id').week.diff()\nsales_per_week.loc[sales_per_week.last_purchase_week.isna(), 'last_purchase_week'] = 0","metadata":{"execution":{"iopub.status.busy":"2022-04-01T13:59:24.978274Z","iopub.execute_input":"2022-04-01T13:59:24.978671Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sales_per_week['last_purchase_week_bin'] = pd.cut(\n    sales_per_week.last_purchase_week,\n    bins=[0,\n          1, 2,\n          4, 9, 17, 26, 52,\n          205],\n    labels=['new',\n            '1 week before', '2 weeks before',\n            '1 month before', '2-3 months before', '4-6 months before', '6-12 months before',\n            '>=1 year before'], right=False)","metadata":{"execution":{"iopub.status.busy":"2022-04-01T13:59:02.456233Z","iopub.execute_input":"2022-04-01T13:59:02.456774Z","iopub.status.idle":"2022-04-01T13:59:02.752930Z","shell.execute_reply.started":"2022-04-01T13:59:02.456718Z","shell.execute_reply":"2022-04-01T13:59:02.751960Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sales_per_week_plot = sales_per_week.groupby(['week', 'last_purchase_week_bin']).agg(unit_sales=('unit_sales', 'sum'), customer_count=('customer_id', pd.Series.nunique)).reset_index()\nsales_per_week_plot['unit_sales_pct'] = sales_per_week_plot['unit_sales']/sales_per_week_plot.groupby('week').unit_sales.transform('sum')*100","metadata":{"execution":{"iopub.status.busy":"2022-04-01T13:59:02.754448Z","iopub.execute_input":"2022-04-01T13:59:02.754777Z","iopub.status.idle":"2022-04-01T13:59:03.794018Z","shell.execute_reply.started":"2022-04-01T13:59:02.754717Z","shell.execute_reply":"2022-04-01T13:59:03.792978Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"px.bar(sales_per_week_plot, x='week', y='customer_count', color='last_purchase_week_bin', title='Number of unique customers per week')","metadata":{"execution":{"iopub.status.busy":"2022-04-01T13:59:03.796182Z","iopub.execute_input":"2022-04-01T13:59:03.796551Z","iopub.status.idle":"2022-04-01T13:59:04.879824Z","shell.execute_reply.started":"2022-04-01T13:59:03.796519Z","shell.execute_reply":"2022-04-01T13:59:04.878952Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tmp = (sales_per_week_plot[sales_per_week_plot.week>40].groupby('last_purchase_week_bin').mean()).reset_index()\ntmp['unit_sales_pct_cum'] = tmp['unit_sales_pct'].cumsum()\ntmp['customer_count_cum'] = tmp['customer_count'].cumsum().astype('int')\ntmp[['last_purchase_week_bin', 'unit_sales_pct', 'unit_sales_pct_cum', 'customer_count_cum']]","metadata":{"execution":{"iopub.status.busy":"2022-04-01T13:59:04.881332Z","iopub.execute_input":"2022-04-01T13:59:04.881606Z","iopub.status.idle":"2022-04-01T13:59:04.915387Z","shell.execute_reply.started":"2022-04-01T13:59:04.881566Z","shell.execute_reply":"2022-04-01T13:59:04.913980Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Focussing on customers that bought something in the last month would account for 70% of the sold items and cut the number of relevant customers to about 20%.","metadata":{}},{"cell_type":"code","source":"one_months_customers = sales[sales.week.between(104-4, 104)].customer_id.nunique()\nprint(f'Number of customers in a month: {one_months_customers}')\nprint(f'Relative number of customers in a month: {one_months_customers/len(customers):.2%}')","metadata":{"execution":{"iopub.status.busy":"2022-04-01T13:59:04.917734Z","iopub.execute_input":"2022-04-01T13:59:04.918480Z","iopub.status.idle":"2022-04-01T13:59:05.133087Z","shell.execute_reply.started":"2022-04-01T13:59:04.918431Z","shell.execute_reply":"2022-04-01T13:59:05.132097Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The number of new customers seems reasonable in line with the transaction data.","metadata":{}},{"cell_type":"code","source":"outer_join = customers.merge(sales, how = 'outer', indicator = True)\nnew_customers_without_transactions = outer_join[~(outer_join._merge == 'both')].drop('_merge', axis = 1)\nlen(new_customers_without_transactions)","metadata":{"execution":{"iopub.status.busy":"2022-04-01T13:59:05.135404Z","iopub.execute_input":"2022-04-01T13:59:05.136076Z","iopub.status.idle":"2022-04-01T13:59:24.976324Z","shell.execute_reply.started":"2022-04-01T13:59:05.136030Z","shell.execute_reply":"2022-04-01T13:59:24.974987Z"},"trusted":true},"execution_count":null,"outputs":[]}]}