0

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)

enter image description here

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

Another P.S.

Here's the DE i made with all instructions: enter image description here

then wrote the query as well: enter image description here

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:

enter image description here

Here's the query that worked for me. enter image description here

From this you can help me compare why the other one doesn't work for me. Special Thanks to @zuzannamj for her kind patience

  • 2
    Two things - in you query you have SentDt etc, while in the DE you have SentDate - all columns have to match. And why do you say you’re not able to create a DE, when we see a screenshot of an actual DE? – zuzannamj Oct 27 '19 at 12:28
  • Hello @zuzannamj the DE displayed is the DE I made for a different query not related tl this. I just showed it for the sake of comparison why I was able to to make that DE and why I cant for the query im doing now. – JM Palomaria Oct 27 '19 at 13:53
1

Queries don't "create" Data Extensions themselves unless you're using Query Studio, which will create a temporary DE in the Query Studio folder, but in general, you need to create a DE yourself first, before you run the query.

Here's how to create a Data Extensions:

  1. Go to Email Studio > Email
  2. Go to Subscribers > Data Extensions
  3. In the desired folder, click on "Create" in the right-hand upper corner
  4. Choose "Standard Data Extension"
  5. Give it a name and click on Next
  6. Leave "Retention Setting" off and click Next
  7. Create all the fields: JobID NUMBER, EmailName Text(250), SubscriberKey Text(250), EmailAddress as Text(250), SentDt DATE, OpenDt DATE Nullable, ClickDt Date Nullable
  8. Click on Create.

As an alternative, you can use an existing Data Extension as a template:

  1. Go to Email Studio > Email
  2. Go to Subscribers > Data Extensions
  3. In the desired folder, click on "Create" in the right-hand upper corner
  4. Choose "Standard Data Extension"
  5. Choose Creation Method > Create from Existing
  6. Give it a name
  7. Do not change any other settings, just click on Next, Next and Create

Now you're ready to run the query.

Documentation: Create a Data Extension

EDIT

This query works 100%, just make sure that column names match the column names in your Data Extension, that you have appropriate data types and string lengths in every column, and that there are no blank spaces after each line of the query when you copy and paste it:

SELECT 
s.SubscriberKey,
j.EmailName,
sub.EmailAddress,
J.JobID,
s.EventDate as SentDt,
o.EventDate as OpenDt,
c.EventDate as ClickDt
FROM _Sent s LEFT JOIN _Job as j ON s.JobID = j.JobID
LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
LEFT JOIN _Click c ON s.JobID = c.JobID  and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
INNER JOIN _Subscribers sub ON s.SubscriberID = sub.SubscriberID
WHERE j.EmailName = 'We miss you - 3' AND
s.EventDate BETWEEN '10-1-2019' AND '10-20-2019'

Check out this article to learn more about SQL and Data Views in Marketing Cloud

|improve this answer|||||
  • Thanks for the reply @zuzannamj appreciated it a lot. What I meant was that i was able to create a data extension with no errors encountered except for this. So with your help i backtracked my steps in creating DE and found out that the error that caused why I can't proceed in making the DE is that i checked "sendable" which I should not for this DE. Thanks again :) Now after i created the DE and wrote the query, i have a syntax error near here "_Subscribers t3 t2.SubscriberID = t3.SubscriberID". should there be something in between t3 and t2? – JM Palomaria Oct 27 '19 at 15:49
  • By the way the DE I showed above is different. Just wanted to show if I mess something on this compared to the DE showed above that was working. – JM Palomaria Oct 27 '19 at 15:51
  • @JMPalomaria You are missing the 'ON' statement after the second inner join (t3) which is causing your error. You have the 2 values, but literally are missing 'ON' before it. – Gortonington Oct 28 '19 at 12:55
  • @Gortonington thanks for the reply. I added ON but i got no results pulling up. – JM Palomaria Oct 28 '19 at 12:58
  • 1
    @Gortonington I'll try it now. thanks for your patience – JM Palomaria Oct 28 '19 at 13:12

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.