Adventure Works Sales Analysis

Situation

• The project used the Adventure Works dataset to create interactive dashboards analyzing sales performance. The main focus was to provide insights into customer behavior, product sales, and regional performance using Power BI.

• The business context was a request from the sales department to analyze recent sales trends to refine their strategy and marketing efforts.

Task

• My role as a Data Analyst involved developing a Power BI dashboard that would track key metrics like internet sales performance by product and customer, enabling the sales team to make informed decisions.

• I was responsible for data cleaning, modeling, and creating visualizations based on the user stories defined by the sales team.

Action

• I started by importing and cleaning data from the Adventure Works database using SQL. This involved removing null values, standardizing date formats, and joining customer and product tables.

• I then built a relational data model in Power BI, connecting sales, customers, and products for a comprehensive analysis. Key SQL queries used during the cleaning process can be found

SELECT 
        c.customerkey AS CustomerKey, 
        c.firstname AS [First Name], 
        c.lastname AS [Last Name], 
        c.firstname + ' ' + lastname AS [Full Name], 
        CASE c.gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS Gender,
        c.datefirstpurchase AS DateFirstPurchase, 
        g.city AS [Customer City] -- Joined in Customer City from Geography Table
    FROM 
        [AdventureWorksDW2022].[dbo].[DimCustomer] as c
    LEFT JOIN dbo.dimgeography AS g ON g.geographykey = c.geographykey 
    ORDER BY 
        CustomerKey ASC

SELECT 
        p.[ProductKey], 
        p.[ProductAlternateKey] AS ProductItemCode, 
        p.[EnglishProductName] AS [Product Name], 
        ps.EnglishProductSubcategoryName AS [Sub Category], -- Joined in from Sub Category Table
        pc.EnglishProductCategoryName AS [Product Category], -- Joined in from Category Table
        p.[Color] AS [Product Color], 
        p.[Size] AS [Product Size], 
        p.[ProductLine] AS [Product Line], 
        p.[ModelName] AS [Product Model Name], 
        p.[EnglishDescription] AS [Product Description], 
        ISNULL (p.Status, 'Outdated') AS [Product Status] 
    FROM 
        [AdventureWorksDW2022].[dbo].[DimProduct] as p
        LEFT JOIN dbo.DimProductSubcategory AS ps ON ps.ProductSubcategoryKey = p.ProductSubcategoryKey 
        LEFT JOIN dbo.DimProductCategory AS pc ON ps.ProductCategoryKey = pc.ProductCategoryKey 
    order by 
        p.ProductKey asc

• Finally, I created dashboards with three key pages: an overview of sales, a detailed view of internet sales by customer, and a breakdown of internet sales by product.

Result

• The dashboards provided actionable insights, allowing sales managers to track sales trends over time, identify top-performing products, and focus on high-value customers.

• The solution helped improve decision-making and provided the sales team with data that could inform marketing efforts, leading to more targeted strategies.

• The dashboards were set to refresh daily, ensuring the data was always up to date for accurate analysis.

Reflection

• From this project, I enhanced my skills in SQL, data modeling, and Power BI. I also learned the importance of structuring user stories to align with business needs.

• If I were to do this again, I would integrate predictive analytics to forecast future sales performance based on historical data.

• My advice for anyone tackling a similar project is to clearly define your data relationships early on and ensure your visualizations directly address the business questions at hand.