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

# Export/get all customer email addresses from uCommerce
- URL: https://blog.tsd.digital/exportget-all-customer-email-addresses-from-ucommerce/
- Published: 2016-08-19T07:27:40.000Z
- Updated: 2016-08-19T07:27:40.000Z
- Author: Tim Gaunt
- Tags: SQL, Ucommerce, Useful Script, Customers, Export, #Import 2025-04-01 05:38

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](https://trendseam.com/?ref=blog.tsd.digital) 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](https://trendseam.com/?ref=blog.tsd.digital) by installing the [uCommerce TrendSeam App](http://apps.ucommerce.net/?ref=blog.tsd.digital#!/apps/TrendSeam), we’ve even got a [Shopify TrendSeam App](https://apps.shopify.com/trendseam?ref=blog.tsd.digital) and a [nicely documented API](http://docs.trendseam.com/?ref=blog.tsd.digital) so you have no excuse not to [segment your customers](https://trendseam.com/?ref=blog.tsd.digital)!

```
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 ('example@gmail.com','someone@hotmail.com')
GROUP BY e.EmailAddress
```