Page tree
Skip to end of metadata
Go to start of metadata

Interaction Studio supports ingesting of transactions to associate purchases of items with individual users. Transactions may be updated by sending records with the same transactionId and all associated records. Files should be sorted by transactionId, but Interaction Studio also provides the ability to sort if needed. Transactions do not require any configuration of the catalog and are available by default. 

This Article Explains

This article details the requirements and schema of the Transaction ETL and provides a sample file structure and download.

Sections in this Article

Requirements and Schema

This etl is designed to bring transactions into Interaction Studio and associate those transactions to the correct user.  A transaction will either be assigned to a known user or generate a new user if there is not a known user already in Interaction Studio.   Users that are created via the transaction etl will count towards the named user total in the account.

Each transaction is made up of a list of consecutive records for individual line items in the transaction, which all have the same transactionId value. All records of a transaction must have the same userId and purchaseDate values. Every record in a transaction must have a productId, price, and quantity. 

If the file is not sorted by transactionId upon delivery, sorting can be enabled for the Transaction ETL in the Gear Configuration screen by marking the Transaction ETL Sort Before Grouping option. If the file is not sorted (either upon delivery or by enabling sorting) then the data will fail to load. The file must be sorted and resubmitted to be processed properly.

When updating a transaction with new information the entire contents of the transaction must be present in the file, meaning any and all line items of the transaction must be included, even if they are not changing. Any records previously stored for the transaction in Interaction Studio will be removed if a file is received that has a transactionId which reflects a previously recorded transaction.


IMPORTANT NOTE

For customers leveraging Interaction Studio’s multiple identities system, at least one identity attribute is required to be present in the ETL file. If you would like to send in multiple identity attributes for a single customer, this is supported by simply having multiple identity attribute columns in the file.  Only unique identities can be included and updated through etl.

The correct ETL header format for identity attributes is attribute:value. Examples of the out-of-the box identity attributes with proper formatting is as follows:

  • attribute:emailAddress
  • attribute:sfmcContactKey
  • attribute:customerId
  • attribute:sfcrmContactId
  • attribute:sfcrmLeadId

userId is not referenced in ETL processing when the multiple identities system is enabled. For customers NOT leveraging Interaction Studio’s multiple identities system, userId will still be supported for profile merging.

Configuration

The Transaction ETL expects each transaction to potentially have multiple line items associated with it. By default the Interaction Studio parser expects that transaction files are sorted by transactionId. If the file needs to be sorted, the Transaction ETL can be sorted by an Admin in the Gear Configuration screen and marking the box to enable transactionETLSortBeforeGrouping. This will result in the file being sorted prior to loading the data into Interaction Studio.

File Format

File Name Format: transaction-YYYY-MM-DD_HH-MM-SS.csv

Requirements and Schema

Field Name

Minimum Requirements

Example Values

Max Length

Interaction Studio Data Type

Identities



userId OR an Identity Attribute

Required.

  • For clients that ARE NOT using Interaction Studio's multiple identities system, a user ID must be included. This ID must be one that is tracked within the Evergage platform so that the events can be tied to the specific user profile.
  • For clients that ARE using Interaction Studio's multiple identities system, userId is not referenced in ETL file loads. At least one identity attribute is required. Multiple identity attributes can be included for a single user by simply including multiple columns in the file. The proper format for identity attributes is detailed in the comment above.

user168515262,

jdoe@test.com

120String
Required Fields



transactionIdRequired: The transactionId field is required for every record in the file. This represents a unique identifier for an individual purchase. All line items from a transaction must share the same transaction ID. It is required that files be sorted by transactionId so that all records in a file that share a transactionId are represented in consecutive rows.860340254255String
purchaseDate

Required: ISO 8601 Date time string for when the transaction occurred. The first record read for a transaction defines the date which is set on the overall order. All dates are stored in UTC time only.

The purchasedDate field is required for campaign attribution to work as intended. If this field is not included, Interaction Studio populates the field with the ETL commit timestamp.

2020-10-15

2020-01-09T11:24:59Z

1023Date
productIdRequired: Represents the product in the catalog which is purchased in the transaction. If the ID does not reflect an existing product in the catalog, a new item with the productId will be created in the catalog. This value should be the same product ID that would be provided in a Product ETL or tracked on the website.

prod001,

prod1101

255String
priceRequired: The unit price the user was charged. Period as the decimal separator, no thousands separator. This will be multiplied by the quantity to determine the total value of this line item. For instance, if price is $1.10 and quantity is 3, the total value of the line item is $3.30.150, 63.25, 101023Decimal
quantityRequired: This is the net quantity purchased. It is multiplied by the price to determine the total cost of that line item in the transaction which are then all summed to reflect the total value of the order in Interaction Studio.1, 50, 1001023Integer
System Fields



attribute:currency

Optional: The currency of the transaction. The currency must be consistent across all records in a transaction. If no currency is provided the currency of the transaction will default to the currency as defined in the Catalog Object Setup screen for that dataset. Must be in ISO 4217 format, 3 Uppercase letters.

USD, AUD, EUR3String
attribute:shipStatusOptional: An enum of string values representing the status of the order. Possible values are: shipped, delivered, processingshipped
delivered
processing
1023string
attribute:quantityReturnedOptional: A potential attribute representing the number of items in the line that have been returned.100, 25, 01023String

  

Sample File

Sample File Download (For clients that ARE NOT leveraging Interaction Studio's multiple identities system)

transactionId

userId

purchaseDate

productId

price

quantity

attribute:currency

attribute:emailAddress

attribute:currency

attribute:shipStatus

attribute:quantityReturned

139502841user1039252020-07-04prod001100.122USDtest@test.comUSDdelivered0
139502841user1039252020-07-04prod192315.091USDtest@test.comUSDdelivered0
139502841user1039252020-07-04prod003441

USD

test@test.comAUDdelivered0
492481058user0492452017-04-22T10:23:37Zprod9991.0050EURuser04925@test.comEURshipped10
860340254user014992018-11-30prod00224415.151AUD
USD

860340255user22012020-08-15prod110122.991USD
CADprocessing

Sample File Structure: Leveraging the IS Multiple Identity System

transactionId

attribute:emailAddress

attribute:sfcrmLeadId

purchaseDate

productId

price

quantity

attribute:currency

attribute:currency

attribute:shipStatus

attribute:quantityReturned

139502841test@test.com
2020-07-04prod001100.122USDUSDdelivered0
139502841test@test.com
2020-07-04prod192315.091USDUSDdelivered0
139502841test@test.com
2020-07-04prod003441

USD

AUDdelivered0
492481058user04925@test.com029418502498562017-04-22T10:23:37Zprod9991.0050EUREURshipped10
860340254
5617168311150902018-11-30prod00224415.151AUDUSD

860340255
9813610798105702020-08-15prod110122.991USDCADprocessing