I have a table which lists the activity of each of my customers. For example:
John Doe, 1/1/2009
Jane Doe, 1/2009
Eric Doe, 2/2009
John Doe, 2/2009
John Doe, 3/2009
Jane Doe, 3/2009
I need to pull out a variety of reports based on attrition and am not sure if I should use VBA or SQL to get it done. One specific report I need to pull is Customer Retention: what percentage of my customers have had consecutive months of activity for 1 month, 2 months, 3 months, etc (all the way to the maximum number of months - based on the first month of activity until the present - somewhere around five years and growing).
Another report is to show which customers are new and which left in any given month. This report will simply check if the customer has activity in the previous month - if yes, they are not new, if no, they are new. Then vice versa - for each customer that has activity in last month, do they also have activity in this month - if no, they are considered "dropped" for this month.
To answer this question, please provide a working solution for the first report (not as a report - it should be either SQL or VBA). It'd be nice to have a working solution for the second as well, but I can always split that into a second question.
Please accept my sincerest thanks in advance for considering this challenge and working with me on it. I truly appreciate the hard work, time and effort all the Experts on this site put in.