In this section:

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 we currently do 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, we recommend 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.

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.

The Sales_channel and 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.

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.
Sales_channel
 Yes (only required for Retail customers participating in the Strategic Dashboard for Retail clients Pilot release)
 String online
 The sales channel of the order. Its value is either “online” or “offline”.
Store_id
 Yes (only required for Retail customers participating in the Strategic Dashboard for Retail clients Pilot release)
 String 87 The external ID of the store.

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
 USD 111-U8

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.

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. we keep 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, and you use the Smart Insight SFTP for uploading your sales data, we will accept the sales data file. In this case, we 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, we 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.

Emarsys is a Leader

You may also be interested in:

Related articles