The Site Doctor Blog

Footprints in the snow of a warped mind

< Back to Blog

Export/get all customer email addresses from uCommerce

If you running an online web-shop, you'll undoubtably be marketing to your customers so here’s  a simple script for you to generate a list of customers and their emails to import (excluding some of the test email addresses).

As you should be segmenting your customers I’ve added some extra information including their total spend to date, first/last order and count of orders.

If you’re using a version of uCommerce of v7 or higher then you can benefit from the power of TrendSeam by installing the uCommerce TrendSeam App, we’ve even got a Shopify TrendSeam App and a nicely documented API so you have no excuse not to segment your customers!


WITH EmailAddresses(EmailAddress, Name)
    SELECT c.EmailAddress, c.FirstName + ' ' + c.LastName FROM uCommerce_Customer c
    SELECT a.EmailAddress, a.FirstName + ' ' + a.LastName FROM uCommerce_Address a
    SELECT oa.EmailAddress, oa.FirstName + ' ' + oa.LastName FROM uCommerce_OrderAddress oa
SELECT e.EmailAddress, MAX(e.Name), COUNT(DISTINCT po.OrderId) AS [Count Of Orders], SUM(po.OrderTotal) AS [Total Spend], MIN(po.CompletedDate) AS [First Order Date], MAX(po.CompletedDate) AS [Last Order Date]AS [Last Order Date], MAX(e.Name), COUNT(DISTINCT po.OrderId) AS [Count Of Orders], SUM(po.OrderTotal) AS [Total Spend]
FROM EmailAddresses e
	LEFT join uCommerce_OrderAddress oa ON e.EmailAddress = oa.EmailAddress AND oa.AddressName = 'Billing'
	LEFT join uCommerce_PurchaseOrder po ON oa.OrderAddressId = po.BillingAddressId 
    LEN(e.EmailAddress) > 0
    AND e.EmailAddress NOT LIKE ''
    AND e.EmailAddress NOT LIKE '%[email protected]'

    -- Add as many of these exclusions as you like, for a domain you can do:
    -- AND e.EmailAddress NOT LIKE ''

    -- For an full email address you can add it as a list i.e. 
    -- AND e.EmailAddress NOT IN ('[email protected]','[email protected]')
GROUP BY e.EmailAddress
Author: Tim on

Liked this post? Got a suggestion? Leave a comment