4 Replies Latest reply on May 15, 2017 2:12 AM by Yuriy Fal

    Retention: Customers Retained/Lost Per Month

    Caleb Das

      I'm trying to get a customer retention workbook done that considers a customer as Retained if they've made a purchase within 3 subsequent months of each purchase. This means that a customer can be considered Retained in January if they make a purchase in March but not Retained in January if their next purchase is in June.

       

      I want to see how many customers per month I can mark Retained. You can use the Orders table from the SuperStore data as the data source. Our tables are very similar.

       

      Any thoughts?

        • 1. Re: Retention: Customers Retained/Lost Per Month
          Deepak Rai

          Hi Caleb,

          Please check the screenshots and the attached. The logic is First you need to find the Minimum date across all Customers which is their First Purchase Date. Now to get to second date you need to compare First Purchase date with the Order date and set it to False so that it becomes 2nd order date. Now you have to set another Date which is the "Last Date to Consider" Retention which is First Purchase date plus 3 Months. Now, If your Order Date is less than the last date that means it is to be retained and others not to be retained.

          Hope it Helps!!!

          Thanks

          Deepak

           

          These are Calcs:

           

           

          Set 2nd Order Date  to False in Filter

           

          Lastly, Check for Retention:

           

           

          1 of 1 people found this helpful
          • 2. Re: Retention: Customers Retained/Lost Per Month
            Deepak Rai

            ONe more thing.. If you want to see retention wrt First Month of Purchase then in the second screenshot above replace MONTH (Order Date) with Month(First Purchase)

            Thanks

            Deepak

            • 3. Re: Retention: Customers Retained/Lost Per Month
              Justin Larson

              I'm going to rephrase your goal in the context of how I wrote the following calculation, and hopefully for clarity.

               

              In order to know if a customer is retained, what you are evaluating is each purchase, which will be considered either coming from a new customer or not, where "New" is someone who has not had a purchase in the previous 3 months. In this light, you must consider the most recent previous purchase for each purchase to infer if the customer is new or not. With this in mind, the only way to get a previous value is with a window calculation, and in order to get the previous date for a customer's purchase, you must have both Date and Customer on the view.

               

              Having said that, window calculations are a bit tricky to follow, so I won't blame you one bit if you have a hard time following them. It took a while to wrap my head around them.

               

              In any case, with the calculation for IsNewCustomer evaluates each purchase, with the following formula and settings:

              DATEADD('month', 3 , LOOKUP(MIN([Order Date]),-1)) //3 months after customer previous order

              <= MIN([Order Date])

              OR isnull(LOOKUP(MIN([Order Date]),-1)) //Mark first purchase as new

              lookup is the window calculation that gets the date of the customer's previous order. We then add 3 months and compare the resulting date to the smallest date in context. If the order date in context is smaller than 3 months from the previous date, you know the customer is coming back. If there was no previous date because it's their first order, lookup would return a null, and we're treating these as new customers with the ISNULL

               

              Getting this far, insofar as showing if each purchase represents a New or Retained customer is not so hard. Getting to the aggregation for showing this over time, especially as you are interested in counting distinct customers, of whom will be new in some purchases, and retained in others, is a much more challenging problem. In short, it's more of a recursion calculation that Tableau struggles modeling - whereas counting customers who are retained or new against a fixed/single range is not hard to do, but when it's calculated relative to the most recent purchase is where it gets pretty difficult. It's a well-documented struggle. If you search around for retention models in Tableau you'll see lot of "last 3 months" kinds of models that are not hard to get to.

               

              Take a look at this perhaps as a reference point: How to count distinct users on a running period

               

              Short of custom SQL and use of a date table, I have yet to see a suitably viable option for general use. Custom SQL works quite well, in fact, but is for the most part, purpose written, and a single custom dataset written to attack a problem like this isn't terribly useful for a wide array of analysis.

               

              That said, if you want to look deeper at that, reach out.

               

              Sorry this is a half-answer.

              1 of 1 people found this helpful
              • 4. Re: Retention: Customers Retained/Lost Per Month
                Yuriy Fal

                Well done, Justin.

                 

                If you don't mind, I'd like to add

                a coupla calcs to your workbook

                to aid with # Customers.

                 

                Please find the attached.

                 

                Yours,

                Yuri