Data Import in Piano Audience

Introduction

Uploading of 1st and 3rd party data is normally motivated by the desire to add additional user data to the data already gather about each user by Piano. This for the purpose of later being able to create user segments that can be used to target groups of users. One of the primary use cases is targeted advertising.

1st party data most often stem from data the users themselves has given during some registration process to obtain a subscription or some other member benefit. Typical data of this kind would be age and gender. 3rd party data normally stems from vendors specializing in either obtaining user data from social networks (for example the user's profession from LinkedIn or sexual orientation ("Interested In") from Facebook) or by the use of probabilistic methods to derive the likely age, gender, income levels etc of the users.

To be able to benefit from the uploaded user data, Piano needs to know which Piano users the data relates to. Since the Piano and the other party has their own system for id-ing the users, the two sets of ids has to be aligned. Only after that will Piano be able to map the uploaded data to the right users.

Depending on the customer case, the uploading of the data can be a one of the kind operation or, what is more common, something to be repeated every so often, most typically once every 24 hours. Another alternative is that it is done for one user at the time whenever that user is accessing a page or is logging in. In the following we will look at both approaches after first having looked at the id syncing step.

Definitions

For simplicity, the term 1st party data or user data refers to both 1st party and 3 party user data if nothing else is specifically stated. The way to sync ids and to upload user data will be very much the same, it be 1st party or 3rd party data.

The term external id as well as the variable name externalId will be frequently used in this tutorial. The term refers to the id used by the Piano customer in their internal client data base (their CRM system). The cookie-based id used by Piano to identify the same user is referred to as the Piano (Cxense) ID.

Prerequisites

To upload you need access to the following:

• The site id of the site

• A user with write access to (one of) the site group(s) the site is member of

• The user's API key

• The site group's customer prefix

ID syncing

Id syncing is the operation of aligning the Piano browser cookie based user ids with the customer's ids for the same users. The customer's user id is normally used as the key in the user table of their CRM database. There are various ways of doing this and in the following we will look at each of them.

Client-side ID syncing

In the figure below, steps 1 through 3 shows the id syncing operation. Step 4 is the user data upload operation that is the final objective that we will discuss in the next section.

Step 1 above corresponds to line 9 in the extended Piano tracking script below (the extension is line 4 through 8). That is, a pixel request is sent with all the information gathered about the user and the user's visit to the page up to that point. One piece of that information is the Piano (Cxense) user ID. Step 2 is when the user logs in and by that reveals his or her identity in the customer's system. This gives us a chance to execute line 5 below which corresponds to step 3 in the figure above where the padlocked chain illustrating how the two ids from this point on are linked together forever.

<script>
    var cX = cX || {}; cX.callQueue = cX.callQueue || [];
    cX.callQueue.push(['setSiteId', '<SITE ID>']);
    cX.callQueue.push(['invoke', function() {
        if (typeof externalId !== "undefined") {
            cX.addExternalId({'id': externalId, 'type': '<CUSTOMER PREFIX>'});
        }
    }]);
    cX.callQueue.push(['sendPageViewEvent']);
</script>

Obtaining external ID

There are mainly 3 ways one get hold of the customer's id for the user that is currently visiting a page:

• It exists (for whatever reason) as a global variable (possibly under some other name)

• It is stored in a cookie

• It is provided as a URL parameter

Obtaining external ID from a cookie

The first case is simple, we can use our code from above as is (or at least with no other change than changing the variable name externalId to something else). In the second cookie stored case we would extend the code from above with one extra line of code as shown here in line 5:

<script>
    var cX = cX || {}; cX.callQueue = cX.callQueue || [];
    cX.callQueue.push(['setSiteId', '<SITE ID>']);
    cX.callQueue.push(['invoke', function() {
        var externalId = cX.getCookie("<NAME OF THE COOKIE>");
        if (typeof externalId !== "undefined") {
            cX.addExternalId({'id': externalId, 'type': '<CUSTOMER PREFIX>'});
        }
    }]);
    cX.callQueue.push(['sendPageViewEvent']);
</script>

Obtaining external ID from a URL parameter

One way to sync user ids is to have the user click a link to a landing page from within a newsletter as shown in the graphic below:

In step 1 the customer is providing their newsletter distributor the content of the newsletter to be sent, the e-mail addresses of their users to which it is to be sent, and their corresponding ids of all those users. In step to the newsletter distributor sends out the newsletter via an e-mail to all registered users. Within the e-mail there will be a link to a landing page. The link has been extended with an URL parameter holding the user's id in the customer's system (example: ?nlid=1234). Line 5 of the Piano script above is now changed to the line below which extract the external id from the link in the newsletter. In step 3 the page view event fires off with both the Piano and the customer's ID.

var externalId = cX.parseUrlArgs().nlid;

Delayed pageview event

Sometime the external id will not be available until after the page has been up for some time. This would typically be some hundreds of milliseconds after the page visit has been initiated, but in principle it can be any length of time. In these cases, if we were to fire off the page view event the normal way, the external id will not be around and thus not be part of the data sent to Piano Insight. We can solve this by wrapping the page event code in a function that is called whenever the external id is available as shown here:

<script>
    function sendLatePageViewEvent(externalId) {
        window.cX = window.cX || {}; cX.callQueue = cX.callQueue || [];
        cX.callQueue.push(['invoke', function() {
            cX.setSiteId('<SITE ID>');
            cX.addExternalId({'id': externalId, 'type': '<CUSTOMER PREFIX>'});
            cX.sendPageViewEvent();
        }]);
    }
</script>
 
<script>
    // This line is called at some later time when the external id is available
    sendLatePageViewEvent(externalId)
<script>

The risk with this approach is of course that one delay the page view event until it is too late and the page is closed or departed by the user. If this happens,  one not only fail to sync the user id, one will also fail to report the page view.

Pageview-independent syncing

In all the examples so far we have piggy-backed on the page view event by adding the external id to the data being uploaded. This is by far the easiest way of implementing the id syncing as the function cX.addExternalId() does all the work for us and there is no need for any persisted query or for applying JSONP functionality. However, this approach is not always possible or desirable. Especially in the case of 3rd party data vendors, it makes more sense to have a page view independent way of syncing the user ids as this would remove the need for the customer and data vendor to coordinate their codes just to get it all to work. Below we see an example of page view independent id syncing:

cX.callQueue.push(['invoke',function() {
   var persistedQueryId = "<YOUR PERSISTED QUERY ID>";
   var apiUrl = 'https://api.cxense.com/profile/user/external/link/update?callback={{callback}}'
                + '&persisted=' + encodeURIComponent(persistedQueryId)
                + '&json=' + encodeURIComponent(cX.JSON.stringify({ id: externalId, cxid: cX.getUserId() }));
   cX.jsonpRequest(apiUrl, function (data) {
        // Uncomment for debugging:
        //alert(cX.JSON.stringify(data))
   });
}]);

The approach requires a persisted query id that you can either obtain from Cxense (to reuse an old one obtained for another use case will not work) or create yourself as described in the Persisted Query Tutorial. The function for which we need to make the persisted query is described at /profile/user/external/link/update.

Server-side ID syncing

In cases where the client side never get to know the customers's id for the user, that is, it is not possible to give the variable exteranlId in the code above the user's id, then the approach shown in the figure below can be used instead:

Instead of adding the customer's id (the variable externalId) to the data reported by the Piano tracking script, the Piano user id is now instead passed on to the customer's back end (illustrated by step 2 in the figure above). The back end obtains the Piano user id from the page's HTTP header as the value of the cookie named "cX_P" (which is the same id as returned by the cx.js library function cX.getUserId())

As soon as the server side has the user's Identity Management, then the customer can execute the functionality shown by the Python 3.x example script below that will sync the ids for a single user. The script below corresponds to step 3 in the figure above. We see in line 26 how the two ids are being mapped together.

_username   = "<YOUR CXENSE USER ID>"
_secret     = "<YOUR CXENSE API KEY>"
_prefix     = "<YOUR CUSTOMER PREFIX>"
_cxUserId   = "<THE USER'S CXENSE ID>"
_externalId = "<THE USER'S ID IN YOUR SYSTEM>"
   
import json, datetime, hmac, hashlib, http.client
      
def cxApi(path, obj):
    date = datetime.datetime.utcnow().isoformat() + "Z"
    signature = hmac.new(_secret.encode('utf-8'), date.encode('utf-8'), digestmod=hashlib.sha256).hexdigest()
    headers = {"X-cXense-Authentication": "username=%s date=%s hmac-sha256-hex=%s" % (_username, date, signature)}
    connection = http.client.HTTPSConnection("api.cxense.com", 443)
    connection.request("POST", path, json.dumps(obj), headers)
    response = connection.getresponse()
    status = response.status
    responseObj = json.loads(response.read().decode('utf-8'))
    connection.close()
    return status, responseObj
    
def errorHandling(status, response, message):
    if status != 200:
        raise Exception("%s (http status = %s, error details: '%s')" % (message, status, response['error']))
   
def syncUserIds(cxUserId, externalId, prefix):
    status, response = cxApi('/profile/user/external/link/update', {"cxid":cxUserId, "id":externalId, "type":prefix})
    errorHandling(status, response, "Failed to sync user")
    return response
  
syncUserIds(_cxUserId, _externalId, _prefix)

The script above is just a example. For writing your own script using the Piano API, check out the Audience/Insight/Content API tutorial. In particular check out the batch mode section as running the script one id at the time is highly inefficient.

The same functionality implemented in the script above, can be executed with the command line below using the cx.py tool:

cx.py /profile/user/external/link/update '{"cxid":<CX_USER_ID>, "id":<EXTERNAL_ID>, "type":<PREFIX>}'

Data uploading

Above we did one of the two steps required for successfully uploading 1st or 3rd party user data. And that was the syncing of ids between the two systems: Piano Insight on one side and the customer's CRM or some third party vendor's database on the other. And notice the choice of words, "one of the two steps" and not "the first of the two steps". That is because the two operations can be done in any order, but only after both operations are completed for a particular user will the uploaded data be linked to that user in Piano Insight.

The most common way to upload the data is to first have the CRM system/database dump the user table as a csv file. That file can then be used as input to some script that do the uploading. An alternative is of course that the script reads the data directly from the database. Given how common the csv approach is and also how much more generic the example will be, below we have implemented a Python script that reads the content of a csv file and upload it to Piano. The sample data that we will be uploading looks like this:

id,gender,age
1234,male,22
5678,female,39

And here is a script that will upload the data:

_username     = '<YOUR CXENSE USER NAME>'
_secret       = '<YOUR SECRET API KEY>'
_prefix       = '<YOUR CUSTOMER PREFIX>'
_dataFile     = '<YOUR CSV DATA FILE PATH>'
_delimiter    = '<YOUR DATA FILE DELIMITER>'
   
import json, datetime, hmac, hashlib, http.client
    
def cxApi(path, obj):
    date = datetime.datetime.utcnow().isoformat() + "Z"
    signature = hmac.new(_secret.encode('utf-8'), date.encode('utf-8'), digestmod=hashlib.sha256).hexdigest()
    headers = {"X-cXense-Authentication": "username=%s date=%s hmac-sha256-hex=%s" % (_username, date, signature)}
    connection = http.client.HTTPSConnection("api.cxense.com", 443)
    connection.request("POST", path, json.dumps(obj), headers)
    response = connection.getresponse()
    status = response.status
    responseObj = json.loads(response.read().decode('utf-8'))
    connection.close()
    return status, responseObj
   
def errorHandling(status, response, message):
    if status != 200:
        raise Exception("%s (http status = %s, error details: '%s')" % (message, status, response['error']))
          
def uploadDataAboutSingleUser(id, type, profile):
    status, response = cxApi('/profile/user/external/update', {"id":id, "type":type, "profile":profile})
    errorHandling(status, response, 'Unable to upload external user data')
    return response
 
def uploadAllUsersData(dataRows, columnNames):
    for userData in dataRows:
        profile = []
        for i in range(len(columnNames)):
            if columnNames[i] == 'id':
                userId = userData[i]
            else:
                profile.append({"group":_prefix + '-' + columnNames[i], "item":userData[i]})
        print(uploadDataAboutSingleUser(userId, _prefix, profile))
 
dataRows = [line.strip().split(_delimiter) for line in open(_dataFile, 'rb').read().decode('utf-8').split('\n') if len(line) > 0]
uploadAllUsersData(dataRows[1:], dataRows[0])

The script above is just a (fully working) example on how to write the data uploading. However, to keep it simple, it makes several assumptions (assumes an 'id' column), do no error handling and upload users one by one (highly inefficient). For writing your own script using the Piano API, check out the Audience/Insight/Content API Tutorial.

Data formats

Our sample data above was very simple. Below you see what characters one is allowed to use for the various entities.

* The max length is 30 instead of 26 if one in the count includes the prefix and the dash that has to precede the column name when uploading the data. Example: "xyz-gender".

Reserved key-value pairs

As long as one stick to the data format above, one can upload any key-value pair one can imaging with the following two exceptions. Piano has reserved these two key names for which only a specific set of values are permitted.

One should strive at using these instead of one's own key-value pairs for gender and birth year since the ones listed above are supported by built-in Piano Insight GUI functionality.

Additionally the following keys are reserved by Piano and should not be selected for ingestion at all:

• <prefix>-card-paid-after-exp

• <prefix>-churned

• <prefix>-gift-recipient

• <prefix>-next-billing-date

• <prefix>-registered

• <prefix>-sub-autorenew-off

• <prefix>-sub-card-expiration

• <prefix>-sub-in-grace-period

• <prefix>-sub-ltc

• <prefix>-sub-payment-term

• <prefix>-sub-start-date

• <prefix>-subscriber

• <prefix>-trial-free

• <prefix>-trial-paid

Key-list-of-values pairs

In our example above we only dealt with single values for each key as shown here:

profile = [{"group":"xyz-age", "item":"22"},{"group":"xyz-gender", "item":"male"}]

However, sometimes a key can have several values as shown in the next example where the same user has now got a language key with values indicating that he knows both English and Spanish:

profile = [{"group":"xyz-age", "item":"22"},{"group":"xyz-gender", "item":"male"},{"group":"xyz-language", "item":"english"}, {"group":"xyz-language", "item":"spanish"}]

Hence, it is perfectly fine to add many values for the same key. They will not overwrite each other.

Maximum number of profile items

It is not possible to upload more than 40 objects/items per profile. By items we are not referring to keys, but to unique key-value pairs. Hence, in the previous example above where the key xyz-language appeared twice, the total number of items was four (one 'xyz-age', one 'xyz-gender' and two 'xyz-language'). It is however possible to exceed the 40 items limitation and go all the way up to 100 items. However, in that case one has to contact one's account manager and request a feature named prefix100.

Interweaved approach

So far we have be doing id syncing and data uploading as two very separate operations. We have done id syncing every time the user visit a page (if logged in) and the data uploading totally independent once every 24 hours or whatever schedule one has settled for. It is however possible to do the two at the same time. The sample code below uploads the gender and the age of the user within the same code block as sending in the page view data:

<script>
	var prefix                = "<CUSTOMER PREFIX>";
    var profile               = {<SAME DATA AS IN PYTHON EXAMPLE ABOVE>};
    var persistedQueryId      = "<YOUR PERSISTED QUERY ID>";
	var throttleCookie        = "<NAME OF THE UPLOAD RATE CONTROL COOKIE>";
	var minTimeBetweenUpdates = <MINIMUM NUMBER OF MINUTES BETWEEN UPDATES>;

	var cX = cX || {}; cX.callQueue = cX.callQueue || [];
	cX.callQueue.push(['setSiteId', '<SITE ID>']);
	cX.callQueue.push(['invoke', function() {
		if (typeof externalId !== "undefined") {
			cX.addExternalId({'id': externalId, 'type': prefix});
    	}
    }]);
	cX.callQueue.push(['sendPageViewEvent']);
 
	cX.callQueue.push(['invoke', function() {
		if (!cX.getCookie(throttleCookie)) {
    		var apiUrl = 'https://api.cxense.com/profile/user/external/update?callback={{callback}}'
            		+ '&persisted=' + encodeURIComponent(persistedQueryId)
            		+ '&json=' + encodeURIComponent(cX.JSON.stringify({"id":externalId, "type":prefix, "profile":profile});
    		cX.jsonpRequest(apiUrl, function(data) {
        		// Uncomment for debugging:
        		//alert(cX.JSON.stringify(data))
    		});
			cX.setCookie(throttleCookie, "throttle", minTimeBetweenUpdates / (24 * 60))
		}
	}])
</script>

The motivation for the throttle cookie is to prevent the Piano server from getting overloaded with user data uploads. User data such as for instance age and gender typically don't change that often, so unless one is uploading more fluctuating types of data, then one can let hours, days, or even weeks pass by between each upload. If the Piano server receives too much traffic from a particular site, it will be blacklisted in an effort of self-protection. It is thus very much in the customer's interest to avoid overloading.

The code above assumes that you have access to the user data and can construct the same user profile variable as in the Python program above (where the external ID was 1234):

{'type': 'xyz', 'id': '1234', 'profile': [{'item': 'male', 'group': 'xyz-gender'}, {'item': '22', 'group': 'xyz-age'}]}

The code also requires you to create a persisted query for the API function /profile/user/external/update.

Verification and troubleshooting

How do one go about verifying that it all works and if it does not, where do you start looking for the reason? Below we see a 3 point check list that we can follow:

• Was the final objective achieved?

• Did the ID syncing take place?

• Did the data uploading take place?

The first thing we can check is to see if the final objective with the data uploading is achieved. If it is, then no further troubleshooting is required. However, if that is not the case, then we need to find out if it was the ID syncing or the data uploading that was unsuccessful.

Was the final objective achieved?

In most cases, the final objective of the uploaded user data is to enable more granular segments by enabling the customer to filter on more user characteristics, typically gender, age, profession, etc. Below we see how we can verify that the data we uploaded earlier in this tutorial, was successfully uploaded. We simply do this by re-finding the uploaded data in the drop downs of the segment creation wizard as shown below:

Before a successful upload of any 1st or 3rd party data there would be no 1st Party Data option to select in the leftmost drop down (and you read correctly, any 3rd party data will also be found under the 1st Party Data option in the drop down). Further, before any age data has been successfully uploaded, there will be no <prefix>-age value to chose in the second drop down, and finally, before any successful upload of the specific age values of 22 and 39, none of these will appear in the right most drop down.

So if you data does not appear here, it is still not sure that the data has not been successfully uploaded. This because it may takes some time from data is uploaded the first time until it will start appearing in the segment creation drop down boxes. This time delay has to do with the time it takes to index the 1st/3rd party data the very first time.

Did ID syncing take place?

If the final objective was not achieved, then we need to find out what failed. Could it be that the id syncing failed?

If the id syncing succeeded, then using the customer's external id, one should be able to retrieve the user data collected by Piano Insight as well as the data uploaded by the customer as shown here below. This of course assumes that the user id that we use for this test stems from a user that has actually visited the site in a way that made his/her id end up being synced (for instance, if syncing only happen on the log in page, then the user must have logged in at some time before we run the command below).

$ python cx.py /profile/user '{"type":"xyz", "id":"1234"}'
{
  "id": "1234",
  "type": "xyz",
  "profile": [
    {
      "item": "25",
      "groups": [
        {
          "group": "xyz-age",
          "weight": 2.0,
          "count": 1
        }
      ]
    },
    {
      "item": "male",
      "groups": [
        {
          "group": "xyz-gender",
          "weight": 2.0,
          "count": 1
        }
      ]
    },
    {
      "item": "wiki.cxense.com",
      "groups": [
        {
          "group": "cxe-site",
          "weight": 1.0,
          "count": 1
        }
      ]
    },
    {
      "item": "windows",
      "groups": [
        {
          "group": "device-os",
          "weight": 1.0,
          "count": 1
        }
      ]
    }
  ]
}

In the command output above we see that we have a mix of uploaded data (age and gender) and Piano Insight gather data (the user's operating system and the URL of sites the user has visited).

If the command above fails to give the expected output, then we have to start looking for the error in the web page.  All information in regard to a page view, including the added external id in this case, is past on as URL query parameters in a pixel request sent to Piano Insight. Hence, by looking at the pixel request in the browser debugger, we can see if the external id indeed is being sent or not. The name of the external id parameter is eid0 and is listed in overview of pixel request parameters. In addition to the external id, the customer prefix will be sent as the parameter eit0. The screenshot below shows how one in this case using the debugger of a Chrome browser, can verify that the data is actually being sent:

Did data uploading take place?

The other thing that can have gone wrong if the data is not appearing in the Piano GUI is the data uploading itself. To check on that we can take a random id from the data we uploaded and see if Piano can account for the data. Below we see how we can use the Piano cx.py tool for that:

$ python cx.py /profile/user/external/read '{"type":"xyz", "id":"1234"}'
{
  "data": [
    {
      "id": "1234",
      "type": "xyz",
      "profile": [
        {
          "group": "xyz-gender",
          "item": "male"
        },
        {
          "group": "xyz-age",
          "item": "22"
        }
      ]
    }
  ]
}

And if you run the same command after removing the user id, then you end up with the full list of users. Due to the amount of data it is better to redirect the output to a file as done below:

python cx.py /profile/user/external/read '{"type":"xyz"}' > users.json

If Piano does not have the data then we need to go back to our uploading program and find out what we did wrong there. In our uploading script above, we throw errors when something fails and the error message can be informative in regard to the source of the problem.

Data quality and usefulness

In most use cases the purpose of the uploaded user data is add granularity to Piano Audience segments. Whereas one before could create the segment "users in London", one could now after the uploading possibly make the segment "female users above 40 years from London that are engineers or doctors" if the relevant user data was uploaded.

It is however important to keep in mind that, if one has data, it does not mean that it is well fit for being uploaded: it can be good, useless or need modification.

Suitable data

The table below shows typical good data that will serve the purpose of creating meaningful user segments.

Data to be modified

The next category is inappropriate data represented by the left column below, that with minimal efforts can (programmatically) be converted into good data as shown in the right column:

For data to be useful to the person creating a audience segment, the data must fall into a limited number of categories. Above we have either converted or extracted data so that it fall into a limited number of options.

Inappropriate data

This last category holds data that is useless as audience segment data because it in one way or another will create single user audience segments.

_{*The misspelling is done on purpose}

The first one, interests, contains free text (no one else will come up with this exact value) and a misspelling (few other, maybe none will ever write "horse riding" that way). The next 3 ones (mobile, email, credit card) are id type of data and by nature is meant to only cover a single person and the credit card one will in addition probably break the law in most jurisdictions. The last one (the user's first name) will probably cover more than one person, but what possible use could you have from it? Using it for targeted ads selling coffee mugs with the name Chris on them?

Having Piano do data uploading

From the above description it should be clear that it is a very feasible thing for a customer to take care of their own 1st party data uploading and this is also what Piano normally expects. However, if a customer for whatever reason is unable to implement their own data uploading, then they can pay Piano to take care of it for them. Piano has a configurable tool called MAD (acronym for Moves Any Data) that is used to upload or download various kind of data to and from Piano and this is not limited to just user data, but applies to traffic data, user segments and even searchable documents (thus the name). The way MAD operates is that the customer uploads a csv (comma separated values) file with the user data to a FTP,  SFTP or Amazon S3 and then provide Piano with the information required for having MAD download the data on a regular basis (normally once every 24 hours).

Data source and schedule

Below we see the part of the MAD configuration that relates to form where MAD is to download the data and according to what schedule. This is information the customer has to provide to Piano.

# Ftp/sftp
ftpServer           = '<FTP/SFTP IP OR FQDN>'
ftpDir              = '<FTP/SFTP DIR PATH>'
ftpUser             = '<FTP/SFTP USER>'
ftpPwd              = '<FTP/SFTP PASSWORD>'
ftpPort             = '<PORT NUMBER, DEFAULT: 22>'
ftpSecure           = '<True IF SFTP, False IF FTP, DEFAULT: True>'
ftpDeleteRemoteFile = '<True/False, DEFAULT: True>'
 
# Amazon S3
s3Bucket            = '<S3 BUCKET NAME>'
s3AccessKey         = '<S3 ACCESS KEY>'
s3SecretAccessKey   = '<S3 SECRET ACCESS KEY>'
s3Folder            = '<S3 KEY PREFIX, DEFAULT: None>'
s3Key               = '<S3 BUCKET KEY NAME, None (ALL KEYS)>'
s3DeleteRemoteFile  = '<True/False, DEFAULT: True>' 
 
# Scheduling
execTimes           = ['HH:MM']

Data formats

The only data format so far supported is csv. However, csv data can be a lot of things. In the following we described the various variants of csv that MAD can be configured to handle.

For increased readability we are in the following using spreadsheet screenshots to demonstrate the various formats like this one:

However, the actual data is a delimiter separated line of textual values. Hence, the actual content of for spreadsheet example above is something like this (depending on what delimiter is being used, in this case, a semicolon):

id;gender;byear;profession;language
1234;male;1979;teacher;english
5678;female;1982;nurse;spanish

MAD is highly configurable and supports various CSV data formats. In the following, we will cover some of the options a customer has.

Fixed vs. free columns

MAD can be configured with a fixed column format or with a free column format.

In case of fixed columns, a given column has to have the same data across all files. No column can be added or removed without MAD being reconfigured. In the example above, the five columns shown must always be present and in the same order (even though the cells may be empty). When using a fixed columns format, there is no need for a column heading line in the file. Hence, line 1 above can stay or be removed. The column headings will be "hard  coded" as a list (array) in the MAD configuration file as shown here:

inputKeys= [id', 'gender', 'byear', 'profession', 'language']

In the case of free columns, the position of the columns can be changed from file to file, and columns can be dropped and added without any need for reconfiguration. The column heading line is mandatory. Hence, line 1 in the table above must always be present as it is this line MAD uses for mapping attribute names to attribute values. The free columns modus is set by the following MAD configuration:

inputKeys= 'firstline'

The column names used in the CSV data file do not need to be the attribute names that end up being displayed in Piano Audience. By providing Piano with the mappings, the column names in the CSV input file will be replaced before uploading. Below we see an example of an input file followed by the MAD mapping configuration.

inputKeys = {'id':'id', 'var1':'gender', 'var1':'byear', 'var1':'profession', 'var1':'language'}

The input data and the mapping table will together create the very same end result as the input data in the previous example above.

Internal keys

MAD can also be configured to use a column-independent format in which each data cell holds both the key and the value.

 If the first column does not have an internal key, MAD will interpret the value to be the user ID (one can also explicitly use id=1234 in the first or any other column). Below we see how to configure this modus.

inputKeys = 'internal'
internalDelimiter = '='

Again, the end result will be the very same as in the two other examples above.

Multi-value columns

Sometimes one has to deal with types of data for which a column may hold more than one value. The users in the example above are only registered with one language, but many people master two or more languages. How can such data be uploaded?

In the case of the fixed or free column formats, one can add extra rows with the additional data. The rows must have the same user id as the original row and all rows with the same id must be grouped together (if not only the last (group of) row(s) for a particular user id will be uploaded).

mergerColumn = 'id'

Another alternative is to use an internal cell delimiter as shown here:

listSeperator = '|'

In the case of internal keys, it is just a matter of adding an extra column with the additional data as shown below. No additional configuration is required.

The formats shown here are the most common ones. MAD also supports other less commonly used ones.

Last updated: June 24, 2026