🚗 End-to-End Car Sales Analytics & Dealer Performance Dashboard (2022 – 2023) 

1. Executive Summary

This project delivers an Executive Sales & Inventory Dashboard for automotive dealership management to evaluate historical performance across 2022 – 2023 and formulate future inventory allocation strategies. Raw transactional data was systematically cleansed and transformed using SQL (MySQL) to ensure data integrity (removing duplicates, standardizing region and engine attributes, casting date types, and dropping non-essential columns).The refined dataset was integrated into Tableau to construct an interactive multi-dimensional dashboard. 

The analysis reveals seasonal sales fluctuations with an all-time low in February 2022 ($9M), a peak in November 2023 ($54M), and an average monthly revenue line of $28M. A distinct annual recurring pattern was identified, marked by a sales decline from September to October, followed by a sharp rally toward peak sales in November and December. Regionally, Austin and Janesville recorded the highest volume of sold units, whereas Hardtop sales in Scottsdale were exceptionally low. Based on these insights, key recommendations focus on supply chain efficiency, regional inventory realignment, and seasonal campaign planning.

2. Business Problem 

• Background: Dealership leadership required a consolidated analytical report for the 2022 – 2023 period to monitor revenue trends, analyze sold car units per region to inform future inventory strategies, and understand customer purchasing power across income levels and gender demographics.

• Key Analytical Questions:

  • Data Integrity & Pipeline (SQL): How do we ensure raw transaction records are free of duplicates, string inconsistencies, blank fields, and invalid date formats?

  • Sales Trend & Seasonality (Tableau): What are the monthly sales dynamics across 2022–2023? What is the baseline average revenue, and are there any recurring trends?

  • Inventory & Regional Sales Strategy (Tableau): Which regions generate the highest volume of sold units, and which vehicle body styles should be expanded or reduced per region in upcoming planning cycles?

  • Customer Segmentation & Product Popularity (Tableau): Which vehicle brands attract higher annual income brackets across male and female buyer cohorts?

3. Data Architecture, Preparation & Tech Stack

• Tools: SQL (MySQL), Tableau Desktop.

• Data Cleansing Pipeline (MySQL):

  • Table Staging & Column Renaming: Created a staging table (car_st) from raw data (car) to preserve raw data integrity, standardizing column names to snake_case (customer_name, annual_income, price, body_style).

  • Deduplication Check: Executed GROUP BY ... HAVING COUNT(*) > 1 queries across date, customer_name, company, model, and price to isolate and resolve duplicate transactions.

  • Data Standardization & Cleaning:

    • Applied TRIM() across string variables to eliminate leading and trailing spaces.

    • Standardized engine descriptions (e.g., correcting double% entries to 'Double Overhead Camshaft').

    • Standardized regional naming variations (e.g., unifying Middletown entries on dealer_region).

  • Date Parsing & Type Casting: Parsed string dates (MM/DD/YYYY) into standard SQL date formats (YYYY-MM-DD) using STR_TO_DATE() and modified column datatypes to DATE.

  • Null Handling & Column Pruning: Verified zero nulls across critical variables (car_id, date, price, and annual_income) and dropped non-essential attributes (ALTER TABLE car_st DROP COLUMN phone).

• Advanced Analytics & Visualization (Tableau):

  • Interactive Controls & Collapsible Drawer: Integrated global filters for Transmission, Company, YEAR, Gender, dynamic Top N Dealer parameters (Top 3/5/10), and custom toggle switches.

  • Multi-Dimensional Visual Mappings:

    • Line Chart with Reference Line: Plotted Sales Seasonal Trend against a baseline average line ($28M).

    • Horizontal Bar Chart: Identified Top 3 Dealers alongside their primary revenue-driving brands.

    • Heatmap Matrix: Visualized Inventory Strategy based on total units sold per region across vehicle body styles.

    • Side-by-Side Horizontal Bar Chart: Segmented customer purchasing power (Annual Income) across Female vs Male cohorts.

    • Treemap: Visualized Product Popularity by brand volume market shares (Chevrolet, Dodge, etc.).

 4. Key Insights & Dashboard Metrics

📈 Performance & Trends (2022 – 2023)

• Sales Seasonal Trend & Recurring Patterns:

  • Average monthly revenue stood at $28M, with the historical lowest point in February 2022 ($9M) and the highest peak in December 2023 ($54M).

  • A clear annual recurring pattern emerged, characterized by consecutive sales declines from September to October. However, this trend quickly reversed and rallied sharply in November, reaching its peak in December 2023 ($54M).

• Top 3 Dealer Performance:

  • Rabun Used Car Sales: Leads with $37M in revenue (top contributor: Ford).

  • Progressive Shippers Cooperative Association No: Second with $37M in revenue (top contributor: Chevrolet).

  • U-Haul CO: Third with $36M in revenue (top contributor: Dodge).

• Inventory Strategy (Regional Sales Volume):

  • Austin and Janesville achieved the highest number of sold car units overall, led by SUVs (1,079 units in Austin; 983 in Janesville) and Hatchbacks (1,024 units in Austin; 987 in Janesville). These regions represent top priorities for expanded unit supply in upcoming cycles.

  • Sales of Hardtop models in Scottsdale were exceptionally low (234 units), indicating low consumer demand for this body style in that area.

👥 Customer Segmentation & Product Popularity

• Customer Segment by Annual Income:

  • Female Cohort: Highest average income buyers favored Infiniti ($862K), while the lowest average income was associated with Buick ($618K).

  • Male Cohort: Highest average income buyers favored Saab ($976K), while the lowest average income was associated with Jaguar ($737K).

• Product Popularity (Treemap):

  • Chevrolet, Dodge, and Ford emerged as the top 3 most popular brands, dominating the largest share of total sales volume on the Treemap.

5. Business Recommendations

1. Seasonal Trend Management (September-October Dip vs November-December Peak):

   • Capitalize on the recurring sales dip in September and October for inventory maintenance, pricing evaluations, and large-scale promotional planning. This preparation is essential to capture the steep demand rally that consistently surges in November and December (peaking at $54M).

2. Demand-Driven Regional Unit Supply Adjustment:

   • Significantly expand vehicle supply in high-volume regions like Austin and Janesville (specifically SUVs and Hatchbacks), while streamlining Hardtop inventory in Scottsdale to prevent slow-moving stock accumulation.

3. Income-Segmented Marketing Campaigns:

   • Target premium brand promotions (e.g., Saab and Infiniti) toward high-income demographics ($800K+ annual income), while focusing Chevrolet, Dodge, and Ford marketing toward mass-market sales volume.

4. Supply Chain Priority for Top Performing Dealers:

   • Maintain priority stock distribution for top-performing dealerships (Rabun Used Car Sales, Progressive Shippers, and U-Haul CO) that consistently serve as the largest revenue drivers (contributing $36M – $37M each).