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 

2 Answers 2


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? Commented May 13, 2020 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? Commented May 14, 2020 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 Commented May 14, 2020 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 :) Commented May 14, 2020 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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .