Multi-Region Commercial Sales Data Warehouse

Global Retail & Supply Chain Analytics

Author

Hasan Muhammad

Overview

NoteProject Information
  • Platform Name: Multi-Region Commercial Sales Data Warehouse
  • Database: AdventureWorksDW 2022
  • Total Queries: 19
  • Total Sections: 15
  • Lines of Code: 652

0.1 Purpose

This comprehensive SQL analysis suite provides end-to-end business intelligence queries for multi-region commercial sales operations, covering global retail performance, supply chain analytics, customer insights, product intelligence, inventory management, and cross-border operational metrics.

0.2 Key Features

  • ✅ Year-over-year growth analysis with automatic partial data handling
  • ✅ Integrated customer segmentation (RFM + CLV + Churn in one query)
  • ✅ Product performance with built-in ABC analysis
  • ✅ Predictive metrics and KPIs
  • ✅ Multi-currency and exchange rate analysis
  • ✅ Customer demographics and geography insights

1 Quick Start Guide

1.1 Common Use Cases

Table 1: Common Query Use Cases
Need Section Approx Line
Date range sales report Section 1 & 2 Lines 3-103
Customer analysis Section 4 Lines 139-211
Top products with ABC Section 5 Lines 213-302
Territory performance Section 6 Lines 304-347

2 Section Index

2.1 SECTION 1: Yearly Growth Analysis

TipSection Details
  • Lines: 3-42
  • Output Tables: 1

2.1.1 Query Description

Total Sales, Orders & Transactions with YoY Growth

  • Shows: Annual orders, transactions, sales, costs, profit, growth rates
  • Key Metrics: Profit margin, OrdersGrowth, SalesGrowth
  • Features: Automatic handling of partial years
  • Use Case: Annual performance review, board presentations

2.1.2 Key Columns

Table 2: Key Columns - Yearly Growth Analysis
Column Description
ActualYear Calendar year
TotalOrders Number of distinct orders
TotalTransactions Total transaction count
TotalSales Total sales amount
TotalCost Total product cost
TotalProfit Total profit (Sales - Cost)
ProfitMargin Profit percentage
OrdersGrowth Year-over-year orders growth
SalesGrowth Year-over-year sales growth

2.2 SECTION 2: Monthly & Quarterly Analysis

TipSection Details
  • Lines: 43-101
  • Output Tables: 2

2.2.1 Queries

2.2.1.1 Query 1: Monthly Sales Breakdown with YTD

  • Shows: Monthly orders, sales, profit, running YTD total
  • Key Metrics: Profit margin per month
  • Use Case: Monthly financial reports

2.2.1.2 Query 2: Quarterly Performance with YoY Growth

  • Shows: Quarterly sales with YoY comparison
  • Features: Quarter-over-quarter growth with partial data handling
  • Use Case: Quarterly business reviews

2.2.2 Key Columns

Monthly Analysis:

  • CalendarYear, MonthNumberOfYear, EnglishMonthName
  • TotalOrders, MonthlySales, MonthlyProfit
  • YTDSales, ProfitMargin

Quarterly Analysis:

  • CalendarYear, CalendarQuarter
  • TotalOrders, QuarterlySales, QuarterlyProfit
  • ProfitMargin, YoYGrowth

2.3 SECTION 3: Inventory Analysis

TipSection Details
  • Lines: 102-135
  • Output Tables: 1

2.3.1 Query Description

Inventory Movement with Stock Status

  • Shows: Current units, inventory value, average unit cost
  • Features: Stock status classification
  • Use Case: Inventory planning, supply chain optimization

2.3.2 Key Columns

  • EnglishProductName, CalendarYear, MonthNumberOfYear
  • CurrentUnits, InventoryValue, AvgUnitCost
  • StockStatus

2.3.3 Classification Rules

Table 3: Stock Status Classification
Status Units Range
Low Stock < 100
Normal 100-500
Overstocked > 500

2.4 SECTION 4: Customer Analysis (CLV, RFM, Churn)

TipSection Details
  • Lines: 136-207
  • Output Tables: 1

2.4.1 Query Description

Comprehensive Customer Analysis

  • Shows: Customer lifetime value, RFM scores, churn status, and tiers
  • Features: Complete 360° customer view with all metrics in one query
  • Use Case: Customer understanding, retention strategies, marketing segmentation

2.4.2 Key Columns

Table 4: Customer Analysis Columns
Column Group Columns
Identity CustomerKey, CustomerName, EmailAddress, Country
Dates FirstPurchaseDate, LastPurchaseDate, DaysSinceLastPurchase
Financial TotalOrders, TotalRevenue, TotalProfit, AvgOrderValue
Metrics AnnualizedCLV, RFM_Score, CustomerSegment, ChurnStatus, CustomerTier

2.4.3 Segmentation Rules

Segment R Score F Score M Score
Champions ≥4 ≥4 ≥4
Loyal Customers ≥3 ≥3 ≥3
Promising ≥4 ≤2 -
At Risk ≤2 ≥3 -
Lost ≤2 ≤2 -
Potential Other combinations Other Other
Tier Revenue Range
Platinum > $10,000
Gold $5,000 - $10,000
Silver $2,000 - $5,000
Bronze < $2,000
Status Days Since Last Purchase
Active ≤ 90
At Risk 91-180
Dormant 181-365
Lost > 365

2.5 SECTION 5: Product Analysis

TipSection Details
  • Lines: 208-293
  • Output Tables: 2

2.5.1 Queries

2.5.1.1 Query 1: Product Performance with ABC Analysis

  • Shows: Orders, revenue, profit, ABC category integrated
  • Features: Pareto analysis built-in with product metrics
  • Use Case: Product portfolio review, inventory prioritization

2.5.2 Key Columns

Product Performance:

  • Rank, EnglishProductName, Category, Subcategory
  • TotalOrders, UnitsSold, Revenue, Profit
  • ProfitMargin, AvgPrice, AvgDiscount
  • CumulativeShare, ABCCategory, MarginCategory

Category Trends:

  • Category, CalendarYear, CalendarQuarter
  • Orders, UnitsSold, Revenue, YoYGrowth

2.5.3 Classification Rules

Category Revenue Share Priority
A - Top 80% Up to 80% Critical
B - Next 15% 80-95% Important
C - Bottom 5% 95-100% Marginal
Category Profit Margin
High Margin > 40%
Medium Margin 20-40%
Low Margin < 20%

2.6 SECTION 6: Sales Territory Analysis

TipSection Details
  • Lines: 294-337
  • Output Tables: 1

2.6.1 Query Description

Territory Performance Dashboard with Market Share

  • Shows: Territory metrics with integrated market share calculation
  • Features: YoY growth and market share in single view
  • Use Case: Territory planning, regional strategy

2.6.2 Key Columns

  • SalesTerritoryRegion, SalesTerritoryCountry, SalesTerritoryGroup
  • CalendarYear, Orders, Customers
  • Revenue, Profit, ProfitMargin
  • MarketShare, RevenuePerCustomer, YoYGrowth

2.7 SECTION 7: Promotion Analysis

TipSection Details
  • Lines: 338-373
  • Output Tables: 1

2.7.1 Query Description

Promotion Effectiveness with Comparison

  • Shows: All promotions with “No Promotion” baseline for comparison
  • Features: Side-by-side promotion performance analysis
  • Use Case: Promotion ROI analysis, discount strategy

2.7.2 Key Columns

  • PromotionStatus, EnglishPromotionName, EnglishPromotionType
  • EnglishPromotionCategory, DiscountRate
  • TotalOrders, Revenue, Profit, ProfitMargin
  • RevenuePerOrder, AvgItemsPerOrder

2.8 SECTION 8: Time Analysis

TipSection Details
  • Lines: 374-432
  • Output Tables: 2

2.8.1 Queries

2.8.1.1 Query 1: Sales by Day of Week

  • Shows: Weekday performance patterns
  • Features: Percentage of total and variance from weekly average
  • Use Case: Staffing optimization, promotion timing

2.8.1.2 Query 2: Seasonal Analysis

  • Shows: Spring/Summer/Fall/Winter performance with YoY growth
  • Features: Partial data handling for incomplete seasons
  • Use Case: Seasonal planning, inventory forecasting

2.8.2 Seasons Definition

Table 5: Seasonal Definitions
Season Months
Winter Dec, Jan, Feb
Spring Mar, Apr, May
Summer Jun, Jul, Aug
Fall Sep, Oct, Nov

2.9 SECTION 9: Shipping & Delivery Analysis

TipSection Details
  • Lines: 433-450
  • Output Tables: 1

2.9.1 Query Description

Shipping Performance & Freight Cost Analysis

  • Shows: Freight costs by territory and time period
  • Features: Complete logistics cost view
  • Use Case: Shipping optimization, cost management

2.9.2 Key Columns

  • SalesTerritoryRegion, CalendarYear, CalendarQuarter
  • TotalOrders, TotalFreightCost
  • AvgFreightPerOrder, TotalRevenue

2.10 SECTION 10: Currency & Exchange Rate Analysis

TipSection Details
  • Lines: 451-473
  • Output Tables: 1

2.10.1 Query Description

Multi-Currency Sales Impact

  • Shows: Revenue in local currency and USD equivalent
  • Features: Exchange rate volatility tracking with min/max rates
  • Use Case: International sales reporting, hedging strategy

2.10.2 Key Columns

  • CurrencyName, CalendarYear, TotalOrders
  • RevenueInLocalCurrency, AvgExchangeRate, RevenueInUSD
  • ExchangeImpactPct, MaxExchangeRate, MinExchangeRate
  • ExchangeRateVolatility

2.11 SECTION 11: Customer Demographics Analysis

TipSection Details
  • Lines: 474-497
  • Output Tables: 1

2.11.1 Query Description

Sales by Demographics & Age Analysis

  • Shows: Gender, income, age, education, occupation metrics
  • Features: Complete demographic breakdown with customer-level detail
  • Use Case: Demographic segmentation, targeted marketing

2.11.2 Key Columns

  • CustomerKey, Gender, MaritalStatus, IncomeRange, AgeGroup
  • EnglishEducation, EnglishOccupation
  • TotalCustomers, TotalOrders, TotalRevenue
  • AvgOrderValue, RevenuePerCustomer, OrdersPerCustomer

2.11.3 Classification Ranges

  • < $25K
  • $25K - $50K
  • $50K - $75K
  • $75K - $100K
  • > $100K
  • 18-24
  • 25-34
  • 35-44
  • 45-54
  • 55-64
  • 65+

2.12 SECTION 12: Geography Analysis

TipSection Details
  • Lines: 498-536
  • Output Tables: 1

2.12.1 Query Description

Sales by Country, Region & Top Cities

  • Shows: Complete geographic breakdown with top 10 cities per country
  • Features: Integrated city rankings with performance metrics
  • Use Case: Market expansion planning, territory optimization

2.12.2 Key Columns

  • Country, CityRank, City, State
  • TotalCustomers, TotalOrders, TotalRevenue
  • TotalProfit, ProfitMargin, RevenuePerCustomer

2.13 SECTION 13: Product Bundle Analysis (Market Basket)

TipSection Details
  • Lines: 537-561
  • Output Tables: 1

2.13.1 Query Description

Products Frequently Bought Together

  • Shows: Top 50 product pairs purchased in same order
  • Features: Support percentage calculation
  • Use Case: Cross-selling strategies, bundle promotions

2.13.2 Key Columns

  • Product1Name, Product2Name
  • TimesBoughtTogether, SupportPercentage

2.14 SECTION 14: Predictive Metrics & KPIs

TipSection Details
  • Lines: 562-632
  • Output Tables: 2

2.14.1 Queries

2.14.1.1 Query 1: Customer Lifetime Value Prediction

  • Shows: Historical revenue with 3-year CLV projection
  • Features: Purchase frequency classification
  • Use Case: Customer investment decisions, retention budgeting

2.14.1.2 Query 2: Sales Velocity (Trend Analysis)

  • Shows: Monthly revenue with 3-month moving averages
  • Features: Quarter-over-quarter growth tracking
  • Use Case: Sales forecasting, trend identification

2.14.2 Purchase Frequency Classification

Table 6: Purchase Frequency Rules
Frequency Days Between Orders
High Frequency ≤ 30
Medium Frequency 31-90
Low Frequency > 90

2.15 SECTION 15: Profitability Deep Dive

TipSection Details
  • Lines: 633-652
  • Output Tables: 1

2.15.1 Query Description

Profit Margin Analysis by Multiple Dimensions

  • Shows: Profitability by category, territory, and year
  • Features: Profit ranking per year
  • Use Case: Strategic planning, resource allocation

2.15.2 Key Columns

  • Category, Territory, CalendarYear
  • Orders, Revenue, Cost, Profit
  • ProfitMargin, ProfitRank

Common Business Questions

Table 7: Business Questions Quick Reference
Question Section Query
“What’s our YoY growth?” 1 Query 1
“Who are our best customers?” 4 Query 1
“Which products sell together?” 13 Query 1
“Which customers are at risk?” 4 Query 1
“What’s our profit margin by category?” 15 Query 1
“How’s our shipping performance?” 9 Query 1
“What are our top cities?” 12 Query 1
“What’s our monthly sales trend?” 2 Query 1
“Which products are top performers?” 5 Query 1
“What’s our inventory status?” 3 Query 1
“How do promotions impact sales?” 7 Query 1
“What’s our seasonal performance?” 8 Query 2
“What’s our customer lifetime value?” 14 Query 1
“What’s the exchange rate impact?” 10 Query 1
“What are our customer demographics?” 11 Query 1

Notes & Tips

2.16 Important Considerations

Warning
  • Section 1 handles partial years automatically
  • Section 13 (Market Basket) requires sufficient transaction history
  • Section 10 (Currency) requires FactCurrencyRate data
  • All YoY comparisons handle partial data appropriately

Appendix