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 ?
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
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.
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.