1 Reply Latest reply on Jun 19, 2014 10:02 AM by Rishi Sharma

# Average Repurchase Rate with weeks after Date of First Purchase

Average Repurchase Rate with weeks after Date of First Purchase.

Searching blogs, questions and answers, it seems that most of them are just related to my problem, but never quite answer my question really. Thus thanks a million for anyone who takes the time to help!

We need to compare the “average repurchase rate” of clients, who belong to one of two groups (new, others). Repurchase rate (RPR) is calculated as the number of orders since date of his/her first orders.

Note:

1. Date of first order differ between clients.
2. It is a cumulative count since first order date; we do NOT need count in week 1, then count in week 2 etc.!

In layman terms:

1. one has to calculate (in theory)  a measure “AvgRPR” for each group like

Avg( cumulative [Count total Number of Orders  -1]) ) / [DATEDIFF (‘week’, Min([Order Date), [Order Date])]

b)      And then plot “AvgRPR”  against a [DATEDIFF (‘week’, Min([Order Date), [Order Date])] that has been converted into a dimension.

Please find attached an Excel Table that explains the problem with Sample Tables. The Table contains three Parts

1. Raw Sample Data (CustomerID, Category, OrderNumber, OrderDate, BasketValue (\$))
2. Part B should only explain which calculations might be necessary
3. Part C shows the desired result table for the count. Calculation of the Average BasketValue are not shown in this Excel sample, because I assume this will work like counting the average count of Number of Orders within a defined time interval.

Definitions:

Cumulative Count (Repurchase):

Number of orders since first order; this which is equivalent to total number of orders minus 1(i.e. first order)

Repurchase Rate: Cumulative Count (Repurchase)/ [time interval]

[time interval]: DATEDIFF (‘week’, FirstOrderDate, [Order Date])

[FirstOrderDate]: Min([Order Date])

Again, thank you very much for any advice! And apologies, if I missed blog/answers, that had solved my problem already.

• ###### 1. Re: Average Repurchase Rate with weeks after Date of First Purchase

Hi,

Were you able to find a solution to this?

I am also facing a similar problem

Referring to this link Finding the Date of First Purchase or Occurrence | Tableau Software i got some information

So with order date on column shelf, customer number on row shelf and a calc field (DATEDIFF('day',WINDOW_MIN(MIN([ORDERED_DATE])),MIN([ORDERED_DATE]))))  on the row shelf and compute using order_date for this calc field, i am able to get a gantt chart with number of days that took the customer to buy the second product.

However i am not able to get the average length of time that a customer takes to repurchase.

Thanks