Customer retention based on product & date selection

[rob2]

I have seen many posts related to customer retention but none that fit my situation.  I'm new to Power BI / DAX so changing the examples for my situation has been difficult.

What I'm trying to do:

• Group new customers (never purchased any product) that purchased the selected products in their first purchase date then track whether they purchased any products after that.

Dataset:

• https://www.dropbox.com/s/oqv5r5k9gxo8oor/AdventureWorks.pbix?dl=0

Steps:

• Calculate the first purchase date for each customer (x-axis).
• Count distinct # of customers who purchased a product from product category 4 in their first purchase date.
• How many of the new customers that had a purchase from category 4 purchased from any products in subsequent periods?
• Below is an example of how I would expect the results to look like based on my T-SQL query using my attached dataset.

• T-SQL query:

  ------------------------------------------------------------
  -- Identify new customers for a given period
  ------------------------------------------------------------ 
  
  	------------------------------------------------------------
  	-- Get Customer First Purchase Date
  	------------------------------------------------------------ 
  
  		DROP TABLE #Customer_First_Purchase_Date
  		select
  			a.CustomerKey
  			, min(a.ShipDate) as 'First_Purchase_Date'
  		into #Customer_First_Purchase_Date
  		from [dbo].[FactInternetSales] a
  		group by 
  			a.CustomerKey
  
  	------------------------------------------------------------
  	-- Identify new customers that purchased from product category 4
  	------------------------------------------------------------ 
  			
  		drop table #NewCustomers_Selected_Items_Dates
  		select
  		distinct 
  				a.CustomerKey
  				, eomonth(First_Purchase_Date,0) as  First_Purchase_Date
  		into #NewCustomers_Selected_Items_Dates
  		from #Customer_First_Purchase_Date a
  			inner join [dbo].[FactInternetSales] b on a.CustomerKey = b.CustomerKey and a.First_Purchase_Date = b.ShipDate
  			inner join continuousimprovement.dbo.DimProductData c on c.ProductKey = b.ProductKey
  			where c.ProductCategoryKey = 4
  		order by 1 
  		
  	------------------------------------------------------------
  	-- Get Totals - Paste to Excel and create pivot table
  	------------------------------------------------------------ 
  
  		select
  			a.CustomerKey
  			, a.First_Purchase_Date
  			, eomonth(b.ShipDate,0) as 'ShipDate_Month'
  			, count(distinct a.CustomerKey) as 'NumCust'
  		from #NewCustomers_Selected_Items_Dates a
  			inner join [dbo].[FactInternetSales] b on a.CustomerKey = b.CustomerKey
  			inner join continuousimprovement.dbo.DimProductData c on c.ProductKey = b.ProductKey
  		group by 
  			a.CustomerKey
  			, a.First_Purchase_Date
  			, eomonth(b.ShipDate,0) 			
  		order by 1

Thanks for your help!

[v-jiascu-msft · Accepted solution]

Hi @rob2,

Try this formula, please. 

Measure =
VAR purchasedFristDay =
    CALCULATETABLE (
        VALUES ( FactInternetSales[CustomerKey] ),
        FILTER (
            ALL (
                FactInternetSales[FirstPurchaseDate],
                FactInternetSales[ShipDate],
                FactInternetSales[FirstPurchaseDate_Month]
            ),
            [FirstPurchaseDate] = FactInternetSales[ShipDate]
                && FactInternetSales[FirstPurchaseDate_Month]
                    = MIN ( FactInternetSales[FirstPurchaseDate_Month] )
        ),
        ALL ( 'Calendar' )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( FactInternetSales[CustomerKey] ),
        FILTER (
            ALL ( FactInternetSales ),
            FactInternetSales[CustomerKey] IN purchasedFristDay
                && FactInternetSales[FirstPurchaseDate] < FactInternetSales[ShipDate]
                && YEAR ( FactInternetSales[ShipDate] )
                    = YEAR ( MIN ( 'Calendar'[Date_USFormat] ) )
                && MONTH ( FactInternetSales[ShipDate] )
                    = MONTH ( MIN ( 'Calendar'[Date_USFormat] ) )
        )
    )

Best Regards,
Dale

[rob2]

Very nice Dale!  I will review and better undertand your approach in order to improve my skills.  Tx!

[Ashish_Mathur]

Hi,

I am not sure of how much i can help but i'd like to try.  Please share data in an MS Excel file format.

[rob2]

@Ashish_Mathur Thanks for looking. Here is the Excel file.

https://www.dropbox.com/s/2p74h00ewkdvxkz/AdventureWorksExcel%20File.xlsx?dl=0

[v-jiascu-msft]

Hi @rob2,

Try out this solution, please.

1. Add a column in the table [FactInternetSales].

FirstPurchaseDate =
CALCULATE (
    MIN ( FactInternetSales[ShipDate] ),
    ALLEXCEPT ( FactInternetSales, FactInternetSales[CustomerKey] )
)

2. Create a measure.

Measure =
VAR purchasedFristDay =
    CALCULATETABLE (
        VALUES ( FactInternetSales[CustomerKey] ),
        FILTER ( FactInternetSales, [FirstPurchaseDate] = FactInternetSales[ShipDate] )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( FactInternetSales[CustomerKey] ),
        FILTER (
            ALL ( FactInternetSales ),
            FactInternetSales[CustomerKey] IN purchasedFristDay
                && FactInternetSales[FirstPurchaseDate] < FactInternetSales[ShipDate]
        )
    )

Please don't share sensitive data here. 

Best Regards,
Dale

[rob2]

@v-jiascu-msft  I added the measures (new PBIX file below) and not giving me the expected results. 

New file with measure:  https://www.dropbox.com/s/oqv5r5k9gxo8oor/AdventureWorks.pbix?dl=0

I'm getting the results below (#'s should tie out the #'s in my original post).