> Continuous Process Improvement > Accounts Receivable > NetSuite > Data Template

Your Accounts Receivable Data Template

NetSuite

Accounts Receivable Attributes (22) Accounts Receivable Activities (13) Extraction Guides (3)

Your Accounts Receivable Data Template

This template provides a comprehensive blueprint for mapping your invoicing and collections cycle within NetSuite. It outlines the essential attributes and process activities required to build a high,quality event log, ensuring you have the visibility needed to optimize your cash flow. By following this guide, you can streamline your data preparation and accelerate your journey toward reducing days sales outstanding.

Essential NetSuite attributes for deep analysis
Standardized AR activity tracking milestones
Strategic extraction guidance for NetSuite systems

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

Download Template

Accounts Receivable Attributes

The following table lists the recommended data fields and dimensional attributes required to provide a complete view of your NetSuite invoice lifecycles.

5 Required 7 Recommended 10 Optional

	Name	Description
	Activity Activity	The specific event or action performed on the invoice.
Description This attribute represents the process step name, such as 'Invoice Created', 'Payment Applied', or 'Dispute Opened'. In NetSuite, this is typically derived from the `type` field of the transaction for creation events, or from the `SystemNote` table for status changes and modifications. It is central to process mining as it defines the nodes in the process map. By analyzing the sequence of these activities, analysts can identify bottlenecks, loops (such as repeated dispute handling), and deviations from the standard happy path. Why it matters Defines the workflow steps and enables the construction of the process graph. Where to get Derived from Transaction 'type' or SystemNote 'field'/'value' Examples Invoice CreatedPayment ReceivedCredit Memo IssuedDispute Opened
	Event Timestamp DateCreated	The exact date and time when the activity occurred.
Description This attribute records the precise moment an event took place. For the creation of the invoice, this is the `datecreated` field in the Transaction table. For subsequent updates, it is the timestamp found in the `SystemNote` or `Message` tables. Accurate timestamps are crucial for calculating cycle times, such as the Average Invoice-to-Cash Cycle Time and Invoice Issuance Lead Time. They allow the analysis to sequence events correctly and identify delays between specific process steps. Why it matters Essential for ordering events and calculating duration metrics between process steps. Where to get Transaction 'datecreated' or SystemNote 'date' Examples 2023-10-15T08:30:00Z2023-10-15T14:45:22Z2023-11-01T09:00:00Z
	Invoice Number TranId	The unique reference number assigned to the invoice in NetSuite.
Description This attribute serves as the primary case identifier for the Accounts Receivable process. In NetSuite, the `tranid` field usually contains the document number (e.g., INV-10023) visible to users and customers. It links all subsequent events, such as payments, disputes, and credit memos, to a specific financial obligation. In analysis, this identifier is used to group individual events into a single process case. It enables the end-to-end visualization of the order-to-cash cycle and is essential for joining data across different NetSuite tables such as `Transaction`, `TransactionLine`, and `SystemNote`. Why it matters It is the fundamental key for tracking the lifecycle of a receivable and calculating cycle times. Where to get Transaction table, field 'tranid' Examples INV-2023-001INV-998822CM-4022JE-10023
	Last Data Update LastDataUpdate	The timestamp of when the data was extracted or last refreshed.
Description This attribute indicates when the data snapshot was taken from NetSuite. It is used to determine the freshness of the analysis and to calculate the age of open cases relative to the analysis date. In dashboards, it helps users understand if they are looking at real-time data or a historical extract. It acts as a reference point for calculating current open durations for invoices that have not yet cleared. Why it matters Ensures data freshness transparency and aids in calculating running durations for open cases. Where to get System time at extraction Examples 2023-12-01T00:00:00Z2023-12-02T12:00:00Z
	Source System SourceSystem	The system of record from which the data was extracted.
Description This attribute identifies the origin of the data, which in this context is 'NetSuite'. In multi-system landscapes where AR data might be merged with a CRM or external banking system, this field ensures traceability. It allows analysts to filter or segment the process view by data source if multiple ERPs are being analyzed simultaneously. It is typically a static value added during the extraction and transformation process. Why it matters Provides data lineage and context in multi-system process mining implementations. Where to get Static value 'NetSuite' Examples NetSuiteNetSuite ERPNetSuite OneWorld
	Business Unit Subsidiary	The subsidiary or business unit the invoice belongs to.
Description In NetSuite OneWorld environments, this attribute identifies the specific legal entity or subsidiary that issued the invoice. It allows for comparative analysis across different branches or divisions of the company. This attribute supports the Invoice Dispatch Efficiency dashboard by allowing users to drill down into specific business units. It helps isolate whether process issues are systemic across the organization or localized to specific operational units. Why it matters Crucial for benchmarking performance across different organizational entities. Where to get Transaction table, field 'subsidiary' Examples US WestEMEA OperationsAPAC Sales
	Customer Name Entity	The name of the customer or entity associated with the invoice.
Description This attribute identifies the customer responsible for paying the invoice. In NetSuite, this links to the `Entity` table (Customer record). It is fundamental for all customer-centric analysis. Analysts use this field to segment performance by customer, identifying those who frequently pay late, generate disputes, or deviate from standard payment terms. It supports the Credit Terms Compliance Monitor and Collection Agent Throughput dashboards. Why it matters Enables segmentation of process performance by customer behavior and risk profile. Where to get Transaction table, field 'entity' (joins to Entity/Customer table) Examples Acme CorpGlobex CorporationSoylent Corp
	Department Department	The internal department responsible for the transaction.
Description This attribute maps to the NetSuite `department` segment, indicating which functional area (e.g., Sales, Services, IT) generated the invoice. It allows for cross-functional analysis of AR performance. By segmenting data by department, analysts can identify if specific operational areas are prone to slower billing cycles or higher dispute rates, aiding in targeted process improvements. Why it matters Identifies internal process owners and varies in billing efficiency. Where to get Transaction table, field 'department' Examples Professional ServicesSoftware SalesHardware Fulfillment
	Due Date DueDate	The date by which the payment is expected according to terms.
Description This attribute holds the scheduled due date for the invoice, found in the `duedate` field in NetSuite. It is the baseline for determining if a payment is on time or late. It is the primary variable for calculating Average Days Beyond Terms and identifying High Value Collection Risk. Comparison between this date and the `DateCreated` of the payment event reveals the extent of delays and helps measure adherence to credit terms. Why it matters The reference point for all aging, overdue, and compliance calculations. Where to get Transaction table, field 'duedate' Examples 2023-11-152023-12-012024-01-30
	Invoice Amount TotalAmount	The total financial value of the invoice.
Description This attribute represents the total amount of the invoice, typically found in the `foreignTotal` or `total` field in the NetSuite Transaction table. It represents the value that needs to be collected from the customer. This metric is used to analyze the value flow through the AR process, prioritize high-value collections, and calculate KPIs like Dispute Backlog Value. It allows for filtering cases by financial tier to focus optimization efforts on high-impact transactions. Why it matters Critical for quantifying the financial impact of process inefficiencies and prioritizing collections. Where to get Transaction table, field 'foreignTotal' or 'total' Examples 1500.00250.5010000.00
	Invoice Status Status	The current system status of the invoice.
Description This attribute reflects the state of the transaction in NetSuite, such as 'Open', 'Paid In Full', or 'Pending Approval'. It is typically derived from the `status` or `statusRef` field. While the Activity attribute tracks the history of what happened, this attribute provides the current snapshot state. It is vital for filtering the High Value Collection Risk dashboard to show only currently open and overdue items. Why it matters Provides immediate visibility into which cases are closed versus those requiring action. Where to get Transaction table, field 'status' Examples Paid In FullOpenPending Approval
	Sales Representative SalesRep	The sales representative associated with the invoice.
Description This attribute identifies the sales rep responsible for the account or transaction. In NetSuite, this is often the `salesrep` field on the transaction record. It is used to correlate billing issues or credit memos with specific sales personnel. For example, the Credit Memo and Revenue Leakage dashboard uses this to see if certain reps have higher rates of post-invoice adjustments, indicating potential upstream quality issues in the sales process. Why it matters Links downstream AR issues back to the upstream sales process owners. Where to get Transaction table, field 'salesrep' Examples John SmithSarah ConnorKyle Reese
	Collection Agent CollectionAgent	The user responsible for collection activities on this invoice.
Description This attribute identifies the specific employee managing the collection efforts for the invoice. It may be the same as the Sales Rep, or a dedicated AR specialist, often found in a custom field or derived from the user logging 'Payment Reminder' activities. This attribute drives the Collection Agent Throughput dashboard, allowing managers to compare performance across the collections team and identify training needs or workload imbalances. Why it matters Enables performance evaluation of individual collection team members. Where to get SystemNotes 'name' on collection activities or Custom Field Examples Agent SmithBilling Specialist ACollections Team 1
	Currency Code Currency	The currency in which the invoice was issued.
Description This attribute specifies the ISO currency code (e.g., USD, EUR) for the transaction, found in the `currency` field in NetSuite. It is necessary for handling multi-currency environments correctly. While often a background field, it is crucial when aggregating `TotalAmount` to ensure that different currencies are not summed incorrectly without conversion. It provides context for regional performance variations. Why it matters Essential for correct financial aggregation in global organizations. Where to get Transaction table, field 'currency' Examples USDEURGBP
	Days Overdue DaysOverdue	The number of days an open invoice has passed its due date.
Description This is a calculated attribute representing the gap between the `DueDate` and the current analysis date (or the payment date if closed). It provides a continuous metric for aging analysis. It is the primary driver for aging buckets (e.g., 30-60, 60-90) used in the High Value Collection Risk dashboard. It allows for immediate identification of the most critical delays in the system. Why it matters Standard metric for assessing the severity of payment delays. Where to get Calculated: (Current Date - DueDate) Examples 150-5
	Dispute Reason DisputeReason	The reason code or description for why an invoice is in dispute.
Description This attribute captures the specific reason a customer has refused to pay or requested a correction. In NetSuite, this is often a custom field or a specific 'Case' record linked to the invoice. It is critical for the Dispute Lifecycle Analysis dashboard. Grouping by dispute reason helps identify root causes—such as 'Pricing Error', 'Damaged Goods', or 'Missing PO'—that require upstream systemic fixes to prevent future revenue leakage. Why it matters Identifies root causes of non-payment and administrative churn. Where to get Linked Support Case or Custom Transaction Body Field Examples Incorrect PriceGoods Not ReceivedTax Error
	Invoice Memo Memo	Notes or descriptions attached to the invoice header.
Description This attribute contains the free-text description or memo entered on the invoice, found in the `memo` field. It often contains manual notes regarding special handling or customer agreements. Text mining this field can reveal unstructured reasons for delays or disputes that structured fields miss. It adds qualitative context to the quantitative process data. Why it matters Provides context and qualitative data regarding the transaction. Where to get Transaction table, field 'memo' Examples Service fee for OctoberCorrection of Inv #1002Special payment terms applied
	Is Disputed IsDisputed	A flag indicating if the invoice is currently or was ever in dispute.
Description This boolean attribute indicates whether the invoice has gone through a dispute process. It is derived from the presence of dispute-related activities or a specific status flag on the invoice record. It acts as a critical filter for the Dispute Lifecycle Analysis and helps isolate 'problem' invoices from 'clean' invoices when calculating the First-Pass Collection Rate. Why it matters Segments complex cases from standard happy-path cases. Where to get Calculated based on presence of 'Dispute Opened' activity Examples truefalse
	Payment Terms Terms	The agreed payment conditions for the invoice.
Description This attribute specifies the payment terms assigned to the invoice, such as 'Net 30' or 'Due on Receipt'. It is found in the `terms` field of the Transaction record. This data is used to calculate the expected payment date and to analyze if customers with lenient terms pay more reliably than those with strict terms. It helps in reviewing credit policies and understanding the baseline for the Average Days Beyond Terms KPI. Why it matters Defines the expected behavior and SLA for the customer payment cycle. Where to get Transaction table, field 'terms' Examples Net 30Net 60Due on Receipt2% 10 Net 30
	Product Category Class	The category or class of the goods/services sold.
Description This attribute maps to the NetSuite `class` segment. It categorizes the revenue stream (e.g., Hardware vs. Consulting). Understanding the product mix is vital for analysis. It allows for the analysis of the 'Credit Memo and Revenue Leakage' dashboard to see if specific product lines are more prone to returns or billing errors than others. Why it matters Segments process performance by the type of product or service sold. Where to get Transaction table, field 'class' Examples License RevenueConsulting ServicesHardware
	Promise to Pay Date PromiseDate	The date the customer has promised to send payment.
Description This attribute records the date a customer committed to paying an overdue invoice, usually logged by a collection agent. In NetSuite, this is typically stored in the 'Phone Call' record or a custom transaction body field. This date is essential for the Promise to Pay Compliance dashboard. By comparing this promised date against the actual payment date, the organization can measure the reliability of customer commitments and the effectiveness of collection calls. Why it matters Measures the reliability of customer commitments and collection effectiveness. Where to get Task/Phone Call record or Custom Field Examples 2023-11-202023-12-05
	Region Location	The location or region associated with the transaction.
Description In NetSuite, the `location` field often represents a warehouse, office, or geographical region. This attribute maps this field to a regional concept for analysis. It is used in the DSO Performance Overview to identify regional disparities in payment behaviors. It helps answer whether collections issues are global or isolated to specific territories. Why it matters Supports geographical segmentation of AR performance. Where to get Transaction table, field 'location' Examples North America WarehouseLondon OfficeSingapore

Required Recommended Optional

Accounts Receivable Activities

These are the critical process steps and transaction milestones that should be captured in your event log to accurately model your collections flow.

6 Recommended 7 Optional

	Activity	Description
	Credit Memo Issued	The creation of a Credit Memo transaction applied to the Invoice. This reduces the open balance and often indicates a return or billing error.
Why it matters Visualizes Revenue Leakage. Frequent credit memos suggest systemic process failures in sales or fulfillment. Where to get Transaction table: Record type 'CustCred' applied to the Invoice ID via 'appliedtotransaction'. Capture Logged when transaction Credit Memo executed Event type explicit
	Full Payment Received	The application of a Customer Payment that reduces the invoice balance to zero. This marks the effective end of the collection effort.
Why it matters The primary timestamp for DSO and Average Days Beyond Terms calculations. Represents the successful realization of revenue. Where to get Transaction table: Type 'CustPymt' applied to Invoice where resulting 'status' of Invoice becomes 'Paid In Full'. Capture Logged when transaction Payment executed Event type explicit
	Invoice Approved	The transition of the invoice status from Pending Approval to Open. This is captured by monitoring the System Notes or workflow history logs associated with the invoice.
Why it matters Indicates internal processing time before the customer is billed. Delays here contribute to longer total cycle times without adding value to the customer. Where to get System Notes: Filter for field 'status' changing from 'Pending Approval' to 'Open'. Capture Compare status field before/after Event type inferred
	Invoice Cleared	The final status change of the invoice to 'Paid In Full'. This serves as the process end state for successful cases.
Why it matters Required for Average Invoice-to-Cash Cycle Time. Differentiates open items from closed items in process variants. Where to get System Notes: Status change on Invoice record to 'Paid In Full'. Capture Compare status field before/after Event type inferred
	Invoice Created	The initial creation of the invoice record in the system. This is captured from the Transaction table where the record type is Invoice.
Why it matters Marks the start of the accounts receivable cycle and establishes the baseline for aging calculations. Essential for calculating Invoice-to-Cash Cycle Time. Where to get Transaction table: 'trandate' or 'datecreated' field for records with type 'CustInvc'. Capture Logged when transaction Invoice executed Event type explicit
	Invoice Dispatched	The act of sending the invoice to the customer via email, mail, or fax. This is captured by looking at the Message records linked to the specific Invoice transaction.
Why it matters Critical for the Invoice Issuance Lead Time KPI. Identifying delays here reveals administrative bottlenecks preventing timely payment collection. Where to get Messages table: Join on transaction ID where 'authoremail' is internal and 'recipientemail' is external. Capture Logged when transaction Email executed Event type explicit
	Bad Debt Written Off	The removal of the invoice balance via a Journal Entry or specific Credit Memo type for uncollectible amounts. Represents a failure of the collection process.
Why it matters Separates uncollected revenue from successful collections in analysis. Important for understanding true collection efficiency. Where to get Journal Entry or Credit Memo: filtered by specific GL accounts (Bad Debt Expense) linked to the Invoice. Capture Derive from GL account usage Event type inferred
	Bank Statement Matched	The reconciliation of the payment transaction against the bank statement feed. Captured via the 'Match Bank Data' or Reconciliation history.
Why it matters Tracks the Automated Bank Clearing Rate. Delays here obscure the true cash position and indicate manual back-office friction. Where to get Transaction status: Check if the Payment transaction status changes to 'Deposited' or is flagged as Reconciled. Capture Compare status field before/after Event type inferred
	Dispute Case Opened	The initiation of a dispute regarding the invoice, often inferred by a linked Support Case or a specific custom status. This halts the standard collection flow.
Why it matters Key for the Dispute Lifecycle Analysis dashboard. High volumes here indicate upstream quality issues in fulfillment or billing. Where to get Support Case record: Join to Transaction where category is 'Billing Dispute', or Transaction status change to 'Disputed'. Capture Compare status field or linked record creation Event type inferred
	Dispute Resolved	The conclusion of a dispute case, returning the invoice to a collectible state or resulting in a credit. Captured when the linked Support Case status moves to Closed.
Why it matters Necessary for calculating Average Dispute Resolution Time. Long resolution times negatively impact DSO and customer satisfaction. Where to get Support Case record: 'dateclosed' field for cases linked to the invoice. Capture Compare status field before/after Event type inferred
	Partial Payment Posted	The application of a Customer Payment that reduces the invoice balance but does not fully clear it. Captured from the Transaction Line table.
Why it matters Supports Partial Payment Process Flow analysis. Identifies fragmented cash flow and customers struggling with liquidity. Where to get Transaction table: Type 'CustPymt' applied to Invoice where 'amountremaining' > 0 on the Invoice. Capture Logged when transaction Payment executed Event type explicit
	Payment Reminder Sent	A collection email or letter generated and sent to the customer regarding overdue payment. Captured via the Dunning Letters module logs or Message records linked to the invoice.
Why it matters Measures the effort required to collect cash and the effectiveness of dunning strategies. Essential for the Collection Reminder Effectiveness dashboard. Where to get Message record or Dunning Bundle Log: Check for specific subject lines (e.g., 'Reminder', 'Overdue'). Capture Derive from message subject patterns Event type inferred
	Promise to Pay Received	A record indicating the customer has committed to paying by a certain date. Usually captured in User Notes or a specialized collection management bundle.
Why it matters Tracks the Promise to Pay Compliance KPI. Helps distinguish between cooperative customers and those requiring escalation. Where to get Note record: Text analysis for 'PTP' or 'Promise', or specific fields in a Collections SuiteApp. Capture Derive from text analysis of notes Event type inferred

Recommended Optional

Extraction Guides

How to get your data from NetSuite

Steps

Prerequisites: Ensure you have the 'SuiteAnalytics Workbook' feature enabled and permissions to access Transactions, System Notes, Messages, and Support Cases.
Access SuiteAnalytics: Navigate to Analytics > Workbooks > New Workbook.
Select Root Record: Choose Transaction as the root record type. This serves as the anchor for financial data.
Dataset Strategy: Due to the need to extract diverse activities (Approvals, Messages, Disputes) into a single 'as-is' event log, a single joined dataset in the UI will produce wide rows (Cartesian product). The recommended expert approach is to utilize the SuiteQL capability underlying SuiteAnalytics. You can execute the query provided below using the 'SuiteAnalytics Connect' (ODBC/JDBC) interface or via a 'SuiteQL Query Tool' bundle within the UI.
Execute Query: Copy the complete SuiteQL script provided in the 'Query' section. Replace the placeholder {{LastDataUpdate}} with your extraction timestamp and {{StartDate}} with your analysis start date (e.g., '2023-01-01').
Verify Columns: Ensure all projected columns (TranId, Activity, DateCreated, etc.) are present in the result set.
Export Data: Run the query and export the results to CSV.
Formatting: Open the CSV. Ensure the 'DateCreated' column is formatted as YYYY-MM-DD HH:MM:SS. Ensure 'TotalAmount' contains no currency symbols, just numeric values.
Validation: Check that a single Invoice ID (TranId) appears multiple times with different 'Activity' values (e.g., Created, Approved, Payment Received).
Ingestion: Upload the CSV to ProcessMind. Map 'TranId' to Case ID, 'Activity' to Activity Name, and 'DateCreated' to Timestamp.

Configuration

SuiteAnalytics Connect: Must be enabled in Enable Features > Analytics to run complex UNION queries externally.
Permissions: User requires 'View' access to Transactions, System Notes, Support Cases, and Messages.
Date Filtering: Recommended range is rolling 12 months. Large historical extracts may time out; verify row limits (NetSuite often limits results to 5k-100k rows depending on the interface; ODBC is recommended for full history).
Subsidiary Context: If OneWorld account, ensure the user has access to all target subsidiaries or filter by t.subsidiary.
Performance: The SystemNote table is very large. Filtering by recordtype and date is mandatory for performance.

a Sample Query config

/* SuiteQL Query for Accounts Receivable Process Mining */
/* Combined Event Log Extraction */

/* 1. Invoice Created */
SELECT
  t.tranid AS TranId,
  'Invoice Created' AS Activity,
  TO_CHAR(t.trandate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  emp.entityid AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
  LEFT JOIN Employee emp ON t.salesrep = emp.id
WHERE
  t.type = 'CustInvc'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 2. Invoice Approved (via System Notes) */
SELECT
  t.tranid AS TranId,
  'Invoice Approved' AS Activity,
  TO_CHAR(sn.date, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN SystemNote sn ON sn.recordid = t.id
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND sn.recordtypeid = -30 /* Transaction Record Type */
  AND sn.standardfield = 'STATUS'
  AND sn.oldvalue = 'Pending Approval'
  AND sn.newvalue = 'Open'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 3. Invoice Dispatched / 4. Payment Reminder (via Messages) */
SELECT
  t.tranid AS TranId,
  CASE 
    WHEN m.subject LIKE '%Reminder%' THEN 'Payment Reminder Sent'
    ELSE 'Invoice Dispatched'
  END AS Activity,
  TO_CHAR(m.messagedate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN Message m ON m.transaction = t.id
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND (m.subject LIKE '%Invoice%' OR m.subject LIKE '%Reminder%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 5. Dispute Opened / 6. Dispute Resolved (via Support Cases) */
SELECT
  t.tranid AS TranId,
  CASE 
    WHEN sc.status = 'Closed' THEN 'Dispute Resolved'
    ELSE 'Dispute Case Opened'
  END AS Activity,
  TO_CHAR(sc.createddate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN SupportCase sc ON sc.custevent_linked_invoice = t.id /* Adjust custom field name */
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 7. Promise to Pay (via Notes) */
SELECT
  t.tranid AS TranId,
  'Promise to Pay Received' AS Activity,
  TO_CHAR(n.notedate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN Note n ON n.transaction = t.id
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND (n.title LIKE '%Promise%' OR n.note LIKE '%Promise%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 8. Credit Memo Issued */
/* Logic: Find Credit Memos applied to the Invoice */
SELECT
  inv.tranid AS TranId,
  'Credit Memo Issued' AS Activity,
  TO_CHAR(cm.trandate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  cm.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  inv.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  inv.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction inv
  JOIN NextTransactionLineLink ntll ON ntll.previousdoc = inv.id
  JOIN Transaction cm ON ntll.nextdoc = cm.id
  LEFT JOIN Customer c ON inv.entity = c.id
  LEFT JOIN Subsidiary s ON inv.subsidiary = s.id
WHERE
  inv.type = 'CustInvc'
  AND cm.type = 'CustCred'
  AND inv.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 9. Partial Payment / 10. Full Payment Received */
SELECT
  inv.tranid AS TranId,
  CASE
    WHEN (inv.foreigntotal - py.foreigntotal) <= 0 THEN 'Full Payment Received'
    ELSE 'Partial Payment Posted'
  END AS Activity,
  TO_CHAR(py.trandate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  py.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  inv.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  inv.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction inv
  JOIN NextTransactionLineLink ntll ON ntll.previousdoc = inv.id
  JOIN Transaction py ON ntll.nextdoc = py.id
  LEFT JOIN Customer c ON inv.entity = c.id
  LEFT JOIN Subsidiary s ON inv.subsidiary = s.id
WHERE
  inv.type = 'CustInvc'
  AND py.type = 'CustPymt'
  AND inv.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 11. Bad Debt Written Off */
SELECT
  t.tranid AS TranId,
  'Bad Debt Written Off' AS Activity,
  TO_CHAR(je.trandate, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  t.status AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN NextTransactionLineLink ntll ON ntll.previousdoc = t.id
  JOIN Transaction je ON ntll.nextdoc = je.id
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND je.type = 'Journal'
  AND (je.memo LIKE '%Bad Debt%' OR je.memo LIKE '%Write Off%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 13. Invoice Cleared */
SELECT
  t.tranid AS TranId,
  'Invoice Cleared' AS Activity,
  TO_CHAR(sn.date, 'YYYY-MM-DD HH:MI:SS') AS DateCreated,
  t.foreigntotal AS TotalAmount,
  c.entityid AS Entity,
  t.duedate AS DueDate,
  s.name AS Subsidiary,
  NULL AS SalesRep,
  'Paid In Full' AS Status,
  'NetSuite' AS SourceSystem,
  SYSDATE AS LastDataUpdate
FROM
  Transaction t
  JOIN SystemNote sn ON sn.recordid = t.id
  LEFT JOIN Customer c ON t.entity = c.id
  LEFT JOIN Subsidiary s ON t.subsidiary = s.id
WHERE
  t.type = 'CustInvc'
  AND sn.standardfield = 'STATUS'
  AND sn.newvalue = 'Paid In Full'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

Steps

Prerequisites and Permissions: Ensure you have a role with 'SuiteAnalytics Connect' or 'SuiteQL' permissions. You will need a valid Token ID and Token Secret for authentication via REST or a configured ODBC driver connection.
Understand the Schema: This extraction relies on the Transaction, TransactionLine, SystemNote, Message, Note, and SupportCase tables. NetSuite stores historical field changes (like Status updates) in SystemNote, which is critical for timestamps of approvals and status changes.
Identify the Scope: Determine the specific subsidiary, date range, and record types (usually 'CustInvc' for Invoices) required for your analysis.
Configure the SuiteQL Query: The provided query uses UNION ALL to combine data from disparate tables into a single event log format. NetSuite SuiteQL does not support complex procedural SQL, so the logic is flattened into specific selection blocks for each activity.
Execution Strategy: Execute the query using the NetSuite REST API /services/rest/query/v1/suiteql or via ODBC. Due to the potential size of the SystemNote table, it is highly recommended to run this extraction in batches partitioned by trandate (e.g., month-by-month).
Pagination Handling: The NetSuite REST API typically limits responses to 1,000 rows. You must implement offset handling or 'Link' header traversal in your extraction script to retrieve the full dataset.
Data Transformation: The query aligns all results into a standard schema (TranId, Activity, Date, etc.). Ensure dates are formatted as ISO 8601 (YYYY-MM-DD THH:MM:SS) during export if the raw SQL output uses a different locale.
Dispute and Collection Logic: The query looks for SupportCase records linked to the customer and Note records for 'Promise to Pay'. Verify if your organization uses custom fields for these flags instead of standard objects, and adjust the WHERE clauses accordingly.
Validation: Compare the 'Invoice Created' count in your extracted file against a Saved Search in NetSuite for the same period to ensure data completeness.
Final Export: Save the resulting dataset as a CSV file. Ensure the headers match the 'Attributes to include' list (TranId, Activity, DateCreated, etc.) before uploading to ProcessMind.

Configuration

Date Range: Filter by trandate in the Transaction table and date in SystemNote. Recommended window is rolling 12 months for active processes.
Subsidiary Filter: Essential for multi-subsidiary environments. Add AND t.subsidiary = [Your Subsidiary ID] to all WHERE clauses.
Record Types: The query focuses on CustInvc (Invoice), CustPymt (Payment), CustCred (Credit Memo), and Journal.
System Notes: This table is massive. Filtering by recordtype = 'invoice' is mandatory for performance.
Status Mapping: NetSuite statuses are internal IDs (e.g., 'A', 'B'). The query uses BUILTIN.DF to fetch display names, but verify these match your localized UI labels.
Join Strategy: Uses standard joins on transaction.id. Ensure 'Applied To' links are intact for Payment history.

a Sample Query sql

/* 1. Invoice Created */
SELECT
  t.tranid AS TranId,
  'Invoice Created' AS Activity,
  TO_CHAR(t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
WHERE
  t.recordtype = 'invoice'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 2. Invoice Approved (Detected via SystemNote status change to Open) */
SELECT
  t.tranid AS TranId,
  'Invoice Approved' AS Activity,
  TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN SystemNote sn ON sn.recordid = t.id
WHERE
  t.recordtype = 'invoice'
  AND sn.recordtypeid = -30 /* Invoice Record Type ID */
  AND sn.field = 'status'
  AND (sn.after LIKE '%Open%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 3. Invoice Dispatched (Email Sent) */
SELECT
  t.tranid AS TranId,
  'Invoice Dispatched' AS Activity,
  TO_CHAR(m.messagedate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN Message m ON m.transaction = t.id
WHERE
  t.recordtype = 'invoice'
  AND m.subject LIKE '%Invoice%'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 4. Payment Reminder Sent */
SELECT
  t.tranid AS TranId,
  'Payment Reminder Sent' AS Activity,
  TO_CHAR(m.messagedate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN Message m ON m.transaction = t.id
WHERE
  t.recordtype = 'invoice'
  AND (m.subject LIKE '%Reminder%' OR m.subject LIKE '%Overdue%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 5. Dispute Case Opened (Linked Support Case) */
SELECT
  t.tranid AS TranId,
  'Dispute Case Opened' AS Activity,
  TO_CHAR(sc.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN SupportCase sc ON sc.company = t.entity
WHERE
  t.recordtype = 'invoice'
  AND sc.title LIKE '%Invoice%'
  AND sc.createddate >= t.createddate
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 6. Dispute Resolved */
SELECT
  t.tranid AS TranId,
  'Dispute Resolved' AS Activity,
  TO_CHAR(sc.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN SupportCase sc ON sc.company = t.entity
WHERE
  t.recordtype = 'invoice'
  AND sc.status = 'Closed'
  AND sc.title LIKE '%Invoice%'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 7. Promise to Pay Received */
SELECT
  t.tranid AS TranId,
  'Promise to Pay Received' AS Activity,
  TO_CHAR(n.notedate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN Note n ON n.transaction = t.id
WHERE
  t.recordtype = 'invoice'
  AND (n.note LIKE '%Promise to Pay%' OR n.note LIKE '%PTP%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 8. Credit Memo Issued */
SELECT
  t.tranid AS TranId,
  'Credit Memo Issued' AS Activity,
  TO_CHAR(link_t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN TransactionLine tl ON tl.doc = t.id
  JOIN Transaction link_t ON link_t.id = tl.transaction
WHERE
  t.recordtype = 'invoice'
  AND link_t.recordtype = 'creditmemo'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 9. Partial Payment Posted */
SELECT
  t.tranid AS TranId,
  'Partial Payment Posted' AS Activity,
  TO_CHAR(link_t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN TransactionLine tl ON tl.doc = t.id
  JOIN Transaction link_t ON link_t.id = tl.transaction
WHERE
  t.recordtype = 'invoice'
  AND link_t.recordtype = 'customerpayment'
  AND ABS(tl.foreignamount) < t.foreigntotal
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 10. Full Payment Received */
SELECT
  t.tranid AS TranId,
  'Full Payment Received' AS Activity,
  TO_CHAR(link_t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN TransactionLine tl ON tl.doc = t.id
  JOIN Transaction link_t ON link_t.id = tl.transaction
WHERE
  t.recordtype = 'invoice'
  AND link_t.recordtype = 'customerpayment'
  AND ABS(tl.foreignamount) >= (t.foreigntotal - 1.00) /* Approx check for full payment allowing small variance */
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 11. Bad Debt Written Off */
SELECT
  t.tranid AS TranId,
  'Bad Debt Written Off' AS Activity,
  TO_CHAR(link_t.createddate, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN TransactionLine tl ON tl.doc = t.id
  JOIN Transaction link_t ON link_t.id = tl.transaction
WHERE
  t.recordtype = 'invoice'
  AND link_t.recordtype = 'journalentry'
  AND (link_t.memo LIKE '%Write off%' OR link_t.memo LIKE '%Bad Debt%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 12. Bank Statement Matched (via Payment Status) */
SELECT
  t.tranid AS TranId,
  'Bank Statement Matched' AS Activity,
  TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN TransactionLine tl ON tl.doc = t.id
  JOIN Transaction link_t ON link_t.id = tl.transaction
  JOIN SystemNote sn ON sn.recordid = link_t.id
WHERE
  t.recordtype = 'invoice'
  AND link_t.recordtype = 'customerpayment'
  AND sn.field = 'status'
  AND sn.after LIKE '%Deposited%'
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

UNION ALL

/* 13. Invoice Cleared (Final Status) */
SELECT
  t.tranid AS TranId,
  'Invoice Cleared' AS Activity,
  TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') AS DateCreated,
  'NetSuite' AS SourceSystem,
  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS LastDataUpdate,
  t.foreigntotal AS TotalAmount,
  BUILTIN.DF(t.entity) AS Entity,
  TO_CHAR(t.duedate, 'YYYY-MM-DD') AS DueDate,
  BUILTIN.DF(t.subsidiary) AS Subsidiary,
  BUILTIN.DF(t.salesrep) AS SalesRep,
  BUILTIN.DF(t.status) AS Status,
  BUILTIN.DF(t.department) AS Department
FROM
  Transaction t
  JOIN SystemNote sn ON sn.recordid = t.id
WHERE
  t.recordtype = 'invoice'
  AND sn.recordtypeid = -30
  AND sn.field = 'status'
  AND (sn.after LIKE '%Paid In Full%')
  AND t.trandate >= TO_DATE('2023-01-01', 'YYYY-MM-DD')

Steps

Prerequisites and Permissions Setup Ensure you have Administrator access to the NetSuite account. Navigate to Setup, Company, Enable Features. Under the SuiteTalk tab, ensure 'REST Web Services' is enabled. Under Setup, Integration, Manage Integrations, create a new integration named 'ProcessMining_Extractor'. Enable 'Token-based Authentication' and uncheck 'TBA: Authorization Flow' if using standard token generation. Note the Consumer Key and Consumer Secret.
Generate Access Tokens Navigate to Manage Access Tokens. Create a new token for the Administrator role (or a specific role with permissions to Transactions, System Notes, and Lists). Copy the Token ID and Token Secret. You now have the four keys required for OAuth 1.0a.
Prepare the Python Environment Ensure Python 3.x is installed. Install the necessary libraries using pip: pip install requests requests-oauthlib pandas. These libraries are required to handle the OAuth 1.0a handshake and data manipulation.
Configure the Script Parameters Open the provided Python script. Replace the placeholders for ACCOUNT_ID, CONSUMER_KEY, CONSUMER_SECRET, TOKEN_ID, and TOKEN_SECRET with your specific credentials. Set the TARGET_START_DATE to your desired historical analysis window (e.g., '2023-01-01').
Understand the API Strategy The script uses the NetSuite REST API /query/v1/suiteql endpoint. While it is possible to iterate through records individually, utilizing SuiteQL via the REST API is significantly more performant for process mining as it allows joining the Transaction, TransactionLine, Message, and SystemNote tables in fewer HTTP requests.
Execute the Extraction Script Run the script via your terminal or IDE. The script performs five distinct extraction passes: one for the main invoice flow, one for payments, one for credit memos, one for communication logs (messages), and one for system status changes (audit trail).
Monitor the Execution The script prints progress to the console, showing the number of records retrieved for each activity type. Watch for HTTP 400 or 500 errors which usually indicate permission issues or malformed SuiteQL syntax.
Data Transformation and Merging The script automatically handles the data transformation. It converts NetSuite's specific date format to a standard ISO 8601 timestamp. It also normalizes field names like tranid to Case ID and maps specific transaction types to the standardized Activity names defined in the requirements.
Handle Post-Processing Logic The script includes logic to differentiate between 'Partial Payment Posted' and 'Full Payment Received' by comparing the payment amount applied to the original invoice total. It also infers 'Invoice Cleared' based on the final status change.
Verify the Output File Upon completion, the script generates a file named netsuite_ar_event_log.csv. Open this file to inspect the first few rows. Verify that TranId is populated and DateCreated is in the correct format.
Import to ProcessMind The generated CSV is formatted specifically for ProcessMind. Map TranId to Case ID, Activity to Activity, and DateCreated to Timestamp. Ensure Amount and Status are mapped as case attributes.

Configuration

Authentication Method: OAuth 1.0a (Token Based Authentication). This is the mandatory standard for NetSuite REST API security.
API Endpoint: https://[AccountID].suitetalk.api.netsuite.com/services/rest/query/v1/suiteql. The SuiteQL endpoint is used within the REST architecture to enable complex filtering and joining of System Notes.
Concurrency Governance: NetSuite enforces concurrency limits based on your service tier (typically 5-20 concurrent threads). This script runs legally on a single thread to avoid '429 Too Many Requests' errors.
Date Formatting: NetSuite allows customizable date formats. The script assumes the standard ISO format or standard US format; you may need to adjust the date parsing if your instance uses DMY.
Query Limits: SuiteQL returns a maximum of 1,000 rows per page. The script implements a while loop with logic to handle hasMore flags and offsets/ranges to paginate through large datasets.
Permissions Required: The user role must have 'View' permissions for: Invoice, Customer Payment, Credit Memo, Journal Entry, Support Case, Message, and System Note.

a Sample Query python

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

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
class=class="hl-string">"hl-comment"># Replace these with your actual NetSuite credentials
ACCOUNT_ID = class="hl-string">'123456_SB1'  class=class="hl-string">"hl-comment"># Use hyphens for sandbox, e.g., 123456-sb1
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'
TARGET_START_DATE = class="hl-string">'2023-01-01'

class=class="hl-string">"hl-comment"># NetSuite REST API Base URL (Format account ID correctly for URL)
class=class="hl-string">"hl-comment"># For URL, usually lowercase and hyphens: 123456-sb1
NS_ACCOUNT_URL_PART = ACCOUNT_ID.lower().replace(class="hl-string">'_', class="hl-string">'-')
BASE_URL = fclass="hl-string">'https://{NS_ACCOUNT_URL_PART}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql'

class=class="hl-string">"hl-comment"># Authentication
auth = OAuth1(CONSUMER_KEY, CONSUMER_SECRET, TOKEN_ID, TOKEN_SECRET, realm=ACCOUNT_ID)
headers = {
    class="hl-string">'Prefer': class="hl-string">'transient',
    class="hl-string">'Content-Type': class="hl-string">'application/json'
}

class=class="hl-string">"hl-comment"># === HELPER FUNCTION ===
def run_suiteql(query):
    all_rows = []
    offset = 0
    limit = 1000
    has_more = True
    
    print(fclass="hl-string">"Executing Query starting with: {query[:50]}...")
    
    while has_more:
        class=class="hl-string">"hl-comment"># Note: Pagination in SuiteQL via REST often involves ROWNUM logic or limits
        class=class="hl-string">"hl-comment"># Standard REST paging links are preferred, but for SuiteQL body, we iterate logic
        class=class="hl-string">"hl-comment"># For simplicity in this script, we assume the query returns < 100k or we would implement ID-based iteration.
        class=class="hl-string">"hl-comment"># Ideally, we inject LIMIT/OFFSET if supported or filter by ID ranges.
        class=class="hl-string">"hl-comment"># Here we use a robust wrapper handling the class="hl-string">'items' response.
        
        class=class="hl-string">"hl-comment"># Adjust query for paging if necessary, or rely on requests if result set is small.
        class=class="hl-string">"hl-comment"># For production large scale, add class="hl-string">'AND rownum between X and Y' logic.
        
        payload = {class="hl-string">"q": query}
        response = requests.post(BASE_URL, auth=auth, headers=headers, json=payload)
        
        if response.status_code == 200:
            data = response.json()
            items = data.get(class="hl-string">'items', [])
            all_rows.extend(items)
            
            class=class="hl-string">"hl-comment"># Check for generic SuiteTalk pagination links
            if len(items) < limit:
                has_more = False
            else:
                class=class="hl-string">"hl-comment"># In strict SuiteQL, you must handle offset manually in SQL
                class=class="hl-string">"hl-comment"># For this script, we assume strict date filtering keeps batches manageable
                has_more = False class=class="hl-string">"hl-comment"># Preventing infinite loop in this demo template
        else:
            print(fclass="hl-string">"Error {response.status_code}: {response.text}")
            has_more = False
            
    return all_rows

class=class="hl-string">"hl-comment"># === EXTRACTION LOGIC ===

events = []

class=class="hl-string">"hl-comment"># 1. INVOICES (Created, Due Date, Attributes)
print(class="hl-string">"Extracting Invoices...")
q_invoices = fclass="hl-string">"""
    SELECT 
        t.tranid,
        'Invoice Created' as activity,
        TO_CHAR(t.trandate, 'YYYY-MM-DD HH24:MI:SS') as event_time,
        t.foreignTotal as amount,
        e.entityid as customer,
        t.duedate,
        s.name as subsidiary,
        emp.entityid as salesrep,
        t.status,
        d.name as department
    FROM 
        transaction t
        LEFT JOIN entity e ON t.entity = e.id
        LEFT JOIN subsidiary s ON t.subsidiary = s.id
        LEFT JOIN employee emp ON t.salesrep = emp.id
        LEFT JOIN department d ON t.department = d.id
    WHERE 
        t.recordtype = 'invoice'
        AND t.trandate >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
invoices = run_suiteql(q_invoices)
for row in invoices:
    events.append({
        class="hl-string">'TranId': row.get(class="hl-string">'tranid'),
        class="hl-string">'Activity': row.get(class="hl-string">'activity'),
        class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'),
        class="hl-string">'TotalAmount': row.get(class="hl-string">'amount'),
        class="hl-string">'Entity': row.get(class="hl-string">'customer'),
        class="hl-string">'DueDate': row.get(class="hl-string">'duedate'),
        class="hl-string">'Subsidiary': row.get(class="hl-string">'subsidiary'),
        class="hl-string">'SalesRep': row.get(class="hl-string">'salesrep'),
        class="hl-string">'Status': row.get(class="hl-string">'status'),
        class="hl-string">'Department': row.get(class="hl-string">'department'),
        class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
    })

class=class="hl-string">"hl-comment"># 2. PAYMENTS (Partial, Full, Bank Match)
print(class="hl-string">"Extracting Payments...")
q_payments = fclass="hl-string">"""
    SELECT 
        inv.tranid as invoice_num,
        t.tranid as payment_ref,
        TO_CHAR(t.trandate, 'YYYY-MM-DD HH24:MI:SS') as event_time,
        tl.foreignAmount as payment_amt,
        inv.foreignTotal as invoice_total,
        t.status as payment_status
    FROM 
        transaction t
        JOIN transactionLine tl ON t.id = tl.transaction
        JOIN transaction inv ON tl.createdfrom = inv.id
    WHERE 
        t.recordtype = 'customerpayment'
        AND inv.recordtype = 'invoice'
        AND t.trandate >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
payments = run_suiteql(q_payments)
for row in payments:
    class=class="hl-string">"hl-comment"># Logic to distinguish Partial vs Full
    act = class="hl-string">'Partial Payment Posted'
    class=class="hl-string">"hl-comment"># Simple heuristic: if remaining amount is small or payment equals total
    class=class="hl-string">"hl-comment"># In a real scenario, we would check class="hl-string">'amountremaining' on the invoice
    try:
        if float(row.get(class="hl-string">'payment_amt', 0)) >= float(row.get(class="hl-string">'invoice_total', 0)):
            act = class="hl-string">'Full Payment Received'
    except:
        pass
        
    events.append({
        class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
        class="hl-string">'Activity': act,
        class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'),
        class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
    })
    
    class=class="hl-string">"hl-comment"># Bank Match / Cleared Check
    class=class="hl-string">"hl-comment"># Assuming class="hl-string">'deposited' status or similar indicates bank matching
    if row.get(class="hl-string">'payment_status') == class="hl-string">'deposited':
        events.append({
            class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
            class="hl-string">'Activity': class="hl-string">'Bank Statement Matched',
            class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'), class=class="hl-string">"hl-comment"># Using payment date as proxy if match date unavailable
            class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
        })

class=class="hl-string">"hl-comment"># 3. CREDIT MEMOS & BAD DEBT
print(class="hl-string">"Extracting Credit Memos...")
q_credits = fclass="hl-string">"""
    SELECT 
        inv.tranid as invoice_num,
        t.tranid as credit_ref,
        t.recordtype,
        TO_CHAR(t.trandate, 'YYYY-MM-DD HH24:MI:SS') as event_time,
        t.memo
    FROM 
        transaction t
        JOIN transactionLine tl ON t.id = tl.transaction
        JOIN transaction inv ON tl.createdfrom = inv.id
    WHERE 
        t.recordtype IN ('creditmemo', 'journalentry')
        AND inv.recordtype = 'invoice'
        AND t.trandate >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
credits = run_suiteql(q_credits)
for row in credits:
    act = class="hl-string">'Credit Memo Issued'
    if class="hl-string">'write off' in str(row.get(class="hl-string">'memo')).lower() or class="hl-string">'bad debt' in str(row.get(class="hl-string">'memo')).lower():
        act = class="hl-string">'Bad Debt Written Off'
        
    events.append({
        class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
        class="hl-string">'Activity': act,
        class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'),
        class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
    })

class=class="hl-string">"hl-comment"># 4. MESSAGES (Dispatch, Reminders)
print(class="hl-string">"Extracting Messages...")
q_msgs = fclass="hl-string">"""
    SELECT 
        t.tranid as invoice_num,
        m.subject,
        TO_CHAR(m.messagedate, 'YYYY-MM-DD HH24:MI:SS') as event_time,
        m.authoremail
    FROM 
        message m
        JOIN transaction t ON m.transaction = t.id
    WHERE 
        t.recordtype = 'invoice'
        AND m.messagedate >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
msgs = run_suiteql(q_msgs)
for row in msgs:
    subj = str(row.get(class="hl-string">'subject')).lower()
    act = None
    if class="hl-string">'invoice' in subj and class="hl-string">'attached' in subj:
        act = class="hl-string">'Invoice Dispatched'
    elif class="hl-string">'reminder' in subj or class="hl-string">'overdue' in subj:
        act = class="hl-string">'Payment Reminder Sent'
        
    if act:
        events.append({
            class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
            class="hl-string">'Activity': act,
            class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'),
            class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
        })

class=class="hl-string">"hl-comment"># 5. SUPPORT CASES (Disputes)
print(class="hl-string">"Extracting Support Cases...")
class=class="hl-string">"hl-comment"># Requires linking Case to Invoice (often custom field or text match)
class=class="hl-string">"hl-comment"># Here assuming generic link via Customer + Time logic or direct field if exists
class=class="hl-string">"hl-comment"># Using standard support case table
q_cases = fclass="hl-string">"""
    SELECT 
        c.casenumber,
        c.title,
        c.status,
        TO_CHAR(c.createddate, 'YYYY-MM-DD HH24:MI:SS') as create_time,
        TO_CHAR(c.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SS') as close_time,
        t.tranid as invoice_num
    FROM 
        supportcase c
        JOIN transaction t ON c.company = t.entity -- Loose join by customer
    WHERE 
        t.recordtype = 'invoice'
        AND (c.title LIKE '%' || t.tranid || '%' OR c.incomingmessage LIKE '%' || t.tranid || '%')
        AND c.createddate >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
cases = run_suiteql(q_cases)
for row in cases:
    class=class="hl-string">"hl-comment"># Dispute Opened
    events.append({
        class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
        class="hl-string">'Activity': class="hl-string">'Dispute Case Opened',
        class="hl-string">'DateCreated': row.get(class="hl-string">'create_time'),
        class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
    })
    class=class="hl-string">"hl-comment"># Dispute Resolved (if closed)
    if class="hl-string">'closed' in str(row.get(class="hl-string">'status')).lower():
        events.append({
            class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'),
            class="hl-string">'Activity': class="hl-string">'Dispute Resolved',
            class="hl-string">'DateCreated': row.get(class="hl-string">'close_time'),
            class="hl-string">'SourceSystem': class="hl-string">'NetSuite'
        })

class=class="hl-string">"hl-comment"># 6. SYSTEM NOTES (Approvals, Status Changes, Cleared)
print(class="hl-string">"Extracting System Notes...")
class=class="hl-string">"hl-comment"># This query can be heavy. Ensure proper filtering.
q_notes = fclass="hl-string">"""
    SELECT 
        t.tranid as invoice_num,
        sn.field,
        sn.oldvalue,
        sn.newvalue,
        TO_CHAR(sn.date, 'YYYY-MM-DD HH24:MI:SS') as event_time
    FROM 
        systemnote sn
        JOIN transaction t ON sn.recordid = t.id
    WHERE 
        sn.recordtypeid = -30 -- Transaction Record Type ID usually -30, verify in setup
        AND t.recordtype = 'invoice'
        AND sn.date >= TO_DATE('{TARGET_START_DATE}', 'YYYY-MM-DD')
"""
notes = run_suiteql(q_notes)
for row in notes:
    field = str(row.get(class="hl-string">'field')).lower()
    old_v = str(row.get(class="hl-string">'oldvalue')).lower()
    new_v = str(row.get(class="hl-string">'newvalue')).lower()
    
    if class="hl-string">'status' in field:
        if class="hl-string">'pending approval' in old_v and class="hl-string">'open' in new_v:
            events.append({class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'), class="hl-string">'Activity': class="hl-string">'Invoice Approved', class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'), class="hl-string">'SourceSystem': class="hl-string">'NetSuite'})
        elif class="hl-string">'paid in full' in new_v:
            events.append({class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'), class="hl-string">'Activity': class="hl-string">'Invoice Cleared', class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'), class="hl-string">'SourceSystem': class="hl-string">'NetSuite'})
            
    class=class="hl-string">"hl-comment"># Promise to Pay check (User Notes often appear in System Notes or separate Note table)
    class=class="hl-string">"hl-comment"># Assuming Note capture via System Note for context
    if class="hl-string">'note' in field: 
        events.append({class="hl-string">'TranId': row.get(class="hl-string">'invoice_num'), class="hl-string">'Activity': class="hl-string">'Promise to Pay Received', class="hl-string">'DateCreated': row.get(class="hl-string">'event_time'), class="hl-string">'SourceSystem': class="hl-string">'NetSuite'})

class=class="hl-string">"hl-comment"># === EXPORT ===
print(class="hl-string">"Generating CSV...")
df = pd.DataFrame(events)

class=class="hl-string">"hl-comment"># Deduplicate and Sort
df.drop_duplicates(inplace=True)
df[class="hl-string">'LastDataUpdate'] = datetime.now().isoformat()

class=class="hl-string">"hl-comment"># Save
df.to_csv(class="hl-string">'netsuite_ar_event_log.csv', index=False)
print(class="hl-string">"Done. File saved as netsuite_ar_event_log.csv")

Your Accounts Receivable Data Template

Your Accounts Receivable Data Template

Accounts Receivable Attributes

Accounts Receivable Activities

Extraction Guides

NetSuite SuiteAnalytics Workbook Data Export

Steps

Configuration

a Sample Query config

NetSuite SuiteQL Transaction Data Extraction

Steps

Configuration

a Sample Query sql

Python SuiteTalk REST API Extraction Script

Steps

Configuration

a Sample Query python

Ready to Get Started?

Accelerate Your NetSuite Accounts Receivable Cycle Now

Your Accounts Receivable Data Template

Your Accounts Receivable Data Template

Accounts Receivable Attributes

Accounts Receivable Activities

Extraction Guides

NetSuite SuiteAnalytics Workbook Data Export

Steps

Configuration

a Sample Query config

NetSuite SuiteQL Transaction Data Extraction

Steps

Configuration

a Sample Query sql

Python SuiteTalk REST API Extraction Script

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Accelerate Your NetSuite Accounts Receivable Cycle Now