Using this spreadsheet, https://docs.google.com/spreadsheets/d/1M80LDEqemHQyPtBliqzPTEzj7gZ0CW5_-w1Ls2fVcVM/edit?usp=sharing. Complete the below task. Thanks Customer Data Suppose that in order to gain

Using this spreadsheet, 

https://docs.google.com/spreadsheets/d/1M80LDEqemHQyPtBliqzPTEzj7gZ0CW5_-w1Ls2fVcVM/edit?usp=sharing. Complete the below task. Thanks

Transcribed Image Text:

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 self- reported 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] 1 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 self- reported 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] 1