> Continuous Process Improvement > Order to Cash Sales Order Processing > NetSuite > Data Template

Data Template: Order to Cash - Sales Order Processing

NetSuite

Order to Cash - Sales Order Processing Attributes (21) Order to Cash - Sales Order Processing Activities (14) Extraction Guides (3)

Your Order to Cash - Sales Order Processing Data Template

This template provides a comprehensive guide to collecting the necessary data for analyzing your Order to Cash - Sales Order Processing in NetSuite. It outlines the essential attributes and activities you will need to extract from your system. Additionally, you will find practical guidance on how to extract this data efficiently, setting you up for successful process analysis.

Recommended attributes for detailed analysis
Essential activities to track in your process
Practical data extraction guidance for NetSuite

New to event logs? Learn how to create a process mining event log.

Download Template

Order to Cash - Sales Order Processing Attributes

These are the recommended data fields to include in your event log for comprehensive analysis of your Order to Cash - Sales Order Processing.

3 Required 8 Recommended 10 Optional

	Name	Description
	Activity Name ActivityName	The name of the business event or activity that occurred at a specific point in time.
Description This attribute describes a specific step or status change within the sales order lifecycle, such as 'Sales Order Created', 'Goods Shipped', or 'Payment Received'. The sequence of these activities forms the basis of the process map. Analyzing the flow of activities helps identify common process paths, deviations, and bottlenecks. Understanding the frequency and order of activities is crucial for identifying opportunities to streamline operations and reduce manual effort. Why it matters It defines the steps of the process, allowing for the visualization and analysis of the process flow. Where to get This is typically derived from system status changes, transaction types, or specific event logs within NetSuite. It often requires mapping status fields or transaction creation events to a standardized activity name. Examples Sales Order CreatedSales Order ApprovedGoods ShippedInvoice CreatedPayment Received
	Event Time EventTime	The timestamp indicating when the activity occurred.
Description This attribute provides the exact date and time for each activity in the process. It is the chronological backbone of the event log, enabling the calculation of cycle times, durations, and waiting times between different steps. Accurate timestamps are essential for performance analysis, such as measuring the time from order creation to shipment or identifying delays in the credit approval process. It allows for detailed analysis of process efficiency and adherence to service level agreements. Why it matters Timestamps are critical for calculating all time-based metrics, including cycle times and durations, which are essential for identifying process bottlenecks. Where to get This corresponds to date fields on NetSuite transaction records, such as 'Date Created' for the sales order, 'Actual Ship Date' for item fulfillments, or 'Date' for invoices and payments. Examples 2023-04-15T10:00:00Z2023-04-15T14:30:00Z2023-04-16T09:00:00Z
	Sales Order SalesOrder	The unique identifier for each sales order document.
Description The Sales Order serves as the primary case identifier, linking all subsequent activities from customer order placement through goods delivery and final payment. Each sales order represents a single instance of the end-to-end process. In process mining, this attribute is fundamental for reconstructing the journey of each order. It allows for the analysis of process variants, cycle times, and bottlenecks on a per-order basis, providing a complete view of the lifecycle for each individual customer request. Why it matters This is the core identifier that connects all related events into a single process instance, making end-to-end analysis possible. Where to get This is the internal ID of the Sales Order transaction record in NetSuite. It can typically be found as 'Internal ID' on the Sales Order form or in search results. Examples SO-100521SO-100522SO-100523
	Customer Name CustomerName	The name of the customer who placed the sales order.
Description This attribute contains the name of the legal entity or individual that purchased the goods or services. It links the sales order process to a specific customer account. Filtering or dimensioning analysis by customer is essential for understanding customer-specific behaviors, identifying issues affecting key accounts, and evaluating service levels. It allows for a customer-centric view of the process, highlighting which customers experience the most delays or rework. Why it matters Allows for segmenting the process by customer, which is key to analyzing customer satisfaction, identifying issues with key accounts, and tailoring services. Where to get This is the 'Customer' or 'Entity' field on the Sales Order transaction record in NetSuite. Examples Global Corp Inc.Innovate Solutions Ltd.Dynamic Tech
	Invoice Number InvoiceNumber	The unique identifier for the customer invoice.
Description This attribute is the reference number for the invoice document generated from the sales order. It links the sales fulfillment process to the accounts receivable process. Tracking the invoice number is important for financial reconciliation and for analyses that span from order to final payment. It provides a concrete link between the operational activities of shipping goods and the financial activity of collecting cash. Why it matters It connects the sales order to the specific financial transaction for billing, enabling a true end-to-end Order-to-Cash analysis. Where to get This is the 'Invoice #' or 'Transaction ID' of the Invoice record created from the Sales Order. Examples INV-2001INV-2002INV-2003
	Payment Terms PaymentTerms	The agreed-upon terms for payment of the invoice.
Description This attribute defines the conditions under which a customer is expected to pay for the goods or services, for example, 'Net 30' or 'Due on Receipt'. These terms are used to calculate the invoice payment due date. Analyzing by payment terms helps identify which terms are associated with late payments and allows the business to assess the financial impact of its credit policies. It is fundamental for analyzing the 'Payment Terms Adherence Rate' dashboard and understanding cash flow dynamics. Why it matters It provides the basis for calculating payment due dates and analyzing customer payment behavior and adherence rates. Where to get This is the 'Terms' field on the Sales Order or Invoice transaction record in NetSuite. Examples Net 30Net 60Due on receipt
	Product Category ProductCategory	The category of the primary product or service on the sales order.
Description This attribute classifies the items on the sales order into broader categories, such as 'Hardware', 'Software', or 'Services'. If an order contains multiple categories, a primary category may be assigned based on value or item count. Analyzing the process by product category can uncover variations in fulfillment paths. For example, services may have a much simpler fulfillment process than physical hardware, which requires picking, packing, and shipping. This segmentation is key to designing category-specific process improvements. Why it matters Segmenting the process by product category helps reveal different fulfillment paths and identify category-specific bottlenecks. Where to get This information is derived from the 'Item' records linked to the sales order lines. It may require joining with the Item master data to get the category. Examples ElectronicsSoftware LicenseConsulting Services
	Requested Delivery Date RequestedDeliveryDate	The delivery date requested by the customer.
Description This attribute captures the date on which the customer has requested to receive their goods. It serves as a key performance indicator baseline for measuring on-time delivery. This date is compared against the actual delivery date ('Goods Shipped' timestamp) to calculate the 'On-Time Delivery Rate' KPI. Analyzing the gap between requested and actual delivery dates helps identify systemic issues in forecasting, inventory management, or logistics that prevent the company from meeting customer expectations. Why it matters It's the baseline for measuring on-time delivery performance and customer satisfaction. Where to get This may correspond to a standard or custom field on the Sales Order record, often named 'Requested Delivery Date' or similar. Examples 2023-05-202023-06-012023-06-15
	Sales Order Status SalesOrderStatus	The current status of the sales order within its lifecycle.
Description This attribute indicates the current state of the sales order, such as 'Pending Approval', 'Pending Fulfillment', or 'Billed'. It provides a snapshot of where the order is in the overall process. While the activity log shows the historical flow, the current status is useful for filtering and focusing on orders that are currently stuck or active. Analyzing cases by their terminal status helps to understand process outcomes, such as whether an order was successfully closed, cancelled, or is still in progress. Why it matters It allows for filtering cases by their current state, which is crucial for analyzing open orders and identifying those that are blocked or delayed. Where to get This is the 'Status' field on the Sales Order transaction record in NetSuite. Examples Pending FulfillmentPending BillingBilledClosed
	Total Order Amount TotalOrderAmount	The total monetary value of the sales order.
Description This attribute represents the total financial value of the sales order, including all items, taxes, and shipping charges. It is a critical measure of the economic significance of each process instance. Analyzing the process based on order value can reveal important patterns. For example, high-value orders may follow a different, more manual approval process, while low-value orders may be highly automated. This analysis helps in prioritizing process improvement efforts on the most impactful orders. Why it matters Enables value-based analysis, helping to prioritize high-value orders and understand how process efficiency impacts revenue. Where to get This is the 'Total' field on the Sales Order transaction record in NetSuite. Examples 1500.00250.5012500.75
	User User	The user or employee who performed the activity.
Description This attribute identifies the individual responsible for executing a specific process step, such as the sales representative who created the order or the warehouse operator who packed the goods. It can be a user's name or a unique ID. Analyzing by user helps to understand workload distribution, identify training needs, and compare performance across individuals or teams. It is crucial for root cause analysis when investigating deviations or delays associated with specific user actions. Why it matters It enables performance analysis by employee or role, helping to identify top performers, automation candidates, and training opportunities. Where to get This information can be found in fields like 'Created By', 'Modified By', or 'Owner' on various NetSuite transaction records. Examples John SmithJane DoeWarehouse User 1
	Credit Status CreditStatus	Indicates the credit hold status of the sales order.
Description This attribute reflects the customer's credit status at the time of order processing, for example, 'On Hold' or 'Released'. It is a key factor in the early stages of the order lifecycle. Analyzing this attribute helps in understanding the impact of credit checks on overall order cycle time. The 'Credit Check Cycle Time Analysis' dashboard relies on this data to identify how many orders are placed on hold and how long it takes to release them, highlighting bottlenecks in the credit management process. Why it matters Directly impacts the 'Credit Check Cycle Time' KPI and helps explain early-stage delays in the order process. Where to get This might be a standard status field or a custom checkbox, e.g., 'Credit Hold', on the Sales Order record. It can also be inferred from the presence of 'Credit Hold Applied' and 'Credit Hold Released' activities. Examples GoodOn HoldReleased
	Is On Time Delivery IsOnTimeDelivery	A flag indicating if the order was delivered on or before the requested date.
Description This calculated boolean attribute compares the 'Goods Shipped' or actual delivery timestamp with the 'RequestedDeliveryDate'. It is true if the delivery was on time or early, and false if it was late. This flag is essential for calculating the 'On-Time Delivery Rate' KPI and for powering the 'Delivery Promise vs. Reality Gap' dashboard. It simplifies the analysis of delivery performance, allowing for easy filtering and aggregation to find the drivers of late shipments, whether they are specific products, regions, or process bottlenecks. Why it matters Provides a clear, binary outcome for delivery performance, simplifying the calculation of KPIs and root cause analysis of late orders. Where to get This is calculated during data transformation by comparing the timestamp of the 'Goods Shipped' activity with the 'RequestedDeliveryDate' attribute. Examples truefalse
	Is On Time Payment IsOnTimePayment	A flag indicating if the invoice was paid on or before the due date.
Description This calculated boolean attribute compares the 'Payment Received' timestamp with the 'PaymentDueDate'. It is true if the payment was made on or before the due date, and false otherwise. This attribute is the foundation for the 'On-Time Payment Rate' KPI and the 'Payment Terms Adherence Rate' dashboard. It provides a clear measure of customer payment behavior, enabling analysis of which customers, regions, or payment terms are most associated with late payments. Why it matters Directly measures customer payment discipline, which is critical for cash flow management and assessing credit risk. Where to get This is calculated during data transformation by comparing the timestamp of the 'Payment Received' activity with the 'PaymentDueDate' attribute. Examples truefalse
	Last Data Update LastDataUpdate	Timestamp of the last data refresh or extraction from the source system.
Description This attribute indicates when the dataset was last updated. It provides transparency to business users about the freshness of the data they are analyzing, ensuring they understand the time frame covered by the process mining dashboards and analysis. This is not used for process flow analysis but is a critical metadata element for data governance and user trust. It helps users assess the currency of the insights and know when to expect new data to be reflected. Why it matters Informs users about the timeliness of the data, which is essential for making decisions based on the analysis. Where to get This timestamp is generated and stamped onto the dataset at the time of data extraction from NetSuite. Examples 2023-10-27T02:00:00Z
	Payment Due Date PaymentDueDate	The date by which the invoice payment is due.
Description This attribute is the calculated date by which the customer must make payment, based on the invoice date and the payment terms. For instance, for an invoice dated April 1st with 'Net 30' terms, the due date would be May 1st. This date is crucial for financial analysis and is directly compared with the 'Payment Received' date to determine if a payment was on time. It is a key component for calculating the 'On-Time Payment Rate' KPI and managing accounts receivable. Why it matters This is the benchmark for measuring on-time payment performance, which is critical for managing cash flow and accounts receivable. Where to get This is the 'Due Date' field on the Invoice transaction record. It is automatically calculated by NetSuite based on the invoice date and payment terms. Examples 2023-05-302023-06-152023-07-01
	Sales Order Change Count SalesOrderChangeCount	The number of times a sales order has been changed after its initial creation.
Description This calculated metric counts the occurrences of 'Sales Order Changed' activities for each case. A high change count indicates rework, which can be caused by customer requests, data entry errors, or pricing adjustments. This attribute is a direct input for the 'Sales Order Rework Rate' KPI and the 'Sales Order Rework Variants' dashboard. Analyzing the characteristics of orders with high change counts helps to identify the root causes of rework, such as issues with specific products, customers, or sales reps. Why it matters Directly quantifies rework, helping to pinpoint sources of inefficiency, data quality issues, and process instability. Where to get This is calculated during data transformation by counting the 'Sales Order Changed' events for each 'SalesOrder' case ID. Examples 013
	Sales Order Type SalesOrderType	The classification of the sales order, such as standard, rush, or special.
Description This attribute categorizes sales orders based on their type, which often dictates the process path and priority. For example, a 'Rush Order' may bypass certain steps or have stricter SLAs compared to a 'Standard Order'. Analyzing the process by order type is critical for understanding if different process variants are intentional and effective. It helps evaluate whether special handling procedures for certain order types are actually leading to faster or better outcomes, and at what cost. Why it matters Allows for comparing different intended process paths, such as standard vs. rush orders, to see if they perform as expected. Where to get This is typically a custom 'Order Type' field on the Sales Order form, as NetSuite uses different transaction forms (e.g., Standard Sales Order, Standard Sales Order - Cash Sale) rather than a single type field by default. Examples Standard OrderRush OrderProject Order
	Sales Team SalesTeam	The sales team or group credited with the sales order.
Description This attribute identifies the team or department responsible for the sale. It is used to organize sales representatives and manage sales territories or product lines. In process mining, analyzing performance by sales team can uncover best practices from top-performing teams or identify systemic issues affecting a particular team. It can highlight differences in data entry quality, discount approvals, or other upstream factors that impact the downstream fulfillment process. Why it matters Enables performance comparison across different sales teams, helping to identify best practices or areas needing support. Where to get This may be a standard or custom field on the Sales Order record, often linked to the Sales Rep's employee record. Examples North America SalesEMEA EnterpriseAPAC Channel
	Shipping Country ShippingCountry	The destination country for the shipment.
Description This attribute contains the country to which the order's goods are being shipped. It is derived from the shipping address associated with the sales order. Geographical analysis based on the shipping country can reveal variations in process performance due to logistics, customs, or regional office efficiency. It allows for comparing shipping times, delivery accuracy, and process costs across different countries or regions. Why it matters Enables geographical analysis to identify regional bottlenecks, compare logistics performance, and understand international complexities. Where to get This is part of the 'Shipping Address' on the Sales Order transaction record. Examples USAGermanyJapan
	Source System SourceSystem	Identifies the system from which the data originated.
Description This attribute specifies the source application where the event data was generated. For this process, it will typically be 'NetSuite'. In more complex environments, it can help differentiate data coming from various integrated systems, such as a separate CRM or WMS. In analysis, it helps confirm data lineage and is crucial when blending data from multiple sources to create a single, unified process view. It ensures that data is correctly attributed to its origin, which is important for data governance and troubleshooting. Why it matters It provides crucial context about data origin, especially in environments where multiple systems are integrated. Where to get This is a static value ('NetSuite') added during the data extraction and transformation process. Examples NetSuite

Required Recommended Optional

Order to Cash - Sales Order Processing Activities

These are the key process steps and milestones to capture in your event log for accurate process discovery and analysis.

7 Recommended 7 Optional

	Activity	Description
	Goods Shipped	This milestone marks the moment items have left the warehouse and are on their way to the customer. This is inferred when the status of the 'Item Fulfillment' record is updated to 'Shipped'.
Why it matters Shipping is a critical handoff point to the carrier and customer. This event is vital for tracking on-time delivery performance and measuring the total order fulfillment lead time. Where to get Inferred from the 'Status' field on the linked 'Item Fulfillment' transaction changing to 'Shipped'. The timestamp for this event is the date of this status change. Capture Timestamp of status change to 'Shipped' on the Item Fulfillment record. Event type inferred
	Invoice Created	This represents the creation of the financial invoice for the shipped goods or services. It is an explicit event triggered by the creation of an 'Invoice' transaction linked to the sales order.
Why it matters This activity is a crucial milestone for revenue recognition and marks the start of the payment cycle. The time between shipment and invoicing directly impacts cash flow. Where to get This is an explicit event captured from the 'Date Created' field on the Invoice transaction record (Transaction table, Type='CustInvc'), linked back to the Sales Order. Capture Use the transaction creation date for the linked Invoice. Event type explicit
	Order Fulfillment Created	This activity marks the start of the physical fulfillment process in the warehouse. It occurs when an 'Item Fulfillment' transaction is generated from the Sales Order.
Why it matters This is a key milestone linking the sales process to warehouse operations. The time between order approval and fulfillment creation is a key indicator of operational readiness. Where to get This is an explicit event. The timestamp is the creation date of the 'Item Fulfillment' record, which is linked back to the source Sales Order. Capture Use the transaction creation date for the linked Item Fulfillment record. Event type explicit
	Payment Received	This activity marks the receipt of payment from the customer against an invoice. It is captured when a 'Customer Payment' transaction is created and applied to the invoice associated with the sales order.
Why it matters As a key terminal event, this activity is vital for analyzing the cash conversion cycle and on-time payment performance. It signifies the successful financial conclusion of a sales transaction. Where to get This is an explicit event. The timestamp is the creation date of the 'Customer Payment' transaction (Transaction table, Type='CustPymt') applied to the relevant invoice. Capture Use the transaction date from the Customer Payment record applied to the Invoice. Event type explicit
	Sales Order Approved	This milestone signifies that the sales order has passed all internal checks, such as credit and inventory, and is ready for fulfillment. This is usually inferred from the order status changing to 'Pending Fulfillment'.
Why it matters Approval is a critical gateway in the process. Measuring the time to approval helps identify delays in internal reviews and decision-making. Where to get Inferred from a status change on the Sales Order record. The timestamp is captured when the 'Order Status' field is updated to 'Pending Fulfillment' or a similar custom approved status. Capture Timestamp of status change to 'Pending Fulfillment' in the Sales Order's system notes. Event type inferred
	Sales Order Closed	This is the final activity, indicating that the sales order is fully fulfilled, invoiced, and considered complete. It is inferred from the sales order's status changing to 'Closed'.
Why it matters This event signifies the operational end of the order lifecycle. The time from creation to closure provides a complete view of the end-to-end process duration. Where to get Inferred from the 'Status' field on the Sales Order transaction changing to 'Closed'. The timestamp is retrieved from the system notes for this final status change. Capture Timestamp of status change to 'Closed' in the Sales Order's system notes. Event type inferred
	Sales Order Created	This activity marks the official start of the sales order process. It occurs when a new sales order transaction is saved in NetSuite for the first time, capturing the initial customer request.
Why it matters This is the primary start event for the Order to Cash process. Analyzing the time from this event to subsequent activities is crucial for measuring overall order processing efficiency and cycle times. Where to get This is an explicit event captured from the 'Date Created' field on the Sales Order transaction record (Transaction table, Type='SalesOrd'). Capture Use the transaction creation date for Sales Orders. Event type explicit
	Credit Hold Applied	This event occurs when a sales order is automatically or manually placed on credit hold, pausing the fulfillment process. This is typically inferred from the order status changing to a 'Pending Approval' or a specific 'Credit Hold' state.
Why it matters Identifying when and why orders are put on hold is key to understanding delays in the fulfillment cycle. This activity highlights bottlenecks related to customer credit issues. Where to get Inferred from the system notes or audit trail on the Sales Order record, specifically looking for changes to the 'Order Status' field to a hold status. Capture Identify timestamp when order status changes to a credit hold state. Event type inferred
	Credit Hold Released	Represents the point at which a sales order is released from a credit hold, allowing it to proceed to fulfillment. This is captured by observing a status change from a hold status to an open or approved status.
Why it matters The duration of credit holds is a critical KPI. This event allows for measuring the time taken to resolve credit issues and its impact on the overall order-to-cash cycle. Where to get Inferred from the system notes or audit trail on the Sales Order record, capturing the timestamp when the 'Order Status' changes from a hold status. Capture Identify timestamp when order status changes from a credit hold state to an active state. Event type inferred
	Credit Memo Created	This event occurs when a credit memo is issued against a sales order or invoice, typically for returns, price adjustments, or other concessions. It is captured when a 'Credit Memo' transaction is created.
Why it matters Credit memos often represent process failures like shipping errors or product defects. Analyzing their frequency and timing helps identify root causes and improve overall quality. Where to get This is an explicit event based on the creation of a 'Credit Memo' transaction (Transaction table, Type='CredMemo'), which can be linked back to the original invoice or sales order. Capture Use the transaction creation date for the linked Credit Memo. Event type explicit
	Goods Packed	Signifies that the picked items have been packed and are ready for shipment. This is captured by tracking the status change on the 'Item Fulfillment' record to 'Packed'.
Why it matters This activity helps measure the efficiency of the packing station. The duration between picking and packing can reveal capacity constraints or process inefficiencies. Where to get Inferred from the 'Status' field on the linked 'Item Fulfillment' transaction changing to 'Packed'. The timestamp is recorded from the system notes for this update. Capture Timestamp of status change to 'Packed' on the Item Fulfillment record. Event type inferred
	Goods Picked	Indicates that the items for the order have been picked from their warehouse locations. This is an inferred event based on the status change of the associated 'Item Fulfillment' record.
Why it matters Analyzing the time taken to pick goods is essential for optimizing warehouse efficiency. This activity helps measure and identify bottlenecks in the picking process. Where to get Inferred from the 'Status' field on the linked 'Item Fulfillment' transaction changing to 'Picked'. The timestamp is taken from the system notes for this status change. Capture Timestamp of status change to 'Picked' on the Item Fulfillment record. Event type inferred
	Inventory Committed	This event marks when inventory is officially reserved for a sales order, ensuring it is available for fulfillment. This is inferred by observing changes to the 'Committed Quantity' on the sales order lines.
Why it matters This activity is crucial for analyzing inventory allocation efficiency. Delays between order approval and inventory commitment can lead to stockouts and impact delivery promises. Where to get Inferred from the system notes for the Sales Order's line items. The timestamp corresponds to when the 'Quantity Committed' field is updated from zero to a positive value. Capture Timestamp of change in the 'Quantity Committed' field on order lines. Event type inferred
	Sales Order Changed	This activity captures any significant modification to a sales order after it has been created, such as changes to quantity, items, or pricing. It is captured by tracking updates in the system notes or audit trail.
Why it matters Frequent changes can indicate data entry errors or unstable customer demand, leading to rework and process inefficiencies. Tracking these changes helps identify the root causes of order modifications. Where to get Derived from the system notes or audit trail associated with the Sales Order transaction. Each logged change to a relevant field can be treated as an instance of this activity. Capture Identify field changes in the Sales Order system notes after initial creation. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from NetSuite

Steps

Login and Navigate: Log into your NetSuite account with permissions to access transaction records and create saved searches.
Access Saved Searches: Navigate to the Saved Search creation page via Reports > New Search. Select Transaction as the search type.
Create Sales Order Events Search: Build the first search to capture events on the Sales Order record itself. Under the Criteria tab, set Type to Sales Order and Main Line to True. Add a Date Created filter for the desired time frame. Under the Results tab, add the columns and formulas specified in the query section below for 'Saved Search 1'. Save this search as ProcessMind_SalesOrder_Events.
Create Fulfillment Events Search: Create a new Transaction search. Under Criteria, set Type to Item Fulfillment and Main Line to True. Use Created From : Date Created to filter for the same date range as the first search. Under Results, add the required columns, making sure to pull the SalesOrder ID from the Created From... fields. Use the formulas specified in 'Saved Search 2' below. Save this search as ProcessMind_Fulfillment_Events.
Create Billing Events Search: Create a third Transaction search. Under Criteria, set Type to Invoice and Credit Memo. Set Main Line to True. Filter by Date Created. Under Results, add the required columns as specified in 'Saved Search 3' below, pulling the SalesOrder ID from the Created From... fields. Save this search as ProcessMind_Billing_Events.
Create Payment Events Search: Create the final Transaction search. Under Criteria, set Type to Customer Payment and Main Line to True. Filter by Date Created. Under Results, add columns as specified in 'Saved Search 4'. Linking back to the Sales Order requires joining through the invoice. Save this search as ProcessMind_Payment_Events.
Run and Export Searches: Run each of the four saved searches individually. From the results page, click the Export - CSV icon to download the data.
Combine CSV Files: Use a spreadsheet tool like Microsoft Excel or a scripting language like Python to open the four CSV files. Append the rows from the fulfillment, billing, and payment files to the sales order events file. Ensure the column headers are identical across all files before combining.
Finalize the Event Log: Review the combined file. Save the final, consolidated data as a single CSV file. This file is the event log ready for upload.

Configuration

Permissions: A user role with view access to Sales Orders, Item Fulfillments, Invoices, Customer Payments, and System Notes is required. Permission to create and run 'Transaction' type saved searches is also necessary.
Saved Search Definitions: This method requires creating four separate Saved Searches (for Sales Order, Fulfillment, Billing, and Payment events) that are later combined externally. All searches are of the Transaction type.
Date Filtering: It is critical to apply the same date range filter across all searches to ensure a complete dataset. Filter on the Date Created field of the primary transaction (e.g., Sales Order Date Created for the first search, Created From : Date Created for the fulfillment search).
Main Line Criteria: Use the Main Line filter set to True in the search criteria. This ensures you get one event per transaction record rather than one per line item, simplifying the event log.
Formulas for Activities: The ActivityName and EventTime columns are generated using Formula (Text) and Formula (DateTime) fields with CASE statements. These formulas must be adjusted to match your company's specific custom fields or status values (e.g., for credit holds).
Scheduled Exports: To automate the process, configure each saved search to email the results on a schedule. Navigate to the Email subtab within the saved search definition, check Send Email Alerts When Records are Created/Updated, and define the schedule and recipients.

a Sample Query config

-- This configuration describes the settings for four separate NetSuite Saved Searches.
-- The CSV results must be combined externally into a single file.

-- =========================================================================
-- Saved Search 1: ProcessMind_SalesOrder_Events
-- =========================================================================

-- Type: Transaction

-- Criteria:
--   Type = is "Sales Order"
--   Main Line = is true
--   System Notes : Field = is any of "Status", "[Your Custom Field for Credit Hold]"
--   Date Created = is within [Specify Date Range]

-- Results (Columns):
--   1. Formula (Text): {tranid} -> Header: SalesOrder
--   2. Formula (Text): CASE WHEN {systemnotes.type} = 'Create' THEN 'Sales Order Created' WHEN {systemnotes.field} = 'Status' AND {systemnotes.newvalue} like 'Pending Fulfillment%' THEN 'Sales Order Approved' WHEN {systemnotes.field} = 'Status' AND {systemnotes.newvalue} like '%Credit Hold%' THEN 'Credit Hold Applied' WHEN {systemnotes.field} = 'Status' AND {systemnotes.oldvalue} like '%Credit Hold%' THEN 'Credit Hold Released' WHEN {systemnotes.field} = 'Status' AND {systemnotes.newvalue} like 'Closed' THEN 'Sales Order Closed' WHEN {systemnotes.type} = 'Change' THEN 'Sales Order Changed' ELSE NULL END -> Header: ActivityName
--   3. Formula (DateTime): {systemnotes.date} -> Header: EventTime
--   4. System Notes : Set By -> Header: User
--   5. Name -> Header: CustomerName
--   6. Amount -> Header: TotalOrderAmount
--   7. Status -> Header: SalesOrderStatus
--   8. [Your Formula for Product Category] -> Header: ProductCategory
--   9. Ship Date -> Header: RequestedDeliveryDate
--   10. Terms -> Header: PaymentTerms
--   11. Created From : Document Number -> Header: InvoiceNumber

-- =========================================================================
-- Saved Search 2: ProcessMind_Fulfillment_Events
-- =========================================================================

-- Type: Transaction

-- Criteria:
--   Type = is "Item Fulfillment"
--   Main Line = is true
--   Created From : Date Created = is within [Specify Date Range]
--   System Notes : Field = is "Status"

-- Results (Columns):
--   1. Formula (Text): {createdfrom.tranid} -> Header: SalesOrder
--   2. Formula (Text): CASE WHEN {systemnotes.type} = 'Create' THEN 'Order Fulfillment Created' WHEN {systemnotes.newvalue} like 'Picked' THEN 'Goods Picked' WHEN {systemnotes.newvalue} like 'Packed' THEN 'Goods Packed' WHEN {systemnotes.newvalue} like 'Shipped' THEN 'Goods Shipped' ELSE NULL END -> Header: ActivityName
--   3. Formula (DateTime): {systemnotes.date} -> Header: EventTime
--   4. System Notes : Set By -> Header: User
--   5. Created From : Name -> Header: CustomerName
--   6. Created From : Amount -> Header: TotalOrderAmount
--   7. Created From : Status -> Header: SalesOrderStatus
--   8. [Your Formula for Product Category] -> Header: ProductCategory
--   9. Created From : Ship Date -> Header: RequestedDeliveryDate
--   10. Created From : Terms -> Header: PaymentTerms
--   11. NULL -> Header: InvoiceNumber

-- =========================================================================
-- Saved Search 3: ProcessMind_Billing_Events
-- =========================================================================

-- Type: Transaction

-- Criteria:
--   Type = is any of "Invoice", "Credit Memo"
--   Main Line = is true
--   Date Created = is within [Specify Date Range]

-- Results (Columns):
--   1. Formula (Text): {createdfrom.tranid} -> Header: SalesOrder
--   2. Formula (Text): CASE WHEN {type} = 'Invoice' THEN 'Invoice Created' WHEN {type} = 'Credit Memo' THEN 'Credit Memo Created' ELSE NULL END -> Header: ActivityName
--   3. Formula (DateTime): {datecreated} -> Header: EventTime
--   4. Created By -> Header: User
--   5. Name -> Header: CustomerName
--   6. Created From : Amount -> Header: TotalOrderAmount
--   7. Created From : Status -> Header: SalesOrderStatus
--   8. [Your Formula for Product Category] -> Header: ProductCategory
--   9. Created From : Ship Date -> Header: RequestedDeliveryDate
--   10. Terms -> Header: PaymentTerms
--   11. Document Number -> Header: InvoiceNumber

-- =========================================================================
-- Saved Search 4: ProcessMind_Payment_Events
-- =========================================================================

-- Type: Transaction

-- Criteria:
--   Type = is "Customer Payment"
--   Main Line = is true
--   Date Created = is within [Specify Date Range]

-- Results (Columns):
--   1. Formula (Text): {appliedtotransaction.createdfrom} -> Header: SalesOrder
--   2. Formula (Text): 'Payment Received' -> Header: ActivityName
--   3. Formula (DateTime): {datecreated} -> Header: EventTime
--   4. Created By -> Header: User
--   5. Customer : Company Name -> Header: CustomerName
--   6. Applied To Transaction : Created From : Amount -> Header: TotalOrderAmount
--   7. Applied To Transaction : Created From : Status -> Header: SalesOrderStatus
--   8. [Your Formula for Product Category] -> Header: ProductCategory
--   9. Applied To Transaction : Created From : Ship Date -> Header: RequestedDeliveryDate
--   10. Applied To Transaction : Terms -> Header: PaymentTerms
--   11. Applied To Transaction : Document Number -> Header: InvoiceNumber

Steps

Enable SuiteAnalytics Connect: Log in to NetSuite as an administrator. Navigate to Setup > Company > Enable Features. On the Analytics subtab, ensure the SuiteAnalytics Connect checkbox is marked. Save your changes.
Download and Install the ODBC Driver: In NetSuite, locate the "Set Up SuiteAnalytics Connect" link on the main dashboard in the Settings portlet. Download the appropriate ODBC driver for your operating system (Windows, macOS, or Linux) and install it on the machine you will use to run the SQL query.
Configure the ODBC Data Source (DSN): Open the ODBC Data Source Administrator on your machine. Add a new User DSN or System DSN using the NetSuite driver you installed. You will need to enter your Service Host, Port, Data Source, Account ID, and Role ID, which are all provided on the "Set Up SuiteAnalytics Connect" page in NetSuite.
Authenticate and Connect: Use a SQL client tool (such as DBeaver, Microsoft Power BI, or a Python script with the pyodbc library) to connect to the DSN you configured. You will be prompted to enter your NetSuite username and password.
Prepare the SQL Query: Copy the complete SQL query provided in the query section of this document into your SQL client's editor.
Customize Query Parameters: Locate the WHERE T.Date_Created >= '[START_DATE_YYYY-MM-DD]' clause at the beginning of the query. Replace the placeholder [START_DATE_YYYY-MM-DD] with the actual start date for the data extraction period.
Execute the Query: Run the customized SQL query against the NetSuite database. Depending on the date range and data volume, this may take several minutes to complete.
Review the Results: The query will return a single, unified table representing the event log. Each row corresponds to a specific activity in the sales order lifecycle. Check for any errors and ensure the columns (SalesOrder, ActivityName, EventTime, etc.) are populated as expected.
Export the Event Log: Export the query results from your SQL client to a CSV file. Ensure the file encoding is UTF-8 and that the column headers match the attribute names required by ProcessMind.
Upload to ProcessMind: The resulting CSV file is now ready to be uploaded directly into ProcessMind for process analysis.

Configuration

Prerequisites: Access to a NetSuite account with the SuiteAnalytics Connect feature enabled is required. The user connecting via ODBC needs a role with sufficient permissions to view transaction records, system notes, entity data, and employee information.
ODBC Driver: The correct NetSuite ODBC driver must be installed and configured on the client machine. The connection details (Account ID, Role ID, Server) are found on the SuiteAnalytics Connect setup page within NetSuite.
Date Range Filtering: The primary performance and data scoping control is the WHERE T.Date_Created >= '[START_DATE_YYYY-MM-DD]' clause in the initial Common Table Expression (CTE). It is critical to set a specific start date to avoid querying the entire history, which can be extremely slow. We recommend starting with a 3 to 6 month period.
Transaction Types: The query explicitly filters for standard NetSuite transaction types such as 'SalesOrd', 'ItemShip', 'CustInvc', 'CustCred', and 'CustPymt'. If your organization uses custom transaction types for the order-to-cash process, you will need to add them to the relevant WHERE clauses.
System Notes: Many key activities, such as status changes and holds, are derived from the System_Notes table. It's essential that auditing is enabled for the 'Status' field on transaction records for these events to be captured accurately.
Performance Considerations: The query joins several large tables, including Transaction and System_Notes. For NetSuite accounts with high transaction volumes, this query can be resource-intensive. It is advisable to run it during off-peak hours.

a Sample Query sql

WITH SalesOrders AS (
    SELECT
        T.Transaction_ID,
        T.TranID AS SalesOrder_Number,
        T.Foreign_Total AS Total_Amount,
        T.Status AS Current_Status,
        T.Ship_Date AS Requested_Delivery_Date,
        CUST.Company_Name AS Customer_Name,
        TERMS.Term_Name AS Payment_Terms,
        (SELECT LIST_AGG(IT.Display_Name, ', ') FROM Transaction_Lines TL JOIN Item IT ON TL.Item_ID = IT.Item_ID WHERE TL.Transaction_ID = T.Transaction_ID) AS Product_Category_List -- Simplified product category
    FROM
        "Transaction" T
    JOIN
        Entity CUST ON T.Entity_ID = CUST.Entity_ID
    LEFT JOIN
        Terms ON T.Terms_ID = TERMS.Terms_ID
    WHERE
        T.Type = 'SalesOrd'
        AND T.Date_Created >= TO_TIMESTAMP_NTZ('[START_DATE_YYYY-MM-DD]', 'YYYY-MM-DD')
)
-- 1. Sales Order Created
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    'Sales Order Created' AS "ActivityName",
    T.Date_Created AS "EventTime",
    E.First_Name || ' ' || E.Last_Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    T.Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    "Transaction" T
JOIN
    SalesOrders SO ON T.Transaction_ID = SO.Transaction_ID
LEFT JOIN
    Employee E ON T.Created_By_ID = E.Employee_ID

UNION ALL

-- 2, 3, 4, 14. Status Changes (Credit Hold, Released, Approved, Closed)
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    CASE
        WHEN SN.New_Value LIKE '%Pending Approv%' OR SN.New_Value LIKE '%Credit Hold%' THEN 'Credit Hold Applied'
        WHEN SN.Old_Value LIKE '%Pending Approv%' AND SN.New_Value LIKE '%Pending Fulfill%' THEN 'Credit Hold Released'
        WHEN SN.New_Value LIKE '%Pending Fulfill%' THEN 'Sales Order Approved'
        WHEN SN.New_Value LIKE '%Closed%' OR SN.New_Value LIKE '%Billed%' THEN 'Sales Order Closed'
    END AS "ActivityName",
    SN.Date AS "EventTime",
    SN.Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SN.New_Value AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    System_Notes SN
JOIN
    SalesOrders SO ON SN.Transaction_ID = SO.Transaction_ID
WHERE
    SN.Field = 'TRANSACTION.STATUS' AND "ActivityName" IS NOT NULL

UNION ALL

-- 5. Sales Order Changed
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    'Sales Order Changed' AS "ActivityName",
    SN.Date AS "EventTime",
    SN.Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    System_Notes SN
JOIN
    SalesOrders SO ON SN.Transaction_ID = SO.Transaction_ID
WHERE 
    SN.Record_Type_ID = -30 -- Sales Order Transaction Type ID
    AND SN.Field NOT IN ('TRANSACTION.STATUS', 'TRANSACTION.MEMO') -- Exclude simple status or memo changes
    AND SN.Date > (SELECT T.Date_Created FROM "Transaction" T WHERE T.Transaction_ID = SO.Transaction_ID)

UNION ALL

-- 6. Inventory Committed
SELECT DISTINCT
    SO.SalesOrder_Number AS "SalesOrder",
    'Inventory Committed' AS "ActivityName",
    SN.Date AS "EventTime",
    SN.Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    System_Notes SN
JOIN
    Transaction_Lines TL ON SN.Transaction_Line_ID = TL.Transaction_Line_ID AND SN.Transaction_ID = TL.Transaction_ID
JOIN
    SalesOrders SO ON TL.Transaction_ID = SO.Transaction_ID
WHERE
    SN.Field = 'TRANSACTION_LINES.QUANTITY_COMMITTED'
    AND SN.New_Value > 0 AND (SN.Old_Value = 0 OR SN.Old_Value IS NULL)

UNION ALL

-- 7. Order Fulfillment Created
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    'Order Fulfillment Created' AS "ActivityName",
    IFF.Date_Created AS "EventTime",
    E.First_Name || ' ' || E.Last_Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    "Transaction" IFF
JOIN
    Transaction_Links TL ON IFF.Transaction_ID = TL.Transaction_ID
JOIN
    SalesOrders SO ON TL.Previous_Transaction_ID = SO.Transaction_ID
LEFT JOIN
    Employee E ON IFF.Created_By_ID = E.Employee_ID
WHERE
    IFF.Type = 'ItemShip'

UNION ALL

-- 8, 9, 10. Goods Picked, Packed, Shipped
SELECT 
    SO.SalesOrder_Number AS "SalesOrder",
    CASE 
        WHEN SN.New_Value = 'Picked' THEN 'Goods Picked'
        WHEN SN.New_Value = 'Packed' THEN 'Goods Packed'
        WHEN SN.New_Value = 'Shipped' THEN 'Goods Shipped'
    END AS "ActivityName",
    SN.Date AS "EventTime",
    SN.Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM 
    System_Notes SN
JOIN 
    "Transaction" IFF ON SN.Transaction_ID = IFF.Transaction_ID
JOIN 
    Transaction_Links TL ON IFF.Transaction_ID = TL.Transaction_ID
JOIN 
    SalesOrders SO ON TL.Previous_Transaction_ID = SO.Transaction_ID
WHERE 
    IFF.Type = 'ItemShip'
    AND SN.Field = 'TRANSACTION.STATUS'
    AND "ActivityName" IS NOT NULL

UNION ALL

-- 11. Invoice Created
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    'Invoice Created' AS "ActivityName",
    INV.Date_Created AS "EventTime",
    E.First_Name || ' ' || E.Last_Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    INV.TranID AS "InvoiceNumber"
FROM
    "Transaction" INV
JOIN
    Transaction_Links TL ON INV.Transaction_ID = TL.Transaction_ID
JOIN
    SalesOrders SO ON TL.Previous_Transaction_ID = SO.Transaction_ID
LEFT JOIN
    Employee E ON INV.Created_By_ID = E.Employee_ID
WHERE
    INV.Type = 'CustInvc'

UNION ALL

-- 12. Credit Memo Created
SELECT
    SO.SalesOrder_Number AS "SalesOrder",
    'Credit Memo Created' AS "ActivityName",
    CM.Date_Created AS "EventTime",
    E.First_Name || ' ' || E.Last_Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    NULL AS "InvoiceNumber"
FROM
    "Transaction" CM
JOIN
    Transaction_Links TL ON CM.Transaction_ID = TL.Transaction_ID
JOIN
    SalesOrders SO ON TL.Previous_Transaction_ID = SO.Transaction_ID
LEFT JOIN
    Employee E ON CM.Created_By_ID = E.Employee_ID
WHERE
    CM.Type = 'CustCred'

UNION ALL

-- 13. Payment Received
SELECT DISTINCT
    SO.SalesOrder_Number AS "SalesOrder",
    'Payment Received' AS "ActivityName",
    PAY.Date_Created AS "EventTime",
    E.First_Name || ' ' || E.Last_Name AS "User",
    SO.Customer_Name AS "CustomerName",
    SO.Total_Amount AS "TotalOrderAmount",
    SO.Current_Status AS "SalesOrderStatus",
    SO.Product_Category_List AS "ProductCategory",
    SO.Requested_Delivery_Date AS "RequestedDeliveryDate",
    SO.Payment_Terms AS "PaymentTerms",
    INV.TranID AS "InvoiceNumber"
FROM 
    "Transaction" PAY
JOIN 
    Transaction_Links PAY_LINK ON PAY.Transaction_ID = PAY_LINK.Transaction_ID -- Payment to Invoice link
JOIN 
    "Transaction" INV ON PAY_LINK.Previous_Transaction_ID = INV.Transaction_ID AND INV.Type = 'CustInvc'
JOIN 
    Transaction_Links INV_LINK ON INV.Transaction_ID = INV_LINK.Transaction_ID -- Invoice to SO link
JOIN 
    SalesOrders SO ON INV_LINK.Previous_Transaction_ID = SO.Transaction_ID
LEFT JOIN
    Employee E ON PAY.Created_By_ID = E.Employee_ID
WHERE 
    PAY.Type = 'CustPymt'
ORDER BY "SalesOrder", "EventTime";

Steps

Prerequisite: Configure NetSuite for API Access a. Navigate to Setup > Integration > Manage Integrations > New. b. Create a new Integration record. Enable Token-Based Authentication. Note the generated Consumer Key and Consumer Secret. c. Navigate to Setup > Users/Roles > Manage Roles > New. d. Create a new Role with permissions for Transactions (Sales Order, Item Fulfillment, Invoice, etc.), Lists (Customers, Employees), Setup (Log in using Access Tokens), and SuiteAnalytics Connect. Assign this role to a specific user. e. Navigate to Setup > Users/Roles > Access Tokens > New. f. Create a new Access Token, selecting the Integration record, User, and Role created above. Note the generated Token ID and Token Secret. g. Find your Account ID by navigating to Setup > Integration > SOAP Web Services Preferences. It will be listed as Account ID.
Set up Local Python Environment a. Ensure you have Python 3.6+ installed. b. Open your terminal or command prompt and install the required libraries: pip install requests requests_oauthlib pandas.
Create the Python Extraction Script a. Create a new Python file (e.g., extract_netsuite_otc.py). b. Copy the complete Python script from the query section below into this file.
Configure Script Parameters a. Open the script and locate the placeholder section at the top. b. Fill in your NetSuite ACCOUNT_ID, CONSUMER_KEY, CONSUMER_SECRET, TOKEN_ID, and TOKEN_SECRET obtained in Step 1. c. Set the START_DATE and END_DATE to define the extraction period. A format of 'YYYY-MM-DD' is expected.
Execute the Extraction Script a. Run the script from your terminal: python extract_netsuite_otc.py. b. The script will connect to the NetSuite REST API, execute the SuiteQL query, and handle pagination to retrieve all the data. c. You will see progress messages printed to the console indicating which page of data is being fetched.
Review the Raw Data a. Once the script finishes, it will generate a file named netsuite_otc_event_log.csv in the same directory.
Data Transformation and Preparation a. The script automatically handles the required data transformations, mapping query results to the specified column names: SalesOrder, ActivityName, EventTime, User, CustomerName, etc. b. The script ensures the EventTime column is in a standard timestamp format.
Finalize for ProcessMind Upload a. The output netsuite_otc_event_log.csv is generated in the correct format. b. Verify the column headers match the required and recommended attributes. c. The file is now ready to be uploaded to ProcessMind for analysis.

Configuration

Authentication Credentials: You must provide the NetSuite Account ID, Consumer Key, Consumer Secret, Token ID, and Token Secret in the Python script. These are generated from the Integration and Access Token records in NetSuite.
API Endpoint: The script targets the NetSuite SuiteQL query endpoint: [your_account_specific_domain]/services/rest/query/v1/suiteql.
Date Range: The script requires a START_DATE and END_DATE to be set. For initial analysis, a period of 3-6 months is recommended to balance data volume with process insights. For large instances, start with a shorter period (e.g., 1 month) to gauge performance.
Subsidiary Filter: The provided SuiteQL query includes a placeholder /* AND t.subsidiary = '[Your Subsidiary ID]' */. If you operate in a multi-subsidiary environment, uncomment and update this line in each SELECT statement to filter for a specific subsidiary.
Performance: The extraction fetches data in pages (default 1000 records per page) to manage memory usage and comply with API limits. For very large datasets spanning multiple years, the extraction can be time-consuming due to API rate limiting enforced by NetSuite.
Prerequisites: The NetSuite role associated with the access token must have sufficient permissions to view Sales Orders, Item Fulfillments, Invoices, Credit Memos, Customer Payments, Customers, Employees, and System Notes records. Access to SuiteAnalytics Connect is also required.

a Sample Query python

import requests
from requests_oauthlib import OAuth1
import json
import pandas as pd
import time

class=class="hl-string">"hl-comment"># --- Configuration Section --- #
class=class="hl-string">"hl-comment"># Replace with your NetSuite account-specific details
ACCOUNT_ID = class="hl-string">'[Your NetSuite Account ID]'
CONSUMER_KEY = class="hl-string">'[Your Consumer Key]'
CONSUMER_SECRET = class="hl-string">'[Your Consumer Secret]'
TOKEN_ID = class="hl-string">'[Your Token ID]'
TOKEN_SECRET = class="hl-string">'[Your Token Secret]'

class=class="hl-string">"hl-comment"># The base URL for the REST API, typically requires replacing hyphens with underscores in the account ID for the domain
class=class="hl-string">"hl-comment"># e.g., if your account ID is 1234567-sb1, the domain part would be 1234567_sb1.suitetalk.api.netsuite.com
class=class="hl-string">"hl-comment"># For production accounts, it's just the account ID, e.g., 1234567.suitetalk.api.netsuite.com
BASE_URL = fclass="hl-string">'https://{ACCOUNT_ID.replace("-", "_")}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql'

class=class="hl-string">"hl-comment"># --- Extraction Parameters --- #
START_DATE = class="hl-string">'2023-01-01' class=class="hl-string">"hl-comment"># Inclusive
END_DATE = class="hl-string">'2023-12-31'   class=class="hl-string">"hl-comment"># Inclusive
OUTPUT_FILE = class="hl-string">'netsuite_otc_event_log.csv'

class=class="hl-string">"hl-comment"># --- SuiteQL Query --- #
class=class="hl-string">"hl-comment"># This query unions data from multiple sources to create a comprehensive event log.
SUITEQL_QUERY = fclass="hl-string">"""
-- 1. Sales Order Created
SELECT
    t.tranId AS SalesOrder,
    'Sales Order Created' AS ActivityName,
    t.createdDate AS EventTime,
    BUILTIN.DF(t.entity) AS CustomerName,
    BUILTIN.DF(t.salesRep) AS User,
    t.total AS TotalOrderAmount,
    BUILTIN.DF(t.status) AS SalesOrderStatus,
    BUILTIN.DF(tl.item) AS ProductCategory, -- Simplified, assumes first line item category is representative
    t.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(t.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS t
    JOIN TransactionLine AS tl ON t.id = tl.transaction
WHERE
    t.type = 'SalesOrd'
    AND tl.mainLine = 'F'
    AND tl.item IS NOT NULL
    AND t.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND t.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 2. Credit Hold Applied / 3. Credit Hold Released / 4. Sales Order Approved / 14. Sales Order Closed
SELECT
    t.tranId AS SalesOrder,
    CASE 
        WHEN sn.newValue LIKE '%Pending Approv%' THEN 'Credit Hold Applied'
        WHEN sn.oldValue LIKE '%Pending Approv%' AND sn.newValue LIKE '%Pending Fulfill%' THEN 'Credit Hold Released'
        WHEN sn.newValue LIKE '%Pending Fulfill%' THEN 'Sales Order Approved'
        WHEN sn.newValue LIKE '%Closed%' THEN 'Sales Order Closed'
    END AS ActivityName,
    sn.date AS EventTime,
    BUILTIN.DF(t.entity) AS CustomerName,
    BUILTIN.DF(sn.author) AS User,
    t.total AS TotalOrderAmount,
    sn.newValue AS SalesOrderStatus,
    NULL AS ProductCategory,
    t.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(t.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS t
    JOIN SystemNote AS sn ON sn.recordId = t.id
WHERE
    t.type = 'SalesOrd'
    AND sn.field = 'TRANSACTION.STATUS'
    AND t.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    AND CASE 
        WHEN sn.newValue LIKE '%Pending Approv%' THEN 1
        WHEN sn.oldValue LIKE '%Pending Approv%' AND sn.newValue LIKE '%Pending Fulfill%' THEN 1
        WHEN sn.newValue LIKE '%Pending Fulfill%' THEN 1
        WHEN sn.newValue LIKE '%Closed%' THEN 1
        ELSE 0
    END = 1
    /* AND t.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 5. Sales Order Changed
SELECT
    t.tranId AS SalesOrder,
    'Sales Order Changed' AS ActivityName,
    sn.date AS EventTime,
    BUILTIN.DF(t.entity) AS CustomerName,
    BUILTIN.DF(sn.author) AS User,
    t.total AS TotalOrderAmount,
    BUILTIN.DF(t.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    t.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(t.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS t
    JOIN SystemNote AS sn ON sn.recordId = t.id
WHERE
    t.type = 'SalesOrd'
    AND sn.field IN ('TRANSACTION.TRANDATE', 'TRANSACTION.SHIPDATE', 'TRANSACTIONLINE.QUANTITY', 'TRANSACTIONLINE.RATE')
    AND t.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND t.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 6. Inventory Committed (approximated by 'Sales Order Approved' event)
SELECT
    t.tranId AS SalesOrder,
    'Inventory Committed' AS ActivityName,
    sn.date AS EventTime,
    BUILTIN.DF(t.entity) AS CustomerName,
    BUILTIN.DF(sn.author) AS User,
    t.total AS TotalOrderAmount,
    sn.newValue AS SalesOrderStatus,
    NULL AS ProductCategory,
    t.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(t.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS t
    JOIN SystemNote AS sn ON sn.recordId = t.id
WHERE
    t.type = 'SalesOrd'
    AND sn.field = 'TRANSACTION.STATUS'
    AND sn.newValue LIKE '%Pending Fulfill%'
    AND t.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND t.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 7. Order Fulfillment Created
SELECT
    so.tranId AS SalesOrder,
    'Order Fulfillment Created' AS ActivityName,
    ful.createdDate AS EventTime,
    BUILTIN.DF(so.entity) AS CustomerName,
    BUILTIN.DF(ful.createdby) AS User,
    so.total AS TotalOrderAmount,
    BUILTIN.DF(so.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    so.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(so.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS ful
    JOIN TransactionLink AS tl ON ful.id = tl.id
    JOIN Transaction AS so ON so.id = tl.previousdoc
WHERE
    ful.type = 'ItemShip'
    AND so.type = 'SalesOrd'
    AND so.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND so.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 8. Goods Picked / 9. Goods Packed / 10. Goods Shipped
SELECT
    so.tranId AS SalesOrder,
    CASE 
        WHEN sn.newValue LIKE '%Picked%' THEN 'Goods Picked'
        WHEN sn.newValue LIKE '%Packed%' THEN 'Goods Packed'
        WHEN sn.newValue LIKE '%Shipped%' THEN 'Goods Shipped'
    END AS ActivityName,
    sn.date AS EventTime,
    BUILTIN.DF(so.entity) AS CustomerName,
    BUILTIN.DF(sn.author) AS User,
    so.total AS TotalOrderAmount,
    BUILTIN.DF(so.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    so.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(so.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM
    Transaction AS ful
    JOIN SystemNote AS sn ON sn.recordId = ful.id
    JOIN TransactionLink AS tl ON ful.id = tl.id
    JOIN Transaction AS so ON so.id = tl.previousdoc
WHERE
    ful.type = 'ItemShip'
    AND so.type = 'SalesOrd'
    AND sn.field = 'TRANSACTION.STATUS'
    AND so.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND so.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 11. Invoice Created
SELECT
    so.tranId AS SalesOrder,
    'Invoice Created' AS ActivityName,
    inv.createdDate AS EventTime,
    BUILTIN.DF(so.entity) AS CustomerName,
    BUILTIN.DF(inv.createdby) AS User,
    so.total AS TotalOrderAmount,
    BUILTIN.DF(so.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    so.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(so.terms) AS PaymentTerms,
    inv.tranId AS InvoiceNumber
FROM
    Transaction AS inv
    JOIN TransactionLink AS tl ON inv.id = tl.id
    JOIN Transaction AS so ON so.id = tl.previousdoc
WHERE
    inv.type = 'CustInvc'
    AND so.type = 'SalesOrd'
    AND so.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND so.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 12. Credit Memo Created
SELECT 
    so.tranId AS SalesOrder,
    'Credit Memo Created' AS ActivityName,
    cm.createdDate AS EventTime,
    BUILTIN.DF(so.entity) AS CustomerName,
    BUILTIN.DF(cm.createdby) AS User,
    so.total AS TotalOrderAmount,
    BUILTIN.DF(so.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    so.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(so.terms) AS PaymentTerms,
    NULL AS InvoiceNumber
FROM 
    Transaction cm
    JOIN TransactionLink tl ON cm.id = tl.id
    JOIN Transaction so ON tl.previousdoc = so.id
WHERE 
    cm.type = 'CustCred'
    AND so.type = 'SalesOrd'
    AND so.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND so.subsidiary = '[Your Subsidiary ID]' */

UNION ALL

-- 13. Payment Received
SELECT
    so.tranId AS SalesOrder,
    'Payment Received' AS ActivityName,
    payment.createdDate AS EventTime,
    BUILTIN.DF(so.entity) AS CustomerName,
    BUILTIN.DF(payment.createdby) AS User,
    so.total AS TotalOrderAmount,
    BUILTIN.DF(so.status) AS SalesOrderStatus,
    NULL AS ProductCategory,
    so.shipDate AS RequestedDeliveryDate,
    BUILTIN.DF(so.terms) AS PaymentTerms,
    inv.tranId AS InvoiceNumber
FROM
    Transaction AS payment
    JOIN TransactionLink AS paymentLink ON payment.id = paymentLink.id
    JOIN Transaction AS inv ON paymentLink.previousdoc = inv.id
    JOIN TransactionLink AS invLink ON inv.id = invLink.id
    JOIN Transaction AS so ON invLink.previousdoc = so.id
WHERE
    payment.type = 'CustPymt'
    AND inv.type = 'CustInvc'
    AND so.type = 'SalesOrd'
    AND so.createdDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
    /* AND so.subsidiary = '[Your Subsidiary ID]' */
"""

def execute_suiteql_query(query):
    class="hl-string">"""Executes a SuiteQL query and handles pagination."""
    all_results = []
    offset = 0
    limit = 1000

    oauth = OAuth1(
        client_key=CONSUMER_KEY,
        client_secret=CONSUMER_SECRET,
        resource_owner_key=TOKEN_ID,
        resource_owner_secret=TOKEN_SECRET,
        realm=ACCOUNT_ID,
        signature_method=class="hl-string">'HMAC-SHA256'
    )

    while True:
        paginated_query = {
            class="hl-string">'q': query
        }

        headers = {
            class="hl-string">'Prefer': fclass="hl-string">'transient, odata.maxpagesize={limit}',
            class="hl-string">'Content-Type': class="hl-string">'application/json'
        }

        rest_url = fclass="hl-string">"{BASE_URL}?limit={limit}&offset={offset}"

        try:
            print(fclass="hl-string">"Fetching data... Page offset: {offset}")
            response = requests.post(rest_url, headers=headers, json=paginated_query, auth=oauth)
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raises an HTTPError for bad responses (4xx or 5xx)

            data = response.json()
            results = data.get(class="hl-string">'items', [])
            all_results.extend(results)

            if not data.get(class="hl-string">'hasMore', False):
                print(class="hl-string">"No more pages to fetch.")
                break

            offset += limit
            class=class="hl-string">"hl-comment"># Respect API concurrency limits
            time.sleep(1)

        except requests.exceptions.HTTPError as e:
            print(fclass="hl-string">"HTTP Error: {e}")
            print(fclass="hl-string">"Response Body: {e.response.text}")
            break
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Request Error: {e}")
            break
        except json.JSONDecodeError:
            print(fclass="hl-string">"JSON Decode Error. Response was: {response.text}")
            break

    return all_results

if __name__ == class="hl-string">"__main__":
    print(class="hl-string">"Starting NetSuite Order-to-Cash extraction...")
    
    results = execute_suiteql_query(SUITEQL_QUERY)

    if results:
        df = pd.json_normalize(results)
        class=class="hl-string">"hl-comment"># Rename columns to match ProcessMind requirements
        df.columns = [c.replace(class="hl-string">'.', class="hl-string">'_') for c in df.columns] class=class="hl-string">"hl-comment"># Flatten nested names
        df.rename(columns={
            class="hl-string">'salesorder': class="hl-string">'SalesOrder',
            class="hl-string">'activityname': class="hl-string">'ActivityName',
            class="hl-string">'eventtime': class="hl-string">'EventTime',
            class="hl-string">'customername': class="hl-string">'CustomerName',
            class="hl-string">'user': class="hl-string">'User',
            class="hl-string">'totalorderamount': class="hl-string">'TotalOrderAmount',
            class="hl-string">'salesorderstatus': class="hl-string">'SalesOrderStatus',
            class="hl-string">'productcategory': class="hl-string">'ProductCategory',
            class="hl-string">'requesteddeliverydate': class="hl-string">'RequestedDeliveryDate',
            class="hl-string">'paymentterms': class="hl-string">'PaymentTerms',
            class="hl-string">'invoicenumber': class="hl-string">'InvoiceNumber'
        }, inplace=True)
        
        class=class="hl-string">"hl-comment"># Ensure all required columns exist
        required_cols = [class="hl-string">'SalesOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'CustomerName', class="hl-string">'User', class="hl-string">'TotalOrderAmount', class="hl-string">'SalesOrderStatus', class="hl-string">'ProductCategory', class="hl-string">'RequestedDeliveryDate', class="hl-string">'PaymentTerms', class="hl-string">'InvoiceNumber']
        for col in required_cols:
            if col not in df.columns:
                df[col] = None

        class=class="hl-string">"hl-comment"># Convert EventTime to datetime and format it
        df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'])
        df = df.sort_values(by=[class="hl-string">'SalesOrder', class="hl-string">'EventTime'])

        df.to_csv(OUTPUT_FILE, index=False)
        print(fclass="hl-string">"Successfully extracted {len(df)} events to {OUTPUT_FILE}")
    else:
        print(class="hl-string">"No data returned from the query.")

Data Template: Order to Cash - Sales Order Processing

Your Order to Cash - Sales Order Processing Data Template

Order to Cash - Sales Order Processing Attributes

Order to Cash - Sales Order Processing Activities

Extraction Guides

NetSuite Saved Search and Automated CSV Export

Steps

Configuration

a Sample Query config

NetSuite SuiteAnalytics Connect (ODBC) SQL Query

Steps

Configuration

a Sample Query sql

Python Extraction using SuiteTalk REST Web Services

Steps

Configuration

a Sample Query python

Ready to Get Started?

Unlock Faster Cash: Optimize Sales Order Processing Today