DATA ANALYSIS ON SALES

BUSINESS REQUEST

1. We need to improve our internet sales reports and want to move from static reports to visual dashboards. Essentially, we want to focus it on how much we have sold of what products, to which clients and how it has been over time.

2. Seeing as each sales person works on different products and customers it would be beneficial to be able to filter them also.

3. We measure our numbers against budget so I added that in a spreadsheet so we can compare our values against performance.

4. The budget is for 2021 and we usually look 2 years back in time when we do analysis of sales.

BUSINESS REQUEST AND USER STORIES

User Stories:

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 follow better which customers and products sells the best	A Power BI dashboard which updates data once a day
2	Sales Representative	A detailed overview of Internet Sales per Customers	Can follow up my customers that buys the most and who we can sell ore to	A Power BI dashboard which allows me to filter data for each customer
3	Sales Representative	A detailed overview of Internet Sales per Products	Can follow up my Products that sells the most	A Power BI dashboard which allows me to filter data for each Product
4	Sales Manager	A dashboard overview of internet sales	Follow sales over time against budget	A Power Bi dashboard with graphs and KPIs comparing against budget.

DATA CLEANSING AND TRANSFORMING (sql)

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

DIMCalendar.sql

-- collect necessary columns

SELECT

[DateKey],

[FullDateAlternateKey] AS Date,

[EnglishDayNameOfWeek] AS Day,

[EnglishMonthName] AS Month,

Left([EnglishMonthName], 3) AS MonthShort, -- Useful for front end date navigation and front end graphs.

[MonthNumberOfYear] AS MonthNo,

[CalendarQuarter] AS Quarter,

[CalendarYear] AS Year

FROM

[AdventureWorksDW2019].[dbo].[DimDate]

WHERE

CalendarYear >= 2019

Customer.sql

-- COLLECT NECESSARY COLUMNS IN DIM_Customers Table --

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

[AdventureWorksDW2019].[dbo].[DimCustomer] as c

LEFT JOIN dbo.dimgeography AS g ON g.geographykey = c.geographykey

ORDER BY

CustomerKey ASC -- Ordered List by CustomerKey

Products.sql

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

[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

factinternalsales.sql

SELECT

[ProductKey],

[OrderDateKey],

[DueDateKey],

[ShipDateKey],

[CustomerKey],

[SalesOrderNumber],

[SalesAmount]

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

Below is a screenshot of the data model after cleansed and prepared tables were read into Power BI. This data model also shows how factBudget has been connected to factInternetsales and other necessary 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 visualizations to show sales over time, per customers and per products.

Click the picture to to open the dashboard and try it out!

To access files click here.https://github.com/mutisya7/projects