Skip to main content

Feeding your Actito custom tables

Introduction#

As for managing profiles in your profile tables, if you want to create, update or delete records from Custom tables in your Actito database via our APIs, you'll find hereby how to do that.


For a full understanding of what is possible with Actito datamart data model management, you can visit the full documentation on Actito Data model.


Let's take a practical case

  • Whenever a customer makes a purchase on your online webshop, the data of that purchase are immediately recorded in your 'Orders' custom table.

  • As your customer have the possibility our profiles to cancel their order, you want to remove the record from your 'Orders' table if it happens.

  • As your customer make purchases every day in your physical stores, the data of those purchases are recorded in your 'Sales' custom table via a daily bulk import.

Step by Step#

  1. Create or update an Order
  2. Retrieve and then delete an Order
  3. Launch a bulk Sales import and check its result
Prerequisites
  • Make sure the custom table exists in your licence and that you know the entity it belongs to and possibly the profile table it is linked to (check with your marketeer).
  • Check that the structure of that custom 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 an order#

Whenever a customer makes a purchase on your online webshop, the data of that purchase are immediately recorded in your 'Orders' custom table.

When you need real-time reaction to data creation or update (for instance, triggering a scenario that will send a purchase confirmation e-mail), you can create or update custom table records with the 1-by-1 API.

To create or update a new order in the Orders table, use the operation:

POST /entity/MyEntity/customTable/Orders/record

The data that compose the order are to be provided as a JSON request body such as :

{  "properties": [    {      "name": "customerId",      "value": 25716    },    {      "name": "refShop",      "value": "5"    },    {      "name": "refProduct",      "value": 32    },    {      "name": "amount",      "value": 123    }  ]}

By default the route behavior is to create a new record, but you can specify that you wish Actito to check existence of the record by providing the allowUpdate=true value as a query param.

POST /entity/MyEntity/customTable/Orders/record?allowUpdate=true

In that case, Actito will try to find an existing record that matches the provided businessKey.
You have in consequence to provide that information in the JSON request body :

{  "businessKey": "0c4b98e9-70a4-4ecc-8cc7-44cba1a8f7e7",  "properties": [    {      "name": "customerId",      "value": 25716    },    {      "name": "refShop",      "value": "5"    },    {      "name": "refProduct",      "value": 32    },    {      "name": "amount",      "value": 123    }  ]}

If one matching record is found, it will be updated with the provided attribute values.

If not, a new one will be created.

In any of those case, as everything runs right, you'll receive a 200 OK http response with a JSON body that contains the businessKey of the created/updated record like this one: 

{  "businessKey": "0c4b98e9-70a4-4ecc-8cc7-44cba1a8f7e7"}

Note that if you provide a businessKey in the body, but specify allowUpdate=false in the request query, this should lead to a 409 Conflict if another record already exists with the same businessKey value.

Updating a record

You can also update a record by using this operation:

PUT /entity/MyEntity/customTable/Orders/record/0c4b98e9-70a4-4ecc-8cc7-44cba1a8f7e7

The route expect a record to exist for the provided businessKey, will not create one if no record found (if so, a 404 Not Found http response will be returned).

Step 2. Retrieve and then delete an order#

As your customer have the possibility our profiles to cancel their order, you want to remove the record from your 'Orders' table if it happens.

Retrieve a specific order record#

Before deleting an order, it might prove useful to retrieve information related to the concerned record (for example if you wish to display details of the order in your cancellation process).

You can retrieve data related to a single record by using following operation:

GET /entity/MyEntity/customTable/Orders/record/0c4b98e9-70a4-4ecc-8cc7-44cba1a8f7e7

As everything is OK, you receive a 200 OK http response with the following JSON response body that contains the requested business key and the properties of the order record:

{  "businessKey": "0c4b98e9-70a4-4ecc-8cc7-44cba1a8f7e7",  "properties": [    {      "name": "id",      "value": 31    },    {      "name": "customerId",      "value": 25716    },    {      "name": "refShop",      "value": "5"    },    {      "name": "refProduct",      "value": 32    },    {      "name": "amount",      "value": 123    },    {      "name": "updateMoment",      "value": "2019-02-25T17:26:44+01:00"    },    {      "name": "creationMoment",      "value": "2019-02-25T17:26:44+01:00"    }  ]}

Delete the order record#

Once confirmed, the cancellation should be applied by using this operation :

DELETE /entity/MyEntity/customTable/Orders/record/0c4b98e9-70a4-4ecc-8cc7-44cba1a8f7e7

If no order has been found for the provided businessKey, you’ll receive a 404 Not Found http response.

Otherwise, once deleted from the Orders table, you’ll receive a 200 OK http response with no body.

Use DELETE with caution...

Though deleting a custom table record through the Actito APIs is possible, please note that such a deletion has consequences. Check the usage of the corresponding data in your scenarios or in your campaigns.

Delete on cascade

If your custom table has Interaction or Linked Data capability, and if delete on cascade option has been defined in the table definition, deleting a profile in the profile table your custom table is linked to will result in deleting every record linked to this profile.

Step 3. Launch a bulk Sales import and check its result#

As your customer make purchases every day in your physical stores, the data of those purchases are recorded in your 'Sales' custom table via a daily bulk import.

As for profiles, Actito Integration Framework provides an asynchronous bulk data import API for Custom tables. Bulk API should be preferred for daily/weekly/monthly flows that don’t require immediate result or real-time based marketing action triggering.

In our case, as your physical stores send their daily sales to your system at the end of the day, it perfectly matches the use case of a daily mass import.

info

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

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 file must be a valid CSV
  • The file first line contains column headers
  • Every header must be mapped to the technical name of an attribute of the Sales custom table (Note that technical names are case sensitive)

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.

The response of a successful call will be the id of the one shot execution.

Launch a bulk import (v4 API)#

You can also use use the operation:

POST /entity/MyEntity/customTable/Sales/import

This call will require you to post a compressed ZIP file that contains a single CSV flat file with the sales data to be loaded in your MyProspectsProfiles profile table.

The CSV file format must fit a bunch of constraints:

  • The file must be a valid CSV
  • The file first line contains column headers
  • Every header matches the technical name of an attribute of the Sales custom table (Note that technical names are case sensitive)

Download an example file here.

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#

Asynchronous custom table bulk imports are managed exactly the same as for profile tables. In consequence, you can apply here the same process than in the profile tables data sync case, described in its 'Checking the result' section.

Define the sequence of your data table updates

Think about the logical dependencies between your tables. Depending on the relations specified in your tables definition, you should write data in a logical order. For example, you should first create/update your profiles, and then create the custom table records that are linked to them.

That's all!

You are now able to feed your whole Actito Data Model with any API mode provided by our Integration Framework!