I've set up an automation that resends an altered version of a mail to all who recieved it the first time it was sent, but didn't open it.
This is done in Automation Studio using 3 SQL Query and a Send Email activities.
Here's the SQL for the three SQL Query activities:
SELECT s.subscriberKey, j.jobid, sub.EmailAddress FROM [_job] j JOIN [_Sent] s ON s.jobid = j.jobid AND s.EventDate > dateadd(d,-7,getdate()) AND j.EmailName like '%TOBERESENTDK%' LEFT JOIN _Subscribers sub ON s.SubscriberKey = sub.SubscriberKey SELECT s.subscriberKey FROM [AutomatedRetargetStep1of3DK] s JOIN [_Open] o ON s.jobid = o.jobid AND s.subscriberkey = o.subscriberKey SELECT s.subscriberKey, s.EmailAddress FROM [AutomatedRetargetStep1of3DK] s WHERE s.subscriberKey NOT IN (SELECT subscriberKey FROM [AutomatedRetargetStep2of3DK])
This automation is set up for 5 countries, and typically the largest sends are approx. sent to 80.000 subscribers, while the smallest sends approx. goes out to 8000 subscribers.
Usually, when I runs these automations, the 2 automations to Switzerland and Sweden (8.000 and 40.000 subscribers) are run successfully, while the 3 remaining automations to Denmark, Germany and The Netherlands (80.000, 75.000 and 56.000 subscribers) times out during the second SQL Query giving me this error: Error: Query failed: Timeout
So, I'm puzzled, I've seen people struggle with SQL queries handling millions or rows, and really hadn't seen this issue comin'.
So, help would really be appreciated. Anyone with more experience than me having an idea if it's really the larger amount of subscribers in the automations being the problem.. or if I'm doing anything else in a inappropriate way that makes these automations time out?
The three Data Extensions for the SQL Queries.