Salesforce Data Cloud allows you to include related attributes during activation to Marketing Cloud. Related attributes are tied to data model objects (DMOs) that have a 1:N relationship with the object used for segmentation. An example of related attributes would be where the individual used for segmentation is the individual, and purchase history could be the related DMO as it would have many rows of purchase history per individual.

When these related attributes are activated from Data Cloud to Marketing Cloud, the related attributes are stored in a single field in the activated data extension as JSON. While the AmpScript function BuildRowSetFromJSON allows you to parse the JSON in an email, there isn’t an easy way to parse the JSON into relational data so that it can be used to drive decision splits in Marketing Cloud Journey Builder.

The following blog post will walk through how to use SSJS in Automation Studio to parse the JSON and insert it into Marketing Cloud data extensions as columns.

Steps to ingest and parse JSON data

The next section walks through how you can parse JSON that you receive in a data extension from an activation in Data Cloud using a script activity in Marketing Cloud’s automation studio. The JSON is parsed and populated into a data extension as columns that can then be used in Journey Builder.

Step 1: Create an activation from Data Cloud with related attributes to Marketing Cloud

Let’s assume that we have activated a segment from Data Cloud to Marketing Cloud with the following attributes:

This is what the activation will look like in Data Cloud:

Step 2: Data extension with related attributes is created in Marketing Cloud

The activation will then create a shared data extension in Marketing Cloud under Customer 360 segments with the related attributes as JSON when completed. The image below shows how the structure of the data extension will look. Note that the direct attributes show up as individual columns, but the related attributes are in one column called FinancialAccount.

When you look at the data in the data extension, this is what the data containing JSON looks like:

This is what one row of JSON data containing related attributes looks like:

[{"OpenDate":"2003-05-16","CloseDate":null,"Name":"eTrade","FinancialAccountType":"Brokerage","FinancialAccountStatus":"Open"},{"OpenDate":"2011-09-27","CloseDate":null,"Name":"Vanguard 401(b)","FinancialAccountType":"401(b)","FinancialAccountStatus":"Open"}]

Step 3: Parse data using SSJS in Automation Studio

Our goal is to parse the JSON and insert the parsed data into separate columns in a data extension, so that it can be used to drive decision splits in Journey Builder. We will be using Automation Studio and SSJS to do this.

First, create a data extension in Marketing Cloud with specific columns to store the parsed data. It is important to create it in another folder because the script mentioned in the next step will not work in the same folder.

Next, create a new scheduled automation with a script activity. This SSJS activity will parse and insert the data into the data extension. Use the following code to parse the JSON and insert it into the data extension you created in the previous step.

Note: The script above was created for demonstration purposes. Depending on the volume of records, it will be necessary to improve it to avoid any timeouts in the process. The ID field was also changed to avoid duplicate keys.

Now, run the automation. Once it’s completed, this is what the data extension output will look like after the JSON column has been parsed into individual columns:

Step 4: Use the new data extension in Journey Builder

The data extension can then be used as entry criteria in Journey Builder, and the parsed columns can be used to drive decision splits in Journey Builder as in the example below.

Conclusion

This blog post showcases a creative and effective method for converting JSON into relational data, overcoming the constraints of native Marketing Cloud functions. It features a practical solution utilizing SSJS in Automation Studio. By parsing the JSON and inserting it into data extensions as columns, marketers can fully leverage the depth of data available through Salesforce Data Cloud, thus driving more nuanced and effective customer journeys.

Resources

• Trailhead: Build a Data Strategy for Data Cloud
• Trailhead: Marketing Cloud Engagement Data Management
• Trailhead: Marketing Cloud Engagement Programmatic Languages
• Trailhead: Data Cloud segmentation and activation

About the authors

Gina Nichols is a Director on the Salesforce Data Cloud product team. She has a background as an architect working with some of Salesforce’s enterprise customers in the MarTech space. She is also a co-author of the award-winning, Transform the Consumer Experience Customer 360 Guide, which won an award of Merit at the STC Chicago competition. You can follow her on LinkedIn.

Marcelo Galvão is a Distinguished, Enterprise Architect based in São Paulo. He has a background as an architect working with some of Salesforce’s enterprise customers on complex projects. He is passionate about transforming customers’ needs using Salesforce technology. You can follow him on LinkedIn.