> Continuous Process Improvement > Credit Management & Collections > NetSuite > Data Template

Your Credit Management & Collections Data Template

NetSuite

Credit Management & Collections Attributes (21) Credit Management & Collections Activities (13) Extraction Guides (2)

Your Credit Management & Collections Data Template

This template provides a comprehensive guide to collecting the right data for analyzing your Credit Management & Collections process. It outlines the essential attributes and activities, along with practical extraction guidance, to help you get started quickly. By following these recommendations, you can ensure your data is optimized for insightful process analysis and improvement.

Recommended attributes to collect
Key activities to track
Extraction guidance for NetSuite

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

Download Template

Credit Management & Collections Attributes

These are the recommended data fields to include in your event log for a thorough analysis of your credit management and collections process.

3 Required 5 Recommended 13 Optional

	Name	Description
	Activity Name ActivityName	The name of the business activity that occurred at a specific point in time.
Description This attribute describes a specific step or event in the credit and collections process, such as 'Invoice Generated', 'Payment Received', or 'Dispute Registered'. Analyzing the sequence and frequency of these activities is the core of process mining, revealing the actual process flow, deviations, and bottlenecks. Why it matters This attribute forms the backbone of the process map, allowing for the visualization and analysis of the process flow. Where to get This field is typically derived from the status changes of transaction records (e.g., Invoice, Customer Payment) or from related records like system notes, custom records for collections activities, or CRM tasks. Examples Invoice GeneratedOverdue Reminder SentPayment ReceivedInvoice Written Off
	Event Time EventTime	The timestamp indicating when the activity occurred.
Description This attribute captures the precise date and time of each activity in the process. It is fundamental for all time-based analysis, including calculating cycle times, durations, and waiting times between steps. In NetSuite, this timestamp is usually the creation or last modified date of the relevant record or system note. Why it matters It enables the calculation of all performance metrics related to time, such as cycle times and bottlenecks, which are critical for efficiency analysis. Where to get This is sourced from date fields on various records. For example, the 'Date Created' of an Invoice record for 'Invoice Generated', or the timestamp from a System Note for status changes. Examples 2023-01-15T10:00:00Z2023-02-01T14:30:00Z2023-02-15T09:05:00Z
	Invoice Number InvoiceNumber	The unique identifier for each customer invoice.
Description The Invoice Number serves as the primary case identifier, uniquely linking all activities related to a specific receivable. This allows for a comprehensive analysis of the entire lifecycle, from the invoice's creation and subsequent credit management actions to its eventual payment or resolution through collection efforts. In NetSuite, this typically corresponds to the Transaction ID when the transaction type is 'Invoice'. Why it matters It is the essential key to trace an invoice's journey from generation to settlement, enabling end-to-end process analysis. Where to get This is typically the 'Transaction ID' (internal ID: tranid) field on the Invoice record in NetSuite. Examples INV0012345INV0054321INV0098765
	Customer ID CustomerId	The unique identifier for the customer associated with the invoice.
Description This attribute links an invoice to a specific customer entity in NetSuite. It is essential for segmenting the data to analyze process performance across different customers or customer groups. For example, it enables the analysis of payment behaviors or dispute rates for strategic accounts versus small businesses. Why it matters Allows for customer-centric analysis, helping to identify which customers are frequently late, raise disputes, or follow non-standard processes. Where to get This is the 'Customer' (internal ID: entity) field on the Invoice transaction record. Examples CUST001CUST002CUST003
	Customer Segment CustomerSegment	The classification of the customer, such as by size, industry, or region.
Description Customer Segment categorizes customers into meaningful groups (e.g., 'Enterprise', 'SMB', 'Public Sector'). This attribute is vital for the 'Collection Strategy Performance by Segment' dashboard, as it allows for comparing the effectiveness of different collection approaches across these segments. It helps tailor strategies to the unique behaviors of each group. Why it matters Enables targeted analysis to determine if certain process issues or payment behaviors are concentrated in specific customer groups. Where to get This is often a custom field on the Customer record in NetSuite. The data needs to be joined from the Customer record to the Invoice transaction. Examples EnterpriseMid-MarketSMB
	Due Date DueDate	The date by which the invoice payment is due.
Description The Due Date is the contractually agreed-upon date for payment. It is the baseline for determining if a payment is on-time or overdue. This attribute is essential for calculating KPIs like 'On-Time Payment Rate' and for triggering dunning activities when the date is passed. Why it matters Acts as the primary benchmark for measuring payment timeliness and is crucial for calculating days overdue and monitoring payment term compliance. Where to get This is the 'Due Date' (internal ID: duedate) field on the Invoice transaction record. Examples 2023-02-142023-03-312023-04-15
	Invoice Amount InvoiceAmount	The total monetary value of the invoice.
Description Represents the total amount due on the invoice. This is a critical financial attribute used in nearly all analyses, such as identifying the financial impact of late payments or analyzing write-offs for high-value invoices. It provides essential context for prioritizing collection efforts. Why it matters Provides financial context to the process, allowing for analysis based on monetary value, such as prioritizing collections for high-value overdue invoices. Where to get This is the 'Amount (Total)' (internal ID: total) field on the Invoice transaction record. Examples 1500.00250.5012500.75
	Invoice Status InvoiceStatus	The current status of the invoice in its lifecycle.
Description Indicates the current state of the invoice, such as 'Open', 'Paid In Full', or 'Overdue'. This is useful for filtering and analyzing only the invoices that are currently active or problematic. The 'Overdue Invoices Aging and Status' dashboard relies on this attribute to provide a snapshot of outstanding receivables. Why it matters Allows for filtering and focusing analysis on specific stages of the invoice lifecycle, such as open, overdue, or disputed invoices. Where to get This corresponds to the 'Status' (internal ID: status) field on the Invoice transaction record. Examples OpenPaid In FullOverdue
	Business Unit BusinessUnit	The business unit or subsidiary associated with the transaction.
Description Represents the specific business unit, department, or subsidiary that issued the invoice. In NetSuite OneWorld accounts, this typically corresponds to the 'Subsidiary' field. It enables process analysis to be segmented by different parts of the organization, allowing for performance comparisons and identification of division-specific issues. Why it matters Allows for process performance to be compared across different organizational units, revealing variations that may require different management approaches. Where to get In NetSuite OneWorld, this is the 'Subsidiary' field. In other accounts, it could be 'Department' or 'Class' depending on configuration. Examples US WestEMEA ServicesAPAC Products
	Collector Name CollectorName	The name of the collections agent assigned to the invoice.
Description Identifies the specific collections agent or resource responsible for managing an overdue invoice. This allows for performance analysis across the collections team and helps in understanding workload distribution and the effectiveness of individual collectors. It supports KPIs like 'Collections Contact Success Rate'. Why it matters Enables performance measurement and workload balancing for the collections team, linking collection activities to specific individuals. Where to get This is often a custom field on the Customer or Invoice record. Some NetSuite bundles for collections add this functionality. Examples Alice JohnsonRobert WilliamsUnassigned
	Country Country	The country of the customer's billing address.
Description This attribute indicates the country associated with the customer's billing address on the invoice. It is used to analyze geographical variations in payment behavior, collection effectiveness, and dispute rates. This can help in tailoring collection strategies to specific regions and understanding the impact of local economic conditions. Why it matters Enables geographical segmentation of the process, which can highlight regional differences in payment behavior or process efficiency. Where to get This is sourced from the 'Billing Address' details on the Invoice transaction, which is typically inherited from the Customer record. Examples USAGermanyJapan
	Days Overdue DaysOverdue	The number of days an invoice is past its due date.
Description This calculated metric measures the number of days between the invoice's due date and the current date (for open invoices) or the payment date. It is a fundamental KPI for collections, used in the 'Overdue Invoices Aging and Status' dashboard to categorize and prioritize collection efforts. A value of zero or less indicates the invoice is not overdue. Why it matters Directly measures the severity of payment delays and is a key metric for aging reports and prioritizing collections activities. Where to get This is calculated during data transformation: (Current Date or Payment Date) - Due Date. Examples 1532090
	Dispute Reason DisputeReason	The reason provided for a customer dispute on an invoice.
Description When a customer disputes an invoice, this attribute captures the reason, such as 'Incorrect Pricing', 'Damaged Goods', or 'Duplicate Billing'. It is critical for the 'Invoice Dispute Resolution Time' and 'Invoice Write-Off Root Cause Analysis' dashboards to understand why disputes occur and identify patterns that can be addressed to prevent future issues. Why it matters Helps identify the root causes of customer disputes, providing insights needed to improve upstream processes like order fulfillment or billing accuracy. Where to get This is likely a custom field on the Invoice record or a related custom record used to track dispute cases. It is not a standard NetSuite field. Examples Incorrect QuantityPrice DiscrepancyDamaged Goods
	Invoice To Cash Cycle Time InvoiceToCashCycleTime	The total time from invoice generation to final settlement.
Description This calculated metric measures the total duration from the 'Invoice Generated' activity to the 'Invoice Settled' activity. It represents the end-to-end performance of the entire credit and collections process. Analyzing this KPI helps identify systemic delays and provides a high-level measure of cash flow velocity. Why it matters This is a critical, high-level KPI that measures the overall efficiency of the entire invoice-to-cash process. Where to get This is a case-level calculation performed in the process mining tool: Timestamp('Invoice Settled') - Timestamp('Invoice Generated'). Examples 32 days45 days91 days
	Is Automated IsAutomated	A flag indicating if an activity was performed automatically by a system.
Description This boolean attribute specifies whether an activity was executed by an automated workflow or a human user. For example, an 'Overdue Reminder Sent' activity could be automated, while a 'Collection Call Made' is manual. This is essential for the 'Manual Activity Frequency and Impact' dashboard to quantify automation levels and identify opportunities for further automation. Why it matters Helps quantify the level of automation in the process, supporting initiatives to reduce manual effort and improve efficiency. Where to get This is a derived attribute based on the activity type or the user who performed it. For example, if the user is a designated 'SYSTEM' user, the activity is flagged as automated. Examples truefalse
	Is On Time Payment IsOnTimePayment	A flag indicating if payment was received on or before the due date.
Description This calculated boolean attribute checks if the 'Payment Received' activity occurred on or before the invoice's 'Due Date'. It simplifies the calculation of the 'On-Time Payment Rate' KPI and allows for easy filtering and analysis of on-time versus late payments. It provides a clear, binary outcome for payment performance. Why it matters Provides a simple, binary metric for assessing payment term compliance, making it easy to calculate and benchmark the On-Time Payment Rate. Where to get This is calculated during data transformation: TRUE if (Payment Date <= Due Date), otherwise FALSE. Examples truefalse
	Last Data Update LastDataUpdate	The timestamp of the last data refresh from the source system.
Description This attribute indicates when the data for this process was last extracted and updated in the process mining tool. It helps users understand the freshness of the data they are analyzing and ensures they are making decisions based on current information. Why it matters Provides transparency about data currency, allowing users to know how up-to-date the process analysis is. Where to get This value is generated and stamped onto the dataset at the time of data extraction. Examples 2023-10-27T02:00:00Z
	Payment Posting Lag PaymentPostingLag	The time between receiving a payment and posting it in the system.
Description This calculated duration measures the delay between the 'Payment Received' activity (when the funds are received) and the 'Payment Posted' activity (when the payment is applied to the invoice in NetSuite). This is a key metric for the 'Payment Posting Lag Analysis' dashboard, highlighting internal inefficiencies in cash application that can distort cash visibility. Why it matters Reveals internal processing delays in cash application, which can impact financial reporting accuracy and customer account statuses. Where to get This is calculated in the process mining tool: Timestamp('Payment Posted') - Timestamp('Payment Received'). Examples 1 day4 hours3 days
	Payment Terms PaymentTerms	The payment terms agreed upon with the customer for the invoice.
Description This attribute specifies the conditions under which a customer is expected to pay an invoice, such as 'Net 30' or 'Net 60'. It is the basis for calculating the invoice's Due Date and is fundamental for the 'Payment Terms Compliance and Deviations' dashboard, which analyzes if customers are adhering to their agreed terms. Why it matters Provides the contractual basis for payment schedules and is essential for analyzing compliance and identifying deviations that impact cash flow. Where to get This is the 'Terms' (internal ID: terms) field on the Invoice transaction record. Examples Net 30Net 60Due on receipt
	Source System SourceSystem	The system from which the data was extracted.
Description Identifies the originating system for the event data. For this process view, the value will consistently be 'NetSuite', but it is a mandatory attribute for data governance and to distinguish data sources in a multi-system environment. Why it matters Ensures data lineage and traceability, which is important for data validation and governance, especially in environments with multiple integrated systems. Where to get This is a static value ('NetSuite') added during the data transformation process. Examples NetSuite
	User ID UserId	The identifier of the user who performed the activity.
Description Identifies the employee or system user responsible for a particular activity, such as posting a payment or making a collection call. This allows for analysis of workload distribution, team performance, and identification of training needs. For automated activities, this might be a system user ID. Why it matters Enables analysis of performance by user or team and helps in understanding workload distribution and identifying where manual interventions occur. Where to get Sourced from fields like 'Created By', 'Modified By', or a specific 'Owner' field on transactions or related records (e.g., Phone Call record for collection calls). Examples j.doea.smithSYSTEM

Required Recommended Optional

Credit Management & Collections Activities

These are the crucial process steps and milestones to capture in your event log for accurate discovery and optimization of your collections workflow.

5 Recommended 8 Optional

	Activity	Description
	Invoice Generated	Marks the official creation of the invoice record in the system. This event is captured from the creation date of the Invoice transaction in NetSuite, serving as the starting point for the invoice's lifecycle.
Why it matters This is the primary start event for the Invoice-to-Cash process. It is essential for calculating key metrics like Days Sales Outstanding (DSO) and the overall Invoice-to-Cash Cycle Time. Where to get This is an explicit event captured from the creation date of the Invoice transaction record in NetSuite (Transaction table where Type is 'Invoice'). Capture Use the creation timestamp of the invoice transaction record. Event type explicit
	Invoice Settled	The successful completion of the invoice lifecycle, where the balance due is reduced to zero through payments and any applied credits. This is inferred when the invoice status changes to 'Paid In Full'.
Why it matters This is the primary successful end event for the process. It is the final data point for calculating the end-to-end Invoice-to-Cash Cycle Time. Where to get Inferred from the 'Status' field on the Invoice transaction changing to 'Paid In Full'. The timestamp can be taken from the date of the final payment or credit memo that closed the invoice. Capture Timestamp of the last payment application that sets status to 'Paid In Full'. Event type inferred
	Invoice Written Off	An unsuccessful end to the invoice lifecycle, where the debt is deemed uncollectible and removed from accounts receivable. This is captured when a journal entry for bad debt is applied to the invoice.
Why it matters This is a critical failure endpoint. Analyzing the paths that lead to write-offs helps identify weaknesses in credit policy and collections strategy, ultimately reducing bad debt. Where to get Inferred from the application of a 'Journal Entry' to the invoice, where the journal entry is specifically for a bad debt write-off. A custom status may also be used. Capture Use the date of the write-off Journal Entry applied to the invoice. Event type inferred
	Payment Due Date Passed	A calculated event that occurs when the current date surpasses the invoice's due date without the invoice being fully paid. It signifies the transition of an invoice from 'current' to 'overdue' status.
Why it matters This event is a critical trigger for all collections activities. Analyzing the time after this event is key to understanding collections efficiency and dunning effectiveness. Where to get This is a calculated event. It is derived by comparing the current date to the 'Due Date' field on the Invoice transaction record. The event timestamp is the Due Date itself. Capture Timestamp is the value of the 'Due Date' field on the invoice. Event type calculated
	Payment Received	This event marks when a customer payment is created and applied to an invoice. It is a critical milestone captured directly from the Customer Payment transaction record in NetSuite.
Why it matters This activity is crucial for calculating DSO and On-Time Payment Rate. It signifies the receipt of cash and is a key measure of collections success. Where to get Captured from the creation date of the 'Customer Payment' transaction record. The 'Apply' sublist on this record links the payment to one or more invoices. Capture Use the transaction date of the Customer Payment record. Event type explicit
	Collection Call Made	A manual collection activity where a collections agent contacts the customer by phone. This event is captured from user-created Phone Call activity records in NetSuite's CRM module.
Why it matters This activity helps quantify manual effort in the collections process. Analyzing its frequency and success rate is important for optimizing resource allocation and automation. Where to get Captured from the creation date of a 'Phone Call' activity record where the related record is the customer, and the invoice number is referenced in the message. Capture Use the timestamp from the Phone Call CRM activity record. Event type explicit
	Credit Memo Issued	Represents the creation of a credit memo that is applied against the invoice, often to correct an error or as part of a dispute resolution. This is an explicit transaction in NetSuite.
Why it matters Frequent credit memos can indicate systemic issues such as pricing errors or shipping problems. Analyzing their occurrence helps identify root causes for revenue leakage. Where to get This is an explicit event captured from the creation date of the Credit Memo transaction record that is linked to the original invoice. Capture Use the creation timestamp of the Credit Memo transaction. Event type explicit
	Dispute Registered	Indicates that a customer has formally disputed the invoice, pausing the standard collections process. This is typically inferred from a change in the invoice's status field to a 'Disputed' state.
Why it matters This event is the starting point for measuring dispute resolution time. Identifying frequent disputes can reveal underlying issues in the billing or sales process. Where to get Inferred from a system log or a timestamped change of a custom 'Invoice Status' field to a value like 'Disputed' or 'In Dispute'. Capture Timestamp when a status field is updated to reflect a dispute. Event type inferred
	Dispute Resolved	Marks the resolution of a customer's dispute, allowing the collections process to resume or the invoice to be settled. This is inferred when the invoice status is changed from 'Disputed' back to an active state.
Why it matters This is the endpoint for measuring dispute resolution cycle time. Efficient dispute resolution is crucial for maintaining customer satisfaction and accelerating cash flow. Where to get Inferred from a system log or a timestamped change of a custom 'Invoice Status' field from 'Disputed' to a value like 'Open' or 'Resolved'. Capture Timestamp when a status field is updated to show resolution. Event type inferred
	Dunning Procedure Initiated	Represents the start of a formal dunning or collections process for an overdue invoice, which may involve multiple steps. This is captured when a specific dunning level is assigned to the invoice.
Why it matters Tracking the initiation of formal dunning helps measure dunning adherence and effectiveness. It is a key milestone for analyzing how different dunning strategies influence payment behavior. Where to get Inferred from the date the 'Dunning Level' on the invoice or customer record is first set to a non-zero or initial dunning state. Capture Identify the timestamp when the Dunning Level field changes. Event type inferred
	Invoice Sent to Customer	Represents the point at which the invoice has been communicated to the customer, typically via email. This is often inferred from system logs that track outbound communications related to the invoice transaction.
Why it matters Tracking this activity helps identify delays between invoice creation and customer notification, which can impact payment times. It provides insight into the efficiency of the billing communication process. Where to get Inferred from the date of an email sent from the invoice record, found on the Communication > Messages subtab of the Invoice transaction. Capture Use the timestamp of the 'Message' record linked to the invoice. Event type inferred
	Overdue Reminder Sent	An initial, often automated, reminder is sent to the customer regarding an overdue invoice. This activity is typically captured from the creation of a dunning letter or a logged communication event in NetSuite's dunning module.
Why it matters This activity is the first step in the dunning process. Analyzing its frequency and impact on payment helps in evaluating the effectiveness of initial collection efforts. Where to get Captured from the creation date of a 'Dunning Letter' record associated with the invoice, generated by the NetSuite Dunning SuiteApp. Capture Use the creation timestamp of the Dunning Letter record. Event type explicit
	Payment Posted	Represents the date when the financial impact of the payment is officially recorded in the General Ledger. This is captured from the posting date of the Customer Payment transaction.
Why it matters The time between 'Payment Received' and 'Payment Posted' reveals administrative delays. Analyzing this lag is important for ensuring accurate and timely financial reporting. Where to get Captured from the 'Posting Period' or transaction date on the General Ledger impact of the 'Customer Payment' transaction. Capture Use the GL posting date from the Customer Payment transaction. Event type explicit

Recommended Optional

Extraction Guides

How to get your data from NetSuite

Steps

Log into NetSuite: Access your NetSuite account with a role that has permissions to create and run Saved Searches, as well as view the required transaction and activity records (e.g., Invoice, Customer Payment, Phone Call).
Navigate to Saved Searches: Go to Reports > Saved Searches > All Saved Searches > New. You will repeat this process to create several distinct searches, one for each type of event.
Create the Base Invoice Search: Start by creating a search for the 'Invoice Generated' event. Select 'Transaction' as the search type. This search will capture the creation of the invoice.
Define Search Criteria: In the 'Criteria' subtab, set the following filters:
- Type is Invoice.
- Main Line is true. This ensures you get one record per invoice, not one per line item.
- Date Created is within your desired analysis period, for example, the last 6 months.
Define Search Results: In the 'Results' subtab, add the columns needed for the event log. Use custom labels to match the required attribute names (e.g., InvoiceNumber). Use formulas to set the ActivityName field. The detailed field mappings are provided in the query section.
Create Searches for Other Events: Following the configurations in the query section, create separate saved searches for other activities like Payments, Credit Memos, Collection Calls, and status changes. These might use different record types like 'Phone Call' or require searching 'System Notes'.
Execute and Export Searches: Run each saved search individually. From the results page, click the 'Export - CSV' icon to download the data. Name each file descriptively (e.g., invoice_generated.csv, payment_received.csv).
Generate 'Payment Due Date Passed' Events: This specific event is not directly exported. It must be calculated after exporting the data. In your preferred data manipulation tool (like Python, Power Query, or an ETL tool), for each invoice that is not yet fully paid, create a row with ActivityName as 'Payment Due Date Passed' and EventTime as the DueDate plus one day.
Combine Exported Files: Use an external tool to stack all the exported CSV files into a single master file. Ensure the column headers are identical across all files before combining.
Final Data Formatting: Review the combined CSV file. Check that EventTime is in a consistent timestamp format (e.g., YYYY-MM-DD HH:MI:SS) and that all required columns (InvoiceNumber, ActivityName, EventTime) are present for every row.
Upload to ProcessMind: The final, combined CSV file is now ready to be uploaded as your event log.

Configuration

Prerequisites: A user role with permissions to create, edit, and export Saved Searches is required. Access to the following record types is necessary: Transaction (for Invoices, Payments, Credit Memos), Phone Call (or other CRM Activity records), and System Notes.
Record Types: The primary record types used in these searches are Transaction and Phone Call. The System Note record is also used to capture status changes and communication events where direct transaction records are not available.
Date Range Filtering: To ensure performance and manage data volume, consistently apply a date filter (e.g., Date Created or Last Modified Date) across all saved searches. A period of 3 to 6 months of data is recommended for an initial analysis.
Main Line Filter: When searching transaction records, use the criterion Main Line is true to isolate header-level information. This prevents duplicating events for each line item on an invoice or payment.
Using Formulas: NetSuite formulas are essential for creating the standardized ActivityName column. Formula (Text) fields allow you to hardcode the activity name for each search, and Formula (Date/Time) can be used to standardize timestamp fields if needed.
Export Limits: Be aware that NetSuite's UI-based CSV export is typically limited to 100,000 rows per search. For larger datasets, you may need to run searches in smaller date chunks or use scheduled searches that deliver results to the File Cabinet.

a Sample Query config

/*
This configuration outlines the setup for multiple NetSuite Saved Searches. 
Each search is designed to extract a specific activity for the event log.
Create one Saved Search in the NetSuite UI for each block defined below.
*/

// Saved Search 1: Invoice Generated
{
  "SavedSearchName": "ProcessMind - Invoice Generated",
  "RecordType": "Transaction",
  "Criteria": [
    {"Field": "Type", "Operator": "is", "Value": "Invoice"},
    {"Field": "Main Line", "Operator": "is", "Value": "true"},
    {"Field": "Date Created", "Operator": "within", "Value": "[Your Date Range]"}
  ],
  "Results": [
    {"Column": "ActivityName", "Field": "Formula (Text)", "Formula": "'Invoice Generated'"},
    {"Column": "EventTime", "Field": "Date Created"},
    {"Column": "InvoiceNumber", "Field": "Document Number"},
    {"Column": "CustomerId", "Field": "Customer : Internal ID"},
    {"Column": "CustomerSegment", "Field": "Customer : Category"},
    {"Column": "InvoiceAmount", "Field": "Amount (Gross)"},
    {"Column": "DueDate", "Field": "Due Date"},
    {"Column": "InvoiceStatus", "Field": "Status"}
  ]
}

// Saved Search 2: Invoice Sent to Customer
{
  "SavedSearchName": "ProcessMind - Invoice Sent",
  "RecordType": "Transaction",
  "Criteria": [
    {"Field": "Type", "Operator": "is", "Value": "Invoice"},
    {"Field": "Main Line", "Operator": "is", "Value": "true"},
    {"Field": "System Notes : Field", "Operator": "is", "Value": "E-mailed"},
    {"Field": "System Notes : Date", "Operator": "within", "Value": "[Your Date Range]"}
  ],
  "Results": [
    {"Column": "ActivityName", "Field": "Formula (Text)", "Formula": "'Invoice Sent to Customer'"},
    {"Column": "EventTime", "Field": "System Notes : Date"},
    {"Column": "InvoiceNumber", "Field": "Document Number"},
    {"Column": "CustomerId", "Field": "Customer : Internal ID"},
    {"Column": "CustomerSegment", "Field": "Customer : Category"},
    {"Column": "InvoiceAmount", "Field": "Amount (Gross)"},
    {"Column": "DueDate", "Field": "Due Date"},
    {"Column": "InvoiceStatus", "Field": "Status"}
  ]
}

// Note: 'Payment Due Date Passed' is a calculated event, generated post-export.

// Saved Search 3: Overdue Reminder Sent & Dunning Procedure Initiated
{
  "SavedSearchName": "ProcessMind - Dunning Events",
  "RecordType": "Transaction",
  "Criteria": [
    {"Field": "Type", "Operator": "is", "Value": "Invoice"},
    {"Field": "Main Line", "Operator": "is", "Value": "true"},
    {"Field": "System Notes : Field", "Operator": "is", "Value": "[Your Dunning Status Field]"},
    {"Field": "System Notes : Date", "Operator": "within", "Value": "[Your Date Range]"}
  ],
  "Results": [
    {"Column": "ActivityName", "Field": "Formula (Text)", "Formula": "CASE WHEN {systemnotes.newvalue} = '[Your First Dunning Level]' THEN 'Dunning Procedure Initiated' ELSE 'Overdue Reminder Sent' END"},
    {"Column": "EventTime", "Field": "System Notes : Date"},
    {"Column": "InvoiceNumber", "Field": "Document Number"},
    {"Column": "CustomerId", "Field": "Customer : Internal ID"},
    {"Column": "CustomerSegment", "Field": "Customer : Category"},
    {"Column": "InvoiceAmount", "Field": "Amount (Gross)"},
    {"Column": "DueDate", "Field": "Due Date"},
    {"Column": "InvoiceStatus", "Field": "Status"}
  ]
}

// Saved Search 4: Collection Call Made
{
  "SavedSearchName": "ProcessMind - Collection Calls",
  "RecordType": "Phone Call",
  "Criteria": [
    {"Field": "Transaction : Type", "Operator": "is", "Value": "Invoice"},
    {"Field": "Date", "Operator": "within", "Value": "[Your Date Range]"}
  ],
  "Results": [
    {"Column": "ActivityName", "Field": "Formula (Text)", "Formula": "'Collection Call Made'"},
    {"Column": "EventTime", "Field": "Completed Date"},
    {"Column": "InvoiceNumber", "Field": "Transaction : Document Number"},
    {"Column": "CustomerId", "Field": "Company : Internal ID"},
    {"Column": "CustomerSegment", "Field": "Company : Category"},
    {"Column": "InvoiceAmount", "Field": "Transaction : Amount (Gross)"},
    {"Column": "DueDate", "Field": "Transaction : Due Date"},
    {"Column": "InvoiceStatus", "Field": "Transaction : Status"}
  ]
}

// Saved Search 5: Dispute Registered & Resolved
{
  "SavedSearchName": "ProcessMind - Dispute Status",
  "RecordType": "Transaction",
  "Criteria": [
    {"Field": "Type", "Operator": "is", "Value": "Invoice"},
    {"Field": "Main Line", "Operator": "is", "Value": "true"},
    {"Field": "System Notes : Field", "Operator": "is", "Value": "Status"},
    {"Field": "System Notes : Date", "Operator": "within", "Value": "[Your Date Range]"}
  ],
  "Results": [
    {"Column": "ActivityName", "Field": "Formula (Text)", "Formula": "CASE WHEN {systemnotes.newvalue} LIKE '%Disputed%' THEN 'Dispute Registered' WHEN {systemnotes.oldvalue} LIKE '%Disputed%' THEN 'Dispute Resolved' ELSE NULL END"},
    {"Column": "EventTime", "Field": "System Notes : Date"},
    {"Column": "InvoiceNumber", "Field": "Document Number"},
    {"Column": "CustomerId", "Field": "Customer : Internal ID"},
    {"Column": "CustomerSegment", "Field": "Customer : Category"},
    {"Column": "InvoiceAmount", "Field": "Amount (Gross)"},
    {"Column": "DueDate", "Field": "Due Date"},
    {"Column": "InvoiceStatus", "Field": "Status"}
  ]
}

// Saved Search 6: Credit Memo Issued
{
  "SavedSearchName": "ProcessMind - Credit Memos",
  "RecordType": "Transaction",
  "Criteria": [
    {"Field": "Type", "Operator": "is", "Value": "Credit Memo"},
    {"Field": "Main Line", "Operator": "is", "Value": "true"},
    {"Field": "Applied To Transaction : Type", "Operator": "is", "Value": "Invoice"},
    {"Field": "Date Created", "Operator": "within", "Value": "[Your Date Range]"}
  ],
  "Results": [
    {"Column": "ActivityName", "Field": "Formula (Text)", "Formula": "'Credit Memo Issued'"},
    {"Column": "EventTime", "Field": "Date Created"},
    {"Column": "InvoiceNumber", "Field": "Applied To Transaction : Document Number"},
    {"Column": "CustomerId", "Field": "Customer : Internal ID"},
    {"Column": "CustomerSegment", "Field": "Customer : Category"},
    {"Column": "InvoiceAmount", "Field": "Applied To Transaction : Amount (Gross)"},
    {"Column": "DueDate", "Field": "Applied To Transaction : Due Date"},
    {"Column": "InvoiceStatus", "Field": "Applied To Transaction : Status"}
  ]
}

// Saved Search 7: Payment Received & Posted
{
  "SavedSearchName": "ProcessMind - Payments",
  "RecordType": "Transaction",
  "Criteria": [
    {"Field": "Type", "Operator": "is", "Value": "Customer Payment"},
    {"Field": "Main Line", "Operator": "is", "Value": "true"},
    {"Field": "Date Created", "Operator": "within", "Value": "[Your Date Range]"}
  ],
  "Results": [
    // For 'Payment Received'
    {"Column": "ActivityName", "Field": "Formula (Text)", "Formula": "'Payment Received'"},
    {"Column": "EventTime", "Field": "Date Created"},
    // For 'Payment Posted'
    // {"Column": "ActivityName", "Field": "Formula (Text)", "Formula": "'Payment Posted'"},
    // {"Column": "EventTime", "Field": "[Your GL Posting Date Field]"},
    {"Column": "InvoiceNumber", "Field": "Applied To Transaction : Document Number"},
    {"Column": "CustomerId", "Field": "Customer : Internal ID"},
    {"Column": "CustomerSegment", "Field": "Customer : Category"},
    {"Column": "InvoiceAmount", "Field": "Applied To Transaction : Amount (Gross)"},
    {"Column": "DueDate", "Field": "Applied To Transaction : Due Date"},
    {"Column": "InvoiceStatus", "Field": "Applied To Transaction : Status"}
  ]
}

// Saved Search 8: Invoice Settled
{
  "SavedSearchName": "ProcessMind - Invoice Settled",
  "RecordType": "Transaction",
  "Criteria": [
    {"Field": "Type", "Operator": "is", "Value": "Invoice"},
    {"Field": "Main Line", "Operator": "is", "Value": "true"},
    {"Field": "Status", "Operator": "anyof", "Value": "Paid In Full"},
    {"Field": "Date Closed", "Operator": "within", "Value": "[Your Date Range]"}
  ],
  "Results": [
    {"Column": "ActivityName", "Field": "Formula (Text)", "Formula": "'Invoice Settled'"},
    {"Column": "EventTime", "Field": "Date Closed"},
    {"Column": "InvoiceNumber", "Field": "Document Number"},
    {"Column": "CustomerId", "Field": "Customer : Internal ID"},
    {"Column": "CustomerSegment", "Field": "Customer : Category"},
    {"Column": "InvoiceAmount", "Field": "Amount (Gross)"},
    {"Column": "DueDate", "Field": "Due Date"},
    {"Column": "InvoiceStatus", "Field": "Status"}
  ]
}

// Saved Search 9: Invoice Written Off
{
  "SavedSearchName": "ProcessMind - Invoice Written Off",
  "RecordType": "Transaction",
  "Criteria": [
    {"Field": "Type", "Operator": "is", "Value": "Journal Entry"},
    {"Field": "Main Line", "Operator": "is", "Value": "false"},
    {"Field": "Account", "Operator": "is", "Value": "[Your Bad Debt Expense Account]"},
    {"Field": "Date Created", "Operator": "within", "Value": "[Your Date Range]"}
  ],
  "Results": [
    {"Column": "ActivityName", "Field": "Formula (Text)", "Formula": "'Invoice Written Off'"},
    {"Column": "EventTime", "Field": "Date Created"},
    {"Column": "InvoiceNumber", "Field": "Created From : Document Number"},
    {"Column": "CustomerId", "Field": "Created From : Customer : Internal ID"},
    {"Column": "CustomerSegment", "Field": "Created From : Customer : Category"},
    {"Column": "InvoiceAmount", "Field": "Created From : Amount (Gross)"},
    {"Column": "DueDate", "Field": "Created From : Due Date"},
    {"Column": "InvoiceStatus", "Field": "Created From : Status"}
  ]
}

Steps

Prerequisites: Ensure you have Python 3.6+ installed on your system. You will also need credentials for NetSuite SuiteTalk, specifically for Token Based Authentication (TBA), which includes an Account ID, Consumer Key, Consumer Secret, Token ID, and Token Secret. The associated NetSuite role must have permissions to access all required records, including Transactions (Invoices, Payments, Credit Memos), CRM Activities (Phone Calls), and Customer records.
Install Python Libraries: Open your command line or terminal and install the necessary Python libraries. The primary libraries are pandas for data manipulation, requests and requests_oauthlib for making API calls, or a dedicated NetSuite library. pip install pandas requests requests_oauthlib
Set Up Authentication: In the provided Python script, locate the section for credentials. Securely enter your NetSuite Account ID, Consumer Key, Consumer Secret, Token ID, and Token Secret into the designated placeholder variables. It is highly recommended to use environment variables or a secure vault to manage these credentials rather than hardcoding them directly.
Configure Extraction Parameters: Within the script, define the start_date and end_date variables to specify the time range for the data extraction. You can also adjust the subsidiary_id filter if you need to limit the extraction to a specific NetSuite subsidiary.
Understand the Script Logic: The script is structured to query each of the 13 required activities separately. Each query is a distinct SuiteQL statement sent to the NetSuite REST API endpoint. The script handles API pagination automatically to retrieve all records for each query.
Execute the Python Script: Run the script from your terminal using the command: python your_netsuite_extraction_script.py. The script will connect to NetSuite, execute the series of queries, and print status updates to the console as it fetches data for each activity.
Combine and Transform Data: After fetching the raw data for each activity, the script uses the pandas library to concatenate all the individual results into a single, unified DataFrame. This DataFrame represents the event log.
Review and Save the Event Log: The script will save the final DataFrame to a CSV file named netsuite_collections_event_log.csv. Open this file to review the data for completeness and accuracy.
Prepare for Upload: Ensure the final CSV file has the required column headers: InvoiceNumber, ActivityName, and EventTime, along with any recommended attributes. The file is now ready to be uploaded to ProcessMind.

Configuration

Authentication: The script uses NetSuite's Token Based Authentication (TBA). You must provide the Account ID, Consumer Key, Consumer Secret, Token ID, and Token Secret. The role associated with the token needs read access to Transaction, Customer, Phone Call, and Message records.
Date Range: The start_date and end_date variables in the script control the extraction window. For an initial analysis, a period of 3-6 months is recommended to balance data volume with performance.
Subsidiary Filter: A subsidiary_id variable is included to filter data for a specific business unit. If you operate in a single-subsidiary environment, this can be omitted.
API Endpoint: The script targets the NetSuite SuiteQL REST API endpoint. Ensure this is enabled for your account (/services/rest/query/v1/suiteql).
Pagination Handling: The script includes a function to automatically handle paginated API responses. The page_size is set to 1000, which is the maximum allowed by the API, to minimize the number of required API calls.
Custom Fields: The extraction for 'Dispute Registered' and 'Dispute Resolved' assumes the use of custom fields (custbody_dispute_date, custbody_dispute_resolved_date). You must update the script with the actual field IDs used in your NetSuite instance.

a Sample Query python

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

class=class="hl-string">"hl-comment"># --- Configuration Settings ---
class=class="hl-string">"hl-comment"># Best practice: Use environment variables instead of hardcoding credentials.
NS_ACCOUNT = os.getenv(class="hl-string">'NS_ACCOUNT', class="hl-string">'[Your NetSuite Account ID]')
NS_CONSUMER_KEY = os.getenv(class="hl-string">'NS_CONSUMER_KEY', class="hl-string">'[Your Consumer Key]')
NS_CONSUMER_SECRET = os.getenv(class="hl-string">'NS_CONSUMER_SECRET', class="hl-string">'[Your Consumer Secret]')
NS_TOKEN_ID = os.getenv(class="hl-string">'NS_TOKEN_ID', class="hl-string">'[Your Token ID]')
NS_TOKEN_SECRET = os.getenv(class="hl-string">'NS_TOKEN_SECRET', class="hl-string">'[Your Token Secret]')

class=class="hl-string">"hl-comment"># The base URL for the SuiteQL API endpoint
BASE_URL = fclass="hl-string">"https://{NS_ACCOUNT.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'
END_DATE = class="hl-string">'2023-12-31'
SUBSIDIARY_ID = class="hl-string">'[Your Subsidiary ID]' class=class="hl-string">"hl-comment"># Optional: filter by subsidiary

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

    oauth = OAuth1(
        client_key=NS_CONSUMER_KEY,
        client_secret=NS_CONSUMER_SECRET,
        resource_owner_key=NS_TOKEN_ID,
        resource_owner_secret=NS_TOKEN_SECRET,
        realm=NS_ACCOUNT,
        signature_method=class="hl-string">'HMAC-SHA256'
    )

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

    while True:
        paginated_query = fclass="hl-string">"{query} LIMIT {limit} OFFSET {offset}"
        payload = json.dumps({class="hl-string">'q': paginated_query})
        
        try:
            response = requests.post(BASE_URL, headers=headers, data=payload, auth=oauth)
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            data = response.json()
            
            if not data.get(class="hl-string">'items'):
                break

            all_results.extend(data[class="hl-string">'items'])
            
            if not data.get(class="hl-string">'hasMore', False):
                break
            
            offset += limit

        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"API Request Error: {e}")
            print(fclass="hl-string">"Response body: {response.text if 'response' in locals() else 'No response'}")
            return None

    return all_results

def main():
    class="hl-string">"""Main function to orchestrate the data extraction."""
    all_events = []
    
    class=class="hl-string">"hl-comment"># Common WHERE clause for filtering by date and subsidiary
    common_filter = fclass="hl-string">"WHERE T.trandate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD') AND T.subsidiary = {SUBSIDIARY_ID}"

    class=class="hl-string">"hl-comment"># --- Define SuiteQL Queries for Each Activity ---
    queries = {
        class="hl-string">'Invoice Generated': fclass="hl-string">"""
            SELECT
                T.tranid AS InvoiceNumber,
                'Invoice Generated' AS ActivityName,
                TO_CHAR(T.createdDate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                T.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                T.foreignTotal AS InvoiceAmount,
                TO_CHAR(T.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(T.status) AS InvoiceStatus
            FROM
                Transaction AS T
                INNER JOIN Customer AS C ON T.entity = C.id
            WHERE
                T.type = 'CustInvc' AND T.voided = 'F' AND T.subsidiary = {SUBSIDIARY_ID}
                AND T.createdDate BETWEEN TO_TIMESTAMP('{START_DATE} 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('{END_DATE} 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
        """,
        class="hl-string">'Invoice Sent to Customer': fclass="hl-string">"""
            SELECT
                T.tranid AS InvoiceNumber,
                'Invoice Sent to Customer' AS ActivityName,
                TO_CHAR(M.messageDate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                T.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                T.foreignTotal AS InvoiceAmount,
                TO_CHAR(T.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(T.status) AS InvoiceStatus
            FROM
                Transaction AS T
                INNER JOIN Message AS M ON T.id = M.transaction
                INNER JOIN Customer AS C ON T.entity = C.id
            WHERE
                T.type = 'CustInvc' AND T.voided = 'F' AND T.subsidiary = {SUBSIDIARY_ID}
                AND M.emailed = 'T' AND M.messageDate IS NOT NULL
                AND M.messageDate BETWEEN TO_TIMESTAMP('{START_DATE} 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('{END_DATE} 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
        """,
        class="hl-string">'Payment Due Date Passed': fclass="hl-string">"""
            SELECT
                T.tranid AS InvoiceNumber,
                'Payment Due Date Passed' AS ActivityName,
                TO_CHAR(T.dueDate, 'YYYY-MM-DD"T"23:59:59"Z"') AS EventTime,
                T.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                T.foreignTotal AS InvoiceAmount,
                TO_CHAR(T.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(T.status) AS InvoiceStatus
            FROM
                Transaction AS T
                INNER JOIN Customer AS C ON T.entity = C.id
            WHERE
                T.type = 'CustInvc' AND T.voided = 'F' AND T.subsidiary = {SUBSIDIARY_ID}
                AND T.status NOT IN ('CustInvc:B', 'CustInvc:A') -- Not Paid In Full or Pending Approval
                AND T.dueDate < CURRENT_DATE
                AND T.dueDate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
        """,
        class="hl-string">'Overdue Reminder Sent': fclass="hl-string">"""
            SELECT
                T.tranid AS InvoiceNumber,
                'Overdue Reminder Sent' AS ActivityName,
                TO_CHAR(M.messageDate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                T.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                T.foreignTotal AS InvoiceAmount,
                TO_CHAR(T.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(T.status) AS InvoiceStatus
            FROM
                Transaction AS T
                INNER JOIN Message AS M ON T.id = M.transaction
                INNER JOIN Customer AS C ON T.entity = C.id
            WHERE
                T.type = 'CustInvc' AND T.voided = 'F' AND T.subsidiary = {SUBSIDIARY_ID}
                AND (M.subject LIKE '%Overdue%' OR M.subject LIKE '%Reminder%')
                AND M.messageDate BETWEEN TO_TIMESTAMP('{START_DATE} 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('{END_DATE} 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
        """,
        class="hl-string">'Dunning Procedure Initiated': fclass="hl-string">"""
            SELECT
                T.tranid AS InvoiceNumber,
                'Dunning Procedure Initiated' AS ActivityName,
                TO_CHAR(SL.systemNoteDate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                T.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                T.foreignTotal AS InvoiceAmount,
                TO_CHAR(T.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(T.status) AS InvoiceStatus
            FROM
                Transaction AS T
                INNER JOIN Customer AS C ON T.entity = C.id
                INNER JOIN SystemNote AS SL ON T.id = SL.recordId
            WHERE
                T.type = 'CustInvc' AND T.voided = 'F' AND T.subsidiary = {SUBSIDIARY_ID}
                AND SL.field = 'custbody_dunning_level' AND SL.oldValue IS NULL -- Assumes a custom dunning level field
                AND SL.systemNoteDate BETWEEN TO_TIMESTAMP('{START_DATE} 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('{END_DATE} 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
        """,
        class="hl-string">'Collection Call Made': fclass="hl-string">"""
            SELECT
                T.tranid AS InvoiceNumber,
                'Collection Call Made' AS ActivityName,
                TO_CHAR(PC.completedDate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                T.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                T.foreignTotal AS InvoiceAmount,
                TO_CHAR(T.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(T.status) AS InvoiceStatus
            FROM
                PhoneCall AS PC
                INNER JOIN Transaction AS T ON PC.transaction = T.id
                INNER JOIN Customer AS C ON T.entity = C.id
            WHERE
                T.type = 'CustInvc' AND T.voided = 'F' AND T.subsidiary = {SUBSIDIARY_ID}
                AND PC.title LIKE '%Collection%'
                AND PC.completedDate BETWEEN TO_TIMESTAMP('{START_DATE} 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('{END_DATE} 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
        """,
        class="hl-string">'Dispute Registered': fclass="hl-string">"""
            SELECT
                T.tranid AS InvoiceNumber,
                'Dispute Registered' AS ActivityName,
                TO_CHAR(T.custbody_dispute_date, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                T.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                T.foreignTotal AS InvoiceAmount,
                TO_CHAR(T.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(T.status) AS InvoiceStatus
            FROM
                Transaction AS T
                INNER JOIN Customer AS C ON T.entity = C.id
            WHERE
                T.type = 'CustInvc' AND T.voided = 'F' AND T.subsidiary = {SUBSIDIARY_ID}
                AND T.custbody_dispute_date IS NOT NULL -- Assumes a custom dispute date field
                AND T.custbody_dispute_date BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
        """,
        class="hl-string">'Dispute Resolved': fclass="hl-string">"""
            SELECT
                T.tranid AS InvoiceNumber,
                'Dispute Resolved' AS ActivityName,
                TO_CHAR(T.custbody_dispute_resolved_date, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                T.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                T.foreignTotal AS InvoiceAmount,
                TO_CHAR(T.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(T.status) AS InvoiceStatus
            FROM
                Transaction AS T
                INNER JOIN Customer AS C ON T.entity = C.id
            WHERE
                T.type = 'CustInvc' AND T.voided = 'F' AND T.subsidiary = {SUBSIDIARY_ID}
                AND T.custbody_dispute_resolved_date IS NOT NULL -- Assumes a custom dispute resolved date field
                AND T.custbody_dispute_resolved_date BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
        """,
        class="hl-string">'Credit Memo Issued': fclass="hl-string">"""
            SELECT
                AppliedTo.tranid AS InvoiceNumber,
                'Credit Memo Issued' AS ActivityName,
                TO_CHAR(CM.trandate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                CM.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                AppliedTo.foreignTotal AS InvoiceAmount,
                TO_CHAR(AppliedTo.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(AppliedTo.status) AS InvoiceStatus
            FROM
                Transaction AS CM
                INNER JOIN TransactionLine AS CML ON CM.id = CML.transaction
                INNER JOIN Transaction AS AppliedTo ON CML.appliedToTransaction = AppliedTo.id
                INNER JOIN Customer AS C ON CM.entity = C.id
            WHERE
                CM.type = 'CustCred' AND CM.voided = 'F' AND CM.subsidiary = {SUBSIDIARY_ID}
                AND AppliedTo.type = 'CustInvc'
                AND CM.trandate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
        """,
        class="hl-string">'Payment Received': fclass="hl-string">"""
            SELECT
                AppliedTo.tranid AS InvoiceNumber,
                'Payment Received' AS ActivityName,
                TO_CHAR(P.trandate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                P.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                AppliedTo.foreignTotal AS InvoiceAmount,
                TO_CHAR(AppliedTo.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(AppliedTo.status) AS InvoiceStatus
            FROM
                Transaction AS P
                INNER JOIN TransactionLine AS PL ON P.id = PL.transaction
                INNER JOIN Transaction AS AppliedTo ON PL.appliedToTransaction = AppliedTo.id
                INNER JOIN Customer AS C ON P.entity = C.id
            WHERE
                P.type = 'CustPymt' AND P.voided = 'F' AND P.subsidiary = {SUBSIDIARY_ID}
                AND AppliedTo.type = 'CustInvc'
                AND P.trandate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
        """,
        class="hl-string">'Payment Posted': fclass="hl-string">"""
            SELECT
                AppliedTo.tranid AS InvoiceNumber,
                'Payment Posted' AS ActivityName,
                TO_CHAR(P.postingdate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                P.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                AppliedTo.foreignTotal AS InvoiceAmount,
                TO_CHAR(AppliedTo.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(AppliedTo.status) AS InvoiceStatus
            FROM
                Transaction AS P
                INNER JOIN TransactionLine AS PL ON P.id = PL.transaction
                INNER JOIN Transaction AS AppliedTo ON PL.appliedToTransaction = AppliedTo.id
                INNER JOIN Customer AS C ON P.entity = C.id
            WHERE
                P.type = 'CustPymt' AND P.voided = 'F' AND P.subsidiary = {SUBSIDIARY_ID}
                AND AppliedTo.type = 'CustInvc' AND P.posting = 'T'
                AND P.postingdate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
        """,
        class="hl-string">'Invoice Settled': fclass="hl-string">"""
            SELECT
                T.tranid AS InvoiceNumber,
                'Invoice Settled' AS ActivityName,
                TO_CHAR(T.lastModifiedDate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                T.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                T.foreignTotal AS InvoiceAmount,
                TO_CHAR(T.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(T.status) AS InvoiceStatus
            FROM
                Transaction AS T
                INNER JOIN Customer AS C ON T.entity = C.id
            WHERE
                T.type = 'CustInvc' AND T.voided = 'F' AND T.subsidiary = {SUBSIDIARY_ID}
                AND T.status = 'CustInvc:B' -- Paid In Full
                AND T.lastModifiedDate BETWEEN TO_TIMESTAMP('{START_DATE} 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('{END_DATE} 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
        """,
        class="hl-string">'Invoice Written Off': fclass="hl-string">"""
            SELECT
                AppliedTo.tranid AS InvoiceNumber,
                'Invoice Written Off' AS ActivityName,
                TO_CHAR(JE.trandate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS EventTime,
                JE.entity AS CustomerId,
                BUILTIN.DF(C.category) AS CustomerSegment,
                AppliedTo.foreignTotal AS InvoiceAmount,
                TO_CHAR(AppliedTo.dueDate, 'YYYY-MM-DD') AS DueDate,
                BUILTIN.DF(AppliedTo.status) AS InvoiceStatus
            FROM
                Transaction AS JE
                INNER JOIN TransactionLine AS JEL ON JE.id = JEL.transaction
                INNER JOIN Transaction AS AppliedTo ON JEL.entity = AppliedTo.entity -- A best-effort link via customer
                INNER JOIN Customer AS C ON JE.entity = C.id
            WHERE
                JE.type = 'Journal' AND JE.voided = 'F' AND JE.subsidiary = {SUBSIDIARY_ID}
                AND JEL.account = [Your Bad Debt Expense Account ID] -- Must configure your specific bad debt account
                AND AppliedTo.type = 'CustInvc'
                AND JE.trandate BETWEEN TO_DATE('{START_DATE}', 'YYYY-MM-DD') AND TO_DATE('{END_DATE}', 'YYYY-MM-DD')
                AND (JEL.memo LIKE '%' || AppliedTo.tranid || '%' OR JE.memo LIKE '%' || AppliedTo.tranid || '%') -- Best-effort link by checking memo field for invoice number
        """
    }

    for activity, query in queries.items():
        print(fclass="hl-string">"Fetching data for: {activity}...")
        results = execute_suiteql(query)
        if results:
            all_events.extend(results)
            print(fclass="hl-string">"  ...found {len(results)} events.")
        else:
            print(fclass="hl-string">"  ...no events found or an error occurred.")

    if not all_events:
        print(class="hl-string">"No data extracted. Exiting.")
        return

    class=class="hl-string">"hl-comment"># Convert to pandas DataFrame
    df = pd.DataFrame(all_events)
    
    class=class="hl-string">"hl-comment"># Data Cleaning and Formatting
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'])
    df = df.sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTime'])
    df.rename(columns=lambda c: class="hl-string">''.join(c.split()).capitalize(), inplace=True)
    
    class=class="hl-string">"hl-comment"># Save to CSV
    output_file = class="hl-string">'netsuite_collections_event_log.csv'
    df.to_csv(output_file, index=False)
    print(fclass="hl-string">"\nEvent log successfully saved to {output_file}")

if __name__ == class="hl-string">'__main__':
    main()

Your Credit Management & Collections Data Template

Your Credit Management & Collections Data Template

Credit Management & Collections Attributes

Credit Management & Collections Activities

Extraction Guides

NetSuite Saved Searches and Reports Data Export

Steps

Configuration

a Sample Query config

Python Extraction via NetSuite SuiteTalk Web Services

Steps

Configuration

a Sample Query python

Ready to Get Started?

Start Optimizing Credit & Collections Today!

Your Credit Management & Collections Data Template

Your Credit Management & Collections Data Template

Credit Management & Collections Attributes

Credit Management & Collections Activities

Extraction Guides

NetSuite Saved Searches and Reports Data Export

Steps

Configuration

a Sample Query config

Python Extraction via NetSuite SuiteTalk Web Services

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Start Optimizing Credit & Collections Today!