Customizing the Sales Data file upload and challenges faced

[hiteshharsh]

Sales Data is an important parameter which is needed to be imported in Emrarsys. Sales data adds up to the revenue metrics as well as data for Smart Insight. This data needs to be configured in a certain order to be imported into Emarsys. In this blog we will discuss the configuration of sales data.

Sales data can be imported into Emarsys via 2 methods, either via Smart Insight SFTP or Emarsys Predict API. It is preferred to use Predict API as it provides a fast as well as immediate verification result which can result into faster troubleshooting. We will be discussing the latter in detail here. The file is configured in a CSV format and consist of header with standard as well as custom fields. Each line consists of a single line item of a particular order. For example, if an order consists of 6 different products, then that order will consist of 6 lines with each product with its quantity in one line. This file should be named sales_items*.csv where * can be any random sets of character. This file needs to be a CSV file with UTF-8 coding.

 

Fields in Emarsys

Standard Fields

Types of fields in Sales data file are of 2 types, Standard and custom. Standard fields are required field and these fields needs to be in it’s particular data type as well. Thers are standard fields in Emarsys

Field	Data Type	Description	Example
item	String	The unique Product ID which is purchased	ABC01
price	Float	Price of the product	25.36
order	String	Order Number	XY3456-T
timestamp	Timestamp in UTC	Time of transaction	2021-12-6T15:02:00Z
customer	String	Customer number	001234432
Email	String(email)	Email Id of the customer	Exp@test.com
quantity	Float	Quantity purchased	2.0

Here both, customer and email can not be configured in the file. Only one can be used for your sales data. This is discussed in detail below.

Custom Fields

Custom Fields are an important aspect of Emarsys Configuration file as additional information such as local currency. These custom fields are added as a new column in the file. Naming convention for these fields are also pre-defined by Emarsys which is DataTypePrefix_Name. The available datatypes available are as follow

Datatype	Prefix
Integer	i_*
Float	f_*
Timestamp	t_*
String	s_*

Challenges in Preparing the file -:

Converting multiple sales data order in Emarsys Format(order per line format)
Sales Data in Emarsys needs to be in The Emarsys format which is line per order and as it is not possible to get sales Data in the Emarsys format by Design. Here we are taking SAP ECC system as our source system.
To get sales data in the expected format we have used SAP CPI.

As data coming from ECC contains multiple orders in one line a splitter is used to split the orders.
Mapping is also done where multiple transformations takes place in order to make them in the format of Emarsys.

Once this data is converted a gatherer is used to gather the data and sent to Predict API in csv format
Sales data can be checked in Emarsys in the predict API in Upload History.

Get prices in different local currencies.
Currently in Emarsys single tenant there is no option to select multiple currencies at once but sometimes we may need it for localized reporting in smart Insight screens.

In order to achieve this, you can add 2 custom fields one for local currency and another one for Price in local currency. You will also need a mechanism to update the currency conversion in regular interval(defined by users).

Once you get these 2 values you can use them to create local currency criterion by combining these 2 filters.

For example your standard currency selection is USD (United States Dollar) and you wanted to segment customers with the criterion people who made a purchase of 100 Euros or more. Here this filtering criterion can be used, we can select 100 from the Local price filter and Euros from Currency filter.

Linking orders to both Customer Id and Email.
If your Sales order data contains both Email and Customer Id, Emarsys do not allow you to link your orders using both. Here we will be using a 3rd system which is capable of making calls to Emarsys, this solutioning was done using SAP Customer Platform Integration(CPI)
This solution also involves SAP Customer Platform Integration(CPI) where we make an Http request to Emarsys.

For example here a Http call is made to the system where we have check for email address and it is then sent to CPI where as the data is received it is added to the payload and sent to Emarsys.

[KarishmaKM]

The blog is helpful to understand the challenges and the solution while preparing the sales order file.

[rsenthilnath]

Good one Hitesh. Thanks for sharing!!

[jsh_agn]

Thank you for sharing this, Hitesh.

I'm currently working with a similar approach of loading the csv to the api. Did you specify a file name for this replication instance? If yes, is there a specific header you populated? The documentation currently shows that it is embedded in the data itself in this sample: 
https://help.emarsys.com/hc/en-us/articles/213706429-Uploading-your-sales-data#endpoint

     --data-binary @/myhome/salesdata-2018-03-26.csv \ 

 I've also looked into replicating using form-data but it parses multipart form thus produces an error in Smart insight.

For now, I have sticked to sending the csv as is but no specifications on filename.
Would like to hear your thoughts on this. 
Thank you!