Question 2 - Working with BIG Data, Data analysis using Excel 20 marks You are employed by a supermarket chain which has recently begun offering a new line of organic products. Senior management of the supermarket want to identify which customers are likely to purchase these products. They have asked you to analyse some existing data and make a recommendation to assist them with this task. The supermarket has a customer loyalty program. As an initial buyer incentive plan, the supermarket provided coupons for the organic products to all of the loyalty program participants and collected data from their purchases, including whether these customers purchased any of the organic products. Required You need to perform an analysis to identify which customers are likely to purchase organic products. It's up to you to decide which data items are meaningful, you will need to play around or a while to build an understanding of how the patterns for each data item are playing out. You require to use charts to more accurately visualize the data patterns. 1. Once you have identified your target customers you require to prepare a brief report (maximum 300 words) suitable for senior management. Make sure you include a clear recommendation identifying which customers should be targeted as a priority for future promotions involving organic products. You need to justify your target recommendation by referring to the analytical results you obtained (10 marks). 2. You must incorporate three charts to visually illustrate the patterns you relied on when you selected your target customers (10 marks). The ORGANICS data set is available through the LMS and contains 13 variables and more than 22,000 observations. The variables in the data set and a brief description of each are shown below (please refer to the next page). An Excel file that includes the charts must be submitted to the LMS. Report should be submitted in a separate MS Word file to the LMS. Name ID Description Customer loyalty identification number DemAffi Affluence grade on a scale from 1 to 30 DemAge Age, in years DemCluster Type of residential neighborhood DemClusterGroup Neighborhood group DemGender M = male, F = female, U = unknown DemRegion Geographic region DemTVReg Television region PromClass Loyalty status: tin, silver, gold, or platinum PromSpend Total amount spent PromTime Time as loyalty card member TargetBuy Organics purchased? 1 = Yes, 0 = No TargetAmt Number of organic products purchased 10 Demam DemAge DemCluster DemCluster Group DemGender Demeg 0000000140 10 76 16 U Midlands 0000000620 4 49 35 D U Midlands 5 70 27 D F Midlands 0000001120 10 65 51 F M Midlands ODO0002313 11 68 04 A F Midlands 000002771 72 28 D U North 0000003131 11 74 03 F Midlands 13 62 32 D M North 10 62 49 F M Midlands 14 49 F F DemTV Reg PromClass PromSpend Prom Time TargetBuy TargetAmt Wales & West Gold 16000 0 Wales & West Gold 6000 5 0 0 Wales & West Silver 0.02 8 1 1 Midlands Tin 0.01 7 1 1 Midlands Tin 0.01 0 0 N West Platinum 20759.81 3 0 0 East Tin 0.01 0 N East Tin 0.01 $ 0 0 East 2038.76 3 0 0 Gold 6000 1 1 1 Silver Question 2 - Working with BIG Data, Data analysis using Excel 20 marks You are employed by a supermarket chain which has recently begun offering a new line of organic products. Senior management of the supermarket want to identify which customers are likely to purchase these products. They have asked you to analyse some existing data and make a recommendation to assist them with this task. The supermarket has a customer loyalty program. As an initial buyer incentive plan, the supermarket provided coupons for the organic products to all of the loyalty program participants and collected data from their purchases, including whether these customers purchased any of the organic products. Required You need to perform an analysis to identify which customers are likely to purchase organic products. It's up to you to decide which data items are meaningful, you will need to play around or a while to build an understanding of how the patterns for each data item are playing out. You require to use charts to more accurately visualize the data patterns. 1. Once you have identified your target customers you require to prepare a brief report (maximum 300 words) suitable for senior management. Make sure you include a clear recommendation identifying which customers should be targeted as a priority for future promotions involving organic products. You need to justify your target recommendation by referring to the analytical results you obtained (10 marks). 2. You must incorporate three charts to visually illustrate the patterns you relied on when you selected your target customers (10 marks). The ORGANICS data set is available through the LMS and contains 13 variables and more than 22,000 observations. The variables in the data set and a brief description of each are shown below (please refer to the next page). An Excel file that includes the charts must be submitted to the LMS. Report should be submitted in a separate MS Word file to the LMS. Name ID Description Customer loyalty identification number DemAffi Affluence grade on a scale from 1 to 30 DemAge Age, in years DemCluster Type of residential neighborhood DemClusterGroup Neighborhood group DemGender M = male, F = female, U = unknown DemRegion Geographic region DemTVReg Television region PromClass Loyalty status: tin, silver, gold, or platinum PromSpend Total amount spent PromTime Time as loyalty card member TargetBuy Organics purchased? 1 = Yes, 0 = No TargetAmt Number of organic products purchased 10 Demam DemAge DemCluster DemCluster Group DemGender Demeg 0000000140 10 76 16 U Midlands 0000000620 4 49 35 D U Midlands 5 70 27 D F Midlands 0000001120 10 65 51 F M Midlands ODO0002313 11 68 04 A F Midlands 000002771 72 28 D U North 0000003131 11 74 03 F Midlands 13 62 32 D M North 10 62 49 F M Midlands 14 49 F F DemTV Reg PromClass PromSpend Prom Time TargetBuy TargetAmt Wales & West Gold 16000 0 Wales & West Gold 6000 5 0 0 Wales & West Silver 0.02 8 1 1 Midlands Tin 0.01 7 1 1 Midlands Tin 0.01 0 0 N West Platinum 20759.81 3 0 0 East Tin 0.01 0 N East Tin 0.01 $ 0 0 East 2038.76 3 0 0 Gold 6000 1 1 1 Silver