3 Replies Latest reply on Feb 23, 2018 7:45 AM by Simon Runc

    compare every month to previous month to see if user was active

    Luna Starr

      Hello,

       

      Is it possible to compare month by month if a customer was active on a website? In the attached workbook I attach a label repeat user, inactive user and new user based on the max activity date and the previous activity date with a LoD (fixed on id). I, however, want to check month by month if the customer was active or inactive. Thus, the customer is only a repeat user when they are active in consecutative months.

       

      help would be appreciated

        • 1. Re: compare every month to previous month to see if user was active
          Simon Runc

          hi Luna,

           

          Take a look at my table ('SR' Tab)

           

           

          and let me know if it's classifying each customer/month correctly.

           

          If so I'll (try!) and explain how it all works

          2 of 2 people found this helpful
          • 2. Re: compare every month to previous month to see if user was active
            Luna Starr

            Hi Simon,

            Thanks, that's exactly what I'm looking for!  Looking through the twbx I get a sense of how it's supposed to work, but an explanation would certainly help me out.

            • 3. Re: compare every month to previous month to see if user was active
              Simon Runc

              Cool...I'll do my best!

               

              So the first thing to do was trigger a thing called 'Domain Padding'. This is a feature that can be applied to "Range Aware" pills (that is dates and bins, so called as they know their Min, Max and Increment), ans means Tableau "densifies" all the marks inbetween

               

              We do that by selecting "Show Missing Values"

               

              Show Missing Months.gif

               

              And we can use Table Calculations to access the densified marks (which don't really exist in the data). This is what [First Access Date Month - DENSE] is, it's the LoD getting the first purchase and "densifying" it so it takes the value for all marks, not just the ones with data. We can then use a combination of rules to classify the Customer/Months (I've commented out what each does)

               

               

              IF MIN([Date Month]) = MIN([First Access Date Month]) THEN 'New'

              //Simple one, it is the first month of purchase

               

              ELSEIF ISNULL([First Access Date Month - DENSE]) THEN ''

              //Blank field if it's before the first month of purchase (as we've not used the DENSE version it's NULL if before the first date of data)

               

               

              ELSEIF ZN(WINDOW_SUM(COUNT([Date Month]),-[In Active Threshold (Months)],0)) >=1

                  AND ZN(WINDOW_SUM(COUNT([Date Month]),-[In Active Threshold (Months)],0)) < [In Active Threshold (Months)]THEN 'Inactive'

              //Counts up the number of months from the current month and 3 months back (although I've set this to a parameter) to see if they have purchased before, but not more than 3 months ago

               

              ELSEIF ISNULL(MIN([Date Month])) THEN 'Lapsed'

              //after all the other checks if this is NULL then they have Lapsed (aka not used for >3 Months)

               

              ELSEIF NOT(ISNULL(LOOKUP(MIN([Date Month]),-1))) THEN 'Repeat'

              //Did they purchase the Month before

               

              ELSE '1'

              //Final Catch All, no cells should equal this, but kept in to ensure I had captured all situations (if I saw a 1, then there was a situation I'd not accounted for)

               

              END

               

              Hope that makes, some, sense!