Purpose
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 the Messages for Analytics and REST permission added before email rows will appear.
Sample Output
Sample data — actual results will vary.
The 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
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 cleanly. Where a record type does not have a field — for example, emails do not have a task status — the query returns NULL for that column, keeping the output schema consistent across all three legs.
Emails (Message). The first block pulls emails from the Message table and labels them as EMAIL INBOUND or EMAIL OUTBOUND depending on the
incomingflag. It outputs subject, message date, author email as the owner, and recipient email as the contact. Email rows do not carry a company name or priority field — those come back NULL.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 native task attributes. Owner and contact resolve to full names via the joins rather than email addresses.
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 store it the same way tasks do.
Date filtering. Each block has its own independent date filter using
TO_DATE. All three must be updated together when changing the reporting window — the query has no shared date parameter. Change the start and end dates in all three WHERE clauses.Sorting by most recent activity.
ORDER BY 5 DESCsorts by the fifth column (activity_date), putting the most recent activities first across all three activity types combined.
Permissions Note for Message Records
The Message table is often restricted by role. Administrators typically have access by default, but non-admin roles may see errors or empty results for the email portion. To grant access, add the Messages for Analytics and REST permission to the role:
Go to Setup → Users/Roles → Manage Roles
Edit the role used by the person running the query
On the Permissions tab → Lists subtab, add Messages for Analytics and REST
Save
Customization Notes
Date range: update the
TO_DATEstart and end values in all three WHERE clauses together. They are independent — missing one will cause that activity type to return a different time window than the others.Email identity: email rows use
authoremailandrecipientemailas owner and contact, while tasks and events resolve to names via joins. To standardize, you can join the Message table to Employee or Contact on the email address fields to return names instead.Adding filters: restrict to specific employees, customers, or activity types by adding AND conditions to the relevant WHERE clause leg. For example, add
AND t.owner = <employee_id>to the Task block to scope to a single rep.Additional activity sources: add another UNION ALL block for other NetSuite activity record types (such as phone calls) if your account uses them, following the same column order.
Excel pivot analysis: pivot by
owner_nameandactivity_typefor activity volume by rep; pivot bycompany_namefor account engagement; groupactivity_dateby month for timeline trends.