A unified view of the customer journey is a must for launching powerful targeted marketing campaigns. The customer profiles feature in Adobe Campaign gives marketers this critical data to create persuasive personal campaigns. Data residing in multiple applications and databases are merged in Adobe Campaign to create comprehensive profiles for each customer.

Two methods are used to import data in Adobe Campaign: API and batch file processing. In the former, a SOAP client is used to call in-built APIs within Adobe Campaign. The latter method is, however, more common and convenient, as this allows marketers to collect a considerable amount of data from different sources and process them before feeding them to the marketing automation system. This also saves them the trouble of setting up API calls to the system, especially if they do not have a technical background.

There is a tiny hitch though. Adobe Campaign only allows import of data in text, CSV, Tab, or XML formats. But that is not always the case with the data received from upstream data sources.

Format Matters

If the customer data file is in .CSV format, it only takes a step to initiate the import. Just add a “Data loading” activity in the workflow and the data will be extracted and imported to Adobe Campaign. Next, add an “Update data” activity to update the required table with the records that are read from the .CSV file.

JSON to CSV conversion
Fig 1: Data loading activity to read CSV file.

Data from some upstreams will be in JSON as JSON is better at showing hierarchical and relational data. As of today, there is no out-of-the-box activity in Adobe Campaign to parse JSON data. 

The JavaScript activity in Adobe Campaign can be used to parse JSON files and create corresponding CSV files. 

Fig 2: JSON file converted to CSV file before data loading activity

Parsing JSON Using JavaScript Activity

1. Inside the JavaScript activity, define and initialize the following variables:

  • inputFilePath to store the file path of the feed file.
  • addKeys initialized to “true”, to determine whether the CSV file needs to have a header. This will be discussed later on.
  • delimiter to define the desired delimiter of the output file.

In this tutorial, the delimiter is set as a comma since the expected output is a .CSV file. If you need the output in a different file format, you need to assign the corresponding delimiter. For example, if you need the output in a tab-separated file, you need to assign ‘\t’ as the delimiter variable. 

2. After defining the variables, call the convertToCSV function.

var inputFilePath= "/<your path here>/recipients.json"
var addKeys = true;
var delimiter = ",";
convertToCSV(inputFilePath, addKeys, delimiter);

It is a good practice to pass the three variables declared above (inputFilePath, addKeys, and delimiter) as arguments to the function, for ease of reusability of code.

Now let us start writing the convertToCSV function.

3. Define file objects 

  • jsonFile - with read access.
  • csvFile - with write access.

The file object ‘csvFile’ is created and opened in the path ‘outputFilePath’ which points to a CSV file with the same filename, and in the same location as that of the JSON file.

function convertToCSV(inputFilePath, addKeys, delimiter) {
  var jsonFile = new File(inputFilePath);
  jsonFile.open("r", File.CODEPAGE_UTF8);
  instance.vars.outputFilePath = inputFilePath.replace('.json','.csv');
  var csvFile = new File(instance.vars.outputFilePath);
  csvFile.open("w", File.CODEPAGE_UTF8);
      
//conversion logic starts
//to be added
//conversion logic ends
 
 jsonFile.close();
 csvFile.close();  
}

4. Add a conversion logic to convert the JSON to CSV in the function.

Each line in the JSON file is read using the ‘readln()’ method inside a while loop. Inside the loop, each line is appended to a variable ‘fullFile’, so that we get the whole JSON content as a string in the variable ‘fullFile’. This string can now be parsed to a variable ‘jsonObj’ using the ‘JSON.parse()’ method.

//conversion logic starts
  var line = '';
  var fullFile = '';
  while(line = jsonFile.readln()) {
    fullFile = fullFile + line;    
  }
  var jsonObj = JSON.parse(fullFile);

In the example shown below, JSON data has been considered as an array of objects.

Adobe Campaign: JSON to CSV conversion method
Fig 3: An example of an array of single-depth JSON objects.

While looping through each JSON object, we need to fetch the keys and values of each recipient. Write the fetched data to csvFile using the ‘writeln()’ method.

for (var obj in jsonObj) {
    var keyVal = getKeysAndValues(jsonObj[obj]);
    var allKeys = keyVal["keys"];
    var allVals = keyVal["vals"];
    if (addKeys == true) {    
      csvFile.writeln(allKeys.join(delimiter));
      addKeys = false;
    }
    csvFile.writeln(allVals.join(delimiter));
  }
  //conversion logic ends

Notice that the first line of the CSV file contains the keys of the objects, separated by the specified delimiter. This is because the ‘addKeys’ variable was initialized to true. So, if you need a header for your CSV file, make sure you set ‘addKeys’ to true.

5. Define custom function ‘getKeysAndValues()’.

A custom function ‘getKeysAndValues()’ has also been used to separate the keys and values of the JSON objects. The function is shown below:

function getKeysAndValues(obj) {
  var keys = [];
  var vals = [];
  for (var item in obj) {
    keys.push(item);
    vals.push(obj[item]);
  }
  return {"keys":keys,"vals":vals};
}

That is pretty much everything you do to parse a simple JSON file to a CSV file.

But what if the JSON data is more complex and contains nested objects/nested array objects, like the one below?

Fig 4: An example of an array of nested JSON objects.

This complicates things!

Inside the array, there is a nested/multi-level JSON object. We need to convert this multi-level JSON to a single-level JSON. To do this, you can make use of the below script.

function flattenObject(ob) {
  var toReturn = {};
  
  for (var i in ob) {
    if ((typeof ob[i]) == 'object') {
      var flatObject = flattenObject(ob[i]);
      for (var x in flatObject) {
        toReturn[i + '.' + x] = flatObject[x];
      }
    } else {
      toReturn[i] = ob[i];
    }
  }
  return toReturn;
}

As you can see, we have made use of recursion here. We iterate through the object and check if the value in the current iteration is an object. If it is an object, we recursively call the function again and fetch the object inside. For values fetched this way, we slightly alter the key. We combine the key from the main object with the value's original key from the child object and separate them with a dot.

With this function, all that’s left to do is to add this single line to flatten the objects, before calling getKeysAndValues() in convertToCSV():

flatObj = flattenObject(jsonObj[obj]);

In our example, the two objects should be converted to the objects shown below.

Adobe Campaign: Flattened JSON object
Fig 5: Flattened JSON object

The converted CSV will look like this:

Steps for JSON to CSV conversion in Adobe Campaign
Fig 6: Converted CSV file contents

Note: One drawback of our method is that the keys should be the same and in the same order for all objects throughout the JSON file. As we are taking keys from only the first object for writing the header for the CSV file, key-value pairs in all other objects are expected to be in the same order.

Do refer to the entire code for conversion. 

var inputFilePath= "/<your path here>/recipients.json"
var addKeys = true;
var delimiter = ",";
convertToCSV(inputFilePath, addKeys, delimiter);
 
function convertToCSV(inputFilePath, addKeys, delimiter) {
  var jsonFile = new File(inputFilePath);
  jsonFile.open("r", File.CODEPAGE_UTF8);
  instance.vars.outputFilePath = inputFilePath.replace('.json','.csv');
  var csvFile = new File(instance.vars.outputFilePath);
  csvFile.open("w", File.CODEPAGE_UTF8);
  //conversion logic starts
  var line = '';
  var fullFile = '';
  while(line = jsonFile.readln()) {
    fullFile = fullFile + line;    
  }
  var jsonObj = JSON.parse(fullFile);
 
  for (var obj in jsonObj) {
    var flatObj = {};
    flatObj = flattenObject(jsonObj[obj]);
    var keyVal = getKeysAndValues(flatObj);
    var allKeys = keyVal["keys"];
    var allVals = keyVal["vals"];
    if (addKeys == true) {    
      csvFile.writeln(allKeys.join(delimiter));
      addKeys = false;
    }
    csvFile.writeln(allVals.join(delimiter));
  }
  //conversion logic ends
  
 jsonFile.close();
 csvFile.close();  
}
 
function getKeysAndValues(obj) {
  var keys = [];
  var vals = [];
  for (var item in obj) {
    keys.push(item);
    vals.push(obj[item]);
  }
  return {"keys":keys,"vals":vals};
}
 
function flattenObject(ob) {
  var toReturn = {};
  
  for (var i in ob) {
    if ((typeof ob[i]) == 'object') {
      var flatObject = flattenObject(ob[i]);
      for (var x in flatObject) {
        toReturn[i + '.' + x] = flatObject[x];
      }
    } else {
      toReturn[i] = ob[i];
    }
  }
  return toReturn;
}

That is all you need to do to convert a JSON file to CSV file. Not only JSON, whatever the format of the file, it is always convenient to convert the file to a CSV/TSV file before you feed it to the Data Loading activity. And any such conversion is possible with JavaScript code activity.

Adobe Campaign is indeed a behemoth of a marketing automation solution that can be harnessed to solve the toughest of campaign challenges. If the learning curve bothers you or if your efforts are not paying off as well as expected, we as a seasoned marketing automation consultant can help you surmount them. 

No Image