Purpose of this query
This SuiteQL statement creates a single, consistent activity log by combining three different NetSuite record types: Message (email), Task, and CalendarEvent. Each record type stores activity data differently and has different fields, which makes unified reporting extremely difficult using saved searches. This query standardizes the output so you can analyze all activity in one Excel table, build pivot tables by owner, contact, company, and activity type, and track engagement volume over time. Update the date range filters to match your reporting window. Note that access to the Message record is permission-sensitive: the query typically works for Administrators, but other roles may need additional permissions to view Message data.
SuiteQL
SELECT
m.id,
m.externalid,
CASE m.incoming WHEN 'F' THEN 'EMAIL OUTBOUND' ELSE 'EMAIL INBOUND' END AS activity_type,
m.subject AS title,
m.messagedate AS activity_date,
NULL AS status,
NULL AS priority,
m.authoremail AS owner_name,
m.recipientemail AS contact_name,
NULL AS company_name
FROM Message m
WHERE m.messagedate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
AND m.messagedate <= TO_DATE('2025-06-30', 'YYYY-MM-DD')
UNION ALL
SELECT
t.id,
t.externalid,
'TASK' AS activity_type,
t.title,
t.startdate AS activity_date,
t.status,
t.priority,
e2.firstname || ' ' || e2.lastname AS owner_name,
c2.firstname || ' ' || c2.lastname AS contact_name,
comp2.companyname AS company_name
FROM Task t
LEFT JOIN Employee e2 ON t.owner = e2.id
LEFT JOIN Contact c2 ON t.contact = c2.id
LEFT JOIN Customer comp2 ON t.company = comp2.id
WHERE t.startdate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
AND t.startdate <= TO_DATE('2025-06-30', 'YYYY-MM-DD')
UNION ALL
SELECT
ce.id,
ce.externalid,
'EVENT' AS activity_type,
ce.title,
ce.startdate AS activity_date,
ce.status,
NULL AS priority,
e.firstname || ' ' || e.lastname AS owner_name,
c.firstname || ' ' || c.lastname AS contact_name,
comp.companyname AS company_name
FROM CalendarEvent ce
LEFT JOIN Employee e ON ce.owner = e.id
LEFT JOIN Contact c ON ce.contact = c.id
LEFT JOIN Customer comp ON ce.company = comp.id
WHERE ce.startdate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
AND ce.startdate <= TO_DATE('2025-06-30', 'YYYY-MM-DD')
ORDER BY 5 DESC
Sample Output
How the query works
Standardizing three different activity sources into one schema
Each SELECT returns the same set of columns in the same order so UNION ALL can combine them. Where a record type does not have a field (for example, emails do not have task status), the query returns NULL for that column.
Emails (Message)
The first block pulls emails from the Message table and labels them as EMAIL INBOUND or EMAIL OUTBOUND depending on the incoming flag. It outputs subject, message date, author email as the owner, and recipient email as the contact.
Tasks (Task)
The second block pulls tasks and enriches the data by joining to Employee (owner), Contact, and Customer (company). It includes status and priority since those are task attributes.
Events (CalendarEvent)
The third block pulls calendar events, joins to Employee, Contact, and Customer, includes event status, and sets priority to NULL because events do not typically store it the same way tasks do.
Date filtering
Each block has its own date filter using TO_DATE with the same range. Change the start and end dates in all three WHERE clauses to adjust the reporting period.
Sorting
ORDER BY 5 DESC sorts by activity_date, putting the most recent activities first.
Permissions note for Message records
The Message table is often restricted. Administrators typically have access by default, but non-admin roles may see errors or empty results for the email portion unless their role permissions allow access to messages and related communication records.
This is a dedicated NetSuite permission (Messages for Analytics and REST) introduced for exactly this use case. Here's how to add it:
Go to Setup → Users/Roles → Manage Roles
Edit the role used by the person running the query
Permissions tab → Lists subtab
Add "Messages for Analytics and REST"
Save
Why this is valuable and why saved searches struggle
• Different record types and field models: email, tasks, and events are separate tables with different schemas.
• Saved search limitations: saved searches generally do not support true UNION operations across unrelated record types into one flat export.
• Consistent output for Excel: one export makes it easy to build a unified engagement report without stitching multiple exports together.
What you can do in Excel with this output
• Activity volume by rep: pivot by owner_name and activity_type.
• Engagement by account: pivot by company_name and activity_type.
• Contact coverage: filter by contact_name and count activities.
• Timeline trends: group by month using activity_date.
• Outbound vs inbound email ratios: filter activity_type and calculate mix.
Customization notes
• Change the date range: update all three date ranges together.
• Improve email identity: emails use authoremail and recipientemail, while tasks and events resolve to names through joins. You can standardize by adding email fields for contacts and employees if needed.
• Add filters: restrict to specific employees, customers, or activity types by adding WHERE conditions to each leg.
• Add additional activity sources: add another UNION ALL block for other activity records if your account uses them.