How to generate customer purchase cohorts from uCommerce data
Friday, April 06, 2012 10:02:29 AM (GMT Daylight Time, UTC+01:00)
I've had a couple of people ask how they can create customer purchase cohorts from their uCommerce data since my last post so here's a quick script.
Depending on how you've setup your uCommerce 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.
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 as your e-commerce provider? Let me know and I'll knock up a script for you.