4 Replies Latest reply: Jun 1, 2018 6:33 AM by Mauro Vasconi RSS

    HOW TO CALCULATE LOST CUSTOMER

    Mauro Vasconi

      Dear all,

      i need your help in order to calculate lost customers. I know  well that it's not easy.

      Lost customer for me is : customer without revenue in the last 6 months

       

      I found an articulate script in this forum and I readjusted it with my fields.

      All works fine but not for lost customer: at the moment (with this script) the lost customer is a customer with "no revenue" in the previous month but that's not what I need.

      Could you please help me in order to solve this "enigma"  ?

      Thanks in advance for your support

      Have a nice day

      Mauro

       

       

      ******* this is my script *******

      FATTURE_TMP:

       

      FATTURE:

      Load

          ANNOMESE,  --> Yearmonth related to the revenue

          ReferenceDate,

          _KCliente,    -->  Customer code

          _KFiliale,    --> Branch code

          SUM(TOTALE_VOCE) AS TOTALE_FATT     --> Total invoice

         

          RESIDENT FATTURE_TMP

          GROUP By

          [_KFiliale],[_KCliente],ReferenceDate,ANNOMESE

          Order by [_KCliente],[_KFiliale],ANNOMESE;

       

      Temp:

      LOAD

      num(min(FieldValue('ANNOMESE',RecNo()))) as MinAnnomese,

      num(max(FieldValue('ANNOMESE',RecNo()))) as MaxAnnomese

      AutoGenerate FieldValueCount('ANNOMESE')

      ;


      LET vMinAnnomese = peek('MinAnnomese');

      LET vMaxAnnomese = peek('MaxAnnomese');


      Temp_Hist:

      LOAD distinct

      [_KCliente],

      ANNOMESE as Yearmonth,

      AutoNumber(ANNOMESE,[_KCliente]&[_KFiliale]) AS Index

      RESIDENT FATTURE

      ORDER BY [_KCliente], ANNOMESE

      ;

      JOIN (Temp_Hist)

      LOAD FieldValue('_KCliente', RecNo()) as [_KCliente], num($(vMinAnnomese) + IterNo() -1) AS Yearmonth

      AutoGenerate FieldValueCount('_KCliente')

      While $(vMinAnnomese) + IterNo() -1<= $(vMaxAnnomese)

      ;


      History:

      LOAD * Where rangesum(New,Lost,Returning,Loyal);

      LOAD *,

      if(Index = 1,1) as New,

      if(CC and Previous(Index) and not alt(Index,0),1)  as Lost,

      if(CC and Index>1 and not alt(Previous(Index),0),1) as Returning,

      if(CC and Previous(Index)=Index-1,1) as Loyal

      ;

      LOAD *, if(Previous([_KCliente])=[_KCliente],1,0) as CC

      Resident Temp_Hist

      ;


      Drop Table Temp;

      Drop table Temp_Hist;

      DROP TABLE FATTURE_TMP;

      Drop Fields Index, CC;