SQL Query for Inactive Users- Send Log DE Code

Question

This question is in relation to a previous question I had asked.

To summarize:

I am trying to run a SQL Query in automation studio to evaluate contacts in a data extension who have not opened an email in the last 6 months. I am planning on running two queries in Automation Studio.

I created a data extension with identical columns to my source DE which we're gonna call A.

The first query, which I intend to rewrite everything from A into the target DE called B:

SELECT *
FROM A

I use * for shorthand, in my real code I write out all my column names (I believe MC doesn't support *). For this query, I selected the overwrite option.

The second query, which I intend to filter out all the contacts that have opened emails in the past 6 months:

SELECT *
FROM [A] 
WHERE email IN 
(
  SELECT SubscriberKey 
  FROM _Open
  where CAST(EventDate as DATE) >= CAST(DATEADD(MONTH, -6, GETDATE()) as DATE)
)

I chose the overwrite option and B as my target DE. Email is used as the primary key, can't change it since that's what being used in the original database.

My Questions:

Is the logic for the 2nd query correct? I am pretty new to SQL and am unsure if the query is doing what I intend. Note: It does not give me any errors; I'm just unsure about the logic.

A few of these records are repeated in other data extensions that I send different emails to. Is there a way to only check if they have opened the emails that have been sent exclusively via this data extension (excluding any opens they have from other data extension sends)? We do not have any campaigns or publication lists settings in place.

Thanks in advance.

UPDATE:

I am including the AMPscript code that I used for creating the send log DE per @Gortonington 's answer. I tried creating it from the template send log (following the salesforce documentation), however I wanted to add more data and the UpdateDE command was giving me errors. Therefore, I created a non-sendable data extension with all nullable fields (no Primary Key).

%%[
IF _isTestSend == FALSE THEN
    VAR @dataSourceName, @jobID, @subID, @emailName, @listID, @batchID, @sentDate 
    SET @dataSourceName = AttributeValue("_DataSourceName")
    SET @jobID = AttributeValue("jobid")
    SET @subID = AttributeValue("subscriberid")
    SET @emailName = AttributeValue("emailname_")
    SET @listID = AttributeValue("listid")
    SET @batchID = AttributeValue("_JobSubscriberBatchID")
    SET @sentDate = NOW() /* US Central Time, need fix */

    IF _messagecontext == "SEND" THEN
        InsertDE(
            'SendLog_AMP'
            , 'JobID',@jobID
            , 'ListID', @listID
            , 'BatchID', @batchID
            , 'SubID', @subID
            , 'EmailName', @emailName
            , 'SentDate', @sentDate
            , 'DataSource', @dataSourceName
            )
    ENDIF
ENDIF 
]%%

Basically you're able to add anything that has a personalization string into a column. Add this to your email templates and you won't have to worry about it anymore.

Hope this helps someone.

Answer 1

To answer your first question, I would recommend first moving the Open Data View into a Data Extension, this will help increase efficient processing times and reduce timeout risk due to increased volume in your Data View.

This can be done easily with a query like so:

SELECT *
FROM _Open

You then reference this DE in your second query:

Select a.*
FROM [A] a
LEFT JOIN [Open_DV_DE] o
ON o.SubscriberKey = a.email
WHERE o.SubscriberKey IS NULL

I like to use left joins for suppressions as I have found them to be more efficient then NOT IN or NOT EXISTS - but its really dealers choice there.

This should give you if the subscriber has opened any emails in the last 6 months.

Now on to the second part - there are some things you can do via Send Log to gather the JobIDs of the sends using that Data Extension. Including adding the '_DataSourceName' attribute to show the list/DE name attached to each send, etc.

Assuming that you do not have these already in place, you can gather historical data through a bit of a duct tape and bubble gum.

You will want to create a Blank Discover report that includes Message ID (JobID) Email Name and Source Name (Data Extension Name). You then put in a filter where SentDate is within last 180 days to grab all the jobids associated with each email/de.

You then import this into a DE to reference in the above queries like so:

SELECT de.*
FROM (
    Select a.*
    FROM [A] a
    LEFT JOIN [Open_DV_DE] o
    ON o.SubscriberKey = a.email
    WHERE o.SubscriberKey IS NULL
) de
INNER JOIN [report_de] r
ON de.JobID = r.[Message ID]
WHERE r.[Source Name] = "myDEName"

You can then also edit it out by Email Name if you preferred as well. This should help provide an interim solution to get this info while you work on implementing a more elegant solution.

Answer 2

Data Views by default hold only last 6 months of data, so you can shorten your query to:

SELECT *
FROM [A] 
WHERE email NOT IN 
(
  SELECT SubscriberKey 
  FROM _Open
)

assuming that your email will be the subscriber key.

For the second part of the question, you would have to know the JobID of each of those sends to be able to filter them down.