{"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":"markdown","source":"\n## EDA\n\nFor this EDA , lets skim thru the transaction data and identify different product  and customer behaviours that we can identify from the given data so they can be used as features while creating different recommendation models\n\n* [Read Data](#section-one)\n* [What is the Active Customer Base?](#custbase)\n* [Does customer always buy products that are less expensive?](#productcust)\n* [Which Segment of Customers are most sticking to the platform?](#custseg)\n* [Does markdown affect the buying pattern of customers?](#buypattern)","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport matplotlib.pyplot as plt\nimport plotly.express as px\nimport numpy as np\nimport gc\npd.set_option('display.max_colwidth', None)","metadata":{"execution":{"iopub.status.busy":"2022-03-06T00:53:19.505323Z","iopub.execute_input":"2022-03-06T00:53:19.506284Z","iopub.status.idle":"2022-03-06T00:53:19.512688Z","shell.execute_reply.started":"2022-03-06T00:53:19.506221Z","shell.execute_reply":"2022-03-06T00:53:19.511312Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id=\"section-one\"></a>\n### Read Data","metadata":{}},{"cell_type":"code","source":"def read_data():\n    data=pd.read_parquet('../input/hm2022-low-memory-fast-loading/transactions_train.parquet', engine='pyarrow')\n    data['price_band']=pd.qcut(data['price'], q=4, labels=['low','medium','high','very high'])\n    data['price']=data['price'].astype('float32')\n    data['sales_channel_id']=data['sales_channel_id'].astype('int32')\n    return data\ndef read_customer_data():\n    data=pd.read_parquet('../input/hm2022-low-memory-fast-loading/customers.parquet', engine='pyarrow')\n    data['FN']=data['FN'].astype('float32')\n    data['Active']=data['Active'].astype('float32')\n    data['age'].fillna(0, inplace=True)\n    data['age']=data['age'].astype('int32')\n    return data","metadata":{"_kg_hide-input":false,"execution":{"iopub.status.busy":"2022-03-06T00:53:22.191467Z","iopub.execute_input":"2022-03-06T00:53:22.192042Z","iopub.status.idle":"2022-03-06T00:53:22.198864Z","shell.execute_reply.started":"2022-03-06T00:53:22.192004Z","shell.execute_reply":"2022-03-06T00:53:22.198059Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id=\"custbase\"></a>\n### What is the Active Customer Base?\n\nActive Customers are customers who have bought anything with the platform in the last 365 days. We will calculate based on the last training date available.","metadata":{}},{"cell_type":"code","source":"cust_data=read_customer_data()\ntrans_data=read_data()\n","metadata":{"execution":{"iopub.status.busy":"2022-03-05T23:39:14.598540Z","iopub.execute_input":"2022-03-05T23:39:14.598965Z","iopub.status.idle":"2022-03-05T23:39:38.126168Z","shell.execute_reply.started":"2022-03-05T23:39:14.598928Z","shell.execute_reply":"2022-03-05T23:39:38.125551Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"unique_cust=len(trans_data[trans_data['t_dat'] >= '2019-09-22']['customer_id'].unique().tolist())\ntotal_cust=cust_data.shape[0]\nactive_customers=unique_cust*100/total_cust","metadata":{"execution":{"iopub.status.busy":"2022-03-05T23:48:59.176990Z","iopub.execute_input":"2022-03-05T23:48:59.177481Z","iopub.status.idle":"2022-03-05T23:49:10.126790Z","shell.execute_reply.started":"2022-03-05T23:48:59.177448Z","shell.execute_reply":"2022-03-05T23:49:10.124189Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del cust_data,trans_data\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-03-05T23:51:01.033897Z","iopub.execute_input":"2022-03-05T23:51:01.034199Z","iopub.status.idle":"2022-03-05T23:51:01.329913Z","shell.execute_reply.started":"2022-03-05T23:51:01.034167Z","shell.execute_reply":"2022-03-05T23:51:01.328984Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<b>The current Active Customer Base for the platform is 72%</b>","metadata":{}},{"cell_type":"markdown","source":"###  Seasonal Trends analysis of Sales","metadata":{}},{"cell_type":"code","source":"trans_data=read_data()\ntrans_data['t_dat']=pd.to_datetime(trans_data['t_dat'])\ntrans_data['YearMonth'] = trans_data['t_dat'].apply(lambda x:x.strftime('%Y%m'))","metadata":{"execution":{"iopub.status.busy":"2022-03-06T00:59:04.013031Z","iopub.execute_input":"2022-03-06T00:59:04.013750Z","iopub.status.idle":"2022-03-06T01:02:31.095781Z","shell.execute_reply.started":"2022-03-06T00:59:04.013695Z","shell.execute_reply":"2022-03-06T01:02:31.094503Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-03-06T01:02:46.805166Z","iopub.execute_input":"2022-03-06T01:02:46.805416Z","iopub.status.idle":"2022-03-06T01:02:46.922233Z","shell.execute_reply.started":"2022-03-06T01:02:46.805386Z","shell.execute_reply":"2022-03-06T01:02:46.921687Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sales_month=trans_data.groupby(['YearMonth'])['article_id'].size().reset_index(name='totalsales')","metadata":{"execution":{"iopub.status.busy":"2022-03-06T01:02:55.620032Z","iopub.execute_input":"2022-03-06T01:02:55.620426Z","iopub.status.idle":"2022-03-06T01:02:58.708497Z","shell.execute_reply.started":"2022-03-06T01:02:55.620390Z","shell.execute_reply":"2022-03-06T01:02:58.707217Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sales_month=sales_month.sort_values('YearMonth')\nfig = px.line(sales_month, x=\"YearMonth\", y=\"totalsales\")\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-03-06T01:03:00.800420Z","iopub.execute_input":"2022-03-06T01:03:00.800754Z","iopub.status.idle":"2022-03-06T01:03:01.882877Z","shell.execute_reply.started":"2022-03-06T01:03:00.800705Z","shell.execute_reply":"2022-03-06T01:03:01.882031Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The graph shows the seasonal pattern of high sales over Jun and July every year","metadata":{}},{"cell_type":"code","source":"del sales_month,trans_data\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-03-06T01:04:53.011518Z","iopub.execute_input":"2022-03-06T01:04:53.012433Z","iopub.status.idle":"2022-03-06T01:04:54.147772Z","shell.execute_reply.started":"2022-03-06T01:04:53.012400Z","shell.execute_reply":"2022-03-06T01:04:54.146247Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<a id=\"productcust\"></a>\n### Does customer always buy products that are less expensive?","metadata":{"execution":{"iopub.status.busy":"2022-02-26T06:34:54.158153Z","iopub.execute_input":"2022-02-26T06:34:54.158749Z","iopub.status.idle":"2022-02-26T06:34:54.170748Z","shell.execute_reply.started":"2022-02-26T06:34:54.158589Z","shell.execute_reply":"2022-02-26T06:34:54.170068Z"}}},{"cell_type":"code","source":"data=read_data()\n","metadata":{"execution":{"iopub.status.busy":"2022-03-05T23:22:35.210704Z","iopub.execute_input":"2022-03-05T23:22:35.211539Z","iopub.status.idle":"2022-03-05T23:23:01.594179Z","shell.execute_reply.started":"2022-03-05T23:22:35.211489Z","shell.execute_reply":"2022-03-05T23:23:01.593345Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_nature=data.groupby(['customer_id','price_band'])['article_id'].count().reset_index(name='totalbought')\ncust_price=cust_nature.groupby('price_band')['totalbought'].sum().reset_index(name='totalitems')\ncust_price['perc_share']=(cust_price['totalitems']/cust_price['totalitems'].sum())*100","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:35:29.466574Z","iopub.execute_input":"2022-03-05T02:35:29.466835Z","iopub.status.idle":"2022-03-05T02:36:09.367427Z","shell.execute_reply.started":"2022-03-05T02:35:29.466805Z","shell.execute_reply":"2022-03-05T02:36:09.366679Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_price","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:36:09.368474Z","iopub.execute_input":"2022-03-05T02:36:09.368714Z","iopub.status.idle":"2022-03-05T02:36:09.382363Z","shell.execute_reply.started":"2022-03-05T02:36:09.368686Z","shell.execute_reply":"2022-03-05T02:36:09.381530Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import plotly.express as px\nfig = px.pie(cust_price, values='perc_share'\n                 , names='price_band', title='% Share by Product Price Type')\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:36:09.383673Z","iopub.execute_input":"2022-03-05T02:36:09.384334Z","iopub.status.idle":"2022-03-05T02:36:09.652674Z","shell.execute_reply.started":"2022-03-05T02:36:09.384299Z","shell.execute_reply":"2022-03-05T02:36:09.651865Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Most of the products that the customer buy are with in the range of medium priced products followed by low and very high value products. Now we can slice and dice and understand which segment of customers contribute to most of the revenue H&M.\n\nIf you are not aware of the type of customers that an online business deal with there are mainly four type of customers that any online business will have\n\n1. New Customers - This segment of customers are the customers who are on the website trying out the platform for the first time and they will move into one of the below categories based on the experience.\n\n2. Returning customers - These are the core customers for any business as they contribute to most of the revenue. These are the segment of customers who keep coming back to the website to buy products at regular intervals\n\n3. Reactivated Customers - This segment of customers are those who havent visited the website in a long time and they have come back to the website because of any marketing activities or any other trigger.Normally the period can vary but some business takes 365 days as reactivation time which means any customer who havent visited the website in the past 365 days and they visit the website they become reactivated customers.\n\n4. Churn Customers - These are short lived customers where the product couldnt establish a long term relationship with the customers. They might have come to the website because of a promotional email or a display campaign and mostly would have done a 1 time purchase or just browsed the website. The churn period differs for different business but some of them take 180 or 365 days as the ideal period that consider that the customer have churned","metadata":{}},{"cell_type":"markdown","source":"<a id=\"custseg\"></a>\n### Which Segment of Customers are most sticking to the platform?","metadata":{}},{"cell_type":"code","source":"data['t_dat']=pd.to_datetime(data['t_dat'])\ndata['days_since_last_event'] = (data.sort_values(['customer_id','t_dat'])\n                                 .groupby('customer_id')['t_dat'].diff()\n                                 .dt.days)","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:36:09.654020Z","iopub.execute_input":"2022-03-05T02:36:09.654456Z","iopub.status.idle":"2022-03-05T02:44:08.404132Z","shell.execute_reply.started":"2022-03-05T02:36:09.654424Z","shell.execute_reply":"2022-03-05T02:44:08.403142Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_repeat_data=data[['t_dat','customer_id','days_since_last_event']].drop_duplicates()\ncust_repeat_data['days_since_last_event'].fillna(0, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:44:08.405735Z","iopub.execute_input":"2022-03-05T02:44:08.406087Z","iopub.status.idle":"2022-03-05T02:44:27.933039Z","shell.execute_reply.started":"2022-03-05T02:44:08.406044Z","shell.execute_reply":"2022-03-05T02:44:27.931718Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:44:27.937088Z","iopub.execute_input":"2022-03-05T02:44:27.937378Z","iopub.status.idle":"2022-03-05T02:44:28.220850Z","shell.execute_reply.started":"2022-03-05T02:44:27.937343Z","shell.execute_reply":"2022-03-05T02:44:28.219252Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def map_days_since(days_since_last_event):\n  if days_since_last_event >= 365:\n    return \"Reactivated\"\n  elif days_since_last_event ==0:\n    return \"New Customers\"\n  else:\n    return \"Returning\"\n\ncust_repeat_data[\"customer_type\"] = cust_repeat_data[\"days_since_last_event\"].apply(lambda days_since_last_event: map_days_since(days_since_last_event))\n","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:44:28.224704Z","iopub.execute_input":"2022-03-05T02:44:28.225656Z","iopub.status.idle":"2022-03-05T02:44:36.414031Z","shell.execute_reply.started":"2022-03-05T02:44:28.225570Z","shell.execute_reply":"2022-03-05T02:44:36.412951Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_repeat_data=cust_repeat_data[['t_dat','customer_id','customer_type']].drop_duplicates()","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:44:36.415160Z","iopub.execute_input":"2022-03-05T02:44:36.415375Z","iopub.status.idle":"2022-03-05T02:44:52.529776Z","shell.execute_reply.started":"2022-03-05T02:44:36.415348Z","shell.execute_reply":"2022-03-05T02:44:52.528819Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cust_type_data=cust_repeat_data.groupby(['t_dat','customer_type'])['customer_id'].nunique().reset_index(name='totalcusttype')","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:44:52.530925Z","iopub.execute_input":"2022-03-05T02:44:52.531141Z","iopub.status.idle":"2022-03-05T02:45:06.406264Z","shell.execute_reply.started":"2022-03-05T02:44:52.531116Z","shell.execute_reply":"2022-03-05T02:45:06.405301Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We will plot data after the first date which we will consider as the first date of sale happened on the platform ","metadata":{}},{"cell_type":"code","source":"cust_type_data['percentage_share']=cust_type_data['totalcusttype'] / \\\ncust_type_data.groupby('t_dat')['totalcusttype'].transform('sum')\ncust_type_data['percentage_share']=cust_type_data['percentage_share']*100","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:45:06.407828Z","iopub.execute_input":"2022-03-05T02:45:06.408226Z","iopub.status.idle":"2022-03-05T02:45:06.417888Z","shell.execute_reply.started":"2022-03-05T02:45:06.408182Z","shell.execute_reply":"2022-03-05T02:45:06.416871Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Note\nFor the sake of simplicity, I am considerting reactivation rate as customers who come to the website after 365 days and since we are looking at the transaction data i have excluded churn analysis here.\nAlso we will consider the first date of transaction that is 2018-09-20 as the start date for all customers","metadata":{}},{"cell_type":"code","source":"fig = px.line(cust_type_data, x=\"t_dat\", y=\"percentage_share\", color='customer_type')\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:45:06.419060Z","iopub.execute_input":"2022-03-05T02:45:06.419317Z","iopub.status.idle":"2022-03-05T02:45:06.627871Z","shell.execute_reply.started":"2022-03-05T02:45:06.419285Z","shell.execute_reply":"2022-03-05T02:45:06.627139Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del cust_type_data,cust_repeat_data \ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-03-05T02:45:06.629108Z","iopub.execute_input":"2022-03-05T02:45:06.629499Z","iopub.status.idle":"2022-03-05T02:45:07.008284Z","shell.execute_reply.started":"2022-03-05T02:45:06.629468Z","shell.execute_reply":"2022-03-05T02:45:07.007643Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"This is an interesting graph that shows the behaviour and nature of the business from the transactional data. Based on our assumptions we can see that initially we have new customers and then slowly as the platform establishes itself there are more and more returning customers. Also the anomaly behaviour that we see around April 2020 might be due to Covid related restrictions and lockdowns.\n\nIt is interesting to see that new customer rate hasnt dropped much which means the market is not yet saturated with the product that there is possibility to onboard more and more new customers.\n\nReactivation rates are quite low but this needs to be looked along with churn analysis and then we wil be able to identify if this looks fine or not","metadata":{}},{"cell_type":"markdown","source":"<a id=\"buypattern\"></a>\n### Does markdown affect the buying pattern of customers?\n\nOne of the other factors that impact online business is people might look for good offers when they buy products. There are 2 possibilities while considering the price and stock of the products that we sell on the website\n\n1. We have an expensive product and people will buy that only when they are on sale. This can be identified as the products that are marked high and very high and then during the life cycle of the product there might be offers where you will find the product purchases vary depending on the offer.\n\n2. You have a less appealing product on the website and because of the price people dont want to buy so they will never get traction unless we are selling the product at probably low to clear the stock","metadata":{}},{"cell_type":"markdown","source":"Lets have a look at point 1. We need to identify product that switch between the price ranges during the life cycle . We are going to take a look at the products which satisfy this condition and lets understand the buying pattern. For simplicity we will only consider products that are marked very high. ","metadata":{}},{"cell_type":"code","source":"data=read_data()","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:14:32.599815Z","iopub.execute_input":"2022-03-05T04:14:32.600021Z","iopub.status.idle":"2022-03-05T04:14:57.874517Z","shell.execute_reply.started":"2022-03-05T04:14:32.599997Z","shell.execute_reply":"2022-03-05T04:14:57.873842Z"},"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_price_data=data[['t_dat','article_id','price_band']].drop_duplicates()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-05T04:14:57.875736Z","iopub.execute_input":"2022-03-05T04:14:57.875982Z","iopub.status.idle":"2022-03-05T04:15:08.572416Z","shell.execute_reply.started":"2022-03-05T04:14:57.875952Z","shell.execute_reply":"2022-03-05T04:15:08.571802Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"single_valued=product_price_data.groupby('article_id')['price_band'].nunique().reset_index(name='uniqueprices')\nsingle_valued_skus=single_valued[single_valued['uniqueprices']==1]['article_id'].unique().tolist()\nproduct_price_data=product_price_data[~(product_price_data['article_id'].isin(single_valued_skus))]\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-05T04:15:08.574012Z","iopub.execute_input":"2022-03-05T04:15:08.574396Z","iopub.status.idle":"2022-03-05T04:15:13.272470Z","shell.execute_reply.started":"2022-03-05T04:15:08.574355Z","shell.execute_reply":"2022-03-05T04:15:13.271751Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndate_range=product_price_data.groupby(['article_id','price_band']).agg({'t_dat': [np.min,np.max]}).reset_index()\ndate_range.columns = date_range.columns.droplevel(0)\ndate_range.columns = ['article_id','price_band','datemin','datemax']","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:15:13.273569Z","iopub.execute_input":"2022-03-05T04:15:13.273858Z","iopub.status.idle":"2022-03-05T04:15:48.259227Z","shell.execute_reply.started":"2022-03-05T04:15:13.273828Z","shell.execute_reply":"2022-03-05T04:15:48.258660Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"initial_data=product_price_data.sort_values(['article_id','t_dat']).groupby('article_id').nth(1).reset_index()\ninitial_data=initial_data[initial_data['price_band'].isin(['very high'])]","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:15:48.260290Z","iopub.execute_input":"2022-03-05T04:15:48.260719Z","iopub.status.idle":"2022-03-05T04:15:56.162310Z","shell.execute_reply.started":"2022-03-05T04:15:48.260682Z","shell.execute_reply":"2022-03-05T04:15:56.161620Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merged_data=pd.merge(date_range,initial_data, how='inner')\ndel date_range,single_valued_skus,initial_data,product_price_data\ngc.collect()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-05T04:15:56.163482Z","iopub.execute_input":"2022-03-05T04:15:56.163706Z","iopub.status.idle":"2022-03-05T04:15:56.383199Z","shell.execute_reply.started":"2022-03-05T04:15:56.163675Z","shell.execute_reply":"2022-03-05T04:15:56.382331Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data=data[data['article_id'].isin(merged_data['article_id'].tolist())]","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-05T04:15:56.384610Z","iopub.execute_input":"2022-03-05T04:15:56.384916Z","iopub.status.idle":"2022-03-05T04:16:00.318326Z","shell.execute_reply.started":"2022-03-05T04:15:56.384879Z","shell.execute_reply":"2022-03-05T04:16:00.317633Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandasql  as ps\n\nsqlcode = '''\nselect pp.t_dat,\npp.article_id,\npp.price_band,\nmd.datemin,\nmd.datemax\nfrom data pp\ninner join merged_data  md on \nmd.article_id=pp.article_id\nand \npp.t_dat >= md.datemin and pp.t_dat<= md.datemax\n'''\n\ndata = ps.sqldf(sqlcode,locals())\n","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:16:00.319787Z","iopub.execute_input":"2022-03-05T04:16:00.320076Z","iopub.status.idle":"2022-03-05T04:18:22.976111Z","shell.execute_reply.started":"2022-03-05T04:16:00.320040Z","shell.execute_reply":"2022-03-05T04:18:22.975080Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"price_data=data.groupby(['price_band','article_id']).size().reset_index(name='total_bought')\nprice_data['percentage'] = 100 * price_data['total_bought'] / price_data.groupby('article_id')['total_bought'].transform('sum')","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:18:22.979132Z","iopub.execute_input":"2022-03-05T04:18:22.979793Z","iopub.status.idle":"2022-03-05T04:18:25.606189Z","shell.execute_reply.started":"2022-03-05T04:18:22.979743Z","shell.execute_reply":"2022-03-05T04:18:25.605377Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"article_data=price_data.loc[price_data.groupby('article_id')['total_bought'].idxmax()][['price_band','article_id']]","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:18:25.607398Z","iopub.execute_input":"2022-03-05T04:18:25.607617Z","iopub.status.idle":"2022-03-05T04:18:26.483395Z","shell.execute_reply.started":"2022-03-05T04:18:25.607593Z","shell.execute_reply":"2022-03-05T04:18:26.482572Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_data=article_data.groupby('price_band').size().reset_index(name='total_products')\nproduct_data['share']=product_data['total_products']/product_data['total_products'].sum()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-05T04:18:26.484419Z","iopub.execute_input":"2022-03-05T04:18:26.484633Z","iopub.status.idle":"2022-03-05T04:18:26.492729Z","shell.execute_reply.started":"2022-03-05T04:18:26.484607Z","shell.execute_reply":"2022-03-05T04:18:26.491985Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_data","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:18:26.494010Z","iopub.execute_input":"2022-03-05T04:18:26.494185Z","iopub.status.idle":"2022-03-05T04:18:26.510146Z","shell.execute_reply.started":"2022-03-05T04:18:26.494163Z","shell.execute_reply":"2022-03-05T04:18:26.509576Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Observation based on Scenario 1 for high value products\nWhat the above table indicates is that out of the 20878 very high value products that we sell on the website, during the life cycle of the product where they shited prices but reverted back to original at some point, 92% of products sold at the orginal price band and about 8% had seen a massive sale when promos happened. This is a good insight to identify because this might be some specific category of products which people might buy when they are on offer only. But overall a high % products are well recieved at the original price.","metadata":{}},{"cell_type":"code","source":"fig = px.pie(product_data, values='share'\n                 , names='price_band', title='% Product Original Price Stickiness likelihood')\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:18:26.511348Z","iopub.execute_input":"2022-03-05T04:18:26.512173Z","iopub.status.idle":"2022-03-05T04:18:27.502094Z","shell.execute_reply.started":"2022-03-05T04:18:26.512140Z","shell.execute_reply":"2022-03-05T04:18:27.501278Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del product_data,price_data,article_data,data\ngc.collect()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-05T04:18:27.503655Z","iopub.execute_input":"2022-03-05T04:18:27.504709Z","iopub.status.idle":"2022-03-05T04:18:29.306492Z","shell.execute_reply.started":"2022-03-05T04:18:27.504633Z","shell.execute_reply":"2022-03-05T04:18:29.305519Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Scenario 2 \nLets look at the overall product markdown trend where we will consider any purchase made at a price point than the original price band of that product as markdown and see how the general sales trend holds true for the products on the website","metadata":{}},{"cell_type":"code","source":"data=read_data()\nproduct_price_data=data[['t_dat','article_id','price_band']].drop_duplicates()\ninitial_data=product_price_data.sort_values(['article_id','t_dat']).groupby('article_id').nth(1).reset_index()\nproduct_data=data.groupby(['price_band','article_id']).size().reset_index(name='total_products')\n","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-05T04:18:29.307927Z","iopub.execute_input":"2022-03-05T04:18:29.308118Z","iopub.status.idle":"2022-03-05T04:19:10.935264Z","shell.execute_reply.started":"2022-03-05T04:18:29.308096Z","shell.execute_reply":"2022-03-05T04:19:10.934338Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merge_data=pd.merge(product_data,initial_data, how='left')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-05T04:19:10.936493Z","iopub.execute_input":"2022-03-05T04:19:10.937191Z","iopub.status.idle":"2022-03-05T04:19:11.120687Z","shell.execute_reply.started":"2022-03-05T04:19:10.937148Z","shell.execute_reply":"2022-03-05T04:19:11.119809Z"},"_kg_hide-output":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"merge_data['original']=np.where(~(merge_data['t_dat'].isnull()),1,0)","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-05T04:19:11.122026Z","iopub.execute_input":"2022-03-05T04:19:11.122245Z","iopub.status.idle":"2022-03-05T04:19:11.144324Z","shell.execute_reply.started":"2022-03-05T04:19:11.122219Z","shell.execute_reply":"2022-03-05T04:19:11.143450Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"saledata=merge_data.groupby(['original','article_id'])['total_products'].sum().reset_index(name='total_sale')\nsaledata['share']=saledata['total_sale']/saledata.groupby('article_id')['total_sale'].transform(sum)","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:19:11.145863Z","iopub.execute_input":"2022-03-05T04:19:11.146328Z","iopub.status.idle":"2022-03-05T04:19:11.468902Z","shell.execute_reply.started":"2022-03-05T04:19:11.146283Z","shell.execute_reply":"2022-03-05T04:19:11.468092Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"final_sku_md_data=saledata.loc[saledata.groupby('article_id')['share'].idxmax()][['original','article_id']]\nproduct_data=final_sku_md_data.groupby('original').size().reset_index(name='total_products')\nproduct_data['share']=product_data['total_products']/product_data['total_products'].sum()","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:19:11.470055Z","iopub.execute_input":"2022-03-05T04:19:11.470338Z","iopub.status.idle":"2022-03-05T04:19:17.625980Z","shell.execute_reply.started":"2022-03-05T04:19:11.470302Z","shell.execute_reply":"2022-03-05T04:19:17.625317Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"product_data","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:19:17.626983Z","iopub.execute_input":"2022-03-05T04:19:17.627291Z","iopub.status.idle":"2022-03-05T04:19:17.636374Z","shell.execute_reply.started":"2022-03-05T04:19:17.627265Z","shell.execute_reply":"2022-03-05T04:19:17.635598Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = px.pie(product_data, values='share'\n                 , names='original', title='% Product Sold Max share at Original Price')\nfig.update_layout(legend_title_text='Original Price or Not')\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2022-03-05T04:20:28.662811Z","iopub.execute_input":"2022-03-05T04:20:28.663263Z","iopub.status.idle":"2022-03-05T04:20:28.715327Z","shell.execute_reply.started":"2022-03-05T04:20:28.663233Z","shell.execute_reply":"2022-03-05T04:20:28.714739Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del product_data,final_sku_md_data,merge_data,data, saledata\ngc.collect()","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2022-03-05T04:19:17.794934Z","iopub.status.idle":"2022-03-05T04:19:17.795386Z","shell.execute_reply.started":"2022-03-05T04:19:17.795143Z","shell.execute_reply":"2022-03-05T04:19:17.795169Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### What the above graph shows us \nis about 77% products have were sold at more share of original price compared to when they were markdown. And they might have been markdown towards the end of the lifecycle of the products. This is pretty good as selling the products at original price band ensures more profitability downstream","metadata":{}},{"cell_type":"markdown","source":"### WIP\n\nThis is the WIP I will be adding in the above analysis .\n\nSome of the other questions that i am looking to add here are\n1. Churn data analysis from the customer data\n2. Returning customer purchase behaviour\n\nPlease let me know in the comments if you would like to add something more\n","metadata":{}}]}