What is Adventure Works?

Adventure Works is a virtual company developed by Microsoft. It’s a well-structured database, which makes it a perfect tool for studying data analysis and business intelligence.

• 

General Analysis

Let’s start with revenue and profit. The total lifetime revenue is $10,984,638, and total profit is $9,371,903.
At first glance, Adventure Works seems to be doing great.
However, if you look at the graph closely, there’s something strange—in June 2013, profit dropped below zero. (Whaaaat?)

Adventure Works serves two types of customers:

• Store Customers (B2B)

• Individuals (B2C)

When splitting revenue by customer type, B2B generally brings in higher revenue. But when it comes to profit, B2B is a disaster—barely breaking even. Meanwhile, B2C is consistently profitable and has been growing steadily since August 2012.

Now let’s look at the margin.
The average margin for B2C is around 39%, which is excellent—especially considering that most industries consider 5–20% to be healthy.

Conclusion

This leads us to a key question: Do we need to continue B2B sales?

In the long term, yes—B2B is usually seen as a stable source of income. But Adventure Works operates with limited resources. So why not focus more on B2C, where we’re already doing well?

Here’s one more interesting fact: 100% of B2C sales happen online. So our next step should be clear—find a way to boost online sales for individual customers.

Query for general analysis

WITH CustomerType AS
(
    SELECT
        c.CustomerID,
        p.PersonType
    FROM Sales.Customer c
    JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
)

SELECT
    FORMAT(soh.OrderDate, 'yyyy-MM') AS OrderMonth,
    -- B2C (IN)
    SUM(
        CASE WHEN ct.PersonType = 'IN' THEN sod.LineTotal ELSE 0 END
        ) AS B2C_Revenue,
    SUM(
        CASE WHEN ct.PersonType = 'IN' THEN sod.LineTotal - (sod.OrderQty * pp.StandardCost) ELSE 0 END
        ) AS B2C_Profit,
    SUM(
        CASE WHEN ct.PersonType = 'IN' THEN 1 ELSE 0 END
     ) AS B2C_OrderAmount,
    SUM(
        CASE WHEN ct.PersonType = 'IN' THEN sod.OrderQty ELSE 0 END
     ) AS B2C_OrderQty,
    (   
        SUM(
        CASE WHEN ct.PersonType = 'IN' THEN sod.LineTotal - (sod.OrderQty * pp.StandardCost) ELSE 0 END
        ) / 
        SUM(
        CASE WHEN ct.PersonType = 'IN' THEN sod.LineTotal ELSE 0 END
        )
        )*100 AS B2C_Margin,
    -- B2B (SC)
    SUM(
        CASE WHEN ct.PersonType = 'SC' THEN sod.LineTotal ELSE 0 END
        ) AS B2B_Revenue,
    SUM(
        CASE WHEN ct.PersonType = 'SC' THEN sod.LineTotal - (sod.OrderQty * pp.StandardCost) ELSE 0 END
        ) AS B2B_Profit,
    SUM(
        CASE WHEN ct.PersonType = 'SC' THEN 1 ELSE 0 END
     ) AS B2C_OrderAmount,
    SUM(
        CASE WHEN ct.PersonType = 'SC' THEN sod.OrderQty ELSE 0 END
     ) AS B2C_OrderQty,
     (   
        SUM(
        CASE WHEN ct.PersonType = 'SC' THEN sod.LineTotal - (sod.OrderQty * pp.StandardCost) ELSE 0 END
        ) / 
        NULLIF(SUM(CASE WHEN ct.PersonType = 'SC' THEN sod.LineTotal ELSE 0 END), 0)
        )*100 AS B2B_Margin
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
JOIN Production.Product pp ON pp.ProductID = sod.ProductID
JOIN CustomerType ct ON soh.CustomerID = ct.CustomerID

GROUP BY FORMAT(soh.OrderDate, 'yyyy-MM')
ORDER BY OrderMonth;

Query to Check Purchase Method

WITH CustomerType AS
(
    SELECT
        c.CustomerID,
        p.PersonType
    FROM Sales.Customer c
    JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
)
SELECT 
    SUM(
        CASE   -- 0 = by sales person, 1 = online
        WHEN soh.OnlineOrderFlag = 1 AND ct.PersonType = 'SC' THEN 1 ELSE 0
        END
        ) AS Online_B2B,
    SUM(
        CASE   -- 0 = by sales person, 1 = online
        WHEN soh.OnlineOrderFlag = 0 AND ct.PersonType = 'SC' THEN 1 ELSE 0
        END
        ) AS Offline_B2B,
    SUM(
        CASE   -- 0 = by sales person, 1 = online
        WHEN soh.OnlineOrderFlag = 1 AND ct.PersonType = 'IN' THEN 1 ELSE 0
        END
        ) AS Online_B2C,
    SUM(
        CASE   -- 0 = by sales person, 1 = online
        WHEN soh.OnlineOrderFlag = 0 AND ct.PersonType = 'IN' THEN 1 ELSE 0
        END
        ) AS Offline_B2C
FROM Sales.SalesOrderHeader soh 
    JOIN CustomerType ct ON soh.CustomerId = ct.CustomerId