Wednesday, November 18, 2026

How to Analyze Customer Purchase frequency and Identify Inactive Customers using DAX in Power BI

How to find First and Second Purchase Dates and detect Non-Recurring Customers using DAX in Power BI
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.

We can explore these scenarios further using the following Power BI data model:


Relationships of the Data Model:


dim_Customer:

dim_Products:

dim_Calendar (Date Ranges for 2026):


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 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
    )

The following measure returns the time gap in days between a customer’s first and second purchases to assess repeat purchase behavior.
Days Between First and Second Purchases =
IF (
    NOT ISBLANK ( [Second Purchase Date] ),
    DATEDIFF (
        [First Purchase Date],
        [Second Purchase Date],
        DAY
    )
)

To Identify Customers Who Purchased a Specific Product, Such as "Radio":
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
    )

To Identify the number of Days elapsed between the Customer's most recent purchase date and the current date:

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
--------------------------------------------------------------------------------------------------------

Thursday, November 12, 2026

How to Analyze Customer Purchase journey and Identify Inactive Customers using SQL

How to find First and Second Purchase Dates and detect Non-Recurring Customers using SQL
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.

-- 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 ;


INSERT INTO
dbo.dim_Products
(
    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.
We can achieve this Scenario using the below SQL Query:

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
--------------------------------------------------------------------------------------------------------

How to find Customers who only Purchased a specific Product using SQL

How to Identify the Single-Category Loyal Customers Using SQL
Scenario:
In today's data-driven business environment, understanding customer purchasing behavior is critical for driving revenue growth and improving customer engagement. 
One common analytical requirement is identifying customers who purchase exclusively from a single product category and have never explored other product offerings. 
These customers often represent highly loyal segments with distinct buying patterns, making them valuable candidates for targeted marketing and growth initiatives.

Consider a retailer offering multiple product categories such as Radios, Televisions, Routers, and Laptops. The business wants to identify customers who have purchased only Radio products throughout their transaction history and determine the revenue generated by this customer segment. 
By leveraging a simple yet highly optimized SQL aggregation technique, organizations can efficiently analyze large transaction datasets and uncover these loyal customers.

In this article, we will explore an SQL solution that identifies customers who have purchased only Radio products, calculates their revenue contribution, and demonstrates how a simple query can deliver meaningful business insights from transactional data.

-- 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 ;


INSERT INTO
dbo.dim_Products
(
    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

---------------------------------------------------------
Now we can use the below SQL Query to Identify the customers who have purchased only the Product is Radio and not any other Products:

SELECT
    c.Customer_ID,
    c.Customer_Name,
    MAX(p.Product_Name) AS Product_Name,
    SUM(f.Sales_Amount) AS Revenue
FROM dbo.fact_Transactions f
INNER JOIN dbo.dim_Customers c
    ON c.Customer_ID = f.Customer_ID
INNER JOIN dbo.dim_Products p
    ON p.Product_ID = f.Product_ID
GROUP BY
    c.Customer_ID,
    c.Customer_Name
HAVING
    MIN(f.Product_ID) = 101 -- 'Radio'
    AND MAX(f.Product_ID) = 101; 

#Result:

We can use the below query to Identify the customers who have purchased only the Product is Radio and not any other Products in last 90 days:

SELECT
    c.Customer_ID,
    c.Customer_Name,
    MAX(p.Product_Name) AS Product_Name,
    SUM(f.Sales_Amount) AS Revenue
FROM dbo.fact_Transactions f
INNER JOIN dbo.dim_Customers c
    ON c.Customer_ID = f.Customer_ID
INNER JOIN dbo.dim_Products p
    ON p.Product_ID = f.Product_ID
WHERE
    -- Option 1: Last 90 Days
    f.Transact_Date >= DATEADD(DAY, -90, GETDATE())

    -- Option 2: Last 3 Calendar Months
    -- f.Transact_Date >= DATEADD(MONTH, -3, GETDATE())
GROUP BY
    c.Customer_ID,
    c.Customer_Name
HAVING
    MIN(f.Product_ID) = 101 -- 'Radio'
    AND MAX(f.Product_ID) = 101; 

#Result:

Business Value Creation:
  • Identify Product Loyalty: Discover customers who consistently purchase only one product category, providing valuable insights into customer preferences and product affinity.
  • Increase Cross-Sell Opportunities: Target loyal customers with complementary products and services to encourage broader product adoption and increase revenue.
  • Improve Marketing Effectiveness: Develop focused campaigns based on customer purchasing behavior rather than relying on generic audience targeting.
  • Measure Revenue Contribution: Quantify the revenue generated by loyal customer segments and evaluate their business impact.
  • Enhance Customer Segmentation: Build more accurate customer profiles and support personalized engagement strategies.
  • Support Data-Driven Decision Making: Enable sales, marketing, and product teams to make informed decisions using actionable insights derived from transactional data.
  • Increase Customer Lifetime Value (CLV): Identify opportunities to deepen customer relationships and maximize long-term business value through targeted recommendations and retention strategies.
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose we have a sample Sales data, which is stored with Monthly Snapsh...

Popular Posts from this Blog