Purchase Pattern

In a previous post, I shared why I believe Adventure Works should focus on B2C online sales. The numbers were clear: more orders, better margins, and healthier growth.

So naturally, the next step was to ask: how do we grow that even more?

Segmenting first-time vs repeat buyers

To make sense of the purchase pattern, I first split our customers into two buckets:

First-time customers: people who’ve made only one purchase
Repeat customers: those who’ve bought at least twice

Why split? Because repeat customers are the foundation of sustainable revenue. They're easier to retain, and as it turns out, they’re pretty profitable too.

And our repeat customers are indeed profitable. Sure, at first glance, first-time customers spend slightly more per order. But difference in profit is only 3$. And remember—repeat customers cost less to acquire. You don’t need ads or discounts to bring them back.

So even though their revenue per purchase is a bit lower, the net margin stays strong.

When I charted monthly orders by product subcategory, something stood out. Repeat customers—especially in B2C—tended to buy certain items every 2–3 months.

Water bottles, helmets, Tires and tubes : Consumables

What’s more interesting: this pattern doesn’t show up for first-time buyers. It’s almost like only loyal customers behave this way.

The next thing I checked was margin. Are these products just frequent… or are they actually worth it?

Turns out, most of them had margin rates over 50%. More sales = more profit. Easy math. So yes, we should absolutely sell more of these.

Who should we be targeting?

I went back to our first-time customers and grouped them by what they bought first.

The result?

People who bought a bike as their first product had the highest repurchase conversion rate.

Makes sense. Bikes aren’t casual buys—they’re commitments. And buyers tend to come back for accessories or replacement parts.

What I’m proposing : CRM

Let’s use CRM to turn these one-time buyers into regular customers.

Appendix

Average Revenue and Average Profit Split Into Regular and First-time Customers

WITH FirstOrders AS (
    SELECT 
        soh.CustomerID,
        MIN(soh.OrderDate) AS FirstOrderDate
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID
    WHERE c.StoreID IS NULL
    GROUP BY soh.CustomerID
),

-- First Order Revenue/Profit
FirstOrderStats AS (
    SELECT 
        AVG(sod.LineTotal) AS AvgFirstRevenue,
        AVG(sod.LineTotal - p.StandardCost * sod.OrderQty) AS AvgFirstProfit
    FROM Sales.SalesOrderHeader soh
    JOIN FirstOrders f ON soh.CustomerID = f.CustomerID AND soh.OrderDate = f.FirstOrderDate
    JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
    JOIN Production.Product p ON sod.ProductID = p.ProductID
),

-- Repeat Order Revenue/Profit
RepeatOrderStats AS (
    SELECT 
        AVG(sod.LineTotal) AS AvgRepeatRevenue,
        AVG(sod.LineTotal - p.StandardCost * sod.OrderQty) AS AvgRepeatProfit
    FROM Sales.SalesOrderHeader soh
    JOIN FirstOrders f ON soh.CustomerID = f.CustomerID
    JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
    JOIN Production.Product p ON sod.ProductID = p.ProductID
    WHERE soh.OrderDate > f.FirstOrderDate
)

-- Result
SELECT 
    f.AvgFirstRevenue,
    f.AvgFirstProfit,
    r.AvgRepeatRevenue,
    r.AvgRepeatProfit
FROM FirstOrderStats f, RepeatOrderStats r;

Average Conversion Rate into Repeat Purchase

-- Step 1: B2C Customer's First Order Date
WITH FirstOrder AS (
    SELECT 
        soh.CustomerID,
        MIN(soh.OrderDate) AS FirstOrderDate
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID
    WHERE c.StoreID IS NULL
    GROUP BY soh.CustomerID
),

-- Step 2: Most expensive Item in First Order (Why most expensicve item? : to identify their interest)
FirstOrderTopItem AS (
    SELECT 
        f.CustomerID,
        pc.Name AS FirstCategory,
        ROW_NUMBER() OVER (PARTITION BY f.CustomerID ORDER BY sod.LineTotal DESC) AS rn
    FROM FirstOrder f
    JOIN Sales.SalesOrderHeader soh ON f.CustomerID = soh.CustomerID AND f.FirstOrderDate = soh.OrderDate
    JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
    JOIN Production.Product p ON sod.ProductID = p.ProductID
    JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
    JOIN Production.ProductCategory pc ON psc.ProductCategoryID = pc.ProductCategoryID
),

-- Step 3: List of most expensive subcategory by order
FirstCategoryBySpend AS (
    SELECT 
        CustomerID,
        FirstCategory
    FROM FirstOrderTopItem
    WHERE rn = 1
),

-- Step 4: Amount of Order
CustomerOrderCount AS (
    SELECT 
        soh.CustomerID,
        COUNT(DISTINCT soh.SalesOrderID) AS OrderCount
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.Customer c ON soh.CustomerID = c.CustomerID
    WHERE c.StoreID IS NULL
    GROUP BY soh.CustomerID
),

-- Step 5: Average Conversion Rate
OverallRate AS (
    SELECT 
        COUNT(*) AS AllCustomers,
        SUM(CASE WHEN OrderCount > 1 THEN 1 ELSE 0 END) AS AllRepeatCustomers
    FROM CustomerOrderCount
)

-- Step 6: Result
SELECT 
    fc.FirstCategory,
    COUNT(*) AS TotalCustomers,
    SUM(CASE WHEN coc.OrderCount > 1 THEN 1 ELSE 0 END) AS RepeatCustomers,
    ROUND(SUM(CASE WHEN coc.OrderCount > 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS RepurchaseRatePercent,
    ROUND(o.AllRepeatCustomers * 100.0 / o.AllCustomers, 2) AS OverallRepurchaseRate
FROM FirstCategoryBySpend fc
JOIN CustomerOrderCount coc ON fc.CustomerID = coc.CustomerID
CROSS JOIN OverallRate o
GROUP BY fc.FirstCategory, o.AllRepeatCustomers, o.AllCustomers
ORDER BY RepurchaseRatePercent DESC;

That’s my take on how CRM can unlock more value from our existing customers.
But I’d love to hear from you —
How are you building repeat buyers in your business?
Let me know in the comments 👇

[Adventure Works] Let's boost profit -2