I am working on a project to measure customer retention over a 12 month period. My data set is very similar to the superstore data set.
Using a dynamic set based on customer id and year of order date i was able to successfully measure customer retention from year to year. Ex. if a customer made a purchase in 2011 and then made a purchase in 2012 they were retained for one period. If they made another purchase in 2013 they were retained for two periods. However, using this methodology I am only able to get an accurate retention rate at the end of each calendar year.
What i am looking for is a way to measure customer retention using a moving 12 month period. The table below has an example of the date ranges that I would like to use. I need to measure the number of customers that ordered during the original order period that also ordered during the reorder period.
|Original Order Period||Reorder Period|
|Jan 2011-Dec 2011||Jan2012-Dec 2012|
|Feb 2011-Jan 2012||Feb 2012- Jan 2013|
|March 2011- Feb 2012||March 2012- Feb 2013|
|April 2011-March 2012||April 2012- March 2013|
I am mostly interested in a one period retention rate. The products that my company sells have a long buying cycle so it is necessary to use periods of at least 12 months. Any suggestions or help that can be provided would be greatly appreciated. Please let me know if you have any questions.
It might be worthwhile to have a look through the knowledge base article on Cohort Analysis. Your cohort would be defined by users who made a purchase in a specific month.
Hopefully this will be of some help...