none
Rolling 12 Months Query To Count Customers RRS feed

  • Dotaz

  • Hello, could someone please help me write a query that would on rolling 12 months basis count number of distinct customers that are newly acquired (first transaction ever), reacquired (were newly acquired before then stopped transacting for at least 12 months and now started transacting again), existing (transacted now but also transacted in prior twelve months), lapsed (not transacted in 12 months). This needs to be done on monthly basis and should be able to go back 4-5 years.

    Payment_Table
    Customer_ID Transaction_Date Transaction_Amount Transaction_Year Transaction_Month
    1 Jun-30-2017 100 2017 6
    1 Aug-20-2018 50 2018 8
    1 Aug-25-2018 100 2018 8
    1 Oct-05-2019 150 2019 10
    2 Apr-10-2016 20 2016 4
    2 Aug-05-2019 250 2019 8
    3 Sep-02-2017 30 2017 9
    4 Jun-10-2015 200 2015 6
    4 Jun-04-2016 40 2016 6
    4 Jun-02-2017 450 2017 6
    4 July-10-2018 50 2018 7

    Customer_Table      
    Customer_ID Customer_Acquired_Date (Same as First Transaction Date) Acquired_Year Acquired_Month
    1 Jun-30-2017 2017 6
    2 Apr-10-2016 2016 4
    3 Sep-02-2017 2017 9
    4 Jun-10-2015 2015 6

    I would really appreciate your help in solving this. Thank you! :).

    pondělí 14. října 2019 23:52

Odpovědi

  • Hi Dodi_777

    Here it goes.

    DROP TABLE IF EXISTS #Payment_Table;
    DROP TABLE IF EXISTS #Customer_Table ;
    DROP TABLE IF EXISTS #DateList ;
    GO
    Create table #Payment_Table
    (Customer_ID	int
    ,Transaction_Date	date
    ,Transaction_Amount int
    ,Transaction_Year	int
    ,Transaction_Month int)
    
    Create table #Customer_Table
    (Customer_ID	int
    ,Customer_Acquired_Date	date
    ,Acquired_Year int
    ,Acquired_Month	int)
    
    CREATE table #DateList (MonthY VARCHAR(100),Month int, Year Int)
    GO
    
    INSERT INTO #Payment_Table
    VALUES
    (2,'04-10-2016',20,2016,4),
    (2,'08-05-2019',250,2019,8)
    
    INSERT INTO #Customer_Table
    VALUES
    (2,'04-10-2016',2016,4)
    
    --Generate Monthly Records
    DECLARE @Today Date = EOMONTH(GETDATE())
    DECLARE @Past Date = DATEADD(Year,-4,CAST(CAST(YEAR(@Today) AS VARCHAR(10))+'-01-01' AS DATE))
    DECLARE @Loop Date = @Today
    
    WHILE (@Loop > @Past)
    BEGIN
    	INSERT INTO #DateList
    	SELECT LEFT(Datename(month,@Loop),3)+'-'+RIGHT(YEAR(@Loop),2) MonthY,DATEPART(Month,@Loop),DATEPART(Year,@Loop)
    
    	SET @Loop = DATEADD(Month,-1,@Loop)
    END
    
    GO
    
    WITH [New Customer Count] AS (
    SELECT 
    	Customer_ID
    	,MIN(Transaction_Date) TranDate
    	,DatePart(Month,MIN(Transaction_Date)) TranMonth
    	,DatePart(Year,MIN(Transaction_Date)) TranYear
    FROM #Payment_Table
    GROUP BY Customer_ID)
    
    ,[Reacquired Customer Count] AS (
    SELECT 
    	Customer_ID
    	,Transaction_Date TranDate
    	,COUNT(Customer_ID) TotalTranCount
    	,DatePart(Month,MIN(Transaction_Date)) TranMonth
    	,DatePart(Year,MIN(Transaction_Date)) TranYear
    	,DATEDIFF(Month,LAG(Transaction_Date) OVER(ORDER BY Transaction_Date),Transaction_Date) MonthsDiff
    FROM #Payment_Table 
    GROUP BY Customer_ID,Transaction_Date)
    
    ,[Lapsed Customer Count] AS (
    SELECT 
    	Customer_ID
    	,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END TranDate
    	,DatePart(Month,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranMonth
    	,DatePart(Year,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranYear
    	,DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) MonthsDiff
    FROM #Payment_Table
    GROUP BY Customer_ID,Transaction_Date)
    
    ,[Existing Customer Count] AS (
    SELECT 
    	Customer_ID
    	,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) < 12 THEN DATEADD(Year,1,Transaction_Date) END TranDate
    	,DatePart(Month,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranMonth
    	,DatePart(Year,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranYear
    	,DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) MonthsDiff
    FROM #Payment_Table
    GROUP BY Customer_ID,Transaction_Date)
    
    SELECT 
    	MonthY
    	,a.Month,a.Year
    	,SUM(CASE WHEN NewCusCnt.Customer_ID IS NOT NULL THEN 1 END) [New Customer Count (Monthly)]
    	,SUM(CASE WHEN NewCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END) [New Customer Count (Rolling 12 Months)]
    	,SUM(CASE WHEN ReacCusCnt.Customer_ID IS NOT NULL THEN 1 END) [Reacquired Customer Count (Monthly)]
    	,SUM(CASE WHEN ReacCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END) [Reacquired Customer Count (Rolling 12 Months)]
    	,SUM(CASE WHEN LapCusCnt.Customer_ID IS NOT NULL THEN 1 END) [Lapsed Customer Count (monthly)]
    	,SUM(CASE WHEN LapCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END) [Lapsed Customer Count (Rolling 12 Months)]
    	,SUM(CASE WHEN ExiCusCnt.Customer_ID IS NOT NULL THEN 1 END) [Existing Customer Count (monthly)]
    	,SUM(CASE WHEN ExiCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END) [Existing Customer Count (Rolling 12 Months)]
    FROM #DateList a
    INNER JOIN #Customer_Table Cust
    ON Cust.Customer_ID IS NOT NULL
    LEFT JOIN [New Customer Count] NewCusCnt
    ON Cust.Customer_ID = NewCusCnt.Customer_ID AND a.Month = NewCusCnt.TranMonth AND a.Year = NewCusCnt.TranYear
    LEFT JOIN [New Customer Count] NewCusCntAnnual
    ON Cust.Customer_ID = NewCusCntAnnual.Customer_ID AND DATEDIFF(Month,NewCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    LEFT JOIN [Reacquired Customer Count] ReacCusCnt
    ON Cust.Customer_ID  = ReacCusCnt.Customer_ID AND a.Month = ReacCusCnt.TranMonth AND a.Year = ReacCusCnt.TranYear AND ReacCusCnt.MonthsDiff >12
    LEFT JOIN [Reacquired Customer Count] ReacCusCntAnnual
    ON Cust.Customer_ID  = ReacCusCntAnnual.Customer_ID AND ReacCusCntAnnual.MonthsDiff >12  AND DATEDIFF(Month,ReacCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    LEFT JOIN [Lapsed Customer Count] LapCusCnt
    ON Cust.Customer_ID = LapCusCnt.Customer_ID AND a.Month = LapCusCnt.TranMonth AND a.Year = LapCusCnt.TranYear
    LEFT JOIN [Lapsed Customer Count] LapCusCntAnnual
    ON Cust.Customer_ID = LapCusCntAnnual.Customer_ID  AND DATEDIFF(Month,LapCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    LEFT JOIN [Lapsed Customer Count] ExiCusCnt
    ON Cust.Customer_ID = ExiCusCnt.Customer_ID AND a.Month = ExiCusCnt.TranMonth AND a.Year = ExiCusCnt.TranYear
    LEFT JOIN [Lapsed Customer Count] ExiCusCntAnnual
    ON Cust.Customer_ID = ExiCusCntAnnual.Customer_ID  AND DATEDIFF(Month,ExiCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    GROUP BY a.MonthY,a.Month,a.Year
    ORDER BY a.Year,a.Month
    

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    pondělí 21. října 2019 6:00

Všechny reakce

  • Hi,

    Sorry for my poor understanding.

    Could you  please share us your expected result and you logic more clearly? So that we’ll get a right direction and make some test.

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    úterý 15. října 2019 2:33
  • Hello, could someone please help me write a query that would on rolling 12 months basis count number of distinct customers that are newly acquired (first transaction ever), reacquired (were newly acquired before then stopped transacting for at least 12 months and now started transacting again), existing (transacted now but also transacted in prior twelve months), lapsed (not transacted in 12 months). This needs to be done on monthly basis and should be able to go back 4-5 years.

    Payment_Table
    Customer_ID Transaction_Date Transaction_Amount Transaction_Year Transaction_Month
    1 Jun-30-2017 100 2017 6
    1 Aug-20-2018 50 2018 8
    1 Aug-25-2018 100 2018 8
    1 Oct-05-2019 150 2019 10
    2 Apr-10-2016 20 2016 4
    2 Aug-05-2019 250 2019 8
    3 Sep-02-2017 30 2017 9
    4 Jun-10-2015 200 2015 6
    4 Jun-04-2016 40 2016 6
    4 Jun-02-2017 450 2017 6
    4 July-10-2018 50 2018 7

    Customer_Table      
    Customer_ID Customer_Acquired_Date (Same as First Transaction Date) Acquired_Year Acquired_Month
    1 Jun-30-2017 2017 6
    2 Apr-10-2016 2016 4
    3 Sep-02-2017 2017 9
    4 Jun-10-2015 2015 6

    I would really appreciate your help in solving this. Thank you! :).

    Good day Dodi,

    OFF-topic the question: related to design of your database

    1. Are you using this database for Data warehouse or for OLTP?

    If you are not sure what these mean, then just answer next question

    2. Why did you design the table to have duplicate data of the first Transaction Date in both tables?

    In first glance it looks like a problematic design for OLTP database or small database. You can get the First Transaction Date from the Payment_Table so why do you need it in the Customer_Table as well?!?

    Think about this point.

    * Regarding the question, I agree with Rachel. Please provide the information she asked for and in addition please provide queries to create the tables including the indexes which you have, and queries to insert the sample data

    Thanks,


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    úterý 15. října 2019 2:56
    Moderátor
  • Hi Rachel, thank you for replying back. I am sorry that I did a bad job explaining what I want from this output.

    This is what I am expecting the output to look like:

      Monthly Rolling 12 Months
    Month-Year New Customer Count Reacquired Customer Count Existing Customer Count Lapsed Customer Count New Customer Count Reacquired Customer Count Existing Customer Count Lapsed Customer Count
    Oct-19                
    Sep-19                
    Aug-19                
    Jul-19                
    Jun-19                
    May-19                
    Apr-19                
    Mar-19                
    Feb-19                
    Jan-19                
    Dec-18                
    Nov-18                
    Oct-18                
    Sep-18                

    .

    .

    .

                 
    Jul-15                

    Basically, every month the output should show a monthly and a rolling 12 month count of new customers, lapsed customers, reacquired customers and existing customers. As an example the Oct 2019 number would show October's numbers under monthly section and Nov-2018 to Oct-2019  number under rolling 12 months section. This output would then be used to calculate retention rates and churn rate. New Customers in a given month or rolling 12 months would be someone who has made their first transaction within that period. Lapsed Customer would someone who has not made a transaction in last 12 months (number of months since last transaction = 12 months). Reacquired Customers is someone for who difference between gift in this period and the prior gift is > 12 months (if the this same customer after being reacquired continues to give then in the next period onwards this customer would be considered an existing customer). Existing Customer is someone for who has made a transaction in the last 12 months.

    I hope I've been able to explain it a little better. Thank you so much.

    úterý 15. října 2019 5:54
  • Hi Ronen,


    Thank you for replying to my question.

    Here are the answers to your questions:

    1. I am not sure I understand that.

    2. The two tables already exist on the server and both have hundreds of other fields that I am using here. I did mention both the tables here with just the relevant fields as they might be needed in some calculation and also to explain what lowest payment_date for each customer id means (it means the date they were acquired on. Both these tables are huge tables with nearly 14 million customer accounts and nearly 4 billion transaction records.

    Response to Rachel:

    Hi Rachel, thank you for replying back. I am sorry that I did a bad job explaining what I want from this output.

    This is what I am expecting the output to look like:

      Monthly Rolling 12 Months
    Month-Year New Customer Count Reacquired Customer Count Existing Customer Count Lapsed Customer Count New Customer Count Reacquired Customer Count Existing Customer Count Lapsed Customer Count
    Oct-19                
    Sep-19                
    Aug-19                
    Jul-19                
    Jun-19                
    May-19                
    Apr-19                
    Mar-19                
    Feb-19                
    Jan-19                
    Dec-18                
    Nov-18                
    Oct-18                
    Sep-18                

    .

    .

    .

                 
    Jul-15                

    Basically, every month the output should show a monthly and a rolling 12 month count of new customers, lapsed customers, reacquired customers and existing customers. As an example the Oct 2019 number would show October's numbers under monthly section and Nov-2018 to Oct-2019  number under rolling 12 months section. This output would then be used to calculate retention rates and churn rate. New Customers in a given month or rolling 12 months would be someone who has made their first transaction within that period. Lapsed Customer would someone who has not made a transaction in last 12 months (number of months since last transaction = 12 months). Reacquired Customers is someone for who difference between gift in this period and the prior gift is > 12 months (if the this same customer after being reacquired continues to give then in the next period onwards this customer would be considered an existing customer). Existing Customer is someone for who has made a transaction in the last 12 months.

    I hope I've been able to explain it a little better. Thank you so much.

    úterý 15. října 2019 6:01
  • Could you please share us expected data in your expected table ? 

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    úterý 15. října 2019 9:19
  • Hello Rachel,

    My apologies for the confusion. Here is the expected output from the above mentioned data points. Thank you :).

    Best Wishes,

    Dodi

      Monthly Rolling 12 Months
    Month-Year New Customer Count Reacquired Customer Count Existing Customer Count Lapsed Customer Count New Customer Count Reacquired Customer Count Existing Customer Count Lapsed Customer Count
    Oct-19   1       2   1
    Sep-19       1   1   1
    Aug-19   1       1 1 1
    Jul-19           1 1 1
    Jun-19           2 1 2
    May-19           2 1 3
    Apr-19           2 1 3
    Mar-19           2 1 3
    Feb-19           2 1 3
    Jan-19           2 1 3
    Dec-18           2 1 3
    Nov-18           2 1 3
    Oct-18           2 1 3
    Sep-18     1     2 1 3
    Aug-18   1     1 2   2
    Jul-18       1 1 1   2
    Jun-18       1 2     1
    May-18         2   1  
    Apr-18         2   1  
    Mar-18         2   1 1
    Feb-18         2   1 1
    Jan-18         2   1 1
    Dec-17         2   1 1
    Nov-17         2   1 1
    Oct-17         2   1 1
    Sep-17 1       2   1 1
    Aug-17         1   1 1
    Jul-17 1       1   1 1
    Jun-17     1       1 1
    May-17             1 1
    Apr-17             1 1
    Mar-17         1   1  
    Feb-17         1   1  
    Jan-17         1   1  
    Dec-16         1   1  
    Nov-16         1   1  
    Oct-16         1   1  
    Sep-16         1   1  
    Aug-16         1   1  
    Jul-16         1   1  
    Jun-16     1   1   1  
    May-16         2      
    Apr-16 1       2      
    Mar-16         1      
    Feb-16         1      
    Jan-16         1      
    Dec-15         1      
    Nov-15         1      
    Oct-15         1      
    Sep-15         1      
    Aug-15         1      
    Jul-15         1      
    Jun-15 1       1      
    May-15                
    Apr-15                
    Mar-15                
    Feb-15                
    Jan-15                
    Dec-14                
    Nov-14                
    Oct-14                
    Sep-14                


    • Upravený Dodi_777 neděle 20. října 2019 21:28
    středa 16. října 2019 3:08
  • Many thanks for your detailed reply. But I'm still sorry, I don't know how you got the data in the table. What does this have to do with your original table?

    I'm really sorry for my poor understanding.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    středa 16. října 2019 9:18
  • Basically, utilized original data and rules to create the output table. Now I would like to write a sql query that would do the same as the actual tables have close to 14 million customers and nearly 4 billion transaction records. Your expert skills in sql would be every helpful. I really appreciate you looking at this question. Thank you :).
    středa 16. října 2019 14:07
  • Input data and rules are mentioned in the initial messages (it is not allowing me to copy those again here). Those are utilized to create the output table.
    středa 16. října 2019 14:09
  • This is what the output table would look like if it were created only for customer id = 1.

    Customer Count Output
      Monthly Rolling 12 Months
    Month-Year New Customer Count Reacquired Customer Count Existing Customer Count Lapsed Customer Count New Customer Count Reacquired Customer Count Existing Customer Count Lapsed Customer Count
    Oct-19   1       1   1
    Sep-19               1
    Aug-19       1       1
    Jul-19           1    
    Jun-19           1    
    May-19           1   1
    Apr-19           1   1
    Mar-19           1   1
    Feb-19           1   1
    Jan-19           1   1
    Dec-18           1   1
    Nov-18           1   1
    Oct-18           1   1
    Sep-18           1   1
    Aug-18   1       1   1
    Jul-18               1
    Jun-18       1       1
    May-18         1      
    Apr-18         1      
    Mar-18         1      
    Feb-18         1      
    Jan-18         1      
    Dec-17         1      
    Nov-17         1      
    Oct-17         1      
    Sep-17         1      
    Aug-17         1      
    Jul-17         1      
    Jun-17 1       1      
    May-17                
    Apr-17                
    Mar-17                
    Feb-17                
    Jan-17                
    Dec-16                
    Nov-16                
    Oct-16                
    Sep-16                
    Aug-16                
    Jul-16                
    Jun-16                
    May-16                
    Apr-16                
    Mar-16                
    Feb-16                
    Jan-16                
    Dec-15                
    Nov-15                
    Oct-15                
    Sep-15                
    Aug-15                
    Jul-15                
    Jun-15                
    May-15                
    Apr-15                
    Mar-15                
    Feb-15                
    Jan-15                

    středa 16. října 2019 14:15
  • This is what output table would look like if it were created only for customer id = 2

    Customer Count Output
      Monthly Rolling 12 Months
    Month-Year New Customer Count Reacquired Customer Count Existing Customer Count Lapsed Customer Count New Customer Count Reacquired Customer Count Existing Customer Count Lapsed Customer Count
    Oct-19           1    
    Sep-19           1    
    Aug-19   1       1    
    Jul-19                
    Jun-19                
    May-19                
    Apr-19                
    Mar-19                
    Feb-19                
    Jan-19                
    Dec-18                
    Nov-18                
    Oct-18                
    Sep-18                
    Aug-18                
    Jul-18                
    Jun-18                
    May-18                
    Apr-18                
    Mar-18               1
    Feb-18               1
    Jan-18               1
    Dec-17               1
    Nov-17               1
    Oct-17               1
    Sep-17               1
    Aug-17               1
    Jul-17               1
    Jun-17               1
    May-17               1
    Apr-17       1       1
    Mar-17         1      
    Feb-17         1      
    Jan-17         1      
    Dec-16         1      
    Nov-16         1      
    Oct-16         1      
    Sep-16         1      
    Aug-16         1      
    Jul-16         1      
    Jun-16         1      
    May-16         1      
    Apr-16 1       1      
    Mar-16                
    Feb-16                
    Jan-16                
    Dec-15                
    Nov-15                
    Oct-15                
    Sep-15                
    Aug-15                
    Jul-15                
    Jun-15                
    May-15                
    Apr-15                
    Mar-15                
    Feb-15                
    Jan-15                

    středa 16. října 2019 14:19
  • Hello, could someone please help me write a query that would on rolling 12 months basis count number of distinct customers that are newly acquired (first transaction ever), reacquired (were newly acquired before then stopped transacting for at least 12 months and now started transacting again), existing (transacted now but also transacted in prior twelve months), lapsed (not transacted in 12 months). This needs to be done on monthly basis and should be able to go back 4-5 years.

    Payment_Table
    Customer_ID Transaction_Date Transaction_Amount Transaction_Year Transaction_Month
    1 Jun-30-2017 100 2017 6
    1 Aug-20-2018 50 2018 8
    1 Aug-25-2018 100 2018 8
    1 Oct-05-2019 150 2019 10
    2 Apr-10-2016 20 2016 4
    2 Aug-05-2019 250 2019 8
    3 Sep-02-2017 30 2017 9
    4 Jun-10-2015 200 2015 6
    4 Jun-04-2016 40 2016 6
    4 Jun-02-2017 450 2017 6
    4 July-10-2018 50 2018 7

    Customer_Table      
    Customer_ID Customer_Acquired_Date (Same as First Transaction Date) Acquired_Year Acquired_Month
    1 Jun-30-2017 2017 6
    2 Apr-10-2016 2016 4
    3 Sep-02-2017 2017 9
    4 Jun-10-2015 2015 6

    I would really appreciate your help in solving this. Thank you! :).

    Hi Dodi_777,

    Thank you for your detailed .

    Based on your raw data and expected results, it's hard for me to figure out what the logic is. I can easily understand the value 'New Customer Count'.When there is a record in the Customer_Table table, there will be a corresponding 1 in your result table. But I'm still confused about where the other columns came from and how they were calculated. For example, Reacquired Customer Count, Existing Customer Count and so on.

    Best Regards,

    Rachel



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    čtvrtek 17. října 2019 8:59
  • Hi Rachel,

    New Customer Count (Monthly) = 1 when it is the first transaction ever made to the organization by this customer. It can either be the lowest Customer transaction date from the Payment Table or it could where Customer transaction date (from payment table) = Customer Acquisition Date (from Customer Table).

    New Customer Count (Rolling 12 Months) = 1 for the first month and also for the following 11 months as this is the rolling 12 months. It has the same logic as New Customer Count (Monthly); when it is the first transaction ever made to the organization by this customer.

    Reacquired Customer Count (Monthly) = 1 when a customer makes a transaction but this transaction date is greater than the customer acquisition date by more than 12 months (in other words the Reacquired Customer Count = 1 when the customer makes a transaction and the difference between this gift and the prior gifts is more than 12 months. Also, total transaction count for the given customer is at least >= 2).

    Reacquired Customer Count (Rolling 12 Months) = 1 based on the same logic as Reacquired Customer Count (Monthly), just keep counting this 1 for the following 11 months as well as this is a rolling 12 months count.

    Lapsed Customer Count (monthly) = 1 when a given customer has not made a transaction in = 12 months.

    Lapsed Customer Count (Rolling 12 Months) = 1 when a given customer has not made a transaction in = 12 months. It is also = 1 the following 11 months as this is rolling 12 months count.

    Existing Customer Count (monthly) = 1 when a customer makes another transaction where the difference between this and the prior transaction is less then 12 months.

    Existing Customer Count (Rolling 12 Months) = 1 Existing Customer Count (Monthly) = 1 and then again = 1 the following 11 months as this is a rolling 12 months count.

    Example of Customer ID = 1

    On Jun-30-2017

    Payment_Table
    Customer_ID Transaction_Date Transaction_Amount Transaction_Year Transaction_Month
    1 Jun-30-2017 100 2017 6
    1 Aug-20-2018 50 2018 8
    1 Aug-25-2018 100 2018 8
    1 Oct-05-2019 150 2019 10

    In Jun-2017, customer id = 1 is considered new acquisition as this is the min transaction date for this customer.

    In Jun-2018 this customer is considered lapsed as it has been 12 months since prior transaction.

    In Aug-2018 this customer is considered reacquired as the total gift count is >=2 and also the difference between this transaction and prior transaction is greater than 12 months.

    The second payment in the same month (Aug-2018) will have no impact. We define the customer based on the first transaction of the month.

    In Aug-2019 this customer is considered lapsed as it has been 12 months since prior transaction.

    In Oct-2019 this customer is considered reacquired as the total gift count is >=2 and also the difference between this transaction and prior transaction is greater than 12 months.

    Please let me know if this explains the concept better?

    Best Wishes,

    Dodi


    • Upravený Dodi_777 neděle 20. října 2019 21:27
    čtvrtek 17. října 2019 13:23
  • Hi Dodi_777, 

    Thank you for your detailed explanation. You are so kind and your question might be clearly. However, I am still confused. Please forgive me.

    Fistly , I know that in your expected result you have two parts . One is Monthly data , another is Rolling 12 Months data. Monthly data is related to a row of Payment_Table . If you have a row in Aug-16 and you will set q from Aug-16 to Jul-17 for Rolling 12 Months data. Right?

    Secondly , in your expected data it might be unclear for me to distinguish which values are in which column because of many null values. Maybe you can show it clearly in the chart. 

    If I have incorrect understanding , please let me know.

    Thank you in advance for your patience.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    pátek 18. října 2019 8:53
  • Hi Dodi_777

    It took a long time to understand your question & replicate your data to tables. Next time when raise a request in Forums please post the data in SQL Scripts along with DDL.

    Here goes the DDL

    Create table #Payment_Table
    (Customer_ID	int
    ,Transaction_Date	date
    ,Transaction_Amount int
    ,Transaction_Year	int
    ,Transaction_Month int)
    
    Create table #Customer_Table
    (Customer_ID	int
    ,Customer_Acquired_Date	date
    ,Acquired_Year int
    ,Acquired_Month	int)
    
    CREATE table #DateList (MonthY VARCHAR(100),Month int, Year Int)
    
    INSERT INTO #Payment_Table
    VALUES
    (2,'04-10-2016',20,2016,4),
    (2,'08-05-2019',250,2019,8)
    
    INSERT INTO #Customer_Table
    VALUES
    (2,'04-10-2016',2016,4)
    
    --Generate Monthly Records
    DECLARE @Today Date = EOMONTH(GETDATE())
    DECLARE @Past Date = DATEADD(Year,-4,CAST(CAST(YEAR(@Today) AS VARCHAR(10))+'-01-01' AS DATE))
    DECLARE @Loop Date = @Today
    
    WHILE (@Loop > @Past)
    BEGIN
    	INSERT INTO #DateList
    	SELECT LEFT(Datename(month,@Loop),3)+'-'+RIGHT(YEAR(@Loop),2) MonthY,DATEPART(Month,@Loop),DATEPART(Year,@Loop)
    
    	SET @Loop = DATEADD(Month,-1,@Loop)
    END

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    pátek 18. října 2019 9:37
  • Hi Dodi_777

    Although it took a long time to understand your question & replicate your data to tables. Next time when raise a request in Forums please post the data in SQL Scripts along with DDL.

    However, after spending lot much I was to complete the code which is required to fulfill your requirement.
    Create table #Payment_Table
    (Customer_ID	int
    ,Transaction_Date	date
    ,Transaction_Amount int
    ,Transaction_Year	int
    ,Transaction_Month int)
    
    Create table #Customer_Table
    (Customer_ID	int
    ,Customer_Acquired_Date	date
    ,Acquired_Year int
    ,Acquired_Month	int)
    
    CREATE table #DateList (MonthY VARCHAR(100),Month int, Year Int)
    
    INSERT INTO #Payment_Table
    VALUES
    (2,'04-10-2016',20,2016,4),
    (2,'08-05-2019',250,2019,8)
    
    INSERT INTO #Customer_Table
    VALUES
    (2,'04-10-2016',2016,4)
    
    --Generate Monthly Records
    DECLARE @Today Date = EOMONTH(GETDATE())
    DECLARE @Past Date = DATEADD(Year,-4,CAST(CAST(YEAR(@Today) AS VARCHAR(10))+'-01-01' AS DATE))
    DECLARE @Loop Date = @Today
    
    WHILE (@Loop > @Past)
    BEGIN
    	INSERT INTO #DateList
    	SELECT LEFT(Datename(month,@Loop),3)+'-'+RIGHT(YEAR(@Loop),2) MonthY,DATEPART(Month,@Loop),DATEPART(Year,@Loop)
    
    	SET @Loop = DATEADD(Month,-1,@Loop)
    END
    
    
    WITH [New Customer Count] AS (
    SELECT 
    	Customer_ID
    	,MIN(Transaction_Date) TranDate
    	,DatePart(Month,MIN(Transaction_Date)) TranMonth
    	,DatePart(Year,MIN(Transaction_Date)) TranYear
    FROM #Payment_Table
    WHERE Customer_ID = 2
    GROUP BY Customer_ID)
    
    ,[Reacquired Customer Count] AS (
    SELECT 
    	Customer_ID
    	,Transaction_Date TranDate
    	,COUNT(Customer_ID) TotalTranCount
    	,DatePart(Month,MIN(Transaction_Date)) TranMonth
    	,DatePart(Year,MIN(Transaction_Date)) TranYear
    	,DATEDIFF(Month,LAG(Transaction_Date) OVER(ORDER BY Transaction_Date),Transaction_Date) MonthsDiff
    FROM #Payment_Table 
    WHERE Customer_ID = 2
    GROUP BY Customer_ID,Transaction_Date)
    
    ,[Lapsed Customer Count] AS (
    SELECT 
    	Customer_ID
    	,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END TranDate
    	,DatePart(Month,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranMonth
    	,DatePart(Year,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranYear
    	,DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) MonthsDiff
    FROM #Payment_Table 
    WHERE Customer_ID = 2
    GROUP BY Customer_ID,Transaction_Date)
    
    ,[Existing Customer Count] AS (
    SELECT 
    	Customer_ID
    	,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) < 12 THEN DATEADD(Year,1,Transaction_Date) END TranDate
    	,DatePart(Month,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranMonth
    	,DatePart(Year,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranYear
    	,DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) MonthsDiff
    FROM #Payment_Table 
    WHERE Customer_ID = 2
    GROUP BY Customer_ID,Transaction_Date)
    
    SELECT 
    	MonthY
    	,CASE WHEN NewCusCnt.Customer_ID IS NOT NULL THEN 1 END NewCusCnt
    	,CASE WHEN NewCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END NewCusCntAnnual
    	,CASE WHEN ReacCusCnt.Customer_ID IS NOT NULL THEN 1 END ReacCusCnt
    	,CASE WHEN ReacCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END ReacCusCntAnnual
    	,CASE WHEN LapCusCnt.Customer_ID IS NOT NULL THEN 1 END LapCusCnt
    	,CASE WHEN LapCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END LapCusCntAnnual
    	,CASE WHEN ExiCusCnt.Customer_ID IS NOT NULL THEN 1 END ExiCusCnt
    	,CASE WHEN ExiCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END ExiCusCntAnnual
    FROM #DateList a
    INNER JOIN #Customer_Table Cust
    ON Cust.Customer_ID IS NOT NULL
    LEFT JOIN [New Customer Count] NewCusCnt
    ON Cust.Customer_ID = NewCusCnt.Customer_ID AND a.Month = NewCusCnt.TranMonth AND a.Year = NewCusCnt.TranYear
    LEFT JOIN [New Customer Count] NewCusCntAnnual
    ON Cust.Customer_ID = NewCusCntAnnual.Customer_ID AND DATEDIFF(Month,NewCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    LEFT JOIN [Reacquired Customer Count] ReacCusCnt
    ON Cust.Customer_ID  = ReacCusCnt.Customer_ID AND a.Month = ReacCusCnt.TranMonth AND a.Year = ReacCusCnt.TranYear AND ReacCusCnt.MonthsDiff >12
    LEFT JOIN [Reacquired Customer Count] ReacCusCntAnnual
    ON Cust.Customer_ID  = ReacCusCntAnnual.Customer_ID AND ReacCusCntAnnual.MonthsDiff >12  AND DATEDIFF(Month,ReacCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    LEFT JOIN [Lapsed Customer Count] LapCusCnt
    ON Cust.Customer_ID = LapCusCnt.Customer_ID AND a.Month = LapCusCnt.TranMonth AND a.Year = LapCusCnt.TranYear
    LEFT JOIN [Lapsed Customer Count] LapCusCntAnnual
    ON Cust.Customer_ID = LapCusCntAnnual.Customer_ID  AND DATEDIFF(Month,LapCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    LEFT JOIN [Lapsed Customer Count] ExiCusCnt
    ON Cust.Customer_ID = ExiCusCnt.Customer_ID AND a.Month = ExiCusCnt.TranMonth AND a.Year = ExiCusCnt.TranYear
    LEFT JOIN [Lapsed Customer Count] ExiCusCntAnnual
    ON Cust.Customer_ID = ExiCusCntAnnual.Customer_ID  AND DATEDIFF(Month,ExiCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    ORDER BY a.Year DESC,a.Month DESC




    Hope this is helpful !!

    Thank you

    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    pátek 18. října 2019 9:38
  • Hello nkumar,

    Thank you so much for your response to the question.

    On running this query, I realized that a couple of things need to be fixed. 1. This query seems to be showing the customer reacquired and existing together. A customer can be existing when making a second transaction within the 12 months of prior transaction and also not being an acquired or reacquired customer.

    2. When running this query on multiple customers, the output shows multiple rows (for each customer) while keeping the count as 1. Example, if two customers are acquired in the same month, then the count should increase to 2 while keeping the number of rows the same.

    Would you really appreciate your help with these. Thank you so much.

    Best Wishes,

    Dodi

    neděle 20. října 2019 21:27
  • Hi Dodi_777

    Here it goes.

    DROP TABLE IF EXISTS #Payment_Table;
    DROP TABLE IF EXISTS #Customer_Table ;
    DROP TABLE IF EXISTS #DateList ;
    GO
    Create table #Payment_Table
    (Customer_ID	int
    ,Transaction_Date	date
    ,Transaction_Amount int
    ,Transaction_Year	int
    ,Transaction_Month int)
    
    Create table #Customer_Table
    (Customer_ID	int
    ,Customer_Acquired_Date	date
    ,Acquired_Year int
    ,Acquired_Month	int)
    
    CREATE table #DateList (MonthY VARCHAR(100),Month int, Year Int)
    GO
    
    INSERT INTO #Payment_Table
    VALUES
    (2,'04-10-2016',20,2016,4),
    (2,'08-05-2019',250,2019,8)
    
    INSERT INTO #Customer_Table
    VALUES
    (2,'04-10-2016',2016,4)
    
    --Generate Monthly Records
    DECLARE @Today Date = EOMONTH(GETDATE())
    DECLARE @Past Date = DATEADD(Year,-4,CAST(CAST(YEAR(@Today) AS VARCHAR(10))+'-01-01' AS DATE))
    DECLARE @Loop Date = @Today
    
    WHILE (@Loop > @Past)
    BEGIN
    	INSERT INTO #DateList
    	SELECT LEFT(Datename(month,@Loop),3)+'-'+RIGHT(YEAR(@Loop),2) MonthY,DATEPART(Month,@Loop),DATEPART(Year,@Loop)
    
    	SET @Loop = DATEADD(Month,-1,@Loop)
    END
    
    GO
    
    WITH [New Customer Count] AS (
    SELECT 
    	Customer_ID
    	,MIN(Transaction_Date) TranDate
    	,DatePart(Month,MIN(Transaction_Date)) TranMonth
    	,DatePart(Year,MIN(Transaction_Date)) TranYear
    FROM #Payment_Table
    GROUP BY Customer_ID)
    
    ,[Reacquired Customer Count] AS (
    SELECT 
    	Customer_ID
    	,Transaction_Date TranDate
    	,COUNT(Customer_ID) TotalTranCount
    	,DatePart(Month,MIN(Transaction_Date)) TranMonth
    	,DatePart(Year,MIN(Transaction_Date)) TranYear
    	,DATEDIFF(Month,LAG(Transaction_Date) OVER(ORDER BY Transaction_Date),Transaction_Date) MonthsDiff
    FROM #Payment_Table 
    GROUP BY Customer_ID,Transaction_Date)
    
    ,[Lapsed Customer Count] AS (
    SELECT 
    	Customer_ID
    	,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END TranDate
    	,DatePart(Month,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranMonth
    	,DatePart(Year,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranYear
    	,DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) MonthsDiff
    FROM #Payment_Table
    GROUP BY Customer_ID,Transaction_Date)
    
    ,[Existing Customer Count] AS (
    SELECT 
    	Customer_ID
    	,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) < 12 THEN DATEADD(Year,1,Transaction_Date) END TranDate
    	,DatePart(Month,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranMonth
    	,DatePart(Year,CASE WHEN DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) >12 THEN DATEADD(Year,1,Transaction_Date) END) TranYear
    	,DATEDIFF(Month,Transaction_Date,LEAD(Transaction_Date) OVER(ORDER BY Transaction_Date)) MonthsDiff
    FROM #Payment_Table
    GROUP BY Customer_ID,Transaction_Date)
    
    SELECT 
    	MonthY
    	,a.Month,a.Year
    	,SUM(CASE WHEN NewCusCnt.Customer_ID IS NOT NULL THEN 1 END) [New Customer Count (Monthly)]
    	,SUM(CASE WHEN NewCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END) [New Customer Count (Rolling 12 Months)]
    	,SUM(CASE WHEN ReacCusCnt.Customer_ID IS NOT NULL THEN 1 END) [Reacquired Customer Count (Monthly)]
    	,SUM(CASE WHEN ReacCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END) [Reacquired Customer Count (Rolling 12 Months)]
    	,SUM(CASE WHEN LapCusCnt.Customer_ID IS NOT NULL THEN 1 END) [Lapsed Customer Count (monthly)]
    	,SUM(CASE WHEN LapCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END) [Lapsed Customer Count (Rolling 12 Months)]
    	,SUM(CASE WHEN ExiCusCnt.Customer_ID IS NOT NULL THEN 1 END) [Existing Customer Count (monthly)]
    	,SUM(CASE WHEN ExiCusCntAnnual.Customer_ID IS NOT NULL THEN 1 END) [Existing Customer Count (Rolling 12 Months)]
    FROM #DateList a
    INNER JOIN #Customer_Table Cust
    ON Cust.Customer_ID IS NOT NULL
    LEFT JOIN [New Customer Count] NewCusCnt
    ON Cust.Customer_ID = NewCusCnt.Customer_ID AND a.Month = NewCusCnt.TranMonth AND a.Year = NewCusCnt.TranYear
    LEFT JOIN [New Customer Count] NewCusCntAnnual
    ON Cust.Customer_ID = NewCusCntAnnual.Customer_ID AND DATEDIFF(Month,NewCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    LEFT JOIN [Reacquired Customer Count] ReacCusCnt
    ON Cust.Customer_ID  = ReacCusCnt.Customer_ID AND a.Month = ReacCusCnt.TranMonth AND a.Year = ReacCusCnt.TranYear AND ReacCusCnt.MonthsDiff >12
    LEFT JOIN [Reacquired Customer Count] ReacCusCntAnnual
    ON Cust.Customer_ID  = ReacCusCntAnnual.Customer_ID AND ReacCusCntAnnual.MonthsDiff >12  AND DATEDIFF(Month,ReacCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    LEFT JOIN [Lapsed Customer Count] LapCusCnt
    ON Cust.Customer_ID = LapCusCnt.Customer_ID AND a.Month = LapCusCnt.TranMonth AND a.Year = LapCusCnt.TranYear
    LEFT JOIN [Lapsed Customer Count] LapCusCntAnnual
    ON Cust.Customer_ID = LapCusCntAnnual.Customer_ID  AND DATEDIFF(Month,LapCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    LEFT JOIN [Lapsed Customer Count] ExiCusCnt
    ON Cust.Customer_ID = ExiCusCnt.Customer_ID AND a.Month = ExiCusCnt.TranMonth AND a.Year = ExiCusCnt.TranYear
    LEFT JOIN [Lapsed Customer Count] ExiCusCntAnnual
    ON Cust.Customer_ID = ExiCusCntAnnual.Customer_ID  AND DATEDIFF(Month,ExiCusCntAnnual.TranDate,CAST(CONCAT(a.Year,'-',a.Month,'-','01') AS DATE)) between 0 and 12
    GROUP BY a.MonthY,a.Month,a.Year
    ORDER BY a.Year,a.Month
    

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    pondělí 21. října 2019 6:00
  • Thank you for this. Just had to make some changes and the query worked. Changes made were replacing "1" with customer_id in the sum statements. Then replaced sum with count distinct. Also, in all lead function included partition by customer_id. These changes in your query worked.

    úterý 22. října 2019 23:51