Data Analyst Project — Sales Management Analysis
Business Request and User Stories
The aim of this project is to build an Internet Sales Dashboard for the Manager. The manager wants to keep track revenue gained from each product and customer so that he can make decision on improving unit sales and profit. Base on the request we following user stories to ensure that all criteria’s were maintained throughout the project. The data was used AdventureWorksDW2019.bak
![](https://miro.medium.com/v2/resize:fit:700/1*8WJDCG6UKDue9X1Z_XpSdg.png)
Preparing Data (SQL)
Base on manager’s requirement, to create the appropriate data model for analyzing and answer manager’s questions the following tables were extracted by using SQL. Moreover, a data source (sale budgets) were provided in Excel format and were connected in the data model in Power Bi. The SQL statements are presented below
DIM_Calendar
-- Cleansed DIM_Date Table --
SELECT
[DateKey],
[FullDateAlternateKey] AS Date,
--[DayNumberOfWeek],
[EnglishDayNameOfWeek] AS Day,
--[SpanishDayNameOfWeek],
--[FrenchDayNameOfWeek],
--[DayNumberOfMonth],
--[DayNumberOfYear],
--[WeekNumberOfYear],
[EnglishMonthName] AS Month,
Left([EnglishMonthName], 3) AS MonthShort, -- Useful for front end date navigation and front end graphs.
--[SpanishMonthName],
--[FrenchMonthName],
[MonthNumberOfYear] AS MonthNo,
[CalendarQuarter] AS Quarter,
[CalendarYear] AS Year --[CalendarSemester],
--[FiscalQuarter],
--[FiscalYear],
--[FiscalSemester]
FROM
[AdventureWorksDW2019].[dbo].[DimDate]
WHERE
CalendarYear >= 2019
DIM_Product
-- Cleansed DIM_Products Table --
SELECT
p.[ProductKey],
p.[ProductAlternateKey] AS ProductItemCode,
-- ,[ProductSubcategoryKey],
-- ,[WeightUnitMeasureCode]
-- ,[SizeUnitMeasureCode]
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
-- ,[SpanishProductName]
-- ,[FrenchProductName]
-- ,[StandardCost]
-- ,[FinishedGoodsFlag]
p.[Color] AS [Product Color],
-- ,[SafetyStockLevel]
-- ,[ReorderPoint]
-- ,[ListPrice]
p.[Size] AS [Product Size],
-- ,[SizeRange]
-- ,[Weight]
-- ,[DaysToManufacture]
p.[ProductLine] AS [Product Line],
-- ,[DealerPrice]
-- ,[Class]
-- ,[Style]
p.[ModelName] AS [Product Model Name],
-- ,[LargePhoto]
p.[EnglishDescription] AS [Product Description],
-- ,[FrenchDescription]
-- ,[ChineseDescription]
-- ,[ArabicDescription]
-- ,[HebrewDescription]
-- ,[ThaiDescription]
-- ,[GermanDescription]
-- ,[JapaneseDescription]
-- ,[TurkishDescription]
-- ,[StartDate],
-- ,[EndDate],
ISNULL (p.Status, 'Outdated') AS [Product Status]
FROM
[AdventureWorksDW2019].[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
DIM_Customers
- Cleansed DIM_Customers Table --
SELECT
c.customerkey AS CustomerKey,
-- ,[GeographyKey]
-- ,[CustomerAlternateKey]
-- ,[Title]
c.firstname AS [First Name],
-- ,[MiddleName]
c.lastname AS [Last Name],
c.firstname + ' ' + lastname AS [Full Name],
-- Combined First and Last Name
-- ,[NameStyle]
-- ,[BirthDate]
-- ,[MaritalStatus]
-- ,[Suffix]
CASE c.gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS Gender,
-- ,[EmailAddress]
-- ,[YearlyIncome]
-- ,[TotalChildren]
-- ,[NumberChildrenAtHome]
-- ,[EnglishEducation]
-- ,[SpanishEducation]
-- ,[FrenchEducation]
-- ,[EnglishOccupation]
-- ,[SpanishOccupation]
-- ,[FrenchOccupation]
-- ,[HouseOwnerFlag]
-- ,[NumberCarsOwned]
-- ,[AddressLine1]
-- ,[AddressLine2]
-- ,[Phone]
c.datefirstpurchase AS DateFirstPurchase,
-- ,[CommuteDistance]
g.city AS [Customer City] -- Joined in Customer City from Geography Table
FROM
[AdventureWorksDW2019].[dbo].[DimCustomer] as c
LEFT JOIN dbo.dimgeography AS g ON g.geographykey = c.geographykey
ORDER BY
CustomerKey ASC -- Ordered List by CustomerKey
FACT_InternetSales
-- Cleansed FACT_InternetSales Table --
SELECT
[ProductKey],
[OrderDateKey],
[DueDateKey],
[ShipDateKey],
[CustomerKey],
-- ,[PromotionKey]
-- ,[CurrencyKey]
-- ,[SalesTerritoryKey]
[SalesOrderNumber],
-- [SalesOrderLineNumber],
-- ,[RevisionNumber]
-- ,[OrderQuantity],
-- ,[UnitPrice],
-- ,[ExtendedAmount]
-- ,[UnitPriceDiscountPct]
-- ,[DiscountAmount]
-- ,[ProductStandardCost]
-- ,[TotalProductCost]
[SalesAmount] -- ,[TaxAmt]
-- ,[Freight]
-- ,[CarrierTrackingNumber]
-- ,[CustomerPONumber]
-- ,[OrderDate]
-- ,[DueDate]
-- ,[ShipDate]
FROM
[AdventureWorksDW2019].[dbo].[FactInternetSales]
WHERE
LEFT (OrderDateKey, 4) >= YEAR(GETDATE()) -2 -- Ensures we always only bring two years of date from extraction.
ORDER BY
OrderDateKey ASC
Data Model
After importing data to Power Bi, the image below is the data model
Sales Management Dashboard
The dashboards below with one page with works as a dashboard and overview, with two other pages focused on combining tables for necessary details and visualizations to show sales over time, per customers and per products.