Analyzing Sales and Customer Data in AdventureWorks2019 Using SQL

9 min readMay 22, 2023

Welcome to the documentation on analyzing sales and customer data in AdventureWorks2019 using SQL! In today’s data-driven world, extracting insights and making informed decisions based on data is crucial. One of the key sources of data for businesses is their sales and customer data. AdventureWorks2019 is a sample database provided by Microsoft that simulates a fictitious bicycle manufacturer with a focus on sales and customer data.

In this documentation, I will explore how to use SQL, a powerful programming language for managing and analyzing relational databases, to extract valuable insights from AdventureWorks2019. I will cover various SQL queries and techniques for analyzing sales data, customer data, and their relationships.

Whether you’re a business analyst, a data scientist, or a SQL enthusiast, this documentation will equip you with the skills you need to effectively analyze and visualize sales and customer data in AdventureWorks2019. So, let’s get started!

Data Collection And Importation

Before I begin, click HERE to view the dataset.

I used the Microsoft SQL Server Management Studio For this analysis. Wondering how to install your database? Watch this VIDEO!

Tables

Sales.SalesOrderHeader: This table contains information about sales orders, including order dates, order numbers, and customer IDs. It is connected to the Sales.Customer and Sales.SalesPerson tables through customer and salesperson IDs, respectively.
Sales.Customer: This table contains information about customers, including demographic data like age, gender, and income. It is connected to the Sales.SalesOrderHeader and Sales.SalesTerritory tables through customer IDs.
Sales.SalesPerson: This table contains information about salespeople, including names and contact information. It is connected to the Sales.SalesOrderHeader table through salesperson IDs.
Sales.SalesTerritory: This table contains information about sales territories, including geographic locations and sales quotas. It is connected to the Sales.SalesOrderHeader table through territory IDs.
Production.Product: This table contains information about products, including descriptions and prices. It is connected to the Sales.SalesOrderDetail and Purchasing.PurchaseOrderDetail tables through product IDs.
Sales.SalesOrderDetail: This table contains detailed information about sales orders, including product IDs and quantities sold. It is connected to the Sales.SalesOrderHeader and Production.Product tables through order and product IDs, respectively.
Purchasing.PurchaseOrderHeader: This table contains information about purchase orders, including order dates, order numbers, and supplier IDs. It is connected to the Purchasing.Vendor table through supplier IDs.
Purchasing.Vendor: This table contains information about suppliers, including names and contact information. It is connected to the
Purchasing.PurchaseOrderHeader table through vendor IDs.
Purchasing.PurchaseOrderDetail: This table contains detailed information about purchase orders, including product IDs and quantities ordered. It is connected to the Purchasing.PurchaseOrderHeader and Production.Product tables through the order and product IDs, respectively.
Production.WorkOrder: This table contains information about work orders, including start and end dates and production times. It is connected to the Production.Product and Production.ScrapReason tables through product and scrap reason IDs, respectively.

Data Cleaning

The AdventureWorks2019 database has undergone much development and testing by Microsoft. Therefore, it is unlikely to contain major data quality issues or inconsistencies.

Data Exploration

Questions And Query From Sales.SalesOrderHeader

● What are the most popular products among customers?

--To view the data in Sales.SalesOrderHeader 
USE AdventureWorks2019
SELECT * FROM [Sales].[SalesOrderHeader]

--What are the most popular products among customers?
SELECT d.ProductID, COUNT(h.CustomerID) AS NUM_PRODUCT
FROM [Sales].[SalesOrderHeader] h
JOIN [Sales].[SalesOrderDetail] d ON h.SalesOrderID = d.SalesOrderID
JOIN [Production].[Product] p ON d.ProductID = p.ProductID
GROUP BY d.ProductID
ORDER BY NUM_PRODUCT DESC

The most popular products among customers

I queried the most popular product via the number of times its was purchased. From the above, it is evident that the most popular product has an ID of 870 with a count of 4688. The second popular product has an ID of 712 with count 3382.

●Which geographic regions generate the most sales?

--Which geographic regions generate the most sales?
SELECT t."Group", SUM(d.LineTotal) AS Total_Regional_Sales
FROM [Sales].[SalesOrderHeader] h
JOIN [Sales].[SalesOrderDetail] d ON h.SalesOrderID = d.SalesOrderID
JOIN [Sales].[SalesTerritory] as t ON h.TerritoryID = t.TerritoryID
GROUP BY "Group"
ORDER BY Total_Regional_Sales DESC;

The data above indicates that the North America region had the highest sales among all regions, with a total of $79,353,361. In comparison, Europe recorded sales of $19,837,684, while the Pacific region had sales of $10,655,336. Overall, the total regional sales amounted to $109,846,381, highlighting the dominance of the North America region in terms of sales.

●How has sales volume changed over time?

SELECT YEAR(OrderDate) AS SalesYear, 
SUM(TotalDue) AS SalesVolume
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY SalesYear ASC;

The provided information presents the total sales figures for each year from 2011 to 2014. Among the four years examined, 2013 had the highest sales, reaching $48,965,888. The preceding year, 2012, had the second-highest sales, totaling $37,675,700.

On the other hand, 2014 experienced a decline in sales compared to the previous years, recording a total of $22,419,498. It’s worth noting that 2014 had the lowest sales figure among the four years analyzed.

Furthermore, the data reveals that 2011 had the lowest sales value of $14,155,700. This suggests that sales increased in subsequent years until reaching a peak in 2013, followed by a decline in 2014.

However, note that this data covers a limited timeframe of only four years. Therefore, it may not provide a comprehensive understanding of long-term sales trends or patterns.

● Which customer segments generate the most revenue?

--Which customer segments generate the most revenue?
SELECT 
    CASE 
        WHEN Customer.AccountNumber LIKE 'AW%' THEN 'A'
        WHEN Customer.AccountNumber LIKE 'CB%' THEN 'C'
        ELSE 'B'
    END AS CustomerSegment,
    SUM(SalesOrderHeader.TotalDue) AS TotalRevenue
FROM Sales.SalesOrderHeader AS SalesOrderHeader
INNER JOIN Sales.Customer AS Customer
    ON SalesOrderHeader.CustomerID = Customer.CustomerID
GROUP BY 
    CASE 
        WHEN Customer.AccountNumber LIKE 'AW%' THEN 'A'
        WHEN Customer.AccountNumber LIKE 'CB%' THEN 'C'
        ELSE 'B'
    END
ORDER BY 
    TotalRevenue DESC;

Among the different customer segments, Customer segment A emerged as the top revenue generator, contributing significantly to the overall financial success of the business. This segment alone accounted for a substantial revenue of $123,216,786.1159. The impressive revenue figure attributed to Customer Segment A highlights its importance and profitability within the company’s customer base.

● How effective are promotional campaigns in driving sales?

SELECT
    SalesMonth,
    ROUND(SUM(MonthlySalesRevenue), 2) AS TotalSalesRevenue,
    MAX(PromotionRunning) AS PromotionRunning
FROM (
    SELECT
        CONVERT(CHAR(7), SalesOrderHeader.OrderDate, 120) AS SalesMonth,
        SUM(SalesOrderDetail.LineTotal) AS MonthlySalesRevenue,
        CASE
            WHEN EXISTS (
                SELECT 1 FROM Sales.SpecialOffer AS SO
                WHERE SalesOrderHeader.SalesOrderID IN (
                    SELECT SalesOrderID FROM Sales.SalesOrderDetail
                    WHERE SpecialOfferID = SO.SpecialOfferID
                )
                AND GETDATE() BETWEEN SO.StartDate AND SO.EndDate
            )
            THEN 'Yes'
            ELSE 'No'
        END AS PromotionRunning
    FROM 
        Sales.SalesOrderDetail AS SalesOrderDetail
    INNER JOIN 
        Sales.SalesOrderHeader AS SalesOrderHeader 
        ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
    GROUP BY 
        CONVERT(CHAR(7), SalesOrderHeader.OrderDate, 120),
        SalesOrderHeader.SalesOrderID
) AS MonthlySales
GROUP BY 
    SalesMonth
ORDER BY 
    SalesMonth DESC;

Questions And Query From Sales.SalesTerritory

● Which territories generate the most revenue?

--Which territories generate the most revenue?
SELECT t.Name, SUM(d.LineTotal) AS Total_Revenue
FROM [Sales].[SalesOrderHeader] h
JOIN [Sales].[SalesOrderDetail] d ON h.SalesOrderID = d.SalesOrderID
JOIN [Sales].[SalesTerritory] t ON h.TerritoryID = t.TerritoryID
GROUP BY Name
ORDER BY Total_Revenue Desc;

Based on the given data, the territory that generates the most revenue is Southwest, with a total revenue of $24,184,609. Canada follows closely behind with a revenue of $16,355,770.45, and the Northwest comes in third with $16,084,942.55. Australia ranks fourth in revenue generated, with a total of $10,655,335.96.

● How does sales performance vary by territory?

--How does sales performance vary by territory?
SELECT 
  st.Name AS TerritoryName,
  ROUND(SUM(soh.TotalDue), 2) AS TotalSales,
  AVG(sod.OrderQty) AS AvgOrderQuantity,
  AVG(sod.LineTotal) AS AvgOrderValue
FROM 
  Sales.SalesOrderHeader AS soh
  INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
  INNER JOIN Sales.SalesPerson AS sp ON soh.SalesPersonID = sp.BusinessEntityID
  INNER JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
GROUP BY 
  st.Name
ORDER BY 
  TotalSales DESC;

The Southwest territory has the highest sales performance with $606,380,951.99, followed by the United Kingdom with $366,037,331.28. Central and Northeast territories also show strong sales performance with $347,868,654.95 and $327,853,784.28, respectively. The sales performance declines gradually from Northwest to Southeast, and the remaining territories, including France, Germany, and Australia, have comparatively lower sales figures.

Questions And Query From Sales.SalesPerson

● How does sales performance vary by geographic region?

--How does sales performance vary by geographic region?
SELECT 
  ST.Name AS TerritoryName, 
  SUM(SOH.TotalDue) AS TotalSales
FROM 
  Sales.SalesOrderHeader SOH 
  INNER JOIN Sales.Customer C ON SOH.CustomerID = C.CustomerID
  INNER JOIN Sales.SalesPerson SP ON SOH.SalesPersonID = SP.BusinessEntityID
  INNER JOIN Sales.SalesTerritory ST ON SP.TerritoryID = ST.TerritoryID
GROUP BY 
  ST.Name
ORDER BY 
  TotalSales DESC;

Sales performance varies significantly by geographic region, with the Southwest region generating the highest total sales of $18,954,587. The Central region follows with $11,342,386, while Canada, Northwest, and Northeast regions closely trail with sales figures of $10,752,959, $10,564,257, and $10,475,367, respectively. In contrast, the United Kingdom, Southeast, France, Germany, and Australia regions have comparatively lower sales totals ranging from $9,585,125 to $1,606,441.

● Are there any correlations between salesperson characteristics and
performance?

--Are there any correlations between salesperson characteristics and performance?
SELECT 
    sp.TerritoryID,
    sp.SalesQuota,
    SUM(soh.TotalDue) AS TotalSales
FROM 
    Sales.SalesPerson sp
    INNER JOIN Sales.SalesOrderHeader soh ON sp.BusinessEntityID = soh.SalesPersonID
GROUP BY 
    sp.TerritoryID,
    sp.SalesQuota
ORDER BY 
    TotalSales DESC;

Questions And Query From Production.Product

● What are the most profitable products?

--The most profitable products
SELECT p.Name, SUM(d.LineTotal) AS total_sold
FROM [Sales].[SalesOrderDetail] d
JOIN [Production].[Product] p ON d.ProductID = p.ProductID
GROUP BY Name
ORDER BY total_sold DESC

The ten profitable products are seen in the data above. However, the most profitable products are the HL Mountain Tire, Fender Set — Mountain, All-Purpose Bike Stand, and ML Mountain Tire. These products have generated the highest total profits, indicating their strong revenue-to-cost ratio and market demand.

● Are there any patterns or trends in product sales over time?

--Are there any patterns or trends in product sales over time?
SELECT CONVERT(date, MAX(Sales.SalesOrderHeader.OrderDate)) AS MaxOrderDate, 
 YEAR(Sales.SalesOrderHeader.OrderDate) AS OrderYear, 
MONTH(Sales.SalesOrderHeader.OrderDate) AS OrderMonth,
SUM(Sales.SalesOrderDetail.LineTotal) AS TotalSales
FROM Sales.SalesOrderHeader
JOIN Sales.SalesOrderDetail ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
GROUP BY YEAR(Sales.SalesOrderHeader.OrderDate), MONTH(Sales.SalesOrderHeader.OrderDate)
ORDER BY OrderYear, OrderMonth;

● How does supplier performance vary by product category?

--How does supplier performance vary by product category?
SELECT pc.Name AS CategoryName, v.Name AS VendorName,
COUNT(DISTINCT po.PurchaseOrderID) AS TotalOrders,
SUM(pod.OrderQty * pod.UnitPrice) AS TotalSpent
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS pcs ON pcs.ProductSubcategoryID = p.ProductSubcategoryID
JOIN Production.ProductCategory AS pc ON pc.ProductCategoryID = pcs.ProductCategoryID
JOIN Purchasing.PurchaseOrderDetail AS pod ON pod.ProductID = p.ProductID
JOIN Purchasing.PurchaseOrderHeader AS po ON po.PurchaseOrderID = pod.PurchaseOrderID
JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = po.VendorID
GROUP BY pc.Name, v.Name
ORDER BY pc.Name ASC, TotalSpent DESC;

I analyzed the supplier performance by product based on the total orders placed with each vendor. In the Accessories category, most vendors received 50 orders, except for Green Lake Bike Company and International Trek Center, which received 2 and 1 order, respectively. G & K Bicycle Corp also received 1 order.

In the Clothing category, Integrated Sports Products received the highest number of orders with 4, followed by Jeff’s Sporting Goods with 2 orders, and Fitness Association and Team Athletic Co. each received 1 order. In the Components category, most vendors received 51 orders, except for Jackson Authority, with 40 orders, and Expert Bike Co, with 25 orders.