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.
We can explore these scenarios further using the following Power BI data model:
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 DAX Query Logics:
Total Sales = SUM(fact_Transactions[Sales_Amount])
First Purchase Date =
VAR _Result =
IF ( NOT ISBLANK( [Total Sales]),
CALCULATE (
MIN ( dim_Calendar[Date_Key] ),
REMOVEFILTERS ( dim_Products[Product_Name] ),
REMOVEFILTERS ( dim_calendar[Date_Key] )
)
)
RETURN
IF ( HASONEVALUE ( dim_Customer[Customer_Name] ), _Result)
First Purchase Product =
VAR _FirstDate = [First Purchase Date]
VAR _FirstProductID =
CALCULATE (
MIN ( fact_Transactions[Product_ID] ),
REMOVEFILTERS ( dim_Products[Product_Name] ),
REMOVEFILTERS ( dim_Calendar[Date_Key] ),
dim_Calendar[Date_Key] = _FirstDate
)
RETURN
LOOKUPVALUE (
dim_Products[Product_Name],
dim_Products[Product_ID], _FirstProductID
)
Second Purchase Date =
VAR _FirstDate = [First Purchase Date]
VAR _Result =
IF (
NOT ISBLANK ( [Total Sales] ),
CALCULATE (
MIN ( fact_Transactions[Transact_Date] ),
REMOVEFILTERS ( dim_Products[Product_Name] ),
REMOVEFILTERS ( dim_Calendar[Date_Key] ),
KEEPFILTERS ( fact_Transactions[Transact_Date] > _FirstDate )
)
)
RETURN
IF ( HASONEVALUE ( dim_Customer[Customer_Name] ), _Result)
Second Purchase Product =
VAR _SecondDate = [Second Purchase Date]
VAR _SecondProductID =
CALCULATE (
MIN ( fact_Transactions[Product_ID] ),
REMOVEFILTERS ( dim_Products[Product_Name] ),
REMOVEFILTERS ( dim_Calendar[Date_Key] ),
dim_Calendar[Date_Key] = _SecondDate
)
RETURN
LOOKUPVALUE (
dim_Products[Product_Name],
dim_Products[Product_ID], _SecondProductID
)
Days Between First and Second Purchases =
IF (
NOT ISBLANK ( [Second Purchase Date] ),
DATEDIFF (
[First Purchase Date],
[Second Purchase Date],
DAY
)
)
Customers Purchased Radio =
VAR _ProductsPurchased =
CALCULATE (
DISTINCTCOUNT ( fact_Transactions[Product_ID] ),
REMOVEFILTERS ( dim_Products )
)
VAR _RadioPurchased =
CALCULATE (
COUNTROWS ( fact_Transactions ),
REMOVEFILTERS ( dim_Products ),
dim_Products[Product_Name] = "Radio"
)
RETURN
IF (
NOT ISBLANK ( [Total Sales] )
&& _ProductsPurchased = 1
&& _RadioPurchased > 0,
SELECTEDVALUE ( dim_Customer[Customer_Name] )
)
To Identify Customers Who Purchased Only a Specific Product, Such as "Router", and No Other Products:
Customers Only Purchased Router =
VAR _TotalSales =
CALCULATE (
[Total Sales],
REMOVEFILTERS ( dim_Products ),
REMOVEFILTERS ( dim_Calendar ),
VALUES ( dim_Customer[Customer_ID] )
)
VAR _RouterSales =
CALCULATE (
[Total Sales],
VALUES ( dim_Customer[Customer_ID] ),
dim_Products[Product_Name] = "Router"
)
RETURN
IF (
_TotalSales = _RouterSales
&& _RouterSales > 0
&& SELECTEDVALUE ( fact_Transactions[Product_ID] ) = 103, --"Router"
MAX ( dim_Customer[Customer_Name] )
)
# The results of all the above logics for Scenario-1 are shown below:
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.
We can achieve this Scenario using the below DAX Query Logics:
Last Purchase Date =
VAR _Result =
IF (
NOT ISBLANK ( [Total Sales] ),
CALCULATE (
MAX ( dim_Calendar[Date_Key] ),
REMOVEFILTERS ( dim_Products[Product_Name] ),
REMOVEFILTERS ( dim_Calendar[Date_Key] )
)
)
RETURN
IF (
HASONEVALUE ( dim_Customer[Customer_Name] ),
_Result
)
Last Purchase Product =
VAR _LastDate = [Last Purchase Date]
VAR _LastProductID =
CALCULATE (
MIN ( fact_Transactions[Product_ID] ),
REMOVEFILTERS ( dim_Products[Product_Name] ),
REMOVEFILTERS ( dim_Calendar[Date_Key] ),
dim_Calendar[Date_Key] = _LastDate
)
RETURN
LOOKUPVALUE (
dim_Products[Product_Name],
dim_Products[Product_ID], _LastProductID
)
Days Inactive after Last Purchase =
VAR _LastPurchaseDate = [Last Purchase Date]
RETURN
IF (
NOT ISBLANK ( _LastPurchaseDate ),
DATEDIFF (
_LastPurchaseDate,
TODAY (),
DAY
)
)
/*
We can use below logic to classify each customer based on their purchase activity.
Non-Recurring : Last purchase was more than 90 days ago.
Active : Last purchase was within the last 90 days.
*/
Customer Purchase Status in Last 90 Days =
VAR _DaysInactive = [Days Inactive after Last Purchase]
RETURN
IF (
NOT ISBLANK ( _DaysInactive ),
IF (
_DaysInactive > 90,
"Non-Recurring",
"Active"
)
)
# The results of all the above logics for Scenario-2 are shown below:
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------














