Import

Agillic offers a variety of methods to import data to your instance. Make sure to read our best practices, and evaluate each import method, to make sure you are aware of their distinct advantages. If you want to use data in communications, but don’t see any use for it in a traditional data Agillic structure, see Contextual Data.

Here are links to the import methods available:

Manual Methods
File Import Service
Agillic API
ETL Service

Let us know if you would like to see other import methods and features by sending an email to product.feedback@agillic.com

Follow Supported Data Format

Your import files should follow the Supported Data Format to ensure that your imports are successfully imported into Agillic. By not following the format applicable to the data type you are trying to import, your imports will most likely fail, or at worst, you might end up importing data into the wrong data fields.

Use a Plain Text Format

By using a plain text format such as .csv or .txt, you minimize the possibility of your import files accidentally containing hidden formatting, which might result in your import files failing.

Check for Duplicates

Check that you do not have any duplicates in your import files. When updating data for existing recipients, make sure that the data is imported to the correct Recipient ID (such as a MEMBERID, ACCOUNTNUMBER etc.). This ensures that you don’t end up with duplicate recipients or an overall messy data structure.

Validate your Import Files

Check out How to Import via the UI to learn how to validate import files via the Agillic UI. We also recommend running your files through an external validator before importing.

Continuously Monitor Imports

Building a robust import integration is the first step to successful utilisation of the Agillic Platform. Monitoring the continuous operation of these imports is the next, and Agillic enables you to do just that. You can use our standard import notification feature to get import status of any import file via email. If you want to integrate into your own monitoring infrastructure, and/or are interested in more detailed attributes of import files, consider writing a Javascript Monitor Extension to have programmatic access to your Agillic imports, and the entire Javascript Extension framework.

Agillic Import UI

Regardless of how a file is imported to Agillic, it will appear in the Agillic Data Module Interface in the import log. This import log will display status and details about your imports, and also show any warnings or errors if present.

Contact your Agillic responsible to learn how to get access to the Agillic UI.

Contextual Data

There might be some data that you want to use in real-time, Agillic-sent communications, but do not need access to the data in Agillic’s normal data structures which can be used in target groups and other segmentation features. This is the primary reason Agillic built this new data format. Contextual Data exists only in the execution which you have sent it with–but otherwise takes the normal form of Person Data, One-to-Many Tables, etc.

There are certain freedoms (some, which also double as restrictions) that come with using Contextual Data:

Flexibility with how you format the Context’s data structure, as you are not beholden to using this data for conditions, and can tailor its structure to the communication instead.
Sending data at the same time as executing the communication via an API call.

If you want to use the data outside of a Context, you still must import the data to a traditional Data Structures.

Read more information here about how to setup Contextual Data. And for more information on how to configure using Contextual Data.

Manual Methods

Manual imports may occasionally be necessary when starting testing or doing one-time campaign updates which do not follow an automated import schedule.

See How to Import via the UI to learn more about importing via the Agillic UI.

Importing can also be done manually via WebDAV, and if you are familiar with any SFTP Clients, the File Import Service can also be utilized as a manual import tool.

File Import Service

The File Import Service supports both HTTP and SFTP protocols. Besides the protocol difference, note that callbacks, and other optional parameters (described later on) are supported by the HTTP method, but not by SFTP.

If you are importing large quantities of data, we highly recommend to zip import files before sending: both protocols support gzip and zip. Zipped folders are not supported. Note that zipped archives containing multiple files appear as the zip archive’s name in the import log.

How to enable File Import Service

In the Agillic UI, go to the Administration module, under ‘Integrations’ select API - File Import
Enable the File Import Service
Note the Developer Key and Secret for both instance, as you will need this for authentication while testing, and later when ready to go live.
If your Callback service requires authentication, provide the domain, authentication, and the full path.
Save the changes

The username used (*-stag or *-prod) determines if the file will be imported to your Staging or Production instance.

Remember to only use small dummy files when sending to Staging. Staging should not have any actual recipient data, and importing of large files is blocked.

By SFTP

To upload a file to the File Import Service via an SFTP connection, use the following hostname:

sftp://import-eu1.agillic.net port:2222

Use the login provided in the Agillic interface (API – File Import). Your import file(s) should be placed in the ‘import’ folder – files placed in the root folder will be rejected. As soon as the file is uploaded, it will be queued for import, and will be removed from the directory for processing. You can see the progress or status of the import to your Agillic instance, through the Agillic Import Interface.

File Import Service SFTP method does not have a hard upper limit on file size. However, we recommend an upper limit of ~200 Megabytes.

By HTTP

The File Import Service Http method uses a POST call to the below endpoint:

https://fileimport-eu1.agillic.net/v1/file/upload

In the header of the call, provide the Basic Authentication built with the Developer Key and Secret provided in the Agillic interface.

To attach the file to transfer, add a file key in the body of the call, typed as form-data. The value should match the path to the file you want imported.

Example:

file=c:/data_files/users_for_Agillic.csv

This API call is asynchronous, meaning we provide a 202 response confirming the file has been correctly transferred to Agillic. This does not describe not the status of the actual import.

File Import Service HTTP method has a hard upper limit on file size of 64 Megabytes

Optional parameters

The HTTP method supports optional parameters on the call made to Agillic, to allow more fine-tune operations and status monitoring of data imports.

Static Target Groups

If you want to import the recipients in a file directly to a Static Target Group you can use the stgname parameter. It is a String value containing the name of the existing static target group you want the file’s recipients to be included to.

Recipients uploaded to a Static Target Group are still added to the recipient database. So if they meet conditions, those recipients will also exist in normal target groups.

To be used with stgname, you can also add the stgreplace parameter, which is a boolean, defining if the recipients in the import should replace the previously present recipients present in the static target group. If this parameter is not present, the value is assumed false.

If using stgreplace=true and the imported file fails, the static target group will still be cleared. This is to safeguard against any unintended communications to recipients who should have been removed by a successful import.

An example call implementing these parameters:

https://fileimport-eu1.agillic.net/v1/file/upload?stgname=test_stg_name&stgreplace=true

Skip new recipients during import

Just like through the Import UI you have the option to skip new recipients when importing a file through SFTP or HTTP as well. This can be done by adding agskipnew_ in front of the file name.

agskipnew_filename.csv

Callback

Due to the asynchronous nature of the http method in the File Import Service, you can configure a callback using the query parameter callback. With a string value containing the callback service URL, the file service will alert an endpoint of the import’s final status.

An example call implementing this parameter:

https://fileimport-eu1.agillic.net/v1/file/upload?callback=https://agilliccandy.com/callbackservice

When the actual import is complete an API call is made to the callback service with a detailed report on the import. The JSON object will consist of the following fields:

Field	Description	Type
id	Import operation id	Text
filename	Internal name of a file in the system	Text
originalFilename	Original name of the file	Text
startTime	Import start time on solution	Number
endTime	Import end time on solution	Number
fileSize	Detected file size in bytes	Number
rowNumber	Detected number of rows	Number
importedRowNumber	Number of imported rows	Number
status	Import status	Text
type	Import type	Text
total_error	Total number of errors during import	Number
not_unique	Not unique errors found	Text
header_error	Header errors found	Text
not_unique_userid	Not unique errorId error found	Number
restriction_error	Restriction errors found	Text
general_info	General information message	Text
field_error	Import end time on solution	Number
not_unique_db	Uniqueness db storage error	Number

Example response:

Responses will either be “Successful”, “Successful with warnings”, or “Failed”. You can investigate the warnings inside the successful response JSON body. Likewise, you can see the error message in case of failure.

API

See our API section section to learn how to use Agillic APIs. Our APIs are recommended for integrations where real-time data transfer for singular recipients is necessary. For bulk transfer of data, see the below File Import Service.

You can also get your instance’s configured data structure by using our Discovery API. This can be especially helpful when making a connector to Agillic, and want detailed information on the instance’s data structures, types, and restrictions.

Like with the Discovery API, our API can also do more than import/export data, so make sure to check out the full extent of features there!

ETL

An ETL (Extract, Transform, Load) service allows you to import content from a URL source, transform it to a valid format and import it into Agillic. This can for instance be used to automatically import the content of an RSS feed as entries in a Global Data Table or a Promotion. Each ETL service can also be set up to run at a certain schedule to ensure the data is consistently updated.

The ETL service does not support loading data sources of more than 100MB in size.
Attempting to do so will result in an error.

Before you setup an ETL service, you should have located the Promotion or Global Data Table, you wish to import your items to. Note the name and the field names.

Note that some RSS feeds reject non-browser requests. Since we load RSS feeds programmatically, such RSS feeds cannot be used with the ETL Service.

How to create the ETL Service

In the Agillic UI, log into Staging and navigate the to the Administration module, under ‘Integrations’ select ETL.
Check the option for ‘Enabled Extract, Transform, Load service’ for Staging and/or Production.
Click the Plus icon to add a new ETL service.
Name the ETL service.
Enter a description (optional).
Enter the URL of the source to import data from.
Select the type of content.
Authenticate the call if needed. This is usually done with a token.
Select if the data should import to a Global Data Table or Promotion.
Select a schedule for how frequent the ETL service should run.
Save the ETL service.

Each ETL Service can be activated/deactivated by clicking the power button for the specific service (under Administration/Integration/ETL).

Create the ETL Transformation Script

To correctly convert the data into a valid format for Global Data Table or Promotion, a script is required. These scripts are coded in xsl and should convert the input data into a predefined format depending on the use for Global Data Table or Promotions.

Script for Promotion

The script for loading the URL source into a Promotion has the following tags to be aware of:

Tag Name	Description	Example value
promotion	Must be the root element – the name is not required to be anything specific, but “promotion” is recommended	Candy Offer
operation	All propositions inside this promotion will operate according to this identifier. Support values (and their behaviour) are: “insert”(create only), “update”(create or update), “set”(Update existing, delete if not provided), “delete”(delete). Default behavior is “insert”.	update
proposition	Does not have to be called “proposition” but have to be a child of the promotion element. Tag cannot be called “name”. Naming the proposition is recommended.	Chocolate Bar
targetGroup	The target group can be defined per proposition or globally. If the tag is empty or not provided the propositions are created/inserted/updated without the target group. If the tag is provided globally, all propositions get the same target group. If the target group does not exist, the proposition is skipped.	candyStoreCustomers
event	An event can be defined per proposition or globally. If the tag is empty or not provided the propositions are created/inserted/updated without the event. If the tag is provided globally, all propositions get the same event. If the event does not exist it will be created on the respective links.	Show on Facebook
startDate	Can be used to configure the period of time a proposition is active. In addition to specifying particular dates in the `yyyy-MM-dd` format you can also give relative days (relative to today). The format there is to write ‘+7’ for seven days in the future and ‘-3’ for three days in the past. For today write ‘0’.	-7
endDate		2018-01-31
inactive	Allows you to specify if the proposition should be inactive from the start. This can be useful since the proposition is related without rules and therefore inactive will allow you to ensure they are not used until a manual step has been done. The tag is only relevant in case the value is set to false – otherwise it’s ignored.	True

It is not mandatory to use CDATA, but recommended, especially if the content contains HTML tags.

Example – Promotion

For source of the URL looking formatted like:

<section class="language-xml">
    <script type="text/plain">
        <?xml version="1.0" encoding="UTF-8"?>
        <?xml-stylesheet type="text/xsl" href="CANDY_transformation.xsl"?>
        <root>
            <title>CandyShop</title>
            <description><![CDATA[Candy Shop top products]]></description>
            <generator>RSS for Node</generator>
            <lastBuildDate>Fri, 01 Dec 2017 08:49:35 GMT</lastBuildDate>
            <copyright><![CDATA[Copyright: (C) Candy Shop.]]></copyright>
            <language><![CDATA[en-gb]]></language>
            <ttl>15</ttl>
            <!--<row>-->
            <!--<ID>PRODUCT.ID</ID>-->
            <!--<CATEGORY> PRODUCT.CATEGORY</CATEGORY>-->
            <!--<NAME> PRODUCT.NAME</NAME>-->
            <!--<IMAGE> PRODUCT.IMAGE</IMAGE>-->
            <!--<DESCRIPTION> PRODUCT.DESCRIPTION</DESCRIPTION>-->
            <!--<RANK> PRODUCT.RANK</RANK>-->
            <!--</row>-->
            <row>
                <ID>1</ID>
                <CATEGORY>Fruity</CATEGORY>
                <NAME>Fruity Bubble Gum</NAME>
                <IMAGE>http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/1.png</IMAGE>
                <DESCRIPTION>The sweet and fruity Bubble Gum will give you a long-lasting taste of fruity goodness that is sure to POP!</DESCRIPTION>
                <RANK>1</RANK>
                <candyurl>http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/candy=candyId%3DCDI-111</candyurl>
                <expdate>11.11.2017 00:00:00</expdate>
                <CreatedDate>03.11.2017 00:00:00</CreatedDate>
                <UpdateDate>03.12.2017 00:20:00</UpdateDate>
            </row>
            <row>
                <ID>2</ID>
                <CATEGORY>Caramel</CATEGORY>
                <NAME>Caramel Butter Scotch</NAME>
                <IMAGE>http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/2.png</IMAGE>
                <DESCRIPTION>This family favorite classic is sure to bring back sweet memories, indulging in chewy caramel delights!</DESCRIPTION>
                <RANK>2</RANK>
                <candyurl>http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/candy=candyId%3DCDI-112</candyurl>
                <expdate>03.12.2017 00:00:00</expdate>
                <CreatedDate>03.11.2017 00:00:00</CreatedDate>
                <UpdateDate>03.12.2017 00:20:00</UpdateDate>
            </row>
            <row>
                <ID>3</ID>
                <CATEGORY>Fruity</CATEGORY>
                <NAME>Fruity Candy Cane</NAME>
                <IMAGE>http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/3.png</IMAGE>
                <DESCRIPTION>An oldie but goldie on the candy scene, this iconic pepperminty sweet treat is a symbol of our favorite thing: Candy!</DESCRIPTION>
                <RANK>2</RANK>
                <candyurl>http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/candy=candyId%3DCDI-113</candyurl>
                <expdate>05.12.2017 02:00:00</expdate>
                <CreatedDate>03.11.2017 00:00:00</CreatedDate>
                <UpdateDate>03.12.2017 00:20:00</UpdateDate>
            </row>
        </root>
    </script>
</section>

Transformation script could look like:

The finished format would then transfer the data to correctly be imported into a Promotion like:

Script for Global Data Table

The script for loading the URL source into a Global Data Table has the following tags to be aware of:

Tag Name	Description	Example value
table	Must be the root element – specific name is not required, but “table” is recommended.
name	For the table you must provide either a name attribute on the tag or a sub-tag called “name”. Agillic first checks for the attribute and then the name.	PRODUCTS
primary	You must provide either a “primary” attribute or sub-tag. This holds the name of the primary field of the Global Data Table. Please ensure the casing in the attribute matches the casing in the field names in the xml. You do not have to make it uppercase, as this is done by default.	ID
separator	An optional “separator” attribute or sub-tag. This defines which separator is used in the system. Default is (;)	,
item	Item tag does not have to be called “item” but have to be children of the table element.	fields
fields	The tags below should be named like the fields configured in the table. Agillic will collect the full list of all fields listed in at least one item. If an item does not have a field present in another item the value will be blank.	CATEGORY

It is not mandatory to use CDATA, but greatly recommended especially if the content contains HTML tags.

Example – Global Data Table

The following example assumes the source of the URL has a format like:

The Transformation script could look like:

The finished format would then transfer the data to correctly be imported into a Global Data Table such as:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="CANDY_transformation.xsl"?>
<table>
    <name>PRODUCT</name>
    <primary>ID</primary>
    <item>
        <fields>
            <CREATEDDATE>
                <![CDATA[2017-11-03]]>
            </CREATEDDATE>
            <UPDATEDDATE>
                <![CDATA[2017-12-03]]>
            </UPDATEDDATE>
            <ID>
                <![CDATA[1]]>
            </ID>
            <CATEGORY>
                <![CDATA[Fruity]]>
            </CATEGORY>
            <NAME>
                <![CDATA[Fruity Bubble Gum]]>
            </NAME>
            <IMAGE>
                <![CDATA[<img decoding="async" src="http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/1.png" width="300" alt="image">]]>
            </IMAGE>
            <DESCRIPTION>
                <![CDATA[The sweet and fruity Bubble Gum will give you a long-lasting taste of fruity goodness that is sure to POP!]]>
            </DESCRIPTION>
            <URL>
                <![CDATA[<a href="http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/candy=candyId%3DCDI-111">Read more</a>]]>
            </URL>
        </fields>
    </item>
    <item>
        <fields>
            <CREATEDDATE>
                <![CDATA[2017-11-03]]>
            </CREATEDDATE>
            <UPDATEDDATE>
                <![CDATA[2017-12-03]]>
            </UPDATEDDATE>
            <ID>
                <![CDATA[2]]>
            </ID>
            <CATEGORY>
                <![CDATA[Caramel]]>
            </CATEGORY>
            <NAME>
                <![CDATA[Caramel Butter Scotch]]>
            </NAME>
            <IMAGE>
                <![CDATA[<img decoding="async" src="http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/2.png" width="300" alt="image">]]>
            </IMAGE>
            <DESCRIPTION>
                <![CDATA[This family favorite classic is sure to bring back sweet memories, indulging in chewy caramel delights!]]>
            </DESCRIPTION>
            <URL>
                <![CDATA[<a href="http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/candy=candyId%3DCDI-112">Read more</a>]]>
            </URL>
        </fields>
    </item>
    <item>
        <fields>
            <CREATEDDATE>
                <![CDATA[2017-11-03]]>
            </CREATEDDATE>
            <UPDATEDDATE>
                <![CDATA[2017-12-03]]>
            </UPDATEDDATE>
            <ID>
                <![CDATA[3]]>
            </ID>
            <CATEGORY>
                <![CDATA[Fruity]]>
            </CATEGORY>
            <NAME>
                <![CDATA[Fruity Candy Cane]]>
            </NAME>
            <IMAGE>
                <![CDATA[<img decoding="async" src="http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/3.png" width="300" alt="image">]]>
            </IMAGE>
            <DESCRIPTION>
                <![CDATA[An oldie but goldie on the candy scene, this iconic pepperminty sweet treat is a symbol of our favorite thing: Candy!]]>
            </DESCRIPTION>
            <URL>
                <![CDATA[<a href="http://training.agilliclabs.com/web/bcmresource/media/resources/images/Products/candy=candyId%3DCDI-113">Read more</a>]]>
            </URL>
        </fields>
    </item>
</table>