Acerca de
Bike Company
Sales Management
Overview
The main goal of this project is to take business requests from management and build sales dashboards on Power BI for sales managers to monitor their business performances (KPIs). Users will have the ability to filter data based on products, customers, geographic location, and more. This project utilizes a sample database environment provided by Microsoft—"AdventureWorks2019.”
Business Request & User Stories
The business request for this data analysis project was an executive sales report for sales managers. Based on the request that was made, the following user stories were defined to fulfill delivery and ensure that acceptance criteria were maintained throughout the project.
Data Cleaning and Transformation - SQL
To create the necessary data model for running analysis and fulfilling the business needs defined in the user stories, the following tables were extracted using SQL.
​
One data source (sales budgets) were provided in Excel format and were connected in the data model in a later step of the process.
​
Below are the SQL statements for cleansing and transforming necessary data:
​
DIM_Calendar:
-- Cleaning DIM_DateTable --
SELECT [DateKey]
,[FullDateAlternateKey] as Date
--,[DayNumberOfWeek]
,[EnglishDayNameOfWeek] as Day
-- ,[SpanishDayNameOfWeek]
-- ,[FrenchDayNameOfWeek]
--,[DayNumberOfMonth]
--,[DayNumberOfYear]
--,[WeekNumberOfYear]
,[EnglishMonthName] as Month,
LEFT([EnglishMonthName], 3) as MonthShort
--,[SpanishMonthName]
--,[FrenchMonthName]
,[MonthNumberOfYear] as MonthNo
,[CalendarQuarter] as Quarter
,[CalendarYear] as Year
--,[CalendarSemester]
--,[FiscalQuarter]
--,[FiscalYear]
--,[FiscalSemester]
FROM [AdventureWorksDW2019].[dbo].[DimDate]
WHERE CalendarYear >= 2020;
DIM_Product:
-- Cleansed DIM_Product Table --
SELECT [ProductKey]
,p.ProductAlternateKey as ProductItemCode
,p.ProductSubcategoryKey
--,[WeightUnitMeasureCode]
--,[SizeUnitMeasureCode]
,p.EnglishProductName as [Product Name]
, ps.EnglishProductSubcategoryName as [Product Subcategory]
, pc.EnglishProductCategoryName as [Product Category]
--,[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]
--,[LargePhoto]
,p.EnglishDescription as [Product Description]
--,[FrenchDescription]
--,[ChineseDescription]
--,[ArabicDescription]
--,[HebrewDescription]
--,[ThaiDescription]
--,[GermanDescription]
--,[JapaneseDescription]
--,[TurkishDescription]
--,[StartDate]
--,[EndDate]
, ISNULL (p.Status, 'Outdated') as [Product Status] -- If Status is Null, then we input 'Outdated'
FROM
[AdventureWorksDW2019].[dbo].[DimProduct] as p
LEFT JOIN [AdventureWorksDW2019].[dbo].[DimProductSubcategory] as ps
ON p.ProductSubCategoryKey = ps.ProductSubcategoryKey
LEFT JOIN [AdventureWorksDW2019].[dbo].[DimProductCategory] as pc
ON ps.ProductCategoryKey = pc.ProductCategoryKey
ORDER BY p.ProductKey
DIM_Promotion:
-- Cleansed DIM_Promotion Table --
SELECT [PromotionKey]
--,[PromotionAlternateKey]
,[EnglishPromotionName] as [Promotion Name]
--,[SpanishPromotionName]
--,[FrenchPromotionName]
,[DiscountPct]
,[EnglishPromotionType] as [Promotion Type]
--,[SpanishPromotionType]
--,[FrenchPromotionType]
--,[EnglishPromotionCategory]
--,[SpanishPromotionCategory]
--,[FrenchPromotionCategory]
--,[StartDate]
--,[EndDate]
--,[MinQty]
--,[MaxQty]
FROM [AdventureWorksDW2019].[dbo].[DimPromotion]
DIM_Customer:
-- Cleansed DIM_Customer Table --
SELECT c.CustomerKey as CustomerKey
--,[GeographyKey]
--,[CustomerAlternateKey]
--,[Title]
,c.FirstName as [First Name]
--,c.MiddleName as [Middle Name]
,c.LastName as [Last Name]
, c.FirstName + ' ' + c.LastName as [Full Name]
--,[NameStyle]
--,[BirthDate]
,LEFT(c.BirthDate, 4) as BirthYear
--,[MaritalStatus]
--,[Suffix]
,CASE c.Gender WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
END as Gender -- Distinguishing Male and Female from M and F
,[EmailAddress] as [Email]
,[YearlyIncome] as [Annual Income]
--,[TotalChildren]
--,[NumberChildrenAtHome]
,[EnglishEducation] as [Education Degree]
--,[SpanishEducation]
--,[FrenchEducation]
,[EnglishOccupation] as Occupation
--,[SpanishOccupation]
--,[FrenchOccupation]
--,[HouseOwnerFlag]
--,[NumberCarsOwned]
--,[AddressLine1]
--,[AddressLine2]
--,[Phone]
,[DateFirstPurchase]
--,[CommuteDistance]
,g.City as CustomerCity -- Joined in Customer City from DimGeography Table
FROM [AdventureWorksDW2019].[dbo].[DimCustomer] as c
LEFT JOIN [AdventureWorksDW2019].[dbo].[DimGeography] as g
ON c.GeographyKey = g.GeographyKey
ORDER BY c.CustomerKey -- Ordered List by CustomerKey
FACT_InternetSales:
-- Cleansed FACT_InternetSales Table --
SELECT [SalesOrderNumber]
,[ProductKey]
,[OrderDateKey]
,[DueDateKey]
,[ShipDateKey]
,[CustomerKey]
,[PromotionKey]
--,[CurrencyKey]
--,[SalesTerritoryKey]
--,[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 -- This ensures we always only bring 2 years of date from extraction.
ORDER BY OrderDateKey
​
You can find all the SQL statements and the exported data that was created based on the SQL statements:
Data Model
Below is a screenshot of the data model after cleansed and prepared tables were loaded into Power BI.
​
This data model also shows how FACT_Budget has been connected to FACT_InternetSales and other dimension tables involved.
Sales Management Dashboards - Power BI
Below are screenshots of the dashboards created on Power BI.
​
Users are allowed to filter through years and months to inspect sales and budgets when needed. A KPI is created to monitor the relationship between sales budget. From the second and third dashboards, users can view detailed sales based on per customer and per product--this allows further sales and marketing strategies to increase profitability.
