Purpose of this query
This SuiteQL statement pulls key opportunity fields such as sales rep, customer, probability, and expected value, then links each opportunity to the sales order that followed it. This helps teams validate pipeline accuracy, attribute bookings back to the opportunity owner, and analyze conversion from forecast to closed revenue. The query uses NextTransactionLineLink to connect the opportunity to the next transaction document and returns sales order details such as order number, booking date, and booking amount. This output is useful for pipeline hygiene, sales rep performance analysis, forecasting retrospectives, and revenue attribution dashboards.
SuiteQL
SELECT
BUILTIN.DF( opp.salesrep ) AS "Sales Rep",
BUILTIN.DF( opp.entity ) AS "Customer",
opp.title AS "Opportunity Name",
opp.probability AS "Probability",
opp.projectedtotal AS "Expected Value",
so.tranid AS "Sales Order",
so.trandate AS "Booking Date",
so.foreigntotal AS "Booking Amount"
FROM Opportunity opp
LEFT JOIN NextTransactionLineLink nxtl
ON ( nxtl.previousdoc = opp.id )
LEFT JOIN Transaction so
ON ( so.id = nxtl.nextdoc AND so.recordtype = 'salesorder' )
WHERE opp.voided = 'F'
ORDER BY opp.projectedtotal DESC;
Sample Output
How the query works
Starting from opportunities
The query selects opportunities and brings in key attributes used for forecasting and analysis: sales rep, customer, opportunity title, probability, and projected total.
Linking to downstream transactions
NextTransactionLineLink is used to connect the opportunity to the next document in the transaction flow. This is commonly how NetSuite relates an opportunity to a sales order created from it.
Returning sales order booking details
The join to Transaction filters to sales orders only (so.recordtype = ‘salesorder’) and returns sales order number, booking date, and booking amount.
Keeping unmatched opportunities
LEFT JOIN is used so opportunities still appear even if no sales order exists yet. This is helpful for identifying pipeline that has not converted.
Sorting by expected value
The results are ordered by opportunity projected total so the highest-value pipeline appears first.
Why this report is valuable
• Booking attribution: connect closed bookings back to the opportunity and rep responsible.
• Forecast accuracy: compare opportunity expected value versus actual booked amount.
• Conversion analysis: identify which opportunities have not yet resulted in a sales order.
• Pipeline hygiene: detect opportunities that should be closed out or updated based on booking outcomes.
• Excel dashboards: build pivot tables by rep, customer, probability band, and booking month.
Customization notes
• Date filtering: add a filter on so.trandate to focus on a booking period.
• Exclude opportunities without bookings: add AND so.id IS NOT NULL if you only want converted deals.
• Add opportunity status or close date: include additional Opportunity fields for better pipeline stage analysis.
• Multi-subsidiary: join subsidiary fields if you need OneWorld segmentation.
• Currency: use BUILTIN.CONSOLIDATE on so.foreigntotal if you need consolidated currency reporting across subsidiaries.