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 Admin > Data Sources > Sales Data page.
- 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:
|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_itemand 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:
- 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.
Important: Make sure to use either
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.
Available custom field prefixes:
- 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.
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 both the
price and the
quantity 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.
A customer might buy three items:
|Order ID||Date||Customer ID||Item ID||price||quantity|
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|
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.