Bike Sales Management

January 9, 2022

Business Request and User Stories

The business request for this data analyst project was an executive sales report for sales managers. Based on the request that was made from the business we following user stories were defined to fulfil delivery and ensure that acceptance criteria’s were maintained throughout the project.

No #	As a (role)	I want (request/demand)	So that I (user value)	Acceptance Criteria
1	Sales Manager	To get a dashboard overview of internet sales	Can observe our top products sales and various customer purchases overtime	A Power BI dashboard which updates data once daily
2	Sales Representative	A detailed overview of Internet Sales per Customers	Can follow up on customers that buys the most products and the customers we can sell more to	A Power BI dashboard which allows filtering of data for each customer
3	Sales Representative	A detailed overview of Internet Sales per Products	Can follow up on products that sells the most	A Power BI dashboard which allows filtering of data for each product
4	Sales Manager	A dashboard overview of internet sales	Follows sales over time against budget	A Power BI dashboard with graphs and KPIs comparing against budget

Data Cleansing and Transformation (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.

To create the necessary data model for doing analysis and fulfilling the business needs defined in the user stories the following tables were extracted using SQL.

Below are the SQL statements for cleansing and transforming necessary data.

DIM_Calendar:

				
					-- Data cleansed DIM_DateTable--
SELECT 
  [DateKey], 
  [FullDateAlternateKey] AS Date, 
  --,[DayNumberOfWeek]
  [EnglishDayNameOfWeek] AS Day, 
  --,[SpanishDayNameOfWeek]
  --,[FrenchDayNameOfWeek]
  --,[DayNumberOfMonth]
  --,[DayNumberOfYear]
  [WeekNumberOfYear] AS WeekNum, 
  [EnglishMonthName] AS Month, 
  LEFT([EnglishMonthName], 3) AS MonthShort, 
  --,[SpanishMonthName]
  --,[FrenchMonthName]
  [MonthNumberOfYear] AS MonthNum, 
  [CalendarQuarter] AS Quarter, 
  [CalendarYear] AS Year 
  --,[CalendarSemester]
  --,[FiscalQuarter]
  --,[FiscalYear]
  --,[FiscalSemester]
FROM 
  [AdventureWorksDW2019].[dbo].[DimDate]
WHERE
	CalendarYear >= 2019

DIM_Customers:

				
					--Cleansed DIM_Customer Table--
SELECT 
	c.customerkey AS CustomerKey,
     -- ,[GeographyKey]
     -- ,[CustomerAlternateKey]
     -- ,[Title]
   c.firstname AS [FirstName],
     -- ,[MiddleName]
   c.lastname AS [LastName],
   c.firstname + ' ' + lastname AS [Full 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 
	dbo.DimCustomer AS c
	LEFT JOIN dbo.DimGeography AS g ON g.GeographyKey = c.GeographyKey
ORDER BY
	CustomerKey ASC -- Ordered List by CustomerKey

DIM_Products:

				
					-- 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 
	[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

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 
  [dbo].[FactInternetSales] 
WHERE 
  LEFT (OrderDateKey, 4) >= YEAR(GETDATE()) -2 -- To bring two years of data of extraction
ORDER BY 
  OrderDateKey ASC

Data Model

Below is a screenshot of the data model after cleansed and prepared tables were loaded into PowerBI.

This data model also shows how FACT_Budget has been connected to FACT_InternetSales and other necessary DIM tables.

Sales Management Dashboard

The finished sales management dashboard with one page with works as a dashboard and overview, with two other pages focused on combining tables for necessary details and visualisations to show sales over time, per customers and per products.