Troubleshoot Automated Events

Learning Objectives

After completing this unit, you’ll be able to:

• Review Automation Studio error information.
  
• Perform standard troubleshooting steps.
  

Find Activity Information

It would be great if everything you created worked perfectly the first time around. However, there are often many moving parts in automations and errors do happen. The important thing is that Automation Studio can tell you what went wrong so you can get everything back on track. Here’s how.

In Automation Studio, click the Activities tab. Here Automation Studio breaks down your activities into different categories, and you can get to the specific activity you want to review.

If you have an error, click the activity to review information about the error. You can find the error information in the Detail column of the Action Log tab.

Solve Any Errors

So what do you do with the error information? Consult the documentation! We provide a list of potential error messages, as well as some possible resolutions to help you troubleshoot. We group the potential error messages into a few categories.

SQL Query Activity Errors

These errors relate primarily to problems with the data from the query activity, such as mismatched data types or values. You might also run into missing data extensions or queries. Review this list for the error you find and follow the advice to correct it.

File Transfer Activity Errors

These errors generally deal with authentication issues or missing files. Be prepared to review your usernames, passwords, and encryption settings.

Import Activity Errors

These errors revolve around missing files or incorrect file locations. Review the advice and make sure everything is in its place.

Automation Studio Best Practices

When you work with Automation Studio, timing is key. Make sure to give your activities enough time to complete their function before you move on to the next step. It doesn’t make sense to send a delivery truck out if the supplies aren’t even on the loading dock, right? Importing or exporting millions of records can take some time, so give the processes the space they need to run.

Query Activities

Query activities can take a great deal of time and resources, so they need more attention than, say, a simple wait activity. It’s a good idea to put query activities in their own step to make sure you allow them to run unhindered by other activities. If you know your account is processing large sends at the same time, move the query activity to a time where there’s less activity. And if you see that your query activity is consistently taking longer than 30 minutes to complete, it might be time to optimize that query and break it into different segments. Here are some tips.

• Rewrite your queries to use the primary key value of a data extension in your SELECT statement.
  
• Specify fields instead of using a SELECT * statement.
  
• Limit your column size to the maximum number of characters instead of the default 50 character limit.
  
• Store numerical values with the Number data type, as opposed to Text.
  
• Move data between columns of like data types.
  
• Limit your tables to 4000 total characters when possible.
  

You can also segment the query into several smaller queries, then join that data later. Your processes run more smoothly and efficiently, and you still get the data you need!

For example, Get Cloudy consultant Alex is helping his client with another SQL Query activity. The client is trying to retrieve data about jobs, opens, and subscribers from three different data views.

• _Jobs
  
• _Opens
  
• _Subscribers
  

The query constantly times out, so Alex decides to break these into three separate queries.

Jobs Query

j.JobID,
j.EmailName,
j.DeliveredTime,
from [_Job] j
where
j.EmailID = 123456

Opens Query

o.JobID,
o.SubscriberID,
o.EventDate,
from [_Open] o
where
o.IsUnique = 1
and j.EmailID = 123456

Subscribers Query

s.SubscriberID,
s.EmailAddress,
s.SubscriberKey
from [_Subscribers] s

After the information is populated into three different data extensions (at a much quicker rate), Alex brings all of the data into the original data extension using this query.

Join Query

Select
j.JobID,
j.EmailName,
j.DeliveredTime as SendTime,
o.EventDate as OpenTime,
s.EmailAddress,
s.SubscriberKey
from [JobTable] j
join [OpenTable] o
on j.JobID = o.JobID
join [SubscriberTable] s
on o.SubscriberID = s.SubscriberID

Everything runs more smoothly, and the client gets the info they need. Brilliant!

Automation Studio is a powerful tool and now that you know how to use it efficiently, you can accomplish great things while your processes run automatically.

Resources

• Salesforce Help: Find Activity Configuration and Error Details
• Salesforce Help: Optimize the Query Activity
• Salesforce Help: Use Intermediate Tables to Optimize a Query
• Salesforce Help: Automation Studio Errors