2 Replies Latest reply on Oct 9, 2018 6:12 AM by jieqiong.xu.0

    How to fix all the lookup values and aggregate - customer attrition & reactivation over weeks

    jieqiong.xu.0

      Hi, this is a sample of my data. The "key" represents customer id. The 0/1 means if the customer is active or not (1 is active and 0 is inactive, null means there is no value for the customer).

       

      What I want to calculate is that on individual customer level, if the previous week is 0/1 and the week after is the same 0/1, it means "no change"; if previous week is 0 and week after is 1, it means "reactivation"; if previous week is 1 and week after is 0, it's "attrition".

       

      I can easily use lookup to find those value but what I want is an aggregate value, meaning if I take all the key out, I want an added up value of how many "reactivations" per week and how many "attritions" per week. I tried to use fixed but no success as fixed and lookup cannot use together. Please help if you could come up with some solutions. Thanks!

       

      Sample:

       

      #Weeks Active
      KeyWeek 0Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8
      01111111011
      02101100110
      03111
      04101101001
      0511000000
      06101101111

      e.g. What I want to have at the end is sth like this

       

      Reactivation003002111
      Attrition031030101
        • 1. Re: How to fix all the lookup values and aggregate - customer attrition & reactivation over weeks
          Michel Caissie

          Jieqiong,

           

          Check in the attached...

           

          On sheet validateData1  I compute the previous status with

          lookup(ATTR([Status]),-1)

          compute using  key,month,status  restarting every key

           

          Next I compute the changes with

          case [previousStatus]

          when 0 then

              case ATTR( [Status] )

              when 0 then 'no change'

              when 1 then 'reactivation'

              end

          when 1 then

              case ATTR( [Status] )

              when 0 then 'attrition'

              when 1 then 'no change'

              end

          end

           

          Next if we go on the sheet validateData2,  I swapped the key and month dimension  so it is easier to view the per month  changes quantities.

          Here I compute the number of attritions and reactivations with

          WINDOW_SUM( if [changes] = 'attrition' then 1 else 0 end )

          pay attention to the computing, it is now  month,key,status  restarting every month

          But since it is a nested calculation, you also have to set the computing of previousStatus, which have a different computing, with the dimensions in a different order. (in the compute using - Specific Dimensions pane, you can drag the dimensions up/down  to get the right order)

           

          Now we have the monthly values on every row. So I compute a boolean calculation returning true on the first row

          first() = 0

          compute using month,key,status  restarting every month

           

          On the finalView sheet, you need to have  Key and Status on the details shelf,  and the  Keep a single row calculation on the filter shelf , keeping the True values.

          Make sure all nested calculations have the right computing with the good dimensions order.

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: How to fix all the lookup values and aggregate - customer attrition & reactivation over weeks
            jieqiong.xu.0

            This is super helpful and exactly what I was looking for. Thank you so so much!