3

I've recently completed an IP warming process. Contacts were being added to a new Data Extension before each send to keep track of the the separate imports throughout the migration.

Now I would like to combine all of the separate import DEs into one main Subscriber Data Extension, as well as one main Suppression Data extension for all the unsubscribes/bounced email addresses from the old system. I'm pretty new to SQL, so I'm wondering first off, is this a good approach?

Also, I'm wondering about the following scenarios:

  • All of the imports SHOULD have a unique list of email addresses, but what happens if they don't? I'm assuming that if an email address exists on multiple Data Extensions, it will likely be listed multiple times in the final Subscriber DE. How could I avoid this to only include one instance of each email address.

  • Should a query be written for each DE, to add is to the master subscriber DE one by one, or would it make more sense to pull everything at one time? (There are about 10 DEs). Are there resources available that explain how to approach this?

  • Can I populate a DE attribute value with SQL? For instance, if I wanted to tag one of the DEs, how could I go about creating a new attribute, and then populating that attribute for all records on the DE with "Sweepstakes-X" for instance.

Thanks in advance for any assistance with this!

2

Presumably each import is its own DE, eg. import1, import2, import3, and presumably they also have the same attribute names, eg. email, subscriberkey, etc., in which case you can either stack the selects together into a single SQL Activity utilizing UNION ALL (use Overwrite as the method), or if they are large, just do a series of SQL Activity adding each import DE into the final destination (use Add and Update as the method). Subscriberkey (or email depending on your use case) should be the primary key for the final DE.

UNION ALL method (SQL Activity)

select Email, SubscriberKey from import1
UNION ALL
select Email, SubscriberKey from import2
UNION ALL
select Email, SubscriberKey from import3
...

If the DE's are large and you want to break it up, then create 10 SQL Activities, one for each import DE, and utilize the "Add and Update' option.

For your first question, setting the primary key should keep that from being an issue (if it finds the key already exists in the final DE, it will just update the row). But if you want to be extra sure, you certainly can add a WHERE clause to prevent the row from being attempted to be inserted. eg.

select subscriberkey, email from import1
where subscriberkey not in (select subscriberkey from final_de)

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.