1

By help from this community I've been able to do a query, that return all that did not open an email campaign into a sendable data extension. So far, so good.

select distinct s.subscriberKey, sub.EmailAddress
from _Sent s
INNER JOIN _Subscribers sub
ON s.SubscriberKey = sub.SubscriberKey
where s.JobID = 4792224
and s.SubscriberKey IS NOT NULL
and s.SubscriberKey NOT IN (
  SELECT o.SubscriberKey 
  FROM _Open o  
  WHERE o.JobID = s.JobID
  AND o.SubscriberKey = s.SubscriberKey
)

The query run smoothly, when I run it on JobID 4792224 which is a test send to 9 subscribers only. But the amount of subscribers in our marketing sends vary from 10K to 95K and trying querying one of the bigger sends (JobID: 4793094) the query always times-out (after 30 minutes). I know the query times out, as I've tested it in an automation.

I've tried replacing IS NOT NULL with != null and the automation did complete (on JobID: 4792224), but did not return any subscribers in my data extension. I've also tried removing this line and s.SubscriberKey IS NOT NULL but then again the result was a time out after 30 minutes of the automation holding my query.

So, I'm really not sure what to do. I have a query that runs fine on JobIDs with a small amount of subscribers, even though it uses the IS NOT NULL expression, that is adviced not to be used. But using it on bigger sends, or replacing or even removing the IS NOT NULL expression leaves me with empty data extensions or queries that time out.

Any idea on what I should try now? Thank you

  • For a start take a look at the query optimisation cheat sheet. Reference: resources.docs.salesforce.com/rel1/doc/en-us/static/pdf/… and the condition IS NOT NULL should be avoided at all costs – StormBr34ker May 5 '17 at 8:05
  • The SOQL queries are not directly passed to the DB instead there is an Optimizer which looks for 'Bad Query'. If it encounters filters such as these Not null. it would simply abort the query operation and throw the timeout error as it knows before hand that your query is gonna time out. This also one reason why your query would return timeout in just 5 to 6 seconds rather than waiting for 15 seconds. – StormBr34ker May 5 '17 at 8:09
  • @Sumuga - OK, thank you, I'll try finding an expression that I can replace IS NOT NULL with. Besides that, I had no idea that expression would be considered 'Bad Query' as the query works perfectly on smaller sends. I truly believed it was a question og splitting the query into smaller ones.. – FelixSanchez May 5 '17 at 8:55
  • @Sumuga - please note this is for Marketing Cloud, not Salesforce. Marketing Cloud is more closely related to T-SQL, where SalesForce uses SOQL. There are some differences in optimization - see this for SFMC query best practices: help.marketingcloud.com/en/documentation/exacttarget/… – Gortonington May 5 '17 at 13:07
1

Based on your experienced issues with pulling data from system tables, and alternate route is to pull data into a data extension first, and then process it from there. I'm going to assume that we do this on a per send (per campaign) basis, but you could build up something more scale-able if needed.

Step one: Send DE
Create a data extension with columns SubscriberKey (Length 100, Primary Key, not nullable) and EmailAddress (Nullable)

Create a query:

select distinct s.subscriberKey, sub.EmailAddress
from _Sent s
LEFT JOIN _Subscribers sub
ON s.SubscriberKey = sub.SubscriberKey
where s.JobID = 4792224

This completes your first step and generates a list of all those who received the email.

Step two: Open DE
Create a data extension with just a subscriberkey column for those who opened.

Populate using this query:

SELECT distinct SubscriberKey
FROM _Open 
WHERE JobID = 4792224 AND IsUnique = 1

Step 3: Create your audience DE:
Finally, create your audience DE with SubscriberKey and EmailAddress columns, SubKey = Primary, Email = Non-Nullable.

Use this query:

SELECT SubscriberKey, EmailAddress
FROM SendDE
WHERE SubscriberKey NOT IN (SELECT SubscriberKey FROM OpenDE)

A better performing query would be this one below, but the above I'm sure should work. But you could try this one as well:

SELECT s.SubscriberKey, s.EmailAddress
FROM SendDE s
WHERE NOT EXISTS (SELECT 1 FROM OpenDE o WHERE s.SubscriberKey = o.SubscriberKey)

Step 4: Create an automation
Create an automation with 3 steps where you pull in the various queries in the above order. Add your email as recipient when it completes.

This should get you the audience you need.

  • Thank you so much for helping complete this task. It's of great value! I've created a setup like the one suggested above (step 3, first version). I've succesfully tested this in an automation on a small send (9 recievers) og have succesfully tested the queries manually on a larger send (80K recievers). In a week I'll test it for real for the first time on 5 marketing sends (330K recievers). I'm excited and I'll make sure to comment here if I find any tweaks, that might improve this setup. I'll test an alternative setup with step3 version 2 also, and let you know if everything works out. – FelixSanchez May 9 '17 at 5:35
  • Glad that it worked Felix :) Would still suggest you create a case based on the query below, because it should it. The beauty with the one below is that it just takes one query and one DE instead of 3 queries and 3 DE's, meaning the admin time of the stuff is significantly less :) – Kenneth Wagner May 9 '17 at 6:35
0

This query should be able to perform the request:

select distinct s.subscriberKey, sub.EmailAddress
from _Sent s
INNER JOIN _Subscribers sub
ON s.SubscriberKey = sub.SubscriberKey
where s.JobID = 4792224
and NOT EXISTS (
  SELECT 1
  FROM _Open o  
  WHERE o.JobID = s.JobID
  AND o.SubscriberKey = s.SubscriberKey
)

You shouldn't have to use subscriberkey is not null, given that you are querying a system table where subscriber key is always written. You can see from the documentation that it cannot be null: https://help.marketingcloud.com/en/documentation/exacttarget/interactions/activities/query_activity/data_view_sent/

  • thank you for taking your time to help. I've tried replacing my code with yours in my query (twice - with JobID 4792224 and 4793094) and now both of them time out after 30 minutes. Not even the small job with only 9 newsletter recievers (4792224) is completed. If your code should be able to perform the request, can I be doing something else regarding this query completely wrong? – FelixSanchez May 8 '17 at 7:17
  • Hey Felix... hmm... this is odd behaviour indeed. Could you try replacing your query with this: select distinct s.subscriberKey, sub.EmailAddress from _Sent s INNER JOIN _Subscribers sub ON s.SubscriberKey = sub.SubscriberKey where s.JobID = 4792224 and s.SubscriberKey NOT IN ( SELECT o.SubscriberKey FROM _Open o WHERE o.JobID = s.JobID AND o.SubscriberKey = s.SubscriberKey ) – Kenneth Wagner May 8 '17 at 7:44
  • Thank you. Tried it, this version returns 3 subscribers as expected on the small send (4792224) but times out after 30 minutes on the large send (4793094) .. I really hope there is some way to be able to complete this using queries in automations, it'd not only save resources but also help us improve our marketing sends – FelixSanchez May 8 '17 at 8:32
  • Hey Felix - it can definitely be solved on the platform with automation studio, it's just a matter of how we structure things. Could you try the following to see if this works? (note you might need to allow for null values in your DE here for email) select distinct s.subscriberKey from _Sent s where s.JobID = 4792224 and NOT EXISTS ( SELECT 1 FROM _Open o WHERE o.JobID = s.JobID AND o.SubscriberKey = s.SubscriberKey ) – Kenneth Wagner May 8 '17 at 9:28
  • Hey @KennethWagner. Your optimist really makes me glad :) Anyway, tried this new code. Same result as before. It succeeds in returning 3 subscribers as expected on the small send (4792224) but times out after 30 minutes on the large send (4793094) – FelixSanchez May 8 '17 at 10:46

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.