11 Replies Latest reply: Oct 11, 2016 1:47 PM by Samuel Lin RSS

    Count #Orders for specific products, Determine segmentation by order timing

    Samuel Lin

      Hello Qlik Geeks,

       

      I have a data set like this:

           

      CustomerIDOrder DateProduct
      12310/1/16A
      12310/1/16B
      12310/3/16C
      12310/5/16D
      12310/10/16A
      45610/1/16A
      45610/2/16A
      45610/3/16B
      45610/5/16C
      45610/5/16D
      45610/10/16C
      45610/11/16C

       

      I want to do something either in the backend script load or front end to:

       

      (1) Segment customers by the number of times placing orders for specific or a group of product; or count the number of times a customer has ordered a specific product

      (2) Determine the last date that a certain customer has ordered product A (or any other product)

      (3) Segment customers into three segments:

           (a)customers who have recently (within the last 6months) bought productA

           (b)customers who have placed at least an order of productA 6+months ago and continue to buy other products other than A recently (within 6months)

           (c)customers who have placed at least an order of productA 6+months ago and stop buying.

       

      I have tried to create segmentation from load script but the outcome doesn't seem accurate, please help and let me know your thoughts!

       

      Thank you,

      Samuel

        • Re: Count #Orders for specific products, Determine segmentation by order timing
          Sunny Talwar

          Would you be able to provide your requirement in form of numbers?

            • Re: Count #Orders for specific products, Determine segmentation by order timing
              Samuel Lin

              Hi Sunny, than you for your reply. I have seen your response to numerous qlik community posts and am very thankful to get your attention to mine.

               

              not exactly sure what you meant by form of numbers, but let me try:

               

              first of all, let me add one more column, Revenue, associated with each order

               

                    

              CustomerIDOrder DateProductRevenue
              12310/1/16A350
              12310/1/16B250
              12310/3/16C100
              12310/5/16D400
              12310/10/16A
              45610/1/16A
              45610/2/16A
              45610/3/16B
              45610/5/16C
              45610/5/16D
              45610/10/16C
              45610/11/16C

               

              (matches with the original post (1) to (3); using productA as the specific product)

              (1) ideally, I think I would like to have a dimension that can count customer who have order product A once, twice, 3~10 times, 10+ times... any thought about calculating revenue from a specific product (e.g. productA)

                    

              DimensionCustomer CountTotal RevenueRevenue from ProductA
              1100
              2200
              3 to 10250
              10+300

               

               

              (2) I would like to have a dimension that can count number of customers who last ordered product A, within 30days, 30 to 60 days, 60 to 90 days, 90 to 180 days, 180+ days....

                    

              DimensionCustomer CountTotal RevenueRevenue from ProductA
              30100
              30 to 60200
              60 to 90250
              90 to 180300
              ….

               

              (3)

                    

              DimensionCustomer CountTotal RevenueRevenue from ProductA
              Recent100
              Stopped ProductA and continued200
              Stopped ProductA and Lost250

               

               

               

               

              Please let me know if this is what you were asking about?

               

              Thanks!!