We have multiple newsletters that generate leads for marketing emails. I'm trying to automate the process of consolidating those leads into one master data extension after they have been on a newsletter list for 30 days.
Currently, the sql I'm using looks like this:
SELECT L.CreatedDate ,L.EmailAddress ,L.ListID ,L.Status FROM ent._ListSubscribers L where l.ListID in (20798913, 20757098, 20828774, 20233021, 20551644, 18973750, 20549665, 20540177, 20712964, 20233025, 19009581, 19009580, 20427611, 19649300, 20233035, 20585536, 20468748, 19009576, 19009574, 19009571, 19009570) /*All_Pub_Lists*/ and Status = 'Active' and CreatedDate between (dateAdd(dd, -31, dateAdd(dd, 0, getDate()))) and (dateAdd(dd, -30, dateAdd(dd, 0, getDate()))) and l.EmailAddress not in (select EmailAddress from ent.Master_Prospect_Copy)
The query activity is set to append new data in the target data extension. I was able to run the query a single time and it produced the expected results. However, now whenever the query activity runs it fails due to a Duplicate Primary Key error.
The error makes sense, as people are free to sign up to newsletters whenever they'd like, so they end up on multiple lists with multiple created dates. I tried to removed the duplicates with the
and l.EmailAddress not in (select EmailAddress from ent.Master_Prospect_Copy)
but it hasn't worked.
Does anyone know how I might continue to append new data, but avoid triggering the Duplicate Primary Key error?