0121 31 45 374
Qoute Icon

How to generate customer purchase cohorts from uCommerce data

Tim

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 Smile

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.

< Back to Blog

Liked this post? Got a suggestion? Leave a comment