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.
- Date of first order differ between clients.
- 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:
- 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
- Raw Sample Data (CustomerID, Category, OrderNumber, OrderDate, BasketValue ($))
- Part B should only explain which calculations might be necessary
- 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.
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.
AverageCountInTimeInteral.xlsx 14.1 KB