This query is timing out
SELECT s.TriggeredSendCustomerKey,
COUNT(s.TriggeredSendCustomerKey ) as TotalOpens
FROM [01_Individual_Emails_Sent_Last_Month_SDV] s with (NOLOCK)
JOIN _Open o with (NOLOCK)
ON s.SubscriberKey = o.SubscriberKey
AND s.TriggeredSendCustomerKey = o.TriggeredSendCustomerKey
GROUP BY s.TriggeredSendCustomerKey
I tried writing it like this, but this produced the ever so helpful generic 'System Error'
SELECT o.TriggeredSendCustomerKey,
COUNT(o.TriggeredSendCustomerKey ) as TotalOpens
FROM _Open o with (NOLOCK)
WHERE o.SubscriberKey IN (
SELECT SubscriberKey FROM [01_Individual_Emails_Sent_Last_Month_SDV] s with (NOLOCK)
)
GROUP BY o.TriggeredSendCustomerKey
The 01_Individual_Emails_Sent_Last_Month_SDV is a Data Extension that contains about 3.3 million records from October. I pull this first to narrow down results. I don't want to add any date logic here, because it will screw up the TotalOpens number I get if I run later in the month (opens continue to get added to the _Open table and associated with a send even if the open occurs in the next month)
Any thoughts?
_Open
data view directly. Hive off the date range you need into data extension and join . it with[01_Individual_Emails_Sent_Last_Month_SDV]
. Also adding an primary key to[01_Individual_Emails_Sent_Last_Month_SDV]
might speed things up – Samuel Dare Jan 2 '18 at 22:38[01_Individual_Emails_Sent_Last_Month_SDV]
? IsSubscriberID
in there by chance? – Adam Spriggs Jan 3 '18 at 0:25