0

Currently, we are sending our initial transactional email through API. However, the next requirement is to send follow up emails to the subscribers who didnt open the initial email within 24 hours . As we are planning to do this through Journey builder, the entry source for the journey should be a data extension that is fired through Automation Studio. Could someone help me out with the Automation studio portion as in what shall be written in SQL activities to achieve this ?

2

Since your initial email is triggered and NOT sent from Journey Builder - you could do the following:

Run a daily Automation Studio activity to find customers who didn't click the initial email from the previous 24-48 hour period, and add them to a Journey Builder activity that sends them a follow up.

Query to add customers to Journey Builder based on "not clicked initial email":

SELECT
initial.SubscriberKey
,initial.EmailAddress
,initial.OtherFields
FROM [Initial_API_Email_DE] as initial
INNER JOIN _Sent as sent ON sent.SubscriberKey = initial.SubscriberKey
LEFT JOIN (
   SELECT
   SubscriberKey
   ,JobID
   ,EventDate
   FROM _Open 
   WHERE JobID = "##Your Initial Email Job ID##"
   AND EventDate >= dateadd(dd,-2,getdate())
) as open ON open.SubscriberKey = initial.SubscriberKey
WHERE sent.JobID = "##Your Initial Email Job ID##"
AND sent.EventDate BETWEEN dateadd(dd,-2,getdate()) AND dateadd(dd,-1,getdate())
AND open.SubscriberKey IS NULL

SQL Explanation:
It is checking your "Initial Email Data Extension" against the _Sent data view to see who was sent the Initial Email 24-48 hours ago. It then checks _Open to see if any of those people have also Opened the email, and only returns the subscribers who didn't open.

Now you have some SQL that selects the Subscribers that didn't open the email and puts them in a Data Extension, you should create a Journey Builder activity (using that DE as an entry source). There are plenty of articles to step you through that process.

References:
_Sent Data view
_Open Data view

| improve this answer | |
  • Hi Cameron, Thanks for the response. Could you please tell me where can i get my Job ID if I am sending the email through API ? – Sourab Mund Jan 14 at 10:52
  • The JobID can be found in your send reporting tab in Email Studio. For triggered (API) sends, make sure you find the correct/current version in the triggered send reporting folder. – Cameron Robert Jan 14 at 12:00
  • Apologies but I couldn't find anything called Send reporting tab in Email Studio – Sourab Mund Jan 14 at 12:33
  • Apologies; you want the Sends option under the Tracking Tab in Email Studio. – Cameron Robert Jan 14 at 12:36
  • That is the problem actually. The Send definitions created through API aren't listed here. – Sourab Mund Jan 14 at 12:42
0

If the numbers of email send-outs is not too high, there is another easier way to do this. 1. Send your transactional email via Journey Builder by inject into journey using API event. 2. Add wait for 1-2 days as per need. 3. Add engagement split activity and send reminder email on basis of open/click etc. Setup in engagement split activity.

| improve this answer | |
  • We need to suggest the client the approach of using Journey Builder for the initial email when nothing works. Thanks – Sourab Mund Jan 14 at 10:53

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.