2 Replies Latest reply on Oct 20, 2016 6:00 PM by Bowen CAI

    Customer Segmentation

    Bowen CAI

      Dear All,

       

      I am a newbie here, currently I want to get the customers who bought a item in previously but not in this year. Anyone know how to achieve that? Thanks.

       

      And I have 7 years transactional data in hand. Thanks

       

      Regards,

      Bowen

        • 1. Re: Customer Segmentation
          Chris Geatch

          Without having all of the information about what you're trying to display, I can only give you a simple example.

          I've used 3 fields:  Customer ID, Purchase Date, and Amount.

          A basic formula might be:

           

          YEAR(MIN([Purchase Date])) < YEAR(NOW())

           

          That works only as long as you haven't filtered out any of your data, it only does the calculation on the data present after you've filtered it.  This is something to watch out for as a new user.

           

          What I've done in the attached workbook is to use the FIXED function.

           

          {FIXED [Customer ID]: MIN([Purchase Date])}

           

          That function does the calculation using all of the data for the field(s) specified.  So, in this case, it looks at the whole list of customer IDs, ignoring any filters on that field, and chooses the minimum purchase date, then uses that in the comparison instead.

           

          Like I said, this is a simple example.  Once you start adding in other display dimensions and filters to your visualisation, it can get more complicated, but hopefully this gives you an idea.  It might be helpful for you to read about the INCLUDE and EXCLUDE functions as well.

          1 of 1 people found this helpful
          • 2. Re: Customer Segmentation
            Bowen CAI

            Hi Chris,

             

            Thanks for your prompted reply. First, I will apologise to you, I did not provide the detail description for my data source. In the data source, there are 4 key fields (Order Product ID-Unique key, no duplication; Order ID-It is a primary Key, but under one order, a customer can buy multiple products; Product ID-it is a primary key also; and OrderDate). I have tried your suggestions, but it only can assist me to filter out the new customers.

             

            But what I want to achieve is that,

            • Firstly, who are the customers keep purchases starting from the beginning 2009 to now?
            • Secondly, who are the customer they keep purchasing from 2009 to 2015 and stop purchasing in 2016
            • Lastly, I also want to know who are the customers keep purchasing from 2009, and stop purchasing for a while but they come back in 2016

            Chris Geatch

            Can you please provide your helpful advice? Thanks a lot.

             

            Regards,

            Bowen