I am joining multiple data view tables to get email metrics, but I keep getting a system error! I read it online that joining multiple data view tables is a pain and not recommended (this post was from 2018). Is there a fix for this?

select J.JobID as JobID,
J.FromName as FromName,
J.FromEmail as FromEmail,
J.EmailName as EmailName,
sum(S.SubscriberID) as Sent,
sum(O.SubscriberID) as Opened,
sum(C.SubscriberID) as Click,
sum(O.SubscriberID) * 1.0 / sum(S.SubscriberID) as total_open_rate,
sum(C.SubscriberID) * 1.0 / sum(S.SubscriberID) as total_click_rate

FROM _Job as J
JOIN _Open as O
ON J.JobID = O.JobID
JOIN _Sent as S
ON J.JobID = S.JobID
JOIN _Click as C
ON J.JobID = C.JobID

AND 1=1
GROUP BY J.JobID, FromName, FromEmail, EmailName
  • Could you post the specific error message? – Adrian Larson Nov 13 at 2:58
  • 'Automation failed due to system error.' THIS IS THE ERROR – user90131 Nov 13 at 3:47
  • 2
    Please edit your post with any clarifications, and indicate it is the verbatim message by formatting it as a quote block. – Adrian Larson Nov 13 at 3:57

I echo @RainerG's concerns. You should be using Count(), not Sum(). I also would not recommend joining multiple data views together in a single query. This may work if you have small sending audiences, but it will not scale once the data starts growing.

I'd recommend this series of Query Activities:

Step 1 - Get the Job details

, J.FromName 
, J.FromEmail
, J.EmailName
FROM _Job as J
where j.jobid = 1234
/* name: EmailMetrics */
/* target: EmailMetrics */
/* action: overwrite */

Step 2 - Append the send counts

, count(*) SendCount
from EmailMetrics m
INNER JOIN _Sent as a ON a.JobID = m.JobID
/* name: EmailMetrics_Sends */
/* target: EmailMetrics */
/* action: update */

Step 3 - Append the open counts

, count(*) OpenCount
from EmailMetrics m
INNER JOIN _Open as a ON a.JobID = m.JobID
where a.isunique = 1
/* name: EmailMetrics_Opens */
/* target: EmailMetrics */
/* action: update */

Step 4 - Calculate the rates

(This case-statement should address your "unknown error" issue. Those errors are typically math or data-type mismatch errors.)

, case
    when ISNULL(m.SendCount,0) = 0 AND ISNULL(m.OpenCount,0) > 0 then 100.0
    when (cast(m.OpenCount as Decimal (18,4))/cast(m.SendCount as Decimal (18,4))) > 1 then 100.0
    else (cast(m.OpenCount as Decimal (18,4))/cast(m.SendCount as Decimal (18,4))) * 100.0
  end as OpenRate  
from EmailMetrics m
/* name: EmailMetrics_Rates */
/* target: EmailMetrics */
/* action: update */
| improve this answer | |

You're using SUM where you should use COUNT. Using SUM is adding together all the SubscriberID values, and would be returning a huge number if you're using any kind of standard length for your SubscriberID. This could have unintended effects based on the Number type and length specified in your destination DE, and causing the generic system error you're seeing.

Depending on how many emails you're sending, this method may also run over your query time limit and generally put a lot of load onto SFMC. Think about first extracting the subset of data you need into your own DE's, and then doing the math.

Also not sure why you're doing * 1.0 and AND 1=1 in your code, I can't see it adding value to the process.

| improve this answer | |
  • Thanks, I used COUNT instead of SUM and I got results but it is giving me an astronomical figure. I even replaced SubscriberID with SubscriberKey (email), but still getting that huge number! – user90131 Nov 13 at 6:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.