Introduction

Customer reactivation is a key metric for any business, especially in B2B sales, where maintaining long-term relationships can drive sustainable revenue. Identifying which customers return after long periods of inactivity helps businesses analyze engagement strategies, optimize marketing efforts, and improve retention. In this case study, I leveraged SQL to extract customer orders from December 2024, focusing on those who hadn’t placed an order in at least 90 days prior.

The Challenge

A business wants to track reactivated customers—those who returned to make a purchase after being inactive for three months or more. Instead of manually filtering customer data in spreadsheets, we can efficiently extract these orders using SQL.

The SQL Solution

I constructed a SQL query that:

1. Identifies the most recent order date before December 2024 for each customer.
2. Filters December 2024 orders where the last recorded purchase was 90+ days prior.
3. Extracts only the first qualifying order in December to prevent duplicate entries for customers with multiple purchases.
4. Displays key order details, including customer information, order total, and days since the previous order.

Query Breakdown

The query consists of three key parts:

1. Previous Order Lookup: The PreviousLOD subquery finds the last recorded order before December 2024 for each customer.
2. Filtering Reactivation Orders: The main query joins this data to extract only those orders where the gap is 90 days or more.
3. First Qualifying Order in December: The main query incorporates the min_order CTE (Common Table Expression) to capture only each customer’s earliest order in December.

 WITH min_order AS (
   SELECT orders.CustomerNumber,
          MIN(orders.OrderDateTime) AS minOrderDate
   FROM orders
      LEFT JOIN (
        SELECT orders_1.CustomerNumber,
               MAX(orders_1.OrderDateTime) AS PreviousLastOrder
        FROM orders AS orders_1  
        WHERE orders_1.OrderDateTime < '2024-12-01'
        GROUP BY orders_1.CustomerNumber
        ) PreviousLOD
           ON orders.CustomerNumber = PreviousLOD.CustomerNumber
        WHERE OrderDateTime BETWEEN '2024-12-01' AND '2024-12-31'
              AND DATE_DIFF(DATE(orders.OrderDateTime),
              DATE(PreviousLOD.PreviousLastorder), DAY) >= 90
        GROUP BY orders.CustomerNumber
       )

 SELECT orders.CustomerNumber,
        orders.CustomerName,
        CONCAT(orders.City, ', ', orders.Province) AS CityProv,
        orders.OrderNumber,
        orders.OrderTotal,
        CAST(orders.OrderDateTime AS DATE) AS OrderDate,
        PreviousLOD.PreviousLastOrder,
        DATE_DIFF(DATE(orders.OrderDateTime), DATE(PreviousLOD.PreviousLastorder), DAY) as     DaysBetween
 FROM orders
  LEFT JOIN (
     SELECT orders_1.CustomerNumber,
            CAST(MAX(orders_1.OrderDateTime) AS DATE) AS PreviousLastOrder
    FROM orders AS orders_1 
    WHERE orders_1.OrderDateTime < '2024-12-01'
    GROUP BY orders_1.CustomerNumber
    ) PreviousLOD
        ON orders.CustomerNumber = PreviousLOD.CustomerNumber
  INNER JOIN min_order
       ON orders.CustomerNumber = min_order.CustomerNumber
       AND orders.OrderDateTime = min_order.minOrderDate
 WHERE OrderDateTime BETWEEN '2024-12-01' AND '2024-12-31'
      AND DATE_DIFF(DATE(orders.OrderDateTime), DATE(PreviousLOD.PreviousLastorder), DAY) >= 90;

Results:

Insights and Impact

By running this query, we can generate a list of reactivated customers, helping businesses:

• Understand purchasing behavior after a period of inactivity.
• Identify trends in seasonal reactivation.
• Develop targeted marketing campaigns for customers who are likely to return after long gaps.

Conclusion

SQL provides a powerful way to automate and streamline customer analysis. By using this approach, businesses can track reactivations in real-time, eliminating the need for manual data processing. If you’re working with customer order data, try adapting this query to your dataset and gain deeper insights into customer retention trends!

Dataset

This dataset was created by me! 😊 Feel free to conduct your own analysis using it. You can find it on Kaggle here: B2B Retail Orders Dataset. Happy analyzing!