Interactive Retail Dashboard

Project Objectives

  • Load a CSV file into Excel, conduct data profiling and build a custom calendar to track performance by day, month, quarter and year.
  • Building a relationship model by foreign keys in the Sales table to primary keys in each dimension table.
  • Add calculated measures to track key business metrics, including total orders, revenue, average order value and delivery time.
  • Design an interactive report for further analysis.

Tools Used

  • Excel

Business Problem

Revenue shows a downward trend since 2020; the goal is to consolidate data to conduct an exploratory analysis.

Data Model

  • The data model shows relationships created from the Sales table to Customers, Stores, Products and Calendar.

KPis (Key Performance Indicators)

  • Creating a measure named Total Orders, based on Order Number. How has order value trended over time? Do all product categories show a similar trend?
  • Calculate Total Revenue (USD), based on Quantity and Unit Price (USD). Which stores generate the most revenue? Which individual products?
  • Calculate Average Order Value (AOV), based on Total Revenue (USD) and Total Orders. How does AOV compare across product categories? Are there differences based on customer age?
  • Calculate Average Delivery Time, in days. Which types of products tend to be delivered fastest or slowest? Have delivery times improved or gotten worse?

Final Report

Click on Image to Access Dashboard:

Summary

  • Revenue has been on a steady increase until April of 2020, the chart shows a downshift and remains consistent. Another pattern shows an emerging dip in revenue every April since 2016, reasons vary; further analysis needed. Delivery days has shown improvement since 2016, from an average of 7 days to 4 days as of 2021. Computers drive the bulk of the revenue since 2016, which signals a continuing pattern in the following years. More analysis is needed to dive into subcategories and profit margins to figure out which products drive the most revenue.