0

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?

Improve this question
  • I think the second query may not work because my triggered send customer key. I think I need to only count it if it's not null because the target data extension requires it as a primary key. will report back once I'm sure – garek007 Jan 2 '18 at 22:21
  • 1
    I would advise not querying the _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
  • What other fields are in [01_Individual_Emails_Sent_Last_Month_SDV]? Is SubscriberID in there by chance? – Adam Spriggs Jan 3 '18 at 0:25
  • @AdamSpriggs no that field is not there, but it can be added. I know you said that SubscriberID is a little faster. I can try it that way. – garek007 Jan 3 '18 at 13:47
  • @Data_Kid I will try that. I'll have to rerun all of my queries so it could take some time to post back here – garek007 Jan 3 '18 at 13:47
2

You are best limiting the query on _Open within a date range as it's otherwise a very heavy table to go querying. You will be limited to 180 days of the query anyway.

Also consider counting only unique opens, rather than every one - this will reduce your load.

Your first query (Join) will likely be more performant that your 2nd query -

The SQL query process time is limited to 30 mins - however you can raise a support request to have this increased (by exception). It wouldn't be advisable tough to increase beyond 120 mins.

You could also try something like below:

Select DISTINCT
SubscriberKey,
TotalOpens
FROM (

SortOrder = Row number() over (PARTITION by s.SubscriberKey ORDER BY TotalOpens DESC)
,s.SubscriberKey 
,COUNT(s.TriggeredSendCustomerKey) as TotalOpens

FROM [01_Individual_Emails_Sent_Last_Month_SDV] s with (NOLOCK)
LEFT JOIN _Open o with (NOLOCK) ON s.SubscriberKey = o.SubscriberKey

Where DateDiff(d,o.EventDate,GetUTCDate()) between 0 and 30 
And o.SubscriberKey is not null

) z
where SortOrder = 1
Improve this answer
  • I can't do this because if the query is run after the first of the month, it results in fewer opens than the system tracking shows. Opens that come in the month of January would not be counted if I limit the _open rows to a date range. Not unless I take all opens from the beginning of the month to NOW. I may try that if it comes to it – garek007 Jan 3 '18 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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