---

name: netsuite-data-analysis description: NetSuite data analysis for professional services organizations. Use when analyzing project profitability, resource utilization, billable hours, project margins, cost overruns, capacity planning, or resource allocation in NetSuite. Leverages NetSuite MCP tools for querying and analysis.

NetSuite Data Analysis

Overview

Analyze NetSuite data for professional services organizations, focusing on project profitability and resource utilization. This skill provides analysis patterns using NetSuite MCP tools.

Core Analysis Capabilities

1. Project Profitability Analysis

Analyze project margins, revenue vs. costs, and identify profitable vs. unprofitable projects.

Common queries:

• Project revenue vs. actual costs
• Budget vs. actual analysis
• Margin analysis by project type
• Cost overruns and variance analysis
• Revenue recognition status

Key tables:

• job (projects)
• transaction (revenue and costs)
• timeEntry (labor costs)
• projectTask
• projectExpenseType

Example workflow:

1. Find projects to analyze:
   - Use netsuite_find_records_saved_search or netsuite_run_suiteql_query
   - Filter by date range, status, customer, or project manager

2. Get project financials:
   - Query revenue: SELECT SUM(amount) FROM transaction WHERE job = ? AND type IN ('Invoice', 'CashSale')
   - Query costs: SELECT SUM(amount) FROM transaction WHERE job = ? AND type IN ('Expense', 'VendorBill')
   - Query labor: SELECT SUM(hours * rate) FROM timeEntry WHERE job = ?

3. Calculate metrics:
   - Gross margin = (Revenue - Total Costs) / Revenue
   - Labor margin = (Revenue - Labor Costs) / Revenue
   - Budget variance = Actual Costs - Budgeted Costs

2. Resource Utilization Analysis

Track billable hours, capacity planning, and resource allocation across projects.

Common queries:

• Billable vs. non-billable hours by resource
• Utilization rate by employee/department
• Capacity vs. allocation
• Hours by project and task
• Forecast vs. actual hours

Key tables:

• timeEntry
• employee
• job
• projectTask
• department

Example workflow:

1. Get time entry data:
   - Use netsuite_run_suiteql_query to fetch time entries
   - Filter by date range, employee, project, or department

2. Calculate utilization metrics:
   - Billable hours: SELECT SUM(hours) FROM timeEntry WHERE isBillable = 'T'
   - Total hours: SELECT SUM(hours) FROM timeEntry
   - Utilization % = Billable Hours / Total Available Hours

3. Analyze by dimension:
   - By employee: GROUP BY employee
   - By project: GROUP BY job
   - By department: GROUP BY department
   - By time period: GROUP BY EXTRACT(MONTH FROM date)

Using NetSuite MCP Tools

netsuite_run_suiteql_query

Use for complex analytical queries joining multiple tables.

Best for:

• Multi-table joins
• Aggregations and calculations
• Custom date ranges and filters
• Ad-hoc analysis

Example:

SELECT
  j.companyName AS project,
  SUM(CASE WHEN t.type IN ('Invoice', 'CashSale') THEN t.amount ELSE 0 END) AS revenue,
  SUM(CASE WHEN t.type IN ('Expense', 'VendorBill') THEN t.amount ELSE 0 END) AS costs,
  (SUM(CASE WHEN t.type IN ('Invoice', 'CashSale') THEN t.amount ELSE 0 END) -
   SUM(CASE WHEN t.type IN ('Expense', 'VendorBill') THEN t.amount ELSE 0 END)) AS profit
FROM job j
LEFT JOIN transaction t ON t.job = j.id
WHERE j.status = 'In Progress'
GROUP BY j.companyName
ORDER BY profit DESC

netsuite_find_records_saved_search

Use for leveraging pre-built saved searches.

Best for:

• Reusing existing searches
• Consistent reporting
• Saved filters and criteria
• Standard reports

When to use: If there's already a saved search for the analysis needed, use it instead of writing custom SuiteQL.

netsuite_get_record_type_object_schema

Use to understand table structure before querying.

Best for:

• Discovering available fields
• Understanding relationships
• Finding custom fields
• Schema exploration

Workflow: Run this first when working with unfamiliar record types to see what fields are available.

Analysis Patterns

Pattern 1: Project Health Dashboard

1. Get active projects: Query job table for status = 'In Progress'
2. For each project, get:
   - Revenue to date
   - Costs to date
   - Hours logged
   - Budget remaining
3. Calculate health metrics:
   - Margin %
   - Budget burn rate
   - Utilization vs. plan
4. Flag at-risk projects (negative margin, over budget, low utilization)

Pattern 2: Resource Capacity Planning

1. Get employee list with capacity (standard hours per week)
2. Query allocated hours from project tasks
3. Query actual hours from time entries
4. Calculate:
   - Available capacity = Standard hours - Allocated hours
   - Utilization = Actual hours / Standard hours
   - Forecast vs. actual variance
5. Identify over/under-allocated resources

Pattern 3: Profitability by Customer

1. Query all projects grouped by customer
2. Aggregate revenue and costs by customer
3. Calculate customer lifetime value and margin
4. Rank customers by profitability
5. Identify high-value vs. high-cost customers

Common SuiteQL Patterns

Time Period Filtering

-- Current month
WHERE EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM CURRENT_DATE)
AND EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE)

-- Last 90 days
WHERE date >= CURRENT_DATE - INTERVAL '90 days'

-- Fiscal year (assuming FY starts in January)
WHERE EXTRACT(YEAR FROM date) = EXTRACT(YEAR FROM CURRENT_DATE)

Joining Projects and Time Entries

SELECT
  j.companyName,
  e.firstName || ' ' || e.lastName AS employee,
  SUM(te.hours) AS total_hours,
  SUM(CASE WHEN te.isBillable = 'T' THEN te.hours ELSE 0 END) AS billable_hours
FROM timeEntry te
JOIN employee e ON te.employee = e.id
JOIN job j ON te.job = j.id
WHERE te.date BETWEEN ? AND ?
GROUP BY j.companyName, e.firstName, e.lastName

Profitability by Project

SELECT
  j.companyName AS project,
  j.projectedTotal AS budget,
  COALESCE(SUM(CASE WHEN t.type IN ('Invoice', 'CashSale') THEN t.amount END), 0) AS revenue,
  COALESCE(SUM(CASE WHEN t.type IN ('Expense', 'VendorBill', 'Check') THEN t.amount END), 0) AS costs,
  COALESCE(SUM(CASE WHEN t.type IN ('Invoice', 'CashSale') THEN t.amount END), 0) -
  COALESCE(SUM(CASE WHEN t.type IN ('Expense', 'VendorBill', 'Check') THEN t.amount END), 0) AS profit
FROM job j
LEFT JOIN transaction t ON t.job = j.id
WHERE j.status IN ('In Progress', 'Completed')
GROUP BY j.companyName, j.projectedTotal
HAVING SUM(CASE WHEN t.type IN ('Invoice', 'CashSale') THEN t.amount END) IS NOT NULL
ORDER BY profit DESC

Tips for Effective Analysis

1. Start with schema exploration - Use netsuite_get_record_type_object_schema to understand table structure
2. Leverage saved searches - Check for existing saved searches before writing custom queries
3. Test queries incrementally - Start with simple queries and add complexity
4. Use appropriate date ranges - Avoid querying all historical data; use specific date filters
5. Consider custom fields - Many companies have custom fields that may be relevant to analysis
6. Validate results - Cross-check query results with NetSuite UI for accuracy
7. Document assumptions - Note fiscal year definitions, billable rate calculations, etc.

Resources

This skill includes comprehensive reference materials:

references/query_library.md

Complete library of common SuiteQL queries organized by analysis type:

• Project Profitability Queries - Basic profitability, budget variance, labor cost analysis
• Resource Utilization Queries - Employee utilization, resource allocation, capacity planning
• Revenue Recognition Queries - Rev rec schedules, percent complete tracking
• Financial Health Queries - AR aging, cash flow analysis
• Customer Analysis Queries - Customer profitability, lifetime value
• Time & Expense Queries - Time entry summaries, expense tracking

When to read: Reference this file when you need specific query examples for common analysis tasks.

references/table_relationships.md

Guide to NetSuite table structure and relationships:

• Core professional services tables and their relationships
• Common join patterns (project revenue & costs, employee hours, etc.)
• Field reference for key tables (customer, job, transaction, timeEntry, employee)
• Important notes on handling amounts, posted vs. unposted, custom fields

When to read: Reference this file when building queries that join multiple tables or when you need to understand NetSuite's data model.

references/data_validation_queries.md

SuiteQL queries to validate that demo data looks realistic before presentations:

• Demo Data Health Check - Comprehensive overview of data quality for a prospect
• Project Data Validation - Check status distribution, budget variance spread
• Resource Utilization Validation - Verify realistic utilization rates (60-95%)
• Financial Data Validation - Validate invoice aging, revenue vs. costs ratios
• Custom Fields Validation - Ensure dashboard custom fields are populated
• Pre-Demo Validation Checklist - Run 24 hours before demo to catch issues

When to read: Run these queries before a prospect demo to ensure data looks realistic and identify any issues that need fixing.

references/financials-deep-dive.md

Comprehensive guide to NetSuite financials (GL, AP, AR) for solution consultants:

• General Ledger Fundamentals - Chart of accounts, multi-subsidiary accounting, period close process
• Accounts Payable - Vendor bill flow, 3-way matching, project cost integration, payment methods
• Accounts Receivable - Invoice types, billing workflows, AR aging, revenue recognition
• Financial Reporting - Essential reports, executive dashboard strategy
• Multi-Currency Operations - Base currency, transaction currency, realized/unrealized gains
• Integration Points - Bank integration, payroll integration, expense management
• Audit & Compliance - Audit trail, role-based access, SOX compliance features
• Common Objections & Responses - Handling prospect concerns about complexity and needs
• Demo Scripts - Close process demo, 60-second financials pitch
• Quick Reference - Financial transaction types and NetSuite menu locations

When to read: Reference this file when preparing financial-focused demos, explaining GL/AP/AR workflows, or addressing prospect questions about accounting capabilities. Essential for CFO and controller-level conversations.

references/projects-psa-deep-dive.md

Complete guide to NetSuite Projects & PSA (Professional Services Automation) for solution consultants:

• Core Concepts - What is PSA, why it matters, pain points and solutions
• Project Record Anatomy - Project types, key fields, task hierarchy
• Billing Models - Time & Materials, Fixed Fee, Milestone billing with rate hierarchies
• Time & Expense Management - Time entry flow, best practices, expense workflows
• Resource Management - Utilization calculations, capacity planning, resource assignment
• Revenue Recognition (ASC 606) - Five-step model, revenue arrangements, percent complete methods
• Key Reports - Project profitability, resource utilization, unbilled time & expense, budget vs. actual
• Demo Scenarios - Step-by-step scripts for project profitability, time entry, and invoicing demos
• Competitive Positioning - NetSuite vs. standalone PSA tools, project management tools, spreadsheets
• Proof Points - Key metrics and typical improvements to quote
• Quick Reference - Key SuiteQL tables for PSA analysis

When to read: Reference this file when preparing PSA-focused demos, explaining project lifecycle workflows, or addressing questions about resource management and billing automation. Essential for project manager and CFO conversations.