NetSuite Search Guide for RightInsight

This guide will help you create effective NetSuite saved searches that work optimally with RightInsight's AI analysis capabilities.

Understanding RightInsight's Token System

RightInsight uses AI models that have token limits - think of tokens as "words" that the AI can process. When your NetSuite search returns too much data, it can exceed these limits, causing:

Analysis failures
Data truncation (important data gets cut off)
Poor performance
Incomplete insights

The key is to provide summarized, focused data rather than raw transaction details.

Core Principles for Effective Searches

1. Summarize at the Source

Instead of returning thousands of individual transactions, create searches that return summarized data:

❌ Avoid:

Individual transaction lines
Detailed item records
Raw customer data with all fields

✅ Prefer:

Monthly/quarterly summaries
Aggregated totals by category
Grouped and summarized results

2. Focus on Key Metrics

Include only the most important columns:

Financial metrics: Amount, quantity, totals
Time periods: Date ranges, periods
Categories: Customer, product, department
Status indicators: Active, completed, etc.

3. Use Appropriate Date Ranges

Recent data: Last 12-24 months for trend analysis
Specific periods: Q1, Q2, etc. for quarterly analysis
Avoid: All-time data unless specifically needed

Essential NetSuite Search Formulas

Date Grouping Formulas

Monthly Grouping (MM-YYYY)

TO_CHAR({trandate}, 'MM-YYYY')

Quarterly Grouping

CASE 
  WHEN TO_CHAR({trandate}, 'MM') IN ('01','02','03') THEN 'Q1-' || TO_CHAR({trandate}, 'YYYY')
  WHEN TO_CHAR({trandate}, 'MM') IN ('04','05','06') THEN 'Q2-' || TO_CHAR({trandate}, 'YYYY')
  WHEN TO_CHAR({trandate}, 'MM') IN ('07','08','09') THEN 'Q3-' || TO_CHAR({trandate}, 'YYYY')
  WHEN TO_CHAR({trandate}, 'MM') IN ('10','11','12') THEN 'Q4-' || TO_CHAR({trandate}, 'YYYY')
END

Year-Month Format

TO_CHAR({trandate}, 'YYYY-MM')

Aggregation Formulas

Sum by Category

SUM({amount})

Use with GROUP BY clauses

Average by Period

AVG({amount})

Count of Records

COUNT({internalid})

Common Grouping Strategies

By Customer and Period

GROUP BY {entity}, TO_CHAR({trandate}, 'MM-YYYY')

By Product and Quarter

GROUP BY {item}, 
CASE 
  WHEN TO_CHAR({trandate}, 'MM') IN ('01','02','03') THEN 'Q1-' || TO_CHAR({trandate}, 'YYYY')
  WHEN TO_CHAR({trandate}, 'MM') IN ('04','05','06') THEN 'Q2-' || TO_CHAR({trandate}, 'YYYY')
  WHEN TO_CHAR({trandate}, 'MM') IN ('07','08','09') THEN 'Q3-' || TO_CHAR({trandate}, 'YYYY')
  WHEN TO_CHAR({trandate}, 'MM') IN ('10','11','12') THEN 'Q4-' || TO_CHAR({trandate}, 'YYYY')
END

Search Templates by Use Case

1. Sales Performance Analysis

Purpose: Monthly sales trends by customer

Key Columns:

Customer Name
Month (MM-YYYY)
Total Sales Amount
Number of Transactions

Sample Formula Fields:

Month: TO_CHAR({trandate}, 'MM-YYYY')
Total Sales: SUM({amount})
Transaction Count: COUNT({internalid})

Grouping: Customer, Period Date Filter: Last 24 months

2. Product Performance

Purpose: Quarterly product sales analysis

Key Columns:

Product Name
Quarter
Total Quantity Sold
Total Revenue

Sample Formula Fields:

Quarter: CASE 
  WHEN TO_CHAR({trandate}, 'MM') IN ('01','02','03') THEN 'Q1-' || TO_CHAR({trandate}, 'YYYY')
  WHEN TO_CHAR({trandate}, 'MM') IN ('04','05','06') THEN 'Q2-' || TO_CHAR({trandate}, 'YYYY')
  WHEN TO_CHAR({trandate}, 'MM') IN ('07','08','09') THEN 'Q3-' || TO_CHAR({trandate}, 'YYYY')
  WHEN TO_CHAR({trandate}, 'MM') IN ('10','11','12') THEN 'Q4-' || TO_CHAR({trandate}, 'YYYY')
END
Total Revenue: SUM({amount})
Total Quantity: SUM({quantity})

Grouping: Product, Quarter Date Filter: Last 4 quarters

3. Cash Flow Analysis

Purpose: Monthly cash flow summary

Key Columns:

Period
Cash In (from sales)
Cash Out (from expenses)
Net Cash Flow

Sample Formula Fields:

Period: TO_CHAR({trandate}, 'MM-YYYY')
Cash In: SUM(CASE WHEN {type} = 'Sales Order' THEN {amount} ELSE 0 END)
Cash Out: SUM(CASE WHEN {type} = 'Bill' THEN {amount} ELSE 0 END)
Net Cash: SUM(CASE WHEN {type} = 'Sales Order' THEN {amount} ELSE -{amount} END)

Grouping: Period Date Filter: Last 12 months

4. Customer Analysis

Purpose: Customer performance summary

Key Columns:

Customer Name
Total Sales
Number of Orders
Average Order Value
Last Order Date

Sample Formula Fields:

Total Sales: SUM({amount})
Order Count: COUNT({internalid})
Average Order: AVG({amount})
Last Order: MAX({trandate})

Grouping: Customer Date Filter: Last 12 months

Advanced Techniques

1. Conditional Aggregation

Use CASE statements for complex calculations:

Revenue by Status: SUM(CASE WHEN {status} = 'Closed Won' THEN {amount} ELSE 0 END)

2. Percentage Calculations

Percentage of Total: ROUND(({amount} / SUM({amount}) OVER()) * 100, 2)

3. Running Totals

Running Total: SUM({amount}) OVER (ORDER BY {trandate})

4. Period-over-Period Comparisons

Previous Period: LAG(SUM({amount}), 1) OVER (ORDER BY {period})
Growth Rate: ROUND(((SUM({amount}) - LAG(SUM({amount}), 1) OVER (ORDER BY {period})) / LAG(SUM({amount}), 1) OVER (ORDER BY {period})) * 100, 2)

Optimization Strategies

1. Limit Results

Use appropriate date filters
Set reasonable result limits (4000 rows max)
Focus on recent, relevant data

2. Reduce Columns

Include only essential columns
Remove memo fields and long text
Avoid calculated fields that aren't needed

3. Use Filters Effectively

Filter by status (e.g., "Closed Won" sales)
Filter by date ranges
Filter by relevant departments/locations

4. Group and Summarize

Always use GROUP BY for aggregated data
Summarize at the NetSuite level, not in RightInsight
Use appropriate aggregation functions

Common Mistakes to Avoid

❌ Don't Do This:

Return individual transaction lines
Include all possible columns
Use very broad date ranges
Forget to group and summarize
Include memo fields or long text

✅ Do This Instead:

Return summarized data by period/category
Include only relevant columns
Use focused date ranges
Group and aggregate in NetSuite
Keep text fields short and relevant

Testing Your Searches

Before using with RightInsight:

Run the search in NetSuite - Verify it returns expected data
Check result count - Should be 4000 rows or less
Review column content - Ensure data is clean and relevant
Test with RightInsight - Try a simple question first

Best Practices Summary

Summarize First: Group and aggregate data in NetSuite
Focus on Metrics: Include key business metrics
Use Appropriate Periods: Recent, relevant time ranges
Limit Results: Keep searches focused and manageable
Test Thoroughly: Verify searches work before using with RightInsight
Iterate and Improve: Refine searches based on analysis results

Example Search Configurations

Monthly Sales Summary

Type: Transaction
Criteria: Date = Last 24 months, Type = Sales Order
Columns: Customer, Period (MM-YYYY), Total Amount
Grouping: Customer, Period
Aggregation: SUM(Amount)

Quarterly Product Performance

Type: Transaction
Criteria: Date = Last 4 quarters, Type = Sales Order
Columns: Product, Quarter, Total Revenue, Total Quantity
Grouping: Product, Quarter
Aggregation: SUM(Amount), SUM(Quantity)

Customer Lifetime Value

Type: Transaction
Criteria: Date = All time, Type = Sales Order
Columns: Customer, Total Revenue, Order Count, Average Order
Grouping: Customer
Aggregation: SUM(Amount), COUNT(Internal ID), AVG(Amount)

Remember: The goal is to provide RightInsight with clean, summarized data that tells a story, not raw transaction details. Focus on business insights, not data dumps!