Query data views for up to six months of Marketing Cloud subscriber and journey information using Automation Studio.
You compose the SQL statement that describes the query when you create a SQL Query activity. You can write the query against a data extension or the system’s data views. Tracking data displayed by clicking and opening is in Central Standard Time, does not observe Daylight Savings Time, and is rounded to the nearest second.
NOTE When profile attributes are created in Enterprise 2.0 accounts, new columns are added to the _EnterpriseAttribute table. Data view queries in Enterprise 2.0 accounts can return results from profile attribute columns in addition to the columns listed here.
NOTE If Add subscribers to this list is not selected for a triggered send, some data is not available at the enterprise level. Enterprise-level data views don’t include sends, opens, clicks, unsubscribes, Forward To A Friend data, and bounces. You can find this data by querying data views in the sender’s Marketing Cloud account.
NOTE Some data views allow you to retrieve more than six months’ data. However, queries returning large data sets can take longer to process and can impact system performance.
Data View: Automation Instance
By monitoring automation health, you may get a broad picture perspective of all automations within your business unit and increase the efficiency and success rate of your automations.
In Automation Studio, query the _AutomationInstance data view to prevent failures by identifying long-running processes and to solve failed automations by addressing the reason of failure.
Create the Data extension first, which will hold the query output.
Data extension columns
| NAME | DESCRIPTION | DATA EXTENSION DATA TYPE | NULLABLE |
|---|---|---|---|
| MemberID | The unique ID of the business unit. | Number | |
| AutomationName | The automation name. | Text | |
| AutomationDescription | The automation description. | Text | X |
| AutomationCustomerKey | The unique ID of the automation. | Text | |
| AutomationType | The automation’s starting source. Possible values are Schedule, File Drop, or Trigger. | Text | |
| AutomationNotificationRecipient_Complete | The email address where notifications about completed automations are sent. | Text | X |
| AutomationNotificationRecipient_Error | The email address where notifications about automation errors are sent. | Text | X |
| AutomationNotificationRecipient_Skip | The email address where notifications about skipped automations are sent. | Text | X |
| AutomationStepCount | The number of steps in the automation. | Number | |
| AutomationInstanceID | The unique ID of the automation run. | Text | |
| AutomationInstanceIsRunOnce | Whether the automation was set to run once. 1 = true, 0 = false. | Boolean | |
| FilenameFromTrigger | For file drop and trigger automations, the file that started the automation. | Text | X |
| AutomationInstanceScheduledTime_UTC | For scheduled automations, the time that the run was scheduled to begin. | Date | X |
| AutomationInstanceStartTime_UTC | The time that the automation run started. This value is null if the automation was skipped. | Date | X |
| AutomationInstanceEndTime_UTC | The time that the automation run ended. This value is null if the automation was skipped or is still running. | Date | X |
| AutomationInstanceStatus | The status of the automation run when the data view is queried. Possible values are QueuedFile, Initialized, Executing, Stopped, Complete, or Error. | Text | |
| AutomationInstanceActivityErrorDetails | The message from the error log, if applicable. If a system or unclassified error occurs, the value is System Error. If multiple errors occur, only the first message is displayed. To get details on multiple errors, use the _AutomationActivityInstance view. | Text | X |
Which Automations Failed Yesterday?
By evaluating automations that failed yesterday, you can identify deficiencies with your automations and prevent future failures.
SELECT automationcustomerkey, AutomationInstanceActivityErrorDetails, AutomationName
FROM [_automationinstance]
WHERE automationinstancestatus = 'Error'
AND automationinstanceendtime_utc >= Dateadd(day, -2, Getutcdate())
Which Automations Completed Yesterday?
Check that yesterday’s automations ran.
SELECT automationcustomerkey, AutomationInstanceActivityErrorDetails, AutomationName
FROM [_automationinstance]
WHERE automationinstancestatus = 'Complete'
AND automationinstanceendtime_utc >= Dateadd(day, -2, Getutcdate())
Which Automations Skipped Yesterday?
Skipped activities indicate an unhealthy automation, such as too little time between scheduled automations. Or, when a running instance was halted, several file drop automation instances waited. Find automations skipped yesterday and decide whether to act using this query.
SELECT automationcustomerkey, AutomationInstanceActivityErrorDetails, AutomationName
FROM [_automationinstance]
WHERE automationinstancestatus = 'Skipped'
AND automationinstanceendtime_utc >= Dateadd(day, -2, Getutcdate())
What Is the Average Duration of Each Automation?
Check your automations’ average performance for patterns and issues before they affect crucial processes. If an automation slows down, find out why and fix it. This query calculates each automation’s average duration for the past 1–31 days.
SELECT automationcustomerkey,
Avg(Datediff(minute, automationinstancestarttime_utc,
automationinstanceendtime_utc)) AS AvgDuration
FROM [_automationinstance] automation
GROUP BY automationcustomerkey
What Is the Latest End Time and Status of Each Automation?
View all automations with at least one run in the previous 6 months, including the latest end time and status. Identify critical failures and inactive automations using this data.
SELECT automationcustomerkey,
automationinstanceid,
automationinstancestatus,
automationinstanceendtime_utc
FROM (SELECT automationcustomerkey,
automationinstanceid,
automationinstancestatus,
automationinstanceendtime_utc,
Row_number()
OVER (
partition BY automationcustomerkey
ORDER BY automationinstanceendtime_utc DESC) AS run_count
FROM [_automationinstance]) AS instances
WHERE run_count = 1
Data View: Automation Activity Instance
Review automation activity health to boost automation efficiency and success.
In Automation Studio, query the _AutomationActivityInstance data view to find often failing or long-running activities to prevent failures.
Create the Data extension first, which will hold the query output.
Data extension columns
| NAME | DESCRIPTION | DATA EXTENSION DATA TYPE | NULLABLE |
|---|---|---|---|
| MemberID | The unique ID of the business unit. | Number | |
| AutomationName | The automation name. | Text | |
| AutomationCustomerKey | The unique ID of the automation. | Text | |
| AutomationInstanceID | The unique ID of the automation run. | Text | |
| ActivityType | The activity type. Possible values are listed in the Activity Type IDs table. | Number | |
| ActivityName | The activity name. | Text | |
| ActivityDescription | The activity description. | Text | X |
| ActivityCustomerKey | The unique ID of the activity. | Text | |
| ActivityInstanceStep | Where the activity occurs in the automation. For example, 3.2 is step 3, activity 2. | Text | |
| ActivityInstanceID | The unique ID of the activity run. | Text | |
| ActivityInstanceStartTime_UTC | The time that the activity run started. This value is null if the automation was skipped. | Date | X |
| ActivityInstanceEndTime_UTC | The time that the activity run ended. This value is null if the automation was skipped, is still running, or wasn’t selected as part of an Advanced Run Once. | Date | X |
| ActivityInstanceStatus | The status of the activity run when the data view is queried. Possible values are Initialized, Executing, Complete, Error, or NotSelected. | Text | |
| ActivityInstanceStatusDetails | The message from the error log, if applicable. If a system or unclassified error occurs, the value is System Error. | Text | X |
Which Automations Are Failing and Why?
Identify the cause of automation errors to prevent future failures. An automation error can be categorized as a configuration error or system error.
- A configuration error happens when an activity or dependency is not successfully set up. For example, suppose a file transfer operation is seeking for a file called test.csv and it does not exist. Modify your automation or its dependencies to resolve a configuration problem.
- A system error is more general and frequently short-term. For example, suppose a server runs out of memory or a connection fails. In many circumstances, you do not need to adjust your automation to resolve a system fault; simply run it again.
Use this query to identify actions that failed due to a configuration problem. Check the error message to see how you may correct the problem.
SELECT activitycustomerkey
FROM [_automationactivityinstance]
WHERE activityinstancestatus = 'Error'
AND activityinstancestatusdetails NOT LIKE 'System Error occurred%'
Use this query to find activities that failed because of a system error. Retry the associated automation.
SELECT activitycustomerkey
FROM [_automationactivityinstance]
WHERE activityinstancestatus = 'Error'
AND activityinstancestatusdetails LIKE 'System Error occurred%'
Which Activities Are at Risk of Timing Out?
Proactively identify long-running activities that are approaching the 30-minute time limit.
Use this query to find query activities with an average duration greater than 20 minutes. Optimize the query activity, or break it up into multiple steps.
SELECT activitycustomerkey,
avgduration
FROM (SELECT activitycustomerkey,
Avg(Datediff(minute, activityinstancestarttime_utc,
activityinstanceendtime_utc)
) AS AvgDuration
FROM [_automationactivityinstance] activity
WHERE activity.activitytype = 300
GROUP BY activitycustomerkey) durations
WHERE avgduration > 20
Activity type equal to 300 because it is query activity
Use this query to find SSJS activities with an average duration greater than 20 minutes. Optimize the SSJS activity, or break it up into multiple steps.
SELECT activitycustomerkey,
avgduration
FROM (SELECT activitycustomerkey,
Avg(Datediff(minute, activityinstancestarttime_utc,
activityinstanceendtime_utc)
) AS AvgDuration
FROM [_automationactivityinstance] activity
WHERE activity.activitytype = 150
GROUP BY activitycustomerkey) durations
WHERE avgduration > 20
activity type = 150 because it is a SSJ activity
What is the Average Duration of Each Activity?
Monitor the average performance of your activities to identify patterns and address issues before they affect important processes. Use this query to calculate the average time of each activity during the last 1–31 days.
SELECT activitycustomerkey,
Avg(Datediff(minute, activityinstancestarttime_utc,
activityinstanceendtime_utc)
) AS AvgDuration
FROM [_automationactivityinstance] activity
GROUP BY activitycustomerkey
