As a manager, you will be required to understand and draw conclusions based on enterprise data. This unit’s application exercise uses data from a simpler CRM system. The CRM data is available for you in two different formats: Excel and Microsoft Access. Please feel free to use any one of these formats. These files contain data on the following:
Customer data including name, address, emails, phone numbers etc.
Customer demographics data
Customer call data
Customer sales data via different channels: web sales, telephone sales, field sales retail sales
Data on advertisements directed towards the customers
PART A:
Using this data, you can address questions such as the following:
Is there a relationship between the income ranges of customers (consider four income ranges: 0-25K, 25-50K, 51-75K, 76-100K, 100+K) and the total purchases made by customers? In the Excel file, these income ranges are already codes as income levels 1, 2, 3, 4, and 5. Depict this as a chart.
Is there a relationship between the race of customers and the total purchases made by customers? Depict this as a chart.
What is the distribution of total sale amounts into field sales, web sales, retail sales, and telephone sales? What are the percentages? Based on the data, what conclusions do you draw in terms of the type of sales you should focus on?
Compare the total customer purchases made by different genders in the categories of field sales, web sales, retail sales, and telephone sales? Are there trends based on gender in terms of these sales?
What are the total number of customer service calls by each gender? What are the percentages?
The original data was distributed among different worksheets in the Excel file. I combined the data into single Excel file for your convenience. You may not be able to find answers to the above questions using a single pivot table (or a single query). It is fine to use multiple pivot tables (or multiple queries in Microsoft Access) to find answers to these questions. Any methods you use to analyze the data is fine.
For this part (Part A), you are expected to complete this assignment in Microsoft Excel or Microsoft Access. Also, submit a Microsoft Word document with your answers to the five questions above.
PART B:
Follow the steps below.
Review the practice work on the level of detail calculations at this course page.
Download the online retail data file. ((Source: This file is adapted from the data set UCI Machine Learning Repository: https://archive.ics.uci.edu/ml/index.phpLinks to an external site.)
Using Tableau, connect to the online retail data file. Create a calculated field called “Sale Amount” using the following formula:
[Unit Price] * [Quantity]
Create a calculated field called “ValuedCustomer” using the formula (a customer is a valued customer if the total sale amount is above $5000): {FIXED[Customer ID] : SUM([Sale Amount])} > 5000
Create a new worksheet called “Valued Customer 1”. Show “Customer ID”, “ValuedCustomer” as rows on this sheet and SUM(Sale Amount) as columns.
Create a new worksheet called “Valued Customer 2”. Show “Customer ID”, “Year(Invoice Date)”, “ValuedCustomer” as rows on this sheet and SUM(Sale Amount) as columns.
For the remaining steps below, refer to the attached annotated PowerPoint file which depicts how the visualizations should look like. (This PowerPoint file is produced from the Tableau visualizations and each PPT slide is annotated with hints/directions on how to create the visualization.)
Create a new worksheet called “# Valued Customers by Country”. Use Country as row, “ValuedCustomer” and CNTD(Customer ID) as columns. Refer to slide 1 of the above PPT file.
Create a new calculated field called “Orders by Customers” with the following calculation: {INCLUDE [Customer ID] : COUNTD( [Invoice No])}. Create a new worksheet called “Sum of Orders by Customer by Country”. Drag and drop country to the details area of Marks. Drag and drop “Orders by Customers” into the “Color” area of Marks. This should show “Orders by Customers” using SUM function. Refer to slide 2 of the above PPT file.
Create a new worksheet called “Ave # of Orders by Customer by Country”. This is similar to step 7, except that use AVE function instead of SUM. Refer to slide 3 of the above PPT file.
Create a new worksheet called “Total Sale Amount by Country”. Use Country as row, “SUM(Sale Amount)” as columns. Refer to slide 4 of the above PPT file.
Create a new worksheet called “Avg Sale Amount by Country”. Use Country as row, “AVG(Sale Amount)” as columns. Refer to slide 5 of the above PPT file.
Create a new worksheet called “Avg. Sale Amount by Country by Year”. Use Country as row, “YEAR(Invoice Date)” as columns, and “AVG(Sale Amount)” as columns. Refer to slide 6 of the above PPT file.
Create a new calculated field “Avg Sale Amount Exclude Invoice Date” using the formula below:
{EXCLUDE [Invoice Date] : AVG([Sale Amount])}
Create a new calculated field “DiffBetweenSaleAmtAvgSaleAmt” using the formula below:
AVG([Sale Amount])-AVG([Avg Sale Amount Exclude Invoice Date])
Create a new worksheet called “Average Sale Amount by Country by Year II”. Use Country as row, “YEAR(Invoice Date)” as columns, and “AVG(Sale Amount)” as columns. Drag and drop “Avg Sale Amount Exclude Invoice Date” as columns. Drag and drop “DiffBetweenSaleAmtAvgSaleAmt” as columns. Refer to slide 7 of the above PPT file.
For this part (Part B), export your work into one file, Tableau packaged workbook file (.twbx file), and submit the .twbx file.
Instructions:
You need to submit the following:
(1) For part (A), you need to submit the Microsoft Excel or Microsoft Access files and a Microsoft Word document with your answers to the five questions.
(2) For part (B), you need to submit one .twbx file with all your work