Sales Analysis (SQL, Power BI)

We will gain experience in sales analysis together through a scenario we created in this article. Thanks to this project, you will both develop your data analysis knowledge through a real project and be prepared for business life.

Let’s start!!

First of all, we need the data. For this project, we take our data from Microsoft AdventureWorks sample databases.

I will be using SQL Server Management Studio(SSMS).

Mike — Sales Manager:

Hi Ahmet!

I hope you are doing well. 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.

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

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

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

Let me know if you need anything else!

Ah, we got an e-mail from the sales manager, but Mike, I don’t understand what to do. It seems a bit complicated, let’s take a closer look at what Mike wants.

• Internet sales reports
• Visual dashboards
• Sold of what products
• Which clients
• It has been over time
• Sales person
• Filter them also
• Budget
• Spreadsheet
• 2 years back

This list has been very clear to us. Let’s start building

Connect to Databases

Download the appropriate .bak file from one of links provided in the download backup files section.

Move the .bak file to your SQL Server backup location. This varies depending on your installation location, instance name and version of SQL Server. For example, the default location for a default instance of SQL Server 2019 is:

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup.

Open SQL Server Management Studio (SSMS) and connect to your SQL Server.

Right-click Databases in Object Explorer > Restore Database… to launch the Restore Database wizard.

Select Device and then select the ellipses (…) to choose a device.

Select Add and then choose the .bak file you recently moved to the backup location. If you moved your file to this location but you're not able to see it in the wizard, this typically indicates a permissions issue - SQL Server or the user signed into SQL Server does not have permission to this file in this folder.

Select OK to confirm your database backup selection and close the Select backup devices window.

Check the Files tab to confirm the Restore as location and file names match your intended location and file names in the Restore Database wizard.

Select OK to restore your database.

This is a tutorial get from Microsoft AdventureWorks sample databases.

We need to update the date column for the AdventureWorksDW database with recent dates.

Let’s Throw out the Trash (Data Cleaning)

At this stage, we write small SQL scripts to make our dataset, which does not have many problems, more convenient for analysis.

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

The query selects the following columns:

• ProductKey
• OrderDateKey
• DueDateKey
• ShipDateKey
• CustomerKey
• SalesOrderNumber
• SalesAmount

It also commented out some other columns that are not selected in the query.

The query filters the results to only include rows where the year of the “OrderDateKey” column is greater than or equal to the current year minus 2. This ensures that the query only returns data from the last 2 years.

Finally, the results are ordered by the “OrderDateKey” column in ascending order.

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

The query selects the following columns:

• ProductKey
• ProductAlternateKey (aliased as “ProductItemCode”)
• EnglishProductName (aliased as “Product Name”)
• EnglishProductSubcategoryName (aliased as “Sub Category”)
• EnglishProductCategoryName (aliased as “Product Category”)
• Color (aliased as “Product Color”)
• Size (aliased as “Product Size”)
• ProductLine (aliased as “Product Line”)
• ModelName (aliased as “Product Model Name”)
• EnglishDescription (aliased as “Product Description”)
• Status (with a default value of “Outdated” when it is null)

It also commented out some other columns that are not selected in the query.

The query is using LEFT JOIN to join the data from the two tables “DimProductSubcategory” and “DimProductCategory” with the table “DimProduct”

The query also uses ISNULL function to check the status of the product, if it is null it assigns it a default value of ‘Outdated’.

Finally, the results are ordered by the “ProductKey” column in ascending order.

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

The query selects the following columns:

• DateKey
• FullDateAlternateKey (aliased as “Date”)
• EnglishDayNameOfWeek (aliased as “Day”)
• EnglishMonthName (aliased as “Month”)
• A shortened version of EnglishMonthName (aliased as “MonthShort”)
• MonthNumberOfYear (aliased as “MonthNo”)
• CalendarQuarter (aliased as “Quarter”)
• CalendarYear (aliased as “Year”)

It also commented out some other columns that are not selected in the query. The query filters the results to only include rows where the “CalendarYear” is greater than or equal to 2019.

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

The query selects the following columns:

• customerkey (aliased as “CustomerKey”)
• firstname (aliased as “First Name”)
• lastname (aliased as “Last Name”)
• firstname + ‘ ‘ + lastname (aliased as “Full Name”)
• Gender, it is using a case statement to convert the gender value to “Male” or “Female”
• datefirstpurchase (aliased as “DateFirstPurchase”)
• city (aliased as “Customer City”) which is joined from the “Geography” table.

It also commented out some other columns that are not selected in the query. The query is using a LEFT JOIN to join the data from the “DimCustomer” table with the “DimGeography” table on the “GeographyKey” column. The query is ordered by the “CustomerKey” in ascending order.

Let’s do Something Cool (Dashboard)

A dashboard in Power BI is a collection of visualizations, such as charts, tables, and maps, that are arranged on a single page and can be interactive.

Dashboards allow users to quickly view and analyze large amounts of data in an easy-to-understand format and can be shared with others.

They can also be embedded in other applications, such as websites or company portals, to make data more widely accessible.

How do we make the Dashboard?

• First, we should Load the Data.
• Organize & Prepare Tables.
• Connect Tables for Data Model.
• Simple Calculations Like:
• Budget = SUM ( FACT_Budget[Budget] )
• Sales = SUM ( FACT_InternetSales[SalesAmount] )
• Sales / Budget = [Sales] — [Budget]

Report

We are designing all pages of our dashboard, with title and date filters on the top, ‘Customer City’, ‘Sub Category’, ‘Category’, and ‘Product Name’ on the left.

We have 3 pages:

• Sales Overview
• Customer Details
• Product Details

Sales Overview

We have some visualization:

• Sales vs Budget (Dynamic KPI Card): Sales refer to the revenue generated by a company from the sale of its products or services, while budget is a financial plan that outlines expected revenues and expenses.
• Sales by Product Category (Donut Chart): Sales by Product Category refers to the revenue generated by a company from the sales of its products, grouped and analyzed by product category.
• Sales vs Budget by Month (Line Chart): Sales vs Budget by Month refers to the comparison of a company’s actual sales revenue for each month against the financial plan for expected revenue for the same month.
• Sales by Top 10 Customers (Stacked Bar Chart)
• Sales by Top 10 Product (Stacked Bar Chart)
• Sales by Customer City (Map)

Customer Details

We have some visualization:

• Sales and Budget (Card)
• Sales vs Budget by Month (Line Chart)
• Sales by Top 10 Customers and their spending (Stacked Bar Chart)
• Here is a table where we list customer names, monthly spending, and total spending from largest to smallest (Matrix).
• Sales by Customer City (Map)
• Sales by Customer City Top 10 (Stacked Bar Chart)

Product Details

• Sales and Budget (Card)
• Sales vs Budget by Month (Line Chart)
• Sales by Customer City (Map)
• Sales by Top 10 Product and Sales (Clustered Bar Chart)
• Here is a table where we list Categories, monthly sales, and total sales from largest to smallest (Matrix).

I hope this article can be useful for you. Thanks to Ali Ahmad for this project.