Based on the subscribers website interaction, my guy handling our website can send me two types of data:
SubscriberKey RoomOfPreference.

I can have him add this data to a pick-up data extension via API.

I'd like to have an automation loop through the entries in the pick-up data extension, extend this data with what I know about the subscriber in my MasterSubscriber data extension in Marketing Cloud already and then populate a sendable data extension with all the necessary and useful info I need. The sendable data extension would then be used as an entry point for a journey in Journey Builder.

So my question is:
How can I loop through my pick-up data extension with an automation that adds info to and populates the sendable journey data extension and then empties the pick-up data extension?

My sendable data extension would require at least the EmailAddress as a data type besides the SubscriberKey and the RoomOfPreference.

The motivation for this question: On our website, users are given the option to choose their room of preference; The room they'd like to give a make-over, and these are the 5 options: Living room, dining room, bedroom, home office, outdoor. I'd now like to create a journey for these people helping them by exploring that room and inspiring them with make-over ideas

Any help achieving this would be much obliged.
Thank you 


If I have understood your question correctly:

I have the Room DE enter image description here

I have the Master DE

enter image description here

I use this query: select r.subscriberkey,r.roomprefernce,m.emailaddress,m.firstname from room r join masterde m on r.subscriberkey = m.subscriberkey

And my resultant DE has this:

enter image description here

You can use this query in the automation Studio

  • This really makes sense. But how would I empty the Room DE after having moved those subscribers to the Entrydefinal1 DE? If not emptying it, I'd add the same subscribers to the Entrydefinal1 DE over and over again, right? – Felix Sanchez May 13 '20 at 20:07
  • You need not empty it, if you have data action as 'Update' on the query activity'. The primary key could be subscriber key and room type. Each time, it will see if there is already a record with that subscriber key and room type, it won't add the record again. If there is no such record, it will add it. Makes Sense? – Swati Mishra May 14 '20 at 7:38
  • Yes, that would be a way. But, If the subscriber has always been added to the Journey, I'd not let them be a part of the Journey DE before having exited it. So, I'd delete individual records in the Journey DE with a retention policy that matches the length of the journey. When moving them from Room DE to Journey DE, I'd only move them if they're not a part of the Journey DE already. This is why I want to be able to empty the DE after having moved the subscribers. Hope it makes sense – Felix Sanchez May 14 '20 at 8:46
  • Can you just not set the journey re entry settings 'Re entry only after exiting'. This will ensure that they do not renter again :) – Swati Mishra May 14 '20 at 9:28

You just need a SQL to join your pickup data extension with Master data extension and populate the target data extension with whatever additional attributes/ fields you need. Then put that query in an Automation and schedule it to run every hour.

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.