0

I'm not even at a level of novice in SQL but I wanted to know what the query would be to put into a data extension all emails that opened an email in the past month. Also, a query that will let me select the JOBID to filter with.

TIA

2 Answers 2

2

You can also do an inner join on the _Job System Data View (on JobId) if you need the email name and send time (PickupTime) in your results.

SELECT
    o.JobID
    , o.ListID
    , o.BatchID
    , o.SubscriberID
    , o.SubscriberKey
    , o.EventDate
    , o.Domain
    , o.IsUnique
    , j.pickuptime
    , j.emailName
FROM _Open as o with (nolock)
INNER JOIN _Job as j with (nolock) on (j.jobid = o.jobid)
WHERE
    j.pickuptime >= dateadd(day,getDate(),-30)

Here are the details on the System Data Views:

Query Activity (System Data Views)

The documentation doesn't really spell out the relationships between the views, but it's generally JobID or SubscriberID.

3
  • Sorry if this is a dump question. But would I get email Addresses returned here?
    – mit88
    Feb 24, 2017 at 9:48
  • what does the nolock mean? Jul 20, 2017 at 14:37
  • Just means to take values from the row even if it the row is locked by another process. I think it's called a "dirty read" or something like that. I don't think it's necessary anymore, since I think SFMC wraps queries inside other queries to handle contention. Jul 20, 2017 at 14:50
1
   SELECT
        JobID,
        ListID,
        BatchID,
        SubscriberID,
        SubscriberKey,
        EventDate,
        Domain,
        IsUnique
    FROM
        _Open WITH (NOLOCK)
    WHERE
        JobID = 12345 OR
        DATEDIFF(day,EventDate,GETDATE()) <= 30

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.