i'm a newbie with SQL, and i wanted to pull out all subscribers that were sent by a particular email with open date send date and click date. @Bread2827 help me out with this query below but i can't seem to create a data extension from this and it says error. not really sure what's happening.
here's the query:
Select DISTINCT t1.JobID, t1.EmailName, t2.SubscriberKey, t3.EmailAddress, t2.EventDate as 'SentDt', t4.EventDate as 'OpenDt', t5.EventDate as 'ClickDt' FROM _Job t1 INNER JOIN _Sent t2 ON t1.JobID = t2.JobID INNER JOIN _Subscribers t3 t2.SubscriberID = t3.SubscriberID Left Join _Open t4 ON t2.SubscriberID = t4.SubscriberID AND t2.ListID = t4.ListID AND t2.BatchID = t4.BatchID AND t2.JobID = t4.JobID LEFT JOIN _Click t5 ON t2.SubscriberID = t5.SubscriberID AND t2.ListID = t5.ListID AND t2.BatchID = t5.BatchID AND t2.JobID = t5.JobID WHERE t1.EmailName = 'Your Exact Email Creative Name' AND t2.EventDate BETWEEN '10/01/2019' AND '10/02/2019'
data extension he advised me to write is JobID NUMBER, EmailName Text(250), SubscriberKey Text(250),EmailAddress as Text(250), SentDt DATE, OpenDt DATE Nullable, ClickDt Date Nullable.
I tried a making a data extension before with a different query and it worked (sample below)
but i can't create the data extension that was advise to me. Can anyone help me, i'm very desperate.
Thanks in advance.
PS emailname is 'We miss you - 3' Date range is 10-1-2019 to 10-20-2019
but I still can't pull any data at all.
I wrote a query before (with different intention) pulling all views for senddate, clickdate, opendate by JobID and so far this works for me. For reference the DE for this is shown below:
From this you can help me compare why the other one doesn't work for me. Special Thanks to @zuzannamj for her kind patience