> ## 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 customer purchase cohorts from uCommerce data
- URL: https://blog.tsd.digital/how-to-generate-customer-purchase-cohorts-from-ucommerce-data/
- Published: 2012-04-06T08:02:29.000Z
- Updated: 2012-04-06T08:02:29.000Z
- Author: Tim Gaunt
- Tags: ASP.Net, Cohorts, e-commerce, Ucommerce, .Net, #Import 2025-04-01 05:37

I've had a couple of people ask how they can create customer purchase cohorts from their [uCommerce](http://www.ucommerce.dk/?ref=blog.tsd.digital) data since my last post so here's a quick script.

![Smile](https://storage.ghost.io/c/64/5b/645b11c2-b436-444c-a523-58dca4ca7bd8/content/images/tim/images/How-to-generate-customer-purchase-cohort_C59E/wlEmoticon-smile.png)

Depending on how you've setup your [uCommerce](http://www.ucommerce.dk/?ref=blog.tsd.digital) store, the customer ids might be different so instead of using customer id. I would use the email address of the customer personally as the identifier as this means you'll be able to analyse those customers who have chosen to check out anonymously 

Here's the SQL to output the data in a format suitable for [www.quickcohort.com](http://www.quickcohort.com/?ref=blog.tsd.digital).

WITH Actions (FirstAction, LastAction, UniqueId) AS ( SELECT min(dateadd(dd, datediff(dd, 0, o.CompletedDate), 0)) , max(dateadd(dd, datediff(dd, 0, o.CompletedDate), 0)) , ltrim(rtrim(LOWER(cc.EmailAddress))) FROM \[uCommerce\_PurchaseOrder\] o LEFT JOIN uCommerce\_Customer cc ON cc.CustomerId = o.CustomerId GROUP BY ltrim(rtrim(LOWER(cc.EmailAddress))) ) SELECT a.\[FirstAction\] , a.\[LastAction\] , count(a.\[UniqueId\]) AS \[CountOfCustomers\] FROM Actions a GROUP BY a.\[FirstAction\] , a.\[LastAction\] HAVING min(dateadd(dd, datediff(dd, 0, a.\[FirstAction\]), 0)) IS NOT NULL ORDER BY a.\[FirstAction\] , a.\[LastAction\] GO

Not using [uCommerce](http://www.ucommerce.dk/?ref=blog.tsd.digital) as your e-commerce provider? Let me know and I'll knock up a script for you.