Blog.

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