**Note: This is an excell assignment. I have already figured out step 1 and 2 of the 4 steps. Any help would be great!

Customer Data Suppose that in order to gain customer insights and develop a data-driven marketing strategy, a major retailer had recently conducted a large-scale customer survey on spending habits and integrated the survey results with internal customer transaction data. You will mine this data for insights that can be translated into actionable and defensible marketing recommendations. You will first analyze the data at three different levels: transaction, customer, and segment. Then, you will use your segmentation analysis to create what-if scenarios enabling you to forecast the expected results of different marketing actions. These forecasts will lead to a data-driven recommendation regarding which, if either, of two marketing campaigns would be expected to yield a higher return on investment. Background The survey was administered online throughout June 2020 and invitations were sent to 10,000 customers who provided an e-mail address when signing up for the store's membership card. As an incentive, customers who completed the survey were offered the chance to win one of five $500 gift cards from the retailer. The response rate was approximately 15%. The survey included two questions that are pertinent to this analysis. First, consumers were asked to recall the total amount of money they had spent at the retailer's store during the previous month. Second, consumers were asked to recall the total amount of money they had spent in the category overall during the previous month, including at stores of competing retailers. Survey results were validated by comparing customers' self-reported spending to their actual transaction data where the customer's membership card was scanned. Cases in which selfreported monthly spend differed from actual monthly spend by more than 5% were eliminated from the dataset. Records with missing or erroneous data in either the survey results or the transaction data were also removed from the dataset during the coding and cleaning process. This resulted in usable survey data from 1,057 customers. The dataset you have been provided contains information regarding 12,145 transactions made by these 1,057 customers during a five-year period (the retailer's fiscal year begins July 1 and ends June 30 of each year). Survey results were appended to this data and are indicated by the prefix "S_" in the variable name. Below is a description of each variable in the dataset, together with the range of valid values in parentheses: TransID: Unique identification number for each transaction (AA00AA00-ZZ99ZZ99) PurchDate: Date of purchase (1-Jul-2015 through 30-Jun-2020) PurchAmount: Purchase amount [in USD] CardID: Unique identification number for each customer membership card (00000000- 99999999) CardDate: Date the customer membership card application was processed (1-Jul-2015 through 31-May-2020) S_Date: Date the survey was completed (1-Jun-2020 through 30-Jun-2020) S_RetSpend: Customers' self-reported spend with the retailer during May 2020 [in USD] 2 S_CatSpend: Customers' self-reported spend in the entire category (including spend with competing retailers) during May 2020 [in USD] Spreadsheet Skills One of the purposes of this assignment is to strengthen your spreadsheet skills. This assignment is to be completed using Microsoft Excel and is designed to ensure that you are familiar with some of the formulas and functions that are commonly used in business. You should understand how to do and reference multiple worksheets within a workbook, how to copy and paste data within Excel, and how to use basic mathematical operators (+, -, *, /), as well as the following formulas: 1. IF 2. AND 3. COUNT 4. COUNTIF 5. SUM 6. SUMIF 7. AVERAGE 8. AVERAGEIF 9. STDEV 10. VLOOKUP 11. RANK (although less commonly used, you may find the related formula PERCENTRANK.INC particularly helpful for this assignment) 12. ROUND In addition to the formulas listed above, it will also be helpful to know the following functions (the menu in which each function is located is listed in parentheses): 1. Sort (Data menu) 2. Remove Duplicates (Data menu) 3. Number and date formatting (Home menu) 4. Text to columns (Data menu) You can learn about each formula and function in Excel's HELP menu. If you need additional help, just google "Excel" together with the name of the function or formula and you will find plenty of good online resources. Although you may be able to complete the assignment without learning all these skills, I believe they will save you a lot of time on the assignment and you are likely to benefit from them throughout your business career, so I would encourage you to invest the time necessary to become familiar with them. 3 Your Tasks Use the data provided to complete each of the four tasks outlined below. The tasks are designed to help you become familiar with common steps in data analytics and to understand some of the common metrics used in analyzing marketing data. In addition, the assignment is designed to assess your attention to detail and ability to follow instructions precisely, which is an extremely important skill when working with data. For all calculations, do not round; use all available decimals (this is Excel's default when you reference cell values). However, when reporting your answer, round as instructed (use Excel to adjust decimal places and type whatever appears in the cell when the appropriate number of decimals are displayed). Do not include dollar signs, percent symbols, or commas when reporting your answers. No partial credit will be awarded for incorrect answersyour answer must exactly match the correct answer (including formatting), so pay close attention to detail and double-check your work before submitting. At the conclusion of the assignment, you will be required to upload your spreadsheet, so please use formulas and label all your work so that I can see the steps you took to obtain each answer. Feel free to do additional worksheets as necessary. The data file is in a .csv format, so you should save it as an Excel file before beginning in order to avoid losing any work. Also note that a cell filled with ####### is not invalid data; rather, it indicates that the column width is too narrow to properly display the cell valuethis can be easily fixed by merely adjusting the column width. Task 1: Transaction-Level Analysis The first task involves analyzing data at the individual-transaction level. This analysis will allow you to get a sense of what the distribution of data looks like and to identify and remove outliers. Follow each of the steps below:

1. Calculate the mean purchase amount across all transactions. [round answer to the nearest whole dollar]

2. Calculate the standard deviation in purchase amount across all transactions. [round answer to the nearest whole dollar]

3. Calculate the percentage of transactions with a purchase amount that is more than three standard deviations above or below the mean purchase amount. These transactions are considered "outliers." [round percentage to two decimal places (x.xx%)]

4. Remove transactions that are considered "outliers" from the dataset and calculate the new mean purchase amount across all remaining transactions. This new "clean" dataset should be used for all subsequent questions in the three remaining tasks. [round answer to the nearest whole dollar; use Excel to adjust decimal places and type whatever appears in the cell when no decimals are displayed]