Preparing your sales data file – Emarsys

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

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 Management > Predict 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 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 (Management menu > Predict 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.

Adding columns to a working sales data file

If you have a working implementation of Smart Insight, you can still add columns to your sales data CSV file without a full reload.

There are two crucial things to pay attention to when adding the new columns:

add the new columns at the end of the configured header (column list)
keep the existing order of the columns

If you change the order of the existing columns or insert the new columns between the existing ones, some of the existing historical sales data will be overwritten. We cannot recover such data, so if this happens, only a full Smart Insight reload is able to restore the lost data.

For in-depth information on CSV file editing, see Modifying CSV files in use.

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	UTC: 2016-06-06T14:02:00Z local time with time zone: 2020-07-24T05:00:01+10:00	The date and, optionally, the time of the transaction either in: UTC or local time with time zone offset information (e.g. if your time zone is in UTC+10, then your `timestamp` will look like as follows: 2020-07-24T05:00:01+10:00). If you only submit the date (e.g. 2016-06-06) we automatically add the time 00:00:00. Note: If you are using Loyalty and Smart Insight, then we recommend specifying the timestamp in local time with time zone offset information. If you submit only the date with the timestamp 00:00:00 (e.g. 2020-08-03T00:00:00), then as a result, your customers may lose some of their Loyalty points. If you specify the timestamp in UTC (e.g. 2020-08-03T15:05:00Z), then it may affect your Smart Insight reports.
`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	The original currency the sale was made in. Always use the three-letter abbreviation, which you can find here: https://www.easymarkets.com/eu/learn-centre/discover-trading/currency-acronyms-and-abbreviations/.

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.

Use cases

I use the Shopify plugin but I cannot add additional columns to my sales data. What shall I do?

If you are using the Shopify plugin, there is no need to add the three additional fields for Loyalty, as the plugin always only works on one Shopify website - market. Loyalty will use the price field instead of the f_original_price.

I only work in a single market and single currency. It is too complicated for me to add the additional fields. Do I still need them?

If you have only one market and currency then you do not need to add the three additional fields, as long as, you send the correct price the user paid at checkout in the price column; including tax and gross price.

I only work in a single market and single currency, but I want the price column to include only the net price. What shall i do?

In this case, provide the additional three fields, and the f_original_price should include the price for which you want to give out points, namely the gross price.

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 (the timestamp is specified in UTC) :

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

In the following example, the timestamp format is specified in local time with time zone offset information (the time zone is UTC+10):

order,timestamp,customer,item,price,s_channel,quantity,f_unit_price,s_storeid,s_currency,s_colour_code,s_colour_name,s_size_code,s_barcode,s_department,s_class,s_season,s_brand,f_total_discount,f_total_ex_tax,s_voucher_number,s_discount_reason
MJB_228762E0022735,2020-07-29T08:47:32+10:00,341472,SHI106S17_Sky,20.30,OFFLINE,1,29,ONLINE,AUD,Sky,Sky,37,9342743705097,Shirts,Item,Spring17,XYZ_Brand,8.70,11.09,,Promotional Discount
MJB_228762E0022735,2020-07-29T08:47:32+10:00,341472,SHI267S17_Blue,20.30,OFFLINE,1,29,ONLINE,AUD,Blue,Blue,37,9342743724463,Shirts,Item,Summer17,XYZ_Brand,8.70,11.09,,Promotional Discount
MJB_228762E0022735,2020-07-29T08:47:32+10:00,341472,SHI229S18_PinkNavy,20.30,OFFLINE,1,29,ONLINE,AUD,Pink/Navy,Pink/Navy,37,9342743788434,Shirts,Item,Summer 18,XYZ_Brand,8.70,12.91,,Promotional Discount
MJB_228840E0022736,2020-07-29T08:48:42+10:00,538691,SHI302S18_Navy,34.30,OFFLINE,1,49,ONLINE,AUD,Navy,Navy,48,9342743790086,Shirts,Item,Spring 18,XYZ_Brand,14.70,25.64,,Promotional Discount
MJB_228856E0022737,2020-07-29T08:49:41+10:00,527149,SHI289S18_Denim,20.30,OFFLINE,1,29,ONLINE,AUD,Denim,Denim,S,9342743789516,Shirts Casual,Item,Summer 18,XYZ_Brand,8.70,9.28,,Promotional Discount
MJB_228825E0022738,2020-07-29T08:50:43+10:00,462561,SHI140W19_SkyWhite,34.30,OFFLINE,1,49,ONLINE,AUD,Sky/White,Sky/White,42,9342743802130,Shirts,Item,Autumn19,XYZ_Brand,14.70,25.64,,Promotional Discount
MJB_228835E0022739,2020-07-29T08:52:05+10:00,527806,SHI210S18_Blue,20.30,OFFLINE,1,29,ONLINE,AUD,Blue,Blue,46,9342743784351,Shirts,Item,Summer 18,XYZ_Brand,8.70,11.09,,Promotional Discount
MJB_228835E0022739,2020-07-29T08:52:05+10:00,527806,SHI284S18_Sky,20.30,OFFLINE,1,29,ONLINE,AUD,Sky,Sky,46,9342743785556,Shirts,Item,Summer 18,XYZ_Brand,8.70,11.09,,Promotional Discount

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.

Preparing for Integration