The _AutomationInstance
data view in Salesforce Marketing Cloud (SFMC) provides a detailed look at how your automations are performing. It’s essential for reviewing execution results, diagnosing issues, and improving the reliability and efficiency of automation processes in Automation Studio.
This blog explains each field in the _AutomationInstance
data view, outlines real-world use cases, and provides SQL query examples to help you manage automation health and avoid process failures.
What is the _AutomationInstance Data View?
The _AutomationInstance
data view returns execution data about each automation run across your account. It logs the schedule, start/end times, execution status, triggering details, and error messages (if any). It’s a go-to resource for operational reporting and troubleshooting automation failures.
Key Benefits of _AutomationInstance Data View
- Track Automation Runs: Monitor every execution instance across all BUs.
- Error Diagnostics: Investigate failure causes and error details.
- Schedule Review: View actual run vs. scheduled run times.
- Efficiency Optimization: Spot long-running or skipped automations.
Fields in _AutomationInstance Data View & Their Uses
Field | Type | Description & Use Case |
---|---|---|
MemberID | Number | Business Unit ID. Used for multi-BU reporting. |
AutomationName | Text(400) | The name of the automation. Core identifier for business reporting. |
AutomationCustomerKey | Text | Unique key used to reference the automation (for joins and APIs). |
AutomationDescription | Text(400) | Optional description of the automation. Useful for documentation. |
AutomationType | Text(50) | How the automation is triggered: Schedule, File Drop, or Trigger. |
AutomationNotificationRecipient_Complete | Text(500) | Email address receiving completion notifications. |
AutomationNotificationRecipient_Error | Text(500) | Email address for error alerts. Useful for alerting setups. |
AutomationNotificationRecipient_Skip | Text(500) | Email address for skipped automation alerts. |
AutomationStepCount | Number | Number of steps in the automation. Useful for complexity analysis. |
AutomationInstanceID | Text(50) | Unique ID of the individual automation run. Used in joins with _AutomationActivityInstance . |
AutomationInstanceIsRunOnce | Boolean | Whether the automation is one-time (1) or recurring (0). |
FilenameFromTrigger | Text(4000) | File name that triggered the automation (for file drop or trigger types). Useful for troubleshooting. |
AutomationInstanceScheduledTime_UTC | Date | Scheduled start time. Useful for schedule validation. |
AutomationInstanceStartTime_UTC | Date | Actual time the automation started. Enables latency analysis. |
AutomationInstanceEndTime_UTC | Date | Actual end time. Useful for tracking execution duration. |
AutomationInstanceStatus | Text(400) | Final status of the automation run: QueuedFile, Initialized, Executing, Stopped, Complete, or Error. |
AutomationInstanceActivityErrorDetails | Text(4000) | First error message logged for the run. Crucial for diagnosing failed automations. |
When and Why to Use _AutomationInstance Data View?
When to Use?
- To monitor execution status of automations.
- To audit timing vs. actual run.
- When diagnosing automation errors.
- To review performance of scheduled vs. triggered workflows.
Why Use It?
- Prevent Failures: Identify long-running or errored automations.
- Ensure Accountability: Track notification recipients and schedules.
- Optimize Operations: Detect inefficiencies or skipped runs.
- Enable Alerting: Build automation health dashboards.
10 Example Scenarios and Queries
1. Retrieve All Completed Automations in Last 7 Days
SELECT AutomationName, AutomationInstanceStartTime_UTC, AutomationInstanceEndTime_UTC
FROM _AutomationInstance
WHERE AutomationInstanceStatus = 'Complete'
AND AutomationInstanceStartTime_UTC >= DATEADD(day, -7, GETDATE())
Use Case: Report on recent successful executions.
2. Find Failed Automation Runs
SELECT AutomationName, AutomationInstanceStartTime_UTC, AutomationInstanceActivityErrorDetails
FROM _AutomationInstance
WHERE AutomationInstanceStatus = 'Error'
Use Case: Troubleshoot error patterns across workflows.
3. Track Long-Running Automations
SELECT AutomationName, DATEDIFF(minute, AutomationInstanceStartTime_UTC, AutomationInstanceEndTime_UTC) AS Duration
FROM _AutomationInstance
WHERE AutomationInstanceEndTime_UTC IS NOT NULL
ORDER BY Duration DESC
Use Case: Identify slow-performing automation runs.
4. View Automation Run Counts by Status
SELECT AutomationInstanceStatus, COUNT(*) AS RunCount
FROM _AutomationInstance
GROUP BY AutomationInstanceStatus
Use Case: Health overview across all automations.
5. Upcoming Scheduled Automations
SELECT AutomationName, AutomationInstanceScheduledTime_UTC
FROM _AutomationInstance
WHERE AutomationInstanceScheduledTime_UTC > GETDATE()
Use Case: Forecast and monitor upcoming processes.
6. Join with _AutomationActivityInstance for Error Breakdown
SELECT a.AutomationName, aa.ActivityName, aa.ActivityStatus, aa.ErrorMessage
FROM _AutomationInstance a
JOIN _AutomationActivityInstance aa
ON a.AutomationInstanceID = aa.AutomationInstanceID
WHERE a.AutomationInstanceStatus = 'Error'
Use Case: Pinpoint step-level failures.
7. Find File Triggered Automations and Files
SELECT AutomationName, FilenameFromTrigger
FROM _AutomationInstance
WHERE AutomationType = 'File Drop'
Use Case: Trace automation back to uploaded files.
8. Automations with Most Steps
SELECT AutomationName, AutomationStepCount
FROM _AutomationInstance
ORDER BY AutomationStepCount DESC
Use Case: Analyze complexity of automation designs.
9. Recently Skipped Automations
SELECT AutomationName, AutomationInstanceStatus, AutomationInstanceScheduledTime_UTC
FROM _AutomationInstance
WHERE AutomationInstanceStatus = 'Stopped'
Use Case: Review non-executed or skipped automations.
10. Weekly Automation Health Summary
SELECT CAST(AutomationInstanceStartTime_UTC AS DATE) AS RunDate, AutomationInstanceStatus, COUNT(*) AS TotalRuns
FROM _AutomationInstance
WHERE AutomationInstanceStartTime_UTC >= DATEADD(day, -7, GETDATE())
GROUP BY CAST(AutomationInstanceStartTime_UTC AS DATE), AutomationInstanceStatus
Use Case: Build a dashboard showing weekly trends.
Conclusion
The _AutomationInstance
data view is your best friend when it comes to auditing, debugging, and optimizing automations in Salesforce Marketing Cloud. Use it in combination with _AutomationActivityInstance
to drill down into step-level performance and build operational dashboards for error tracking and process efficiency.