This question is in relation to a previous question I had asked.
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.
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.
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.