Client wants their analyst team to have every tracking event in their data warehouse. Clicking CSV export from Tracking for each send doesn't scale. Two SFMC-native tools handle bulk tracking exports:
- Tracking Data Extract - automated exports to SFTP on a schedule
- SQL Query Activity against Data Views - query tracking directly within SFMC
Both work. Which depends on where the data needs to land.
Option 1: Tracking Data Extract
Automation Studio > Data Extract Activity > Tracking Extract type. Pick the data type and date range; SFMC writes a CSV to the Safehouse. Then File Transfer Activity pushes it to the client's SFTP.
Types supported:
- Sent
- Opens
- Clicks
- Bounces
- Unsubscribes
- Complaints (spam reports)
- Not Sent
Typical automation:
Schedule (daily or weekly)
-> Data Extract Activity (Tracking Extract, date range: yesterday)
-> File Transfer Activity (push CSV to client SFTP)
Output is flat CSV with 1 row per event. Analysts load into their warehouse and query.
Retention advantage: Tracking Extract can pull years of data if it hasn't aged out. Some events retain 2 years in SFMC - extract them before they age out.
Option 2: SQL Query Activity against Data Views
Data Views are SFMC's system tables storing tracking data. Query with SQL Query Activity, write results to a DE.
| Data View | Contains |
|---|---|
_Sent |
Every email attempted |
_Open |
Every open event |
_Click |
Every click event |
_Bounce |
Every bounce, with BounceType |
_Unsubscribe |
Every unsubscribe |
_Complaint |
Every spam report |
_Job |
Send job metadata |
_Subscribers |
All Subscribers list |
Example: subscribers who opened any email in the last 30 days:
SELECT DISTINCT SubscriberKey, EmailAddress
FROM _Open
WHERE EventDate >= DATEADD(DAY, -30, GETDATE())
Writes to a DE. Use the DE as:
- Audience for a re-engagement campaign
- Source for additional SQL analysis
- Input to a File Drop for external systems
When to pick which
| Need | Tool |
|---|---|
| Export to client's data warehouse | Tracking Data Extract + File Transfer |
| Build internal SFMC segments from tracking | SQL Query Activity |
| One-time historical pull | Tracking Data Extract |
| Ongoing segmentation based on engagement | SQL Query Activity |
| Data for analysts outside SFMC | Tracking Data Extract |
| Keep everything inside SFMC | SQL Query Activity |
Retention limits
- Data Views default retention: 6 months
- Some event data: up to 2 years (depending on contract)
- Tracking Data Extract range: limited to what's retained - can't pull what's gone
Rule: set up archive automation early in the engagement. Weekly extract of _Open, _Click, _Sent, _Bounce into the client's warehouse or into archive DEs. By month six, you have everything backed up.
Archive pattern
If the client wants 2+ years of tracking queryable inside SFMC:
Weekly archive automation:
Schedule (Monday 2am)
-> SQL Query Activity: SELECT * FROM _Open WHERE EventDate BETWEEN X AND Y INTO Archive_Open DE
-> SQL Query Activity: Same for _Click, _Sent, _Bounce
Archive DEs grow over time but don't age out. Queries across archive + live data give unlimited retention.
For warehouse archive (outside SFMC):
Daily export:
Schedule (daily 1am)
-> Data Extract Activity (each tracking type, yesterday's data)
-> File Transfer Activity (push to client SFTP)
Either pattern works. Pick based on where downstream analysis happens.
Common mistakes
Not archiving early
Client asks at month 10 for last year's campaign data. It's gone. Data View retention ended at month 6.
Fix: start archive on day one, not when someone asks.
Trying to query Data Views from outside SFMC
Data Views are internal SFMC system tables. You can't connect a BI tool directly. Must export first.
Assuming all Data Views retain equally
_Sent retains differently than _Open and both differ from _Subscribers. Read the docs for your specific account's retention before promising a date range.
Joining Data Views without indexes
SQL Query Activities on Data Views against large volumes (millions of rows) can time out if the query doesn't use the supported join patterns. Keep queries simple and filtered; use primary keys whenever possible.
Pattern: Re-engagement campaign from Data Views
Common use case - find subscribers who haven't opened in 90 days and include them in a re-engagement journey:
SELECT s.SubscriberKey, s.EmailAddress
FROM _Subscribers s
LEFT JOIN (
SELECT DISTINCT SubscriberKey
FROM _Open
WHERE EventDate >= DATEADD(DAY, -90, GETDATE())
) o ON s.SubscriberKey = o.SubscriberKey
WHERE s.Status = 'Active' AND o.SubscriberKey IS NULL
INTO ReEngagement_Candidates_DE
Schedule weekly. Journey Builder reads from ReEngagement_Candidates_DE and triggers the re-engagement series.
Takeaway
Tracking Data Extract for exports to client warehouses and external systems. SQL Query Activity against Data Views for internal segmentation and in-SFMC analysis. Both work alongside each other on most engagements. Set up archive automations early - the alternative is telling the client their year-old campaign data is gone.
Architecting SFMC data export and archive strategy? Our Salesforce team ships tracking data pipelines with retention planning on production engagements. Get in touch ->
See our full platform services for the stack we cover.
