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!!!
These are Calcs:
Set 2nd Order Date to False in Filter
Lastly, Check for Retention:
Retained.twbx 1.2 MB
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)
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.
Customer Retention.twbx 1.2 MB