I want to update subscribers profile attributes in Salesforce Marketing Cloud with respect to their business units

Question

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.

Answer 1

The easier way would be to have 2 SQL in the automation:

The first SQL looking into the inner join between the all subscriber and _sent based on the send from MID A saving s.OYBAccountID AS MID01. The second SQL looking into the inner join between the all subscriber and _sent based on the send from MID B saving s.OYBAccountID AS MID02.

In this way, if the record exists in both BUs you will have Mid01 and Mid02 populated.

Answer 2

While importing to all subscriber which attribute you are updating?

The query you are showing doesn’t reflect the profile attribute.

SQL : Select subscriberkey , MID_A, MID_B from ent._subscribers inner join ent._EnterpriseAttribute on Ent._Subscriberid=ent._enterpriseattribute.A-subscriberid

You should update MID_A or MID_B not accountid