2 Replies Latest reply on Dec 15, 2016 5:38 AM by Cristiano Nardocci

    Customers purchasing after a # of consecutive months of not buying

    Cristiano Nardocci

      Hi Community,

       

      I'm trying to use the data in our sales db to plot by month the number of customers that are purchasing again after a certain number of consecutive months (I'd like to check 6 or 12). I attach data sample.

      Given the data structure I planned to start by pivoting the data, but then I'm not sure how to get a dimension or measure that can allow me to identify the customers that in any specific month have purchased something after let's say 6 consecutive months without buying that product.

       

      Any help would be greatly appreciated!!!!

       

      thanks!!!

        • 1. Re: Customers purchasing after a # of consecutive months of not buying
          Norbert Maijoor

          Hi Christiano

           

          Find my approach as reference below and stored in attached workbook version 9.3

           

          Upfront "Pivot" first...

           

          a. Month edit

           

          if [Month]="Jan" then 1

          elseif [Month]="Feb" then 2

          elseif [Month]="Mar" then 3

          elseif [Month]="Apr" then 4

          elseif [Month]="May" then 5

          elseif [Month]="Jun" then 6

          elseif [Month]="Jul" then 7

          elseif [Month]="Aug" then 8

          elseif [Month]="Sep" then 9

          elseif [Month]="Oct" then 10

          elseif [Month]="Nov" then 11

          elseif [Month]="Dec" then 12

          END

           

           

          b. Month value: if [Pivot Field Values]>0 then [Month edit] END

           

          c. Max Month: zn({fixed [Customer]: max([month value])})

           

          d. Consecutive Month without..: 12-[Max Month]

           

           

          • 2. Re: Customers purchasing after a # of consecutive months of not buying
            Cristiano Nardocci

            Thanks a lot Norbert!

             

            I understand the approach now to get a count of the number of months without purchasing from december. Anyway how would you then be able to have for each month the number of customers that have purchased something but had not purchased anything for the 6 months before? Ideally I'd like to have a line graph with the months on the X axis and the number of customers buying something in that month after at least 6 months without purchasing on the Y axis. Even better it'd be a bubble chart same as the line chart but with the size of the bubbles = the volume corresponding to the customers buying something in that month after at least 6 months without purchasing.

             

            Thanks for your help!!!