SFMC Querying Subscriber Email Performance Keeps Erroring Out "Automation Failed Due to System Error"

Question

I have tried countless ways to make this SQL query work, using different date formats, different filters in WHERE clause, ensuring the DE has the correct fields with data types, etc. but I cannot get this query to output results. Always outputs system error.

Essentially, I want to be able to see all subscribers who were sent an email, and see whether or not it was sent, opened, clicked, bounced, etc. Best case scenario would be for me to be able to filter the data based on a specific email, but I have tried EmailID AND EmailName from the Job Data View and it outputs an error as well.

For context, we use single send journeys or multi-step journeys since we want to send tracking data to SFDC, so we also add in journey_name as well for debug/QA.

SELECT
      s.SubscriberKey     AS subscriber_key
    , sub.EmailAddress    AS email_address
    , sub.Status          AS sub_status
    , j.JourneyName       AS journey_name
    , job.EmailName       AS email_name
    , job.EmailID         AS email_id
    , s.EventDate         AS email_sent_date
    , job.DeliveredTime   AS job_delivery_date
    , o.EventDate         AS email_open_date
    , c.EventDate         AS email_click_date
    , b.EventDate         AS email_bounce_date
    , b.BounceCategory    AS bounce_category
    , b.BounceSubcategory AS bounce_subcat
    , b.SMTPBounceReason  AS bounce_reason
    , b.BounceType        AS bounce_type
    , u.EventDate         AS unsub_date
FROM _Sent AS s
    LEFT JOIN _Job AS job
        ON job.JobID = s.JobID
    LEFT JOIN _Open AS o
        ON o.JobID = s.JobID
        AND o.ListID = s.ListID
        AND o.BatchID = s.BatchID
        AND o.SubscriberID = s.SubscriberID
        AND o.IsUnique = 1
    LEFT JOIN _Click AS c
        ON s.JobID = c.JobID
        AND c.ListID = s.ListID
        AND c.BatchID = s.BatchID
        AND c.SubscriberID = s.SubscriberID
        AND c.IsUnique = 1
    LEFT JOIN _Bounce AS b
        ON s.JobID = b.JobID
        AND b.ListID = s.ListID
        AND b.BatchID = s.BatchID
        AND b.SubscriberID = s.SubscriberID
        AND b.IsUnique = 1
    LEFT JOIN _Unsubscribe AS u
        ON s.JobID = u.JobID
        AND u.ListID = s.ListID
        AND u.BatchID = s.BatchID
        AND u.SubscriberID = s.SubscriberID
        AND u.IsUnique = 1
    LEFT JOIN _JourneyActivity AS ja
        ON ja.JourneyActivityObjectID = s.TriggererSendDefinitionObjectID
    LEFT JOIN _Journey AS j
        ON j.VersionID = ja.VersionID
    LEFT JOIN _Subscribers AS sub
        ON sub.SubscriberKey = s.SubscriberKey
WHERE s.EventDate >= DATEADD(DAY,-30, GETDATE())

Answer 1

In my experience, "System Errors" are sometimes caused by datatype mismatches or type conversion function issues. Those types of errors generally aren't surfaced explicitly when they cause a Query Activity to fail.

I've run into this issue with the datatypes in _JourneyActivity -- specifically with VersionId.

Try replacing your LEFT JOIN to _JourneyActivity with this:

left join (

  select
    convert(varchar(36), ja0.VersionID) VersionID /* UniqueIdentifier datatype doesnt play nicely with JO-INs */
  , convert(varchar(36), ja0.ActivityID) ActivityID
  , ja0.ActivityName
  , ja0.ActivityExternalKey
  , ja0.JourneyActivityObjectID
  , ja0.ActivityType
  from _JourneyActivity ja0

) ja on ja.JourneyActivityObjectID = s.TriggererSendDefinitionObjectID

I've done the same thing with _Journey:

left join (

  select
    convert(varchar(36), j0.VersionID) VersionID
  , convert(varchar(36), j0.JourneyID) JourneyID
  , j0.JourneyName
  , j0.VersionNumber
  , j0.CreatedDate
  , j0.LastPublishedDate
  , j0.ModifiedDate
  , j0.JourneyStatus
  from _Journey j0

) j on j.versionid = ja.versionid

Answer 2

Your query runs fine in my (test) system. I copy/pasted and got results within seconds.

So technically, your query is fine, but my system is practically empty. You are likely in a production system with more traffic, and the amount of JOINs, coupled with the number of records in your tables, coupled with the abysmal performance of data views when querying at scale seems to be the problem. Essentially, you are seeing a timeout.

There are two general approaches to improve this (which can be combined)

a) pull all the data from the data views into regular data extensions, say, daily. Base your queries on those data extensions instead. This will already improve performance. As a hint, I have found that setting Primary Keys in those DEs is a source of errors (even though that would improve performance even more), so I am setting them up without PKs.

Also, you'll have to sanitize your data or weird field content will break simple "select everything" queries due to field limits. See some examples here: SQL Automation Qry error

b) split your query. No need to perform all joins in one go. Since your entire query appears to be technically fine, you should be easily able to strip it down into parts.

(e.g.

• first join sent, subscribers and job
• join opens with first result
• join clicks with first result
• join bounces with first result
• join bounces with first result
• join the two journey DVs with first result )

Seems like a pain to do, but if you don't do this right from the start, the more data you get into your data views (with time you'll have more sends, more journeys,...), keeping too many joins in one operation will lead to your query failing "at some point later", seemingly out of nowhere -simply due to increased load.