Scheduled Imports

Note: This is an On Demand feature. If you would like more information about this feature, contact your account manager at account.management@adestra.com.

Adestra allows users to automate data imports to use with campaigns and reports, helping to provide accurate and seamless data transfers. This documentation will take you through the requirements necessary to apply automated imports to your account.

On this page:

Assumptions
Data Format
Alternative Data Formats
Data Files
Data File Content
Data File Transfer and Security
Data File Encryption
Data Update Process Example
Contact Data Deltas
List Removals
Ready to use the Import Manager?

Assumptions

Mailing list data is completely free from duplicate email addresses
Primary keys or unique record identifiers either exist, or can be added for each contact record
Data can be provided in a specific format
Contact records that have been added or updated in a particular timeframe can be identified
The mailing list is comprised of either a single monolithic list, or several separate, but similarly-formatted lists

Data Format

In order to automate imports we recommend data be provided in a standard tab-delimited format with one record per line. The file should be encoded in ASCII, Latin-1 or UTF-8 format. Windows and UNIX line terminators are both acceptable. The first line of each file should contain a header row consisting of the names of each column, in order, each delimited with a single tab character. Adestra does not support 'null' values so these fields must remain blank.

The import manager will assume that these files are placed into a compressed file format—supported types are "zip", "tar.gz", "gzip".

If you are encrypting your data, the import manager will assume you have encrypted the compressed file. The files contained inside the compressed file should not be encrypted. E.g., "automated_import.zip.gpg" would contain two files, "contacts.csv" and "list_assignment.csv".

Columns

The files should contain a header row containing the column title; individually named using 1-50 characters beginning with an alphabetical character. The remainder can contain a combination of alphanumeric, underscore, dash, dot and space characters. column names are not case-sensitive and do not need exactly match how you would like them to appear in the Adestra interface, as they can be remapped.

Rows

Each data row should consist of a number of fields, separated by a single tab character, the number of fields in each row should exactly match the number of columns in the header row. Each field can contain between 0 and 255 characters, with no line feeds, carriage returns, tabs or any other non-printable characters.

File names

If you require datestamps to be placed into your filenames, use the following format—YYYY-MM-DD.

Alternative data formats

We recommend tab delimited formatted data for automated data transfer. It is a simple, easily implemented, human-readable data format supported by the majority of databases, CRM systems and data processing tools.

However, we can accept data in CSV format, or similar single-character delimited formats if necessary.

Data Files

File Deltas

Rather than sending a complete copy of the entire mailing list every day, we recommend that delta update files are employed. After the initial full copy of data has been imported into Adestra, each consecutive file should contain only the records that have changed since the last file was sent. This ensures that the transfer and update process will continue to scale with your mailing list size.

If this cannot be provided, then if possible a timestamp of the last update could be added to each record, then we will be able to automatically exclude unchanged data from the import process.

Duplicate Records

To ensure accurate reporting and to preserve data integrity, we recommend all mailing list data is free from duplicate data before beginning this process. Please note that although the contact data file should contain no duplicate records, this does not prevent each record from appearing on several lists.

We also recommend that preventative measures are implemented to prevent duplicate records appearing at a later date, either by new records being added or existing records being updated. Most database systems are capable to providing this functionality in the form of a 'unique key' or index.

Record Identifiers

Each individual contact should be assigned a single, unique record identifier. Database primary keys are usually suitable for this purpose, although creating a unique identifier is just as suitable. If the mailing list has been successfully cleaned of duplicate records, then it is possible to use the individual's email address for this purpose.

This identifier is used in order to facilitate accurate communication between the two systems. Record identifiers are critical if duplicate records do exist in the mailing list data, or if there is no provision to prevent duplicate records from being created at a later date.

Data with duplicate records

Although we do not recommend it, it is possible for us to handle and compensate for mailing list data that contains duplicate records, as long as each record has been assigned a unique identifier. During the data import process, Adestra can automatically remove duplicates as they appear, although this will affect overall data integrity.

Data File Content

Data should be separated into two different classes of file:

The contact data file contains the data for each individual on the mailing list.
The list assignment file contains information used to assign or remove each individual to one or more lists within Adestra.

Contact Data File

This is a single delimited data file in the format specified in the 'Data Format' section which, at a minimum, should contain an 'email' column to correspond to the individual's email address in the body of the data. Although we are theoretically capable of handling data files with any number of columns, in practice, we recommend a limit of 50 columns. This contact data file will be imported into a "coretable" within your Adestra account and used to provide data to the individual lists.

List Assignment File

In each list assignment file must be the following columns, in the following order:

Contact—This column should contain the record identifier.
List—This column should contain an identifier that can be specified during the list creation process within Adestra, using up to 50 alphanumeric or underscore characters.
Add—This column is used to denote whether the record is to be added to, or removed from the list. This should contain either 'y', '1' or 't' to represent a record to be added to the list, and either 'n', '0' or 'f' to represent a record to be removed from the list.

The headers for these columns do not have to be named as above, although must remain in order of Contact, List and Add.

List Data Tables

List data tables are a variation of normal data tables, the difference being that data imported into a list data table is only available in a single list.

When automating an import, in order to import list data it must be in the list assignment file.

Data can be added to the list assignment file by inserting extra columns to the right of the 'Add' column. The column header must be prefixed with the id of the list data table, followed by the field name. For example;

contact	list	add	7.voucher_code
1000	list_a	y	333
1001	list_a	y	333
1001	list_b	y	444
1002	list_b	y	444

For more information, refer to the List Data Tables topic. And for more information how the files are designed to operate and how they interact with the Contact Data file, see the Data Update Process Example section of this topic.

Data File Transfer and Security

We can support either data being pushed from a remote site into our servers, or we can pull data from a remote location automatically. We support the following data transfer methods:

For files pushed to Adestra:

FTP
SFTP

For files pulled by Adestra from a remote site:

FTP
SFTP
HTTP
HTTPS
SCP

If you have a preference for a transfer method not mentioned, we may still be able to support it on request.

Data File Encryption

We are able to handle files encrypted only with PGP or GnuPG, which provides a strong, public-key based encryption mechanism allowing data to be encrypted such that only we can decrypt it. Our public key is available below.

Caution: We do not support other forms of file encryption.

Public Key

Our current public key is below. This is a 4096-bit RSA key.

Copy

Data Update Process Example

Contact Data and Lists

The following contains an example of how a Contact Data file and a List Assignment file can work together to create and manage many individual lists.

Contact Data File

identifier	email	forename
1000	todd@example.com	Todd
1001	brad@example.com
1002	zach@example.com	Zachary

List Assignment File

contact	list	add
1000	list_a	y
1001	list_a	y
1001	list_b	y
1002	list_b	y

After import, the Contact data file will be copied directly into the Adestra "core table". Two lists, 'list_a' and 'list_b' will be populated using the information in the List Assignment file, and contain two records each.

Below is an example of how the two lists will appear in Adestra after the example files above have been imported:

list_a

identifier	email	forename
1000	todd@example.com	Todd
1001	brad@example.com

list_b

identifier	email	forename
1001	brad@example.com
1002	zach@example.com	Zachary

Notice that although record 1001 ('brad@example.com') appears in both lists, that record appears only once in the underlying "core table".

Contact Data Deltas

Each record in the Adestra "core table" can be updated at a later date by sending a new Contact Data file containing a matching identifier. In this example, the 'forename' column has been altered to include a value which was previously empty:

Updated Contact Data File

identifier	email	forename
1001	brad@example.com	Bradley

Because each record is stored only once and each list references the stored record, both lists will have access to the updated forename column and will appear as below:

list_a

identifier	email	forename
1000	todd@example.com	Todd
1001	brad@example.com	Bradley

list_b

identifier	email	forename
1001	brad@example.com	Bradley
1002	zach@example.com	Zachary

This process allows data to be updated accurately, with each list benefiting from new data about each individual. It also ensures complete data integrity at all times, increases the effectiveness of your reporting and allows accurate 'single contact' reports.

List Removals

Just as records in the Contact Data file can be updated at a later date, lists can also. Here is an example of a List Assignment Delta file that will remove a record from a list:

List Assignment file delta

contact	list	add
1001	list_b	n

This list assignment delta will remove the contact record with the identifier 1001 from list_b. After this file has been imported, list_b will appear as below:

list_b

identifier	email	forename
1002	zach@example.com	Zachary

The record still exists in the "core table", and list_a still refers to it: list_a will appear as:

list_a

identifier	email	forename
1000	todd@example.com	Todd
1001	brad@example.com	Bradley

Ready to use the Import Manager?

When you are ready to use the import manager, contact your account manager with the following information so that they can arrange for your import to be set up. Please be aware a setup charge may apply.

Information required:

The names of your files.
The time of day that you'll be uploading your data to an FTP.
The name of the field you want to de-duplicate your data on. See Duplicate Records section above.
Whether or not you'd like to refresh lists on each import. I.e., empty your list and then import your data.
Any email addresses you'd like to include on the Import Complete/Failure? notifications.

Please also include any other information you feel relevant.