Assignment 4: Describing and understanding Survey Data

Answer the below questions with the survey data given (Survey data.xlsx). Once you have followed these instructions, go ahead and answer the Quiz given on blackboard.

Before you attempt this assignment, make sure that you have gone through the pdf: Measuring Evaluation results with Excel.

Data manipulation

Open the survey data.xlsx file. You can see that variables have their responses in text, while some are in numbers. Some variables are categorical and some are continuous. Identify such variables first before going ahead with the Qs.

You will need to change how data is represented and coded before answering these questions. For some of the Qs below you will have to change the answers text to numbers (Per a crowd flowerreport data scientists spend 51% of their time cleaning, labeling and manipulating data. So, get used to it.)

QUESTIONS

1. Report differences between the segments for the following survey items:
   1. Consumers overall rating of AWESOME SPORTS (Q10)
   2. If they would recommend AWESOME SPORTS (Q11)
   3. Consumers overall satisfaction for Awesome Sports (Q17)
   4. Purchase from Awesome sports in last 3 months
   5. Trust in Awesome Sports (Q20)

Notes: Segments are the four segments given in your data with the column segments (corporate, members, new customer, returning customer). Asking a data analyst to report differences between segments is probably one of the most asked questions in the industry. So, in a descriptive analytics report the analyst would take some key items (such as the ones 1.1 1.5 listed above) and show/describe how each segment is different across these items (and assess whether each segment is statistically different by conducting statistical tests for means and promotions which we will cover next week). The items can either be continuous (such as consumers overall rating) or dichotomous/categorical (such as recommendation: yes/no/unsure).

For continuous items - such as Q10, Q17 and Q20 given above change the text to numbers, and calculate the mean and standard deviation for each segment. For categorical items, you dont need to change the text to numbers calculate the number and/or percentage of people who said yes/no.

[The easiest way to get these descriptives (mean, std dev, %age of respondents) is to use pivot tables in Excel. When you create the pivot table, also ask it to report the number of customers in each segment.)

2. What are the differences between the Purchase location for the following survey items:
   1. Consumers overall rating of AWESOME SPORTS (Q10)
   2. If they would recommend AWESOME SPORTS (Q11)
   3. Consumers overall satisfaction for Awesome Sports (Q17)
   4. Purchase from Awesome sports in last 3 months
   5. Trust in Awesome Sports (Q20)

Notes: Purchase locations are given in the answer to Q12__Where_do_you_normally_purchase_from_. You would follow the same steps as the ones in Q1 above, but divide customers by their usual purchase channel/location (Outlets/ Online/ In store/ 3rd Party).

3. What is the correlation between the following (calculate pearson correlation numbers; the link on blackboard shows you how to do that in Excel):
   1. Satisfaction (Q17) and attitude towards pricing (Q15)
   2. Satisfaction (Q17) and Preference towards Awesome Sports (Q19)