Analysing Customer Purchasing Behaviour with SQL

In the previous article, I created a reusable analytical layer over the AdventureWorks2025 sales model.

That changes the nature of the work. Instead of rebuilding joins and customer definitions, I can start asking how customers actually purchase.

That means separating account activity, repeat purchasing, value, recency, and purchase cadence without losing the important differences between store and individual accounts.

These sound like simple questions. The definitions behind them still need care.

This article is part of a twelve-part SQL sales analytics project based on AdventureWorks2025, supported by the sql-sales-analytics-portfolio repository.

SQL Sales Analytics Project Series

1. Building a Sales Analytics Solution with SQL: Project Overview
2. Understanding the Sales Data Landscape with SQL
3. Assessing Sales Data Quality with SQL
4. Preparing Sales Data for Analysis with SQL
5. Analysing Customer Purchasing Behaviour with SQL ← You are here
6. Measuring Product Performance with SQL
7. Analysing Regional Sales Performance with SQL
8. Time-Series Analysis with SQL
9. Using Advanced SQL to Generate Business Insights
10. Optimising SQL for Analytical Workloads
11. Designing an Analytics-Friendly Data Model
12. Completing a SQL Sales Analytics Project

Reproduce the Analysis

This article depends on the analytical views created in Article 4. If the Analytics views aren’t installed, run the analytical view creation script against AdventureWorks2025 first.

Then run the complete customer purchasing behaviour script in SQL Server Management Studio. The analysis is read-only, uses a session-scoped temporary table, and includes every account-grain, activity, frequency, recency, value, and purchase-interval query referenced below.

The Business Problem

A sales total tells the business what happened. Customer analysis begins to explain where that value came from and how stable it might be.

Two businesses with identical revenue can have very different customer foundations. One may depend on a small group of repeat buyers, while the other continuously replaces one-time customers. Those situations imply different priorities for retention, acquisition, account management, and forecasting.

For this analysis, I want to establish:

The commercial sales measure remains SubTotal. It excludes tax and freight and reconciles with the net value of the underlying order lines.

Begin with the Analytical Dates

AdventureWorks2025 is a static sample, so measuring recency against today’s date would be misleading. Every customer would become more “inactive” as time passes, even though the data hasn’t changed.

The time-series analysis later in the series found that store orders end on 30 April 2025, while individual orders continue to 29 June. Using the overall maximum date would make every store appear 60 days less recent without establishing that store activity was still being captured.

I therefore use the latest observed date for each customer type:

DECLARE @AnalysisDate date =
(
    SELECT MAX(OrderDate)
    FROM Analytics.vwSalesOrders
);

DECLARE @StoreAnalysisDate date =
(
    SELECT MAX(OrderDate)
    FROM Analytics.vwSalesOrders
    WHERE CustomerType = 'Store'
);

The available orders run from 30 May 2022 to 29 June 2025. Individual recency is measured at 29 June 2025, while store recency is measured at 30 April 2025.

This convention makes the results reproducible. In a live system, the appropriate reference would normally be the current reporting date or the end of a defined reporting period.

The supporting script begins with the analysis-date and observation-period queries, which establish both reference dates and return the 31,465 orders and 19,119 ordering customer accounts.

Customer Accounts Aren’t Always Business Entities

The analytical views preserve Sales.Customer.CustomerID as the customer-account grain. That is the identifier recorded on each sales order, but it doesn’t always correspond to one distinct real-world store.

The underlying data contains 701 store-linked customer accounts with no orders. It would be tempting to call them inactive stores. A closer look shows why that would be wrong.

• 635 stores have two customer-account records: one store-only account with no orders and another person-and-store account containing the orders.
• 66 stores have one store-only account and no recorded orders.
• All 3,806 store orders belong to the 635 person-and-store accounts.

I generated those figures by aggregating orders at customer-account grain first, then summarising the accounts attached to each StoreID:

WITH store_accounts AS
(
    SELECT
        customer.CustomerID,
        customer.PersonID,
        customer.StoreID,
        COUNT(sales_order.SalesOrderID) AS Orders
    FROM Sales.Customer AS customer
    LEFT JOIN Sales.SalesOrderHeader AS sales_order
        ON sales_order.CustomerID = customer.CustomerID
    WHERE customer.StoreID IS NOT NULL
    GROUP BY
        customer.CustomerID,
        customer.PersonID,
        customer.StoreID
),
store_summary AS
(
    SELECT
        StoreID,
        COUNT(*) AS CustomerAccounts,
        SUM(CASE WHEN Orders > 0 THEN 1 ELSE 0 END) AS OrderingAccounts,
        SUM(CASE WHEN Orders = 0 THEN 1 ELSE 0 END) AS NonOrderingAccounts,
        SUM(Orders) AS Orders
    FROM store_accounts
    GROUP BY StoreID
)
SELECT
    CustomerAccounts,
    OrderingAccounts,
    NonOrderingAccounts,
    COUNT(*) AS Stores,
    SUM(Orders) AS Orders
FROM store_summary
GROUP BY
    CustomerAccounts,
    OrderingAccounts,
    NonOrderingAccounts
ORDER BY CustomerAccounts;

The result has one row for 635 stores with two accounts and one row for 66 stores with a single account. The same query is available in the complete script.

The first group is not evidence of 635 lapsed store relationships. It is a feature of how AdventureWorks represents the accounts.

For that reason, the behavioural analysis starts with the 19,119 customer accounts that have at least one order. This count comes directly from the order-grain analytical view:

SELECT
    COUNT(DISTINCT CustomerID) AS OrderingCustomerAccounts
FROM Analytics.vwSalesOrders;

The query is included in the opening section of the supporting script. It counts each CustomerID that appears on at least one order. I keep CustomerID as the grain because it is the key used by the transaction, while avoiding claims that every non-ordering account represents a separate inactive business.

A customer count needs a declared grain

This article counts ordering customer accounts. A store-level analysis would need to consolidate records by StoreID and decide how to handle contacts, accounts, and businesses separately.

Building One Customer-Level Dataset

The order view contains one row per order. I aggregate it to one row per ordering customer account before calculating behavioural segments.

SELECT
    sales_order.CustomerID,
    MAX(sales_order.CustomerType) AS CustomerType,
    MIN(sales_order.OrderDate) AS FirstOrderDate,
    MAX(sales_order.OrderDate) AS LastOrderDate,
    DATEDIFF
    (
        day,
        MAX(sales_order.OrderDate),
        CASE
            WHEN MAX(sales_order.CustomerType) = 'Store'
                THEN @StoreAnalysisDate
            ELSE @AnalysisDate
        END
    ) AS RecencyDays,
    COUNT(*) AS OrderCount,
    SUM(sales_order.SubTotal) AS CustomerRevenue,
    AVG(sales_order.SubTotal) AS AverageOrderValue
FROM Analytics.vwSalesOrders AS sales_order
GROUP BY sales_order.CustomerID;

Each measure now has an unambiguous meaning:

• OrderCount is the number of orders placed by an account.
• CustomerRevenue is the sum of order subtotals for that account.
• AverageOrderValue is the account’s commercial sales value divided by its orders.
• RecencyDays is the number of days from the account’s last order to the reference date for its customer type.

The complete supporting script stores this result in a temporary table so the same customer-level metrics can be reused without repeating the aggregation.

Two Very Different Customer Models

The first comparison separates store and individual accounts.

These figures come from the script’s active-customer summary query, which groups the customer-level dataset by CustomerType.

Store accounts represent only 3.32% of ordering accounts, but they generate almost three-quarters of revenue. Their average order value is nearly twenty times the individual average, and 605 of the 635 ordering store accounts purchase more than once.

Individuals provide much more order volume, but at a substantially lower value per order. Of the 18,484 ordering individual accounts, 6,865 are repeat purchasers.

This isn’t simply a useful customer segment. It is effectively two different commercial models living in the same database.

There is also an important limitation. Every individual order is online, while every store order is salesperson-assisted. The customer-type and channel query confirms that they are perfectly aligned in this sample. I cannot use this data to determine whether the differences are caused by the kind of customer, the sales channel, or both.

How Much Value Comes from Repeat Purchasing?

I define a repeat customer account as one with at least two orders during the available period.

The one-time and repeat query derives both groups from OrderCount and calculates their share of total revenue with a windowed aggregate.

Only 39.07% of ordering accounts are repeat purchasers, yet they account for nearly 94% of revenue.

That doesn’t mean converting any one-time buyer into a repeat buyer will produce the same value. Store accounts are both more likely to repeat and much more valuable per order, so the overall percentage is heavily influenced by the store business.

The safer conclusion is that repeat relationships are commercially important, but retention priorities should be evaluated separately for stores and individuals.

Store relationships

312 frequent store accounts generate 72.02% of store revenue, making sustained account relationships central to the store channel.

Individual conversion

11,619 individual accounts order once, but the data doesn’t contain marketing interactions or acquisition context that would explain why.

A Simple Frequency Segmentation

Segmentation is most useful when people can understand how a customer entered a group. I start with a deliberately simple definition:

• One-time: one order
• Repeat: two to four orders
• Frequent: five or more orders

CASE
    WHEN OrderCount = 1 THEN 'One-time'
    WHEN OrderCount BETWEEN 2 AND 4 THEN 'Repeat (2-4)'
    ELSE 'Frequent (5+)'
END AS FrequencySegment

The results again differ sharply by customer type.

The complete frequency-segmentation query applies the same CASE expression before aggregating accounts, orders, revenue, and revenue share by customer type.

The frequent threshold identifies an important store group, but it behaves strangely for individuals. The 95 individuals with five or more orders generate less than 1% of individual revenue. More orders don’t automatically mean greater value when order sizes differ.

This is why I would not turn the segment labels directly into a marketing programme. They provide a useful first view of behaviour, but a production segmentation would also consider value, product mix, tenure, margin, and potentially customer acquisition source.

Measuring Time Between Purchases

Order count doesn’t show purchase cadence. Two customers may each place four orders, while one purchases monthly and the other returns once a year.

The LAG window function makes the previous order date available without joining the order table to itself:

LAG(OrderDate) OVER
(
    PARTITION BY CustomerID
    ORDER BY OrderDate, SalesOrderID
) AS PreviousOrderDate

I use SalesOrderID as a deterministic tie-breaker because the sample contains same-day orders. A gap of zero days is therefore valid rather than an error.

Across all repeat-purchase intervals:

• Store accounts average 98.72 days between orders.
• Individual accounts average 317.12 days between orders.
• Observed intervals range from same-day repeat orders to more than two years.

The purchase-interval query filters out each customer’s first order, then aggregates the dated intervals produced with LAG.

The averages are useful for orientation, but the wide ranges mean they shouldn’t become automatic contact schedules. A median, distribution percentiles, or segment-specific cadence would provide a stronger operational baseline. Those techniques fit naturally into the later advanced-analysis article.

Adding Recency

Frequency describes how often an account has purchased. Recency asks how long it has been since the most recent purchase.

I use three initial bands:

• 0 to 90 days
• 91 to 365 days
• more than 365 days

The recency query applies these bands to RecencyDays and groups the results by customer type.

The 142 ordering store accounts with no order in more than a year deserve investigation because their historical revenue totals $12.38 million. That figure is not a forecast of recoverable revenue, and the sample contains no reason for their inactivity. It is a prioritised question for account managers, not proof that a re-engagement campaign will work.

The recency thresholds are also working assumptions. A one-year gap may signal attrition for one product category and a normal replacement cycle for another. Product-level analysis will help add that context.

Business Recommendations

The findings support several practical next steps.

Protect important store relationships

Store accounts generate 73.27% of revenue, and frequent stores contribute most of that value. Account-management reporting should track store recency, order frequency, value, and changes in cadence rather than relying on total sales alone.

Investigate one-time individual purchasing

Most ordering individual accounts purchase once. Before proposing a retention campaign, I would examine the products bought, acquisition period, geography, and whether repeat purchasing is realistic for those items.

Review lapsed store accounts individually

The 142 store accounts beyond the one-year recency threshold represent enough historical value to justify investigation. Before offering a blanket discount or sending the same re-engagement campaign to every store, the relevant account manager should review each store’s purchase history, product mix, previous ordering cadence, and salesperson relationship. That may reveal whether the change reflects normal purchasing cycles, a lost account, or something that needs direct follow-up.

Keep store and individual benchmarks separate

A single average order value or repeat rate hides two fundamentally different patterns. Performance targets, expected cadence, and meaningful segments should be defined independently for the two customer types.

Limitations

This analysis describes a static sample rather than a live customer base.

It doesn’t include:

• acquisition source or marketing interactions
• customer-service history
• profitability or cost to serve
• reasons for inactivity
• a channel comparison independent of customer type
• a guaranteed one-to-one mapping between customer accounts and store businesses

The frequency and recency bands are transparent starting points, not validated commercial rules. They become more useful when combined with product, regional, and time-based analysis.

Next in the Series

The next article will measure product performance.

I will examine product and category contribution, sales volume, discounts, and changes in performance over time. That will add product context to the customer patterns identified here and help distinguish commercially important products from those that simply rank highly on one measure.