6 Replies Latest reply: Jun 11, 2018 1:56 PM by Youssef Belloum RSS

    different types of customers

    willem van der Plas

      Hello all,

      I made a customer distribution a while ago (loyal, new, stopped). Now it is true that I determined this on the basis of months (see script). Now I want to do this on the basis of weeks (5 weeks) instead of months because this is somewhat more robust and can be extended or shortened by a week. Can someone tell me how the script should look like?

      Below the script that I now use:

       

      //CustomerGrouping
      CustomerClassesTemp:
      Load Distinct
      %DebiteurId
      Resident DebiteurEFEP;

      ////Creates flag for Customers with an order this month
      Left Join (CustomerClassesTemp)
      Load
      %DebiteurId,
      '1'
      as HasOrdersThisMonthFlag
      Resident Feitentabel
      Where %DatumId >= MonthStart(Today());


      ////Creates flag for Customers with an order in a prior month
      Left Join (CustomerClassesTemp)
      Load
      %DebiteurId,
      '1'
      as HasPastOrdersFlag
      Resident Feitentabel
      Where %DatumId < Date(MonthStart(Today()));


      Left Join(DebiteurEFEP)
      Load
      %DebiteurId,
      If(HasOrdersThisMonthFlag = '1' and Isnull(HasPastOrdersFlag),'New Customer',
      if(HasOrdersThisMonthFlag = '1' and HasPastOrdersFlag = '1','Loyal Customer',
      if(HasPastOrdersFlag = '1' and isnull(HasOrdersThisMonthFlag),'Stopped Customer'))) as [Customer Group]
      Resident CustomerClassesTemp;
      Drop Table CustomerClassesTemp;

       

       

       

      Thanks in advance!