基于SQL Server与AdventureWorks2012的客户流失判定方法咨询

Alright, let's tackle identifying churned customers in AdventureWorks2012 using your definition—customers who had transactions in a 6-month window, then went silent for a subsequent period. Here's a practical, step-by-step approach tailored to the data attributes you have:

First, we need to turn your vague "某段时间" (some period) into concrete thresholds. A standard setup is:

• Active Window: The 6-month period where the customer had at least one transaction (we’ll anchor this to the latest order date in the database for consistency).
• Churn Window: The period after the active window where no transactions occurred (e.g., 3 months—adjust this based on your business needs).

Start by calculating key dates and order counts for each customer using AdventureWorks' core sales tables (Sales.Customer and Sales.SalesOrderHeader):

WITH CustomerOrderMetrics AS (
    SELECT
        c.CustomerID,
        MIN(soh.OrderDate) AS FirstOrderDate,
        MAX(soh.OrderDate) AS LastOrderDate,
        COUNT(DISTINCT soh.SalesOrderID) AS TotalOrders
    FROM Sales.Customer c
    LEFT JOIN Sales.SalesOrderHeader soh 
        ON c.CustomerID = soh.CustomerID
    GROUP BY c.CustomerID
)

Now, use the time windows to label each customer as active or churned. We’ll use the database's latest order date as our reference point:

SELECT
    com.CustomerID,
    com.FirstOrderDate,
    com.LastOrderDate,
    com.TotalOrders,
    -- Check if customer was active in the 6-month window before the latest order
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM Sales.SalesOrderHeader soh
            WHERE soh.CustomerID = com.CustomerID
            AND soh.OrderDate BETWEEN DATEADD(MONTH, -6, (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)) 
                AND (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)
        ) THEN 1 
        ELSE 0 
    END AS WasActiveIn6MonthWindow,
    -- Flag as churned if no orders in the 3 months after the active window (adjust months as needed)
    CASE 
        -- Safe guard against impossible future orders
        WHEN EXISTS (
            SELECT 1 
            FROM Sales.SalesOrderHeader soh
            WHERE soh.CustomerID = com.CustomerID
            AND soh.OrderDate > (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)
        ) THEN 0
        -- If time since last order exceeds churn window threshold
        WHEN DATEDIFF(MONTH, com.LastOrderDate, (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)) > 3 THEN 1
        ELSE 0
    END AS IsChurned
FROM CustomerOrderMetrics com
WHERE com.TotalOrders > 0; -- Only include customers who've made at least one order

You can dig deeper by incorporating product categories, subcategories, or online order flags to segment churned customers:

Example: Churn by Product Category #

WITH CustomerCategoryActivity AS (
    SELECT
        c.CustomerID,
        pc.Name AS ProductCategory,
        MAX(soh.OrderDate) AS LastOrderInCategory
    FROM Sales.Customer c
    JOIN Sales.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID
    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
    GROUP BY c.CustomerID, pc.Name
)
SELECT
    CustomerID,
    ProductCategory,
    LastOrderInCategory,
    CASE 
        WHEN DATEDIFF(MONTH, LastOrderInCategory, (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)) > 3 THEN 1 
        ELSE 0 
    END AS IsChurnedInCategory
FROM CustomerCategoryActivity;

Example: Churn by Online/Offline Orders #

Add soh.OnlineOrderFlag to your grouping to see if online customers churn at a different rate than offline ones—just include it in the GROUP BY and select clauses.

• Flexible Time Windows: Swap out the 3 in DATEDIFF(MONTH, ..., ...) > 3 with your preferred churn threshold (e.g., 6 months for longer-term churn).
• New Customer Exclusion: If you want to exclude customers who first ordered in the last month of the active window (since they might not have had time to churn), add a filter like DATEDIFF(MONTH, com.FirstOrderDate, (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)) > 1.
• Null Handling: The LEFT JOIN ensures we capture all customers, even those who stopped ordering entirely.

内容的提问来源于stack exchange，提问作者GNJ