Description

Comic Mail Order

Background:

The provided access database is a mail-order comic book retailer’s order data from 1995-2001. 

ComicBusinessExample.mdb

Currently, the database does not contain calculated tables for prices charged to the customer nor the item costs from the suppliers to the business.  These must be calculated by the discount codes and customer agreements.  In addition, the database does not explicitly cover other costs of goods sold (i.e. shipping costs, travel expenses, etc.).  A summary of some of the pertinent tables within the database are provided below.

Tables:

Accounts: Customer table

Orders: Customer orders by monthly period

Products: Monthly offerings from the distributor.  Special note: The last character of the item code is the discount code.

Suppliers:  Product suppliers

Discounts: Supplier percentage discount for products purchased by the business

OrderDateSequence: Converting monthchar+year to a oldest to newest sequence

User: online app use only

ComicsReceived: Fulfilled orders

ComicOrderDates.xlsx

Your Task:

1. Analyze the database, considering what preparation steps must take place to render this data more useful (in class: 0 points)
   • Which data does not fit within the analysis paradigm that we require?
   • What transformations must take place to make the data useful for analysis?
2. Prepare and transform the data, “CRUD”ing the data as necessary (in class: 0 points).
   • Make a backup of your data first.
   • Make changes after.
   • Make more backups.
3. Bring the access db into Tableau (in class: 0 points).
4. Create visualizations for the data answering the listed questions.  Summarize your findings in written form as well.  You will be evaluated on accuracy (content, calculations, and conclusions) and effectiveness (choice of visualization, answering the question, and summary).  ( assignment: 28 points).
   • What is overall ordering trend by the customers?
   • What is the overall ordering trend from the suppliers?
   • Has there been a shift in pricepoints from the various suppliers (both in terms of items offered and in terms of items ordered)?
   • What are the spending patterns of specific customers in terms of their pricepoint and quantity ordered?
5. Detail a plan to consider business profit in the analysis.  Create this plan as if you will need to hand it off for someone else to implement (Evaluated on likelihood of successful implementation 12 points).
   • Identify data transformations that must take place
   • Create a plan for each data transformation
   • Identify additional data that will be required (potentially from other sources not yet provided)
   • Describe the assumptions made
   • Discuss additional analysis that could be performed
   • Detail the questions that could be asked and answered by this analysis