2

Why isn't this working? I took this query

SELECT s.TriggeredSendCustomerKey as TriggeredSend,
COUNT(o.TriggeredSendCustomerKey) as TotalOpens,
COUNT(case when o.IsUnique = 1 then 1 end) as UniqueOpens
FROM _Sent s with (NOLOCK)
JOIN _Open o with (NOLOCK)
ON s.SubscriberKey = o.SubscriberKey
WHERE s.EventDate BETWEEN '2017-08-01 00:00:00' AND '2017-08-31 23:59:59'
AND s.TriggeredSendCustomerKey IN ('40712')
AND o.TriggeredSendCustomerKey IN ('40712')
Group by s.TriggeredSendCustomerKey

Which works for one triggered send perfectly. Whenever I add more TriggeredSendCustomerKeys in the parenthesis, the numbers are off. I also don't like hard coding the number so I tried this, which validates and seems like it should work, but the system doesn't like it.

SELECT s.TriggeredSendCustomerKey as TriggeredSend,
COUNT(o.TriggeredSendCustomerKey) as TotalOpens,
COUNT(case when o.IsUnique = 1 then 1 end) as UniqueOpens
FROM _Sent s with (NOLOCK)
JOIN _Open o with (NOLOCK)
ON s.SubscriberKey = o.SubscriberKey
WHERE s.EventDate BETWEEN '2017-08-01 00:00:00' AND '2017-08-31 23:59:59'
AND s.TriggeredSendCustomerKey = o.TriggeredSendCustomerKey
Group by s.TriggeredSendCustomerKey
  • Here's what I ended up doing in the end. I broke it up into two queries as data kid suggested. The query I posted where s.TriggeredSendCustomerKey = o.TriggeredSendCustomerKey finally worked when I first created a subset of the _Sent table and instead queried on that. Adam's answer was great too, but I gave the "right" answer to DataKid because he was first to act. Thanks all! – garek007 Sep 21 '17 at 13:43
4

The s.TriggeredSendCustomerKey = o.TriggeredSendCustomerKey is way more computationally expensive than

AND s.TriggeredSendCustomerKey IN ('40712')
AND o.TriggeredSendCustomerKey IN ('40712')

Although the hard coding reducing flexibility, it limits the range of values that the system has to search.

Instead of just searching for 40712, it has to return every case where the two value are the same. That combined with running joins on a system table, and you have a query that is taking longer than 30 minutes and timing out.

To improve performance I would break it into 2 part.

  1. The query without the Aggregate Functions(TableA)

    SELECT s.TriggeredSendCustomerKey as TriggeredSend
    ,o.TriggeredSendCustomerKey as Opens
    ,(case when o.IsUnique = 1 then 1 end) as IsUnique
    FROM _Sent s with (NOLOCK)
    JOIN _Open o with (NOLOCK)
    ON s.SubscriberKey = o.SubscriberKey
    WHERE s.EventDate BETWEEN '2017-08-01 00:00:00' AND '2017-08-31 23:59:59'
    AND s.TriggeredSendCustomerKey = o.TriggeredSendCustomerKey
    
  2. Query with Aggregates (TableB):

    SELECT TriggeredSend,
    COUNT(Opens) as TotalOpens,
    COUNT(IsUnique) as UniqueOpens
    FROM [TableA]
    GROUP BY TriggeredSend
    
  • Well that explains why it's timing out, but can you help me make it better so that I can do a lookup? – garek007 Sep 8 '17 at 15:34
  • Staging tables? will update my answer – Samuel Dare Sep 8 '17 at 15:36
  • 40712 is that an exact match or is the entire number longer? – Samuel Dare Sep 8 '17 at 15:38
  • it is exact match – garek007 Sep 8 '17 at 16:34
  • 1
    Another reason i didn't like the hard coding was because it was skewing the results. I think there must have been duplicate subscriber keys. Thanks for your help! – garek007 Sep 8 '17 at 17:44
2

I agree with @Data_Kid about breaking it up into two queries. Generally speaking, I try not to join multiple System Data Views in a single query -- simply because you're asking for a timeout if you have a high send volume. Here's how I'd approach it.

The first query selects only _Sent rows and does an overwrite on the target:

select distinct
s.TriggeredSendCustomerKey as TriggeredSend
FROM _Sent s
where 
s.EventDate >= '2017-08-01 00:00:00' 
and s.EventDate <= '2017-08-31 23:59:59'
and s.TriggeredSendCustomerKey IN ('40712','40713','40714')
/* name: TriggeredSendSummary_Sends */
/* target: TriggeredSendSummary */
/* action: overwrite */

The second query uses the results of the first query and inner joins with _Open and does an update:

select 
s.TriggeredSendCustomerKey as TriggeredSend
, COUNT(o.TriggeredSendCustomerKey) as TotalOpens
, COUNT(case when o.IsUnique = 1 then 1 else 0 end) as UniqueOpens
from TriggeredSendSummary s
inner JOIN _Open o ON o.SubscriberKey = s.SubscriberKey
group by s.TriggeredSendCustomerKey
/* name: TriggeredSendSummary_Opens */
/* target: TriggeredSendSummary */
/* action: update */

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.