Feeding your Actito Profile tables

Introduction#

If you want to create and update your profiles in your Actito database directly via API, this case is made for you.

You will learn here the basis to automate your data flows.

While understanding of the Actito concepts is sufficient to set up your data flows, you can read the full documentation on Actito Profile tables to dive into profiles management.

Automated imports#

Let's consider a practical case:

When a client registers on your website, you want to immediately create their profile in Actito.
Stating you use an opt-in management page in your website, you want the modification on subscriptions/unsubscriptions to be pushed to Actito immediately.
Twice a day, you import a list of new profiles acquired from partner agencies.
Should a profile delete their account in your system, you want to remove their information from your Actito database

Step by Step#

Create or update a single profile
Update a single profile subscriptions
Delete a single profile
Launch a bulk profile import and check its result

Prerequisites

Make sure the profile table exists in your licence and that you know the entity which it belongs to (check with your marketeer).
Check that the structure of that profile table contains all the fields necessary for the data you wish to provide.
We assume that you have checked out the Quick Start guide, so we guess that you know how to make some curl calls with the sample JSONs of the steps below.

Step 1. Create or update a single profile#

Real-time one by one API calls should be used when the data is to be present in Actito and used as soon as it is present in your system (for example to trigger a scenario, send a welcome e-mail etc.).

You can make that happen by using the operation:

POST /entity/MyEntity/table/MyWebsiteProfiles/profile?allowUpdate=true

This operation will create a new profile or update an existing one by using the data you provide within the call.

These data are profile attributes values, subscriptions, segmentations and data collection information. All that fields will define the full information of the profile.

Hereby follows an example of a representative JSON:

{  "attributes": [    {      "name": "lastName",      "value": "Smith"    },    {      "name": "firstName",      "value": "John"    },    {      "name": "birthDate",      "value": "26/05/1967"    },    {      "name": "sex",      "value": "M"    },    {      "name": "motherLanguage",      "value": "EN"    },    {      "name": "emailAddress",      "value": "john.smith@actito.com"    },    {      "name": "addressLocality",      "value": "Los Angeles"    },    {      "name": "addressCountry",      "value": "US"    },    {      "name": "gsmNumber",      "value": "1223344556"    },    {      "name": "customerId",      "value": 34567    },    {      "name": "nbChildren",      "value": 2    }  ],  "dataCollectionInformation": {    "date": "10/09/2019",    "source": "TEST",    "way": "website registering"  },  "subscriptions": [    {      "name": "Newsletter",      "subscribe": "true"    },    {      "name": "Promotions",      "subscribe": "false"    }  ],  "segmentations": [    {      "belongs": "true",      "segmentation": {        "name": "ClientType",        "category": "Gold"      }    },    {      "belongs": "true",      "segmentation": {        "name": "isActive",        "category": "Member"      }    }  ]}

In this example, Actito will try to match an existing profile in table "MyWebsiteProfiles" regarding the unique key customerId which is the unique identifier of the clients in your system. If it exists, Actito will update it by replacing existing data with the provided. If not, Actito will create a new profile.

As everything is OK, you receive a 200 OK http response with the following JSON response body that contains the profileId of the created/matched profile :

{  "profileId": 1147690}

Behaviour in case of multiple unique keys in the database

This operation can be used both for Create and Update operations. For Updates, the "allowUpdate" parameter must set as true. In this case, the link with the existing profile will be based on a key attribute.

If the JSON object contains more than one key (for instance, a customerId and a unique e-mail address), the following rules will be applied to find the matching profile:

For each value of unique attribute found in the file, the program will search for the matching profile
If all the unique field values match the same profile, the profile is updated
If two or more field values match different profiles, a conflict is generated and the update is not applied (you'll receive a 409 Conflict http response)
If no matching profile can be found, the system will create a new one

Step 2. Update a single profile subscriptions#

As an existing profile modifies his opt-in preferences on your website (personal space), you also want this modification to be immediately pushed to Actito.

To update an existing profile, you may use the operation:

PUT /entity/MyEntity/table/MyWebsiteProfiles/profile/1147690

In that example, we will update a profile that exists in "MyWebsiteProfiles" with the profileId equal to 1147690.

In that a case, the JSON body to provide in the request should only contain information that has to be updated. Unmentioned attributes in the body will be left unchanged in the profile.

In our use case, wishing only to update the opt-in preferences of our profile, the following JSON only references the subscriptions to modify and the GDPR compliance information dataCollectionInformation:

{  "subscriptions": [    {      "name": "Newsletter",      "subscribe": "false"    },    {      "name": "Promotions",      "subscribe": "true"    }  ],  "dataCollectionInformation": {    "source": "WebsitePersonalSpace",    "date": "2019-09-10 10:10:00",    "way": "A way"  }}

As everything is OK, you receive a 200 OK http response with the following JSON response body that contains the profileId of the updated profile:

{  "profileId": 1147690}

Note that you can also impact a single subscription or segmentation for an existing profile by using those operations:

Subscribe the profile to subscription ***Newsletter***:

POST /entity/MyEntity/table/MyWebsiteProfiles/profile/1147690/subscription/Newsletter

Unsubscribe the profile of subscription ***Newsletter***:

DELETE /entity/MyEntity/table/MyWebsiteProfiles/profile/1147690/subscription/Newsletter

When using those two methods, you should provide always provide the data collection information (GDPR compliance) as the JSON body of your requests:

{  "source": "WebsitePersonalSpace",  "date": "2019-09-10 10:10:00",  "way": "A way"}

Step 3. Delete a single profile#

If a profile requests the deletion of their account from your website, you should also delete it in Actito, as keeping their information is not GDPR compliant.

You can delete a profile through the operation:

DELETE /entity/MyEntity/table/MyWebsiteProfiles/profile/1147690

If no profile has been found for the provided profileId, you'll receive a 404 NOT FOUND http response.

If the profile has been found and deletion was OK, then you'll receive a 200 OK http response.

Use DELETE with caution...

Though deleting a profile through the Actito APIs is possible, please note that such a deletion has consequences. We therefore invite you to read the Deleting a profile page before automating such an action.

Step 4. Launch a bulk profile import and check its result#

As one by one operations are not the most efficient way to push a huge volume of data, Actito Integration Framework provides an asynchronous data import API that takes in charge flat files upload and load into your Profile tables. Bulk API calls should be preferred for daily/weekly/monthly flows that don't require immediate result or real-time based marketing action triggering.

In addition to new customers who register on your website, you also acquire new profiles through the means of partners agencies (lead generation during an exhibition, a commercial fair etc. for instance).
As they provide you daily with a list of new prospects, a daily bulk call is therefore the best way to import those profiles into Actito.

info

There are 2 ways to import bulk files through the Actito API:

with the v5 call to create a one shot ETL execution
with the v4 call to create a bulk import

Both are equally valid and it is not planned to remove any option in the near future. Nevertheless, we advise you to prefer the more recent 'one shot ETL' feature, because it offers additional possibilities, such as data transformation or retrieving the file on a remote server.

Launch a one shot ETL execution (v5 API)#

To launch a one shot ETL execution, use the operation:

POST /entities/MyEntity/etl-executions

tip

This operation can both be used to relaunch a failed daily synchronization or to launch a one shot synchronization. We will focus only on the second option.

When creating a one shot ETL, you will need to provide a flat CSV file.We kindly encourage you to compress it (ZIP and GZIP are supported) to optimize data transfer. In case of a ZIP file, it should contain a single CSV file.

The CSV file format must fit with following rules:

The file must be a valid CSV
The file first line contains column headers
Every header is mapped to the technical name of a field of the profile table (a field can be an attribute, a subscription or a segmentation) in the attributeMapping parameter of the definition
Technical names are case sensitive
Subscriptions will be matched by using the following pattern: subscriptions#xxxx where xxxx is the name of the subscription.
Segmentations will be matched by using the following pattern: S_xxxx where xxxx is the name of the segmentation.
Comprehensive information regarding data format is available here.

There are 2 options with one shot ETLs:

retrieving the file from a file transfer location (FTPS server)
- use an application/json content type body to provide the oneshot ETL definition
providing the file in the call
- use a multipart/form-data content type to provide at the same time the oneshot ETL definition in a JSON file and the input (zipped) CSV file

In both cases, the content of the JSON definition of the import is similar.

It always includes:

the format of the CSV file
the profile table destination and the attribute mapping
the mode of the import (CREATE_UPDATE, CREATE_ONLY, UPDATE_ONLY, DELETE)

Optionally, the definition can include:

reports recipients who will receive an e-mail notification
data transformation
whether result and error files must be generated

If the file is retrieved from a file transfer location, the definition must also specify the location reference.

Example of a call with a file retrieved from a FTPS location:

curl  -X POST \  'https://api3.actito.com/v5/entities/{{entity}}/etl-executions' \  --header 'Accept: */*' \  --header 'Authorization: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJhY2NvdW50SWQiOjM3LCJzdWIiOiIyMDAxODQiLCJsaWNlbmNlSWQiOjIwMDE4NCwiaXNzIjoiYWN0aXRvIiwiYWNjZXNzUmlnaHRzIjpbIndyaXRlX2V4dGVybmFsX2NvbnRlbnQiLCJ3cmlAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' \  --header 'Content-Type: application/json' \  --data-raw '{  "type": "ONESHOT",  "fileTransfer": {    "input": {      "file": {        "fileNamePattern": "customers.csv",        "compressionType": "GZIP",        "compressedFileNamePattern": "customers.csv.gz"      },      "location": {        "type": "REMOTE",        "remoteLocationId": "1"      },      "deleteFilesOnSuccess": true    },    "output": {      "location": {        "type": "REMOTE",        "remoteLocationId": "1"      }    }  },  "inputFileProperties": {    "csvFormat": {      "separator": ","    }  },  "dataLoading": {    "destination": {      "type": "PROFILE_TABLE",      "id": "123",      "attributesMapping": [        {          "header": "email",          "attributeName": "emailAddress"        },        {          "header": "last_name",          "attributeName": "lastName"        },        {          "header": "first_name",          "attributeName": "firstName"        }      ]    },    "parameters": {      "mode": "CREATE_OR_UPDATE",      "generateErrorFiles": true,      "generateResultFiles": true    }  },  "reportRecipients": [    "john.smith@actito.com"  ]}'

For a one shot ETL with the file provided in the call, this will consist in a multipart/form-data body with a CSV file called 'inputFile' and JSON file called 'oneshotEtl' that contains the ETL definition (like in the JSON body above)

Example of a call with a provided file:

curl  -X POST \  'https://api3.actito.com/v5/entities/{{entity}}/etl-executions' \  --header 'Accept: */*' \  --header 'Authorization: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9.eyJhY2NvdW50SWQiOjM3LCJzdWIiOiIyMDAxODQiLCJsaWNlbmNlSWQiOjIwMDE4NCwiaXNzIjoiYWN0aXRvIiwiYWNjZXNzUmlnaHRzIjpbIndyaXRlX2V4dGVybmFsX2NvbnRlbnQiLCJ3cmlAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' \  --form 'inputFile=@\yourfolder\inputFile.csv' \  --form 'oneshotEtl=@\yourfolder\oneshotEtl.json'

In both cases, the response of a successful call will be the id of the one shot execution.

Check the result of a one shot execution#

One shot executions allow you to receive a report directly by e-mail, but you can also check the result through the operation

GET /entities/MyEntity/etl-executions/123456/integration-results

Retrieve the result or error file#

You can then retrieve the output files of the execution, which will be the result and/or error files, depending on the result of the execution.

/entities/MyEntity/etl-executions/123456/output-files

tip

The output files are only generate if the parameters generateErrorFiles and generateResultFiles are set to true in the definition.

To understand the error file, check the explanation of the possible error codes.

Launch a bulk import (v4 API)#

It is also possible to launch a bulk import using the following v4 operation:

POST /entity/MyEntity/table/MyProspectsProfiles/import

This call will require you to post a flat CSV file. We kindly encourage you to compress it (ZIP and GZIP are supported) to optimize data transfer. In case of a ZIP file, it should contain a single CSV file.
In our example, the data are to be loaded in your MyProspectsProfiles profile table.

The CSV file format must fit with following rules:

The file must be a valid CSV
The file first line contains column headers
Every header matches the technical name of a field of the profile table (a field can be an attribute, a subscription or a segmentation) or a custom mapping should be provided (check how below).
Technical names are case sensitive
Subscriptions will be matched by using the following pattern: subscriptions#xxxx where xxxx is the name of the subscription.
Segmentations will be matched by using the following pattern: S_xxxx where xxxx is the name of the segmentation.

To fit with your CSV generation tool or program (if not exactly what Actito takes in charge by default) and to specify the data loading parameters (mode, csv columns to attributes mapping, e-mail report recipients), you can optionnaly provide an `application/json` file with those custom settings.

Both files are to be provided as body parts of the POST call with `multipart/form-data` content type.

Example of POST call with both zipped file and json parameters:

curl -X POST "https://api.actito.com/v4/entity/MyEntity/table/MyProspectsProfiles/import" \     -H "Authorization: Bearer eyJhbGciOiJSUzI1NiJ9.eyJqdGkiOiI0IiwiaWF0IjoxNTg2ODY1MjE1LCJpc3MiOiJhY3RpdG8iLCJleHAiOjE1ODY4NjYxMTUsImxpY2VuY2VJZCI6MTI3NDAsImFjY291bnRJZCI6MTIsInBvbCI6Im1haW5Qb2xpY3lBcGkiLCJzdWIiOiI2ZWY3YjZmYS0wYTc1LTQ1YTYtYmE5My1iZGY5MmUyZjg3NDAifQ.umizXm0TueN6jRkMCaz9AnQP30qNxud5XIxnZiPzz24L8Aon7WKeJ8_49xcjsTe_v13nv4AI9991Mw_k9bvQffT__eikkv9UMmZ22wvQr5UxCH5Y-NkxFRctEGLjmkEdFFe2EuOkF1GjsIetPrJgY-_L6bpoa3G0o69IWavBIFowQtw_q0FOPaZ_JtBLiDiFH59IM5s4-8S-QAhGkGgjOhTzqBTyDBGj8cqnhvr9eFwgoxGSAZ1QLGU5yTRyIJm8Uaq97M5UhKn98ixK4oQhQvVKwW9MDgGyf0jLFLEFO7l9kyFON34OsxiTyK58U_OFJzehgxqRokBE3wXWo9rKEA"  \     -H "Accept: application/json" \   -H "Content-Type: multipart/form-data" \   -F "inputFile=@/yourfolder/profiles.zip;type=application/zip" \   -F "parameters={\"mode\" : \"CREATE_ONLY\", \"encoding\" : \"UTF-8\", \"format\" : { \"separator\" : \";\", \"endOfLine\" : \"\r\n\", \"enclosing\" : \"\\\"\", \"escaping\" : \"\\\"\" }, \"reportRecipients\" : [ \"john.smith@actito.com\", \"jane.smith@actito.com\" ], \"attributesMapping\" : [ { \"headerColumn\" : \"AdresseEmail\", \"attributeName\" : \"emailAddress\" } ] };type=application/json"

Multi-value attributes

Multi-value attributes always use the separator , between the different values.
If there are multi-value attributes in your bulk import, make sure to use another character as main separator between columns.
, is never allowed in the values of the multi-value attributes.

When posting a new bulk import request, if everything is OK, you should receive a JSON response containing the technical id of the created import:

{  "id": 123456789,  "name": ""}

Checking the result#

As bulk imports are asynchronous the API also allows you to check your import's status and potential error causes.

Import status#

The following operation provides you with the status of an existing import. An import status should be RUNNING or FINISHED.

GET /entity/MyEntity/import/123456789

Detailed result#

When an import is FINISHED, you can retrieve a detailed result to check how the parsing and loading procees went.

A successful import will return a result like this:

{  "entityId": 1,  "tableId": "1",  "synchroId": 123456789,  "mode": "CREATE_UPDATE",  "startExecution": "2019-10-10 20:00:00",  "endExecution": "2019-10-10 21:00:00",  "withErrorFile": true,  "withErrors": false,  "errors": [],  "executionResult": {    "rowsRead": 100,    "rowsInError": 10,    "rowsWritten": 90  }}

Though the import creation API would directly return bad request errors when providing wrong import parameters, some errors should be only detected while processing the file to load.

Potential errors will be signaled with the following parameters:

"withErrorFile": Boolean showing whether there is an error file available to download. If set to "true", the file has been processed but some lines could not be imported because of a format or an integrity error. Even if all lines failed, it does not mean there was a global error.
"withErrors": Boolean stating if the import execution encountered a global error (linked to the global processing of the file such as a corrupted ZIP file or a CSV format problem etc.). The import could not be completed and no data has been processed at all.
"errors": This information will be only available if there is a global error that prevented the processing of the file. It will give the row or column where the error occurred. It may be one of the following codes:
- "INVALID_LINES": A row is invalid, for example because it has an extra column
- "DUPLICATE_HEADERS": There is a duplicate in the header names of the file

However, even if input file and import parameters are all correct, you can always have some processing errors regarding the data validation in Actito.

Retrieving the detailed errors file#

When the import result states that an error file is available, you can retrieve it by using the following operation:

GET /entity/MyEntity/import/123456789/errors

This call will return a compressed ZIP file containing a single CSV file that contains all the lines of the import input file for which an error was raised by Actito.
A line in this error file contains the same data than the corresponding line in the input file, but with two more columns containing the error code, and the reason of the error.
An error shall be raised when a column linked to mandatory attribute is empty, when a value in a column is not compatible with the definition of the attribute the column is linked to etc.

You are done!

You are now used to feeding the Actito profile tables of your licence.

Data initialization#

The very first import you'll do in your profile table after its creation will usually not be an automated one. Indeed, you will start by populating the table with your historical data in one big imports.

The initialization of the table is the opportunity to migrate data from your former marketing automation operator.

Actito has recency fields corresponding to each channel of its Marketing Activation platform (such as e-mail opening or click recency). This is the kind of data that you may have already from your previous solution and that you want to import in Actito.

For this reason, the following technical attributes can be imported in CREATE_ONLY mode with methods documented above:

lastMailOpeningMoment
lastMailClickMoment
lastMailTargetingMoment
lastMailActivityStatus
lastDevice
lastUserAgent
blacklistMoment
mobileTargetingRecency
mobileDeliveryRecency
goalReachedRecency
participationRecency

These fields can be added to the import, just like any attribute of the profile table.

tip

After the initialization, these fields are calculated automatically based on the interactions of the profiels with the communication channels. They can therefore not be updated for existing profiles and it is not possible to include these fields in an import in another mode than CREATE_ONLY.

info

All fields are DATETIME types corresponding to the interaction moment of the respective channel, except:

blacklistMoment: DATETIME when the profile reported an e-mail as SPAM
lastDevice: STRING corresponding to the last device with which the profile interacted with an e-mail
lastUserAgent: STRING corresponding to the last user agent with which the profile interacted with an e-mail
lastMailActivityStatus: STRING with possible values SENT, OPENED, CLICKED, ERROR, FILTERED_NO_EMAIL_ADDRESS, FILTERED_INVALID_EMAIL_ADDRESS, FILTERED_DUPLICATE_EMAIL_ADDRESS, FILTERED_EMAIL_SCORE_TOO_LOW, FILTERED_BLACKLISTED_BY_RECIPIENT, FILTERED_LONG_QUARANTINE, FILTERED_TEMPORARY_QUARANTINE, FILTERED_LOW_EMAIL_ACTIVITY, FILTERED_CATCH_ALL_DOMAIN, FILTERED_COMMERCIAL_PRESSURE, FILTERED_OTHER, BOUNCED_HARD_BOUNCE, BOUNCED_SOFT_BOUNCE, BOUNCED_AUTO_REPLY