Published on October 1, 2023.

Over the summer, I had an interesting request from one of my NetSuite clients. Their marketing department was about to run a campaign to promote a new product, and they wanted to target existing customers in specific zip codes, as well as customers who had previously purchased a specific item. They wanted a SuiteQL query to pull the customers.

In this post, I'll walk you through the steps I took to create the query.

Customers In Specific Zip Codes

I started by creating a query to identify customers in specific zip codes, based on ship-to addresses. Here's the query.

SELECT
	Customer.LastName,
	Customer.FirstName,
	Customer.Email,
	EntityAddress.Addr1,
	EntityAddress.Addr2,
	EntityAddress.Addr3,
	EntityAddress.City,
	EntityAddress.State,
	EntityAddress.Zip
FROM
	EntityAddress
	INNER JOIN Customer ON
		( Customer.DefaultShippingAddress = EntityAddress.nKey )
WHERE
	( EntityAddress.Zip IN ( '23113', '23235', '94087' ) )
	AND ( Customer.IsInactive = 'F' )

To help optimize the query, I'm started with the EntityAddress table, and filtered it on the zip codes. I then joined to the Customer table based on the DefaultShippingAddress column. And finally, I also filtered out inactive customers.

Customers That Purchased A Specific Item

I then created a second query to identify customers that have purchased a specific item. Here's that query.

SELECT DISTINCT
	Customer.LastName,
	Customer.FirstName,
	Customer.Email,
	EntityAddress.Addr1,
	EntityAddress.Addr2,
	EntityAddress.Addr3,
	EntityAddress.City,
	EntityAddress.State,
	EntityAddress.Zip
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
	INNER JOIN Customer ON
		( Customer.ID = Transaction.Entity )
	INNER JOIN EntityAddress ON
		( EntityAddress.nKey = Customer.DefaultShippingAddress )
WHERE
	( Transaction.Type = 'SalesOrd' )	
	AND ( TransactionLine.Item = 8919 )
	AND ( Customer.IsInactive = 'F' )

For this query, I started with the Transaction table, and looked specifically for Sales Orders. I then joined to the TransactionLine table, which enabled me to add the item filter. And finally, I joined to the Customer and EntityAddress tables, in much the same way that I did in the first query.

Notice that I used SELECT DISTINCT in the query. This prevents a customer that has purchased the item multiple times from appearing multiple times in the results.

Using the UNION Operator

The final step was to put the two queries together. To do that, I used the SQL UNION operator, which combines the results of the queries and removes any duplicates. If a customer lives in one of the target zip codes, and has purchased the target item, they'll still only show up once in the results.

SELECT
	Customer.LastName,
	Customer.FirstName,
	Customer.Email,
	EntityAddress.Addr1,
	EntityAddress.Addr2,
	EntityAddress.Addr3,
	EntityAddress.City,
	EntityAddress.State,
	EntityAddress.Zip
FROM
	EntityAddress
	INNER JOIN Customer ON
		( Customer.DefaultShippingAddress = EntityAddress.nKey )
WHERE
	( EntityAddress.Zip IN ( '23113', '23235', '94087' ) )
	AND ( Customer.IsInactive = 'F' )
		
UNION

SELECT DISTINCT
	Customer.LastName,
	Customer.FirstName,
	Customer.Email,
	EntityAddress.Addr1,
	EntityAddress.Addr2,
	EntityAddress.Addr3,
	EntityAddress.City,
	EntityAddress.State,
	EntityAddress.Zip
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
	INNER JOIN Customer ON
		( Customer.ID = Transaction.Entity )
	INNER JOIN EntityAddress ON
		( EntityAddress.nKey = Customer.DefaultShippingAddress )
WHERE
	( Transaction.Type = 'SalesOrd' )	
	AND ( TransactionLine.Item = 8919 )
	AND ( Customer.IsInactive = 'F' )

Wrapping Up

This is a rather simple example of a SuiteQL query, and yet it shows just how powerful SuiteQL can be.

As always, I hope you find the queries that I've shared to be helpful.