MSMK7021聚类、预测和价格弹性

作业将以最多6名学生为一组完成。请提交1份报告,否20多页,双倍行距。报告应简明扼要。附加表格和数字可以包括在附录中,但不能保证它会被阅读。任务将同时使用Tableau和AET。Moodle上提供了数据文件。是的分为6个主要部分:探索性和描述性分析、市场篮子分析、,分类规划、RFM和聚类、预测和价格弹性。阅读提前分配。每个部分所需的时间不相等。本作业假定具备Tableau的基本知识。有许多视频教程在线帮助您入门。您可能还需要学习更多高级功能,如如何在Tableau中联接表
MSMK7021 Assignment #1Due: Monday, Feb. 20, 11:59pm, 2023

1.Introduction

The assignment will be done in groups of up to 6 students. Please submit 1 single report, nomore than 20 pages, double spaced. Report should be concise. Additional tables andfigures can be included in the appendix but cannot be guaranteed that it will be read.The assignment will use both Tableau and AET. The data file isavailable on Moodle. It isdivided into 6 main parts – exploratory and descriptive analysist, market basket analysis,assortment planning, RFM & Clustering, Predictions, and Price Elasticity. Readtheassignment in advance. The time required on each part is not equal.The assignment assumes basic knowledge of Tableau. There are a number of video tutorialsonline to help you get started. You will also likely need to learn more advance functions likehow to join tables in Tableau. One tutorial here https://youtu.be/Xbg2WabUIKI .
Answer the following questions.Part 1 – Exploratory data analysis1. [10 points] Perform descriptive analytics of the orders. Specifically, plot thedistributions for number of orders, quantity and unit price over time. Note that youwill need to create a new variable for unit price which is the Sales divided byQuantity. Describe what you see. Are there any purchasing patterns over time likeholidays?2. [15 points] For a particular product, is there variance in unit price? Which producthas the biggest variance in unit price? How does the unit price vary over time? Canyou show a few examples of products which varying a lot over time? Whichproducts vary little over time? Which subcategory has the largest variance? Can youexplain why some of the products vary in unit price? Why do you think it happens?Part 2 – market basket analysis3. [25 points] Perform market basket analysis at the sub-category level. You do notneed to compute the support, lift and confidence. Rather, a simple quantity count issufficient. What sub categories are purchased the most together?4. [25 points] Now, conduct a market basket analysis at the product level for the top 10selling products. What do you find? Are these high or low priced products?Part 3 – Assortment Planning5. [25 points] For the bakery example done in class, what is the estimated demand forFudge, Family Size, Tiny Tina? What was the actual substitution effect with Fudge,Family Size, Yummy Cake? Show your work.Part 4 – RFM & Clustering1. [5 points] Generate the RFM variables using the superstore data. Remember, youwill need to group by customer id first. Then, assign a score of 1 for the “best”customer for the top 20 percentile of customers for that dimension. Show your andprocedure how you did this.2. [20 points] Once you have created a worksheet with proper variables andvisualization, you can export the worksheet data using the “Crosstab to Excel” in the“Worksheet” menu item. Make sure that the rows and columns in the exported filesare in the correct. Open the file in Excel to check that it is what you want. Then,export the file to csv, and import into the AET clustering module.a. Make plots for R&F, F&M, and M&R. Perform clustering on these plots. TheAET shows What do you choose for the number of clusters? How and whydid you come up with those values?b. Now, we will cluster on all 3 RFM dimensions. Use Step 3 in the AET. Howmany clusters did you choose? Why?c. Now, repeat part (b) with more variables from Tableau. Which variables doyou add and why?Part 5 - Prediction3. [5 points] Now we will predict customers who are likely to return items. In Tableau,join the order table with the returns table. Do a cross tabulation or “group by”Customer ID, and count how many returns each customer does. Check that yourtable join includes customers who had no returns. Now perform a “slice-and-dice”by visualizing the number of returns by each group of R, F, M like in lecture for thecampaign response rates.4. [20 points] Now export your worksheet data like in part 4, into excel, then into csv.Upload the csv file into the Decision Tree module of the AET. Note that for decisiontree, the value can only be 0 or 1. You will need to convert your returns count into 0if they have never returned an item, and 1 if they have returned 1 or more. In theDecision Tree module, select your new returns binary variable as the “target”, andone more more for the features. Plot the decision tree1. What do you see? Explainthe outcomes. What other variables can you add?
1 For explanation of the results and plots, see “Results Explained” below. Note that the AET reports the“value” as a proportion, not a count as mentioned in thewebsite:https://www.w3schools.com/python/python_ml_decision_tree.asp5. [25 points] Now we will try to use the Regression Module to predict the returns. Youcan use the total returns count. What independent variables do you use? Whichones are statistically significant? What other variables can you add?
Part 6 – Price Elasticity6. [25 points] For this part, we will estimate the price elasticity of each sub-categoryfrom the superstore dataset. Prepare your data by creating separate csv files foreach sub-categories, and each row should be aggregated by product. Product theaverage unit price for each product, and total units (quantity) sold. Remember toalso produce the log of these values. You will need to do regression analysis. Thereare several options to do so, and you can choose which you are most familiar with:a. Tableau. You can use the Analytics and trend line function. A quicktutorialhttps://youtu.be/lc76P-C205ob. AET. Import the csv into the Regression Module of the AET and compute theregression on the log values.c. R. If you are familiar with R, you can use
What are the price elasticities for each of the sub-categories? Can you explain whatyou see? Does it make sense?
WX:codehelp

评论

暂无评论