Most of the time, the best method is to just export the data extension and then import it again into All Subscribers. For one time bulk imports, this is by far the most efficient method. Alas, as with any solution in Salesforce Marketing Cloud, it comes with a ton of ‘but’ statements.

For instance, what if you need to do this very frequently with a low volume DE? or Multiple low volume DEs? I mean, by the time you created all the activities and automations to handle this, you probably could have just manually added them inside the UI.

Not only that, but the processing required to export/transfer from safehouse/import the file would become inefficient very quickly – potentially slowing down your process.

By using WSProxy, you can retrieve the information from the DE, shape it into the correct format and then upsert it into All Subscribers. All in one single script activity!

The first step of this script is to set the required variables.

<script runat="server">

var mid = '8675309'; //MID of target BU OPTIONAL 
//Default is current BU of script/page
var deCustKey = 'de1'; //your DE's CustomerKey / External Key
var prox = new Script.Util.WSProxy(); //creates proxy

</script>

To help speed up the script, I have not called the ‘Core’ library, but if it is easier (e.g. using Write instead of Platform.Response.Write) for you to code with the library in – the difference is negligible.

You also have the option, if you are looking to do multiple DEs, to gather this info from another data extension via Lookups or similar. You would just want to pay attention to processing time as there is a 30 minute time-out for script activities. (check out here for options around that, if you think that may be an issue for you)

After declaring our variables, we have 2 different user declared functions that we will use. First is retrieveDERows which will, as the name states, retrieve the DE rows we need.

function retrieveDERows(mid,deCustKey) {

	if(mid) {
		prox.setClientId({ "ID": mid }); //Impersonates the BU
	}
	var cols = ["EmailAddress","SubscriberKey","FirstName", "LastName"]; //Columns you want retrieved

	var desc = prox.retrieve("DataExtensionObject[" + deCustKey + "]", cols); //executes the proxy call

	return desc;
}

Inside of this, you will notice I do not declare a new instance of WSProxy. This is because it is declared at the top and creating a new instance each run would cause extra processing and delays.

I manually declared the fields to be retrieved in my example, but if you have dynamic fields inside your target DEs, you can utilize a WSProxy retrieve on the DataExtensionField object to collect the fields you want retrieved. You can find an example of that process here.

The second user declared function is upsertIntoSubscribers. This is also fairly easy to discern its purpose from the name. It is going to take the inputted values and upsert it into your All Subscribers.

function upsertIntoSubscribers(mid,subProperties) {

	if(mid) {
		prox.setClientId({ "ID": mid }); //Impersonates the BU
	}
	var updateObject = Platform.Function.ParseJSON(subProperties);
	var options = {SaveOptions: [{'PropertyName': '*', SaveAction: 'UpdateAdd'}]};

	var res = prox.updateItem('Subscriber', updateObject, options);

	return res;
}

Sweet! Now we have our functions defined. Next is creating the process to iterate through each row and utilize the functions accordingly.

I personally recommend encasing it inside of a try/catch to help with error handling and debugging. So, this means so far we should have:

<script runat="server">

var mid = '8675309'; //MID of target BU
var deCustKey = 'de1'; //your DE's CustomerKey / External Key
var prox = new Script.Util.WSProxy(); //creates proxy

try {

} catch(e) {

	Platform.Response.Write('<br /><b>Error:</b> ' + Platform.Function.Stringify(e));

}

function retrieveDERows(mid,deCustKey) {

	if(mid) {
		prox.setClientId({ "ID": mid }); //Impersonates the BU
	}
	var cols = ["EmailAddress","SubscriberKey","FirstName", "LastName"]; //Columns you want retrieved

	var desc = prox.retrieve("DataExtensionObject[" + deCustKey + "]", cols); //executes the proxy call

	return desc;
}

function upsertIntoSubscribers(mid,subProperties) {

	if(mid) {
		prox.setClientId({ "ID": mid }); //Impersonates the BU
	}
	var updateObject = Platform.Function.ParseJSON(subProperties);
	var options = {SaveOptions: [{'PropertyName': '*', SaveAction: 'UpdateAdd'}]};

	var res = prox.updateItem('Subscriber', updateObject, options);

	return res;
}

</script>

Now we just need to fill in that try section with our iteration script.

First we would need to call the retrieveDERows function to get the DE’s rows.

var deReturn = retrieveDERows(mid,deCustKey);

Great! Now we have a JSON with all of the rows…but what do we do with it? Using a For loop, we can iterate through each row to grab that row specific data.

for (var a = 0; a < deReturn.Results.length; a++) {

	var attributes = []

	var results = deReturn.Results[a];
}

Awesome, now we have the row…but we need the individual fields and their values. Luckily, nesting a second for loop inside of the one above will get us the info we need. If you notice above, I created an array named attributes. This will come into use in the second for loop.

for (var i = 0; i < results.Properties.length; i++) {

	var name =  results.Properties[i].Name;
	var value = results.Properties[i].Value;

	if (name == "FirstName" || name == "LastName") {
		name = name.replace(/tName/g,"t Name")
	}

	if (name == "SubscriberKey") {
		subkey = 'SubscriberKey: "' + value + '"';
	}
	else if (name == "EmailAddress") {
		email = 'EmailAddress: "' + value + '"';
	}
	else {
		var object = '{Name:"' + name + '",Value:"' + value + '"}'
		attributes.push(object);
	}

}

Inside the above, you will notice I have a few different if/else conditions. The first condition is to add a space into FirstName or LastName (common field names inside of a DE) because inside of All Subscribers, this field is ‘First Name’ or ‘Last Name’ and without the space, the fields will not match.

The second conditional statement is correctly formatting and assigning the values of the SubscriberKey and Email Address. If the field is neither of those, then it will correctly format the non-primary value into the attributes array.

Depending on your data extension field naming conventions, you may want to add a couple of your own conditions to properly format your data into the required object for upsert.

The attributes array we declared earlier, will now contains all the assigned fields that can be used for personalization or filtering – for example “Region” or “Agent” fields. This is due to the attributes.push(object) action inside the for loop.

We then take our created pieces and put them together to make the necessary object for the All Subscribers upsert. We then push that into our second function upsertIntoSubscribers.

var subProperties = "{" + subkey + "," + email + "," + "Attributes:[" + attributes + "]}";

var subUpdate = upsertIntoSubscribers(mid,subProperties);

Great! Now we have inserted our first row! From here, the for loop will continue to do so for the remaining rows until it completes.

Is this script a be-all end-all solution to every possible way of moving rows from a DE to All Subscribers? Not even close. But, it can be a super useful script to have at times and is much easier to transfer between BUs than an automation with multiple steps to export and import a single DE.

Below is the fully fleshed out script:

<script runat="server">

var mid = '8675309'; //MID of target BU -- OPTIONAL
//Default is current BU of script/page
var deCustKey = 'de1'; //your DE's CustomerKey or External Key
var prox = new Script.Util.WSProxy(); //creates proxy

try{

	var deReturn = retrieveDERows(mid,deCustKey);

	for (var a = 0; a < deReturn.Results.length; a++) {

		var attributes = []

		var results = deReturn.Results[a];

		for (var i = 0; i < results.Properties.length; i++) {

			var name =  results.Properties[i].Name;
			var value = results.Properties[i].Value;

			if (name == "FirstName" || name == "LastName") {
				name = name.replace(/tName/g,"t Name")
			}

			if (name == "SubscriberKey") {
				subkey = 'SubscriberKey: "' + value + '"';
			}
			else if (name == "EmailAddress") {
				email = 'EmailAddress: "' + value + '"';
			}
			else {
				var object = '{Name:"' + name + '",Value:"' + value + '"}'
				attributes.push(object);
			}

		}
		
		var subProperties = "{" + subkey + "," + email + "," + "Attributes:[" + attributes + "]}";

		var subUpdate = upsertIntoSubscribers(mid,subProperties);
	}

} catch (e) {

	Platform.Response.Write('<br /><b>Error:</b> ' + Platform.Function.Stringify(e));

}

function retrieveDERows(mid,deCustKey) {

	if(mid) {
		prox.setClientId({ "ID": mid }); //Impersonates the BU
	}
	var cols = ["EmailAddress","SubscriberKey","FirstName", "LastName"]; //Columns you want retrieved

	var desc = prox.retrieve("DataExtensionObject[" + deCustKey + "]", cols); //executes the proxy call

	return desc;
}

function upsertIntoSubscribers(mid,subProperties) {

	if(mid) {
		prox.setClientId({ "ID": mid }); //Impersonates the BU
	}
	var updateObject = Platform.Function.ParseJSON(subProperties);
	var options = {SaveOptions: [{'PropertyName': '*', SaveAction: 'UpdateAdd'}]};

	var res = prox.updateItem('Subscriber', updateObject, options);

	return res;
}

</script>
Tags: , , , , , , , , , , , , ,
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments