Scheduled customer data import

Customer data import can be automated using scheduled Database import. The import module takes data from external data tables. To configure automatic data collection, you need to configure SQL connector in admin panel and bind it to your account. In your account, to proceed to import creation, go to Data —> Database Imports.

You can also go to this section from the list of databases by choosing "Import customer profiles" in the context menu on the right:

General settings

In general settings of import, you can set import name, assign access groups and labels and set how the import will process data in the platform account:

1. Database — import will add or update fields in the selected database of customer profiles. The set of available fields for storing information depends on the database.
2. Database import mode — import can work in three modes:
   • Add new leads and update existing ones
   • Add new leads, do not update existing ones
   • Update only
3. Profiles matching mode — You can search by contact data, subscription data (including Push), by profile ID to update existing profiles — and by custom database field.
4. Matching field — if you search for profiles by custom field of the database, you need to select it in this menu.

For scheduled import IP location is also available. If you have configured trigger mailings to import or update profiles, you can restrict their triggering for scheduled import.

In the main settings there is an option to instantly start import in addition to the schedule. If this item is checked, after clicking the Apply or Save button, the import will be launched.

Database import schedule

You can flexibly configure schedule for regular import. Choose schedule plan from the menu and configure it depending on your import purpose.

tip

Mailing schedule is described in detail on this page.

For each schedule plan, you can set the time zone for the calculation. You can also set import deactivation date:

Database import settings

Choose the necessary database connector (configured in administrative panel) in database import settings and the data table to take data from:

Create Query body depending on DB management system you use: MySQL, PostgreSQL, MSSQL, Oracle, Hive or ClickHouse. SELECT operator is necessary on your queries. There are no any other restrictions.

Prevent duplication

These functions are available to prevent repetative data update:

• Delete table — deletes all records in data table

• Drop table — deletes table from database

• Remove row — if the value of the specified column in the record from the table and in the profile in the database coincides, such record will be deleted.

• Update row —

  updates values in selected column for imported records. It identifies records by Matching column ( unique for every customer profile and differs from profile search column) and updates data in Update column.

Splitting import into parts

When you import big amount of records, you can divide this process, to distribute loading. To do this, you need to add {lastItem} variable into Query body for the column with unique ID. It is recommended to sort IDs when making a query.

Query example: SELECT * from TestTable where id>{lastItem} LIMIT 10

Next, you need to specify from which row in the table you want to start the first import. To do this, select the column with the ID in Next import start column and specify Field value.

An example of settings for starting import from the first row (if the data in the id column is sorted in ascending order):

After starting a regular import, Field value will be automatically updated — it will be filled with the last ID after the previous run.

Column mapping

In this block, you need to determine the correspondence of profile fields in the Altcraft database and columns of the external SQL database. After clicking on the Add column button on the left of the drop-down menu, select Altcraft profile field for updating or recording data.Dur

When you choose column of external SQL database, its first value will be shown to the right as gray text, like in example below:

Field import specifics

When importing data into the system, the platform expects specific value formats for each field type. All valid formats are described in this article. Make sure the data in your source matches these requirements, otherwise the platform will not be able to process it correctly.

For Object and Array of Objects fields, the platform expects the value to be a string containing valid JSON. To set this value, you need to first convert the JSON object into a string and then escape it according to the CSV rules described in RFC 4180 (double quotes " are replaced with "").

Array of objects example:

[
    {
        "field1": 123,
        "field2": {"subfield": false},
        "field3": "abc"
    }
]

Expected value: "[{""field1"": 123, ""field2"": {""subfield"": false}, ""field3"": ""abc""}]"

For Enum fields, you can import either the values themselves or their indexes. For example, if you have created a enum list in the database with options "tariff_1", "tariff_2", "tariff_3", you can specify the same values in the source column or use the indexes: 1 — "tariff_1", 2 — "tariff_2", 3 — "tariff_3", 0 — "Not selected" (default value).

Please note that values consisting only of digits cannot be imported directly — the platform will treat them as indexes. For example, if the list contains the values 100 and 200, you need to import the corresponding indexes: 1 — 100, 2 — 200.

Subscription settings

During import, you can automatically subscribe customers to certain resources - through one or more channels. For every subscription you need to select a resource, communication channel and select column in the data table from which contact data for subscriptions will be imported.

You can set status for every subscription:

• During import, you can give "Subscribed" status for Single opt-in, or "Unconfirmed" status for Double opt-in logics.

• During data update, you can change subscription status depending on user actions, like suspending or unsubscribing

Database imports report

In this block, you can see information about last 10 imports. If any errors occurred during last import, you can load report about them.