Understanding the Different Types of Tables

Actito allows to build your data model based on the principle of a star model centered on profiles.

As central element of this model, the profile table is the first level of the data model.

To benefit from the extensive Actito data model, you will be able to combine different types of custom tables gravitating around this profile table.

The available additional table types are the following:

  • the Interaction Custom Table

  • the Linked Data Custom Table

  • the Repository Custom Table ('Look up' table)

Central level of the Data Model

Profile table

images/download/thumbnails/615293145/image2019-5-24_11-20-15.png

This is the main table in Actito. It is mandatory before creating any Custom Table.

It allows you to store contact information which will be used for your marketing communications.

It contains:

  • the personal data of your customers/members/donors/prospects/patients/... (name, surname, sex, language, date of birth ...)

  • the data related to contact information (e-mail address, telephone number, postal address, ...)

  • the subscription data (opt-in)

  • the segmentation data

For more information about profile tables, we invite you to read the chapter on 'Profiles'.

Peripheral level of the Data Model

Interaction Custom Table

images/download/thumbnails/615293145/image2019-5-24_11-20-57.png

Use:

  • This table type allows you to store ‘one-off’ data which will be recorded each time a profile interacts with your business. When this happens, a new 'interaction' will be created in this table.

Example of data to be stored via this type of Custom Table:

  • A purchase, a quote request, an order, an order line, an abandoned cart, a sponsorship request, a catalog request, ...

Relationship with profile table:

  • N - 1 with the profile table: Several interactions can be recorded for the same profile

Further information:

  • Each interaction can be used in Actito to

    • Trigger a communication

    • Customize a communication

    • Target a population

  • At the targeting level, calculation operators will be available on this type of table

    • ‘Number of’: allow you to count the number of interactions

    • ‘Total of’: allows you to calculate the total of numerical attributes (for example, the 'amount' attribute of a 'purchase' interaction)

Representation in Actito

At a global level, an Interaction Custom Table is seen in the right zone of the ‘DataMart Studio’ application, which gives quick access to the ‘Manage Tables’ application. Clicking on this table will display the details of fields found in this table.

images/download/attachments/615293145/image2019-5-14_15-16-1.png

At the profile level, the interactions can be found in the 'interactions' tab of each profile. They are sorted by order of creation.

images/download/attachments/615293145/image2019-5-20_10-43-23.png

Usage frontiers

  • Maximum number of interactions per table: 10 millions

  • Maximum number of fields in a table: 40

Linked Data Custom Table

images/download/thumbnails/615293145/image2019-5-24_11-21-54.png

Use :

This type of table can store data in extension to the profile table.
In contrary to interaction tables, which are used to collect one-off events when they occur, the 'Linked data' table type is mainly used to collect general profile information that remain constant over time.

It is used to store data derived from other information, to simplify the use of these relevant data.

Another use is to store data related to a third party related to the profile.

Example of data to be stored via this type of Custom Table:

  • Derived data: An average basket by category, a purchase history over a period, the vehicles owned, the status of the loyalty account, ...

  • Third party related to profiles: Children, animals, godchildren, ...

Relationship with profile table:

  • 1 - 1 with the profile table: In the case of derived data, each profile is associated to its own aggregate, its own history

  • N - 1 with the profile table: In the case of third parties related to profiles, each profile can be associated to several third parties

Representation in Actito

At a global level, an Interaction Custom Table is seen in the right zone of the ‘DataMart Studio’ application, which gives quick access to the ‘Manage Tables’ application. Clicking on this table will display the details of fields found in this table.

images/download/attachments/615293145/image2019-5-20_11-20-27.png

At the profile level, linked data related to a profile can be found in the 'Linked data' of the profile file. Each table will have its own section, with the table name as title.

images/download/attachments/615293145/image2019-5-20_11-23-44.png

Usage frontiers

  • Number of interactions by table: 10 millions

  • Maximum number of fields in a table: 40

Repository Custom Table

images/download/thumbnails/615293145/image2019-5-24_11-23-19.png

Use:

This type of table stores repository data, which can be used through other tables (Profile Table or other Custom Tables). These repository data are therefore common to all or to some of the profiles.

Example of data stored via this type of Custom Table:
Repository of stores/sales point/shops/ hospitals/ ... (Address, opening hours, name of the person in charge, ...); repository of products/product categories/product classifications; repository of discounts; ...

Relationship with other tables:

  • N - 1 with the profile table or with the interaction table or profile linked data table: Each entry of a repository table can be associated to several profiles

Representation in Actito

At a global level, a Repository Custom Table can be found in the ‘DataMart Studio’ app, in relation to the table to which it is linked.

It appears among the fields that make up this table, whether it be profile attributes and custom table attributes.

images/download/attachments/615293145/image2019-5-20_11-36-18.png

A Repository table related to a profile table can be viewed in the profile table among its attributes.

images/download/attachments/615293145/image2019-6-5_16-45-36.png

Usage frontiers

  • Maximum storage of lines per table: 10 000

"Big" tables

The usage frontiers stated in the previous sections set the maximum number of lines at 10 million for interaction and linked data tables, and 10 000 lines for repository tables.

However, in some cases, it is possible to increase the table size to store 10 times more lines.

These tables have an additional capability that would allow for the maximum volume to be increased, always taking into account certain restrictions.

"Big" repository tables

The 10 000-line constraint for repository tables might not be very convenient when, for example, we want to create a list of products and there are more than 10 000 objects in our catalog.

By setting up a repository table with the "Repository" capability you will be able to exceed the limit. In return, there is only a minimum additional constraint related to the display name. In this way, you will not have to use an interaction table to store your product references, so that you won't have to update every interaction in case of modifying only one product reference; you will only have to update one line of your repository table.

In detail:

  • A repository table can store up to 100 000 lines.

  • It will not be possible to have a custom display name for the table values. In 'small' repository tables, the field "prettyNameExpression" allows you to create a composite display name made of several attributes (e.g. : "prettyNameExpression" = "${shopName} (${country} - ${town)"). In 'big' tables, the value of the businessKey will be displayed instead of the display name when creating a new line manually, or in targeting criteria.

In targeting criteria, remember that drop-down lists can only display the first 10 000 unique values of a 'cache-indexed' field. Therefore, if a 'cache-indexed' field of big repository table has more unique values (for example, "productCode"), you may not have a full dropdown list available. However, in such case, it would be quite unusual anyway that the user tries to define the target by heart and not by copying and pasting the product reference numbers.

On the other hand, 'cache-indexed' fields of a big repository table with less than 10 000 unique values (for example, a "status" with 5 possible values) can still be displayed as a dropdown list in a "big table".

Setting up a "big" repository table

For a repository table to be considered "big" and store more than 10 000 lines, it is necessary to remove the capability «LookUpTable» and use the capability «Repository» instead.

The following JSON file is an example of the structure for a "big" repository table.

Exemple de table Repository
{
"userName": "Produits",
"technicalName": "Product",
"businessKey": "ProductId",
"autoGeneratedBusinessKey": null,
"description": "null",
"creationTimeColumn": null,
"entityName": "entityName",
"prettyNameExpression": null,
"layout": {
"list": null,
"details": null
},
"emails": [],
"capabilityDTOs": [
{
"name": "Repository"
}
],
"defaultFetchingParameters": [],
"links": [ ],
"tableEventDefinitions": [],
"fields": [
{
"objectType": "SIMPLE",
"fieldName": "name",
"columnName": "name",
"userName": "Nom",
"required": false,
"unique": false,
"extended": false,
"indexed": false,
"cacheIndexed": false,
"description": null,
"type": "STRING",
"possibleValues": [],
"valueDisplayer": null,
"typeValidator": {
"objectType": "STRING_LENGTH",
"minCharacters": 0,
"maxCharacters": 50
}
},
{
"objectType": "SIMPLE",
"fieldName": "sku",
"columnName": "sku",
"userName": "SKU",
"required": false,
"unique": false,
"extended": false,
"indexed": false,
"cacheIndexed": false,
"description": null,
"type": "STRING",
"possibleValues": [],
"valueDisplayer": null,
"typeValidator": {
"objectType": "STRING_LENGTH",
"minCharacters": 0,
"maxCharacters": 100
}
},
{
"objectType": "SIMPLE",
"fieldName": "color",
"columnName": "color",
"userName": "Couleur",
"required": false,
"unique": false,
"extended": false,
"indexed": false,
"cacheIndexed": false,
"description": null,
"type": "STRING",
"possibleValues": [],
"valueDisplayer": null,
"typeValidator": {
"objectType": "STRING_LENGTH",
"minCharacters": 0,
"maxCharacters": 20
}
},
{
"objectType": "SIMPLE",
"fieldName": "size",
"columnName": "size",
"userName": "Taille",
"required": false,
"unique": false,
"extended": false,
"indexed": false,
"cacheIndexed": false,
"description": null,
"type": "STRING",
"possibleValues": [],
"valueDisplayer": null,
"typeValidator": {
"objectType": "STRING_LENGTH",
"minCharacters": 0,
"maxCharacters": 10
}
},
{
"objectType": "SIMPLE",
"fieldName": "unitPrice",
"columnName": "unitPrice",
"userName": "Prix unitaire",
"required": false,
"unique": false,
"extended": false,
"indexed": false,
"cacheIndexed": false,
"description": null,
"type": "NUMERIC",
"possibleValues": [],
"valueDisplayer": null,
"typeValidator": null
},
{
"objectType": "SIMPLE",
"fieldName": "productId",
"columnName": "productId",
"userName": "Id",
"required": true,
"unique": true,
"extended": false,
"indexed": false,
"cacheIndexed": false,
"description": null,
"type": "STRING",
"possibleValues": [],
"valueDisplayer": null,
"typeValidator": {
"objectType": "STRING_LENGTH",
"minCharacters": 0,
"maxCharacters": 10
}
}
],
"rowCleaning": {
"cleanPredicate": null,
"fifoLimit": null,
"softLimit": false,
"hardLimit": false
},
"entityId": 11
}

Modifying an existing table

It is possible to modify an existing table to store more than 10 000 lines.

For that, you need to modify the table structure with a JSON file such as the following one. The table fields can keep the parameters for the drop-down list ("cacheIndexed": true), it is not necessary to modify it. However, it will not be taken into account and the drop-down list will not be available from the targeting module.

Rendre une table
{
"type": "nonStructural",
"capabilities": [
{
"name":"Repository"
}
]
}