Published on February 19, 2025.

The purchasing manager at one of my NetSuite clients (a wholesale distributor of promotional products) recently asked for a series of SuiteQL queries that they can use to analyze customer returns.

One of their goals is to identify items that are being returned the most frequently. Here's an early version of the query that I created for them.

SELECT
	Item.ID AS Item,
	Item.ItemID,
	Item.DisplayName,
	Item.Manufacturer,
	SUM( TransactionLine.Quantity ) AS QtyReturned
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
	INNER JOIN Item ON
		( Item.ID = TransactionLine.Item )
WHERE
	( Transaction.Type = 'RtnAuth' )
	AND ( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO90', 'START' ) )
	AND ( Transaction.Void = 'F' )
	AND ( Transaction.Voided = 'F' )
	AND ( Item.ItemType = 'InvtPart' )
GROUP BY
	Item.ID,
	Item.ItemID,
	Item.DisplayName,
	Item.Manufacturer
ORDER BY
	QtyReturned DESC,
	Item.ItemID

The query returns a list of items that have been returned in the past 90 days, including the internal ID, item ID, name, and manufacturer, as well as the total quantity returned. Note that in the WHERE clause, I'm using the BUILTIN.RELATIVE_RANGES function, which I discussed in this blog post.

In the query, I'm joining from the return authorization transaction, to the transaction lines, and then to the items. By joining to the item, I can return the manufacturers of the items.

The items are returned in descending order based on the total quantity returned. In other words, items that were returned the most, based on quantity, are listed first.

My client also asked for a query to identify the manufacturers of the items that are being returned the most frequently. This is the modified query that I provided to them, which shows the top 25 manufacturers, based on items returned in the past 90 days.

SELECT TOP 25
	Item.Manufacturer,
	SUM( TransactionLine.Quantity ) AS QtyReturned
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
	INNER JOIN Item ON
		( Item.ID = TransactionLine.Item )
WHERE
	( Transaction.Type = 'RtnAuth' )
	AND ( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO90', 'START' ) )
	AND ( Transaction.Void = 'F' )
	AND ( Transaction.Voided = 'F' )
	AND ( Item.ItemType = 'InvtPart' )
GROUP BY
	Item.Manufacturer
ORDER BY
	QtyReturned DESC,
	Item.Manufacturer

I hope you find the queries that I've shared in this post to be helpful.