Assignment:
Telecommunications service
Q: A consulting firm is investigating the level of service provided by a telecommunication company in resolving customer issues. Data was obtained from a sample of 999 customer reviews which included variables such as age, income, number of enquiries made to resolve issue, etc. This information is available in the attachment which has columns containing the following variables.
• Customer_ID: customer's ID
• Age: age = 1
• Gender: 1 = female, 0 =male
• Married: marital status, Y = married, N = otherwise
• Postcode: postcode
• Employment: 1 = Employed, 0 = Unemployed
• Time Spent: time spent to resolve the issue (minutes)
• Rating: rating for the service from 1 to 5 (1=the lowest, 5=the highest)
• Enquiries: number of enquiries made to resolve the issue
Based on the data provided, answer the following questions:
a. Select two different kinds of qualitative variables and two different kinds of quantitative variables. State whether the variable is nominal, ordinal, discrete or continuous. Explain your reasoning in each case.
b. Is the data "cross-sectional" or "time-series" data? Explain your answer.
c. A recent report states the following: i. there is an even spread of the number of enquiries made to resolve the issue. ii. ratings are quite evenly distributed. Do you agree with these two statements? Justify your answers for both statements by providing relevant tables for each statement.
d. Construct two Pivot Tables using the number of enquiries and age groups, one using percentage column total and the other row total. (Hint: Group age using interval widths of 15 years, beginning with 16 years of age).
e. For each level of enquiry,
i. which is the dominant age group? Are they younger or older customers?
ii. why is this the case?
Support your discussion with the aid of a table.
f. Are male customers making more enquiries than female customers? Provide a brief explanation. Support your answer with the aid of a table.
g. Complete the following tables of summary statistics of time spent for each of the following two variables:
i. Gender
ii. Rating (in two groups 1-3 and 4-5)
Give your answers in the following tables to two decimal places and make sure you indicate units. 10 marks
Time Spent
Male Female
Mean
Median
Standard Deviation
First Quartile
Third Quartile
Interquartile Range
Range
Minimum
Maximum
Coefficient of Variation
Time Spent
Rating 1-3 Rating 4-5
Mean
Median
Standard Deviation
First Quartile
Third Quartile
Interquartile Range
Range
Minimum
Maximum
Coefficient of Variation
h. Construct percentage frequency histograms of the Time Spent for the two rating groups:
1) Rating 1-3
2) Rating 4-5
Hint: You may need to sort the data by Rating, then copy the Time Spent data with rating 1-3 to the column K (under the variable name "Time (Rating 1-3)") and that with rating 45 to the column M (under the variable name "Time (Rating 4-5)") separately. In the histogram, group Time Spent using interval widths of 8 minutes, beginning with 0 minutes.
i. Based on your answers obtained in parts g and h, compare the distribution of the variable Time Spent for the two rating groups (Low: rating 1-3 and High: rating 4-5), discussing typical values (i.e. "central tendency"), how spread out the values are ("variability"), and the shape of the distributions. Which of the genders shows more variability in Time Spent? Explain using appropriate evidence. Quote the values to two decimal places and make sure you indicate units.
j. Create a pivot table of the mean Time Spent - broken down by Gender (COLUMNS) and Rating (ROWS). Group the rating variable into two groups (Low: rating 1-3 and High: rating 4-5). Using the suggested approach to pivot table analysis, analyse how the time spent is related to the gender and rating groups. Be sure to quote relevant figures. Give your answers in the pivot table to two decimal places and make sure you indicate units.
k. Using Excel's Pivot Table function, obtain the following tables and copy them into your Word document. Use the "Tabular Form" report layout and label your tables accurately and informatively. Round off percentage values to one decimal place.
Table 1: frequency distribution of the customers - broken down by High (4-5) and Low Rating (1-3) ("COLUMNS") and Gender ("ROWS").
Table 2: the frequency distribution of Table 1, but with values shown as "% of Grand Total".
Table 3: the frequency distribution of Table 1, but with values shown as "% of Column Total".
Table 4: the frequency distribution of Table 1, but with values shown as "% of Row Total".
l. Based on the tables generated in part (k), answer the following questions. Be sure to state the table you have used.
i) How many customers provide a high rating?
ii) What proportion of customers gave a low rating?
iii) If a customer who provides a high rating is selected at random, what is the probability they are male?
iv) Are female customers more likely to provide a high rating?
v) What is the probability that a customer gave a low rating and is female?
Attachment:- Data-Time Rating.rar