SALES INTELLIGENCE
Internet Sales & Budget
Which customers and products drive sales, and how does performance compare with budget?
25,429 orders · Portfolio case study
Sales amount by year · source units
Web visualisation · calculated from supplied data · 2021 is partialThe 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
- Prepared calendar, customer, and product dimensions with readable labels and SQL joins.
- Selected sales facts and integrated the supplied Excel budget.
- 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.