I have two business units A and B and both are sending out emails to the subscribers I have. I have created two profile attributes MID_A and MID_B which will store the MID of A and B respectively. I want to update the MID_A attribute for all those subscribers to whom email has been sent out from BU A and the same for BU B and if an email has been sent out from both the BUs then in that case I want to update both profile attributes with the MID.
I tried the same in a way by fetching all the subscribers from _Sent data view and using Inner join with All Subscribers.
Select s.SubscriberKey as [Subscriber Key], st.EmailAddress as [Email Address], s.OYBAccountID AS MID01, 'UpdateAllSubscribers' As Name, '******' AS BU_ID From _Sent s Inner join Ent._Subscribers st on st.SubscriberID = s.SubscriberID Where s.OYBAccountID='*****'
Then extract the data and transferring the data to the FTP via automation studio. After this, I imported the file from FTP and updating the All Subscribers list. This is not giving me the correct results as expected. One subscriber to whom I have sent out the email from both BU A and B, only parent value got updated in the [rofile attribute but for the one to whom I have sent out the email from only one BU, both attributes value got updated.