1 Reply Latest reply on Sep 18, 2018 2:21 PM by Patrick A Van Der Hyde

    how to find repeat customers vs total unique customers month by month

    sheetal.oka.0

      Tableau version 2018.1.2

       

      Hello,

       

      This is my first tableau post so please excuse me for my long past and if this sounds very basic. I am trying to plot a graph of % of monthly repeat customers out of total unique customers.

       

      The logic given to me ( which can not be altered) to determine if a customer is a repeat customer of a given month is

      Customer has at least one 'Buy' type transaction in given month and at least one 'Buy' type transaction in previous two months.

      that is if customer A had one a 'Buy' transaction in May, zero in July and one in August then customer A is a repeat customer in month of August.

      if customer B had zero  'Buy' transaction in May, one in July and two in August then customer B is a repeat customer in month of August.

      if customer C had zero  'Buy' transaction in May, zero in July and two in August then customer B is NOT  a repeat customer in month of August.

       

      I have attached the packaged workbook..

       

      I used following formula to determine repeat customer and it works however the monthly total of repeat customers is always coming as 1 no matter how many repeat customers...

       

       

      if(WINDOW_SUM(sum([cCustomerTransactionCountPerMonth]),-2,-1) >= 1  // customer has at least one transaction in previous two months

      and

      WINDOW_SUM(sum([cCustomerTransactionCountPerMonth]),0,0) >= 1 // customer has at least one transaction in current month

      )  then     TRUE  else     FALSE  END

       

      where cCustomerTransactionCountPerMonth is

      { FIXED [CTransactionMYCustomer]  

       

      : COUNT(if([Transaction Type] = 'Buy' )then [Invoice Id] end )}

      and

      CTransactionMYCustomer is

      STR(DATEPART('month',[Transaction Date]))

      +'-'+

      STR(DATEPART('year',[Transaction Date]))

      +'-'+

      STR([Customer Id])