6 Replies Latest reply: Oct 18, 2013 11:18 AM by Michael Solomovich RSS

    Script to define customer as new or old

    Paul Edrich

      I have a problem I'm struggling to overcome, I have created a dashboard with around 10 tables all joined with no problem, the tables have customer information, transaction details, product descriptions and so on.

       

      I want to give each customer a flag of "new"  when they buy the first one of a range of products and use that date as the 1st first purchase and potentially a Flag of "Old" for future purchases.

       

      The customer table has a created date, UniqueID.

      The Transaction table is for a specific range of products which a customer may or may not have had.

       

      I would like to have this calculated in the load script and have in the past been successful with a simple if - and Im quite green with scripting so any help or a steer on where to try and find out the answer would be of great help.

       

      P

        • Re: Script to define customer as new or old
          Manish Kachhia

          Could you please load your script to make it easy for everyone to understand?

          • Re: Script to define customer as new or old
            Michael Solomovich

            Paul,


            I'm assuming that the same customer is both "new" and "old", depending on time of purchase.  So, it makes sense to flag the purchase itself as "new" or "old" per customer per product.  The "Yes" value is assigned when a customer buys a product for the first time.  Example:
            "Data" table includes CustomerId, ProductId, PurchaseId, and Date.
            The script to could be:

             

            tmp:
            LOAD DISTINCT
            CustomerId,
            ProductId,
            min(Date) as FirstPurchaseDate // this is the date of the 1st purchase of Product by Customer
            RESIDENT Data
            GROUP BY CustomerId, ProductId;

             

            LEFT JOIN (Data) LOAD DISTINCT
            CustomerId,
            ProductId,
            FirstPurchaseDate
            RESIDENT tmp;
            DROP TABLE tmp;

             

            LEFT JOIN (Data) LOAD DISTINCT
            PurchaseId,
            CustomerId,
            ProductId,
            Date
            if(FirstPurchaseDate=Date, 'Yes','No') as NewCustomer          // this is your flag
            RESIDENT tmp;
            DROP FIELD FirstPurchaseDate;

             

            Regards,

            Michael

              • Re: Script to define customer as new or old
                Paul Edrich

                Hi Michael

                 

                I have added the solution to the load script with amendments to correct field names and receive error messages in order :-

                 

                1. Table not found

                tmp:

                LOAD DISTINCT

                CustomerID,

                ProductID,

                min(AgreementDate) as FirstPurchaseDate

                RESIDENT Data

                GROUP BY CustomerID, ProductID

                 

                2.

                Table not found

                LEFT JOIN (Data) LOAD DISTINCT

                CustomerID,

                ProductID,

                FirstPurchaseDate

                RESIDENT tmp

                 

                3.

                Table not found

                DROP TABLES statement

                 

                4.

                Table not found
                LEFT JOIN (Data) LOAD DISTINCT
                AgreementID,
                CustomerID,
                ProductID,
                AgreementDate,
                if(FirstPurchaseDate=AgreementDate, 'Yes','No') as NewCustomer         

                RESIDENT tmp

                 

                5.

                Field not found

                Did not find the field "FirstPurchaseDate" from the DROP FIELD statement


              • Re: Script to define customer as new or old
                Paul Edrich

                Hi Michael

                 

                Thank you for the reply & sorry for the late reply.

                 

                Looks good and thanks for the help - I will be getting back to this piece of work later this week and will keep you updated.

                 

                Thanks again.

                 

                P