6 Replies Latest reply on Jun 28, 2018 7:32 PM by Zhouyi Zhang

    REPEAT CUSTOMERS

    AJ.Yusuf

      Hi All,

       

      I have data from 2004-2018, I would like to find repeat customers based on a few conditions

       

      The main one is base customer id,( but sometimes this will not be same so)

       

      OR

       

      Phone Number (Mobile OR Landline)

       

      OR

       

      Email  ignoring any email with the following words ( 'noemail%' , 'nomail%' )

       

      OR

       

      (First name + Last name + Postcode Match)

       

      How do I do a yearly repeat customer visual representation or table .

       

       

       

       

       

        • 1. Re: REPEAT CUSTOMERS
          Zhouyi Zhang

          Hi, AJ

           

          try this

           

          { FIXED [Basecustomerid]:SUM({ FIXED [Basecustomerid],YEAR([Saledate]):COUNTD([Basecustomerid])})} >1

          OR

          { FIXED [Mobile Phone]:SUM({ FIXED [Mobile Phone],YEAR([Saledate]):COUNTD([Mobile Phone])})} >1

          OR

          { FIXED [Landline Phone]:SUM({ FIXED [Landline Phone],YEAR([Saledate]):COUNTD([Landline Phone])})} >1

          OR

          { FIXED ([Firstname]+[Lastname]+[Postcode1]):SUM({ FIXED ([Firstname]+[Lastname]+[Postcode1]),YEAR([Saledate]):COUNTD(([Firstname]+[Lastname]+[Postcode1]))})} >1

          OR

          { FIXED [Email Condition]:SUM({ FIXED [Email Condition],YEAR([Saledate]):COUNTD(IF [Email Condition] <> '' THEN [Email Condition] END)})} >1

           

          Hope this helps

           

          ZZ

          • 2. Re: REPEAT CUSTOMERS
            AJ.Yusuf

            Thanks ZZ

            • 3. Re: REPEAT CUSTOMERS
              AJ.Yusuf

              Just one thing, forgive my ignorance why do you put  >1

              I am trying to understand this?

              • 4. Re: REPEAT CUSTOMERS
                Zhouyi Zhang

                You are welcome. the reason is you want count repeat customers only, so only count those customers with orders in different year which means at least 2.

                 

                ZZ

                • 5. Re: REPEAT CUSTOMERS
                  AJ.Yusuf

                  Hi zz,

                   

                  There shouldnt be any repeat customers in 2005, yr example is showing 3556, I think its double counting

                   

                  For example if someone brought in 2005 and then in 2009 it should alllocate a 0 and 1. the 0 being the first puchase, I think this caluclation is allocating 1 to both.

                   

                  BuyerA  Year when buy a car  2006    2010     2011

                  Count                                       0           1            1

                   

                  * 0 should be given to the first puchase                 

                   

                  * I believe yr cal is giving 1 to all purchases which are repeat, but the first purchase must be 0

                   

                  thanks aj

                  • 6. Re: REPEAT CUSTOMERS
                    Zhouyi Zhang

                    Hi, AJ

                     

                    Please try this calculation

                     

                    Countd(

                    if Year([Saledate])<> {Fixed:min(Year([Saledate]))} and [Condition] then

                    [Basecustomerid]

                    end

                    )

                     

                    ZZ