Importing historical transactions

When getting started with Voyado Customer Engagement, historical transactions are valuable as a way to gain insights and allow you to try out more advanced segmentation. However, the Customer Engagement API should never be used to perform operations on a large number of transactions simultaneously (a so-called bulk update). For doing this, our recommended approach is to migrate your transactions via a file, either as XML or CSV. The key questions you need to answer before a file-based migration are:

How far back should I go? (Voyado usually recommends 24 months)
Do I plan to add additional product data to the transactions?
Does my data include transactions that aren’t relevant? For example, those of deleted contacts or with irrelevant products.

Any such migration needs to be planned with your Voyado team. Migrations are handled separately from automated imports.

In Customer Engagement, a transaction is defined as when a customer visits a physical or online store and purchases (or returns) one or more items. The transaction contains metadata — such as the transaction’s unique ID, the store’s ID, the timestamp — plus the data for each individual item purchased or returned (line items). The transaction’s data is then sent to the endpoint /receipts to be saved so you might see the term “receipt” turning up in the API.

An import of historical transactions by file will NOT trigger the “Product purchase” automation trigger.

Prerequisites

Here are some important things to confirm before you import your historical transactions.

All contacts referred to in your file must already exist in Customer Engagement before the import of transactions is started. If a contact does not exist, the transaction data connected to them will be ignored.
Likewise, all stores referred to in the file must already exist in Customer Engagement.
If an imported transaction already exists in Customer Engagement then the imported transaction data will be ignored. No updates will be done to the existing transaction.
Don’t exceed 20 000 receipts per file. This is not a hard limit but a strong recommendation. Large files can make the server very slow and risk causing a timeout. Note that the number refers to transaction lines and not transactions.
If the import is done in CSV format the columns should be separated by semicolons and not by commas.
For dates, Customer Engagement requires the data formatted as ISO8601 with Time zone designators.

It is important to only use the matchKeyType that is configured for your Customer Engagement environment when importing transactions, otherwise your import will fail. Allowed values for matchKeyType are:

email
mobilePhone
externalId
memberNumber
socialSecurityNumber

Validation of your files

Validation of your CSV or XML files is an important step before importing. It ensures your data has the correct format and will greatly reduce the possibility of errors and lost data. Use the Customer Engagement validation service to make sure your files are in the correct format. You can read more about the service below.

When using the data validator for transactions, be aware that it will NOT detect missing stores or contacts, because the validator is not directly connected to a specific Customer Engagement environment. The client should ensure that the stores referenced exist in Customer Engagement, as do the contacts. However, when importing that file into Customer Engagement, any “bad” lines will just be skipped and logged as errors.

File import location

When your files have been prepared and approved on your side, they’ll need to be placed in a designated folder on the Customer Engagement FTP for the actual import to happen. Place your transaction files in the folder named transactionMigration. If this folder has not been created, you can create it manually in the FTP client if you have sufficient access rights. Read more about the import steps here. If you need help, contact your Voyado team.

CSV file import

You can download CSV and Excel examples on the example files page. There are two kinds of rows in a CSV import, header rows and item rows.

Header rows hold information about the transaction
Item rows are the items involved in that transaction

Whether a row is a header or an item is determined by the the field called header which is 1 for header rows and 0 for item rows.

Header fields

Fields marked with * are mandatory.

Field	Example	Type	Description
*matchKey	doe@voyado.com	string	A value to match against the specified matchKeyType and contactType
*matchKeyType	email	string	Valid options: memberNumber, socialSecurityNumber, mobilePhone, externalId, email
*contactType	member	string	Which contact type to match the transaction with
*uniqueReceiptId	2287565954-25	string	Needs to be a unique number for each transaction
*receiptNumber	2287565954	string	The transaction number you segment on in Customer Engagement. Does not have to be unique
*createdDate	2020-09-06T15:55:54+01:00	datetime	Date and time when the transaction was created formatted according to ISO 8601 with Time Zone designator
*storeExternalId	930	string	The unique ID for the store where the purchase was made
*currency	EUR	currency	The currency code, according to ISO 4217, for the currency in which the customer paid (local currency)
*exchangeRateToGroupCurrency	10.00	decimal	The exchange rate to the group currency set in Customer Engagement. Default value is 1.00
*totalGrossPrice	200.00	decimal	The total price paid by the customer including VAT. A decimal point (.) is always used as decimal separator. Default is 0
*header	1	bit	Should always have the value 1 if it is a transaction header
paymentTypeType	creditcard	string	Pre-defined values can be set: “card”, “cash”, “creditcard”, “bonuscheck”, “swish”, “klarna”. If your value does not match one of the above, it will be categorized as custom: “custom: [value]”(max 245 characters)
paymentTypeValue	200.00	decimal	Amount in decimal format, cannot be empty
paymentTypeDescription	Mastercard	string	A descriptive text (max 255 characters)

Only the matchKeyType that is actively configured for your Customer Engagement tenant will work when importing transactions. To avoid failed imports, verify which matchKeyType is configured in your environment before use.

Item row fields

Fields marked with * are mandatory.

Field	Example	Type	Description
*type	PURCHASE	string	A line item can either be of type PURCHASE or RETURN
*sku	12345-1	string	Store keeping unit of the article
*articleName	Denim white	string	Article name
articleGroup	Jeans	string	The highest level of the article group categorization to segment on in Voyado
*quantity	1	integer	The number of units sold in the specific line item. If it is a RETURN then it must be a negative value.
*grossPaidPrice	200.00	decimal	The total price of units in the specific line item including VAT and the discount withdrawal
articleNumber	12345	string	Article number
awardsBonus	true	boolean	If the line item included for reward calculation or not. Default value is true if the field is not defined
taxAmount	40.00	decimal	The total VAT of the units in the line item. Default is 0
taxPercent	25.00	decimal	The VAT percentage of the unit. Default is 0
discount	-200.00	decimal	Discount value in local currency including VAT / MOMS. Should always be negative. Segmentation on discounts is possible in Voyado. Default is 0
spare[1-10]		string	Additional data for the line item
datespare[1-5]	2020-09-06T15:55:54+01:00	datetime	Additional datetime data for the line item
*header	0	bit	Must have value 0 if it is a line item row

Unusual characters might give an error during a CSV validation or import. One of these is the character ” (ASCII code Alt 0148). If your import fails for unknown reasons, check for this and other unusual characters.

XML file import

If your import data is in XML format, it must have the following header:

<?xml version="1.0" encoding="UTF-8"?>
<transactions xmlns="http://voyado.Schemas.ImportReceipts" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

A complete XML file for importing transactions will look something like this:

<?xml version="1.0" encoding="UTF-8"?>
<transactions xmlns="http://voyado.Schemas.ImportReceipts" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<transaction LineNumber="0" LinePosition="0">
		<contact LineNumber="0" LinePosition="0">
			<matchKey>120200000044</matchKey>
			<matchKeyType>memberNumber</matchKeyType>
			<contactType>Member</contactType>
		</contact>
		<uniqueReceiptId>7a5f0140-c50f-11eb-8984-5b9396574b87</uniqueReceiptId>
		<receiptNumber>7a5f0140-c50f-11eb-8984-5b9396574b87</receiptNumber>
		<createdDate>2020-08-06T08:29:40+02:00</createdDate>
		<storeExternalId>8202</storeExternalId>
		<currency>SEK</currency>
		<exchangeRateToGroupCurrency>1</exchangeRateToGroupCurrency>
		<totalGrossPrice>200</totalGrossPrice>
		<paymentMethods>
		      <paymentMethod>
		        <type>bonuscheck</type>
		        <description>50kr bonuscheck</description>
		        <value>50.00</value>
		      </paymentMethod>
		      <paymentMethod>
		        <type>card</type>
		        <description>card</description>
		        <value>150.00</value>
		      </paymentMethod>
		</paymentMethods>
		<items>
			<item LineNumber="1" LinePosition="0">
				<type>PURCHASE</type>
				<sku>A123</sku>
				<quantity>1</quantity>
				<grossPaidPrice>250</grossPaidPrice>
				<taxAmount></taxAmount>
				<taxPercent></taxPercent>
				<articleNumber>ArticleNumber123</articleNumber>
				<articleName>ArticleName 1</articleName>
				<articleGroup>Group 1</articleGroup>
				<discount>0</discount>
				<awardsBonus>false</awardsBonus>
				<spare1>Spare 1</spare1>
				<spare2>Spare 2</spare2>
			</item>
			<item LineNumber="5" LinePosition="0">
				<type>RETURN</type>
				<sku>A123</sku>
				<quantity>-1</quantity>
				<grossPaidPrice>50</grossPaidPrice>
				<taxAmount></taxAmount>
				<taxPercent></taxPercent>
				<articleNumber>ArticleNumber456</articleNumber>
				<articleName>ArticleName 2</articleName>
				<articleGroup>Group 2</articleGroup>
				<discount>0</discount>
				<awardsBonus>false</awardsBonus>
				<spare3>Spare 3</spare3>
			</item>
		</items>
	</transaction>
</transactions>

Due to technical limitations, only one discount amount can be processed per file. If multiple discounts need to be used, summarize the values into a single discount amount.

It’s important to note that when sending paymentMethod in the XML you need to include all three elements, type, value and description, as seen in the example above.

The mandatory fields in an XML import are the same as in the CSV import. See the tables above.

Validation service

There is an online validation service you can use to make sure your transactions (receipts) and points files are correctly formatted before you try to import them into Customer Engagement. You’ll find the online validation here: https://datavalidator.voyado.com. Follow the guidelines laid out in this section.

Validation service - local

The local validation service is no longer supported. Validation is now done through the web-based validation service.

Importing historical data

Data import by file

Data export by file

Importing historical transactions

Prerequisites

Validation of your files

File import location

CSV file import

Header fields

Item row fields

XML file import

Validation service

Validation service - local

​Prerequisites

​Validation of your files

​File import location

​CSV file import

​Header fields

​Item row fields

​XML file import

​Validation service

​Validation service - local

Prerequisites

Validation of your files

File import location

CSV file import

Header fields

Item row fields

XML file import

Validation service

Validation service - local