SFMC Send Classification Report

SWARM Guide

Build a quarterly report showing emails sent last quarter, broken down by Business Unit, Send Classification, and Send Count.

Why this Matters:

Marketing leaders want validation that emails are properly categorized so that they will honor unsubscribe requests and comply with CAN-SPAM laws.

Session: Standalone SWARM - Session (1 of 1)

Audience: Access to SFMC Contact Builder and Automation Studio. Moderate knowledge of SQL needed.

Author: Crafted by Steve Weathers, Telios Guild - last updated Sept 22, 2026

1. What Gets Built (Overview)

A Data Extension: SendClassificationByJob
A nested SQL query

Not built this session: an Automation to run the query on a quarterly schedule, a friendly name for TriggeredSend, or Query Studio validation — all good follow-on work for later.

2. Data Extension: SendClassificationByJob

Participant: needs Contact Builder / Data Extension creation access; comfortable with basic field-type sizing (Number vs. Decimal, text length).

Config: Non-sendable. Data retention set to 6 months.

Build this DE together before writing the query — the query's SELECT list is aliased to match these field names exactly.

DE Fields

JobID — Number
- Primary Key
SendClassification — Text (50)
BusinessUnitID — Number
BusinessUnitName — Text (100)
EmailName — Text (250)
EmailSubject — Text (500)
SendCount — Number
EmailID — Number
FromName — Text (100)
FromEmail — Text (254)
JourneyName — Text (250)
TriggeredSend — Text (100)
- Raw Customer Key, not a friendly name
JobCreatedByUserID — Number
JobCreatedDate — Date
LogDate — Date

3. Scratch Data Extensions

If you have Query Studio: skip this — Query Studio's results grid does the same job interactively.

If you don't: create these test DEs now:

Config: Non-sendable. Data retention: 1 week. Namespace the DE name (e.g. with your initials or a session code) to avoid conflicting with another scratch data extension

You'll use these DEs in Section 4.1 to check the inner and outer sections of the query before they are used together.

SendClassification_TestSent

JobID — Number
SubscriberKey — Text (255)

SendClassification_TestJob

JobID — Number
BusinessUnitID - Number
BusinessUnitName - Text(100)
EmailID - Number

4. SQL Query — Build Incrementally

Participant: needs Automation Studio access (Query Activity Builder); some SQL experience.

Build this in layers rather than pasting the whole query at once — each layer is independently testable, which helps troubleshooting later.

4.1 Send Dedup Layer

SELECT DISTINCT

s.JobID

,s.SubscriberKey

FROM [_Sent] s

LEFT JOIN [_Subscribers] sub

ON s.SubscriberKey = sub.SubscriberKey

WHERE s.EventDate BETWEEN

DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()) - 1, 0)

AND

DATEADD(second, -1, DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()), 0))

AND s.Domain NOT IN ('example.net')

AND sub.EmailAddress NOT IN ('mytest1@example.com', 'mytest2@example.org')

AND sub.EmailAddress NOT LIKE '%+%@gmail.com'

Update the example.net/example.com values with your own company domain and test email addresses to remove unwanted internal and test email sends.

Test this layer in isolation, before it's nested inside the full query, to catch unexpected results while they're still easy to isolate.

If you have Query Studio: run this piece there and check the results grid directly.

If you don't: point the Query Activity at the Scratch DE (SendClassification_TestSent) and check the row count and a few sample rows.

Once it looks right, try removing the _Subscribers join and the two EmailAddress lines — the query should still run and return meaningful (if less filtered) results. Put the _Subscribers join back before continuing.

4.2 Full Query

Nest the previous query, join to _Job, resolve BU name and Journey. Paste in each section below and discuss/update with the group.

Optional - to test the outer query, change the Inner Join _Job to a FROM statement and send output to SendClassification_TestJob

SELECT TOP 1000000

j.AccountID AS BusinessUnitID

,CASE j.AccountID

WHEN 100001234 THEN 'Corporate Marketing'

WHEN 100005678 THEN 'Retail Division'

WHEN 100009012 THEN 'Partner Programs'

ELSE 'Other BU - ' + CONVERT(VARCHAR(20), j.AccountID )

END AS BusinessUnitName

,j.EmailID

,j.JobID

,j.SendClassificationType AS SendClassification

,COUNT(1) AS SendCount

,j.EmailName

,j.EmailSubject

,j.FromName

,j.FromEmail

,j.AccountUserID AS JobCreatedByUserID

,j.CreatedDate AS JobCreatedDate

,jrn.JourneyName

,j.TriggeredSendCustomerKey AS TriggeredSend

FROM (

-- Paste Section 4.1's SentDedup query here

) AS SentDedup

INNER JOIN [_Job] AS j

ON SentDedup.JobID = j.JobID

LEFT JOIN [_JourneyActivity] AS JA

ON j.[TriggererSendDefinitionObjectID] = JA.[JourneyActivityObjectID]

LEFT JOIN [_Journey] AS jrn

ON JA.VersionID = jrn.VersionID

WHERE j.EmailName IS NOT NULL

AND j.EmailSubject NOT LIKE '[[]Test]%'

GROUP BY

j.AccountID

,j.EmailID

,j.SendClassificationType

,j.JobID

,j.EmailName

,j.EmailSubject

,j.FromName

,j.FromEmail

,j.AccountUserID

,j.CreatedDate

,jrn.JourneyName

,j.TriggeredSendCustomerKey

ORDER BY

j.AccountID

,j.SendClassificationType

,j.EmailName

,SendCount DESC

Remaining steps, in order:

Update the CASE statement with your own org's real AccountID → BU name pairs
Validate the syntax
Set the target DE to SendClassificationByJob, action = Overwrite
Save the query
Execute a “Run Once” and view the Action Log
- Pro Tip: attempting to “Run Once” again will produce a warning if the query is still running

5. Done Criteria

Query completes in the Action Log — no errors
View records in the SendClassificationByJob DE
[Test]-subject rows are not in the output
BusinessUnitName contains friendly BU names
Check a few rows to confirm EmailName + EmailSubject matches SendClassification
SendCount looks right for at least one send job you know was high-volume last quarter

6. Wrap-Up

Mission Accomplished! Well Done! You built and ran a complete quarterly Send Classification Report, from Data Extension to a live query against production dataviews.

Next Steps: create an Automation to run this query on a quarterly schedule.

Was there anything in this session that we have questions about?

7. After Party

Can we change the date range to get more recent data?

In the Inner Select clause update

FROM

s.EventDate BETWEEN

DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()) - 1, 0)

AND

DATEADD(second, -1, DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()), 0))

s.EventDate >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 6, 0)

Can we add _Bounce _Unsubscribe and _Complaint counts to the report?

© 2026 Telios Guild LLC. All rights reserved.This document and the accompanying code, queries, and architecture are provided for educational purposes only, as part of the Telios Guild training series. No warranty of any kind — express or implied — is made regarding accuracy, completeness, or fitness for any particular purpose. This solution has not been hardened for production use and is not yet suitable for commercial deployment as-is.Want help deploying this or similar solutions in your environment? Telios Guild offers hands-on implementation support — contact us at www.teliosguild.com

Report abuse