Salesforce Marketing Cloud Data Views: _AutomationActivityInstance (Automation Studio)

Posted on by admin

The _AutomationActivityInstance data view in Salesforce Marketing Cloud (SFMC) allows you to monitor the execution status of each individual activity within your automations. This granular visibility helps you identify recurring issues, bottlenecks, or failures in specific steps.

This blog explains each field in the _AutomationActivityInstance data view, shares use cases, and provides SQL query examples to improve automation reliability and transparency.

What is the _AutomationActivityInstance Data View?

The _AutomationActivityInstance data view captures performance metrics for each activity run in Automation Studio. You can use this data to track performance, detect issues, and investigate automation failures in greater detail than the _AutomationInstance view.

Key Benefits of _AutomationActivityInstance Data View

  • Step-Level Insight: Drill into the success/failure of individual automation activities.
  • Detailed Status Reporting: Understand when and why specific activities fail.
  • Troubleshooting Tool: Gain context about where automations break down.
  • Optimization: Identify long-running or repeated issues in complex workflows.

Fields in _AutomationActivityInstance Data View & Their Uses

FieldTypeDescription & Use Case
MemberIDNumberID of the business unit running the automation.
AutomationNameText(400)Friendly name of the automation.
AutomationCustomerKeyText(400)Unique key for the automation (used in joins and tracking).
AutomationInstanceIDText(50)Links to specific execution instance of the automation. Used to join with _AutomationInstance.
ActivityTypeNumberNumeric ID for the type of activity (e.g., Send Email, SQL Query, File Transfer).
ActivityNameText(400)Descriptive name of the activity in Automation Studio.
ActivityDescriptionText(400)Details about what the activity is doing (if documented).
ActivityCustomerKeyText(400)Unique identifier for the activity (used in API or config).
ActivityInstanceStepText(50)Indicates order/position within the automation (e.g., 2.1 = step 2, first activity).
ActivityInstanceIDText(50)Unique ID for the specific activity execution.
ActivityInstanceStartTime_UTCDateWhen the activity started. Helps identify performance issues.
ActivityInstanceEndTime_UTCDateWhen the activity ended. Can be null if skipped or running.
ActivityInstanceStatusText(256)Status at the time of query: Initialized, Executing, Complete, Error, NotSelected.
ActivityInstanceStatusDetailsText(4000)Error or system message, if available. Crucial for diagnostics.

Note: Use the Activity Type IDs reference from Salesforce documentation for decoding the ActivityType field.

When and Why to Use _AutomationActivityInstance Data View?

When to Use?

  • To debug automation errors at the activity level.
  • To analyze execution time and performance of specific tasks.
  • When building error reports or dashboards.
  • To ensure automation activities follow expected run patterns.

Why Use It?

  • Pinpoint Issues: Zoom in on which step failed in a multi-step automation.
  • Refine Scheduling: Identify tasks that consistently run long.
  • Improve Monitoring: Build accurate dashboards for automation health.
  • Prioritize Fixes: See which types of activities fail most often.

10 Example Scenarios and Queries

1. List All Failed Activities in Last 7 Days

SELECT AutomationName, ActivityName, ActivityInstanceStatusDetails, ActivityInstanceEndTime_UTC 
FROM _AutomationActivityInstance 
WHERE ActivityInstanceStatus = 'Error' 
AND ActivityInstanceEndTime_UTC >= DATEADD(day, -7, GETDATE())

Use Case: Diagnose what activities have recently failed.

2. Longest Running Activities

SELECT AutomationName, ActivityName, DATEDIFF(minute, ActivityInstanceStartTime_UTC, ActivityInstanceEndTime_UTC) AS DurationMinutes 
FROM _AutomationActivityInstance 
WHERE ActivityInstanceStatus = 'Complete' 
AND ActivityInstanceEndTime_UTC IS NOT NULL 
ORDER BY DurationMinutes DESC

Use Case: Find bottlenecks in automation flows.

3. Count of Activity Runs by Type

SELECT ActivityType, COUNT(*) AS TotalRuns 
FROM _AutomationActivityInstance 
GROUP BY ActivityType

Use Case: Identify which types of tasks are used most.

4. Find Steps that Failed in a Specific Automation

SELECT ActivityInstanceStep, ActivityName, ActivityInstanceStatusDetails 
FROM _AutomationActivityInstance 
WHERE AutomationName = 'Monthly_Campaign_Refresh' 
AND ActivityInstanceStatus = 'Error'

Use Case: Drill into failures of a specific automation.

5. Get All Currently Executing Activities

SELECT AutomationName, ActivityName, ActivityInstanceStartTime_UTC 
FROM _AutomationActivityInstance 
WHERE ActivityInstanceStatus = 'Executing'

Use Case: Monitor real-time automation execution.

6. Activities with System Errors

SELECT AutomationName, ActivityName, ActivityInstanceStatusDetails 
FROM _AutomationActivityInstance 
WHERE ActivityInstanceStatusDetails = 'System Error'

Use Case: Identify system-level automation issues.

7. Recently Completed Activities

SELECT AutomationName, ActivityName, ActivityInstanceEndTime_UTC 
FROM _AutomationActivityInstance 
WHERE ActivityInstanceStatus = 'Complete' 
AND ActivityInstanceEndTime_UTC >= DATEADD(day, -3, GETDATE())

Use Case: Confirm successful activity runs.

8. Run Order of Activities in a Specific Automation

SELECT ActivityInstanceStep, ActivityName 
FROM _AutomationActivityInstance 
WHERE AutomationName = 'Daily_Imports' 
ORDER BY ActivityInstanceStep

Use Case: Visualize execution sequence.

9. Total Errors by Activity

SELECT ActivityName, COUNT(*) AS ErrorCount 
FROM _AutomationActivityInstance 
WHERE ActivityInstanceStatus = 'Error' 
GROUP BY ActivityName 
ORDER BY ErrorCount DESC

Use Case: Identify problematic automation tasks.

10. Join With _AutomationInstance for Context

SELECT ai.AutomationName, aa.ActivityName, aa.ActivityInstanceStatus, aa.ActivityInstanceStatusDetails 
FROM _AutomationActivityInstance aa 
JOIN _AutomationInstance ai 
ON aa.AutomationInstanceID = ai.AutomationInstanceID

Use Case: Provide full automation run context per activity.

Conclusion

The _AutomationActivityInstance data view is critical for step-level visibility into automation performance and health. Whether you’re debugging errors, monitoring execution times, or reporting on efficiency, this view empowers you to manage Automation Studio at a deeper operational level.

Combine it with _AutomationInstance and system alerts to build robust monitoring and alerting frameworks for your SFMC environment.

Related Posts