📈 End-to-End Sales & Customer Performance Dashboard (Year-over-Year Analysis) 

1. Executive Summary

This project aims to deliver an interactive executive dashboard for the management team to evaluate current-year (2023) business performance against the prior year (2022). By integrating four distinct data entities, the dashboard maps revenue growth, profitability margins, product category efficiency, and high-value customer contributions. The analysis reveals that while Total Sales grew +20.36% YoY ($733K) and Total Profit increased +14.24% YoY ($93K), there were significant profit losses in the Tables and Machines sub-categories, alongside a sharp dip in profitability in April before stabilizing through year-end. Based on the analysis, key recommendations include capitalizing on year-end sales surges starting in August, conducting operational audits to evaluate April performance, restructuring pricing for Tables and Machines, and implementing retention programs to optimize Customer Lifetime Value (CLV).

2. Business Problem & Objectives

• Background: Management requires a comprehensive visual interface to compare key performance indicators (KPIs) for 2023 against 2022 to guide upcoming business planning and annual budget allocations.

• Key Analytical Questions:

  • Sales & Profit Dynamics (YoY): What are the monthly sales and profit growth trends in 2023 compared to 2022? Which months drive revenue spikes, and when does the most severe profit drop occur?

  • Product & Customer Performance: Which sub-categories are the most profitable versus loss-making? To what extent does customer purchase frequency impact total revenue?

3. Data Architecture, Preparation & Tech Stack

• Tools: Tableau Desktop, MS Excel (Data Inspection).

• Data Pipeline & Modeling:

  • Data Cleaning: Resolved semicolon-separated values (;) formatting in raw CSV files (Customers.csv, Location.csv, Orders.csv, Products.csv) to parse them into structured columns.

  • Data Relationships / Joins: Linked all four tables on the Tableau Data Source canvas using key relationships (Star-Schema):

    • Orders.csv (Fact Table) with  Customers.csv via Customer ID

    • Orders.csv with Products.csv via Product ID

    • Orders.csv with Location.csv via Postal Code

• Advanced Tableau Calculations & Features:

  • Calculated Fields YoY: Applied custom formulas to compute YoY percentage increases/decreases across Sales, Profit, Quantity, and Customer Count:
    YoY Growth = (CY Metric - PY Metric) / PY Metric

  • Min/Max Dot Highlights: Added automated visual indicators (Highest Month vs Lowest Month) on sparkline trendlines to visually capture peaks and troughs.

  • Collapsible Filter Panel & Navigation: Designed a collapsible filter panel (Show/Hide Filter Container) and interactive navigation icons between the Sales Dashboard and Customer Dashboard.

4. Key Insights & Dashboard Metrics 

📊 Dashboard 1: Sales & Profit Performance (2023 vs 2022)

• Key Financial Performance (KPIs):

  • Total Sales: $733K (▲ +20.36% vs PY)

  • Total Profit: $93K (▲ +14.24% vs PY)

  • Total Quantity Sold: 12K units (▲ +26.83% vs PY)

• Monthly Trend Dynamics (Peak & Trough):

  • Sales Peak: Peak sales occurred in November ($118K), followed by September ($88K) as the second highest, and December ($84K).

  • Profit Trough: Profit experienced a steep decline in April ($1K), dropping significantly from $3K in April of the prior year.

  • Performance Bounce Back: Following the April drop, profitability staged a steady recovery in subsequent months and remained consistent through year-end.

• Sub-Category Performance (Sales vs Profit/Loss):

  • Top Profit Contributors: The Copiers, Phones, and Accessories sub-categories served as primary profit drivers.

  • Profit Bleed / Loss Leaders: The Tables sub-category recorded the most severe losses (-$8K), followed by Machines (-$3K). Despite healthy sales volumes for Tables, pricing failed to yield positive profit margins.

👥 Dashboard 2: Customer Behavior & Interactivity

• Customer Metrics (YoY):

  • Total Customers: 693 (▲ +8.62% vs PY)

  • Total Sales per Customer: $1,058 (▲ +10.80% vs PY)

  • Total Orders: 1,687 orders (▲ +28.29% vs PY)

• Customer Order Distribution:

  • The majority of customers were concentrated at 1 order (200 customers) and 2 orders (200 customers) throughout the year.

• Top High-Value Customers:

  • The #1 ranked customer (Raymond Buch) generated $6,781 in profit from total purchases of $14,203 across just 3 orders.

5. Business Recommendations

1. Capitalize on Seasonal Peak Trends (September & November Peaks):

   • Given that major sales spikes occur in September ($88K) and November ($118K), marketing and operations teams should concentrate ad campaign budgets and stock readiness for high-margin products (such as Copiers & Accessories) starting in Q3 (August).

2. Mitigate Profit Anomalies in April:

   • Conduct an in-depth operational audit into the root causes of the April profit drop ($1K). Management must evaluate whether this decline was driven by excessive discounting, increased logistics costs, or post-Q1 seasonality to implement cost-efficiency strategies ahead of April next year.

3. Restructure Pricing & Discounts for 'Tables' & 'Machines':

   • The Tables and Machines sub-categories are actively eroding overall profitability (profit bleed). It is recommended to revise maximum discount thresholds or reduce shipping costs associated with these product lines.

4. Implement Retention Programs to Drive Repeat Purchases:

   • With 57.7% of customers transacting only 1–2 times, implementing automated post-purchase re-engagement campaigns can lift purchase frequency to 3–4 orders, optimizing overall Customer Lifetime Value (CLV).