Scenario:
Understanding customer purchase behavior is one of the most valuable applications of data analytics. Beyond knowing what customers purchase, businesses often want to understand how quickly customers return after their first purchase and which customers have stopped purchasing altogether.
These insights help organizations measure customer engagement, improve retention strategies, and identify opportunities to increase Customer Lifetime Value (CLV).
In this article, we will use a simple transaction dataset to address below two common business scenarios:
Business Scenario 1: Customer Repeat Purchase Analysis:
Find the First and Second Purchase Dates for each Customer and calculate the number of days between them.Business Scenario 2: Identifying Non-Recurring Customers:
Identify Non-Recurring (Inactive) Customers who have not made a purchase in the last 90 days or 3 months.
Identify Non-Recurring (Inactive) Customers who have not made a purchase in the last 90 days or 3 months.
-- Create required Tables
CREATE TABLE dbo.dim_Customers
(
Customer_ID INT,
Customer_Name VARCHAR(50)
);
CREATE TABLE dbo.dim_Products
(
Product_ID INT,
Product_Name VARCHAR(50)
);
CREATE TABLE dbo.fact_Transactions
(
Transact_ID INT,
Transact_Date DATE,
Customer_ID INT,
Product_ID INT,
Sales_Amount DECIMAL(10,2)
);
-- Insert Data into Tables
INSERT INTO dbo.dim_Customers
(
Customer_ID,
Customer_Name
)
VALUES
(1,'Sun'),
(2,'Moon'),
(3,'Earth'),
(4,'Water'),
(5,'Sky');
SELECT * FROM dbo.dim_Customers ;
(
Product_ID,
Product_Name
)
VALUES
(101,'Radio'),
(102,'Television'),
(103,'Router'),
(104,'Laptop');
SELECT * FROM dbo.dim_Products;
-- TRUNCATE TABLE dbo.fact_Transactions
INSERT INTO dbo.fact_Transactions
(
Transact_ID,
Transact_Date,
Customer_ID,
Product_ID,
Sales_Amount
)
VALUES
-- Sun (Only Radio)
(1001,'2026-08-20',1,101,10000),
(1002,'2026-10-15',1,101,12000),
-- Moon (Radio + Television)
(1003,'2026-07-20',2,101,15000),
(1004,'2026-10-05',2,102,30000),
-- Earth (Only Radio)
(1005,'2026-02-10',3,101,8000),
-- Water (Only Laptop)
(1006,'2026-03-25',4,104,70000),
-- Sky (Radio + Router)
(1007,'2026-08-30',5,101,9000),
(1008,'2026-11-01',5,103,5000);
SELECT * FROM dbo.fact_Transactions
---------------------------------------------------------
Business Scenario 1: Customer Repeat Purchase Analysis:
Business to understand how quickly customers return after making their first purchase. The time gap between the first and second purchases is a key indicator of customer engagement and purchasing behavior.
Customers who return quickly are often more engaged and have a higher likelihood of becoming long-term customers.
Business Benefits:
- Measure Customer Engagement by tracking how quickly customers make a second purchase.
- Identify High-Value Repeat Buyers who demonstrate strong purchasing behavior.
- Improve Marketing Timing by understanding the optimal period for follow-up campaigns.
- Support Customer Retention Strategies using customer purchase patterns.
- Increase Customer Lifetime Value (CLV) through proactive engagement.
We can achieve this Scenario using the below SQL Query:
WITH CTE_Ranked_Purchases AS
(
SELECT
f.Customer_ID,
f.Transact_Date,
p.Product_Name,
ROW_NUMBER() OVER
(
PARTITION BY f.Customer_ID
ORDER BY f.Transact_Date ASC
) AS RN -- Purchase Sequence Number
FROM dbo.fact_Transactions f
INNER JOIN dbo.dim_Products p
ON f.Product_ID = p.Product_ID
)
SELECT
c.Customer_ID,
c.Customer_Name,
MAX(CASE WHEN RN = 1 THEN Transact_Date END) AS First_Purchase_Date,
MAX(CASE WHEN RN = 1 THEN Product_Name END) AS First_Purchase_Product,
MAX(CASE WHEN RN = 2 THEN Transact_Date END) AS Second_Purchase_Date,
MAX(CASE WHEN RN = 2 THEN Product_Name END) AS Second_Purchase_Product,
DATEDIFF
(
DAY,
MAX(CASE WHEN RN = 1 THEN Transact_Date END),
MAX(CASE WHEN RN = 2 THEN Transact_Date END)
) AS Days_Between_Purchases
FROM CTE_Ranked_Purchases rp
INNER JOIN dbo.dim_Customers c
ON rp.Customer_ID = c.Customer_ID
WHERE RN <= 2 -- Keep only First and Second Purchases
GROUP BY
c.Customer_ID,
c.Customer_Name
HAVING COUNT(*) = 2 -- Customers having both 1st and 2nd Purchases
ORDER BY
c.Customer_ID;
#Result:
Business Scenario 2: Identifying Non-Recurring Customers:
Customer retention is often more cost-effective than acquiring new customers. By identifying customers who have not purchased within a defined period, business can proactively launch retention campaigns, improve engagement, and encourage repeat purchases.
In this scenario, a customer is considered Non-Recurring (Inactive) if they have not made a purchase in the Last 90 days or last 3 months.
Business Benefits:
- Identify Inactive Customers who have stopped purchasing.
- Launch Targeted Retention Campaigns to re-engage dormant customers.
- Improve Customer Retention through timely outreach.
- Optimize Marketing Spend by focusing on customers most likely to return.
- Monitor Customer Health using purchase recency.
- Support Data-Driven Decision-Making for customer engagement initiatives.
WITH CTE_LastPurchase AS
(
SELECT
f.Customer_ID,
f.Transact_Date,
p.Product_Name,
ROW_NUMBER() OVER
(
PARTITION BY f.Customer_ID
ORDER BY f.Transact_Date DESC
) AS RN -- Purchase Sequence Number
FROM dbo.fact_Transactions f
INNER JOIN dbo.dim_Products p
ON f.Product_ID = p.Product_ID
)
SELECT
c.Customer_ID,
c.Customer_Name,
lp.Transact_Date AS Last_Purchase_Date,
lp.Product_Name AS Last_Purchase_Product,
DATEDIFF
(
DAY,
lp.Transact_Date,
GETDATE()
) AS Days_Inactive
FROM CTE_LastPurchase lp
INNER JOIN dbo.dim_Customers c
ON lp.Customer_ID = c.Customer_ID
WHERE lp.RN = 1 -- Most recent Purchase
-- Option 1: Inactive for more than 90 Days
AND lp.Transact_Date < DATEADD(DAY, -90, GETDATE())
-- Option 2: Inactive for more than 3 Calendar Months
-- AND lp.Transact_Date < DATEADD(MONTH, -3, GETDATE())
ORDER BY
c.Customer_ID;
#Result:
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
