N. Okoro
← All projects

SALES INTELLIGENCE

Internet Sales & Budget

Which customers and products drive sales, and how does performance compare with budget?

SQLPower BIExcel

25,429 orders · Portfolio case study

Sales amount by year · source units

202016,351,550
20195,842,485
202145,695
Web visualisation · calculated from supplied data · 2021 is partial

The question & data

A dimensional sales analysis joins customer, product, and calendar data with an Excel budget for focused reporting.

AdventureWorks-based exports contain 58,168 sales rows and 25,429 distinct sales orders, dated 1 January 2019 to 28 January 2021.

The approach

  1. Prepared calendar, customer, and product dimensions with readable labels and SQL joins.
  2. Selected sales facts and integrated the supplied Excel budget.
  3. Organised the report into Sales Overview, Customer Details, and Product Details pages.

What the work reveals

Sales-row count is larger than order count, making the distinction essential when reporting volume.

The fact export ends in January 2021; budget comparisons must use matching reporting periods.

From findings to next steps

Compare actuals and budget at a shared monthly grain and use customer and product drill-downs to investigate variances.

Read the results in context

This is a sample-data project. Daily refresh appears in the requirements but a production refresh schedule has not been verified.

Explore the SQL implementation
-- 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
Download SQL ↓

Based on supplied portfolio materials. Findings describe the dataset and report; recommendations are proposed next steps, not measured business outcomes.

Next case study Banking Customer & Portfolio Analysis ↗