Description

For the following scenarios / questions use Data Set 1 and Data Set 1a:

Wrangling: Part A

1. Data set #1 is missing 3 columns of data. This data can be found in data set 1a. Use a formula learned in class to bring these additional columns (UserName, CompanyName, JobTitle) into data set #1.
2. Create a new column called "FullName" and use an equation learned in class to bring together the first and last names into the same cell.

Analyzing Customer Demographics and Sales: Part B (Use the best equation to solve these problems, do not use pivot tables and make sure "flexible" equations are used, meaning the data set can be updated without the need to update your equations. You may have the "right" answer, but if your answer doesn't change as data is updated, points will be deducted):

1. Show the top 5 "best" customers defined by the most number of purchases and then defined by the total value of purchases. How are they different / similar?
2. Use an equation learned in class and pull in the companies of those customers that made the top 3 number of purchases.
3. What is the distribution of customer diversity (race) by country? How does the diversity in the data set compare to that of the nation? Would this be an accurate comparison, why or why not?
4. What is the unique customer gender distribution in the data set for those customers in the US?
5. Show the total sales by gender and race in the data set. Which gender / race combination has the most / least total sales?

For the following scenarios / questions use Data Set 2:

Wrangling: Part C

1. Create two additional columns called Month and Year. Use formulas learned in class to extract the month and year into each column respectively.

Analyzing Sales: Part D (Do not use equations to solve these problems, use pivot tables.)

1. Show the total sales amount by year. Which year had the highest / least amount of sales?
2. Show the average sales amount by month for all years. Which month had the highest / lowest average of sales amount?
3. Show the total sales amount by the customer's company. Order the list by total sales in descending order (most to least).  What are the top 5 companies by sales?
4. Show the total sales amount by credit car company. Order the list by totals sales in descending order. Which credit card company has the most sales, which has the least?
5. Show the average sales amount by state. Which state has the highest / lowest sales average?