• Deutsch español français русский Türkçe 中文（中国）
    1. Emarsys
    2. Getting Started
    3. Preparing for Integration

    Preparing your sales data file
    Updated

    You must format your sales data file according to the specifications and guidelines described below.

    Contents

    Before you start

    What is sales data?

    Sales data is all the information about the transactions registered in your stores, including daily purchases, returns and cancellations, as well as offline data from your physical stores. We store your sales data in our Sales Data Service, through which individual Emarsys apps can access it.

    The status of your sales data as well as the regular updates can be monitored on the Admin > Data Sources > Sales Data page.

    Important

    • Sales data uploads are incremental, meaning new uploads add to existing sales data and do not overwrite them.
    • As Emarsys currently does not deduplicate sales data, make sure you upload each individual order only once as duplicate records will skew your reporting.

    Why is sales data important?

    Even though our Web Extend data collection scripts track purchases in your online store, they do not collect any information about returns, cancellations and offline transactions. This information is essential for all the related Emarsys functionality, such as accurate revenue reporting or product affinity models, and helps to make our smart features even smarter.

    Your sales data is required by most of the Emarsys products and features, including Predict and Incentive Recommendations, and Smart Insight. It is also needed for campaign revenue attribution and revenue statistics and reporting.

    How much effort do I need to invest in this?

    First you will need to generate an initial file, ideally containing at least two years of sales data, that conforms to our specifications, described below. Depending on the size and complexity of your sales history, it may take a few hours for your data specialist to deliver this file. 

    Then, if our validation engine detects some issues, you will need to fix these before proceeding, which may take another couple of hours of your data specialist’s time.

    Based on past experience with a wide sample of customers, here is an indication of how much time you may require to set up your sales data file:

    Action
    		 Role Max effort
    Generating historical sales data file Data specialist Up to 8 hours
    Validating the file Data specialist 5 minutes
    Fixing errors, if applicable Data specialist Depends on the number and severity of issues, but usually no more than 2 hours
    Setting up daily sales data import Data specialist Up to 2 hours

    How often do I need to upload sales data?

    After making the initial upload of your historical sales data, Emarsys recommends to upload new sales data on a daily basis.

    You may upload sales data updates more frequently, but Smart Insight will process these sales data files in an aggregated manner only once a day.

    General rules for your data file

    Your sales data file upload will fail if it does not pass all our validation checks. You must make sure that the file is correctly formatted as described below.

    The columns in the header of your sales data file must be in the order that was agreed during the Smart insight onboarding implementation. If they are not, the upload will fail.

    You can check the order of the columns at the top of the Sales Data page (Admin menu > Data Sources > Sales Data > Manage sales data), for example:

    • Your sales data file should be a simple .csv table, with each row describing the attributes of a single line item.
    • Your file must start with a header row (e.g. the field names).
    • The file name must follow the convention sales_items*.csv. In other words, the file name must begin with the string sales_item and its extension must be .csv, while you can replace the asterisk with a number of characters of your choice (e.g. a timestamp).
    • Use a CSV export library that is compatible with the standard CSV format.
    • Use a comma to separate fields in a row.
    • When a field does not have any value, leave it empty. Example: value1,,value3,value4.
    • Do not use newline characters, either unquoted or quoted, in the fields.
    • Use UTF-8 encoding.
    • Include the field names in the first row (header) and make sure that all the subsequent rows contain the values corresponding to the fields listed in the header. If you are not sure how to do this, contact Emarsys Support.
    • Do not use more than 255 columns.
    • Do not use field values longer than 64 KB.
    • Make sure the required fields are entered case-sensitively, or otherwise those will not be recognized by the system. 'Price' is not the same as 'price'. 'Price' is recognized as a custom field and will get a prefix, such as f_, which will look like f_Price. In this case the price field will be missing.

    Sample data file

    Need an example? Click here to see how a sales data file looks like or download a sample file.

    Required fields

    Standard field set

    These fields are required for the standard feature set in Emarsys.

    Field
    		 Required
    		 Type
    		 Example
    		 Description
    item
    		 Yes
    		 String
    		 BOOK0012
    • The unique ID used in your web store to identify the product.
    • The ID submitted here should be found in your Emarsys product database.
    • For orders comprising multiple products, each product (known as line item) must be submitted in a separate row with the same order ID.
    price
    		 Yes
    		 Float
    		 110.30
    • The total price of the items (the unit price multiplied by the quantity) in the currency defined in the Account Details section of the Data Sources page.
    • Always use the period (.) as the decimal mark (i.e. 10.5, not 10,5).
    • Do NOT use any thousands separator (i.e. 2000, not 2,000).
    • Do NOT include the currency.
    • For refunds, always submit a negative value.
    order
    		 Yes
    		 String
    		 111-U8
    • The unique identifier of the order.
    • For orders comprising multiple products, use the same order ID for each line item.
    timestamp
    		 Yes
    		 Timestamp
    		 2016-06-06T14:02:00Z
    • The date and, optionally, the time in UTC of the transaction.
    • If you only submit the date (e.g. 2016-06-06) we automatically add the time 00:00:00.
    customer
    		 Yes (either email or customer is required. Do not use both!)
    		 String
    		 12343-14B
    • The anonymous unique ID of the customer placing the order.
    • The ID submitted here should be found in your Emarsys contact database.
    email
    		 Yes (either email or customer is required. Do not use both!)
    		 Email
    		 sportscar_fanatic
    @gmail.com
    • The email address of the customer placing the order.
    • For privacy reasons, Emarsys stores the email addresses you submit in your sales data file encoded in such a way that the original email address cannot be restored.
    quantity
    		 Yes
    		 Float
    		 5
    • The quantity (number) of the purchased item(s).
    • Always use the period (.) as the decimal mark.
    • Do NOT use any thousands separator.
    • For refunds, always submit a negative value.

    Important: Make sure to use either customer or email for user identification consistently in all your sales data files and not to mix them.

    Loyalty fields

    If you are using Emarsys Loyalty, you will also need to have these fields in your sales data file.

    Field
    		 Required
    		 Type
    		 Example
    		 Description
    s_market
    		 Yes
    		 String
    		 Germany
    • The market where this sales was made.
    • Market can be either country-based, language-based or even based on your own definition of markets, e.g. B2B & B2C.
    f_original_price
    		 Yes
    		 Float
    		 3549.92
    • The total price of the items (the unit price multiplied by the quantity) in the original currency.
    • Always use the period (.) as the decimal mark (i.e. 10.5, not 10,5).
    • Do NOT use any thousands separator (i.e. 2000, not 2,000).
    • Do NOT include the currency.
    • For refunds, always submit a negative value.
    s_original_currency
    		 Yes
    		 String USD

    Cancellation and refunds requirements for Loyalty

    When submitting returned items or orders, always use the order, customer and item IDs of the original order and provide a negative value in both the price and the quantity fields, as well as in f_original_price. Please make sure to populate the fields in your sales data file this way, otherwise refunds will not be properly represented in your reports.

    Retail fields

    The s_sales_channel and s_store_id fields are part of a feature that is currently on Pilot release for Retail clients only. If you are interested in participating in the Pilot phase, please speak to your Success Manager.

    If you are using the Strategic Dashboard for Retail customers feature, you will also need to have these fields in your sales data file.

    Field Required Type Example Description
    s_sales_channel
    		 Yes String “online" The sales channel of the order. Its value is either “online” or “offline”.
    s_store_id
    		 Yes String “87" The external ID of the store.

    Custom fields

    Besides the mandatory sales data fields, you can also add other information to your sales data file by using custom fields. The names of custom fields must begin with a prefix specifying the data type. The prefix then must be followed by an underscore (_) and the name of the custom column. If the name of a field consists of multiple words, then each word must start with a lowercase letter and each word should be separated by an underscore. For example, s_original_currency.

    Available custom field prefixes:

    Prefix
    		 Type Example
    i_*
    		 Integer i_customer_age
    f_*
    		 Float f_customer_height
    t_*
    		 Timestamp t_date_of_manufacture
    s_*
    		 String s_book_author
    • Custom fields are validated for data type and if the submitted values are not consistent with the expected data type, the sales data file will be rejected.
    • Custom fields can be empty. However, for numeric fields a custom integer field cannot have empty strings. For example, double quotes with no values in it "" are not accepted. Numeric fields with a NULL value, for example double commas: ,, are acceptable.

    Sales data example

    The following example shows orders for a shoe store with three custom columns:

    item,price,order,timestamp,customer,quantity,s_color,f_size,t_published
103-nike-1,24.95,112343,2016-06-06T14:02:00Z,093275854,1,blue,14.5,2016-09-20T15:00:00Z
105-adidas-66,99.95,112343,2016-06-06,493027334,3,black,14.5,2016-09-20T15:00:00Z
107-nike-24,50.99,66234,2016-06-06T17:00:00Z,672985375,1,pink,13.5,2016-08-01

    Returns, cancellations and refunds

    When submitting returned items or orders, always use the order, customer and item IDs of the original order and provide a negative value in thes_ price,  the quantity and the f_original_price fields. Please make sure to populate the fields in your sales data file this way, otherwise refunds will not be properly represented in your reports.

    Example

    A customer might buy three items:

    Order ID Date Customer ID Item ID price quantity s_market f_original_price s_original_currency
    OR12345
    		 15.10.2015
    		 CU54343
    		 IT8798
    		 45 1 HU 14234.76 HUF
    OR12345
    		 15.10.2015
    		 CU54343
    		 IT2235
    		 30 1 HU 9546.23 HUF
    OR12345
    		 15.10.2015
    		 CU54343
    		 IT8840
    		 25 1 HU 3452.45 HUF

    A week later they decided to return two of the items, which should be reported in the sales data file as follows:

    Order ID Date Customer ID Item ID price quantity s_market f_original_price s_original_currency
    OR12345
    		 22.10.2015
    		 CU54343 IT2235
    		 -30
    		 -1 HU -9546.23 HUF
    OR12345
    		 22.10.2015
    		 CU54343
    		 IT8840
    		 -25 -1 HU -3452.45 HUF

    These two order parts will then be aggregated into a single order with the total value of 45, while the order date will remain unchanged (i.e. Emarsys keeps the date of the original order). 

    Smart Insight and sales data FAQ

    Why isn’t SFTP the preferred method for uploading sales data?

    Because it has several disadvantages compared to uploads via the sales data API. Among other things:

    • Your file is not validated straight away, which makes troubleshooting slower.
    • FTP accounts need to be set up, while the sales data API is totally self-service.
    • With FTP, you always have to make sure to give a unique name to your sales data file to avoid overwriting previous uploads.

    What happens if the customer identifier (email or customer_id) is missing from some or all of the rows in the sales data file?

    Well, try to make sure that this does not happen as this can skew your Smart Insight scoring and reporting.

    But if it still does happen Emarsys will accept the sales data file, regardless if it was uploaded via Predict API or the Smart Insight FTP method. In this case, Emarsys will create one pseudo contact for your account, and attribute all orders where the customer identifier is missing to this contact. If you often submit your sales data files with missing customer IDs, Emarsys may create a new pseudo contact for each affected order.

    Pseudo contacts are given a unique customer ID in our contact database and appear as generated on the Smart Insight screens.

    What happens if a customer is not found in the Emarsys contact database?

    If you use the Smart Insight SFTP for uploading your sales data, orders submitted with an email or customer ID missing from the Emarsys contact database will still be processed, and the unknown contact will be given a customer ID and will be flagged as generated in the Customer Lifecycle reports. Using the generated filter, this contact can be separated from regular contacts in your contact database.

    If such generated contacts are explicitly identified later, they will turn into regular contacts at the next data load.