> ## Content Index
> Fetch the complete content index at: https://blog.tsd.digital/llms.txt
> Use this file to discover other available public pages before exploring further.

# How to generate the data needed for a cohort chart- cohort analysis Part 3
- URL: https://blog.tsd.digital/how-to-generate-the-data-needed-for-a-cohort-chart-cohort-analysis-part-3/
- Published: 2012-02-22T13:15:53.000Z
- Updated: 2026-01-13T10:51:07.000Z
- Author: Tim Gaunt
- Tags: Analysis, Business, Business Start-up Advice, Client, Cohorts, Start-up Advice, The Site Doctor, #Import 2025-04-01 05:37

[Sean Ronan](http://twitter.com/seanronan?ref=blog.tsd.digital) pointed out [on twitter](https://twitter.com/seanronan/status/171938616212340736?ref=blog.tsd.digital) that "what" the columns on [www.quickcohort.com](http://www.quickcohort.com/?ref=blog.tsd.digital) are which is a good point so I thought I would fill in the gaps a little. The data for a cohort is fairly simple and can be as granular as you decide but the columns needed are:

1. First Action Date/Time
2. Most Recent Action Date/Time
3. Count of customers which have this First/Most Recent Action Date/Times

Most of the time you can strip the time part from the date/time (especially if you're looking at the data on a month basis) but the tricky part is getting the count of users within each date grouping. You can't just select min/max dates as you need the data grouped by your unique customer identifier. If you're running SQL Server 2005+ then you've got the benefit of Common Table Expressions.

For this example, I've assumed a simple order table structure which contains a Customer Reference (CustomerId) and an Order Date (OrderDate). You could however use any date and identifier which groups actions together e.g. ProfileId and LastLoginDate.

## SQL 2005 or later

```sql
WITH Actions (FirstAction, LastAction, UniqueId)
AS (
	SELECT min(dateadd(dd, datediff(dd, 0, o.[OrderDate]), 0))
		 , max(dateadd(dd, datediff(dd, 0, o.[OrderDate]), 0))
		 , o.[CustomerId]
	FROM Orders o
	GROUP BY CustomerId
)
SELECT a.[FirstAction]
	 , a.[LastAction]
	 , count(a.[UniqueId]) AS [CountOfCustomers]
FROM
	Actions a
GROUP BY a.[FirstAction]
	   , a.[LastAction]
ORDER BY a.[FirstAction]
	   , a.[LastAction]
```

Otherwise, I think you'll need to write something using temporary tables e.g.:

## Pre SQL 2005

```sql
CREATE TABLE #Actions(
	FirstAction SMALLDATETIME, 
	LastAction SMALLDATETIME, 
	UniqueId INT
)
INSERT INTO #Actions
(
  FirstAction
 ,LastAction
 ,UniqueId
)
SELECT min(dateadd(dd, datediff(dd, 0, o.[OrderDate]), 0))
	 , max(dateadd(dd, datediff(dd, 0, o.[OrderDate]), 0))
	 , o.[CustomerId]
FROM #Orders o
GROUP BY CustomerId

SELECT a.[FirstAction]
	 , a.[LastAction]
	 , count(a.[UniqueId]) AS [CountOfCustomers]
FROM
	#Actions a
GROUP BY a.[FirstAction]
	   , a.[LastAction]
ORDER BY a.[FirstAction]
	   , a.[LastAction]

DROP TABLE #Actions
```

This should then generate some data that looks like this:

```
FirstAction	LastAction	CountOfCustomers
2012-08-01	2013-01-01	375
2012-08-01	2013-08-01	6
2012-08-01	2012-12-01	9
```

Which you should just be able to drop into [www.quickcohort.com](http://www.quickcohort.com/?ref=blog.tsd.digital). I've not written a version for MySQL as I suspect someone far better at MySQL will be able to pop something together but the pre SQL 2005 script should work.