Adventure Works Dashboard

Dashboard Preview :

Introduction

The AdventureWorks Sales Dashboard provides a comprehensive view of the company's sales performance from 2019 to 2024. It tracks key metrics such as total sales, budget vs. sales performance, and top customers and products. The dashboard helps stakeholders understand sales trends across different time periods, product categories, and regions, enabling data-driven decisions that improve sales strategies and enhance customer satisfaction.

The data is presented through interactive visuals, allowing users to explore the performance by product, region, and customer segments.

Steps followed

• Step 1: Loaded data into Power BI Desktop from MS SQL Server database.
• Step 2: In the Power Query editor, used data profiling options to check column distribution, quality, and profile across the entire dataset.
• Step 3: Cleaned data by addressing missing values and ensuring all relevant columns were accurate.
• Step 4: Created various visuals, including bar charts, line charts, and card visuals to represent key metrics such as total sales, sum of budget, sales by product category, etc.
• Step 5: Applied slicers for month and years to allow dynamic filtering of the visuals.
• Step 6: Created measures to calculate budget amount, sales and sales/budget amount using DAX expressions.
• Step 7: Designed the dashboard layout and applied visual filters to allow the end-user to drill down into specific sales data.

Data Cleaning Process Using SQL

Before importing the data into Power BI, I used SQL queries to clean and prepare the data. The SQL data cleaning process involved:

Removing Duplicates

• I ran queries to check for and remove any duplicate records in the tables to ensure data accuracy.

Handling Missing Values

• Queries were used to identify missing or null values in critical columns such as SalesAmount and CustomerKey. Where appropriate, missing values were replaced or excluded to avoid skewing the data.

Correcting Data Types

• Ensured that all numeric fields (e.g., SalesAmount, OrderQuantity) were of the correct data type to prevent errors during analysis.

Filtering Data

• The dataset was filtered to include only records from 2019 to 2024, which was necessary to align the analysis period with the scope of the dashboard.

Total Sales

• $22.24M (as of 2024)
• Represents the overall revenue generated by the company during the reporting period.

Sum of Budget

• $21M
• Tracks the budgeted revenue, allowing a comparison between actual sales and budgeted goals.

Sales by Product Category

• Accessories: $22.24M
• Bikes: $22.24M
• Clothing: $22.24M
• Components: $22.24M
• Highlights the sales distribution across product categories, with bikes and accessories being major contributors to the total sales.

Sales by Top 10 Customers

• Jordan Turner: $16K
• Maurice Shan: $13K
• Janet Munoz: $12K
• Identifies the customers who have contributed significantly to overall sales.

Sales by Top 10 Products

• Touring-1000 Blue, 46: $12K
• Mountain-200 Black, 46: $11K
• Mountain-200 Silver, 46: $11K
• This highlights the most popular products that drive revenue.

Sales Trend Analysis

Sales and Budget by Month

• The dashboard shows fluctuations in monthly sales performance, with peaks observed in July and December, indicating seasonal trends. Comparing sales against the budget helps identify months where the business either exceeded or fell short of financial expectations.

Sales by Customer City

• The geographic distribution of sales across various cities provides insight into the regions with the highest revenue, aiding in regional strategy planning.

Sales Breakdown by Product Category and Customer Segment

Sales by Product Category

• Accessories, bikes, and components are the key drivers of revenue. The sales are distributed almost equally across these categories, with each contributing a significant portion to the overall revenue.

Top 10 Customers

• The top 10 customers, such as Jordan Turner, Maurice Shan, and Janet Munoz, contribute a large share of the total sales, highlighting the importance of maintaining strong relationships with these key clients.

Product Performance Insights

Sales by Top 10 Products

• Touring-1000 Blue, 46 is the top-selling product, followed by Mountain-200 Black, 46 and Mountain-200 Silver, 46.
• These products are consistently high-performing, offering a clear direction for inventory planning and marketing focus.

Sales by Product Category

• Bikes and Accessories are particularly high in demand, contributing equally to overall sales.
• This insight can guide future product development and promotional efforts.

Conclusion and Actionable Insights

The AdventureWorks Sales Dashboard provides a clear view of the company's performance over time, with a focus on key metrics such as sales by product category, top customers, and geographic trends. The SQL-based data cleaning process ensured that the analysis is built on clean, accurate data, further improving the reliability of the insights.

Key Takeaways:

• Product Strategy: Focus on promoting top-selling products such as Touring-1000 Blue and Mountain-200 series. Consider boosting the inventory for these products during peak sales months.
• Customer Retention: Top customers like Jordan Turner and Maurice Shan drive significant sales. Offering personalized promotions or loyalty programs to these customers can help increase revenue.
• Geographic Expansion: Identify high-potential regions for expansion or increased marketing efforts based on the sales distribution by city.
• These insights, combined with regular data monitoring, will enable AdventureWorks to enhance its sales strategy, improve customer engagement, and optimize operational performance.