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.
| user168515262, jdoe@test.com | 120 | String |
| Required Fields | ||||
| transactionId | Required: 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. | 860340254 | 255 | String |
| 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 | 1023 | Date |
| productId | Required: 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 | 255 | String |
| price | Required: 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, 10 | 1023 | Decimal |
| quantity | Required: 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, 100 | 1023 | Integer |
| 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, EUR | 3 | String |
| attribute:shipStatus | Optional: An enum of string values representing the status of the order. Possible values are: shipped, delivered, processing | shipped delivered processing | 1023 | string |
| attribute:quantityReturned | Optional: A potential attribute representing the number of items in the line that have been returned. | 100, 25, 0 | 1023 | String |
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 |
|---|---|---|---|---|---|---|---|---|---|---|
| 139502841 | user103925 | 2020-07-04 | prod001 | 100.12 | 2 | USD | test@test.com | USD | delivered | 0 |
| 139502841 | user103925 | 2020-07-04 | prod1923 | 15.09 | 1 | USD | test@test.com | USD | delivered | 0 |
| 139502841 | user103925 | 2020-07-04 | prod003 | 44 | 1 | USD | test@test.com | AUD | delivered | 0 |
| 492481058 | user049245 | 2017-04-22T10:23:37Z | prod999 | 1.00 | 50 | EUR | user04925@test.com | EUR | shipped | 10 |
| 860340254 | user01499 | 2018-11-30 | prod002244 | 15.15 | 1 | AUD | USD | |||
| 860340255 | user2201 | 2020-08-15 | prod1101 | 22.99 | 1 | USD | CAD | processing |
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 |
|---|---|---|---|---|---|---|---|---|---|---|
| 139502841 | test@test.com | 2020-07-04 | prod001 | 100.12 | 2 | USD | USD | delivered | 0 | |
| 139502841 | test@test.com | 2020-07-04 | prod1923 | 15.09 | 1 | USD | USD | delivered | 0 | |
| 139502841 | test@test.com | 2020-07-04 | prod003 | 44 | 1 | USD | AUD | delivered | 0 | |
| 492481058 | user04925@test.com | 02941850249856 | 2017-04-22T10:23:37Z | prod999 | 1.00 | 50 | EUR | EUR | shipped | 10 |
| 860340254 | 561716831115090 | 2018-11-30 | prod002244 | 15.15 | 1 | AUD | USD | |||
| 860340255 | 981361079810570 | 2020-08-15 | prod1101 | 22.99 | 1 | USD | CAD | processing |
Overview
Content Tools