Anyone ever pull off a 'Cohort Analysis'?

Points for any useful design suggestions on how to pull off a 'Cohort Analysis'., used to measure customer lifetime and used to make spending decisions on attracting new customers vs. retaining existing ones.

Assuming a single table with subscription_start_dt and subscription_end_dt...

For example in the below image the current day is somewhere between 10/31/13 and end of November 2013.
There were 168 new customers in October 2013.
Of those 168, 99.4% (167) were still customers as of 10/31/13
There were 161 new customers in September 2013.
Of those 161, 98.14% (158) were still customers as of 9/30/13.
Of those 161, 95.65% (154) were still customers as of 10/31/13.
So the %age numbers will only go down across the grid, as the 161 number doesn’t change, as people leave.  (assuming that people coming back will be represented as a new customer again on the line they ‘came back’.
January 2013 has ten columns as there are ten months between January 2013 and current end of month October 2013.

Cohort AnalysisRetention
Note:  Googling 'Cohort Analysis' and posting links here with no other commentary will be mercilessly mocked with child-like glee.
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Jim,

I'm actually taking a stab at something now.  I'll let you know what I come up with.
0
Brian CroweDatabase AdministratorCommented:
I tried to write something against our own Subscription table.  We have a CreationDate and ExpirationDate in place of your subscription_start_dt and subscription_end_dt respectively.

The query returns counts instead of percentages but that is fairly easy to correct.

WITH cteSubLength AS
(
	SELECT UserID, CreationDate,
		DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS CreationMonth,
		ExpirationDate,
		CASE
			WHEN ExpirationDate IS NULL THEN 99
			ELSE DATEDIFF(MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0), DATEADD(MONTH, DATEDIFF(MONTH, 0, ExpirationDate), 0))
		END AS LengthInMonths
	FROM Kepler.dbo.UserSubscription
)
SELECT CreationMonth, COUNT(UserID),
	SUM(CASE WHEN LengthInMonths >= 1 THEN 1 ELSE 0 END) AS [1],
	SUM(CASE WHEN LengthInMonths >= 2 THEN 1 ELSE 0 END) AS [2],
	SUM(CASE WHEN LengthInMonths >= 3 THEN 1 ELSE 0 END) AS [3],
	SUM(CASE WHEN LengthInMonths >= 4 THEN 1 ELSE 0 END) AS [4],
	SUM(CASE WHEN LengthInMonths >= 5 THEN 1 ELSE 0 END) AS [5],
	SUM(CASE WHEN LengthInMonths >= 6 THEN 1 ELSE 0 END) AS [6],
	SUM(CASE WHEN LengthInMonths >= 7 THEN 1 ELSE 0 END) AS [7],
	SUM(CASE WHEN LengthInMonths >= 8 THEN 1 ELSE 0 END) AS [8],
	SUM(CASE WHEN LengthInMonths >= 9 THEN 1 ELSE 0 END) AS [9],
	SUM(CASE WHEN LengthInMonths >= 10 THEN 1 ELSE 0 END) AS [10]
FROM cteSubLength
GROUP BY CreationMonth
ORDER BY CreationMonth

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
... looking ...
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
We have a winner ... Will post full T-SQL as soon as I make it pretty.  Thanks.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Bingo Bango.  I was able to take your T-SQL and make a SP out of it, then use it as a data source of an SSRS report.
Was even able to pull off the background color/border shading based on the value.
Cohort-Analysis-blank-title.jpg
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.