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 /profiles/v4/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 /profiles/v4/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 /profiles/v4/entity/MyEntity/table/MyWebsiteProfiles/profile/1147690/subscription/Newsletter

Unsubscribe the profile of subscription Newsletter:

DELETE /profiles/v4/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 /profiles/v4/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. Mass import profiles with an ETL execution launched with an API call

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.

There are 2 ways to push data in bulk through an API call: a one shot ETL execution or an ETL triggered by API.

A one shot ETL execution means that you are providing the definition of the ETL and the source data in the same single call. It does not require preliminary work and is highly flexible, because all the parameters (destination table, import mode, transformations, output,...) are provided on the go. It is mostly recommended if these parameters are variable and depend on upstream elements in your stack (ex.: the report recipients are not always the same one day to another and depend on the source of the data). Due to their nature, only finished one shot executions are visible in the user interface.
A triggered by API ETL means that you have already set-up a fixed definition ETL and you are pushing the data file through an API call. It is recommended for your recurring synchronizations where the parameters stay the same one day to another ( same destination table, import mode, transformations, output,...). Of course, if you have multiple sources of data with different definitions, you can set up one ETL for each definition or a multifiles ETL. These ETLs and their definition will be visible in the user interface, giving a snapshot of the expected data flows into the license.

info

Both options allow to make several imports per day.

The base limit is 12 bulk imports per table per day.

Launch a one shot ETL execution

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

POST /mass-imports/v5/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/mass-imports/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/mass-imports/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.

Launch a 'triggered by API' ETL execution

To be able to trigger a recurring ETL through an API call, you first need to set up a TRIGGERED ETL. This is ideal if the definition of the ETL remains fixed: same destination table(s), import mode, transformations, attribute mapping, etc, so that your API call only contains the data file.

tip

If you need to synchronize data into multiple tables at the same time (ex. a "customer" profile table and a related "orders" interaction table), you do not need to define multiple ETLs!
Thanks to the files array, you can set up a 'multifiles' ETL (which is not possible through one shot ETL executions), and provide the files in a ZIP archive in your call. This also guarantees that the imports are processed in sequence.

Defining a 'triggered by API' ETL

Defining the ETL is done through the following operation:

POST https://api3.actito.com/mass-imports/v5/entities/***MyEntity***/etls

{
  "type": "TRIGGERED",
  "name": "profiles-and-orders-triggered-ETL",
  "description": "The integration of profiles and orders from physical stores all along the day",
  "triggering": {
    "type": "API",
    "paused": false
  },
  "reportRecipients": [
    "john.smith@actito.com"
  ],
  "fileTransfer": {
    "input": {
      "files": [
        {
          "fileCode": "profiles",
          "fileNamePattern": "profiles_$YYYYMMDD.csv",
          "compressionType": "ZIP",
          "compressedFileNamePattern": "ordergeneration_$YYYYMMDD.zip"
        },
        {
          "fileCode": "orders",
          "fileNamePattern": "orders.csv",
          "compressionType": "ZIP",
          "compressedFileNamePattern": "ordergeneration_$YYYYMMDD.zip"
        }
      ]
    },
    "output": {
      "location": {
        "type": "REMOTE",
        "remoteLocationId": "1"
      }
    }
  },
  "inputFilesProperties": [
    {
      "fileCode": "profiles",
      "csvFormat": {
        "encoding": "UTF-8",
        "separator": ";",
        "enclosing": "\""
      }
    },
    {
      "fileCode": "orders",
      "csvFormat": {
        "encoding": "UTF-8",
        "separator": ";",
        "enclosing": "\""
      }
    }
  ],
  "dataTransformations": [
    {
      "fileCode": "orders",
      "transformations": [
        {
          "header": "orderMoment",
          "transformation": {
            "type": "DATE_FORMAT",
            "format": "MM-dd-yyyy HH:mm:ss"
          }
        }
      ]
    }
  ],
  "dataLoadings": [
    {
      "fileCode": "profiles",
      "destination": {
        "type": "PROFILE_TABLE",
        "id": "1",
        "attributesMapping": [
          {
            "header": "eMail",
            "attributeName": "emailAddress",
            "ignoreEmptyValues": false,
            "ignoreInvalidValues": false,
            "ignoreValuesWhenAlreadyKnown": false,
            "mergeValuesWhenMultivalued": false
          }
        ]
      },
      "parameters": {
        "mode": "CREATE_OR_UPDATE",
        "generateResultFiles": false,
        "generateErrorFiles": true
      }
    },
    {
      "fileCode": "orders",
      "destination": {
        "type": "CUSTOM_TABLE",
        "id": "bezoeiodif-sdfsdf-dfs-sdfsdf",
        "attributesMapping": [
          {
            "header": "source",
            "attributeName": "formSource",
            "ignoreEmptyValues": false,
            "ignoreInvalidValues": false,
            "ignoreValuesWhenAlreadyKnown": false,
            "mergeValuesWhenMultivalued": false
          }
        ]
      },
      "parameters": {
        "mode": "CREATE_ONLY",
        "generateResultFiles": false,
        "generateErrorFiles": true
      }
    }
  ]
}

As response, you retrieve the id of the ETL.

Trigger an ETL execution by API

Once the ETL definition has been created, files matching the parameters can be pushed through the following operation:

POST /mass-imports/v5/entities/MyEntity/etls/MyEtlId/trigger-execution

While this call can be used to trigger the retrieval of a file from a FTP location, here we are mainly focusing on pushing the file in the body of the API call, as a multipart/form-date body schema.

Here is an example of call to directly push the source file by API.

curl --location --request POST 'https://api3.actito.com/mass-imports/v5/entities/MyEntity/etls/123456/trigger-execution' \
--header 'Authorization: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJJJJJJJJJJJJJJJJJJJJJJJJJ' \
--form 'inputFile=@\yourfolder\ordergeneration_20241029.zip' 

info

The fileNamePattern in the definition of the ETL is only relevant if you set up a "multifiles" ETL, like in the example above where several files are pushed in the same ZIP archive.
In the case of a "monofile" ETL, there is no validation on the name of the file.

Check the result of an ETL execution

Marketeers can receive a report directly by e-mail for both types of ETL executions. Technical users have 2 options to check this result:

setting up a webhook
retrieving the integration results by API

Setting up a webhook on ETL executions

You can set up a webhook to retrieve the results of an ETL execution as soon as it is finished.

POST https://api3.actito.com/webhooks/v4/entity/*MyEntity*/webhookSubscription

{
  "on": "ETL_EXECUTION",
  "onElementId": "123456",
  "eventType": "FINISHED",
  "targetUrl": "https://myactitowebhookendpoint.com/etlExecution",
  "headers": {
    "X-Authorization": "Lkjvlknqdjd54DOJF$"
  },
  "webhookPushType": "ONE_BY_ONE",
  "isActive": true
}

You can check payload examples for webhooks on ETL executions here.

Retrieving the integration results by API

You can use the following operation to retrieve the integration results:

GET /mass-imports/v5/entities/MyEntity/etl-executions/123456/integration-results

If the ETL encounters a global error ("status": FAILED ), the "error" parameter will state the reason of the issue. It can be:

the file is invalid, because of its format as a whole, or a row is invalid because it has the wrong number of columns (a column value might contain the unescaped separator, or a carriage return, for example).
the file contains duplicate headers
the file is missing (only when it is retrieved from a FTP location)

If the ETL is successful, the "integrationResults" parameter gives the number of lines read, in error, inserted, updated and deleted.

tip

The ETL status remains IN_PROGRESS until it is FINISHED or FAILED.
To avoid unnecessary polling, we advise you to set up a webhook, as explained above.

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.

/mass-imports/v5/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.

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 import.

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 profiles with the communication channels. They therefore cannot be updated for existing profiles and it is only possible to include these fields in an import in mode 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

Introduction​

Automated imports​

Step by Step​

Step 1. Create or update a single profile​

Step 2. Update a single profile subscriptions​

Step 3. Delete a single profile​

Step 4. Mass import profiles with an ETL execution launched with an API call​

Launch a one shot ETL execution​

Launch a 'triggered by API' ETL execution​

Defining a 'triggered by API' ETL​

Trigger an ETL execution by API​

Check the result of an ETL execution​

Setting up a webhook on ETL executions​

Retrieving the integration results by API​

Retrieve the result or error file​

Data initialization​

Introduction

Automated imports

Step by Step

Step 1. Create or update a single profile

Step 2. Update a single profile subscriptions

Step 3. Delete a single profile

Step 4. Mass import profiles with an ETL execution launched with an API call

Launch a one shot ETL execution

Launch a 'triggered by API' ETL execution

Defining a 'triggered by API' ETL

Trigger an ETL execution by API

Check the result of an ETL execution

Setting up a webhook on ETL executions

Retrieving the integration results by API

Retrieve the result or error file

Data initialization