SFMC Tips #45 : How to Bulk Retrieve Execution Times of Automation Activities in Each Step of Automation Studio

Photo by Stephan Louis on Unsplash

Have you ever wanted to find out:

1. What time the automation activities placed in the steps of Automation Studio in Salesforce Marketing Cloud started,
2. What time they ended, and
3. How many minutes and seconds it took to complete them?

All summarized in one view?

You can easily find out this information using the following SQL query. Just replace “XXXXXXXXXXXXXXX” in the WHERE clause with the name of the automation you want to investigate and run it in Query Studio.

SELECT TOP 10000 
    AutomationName,
    ActivityName,
    ActivityInstanceStep,
    CONVERT(VARCHAR(19), DATEADD(HH, 9, ActivityInstanceStartTime_UTC), 120) AS [ActivityInstanceStartTime],
    CONVERT(VARCHAR(19), DATEADD(HH, 9, ActivityInstanceEndTime_UTC), 120) AS [ActivityInstanceEndTime],
    FORMAT(DATEDIFF(S, ActivityInstanceStartTime_UTC, ActivityInstanceEndTime_UTC) / 60, '00') + ':' + 
    FORMAT(DATEDIFF(S, ActivityInstanceStartTime_UTC, ActivityInstanceEndTime_UTC) % 60, '00') AS [Duration]
FROM 
    _automationactivityinstance
WHERE 
    AutomationName = 'XXXXXXXXXXXXXXX'
    AND DATEADD(HH, 9, ActivityInstanceStartTime_UTC) >= CONVERT(DATE, DATEADD(HH, -9, GETDATE()), 111)
ORDER BY 
    CONVERT(DECIMAL, ActivityInstanceStep) ASC, 
    DATEDIFF(S, ActivityInstanceStartTime_UTC, ActivityInstanceEndTime_UTC) DESC

• Since this data view can only extract data from 1 to 31 days ago, it cannot extract the current day’s data. The sample SQL targets yesterday’s automations.
• The extracted dates (ActivityInstanceStartTime_UTC etc.) are in the UTC time zone, so you will need to adjust them for your country’s time zone. The current 9-hour adjustment is based on my country’s time zone, Japan.
• The Getdate() function extracts dates in the CST time zone, so you will need to adjust them for your country’s time zone. (In my country, Japan, there is a 15-hour time difference, so to extract the data as the previous day’s data, we need to subtract 9 hours.)
• The results will be sorted by step order. Within the same step, activities will be sorted in descending order of processing time (Duration).

Is this magic?

No, it’s just a clever idea. 😎

Feel free to use it!

Thank you for reading.

Nobuyuki Watanabe

https://www.linkedin.com/in/nobuyuki-watanabe/ (+Follow me)