Omnichannel Integration Guide via FTP/SFTP Files
Omnichannel integration through FTP/SFTP files allows for easy, agile processing of large volumes of data from any source platform, whether online or offline.
Based on the daily generation of 4 files, ICOMM can obtain relevant transactions (and their customers) for their business. By processing these files, over 75 indicators will be calculated daily and can be used in dynamic segmentation and automation processes.
The following 4 plain text files must be generated daily:
Customers: This file aims to contain the list of customers (and their marketing-relevant attributes) linked to the transactions reported in the Orders file, as well as any new or updated customers from any data sources managed by the organization. It is possible to include prospects who have not made a purchase yet. However, it is essential to send customers who have made purchases that are being reported in the other files.
Orders: This file aims to contain a summary of the billed transactions or purchase orders, reporting total amounts, quantities, and other relevant operation attributes.
OrderDetails: This file aims to report the composition of a transaction or purchase order - its products and/or services with their respective details or attributes.
OrderDetailCategories: This file aims to list categories that can be attributed to each of the products and/or services reported in the OrderDetails file.
Orders: ICOMMKT_ORDERS_[UNIQUE INSTANCE ID]_YYYYMMDD
OrderDetail: ICOMMKT_ORDERSDETAIL_[UNIQUE INSTANCE ID]_YYYYMMDD
OrderDetailCategories: ICOMMKT_ORDERSDETAILCATEGORIES_[UNIQUE INSTANCE ID]_YYYYMMDD
Customers: ICOMMKT_CUSTOMERS_[UNIQUE INSTANCE ID]_YYYYMMDD
[UNIQUE INSTANCE ID] should be an identifier relative to the instance, when multiple brands/countries/business units are represented in different instances of our platform.
The files must be generated daily, whether they contain information or not. When any of them do not contain data, only the headers line should be sent.
The User/Client ID in the source system where the file is generated. It can be any type of identifier, including the same EMAIL if the data originates from an e-commerce where the user does not have any other assigned ID.
It should match the ID reported in the orders file to link the customer with their transactions.
Type: CHAR(50)
The store code.
This field MUST be left empty when the organization has a unique user ID across all its stores, which has been unified in its CRM, ERP, or data source system. It should only be informed when a customer has different IDs for each store, where the store code originating this user/customer should be provided.
Type: CHAR(30)
Customer's email.
Type: CHAR(300)
Collection.
Any number of attributes can be informed according to the actions desired by the marketing department. Each of these attributes will later become a field in the profile (database) within the platform.
Type: JSON Key/Value,
Format: Key/Value in JSON format.
Example: {"Key":"FIRST_NAME","Value":"Juan"},{"Key":"LAST_NAME","Value":"Sanchez"}
The transaction or order number.
Type: CHAR(50)
The store code where the transaction or order originated.
Type: CHAR(30)
*The User/Client ID in the source system where the file is generated.
Type: CHAR(50)
Order date.
Type: DATE
Format: dd/mm/yyyy
Quantity of items in the order. It represents the total.
Type: DECIMAL(5,2)
Quantity of Products (different items) in the order. Quantity of different products of the transaction or order. Different SKUs or ITEM CODE, depending on the value reported in the ITEM CODE of the Details File.
Type: DECIMAL(5,2)
Total amount of the order.
Type: DECIMAL(18,2)
Total amount of the order items (Without tax)
Type: DECIMAL(10,2)
Amount of discounts applied to the order. Whether they are discount codes applied at the end of the operation or other quantity discounts.
Type: DECIMAL(10,2) ()
Shipping amount for the order.
Type: DECIMAL(10,2)
Tax amount for the order.
Type: DECIMAL(10,2)
Promotion codes applied.
Type: TEXT
Total amount of the Order without shipping cost. There must be a value greater than zero. It should equal TotalAmount minus ShippingAmount.
Type: DECIMAL(10,2)
Shipping Type (Ex: Standard, Express).
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3
Type: CHAR(50)
Carrier.
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3
Type: CHAR(50)
Carrier.
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3
Type: CHAR(80)
Payment method used for the order.
Type: CHAR(50)
Detail of the payment method (Ex: VISA).
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3.
Type: CHAR(80)
Entity of the payment method (Ex: Banxo XXX).
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3.
Type: CHAR(80)
Payment terms (Ex: 1 payment, 3 payments, etc).
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3.
Type: CHAR(50)
Send values that link the order header (or summary) data from the Orders file with its products and/or lines.
Send values that link the order header (or summary) data from the Orders file with its products and/or lines.
Line number. Incremental integer to number each of the order lines. Send from 1 to N, where N is the total number of products in the transaction.
Type: INT(11)
Amount.
Type: DECIMAL(5,2)
Product code. Product code or SKU with which the marketing area can later identify the item to segment by user purchasing behavior.
Type: CHAR(80)
Product short name.
Type: CHAR(200)
Brand of the product.
Type: CHAR(200)
Product description.
Type: CHAR(200)
Price of the product. (Final unit price of the product after discounts). Without taxation
Type: DECIMAL(10,2)
List price. (Unit price of product list. Send the same value as Price when there are no discounts or differential prices). Without taxation.
Type: DECIMAL(10,2)
Sale price. (Price of the product before discounts. Send the same value as Price when there are no discounts). Without taxation.
Type: DECIMAL(10,2)
Main Product Image URL. Url of the main image of the product in e-commerce or public hosting. (Send empty if it is not available or cannot be obtained).
Type: CHAR(255)
Product detail URL. Url of the product in e-commerce or public hosting. (Send empty if it is not available or cannot be obtained).
Type: CHAR(255)
Name of the Seller of the Product.
Type: CHAR(100)
Store Code.
Type: CHAR(30)
Number of order.
Type: CHAR(50)
Line number.
Type: INT(11)
Use the Combination of Store Code + Order Number + Line Number to assign the category to the corresponding order line reported in OrderDetails.
Product Category.
Category short text.
Type: CHAR(255)
When a product has more than one category assigned, a record must be sent for each category with the same StoreCode/OrderNumber/LineNumber.
As delimiter it is recommended to use “|”.
All DECIMAL type fields must incorporate the point (“.”) as a decimal separator. Do not use thousands separator.
In the fields that do not contain information, replace with empty (“”) or zero (0) as appropriate depending on the type of data (Empty for CHAR, or Zero for integers or decimals).
Respect in the files the order of the fields according to the design sent.
Verify that the 4 files are generated with the same ENCODING.
Remove all kinds of special characters outside the alphabet.
The formats will be received in a SFTP repository. Said repository can be the client's own or if required, ICOMMKT can have a space for this exchange of information.
Download these sample files on Omnichannel Integration at the following link.
You have doubts? Consult the FAQs on Omnichannel Integration in our help center.
Based on the daily generation of 4 files, ICOMM can obtain relevant transactions (and their customers) for their business. By processing these files, over 75 indicators will be calculated daily and can be used in dynamic segmentation and automation processes.
Formats
The following 4 plain text files must be generated daily:
Customers: This file aims to contain the list of customers (and their marketing-relevant attributes) linked to the transactions reported in the Orders file, as well as any new or updated customers from any data sources managed by the organization. It is possible to include prospects who have not made a purchase yet. However, it is essential to send customers who have made purchases that are being reported in the other files.
Orders: This file aims to contain a summary of the billed transactions or purchase orders, reporting total amounts, quantities, and other relevant operation attributes.
OrderDetails: This file aims to report the composition of a transaction or purchase order - its products and/or services with their respective details or attributes.
OrderDetailCategories: This file aims to list categories that can be attributed to each of the products and/or services reported in the OrderDetails file.
File Naming Convention
Orders: ICOMMKT_ORDERS_[UNIQUE INSTANCE ID]_YYYYMMDD
OrderDetail: ICOMMKT_ORDERSDETAIL_[UNIQUE INSTANCE ID]_YYYYMMDD
OrderDetailCategories: ICOMMKT_ORDERSDETAILCATEGORIES_[UNIQUE INSTANCE ID]_YYYYMMDD
Customers: ICOMMKT_CUSTOMERS_[UNIQUE INSTANCE ID]_YYYYMMDD
[UNIQUE INSTANCE ID] should be an identifier relative to the instance, when multiple brands/countries/business units are represented in different instances of our platform.
The files must be generated daily, whether they contain information or not. When any of them do not contain data, only the headers line should be sent.
File Structure
Customers
UserId:
The User/Client ID in the source system where the file is generated. It can be any type of identifier, including the same EMAIL if the data originates from an e-commerce where the user does not have any other assigned ID.
It should match the ID reported in the orders file to link the customer with their transactions.
Type: CHAR(50)
StoreCode:
The store code.
This field MUST be left empty when the organization has a unique user ID across all its stores, which has been unified in its CRM, ERP, or data source system. It should only be informed when a customer has different IDs for each store, where the store code originating this user/customer should be provided.
Type: CHAR(30)
Email:
Customer's email.
Type: CHAR(300)
CustomerData:
Collection.
Any number of attributes can be informed according to the actions desired by the marketing department. Each of these attributes will later become a field in the profile (database) within the platform.
Type: JSON Key/Value,
Format: Key/Value in JSON format.
Example: {"Key":"FIRST_NAME","Value":"Juan"},{"Key":"LAST_NAME","Value":"Sanchez"}
Orders
OrderNumber:
The transaction or order number.
Type: CHAR(50)
StoreCode:
The store code where the transaction or order originated.
Type: CHAR(30)
UserId:
*The User/Client ID in the source system where the file is generated.
Type: CHAR(50)
Date:
Order date.
Type: DATE
Format: dd/mm/yyyy
TotalItems:
Quantity of items in the order. It represents the total.
Type: DECIMAL(5,2)
TotalProducts
Quantity of Products (different items) in the order. Quantity of different products of the transaction or order. Different SKUs or ITEM CODE, depending on the value reported in the ITEM CODE of the Details File.
Type: DECIMAL(5,2)
TotalAmount:
Total amount of the order.
Type: DECIMAL(18,2)
ItemsAmount
Total amount of the order items (Without tax)
Type: DECIMAL(10,2)
DiscountsAmount
Amount of discounts applied to the order. Whether they are discount codes applied at the end of the operation or other quantity discounts.
Type: DECIMAL(10,2) ()
ShippingAmount:
Shipping amount for the order.
Type: DECIMAL(10,2)
TaxAmount:
Tax amount for the order.
Type: DECIMAL(10,2)
Promo Codes
Promotion codes applied.
Type: TEXT
PreShippingAmount
Total amount of the Order without shipping cost. There must be a value greater than zero. It should equal TotalAmount minus ShippingAmount.
Type: DECIMAL(10,2)
DeliveryType
Shipping Type (Ex: Standard, Express).
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3
Type: CHAR(50)
DeliverySubType
Carrier.
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3
Type: CHAR(50)
DeliveryCompany
Carrier.
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3
Type: CHAR(80)
PaymentMethod:
Payment method used for the order.
Type: CHAR(50)
PaymentMethodDetail
Detail of the payment method (Ex: VISA).
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3.
Type: CHAR(80)
PaymentMethodEntity
Entity of the payment method (Ex: Banxo XXX).
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3.
Type: CHAR(80)
PaymentTerms
Payment terms (Ex: 1 payment, 3 payments, etc).
When there is more than one piece of data to report for the field, they must be sent one after the other using a different separator than the general file. For example: VALUE1##VALUE2##VALUE3.
Type: CHAR(50)
Order details
StoreCode
Send values that link the order header (or summary) data from the Orders file with its products and/or lines.
OrderNumber
Send values that link the order header (or summary) data from the Orders file with its products and/or lines.
LineNumber
Line number. Incremental integer to number each of the order lines. Send from 1 to N, where N is the total number of products in the transaction.
Type: INT(11)
Quantity
Amount.
Type: DECIMAL(5,2)
ItemCode
Product code. Product code or SKU with which the marketing area can later identify the item to segment by user purchasing behavior.
Type: CHAR(80)
ItemName
Product short name.
Type: CHAR(200)
Brand
Brand of the product.
Type: CHAR(200)
Description
Product description.
Type: CHAR(200)
Prices
Price of the product. (Final unit price of the product after discounts). Without taxation
Type: DECIMAL(10,2)
ListPrice
List price. (Unit price of product list. Send the same value as Price when there are no discounts or differential prices). Without taxation.
Type: DECIMAL(10,2)
SellingPrice
Sale price. (Price of the product before discounts. Send the same value as Price when there are no discounts). Without taxation.
Type: DECIMAL(10,2)
ImageURL
Main Product Image URL. Url of the main image of the product in e-commerce or public hosting. (Send empty if it is not available or cannot be obtained).
Type: CHAR(255)
DetailURL
Product detail URL. Url of the product in e-commerce or public hosting. (Send empty if it is not available or cannot be obtained).
Type: CHAR(255)
Seller
Name of the Seller of the Product.
Type: CHAR(100)
Order Details Categories
StoreCode
Store Code.
Type: CHAR(30)
OrderNumber
Number of order.
Type: CHAR(50)
LineNumber
Line number.
Type: INT(11)
Use the Combination of Store Code + Order Number + Line Number to assign the category to the corresponding order line reported in OrderDetails.
Category
Product Category.
Category short text.
Type: CHAR(255)
When a product has more than one category assigned, a record must be sent for each category with the same StoreCode/OrderNumber/LineNumber.
General Note:
As delimiter it is recommended to use “|”.
All DECIMAL type fields must incorporate the point (“.”) as a decimal separator. Do not use thousands separator.
In the fields that do not contain information, replace with empty (“”) or zero (0) as appropriate depending on the type of data (Empty for CHAR, or Zero for integers or decimals).
Respect in the files the order of the fields according to the design sent.
Verify that the 4 files are generated with the same ENCODING.
Remove all kinds of special characters outside the alphabet.
Transfer
The formats will be received in a SFTP repository. Said repository can be the client's own or if required, ICOMMKT can have a space for this exchange of information.
Sample files
Download these sample files on Omnichannel Integration at the following link.
You have doubts? Consult the FAQs on Omnichannel Integration in our help center.
Updated on: 08/03/2023
Thank you!