Salesforce Marketing Cloud Data Views: _AutomationInstance (Automation Studio)

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.

---