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)
AS
(
    SELECT c.EmailAddress, c.FirstName + ' ' + c.LastName FROM uCommerce_Customer c
    
    UNION ALL
    
    SELECT a.EmailAddress, a.FirstName + ' ' + a.LastName FROM uCommerce_Address a
    
    UNION ALL
    
    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 
WHERE 
    LEN(e.EmailAddress) > 0
    AND e.EmailAddress NOT LIKE '%@uCommerce.dk'
    AND e.EmailAddress NOT LIKE '%leskil99@'

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

    -- 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