eads from Data Extensions or System Data Views, applies filtering and aggregation, and writes the result to a staging Data Extension. This step prevents volume explosions by ensuring only relevant data is exported.
Best Practice: Always filter by a time window and truncate timestamps to avoid partial day exports or timezone drift issues. Use CAST(GETDATE() AS DATE) to ensure consistent daily boundaries.
/*
Target: Staging_Click_Export
Action: Overwrite
*/
SELECT
c.SubscriberKey,
c.JobID,
c.LinkName,
c.URL,
CAST(c.EventDate AS DATE) AS ClickDate
FROM _Click c
WHERE c.EventDate >= DATEADD(day, -1, CAST(GETDATE() AS DATE))
AND c.EventDate < CAST(GETDATE() AS DATE)
AND c.IsUnique = 1;
Rationale:
- Volume Control: Querying
_Click directly without filters can result in multi-million row exports, causing timeouts or overwhelming downstream parsers.
- Data Shaping: The query allows you to join with
JourneyActivity or Send data views to enrich the export with context not present in the raw event.
- Staging: Writing to a dedicated staging DE isolates the export payload from operational data, preventing lock contention.
Step 2: Data Extract Activity (Serialization)
The Data Extract activity converts the staging Data Extension into a flat file. It writes the file to the SFMC internal SFTP. Configuration options include delimiter selection, header inclusion, and compression.
Configuration Parameters:
- Source:
Staging_Click_Export (from Step 1).
- File Type: CSV or Pipe-Delimited.
- Delimiter:
| (Pipe is recommended to avoid conflicts with data containing commas).
- Include Header: Yes.
- Compression: ZIP enabled for bandwidth optimization.
- File Name:
click_export_%%YEAR%%%%MONTH%%%%DAY%%.csv.zip.
Rationale:
- Format Standardization: This activity enforces a consistent file structure for downstream consumers.
- Compression: Zipping reduces transfer time and storage footprint on the external SFTP.
- Naming Convention: Dynamic filenames using AMPscript-style tokens ensure unique files per run, preventing overwrites.
Step 3: File Transfer Activity (Egress)
The File Transfer activity moves the file from the SFMC internal SFTP to the external destination. This requires a pre-configured External File Location.
Architecture Decision:
- External File Location: Must be configured in
Setup > Data Management > Key Management + File Locations. This centralizes credentials and connection details.
- Activity Configuration:
- Source: SFMC SFTP (File name matching Step 2 output).
- Destination: External File Location (e.g.,
Client_SFTP_Drop).
- Destination Path:
/incoming/sfmc_exports/.
Rationale:
- Security: Credentials are stored in a secure vault, not inline in the automation.
- Reusability: The External File Location can be referenced by multiple automations, simplifying credential rotation.
- Network Handling: The activity manages the SFTP handshake, authentication, and file transfer protocol.
Pitfall Guide
1. The Silent SFTP Gap
- Explanation: The automation includes
SQL Query and Data Extract but omits File Transfer. The file lands on SFMC's SFTP, and the automation reports success. The external system never receives data.
- Fix: Audit all external export automations. If the destination is outside SFMC, a
File Transfer activity is mandatory.
2. The Volume Avalanche
- Explanation: The
Data Extract activity targets a raw source Data Extension (e.g., All_Subscribers) instead of a filtered staging DE. This generates massive files that timeout during transfer or crash downstream parsers.
- Fix: Always use a
SQL Query to filter and reduce the dataset before extraction. Validate row counts in the staging DE.
3. Credential Sprawl
- Explanation: SFTP credentials are hardcoded or duplicated across multiple
File Transfer activities. When credentials rotate, engineers must hunt through dozens of automations to update them.
- Fix: Use External File Locations. Update credentials in one place, and all referencing automations inherit the change.
4. Encryption/Transfer Conflation
- Explanation: Attempting to encrypt and transfer a file in a single
File Transfer activity. SFMC does not support simultaneous encryption and transfer in one step.
- Fix: Use two
File Transfer activities in sequence. The first encrypts the file on the SFMC SFTP using PGP. The second transfers the encrypted file to the external destination.
5. Key Rotation Blindness
- Explanation: PGP encryption is configured, but the team never tests key rotation. When the client rotates their public key, the encryption step fails silently or produces unreadable files, breaking the pipeline.
- Fix: Document the key rotation procedure. Schedule periodic tests where the PGP key is updated and the automation is run to verify end-to-end functionality.
6. Timezone Drift
- Explanation: The SQL query uses
GETDATE() without timezone awareness. SFMC operates in UTC, but the client expects data based on their local timezone (e.g., EST). This results in data appearing in the wrong daily file.
- Fix: Explicitly handle timezones in the SQL query using
DATEADD offsets or document the UTC expectation clearly with the client. Use CAST(GETDATE() AS DATE) to anchor to calendar days.
7. Delimiter Mismatch
- Explanation: The
Data Extract activity is configured with a comma delimiter, but the downstream parser expects pipe-delimited data. This causes parsing errors or data corruption.
- Fix: Standardize delimiter configuration across the pipeline. Verify the delimiter in the
Data Extract settings matches the consumer's requirements. Use pipe (|) as the default to minimize conflicts with data content.
Production Bundle
Action Checklist
Decision Matrix
| Scenario | Recommended Approach | Why | Cost/Complexity Impact |
|---|
| Internal Analytics | Query + Extract | Data remains within SFMC ecosystem for internal reporting. | Low |
| External Warehouse | Query + Extract + Transfer | Data must leave SFMC boundary to external SFTP. | Medium |
| Compliance/PII | Query + Extract + Encrypt Transfer + Transfer | PGP encryption required for sensitive data egress. | High |
| High Volume | Query (Filtered) + Extract (Zip) + Transfer | Reduces payload size and transfer time. | Medium |
| Real-Time Needs | API-based Export | Automation is batch-oriented; use API for near-real-time. | High |
Configuration Template
External File Location Setup
{
"Name": "Client_SFTP_Drop",
"Type": "SFTP",
"Host": "sftp.client-domain.com",
"Port": 22,
"Authentication": "SSH_KEY",
"PublicKey": "ssh-rsa AAAAB3NzaC1yc2E...",
"Path": "/incoming/sfmc_exports",
"Description": "External SFTP for daily click export"
}
Automation Studio Activity Sequence
Schedule: Daily at 23:00 UTC
|
+-- Activity 1: SQL Query
| Source: _Click
| Target: Staging_Click_Export
| Mode: Overwrite
|
+-- Activity 2: Data Extract
| Source: Staging_Click_Export
| File: click_export_%%YEAR%%%%MONTH%%%%DAY%%.csv
| Delimiter: |
| Compression: ZIP
| Location: SFMC SFTP
|
+-- Activity 3: File Transfer
Source: SFMC SFTP (click_export_*.csv.zip)
Destination: Client_SFTP_Drop
Path: /incoming/sfmc_exports/
Quick Start Guide
- Create External File Location: Navigate to
Setup > Data Management > Key Management + File Locations. Add a new SFTP location with host, port, and authentication details. Test the connection.
- Build SQL Query: Create a new Query Activity. Write a query to filter and shape data from a Data View into a staging Data Extension. Run and verify row counts.
- Add Data Extract: In Automation Studio, add a
Data Extract activity. Configure it to export the staging DE to a CSV/ZIP file on the SFMC SFTP.
- Add File Transfer: Add a
File Transfer activity. Set the source to the file generated in Step 3 and the destination to the External File Location created in Step 1.
- Run and Verify: Execute the automation manually. Check the external SFTP for the file. Confirm the file is readable and matches the expected schema. Schedule the automation once verified.
