I am working on creating a "Products Usage Dashboard" and am stuck at calculating the # of next visits by customers after buying a product.
My requirements are :
1. Calculation of # of unique Sale dates - When a customer purchases a product for the first time, after how many unique "Sale dates" did the customer purchase the product again?
2. # of days taken to repeat product purchase - When a customer purchases a product, after how many days did the customer purchase the same product again?
The tricky part is, in my data, there are instances where different customers buy same products multiple times - in such cases, I would like to show Average number of unique "Sale dates" after the product is purchased for the first time (# of unique sale dates after product purchased / # of customers that purchased the product).and
Average number of days taken to repeat the product purchase (# of days taken to repeat the product purchase / # of customers that purchased the product)
I attached an Excel workbook with sample data and desired output in column 'A' and column 'D' in the adjacent sheets of Excel (' # of Next Sale dates - Result - 1' and 'Repeat purchases - Result - 2'). I included Column 'B' and Column 'C' to show on how I calculated Column 'D' (Desired output)