1 Reply Latest reply on Jul 5, 2016 6:21 AM by Yuriy Fal

    Categorize/cohort users into days since last purchase

    Sarah Arnio

      Hi,
      I was hoping someone could confirm for me if I'm on the right track with this calculated field that I created in order to view customers by their last purchase date. I want to separate customers into 4 categories, those with a purchase less than 31 days ago, in the last 30-60 days, 60-90 days, and more than 90 days ago. I used LOD to find the customer's most recent purchase date and DATEDIFF to find the difference between today's date and the customer's last purchase date. I am getting data which looks right, I just wanted to get the community's feedback to see if I went about it the right way/the most logical/easiest way. Here is the calculated field I created:

       

      if DATEDIFF('day',{ FIXED [user id]:MAX([Paid Date (EST)])},TODAY())<31 then "Less than 31 days ago"

      ELSEIF DATEDIFF('day',{ FIXED [user id]:MAX([Paid Date (EST)])},TODAY())>30 and DATEDIFF('day',{ FIXED [user id]:MAX([Paid Date (EST)])},TODAY())<61 then "30-60 days ago"

      ELSEIF DATEDIFF('day',{ FIXED [user id]:MAX([Paid Date (EST)])},TODAY())>60 and DATEDIFF('day',{ FIXED [user id]:MAX([Paid Date (EST)])},TODAY())<91 then "61-90 days ago"

      ELSEIF DATEDIFF('day',{ FIXED [user id]:MAX([Paid Date (EST)])},TODAY())>90 then "90+ days ago"

      END

       

      Thanks!

        • 1. Re: Categorize/cohort users into days since last purchase
          Yuriy Fal

          Hi Sarah,

           

          Your calculation looks right.

          You may want to ease it a bit for reading, something like this:

           

          Put your

          { FIXED [user id] : MAX( [Paid Date (EST)] ) }

          into a distinct calculation, say [Last Paid Date].

           

          Then your base calculation would look like this:

           

          IF     DATEDIFF('day', [Last Paid Date], TODAY()) < 0 THEN 'Future Last Paid Date :-)'

          ELSEIF DATEDIFF('day', [Last Paid Date], TODAY()) < 31 THEN '0-30 days ago'

          ELSEIF DATEDIFF('day', [Last Paid Date], TODAY()) < 61 THEN '31-60 days ago'

          ELSEIF DATEDIFF('day', [Last Paid Date], TODAY()) < 91 THEN '61-90 days ago'

          ELSE   '91+ days ago'

          END

          Hope it helps a bit.

           

          Yours,

          Yuri