Hi,I have a database with 100,000(+) unique customer ID tied to their invoice dates (of purchasing)over a period of 3 years. I would like to understand the buying pattern of each customer by calculating the average time between their purchases (determined by their invoice dates. Sample as below:
Transaction Data:
ID Invoice Date (mm/yyyy)
AA 01/2013
AA 01/2013
BB 07/2011
BB 01/2011
BB 07/2010
BB 07/2010
BB 06/2010
BB 05/2010
BB 05/2010
BB 03/2010
CC 02/2010
CC 01/2010
CC 04/2010
CC 09/2012
CC 01/2013
DD 12/2012
EE 11/2012
EE 06/2012
EE 05/2012
FF 03/2012
FF 01/2013
… …
… …
Is there a straight forward formula where I can feed the "average time between invoice dates" into the table below (using the left column (ID) as a table of reference)?
Analysis:
ID Average Time between Invoice Dates (in days)
AA:
BB:
CC:
DD:
EE:
FF
…
Transaction Data:
ID Invoice Date (mm/yyyy)
AA 01/2013
AA 01/2013
BB 07/2011
BB 01/2011
BB 07/2010
BB 07/2010
BB 06/2010
BB 05/2010
BB 05/2010
BB 03/2010
CC 02/2010
CC 01/2010
CC 04/2010
CC 09/2012
CC 01/2013
DD 12/2012
EE 11/2012
EE 06/2012
EE 05/2012
FF 03/2012
FF 01/2013
… …
… …
Is there a straight forward formula where I can feed the "average time between invoice dates" into the table below (using the left column (ID) as a table of reference)?
Analysis:
ID Average Time between Invoice Dates (in days)
AA:
BB:
CC:
DD:
EE:
FF
…