You are here:
Transaction ETL Data Feed
Use the Transaction ETL data feed to bulk upload transactions to associate purchases of items with individual user profiles.
About Ingesting Transaction Data
This ETL data feed imports transactions into Personalization and associates them with user profiles by either assigning to an existing user profile or creating a user profile when there isn’t a matching one.
Each transaction consists of a list of consecutive records for individual line items that all have the same transactionId. All records of a transaction must have the same userId and purchaseDate values. Each record in a transaction must have a productId, price, and quantity.
Sort the file by transaction ID. You can sort the file yourself before uploading it, or you can enable sorting on the Gear Configuration page by selecting the Transaction ETL Sort Before Grouping option. If the file isn't sorted, Personalization doesn’t upload it, so you must sort and resubmit the file to process it.
When updating a transaction with new information, the entire contents of the transaction must be present in the file, even if they aren’t changing. Personalization overwrites any records previously stored for the transaction if a file contains a transactionId for a previously recorded transaction. Updates to existing orders do not update statistics derived from the original order, such as Lifetime Value or daily purchase statistics.
Requirements
- ETL files must be in a CSV file format that adheres to the schema for the data feed. Files that don’t follow the file naming conventions or the appropriate schema result in errors and fail to process.
- Field names that begin with
attribute:must match the custom attribute names on the User Attributes tab of the Settings > Attributes page.
Filename Format
transaction-YYYY-MM-DD_HH-MM-SS.csv
Schema
| Field Name | Description | Example Values | Maximum Length | Data Type |
|---|---|---|---|---|
|
Required. Only unique identities can be included and updated through an ETL feed. If you aren’t using Personalization's multiple identities system, a user ID must be included. This ID must be one that is tracked within the system so that the events can be tied to the specific user profile. If you're using Personalization's multiple identities system, userId isn’t referenced in ETL files. At least one identity attribute is required. Multiple identity attributes can be included for a single user by including multiple columns in the file. There is a limit of five identity attributes for the Transaction ETL. The correct header format for identity attributes is |
user168515262 jdoe@test.com attribute:emailAddress attribute:sfmcContactKey attribute:customerId attribute:sfcrmContactId attribute:sfcrmLeadId |
120 |
String |
transactionId
|
Required. Represents a unique identifier for an individual purchase. All line items from a transaction must have the same transaction ID. |
860340254 |
255 |
String |
purchaseDate
|
Required. An ISO 8601 date and time string for when the transaction occurred. The first record for a transaction defines the date for the overall order. All dates are stored in UTC time. Timezone offsets aren’t supported. |
2022-04-12 2022-04-12T11:24:59Z |
1023 |
String |
productId
|
Required. Represents the product in your catalog that was purchased in the transaction. If the ID doesn’t match an existing product in your catalog, Personalization creates an item with the productId in the catalog. |
prod001 |
255 |
String |
price
|
Required. The unit price charged to the user. This field is multiplied by the quantity to determine the total value of this line item. For example, if the price is $1.10 and the quantity is 3, the total value of the line item is $3.30. Use a period as the decimal separator. Don't use thousands separators. |
150 63.25 10 |
1023 |
Decimal |
quantity
|
Required. Represents the net quantity purchased. This field is multiplied by the price to determine the total cost of that line item in the transaction. Then all line items are added to determine the total value of the order. |
1 50 100 |
1023 |
Integer |
attribute:currency
|
An ISO 4217 currency code of three uppercase letters for the transaction. The currency must be consistent across all records in a transaction. If no currency is provided, the currency defaults to the currency for that dataset. |
USD CAD EUR |
3 |
String |
attribute:shipStatus
|
Represents the status of the order. Possible values are: shipped, delivered, processing |
shipped delivered processing |
1023 |
String |
attribute:quantityReturned
|
Represents the number of items that have been returned. |
0 25 100 |
1023 |
String |
Sample File Structure: Not using the multiple identities system
| transactionId | userId | purchaseDate | productId | price | quantity | attribute:currency | attribute:emailAddress | attribute:shipStatus | attribute:quantityReturned |
139502841 |
user103925 |
2022-04-12 |
prod001 |
100.12 |
2 |
USD |
test@test.com |
processing |
0 |
139502841 |
user103925 |
2022-04-12 |
prod001923 |
15.09 |
1 |
USD |
test@test.com |
processing |
0 |
139502841 |
user103925 |
2022-04-12 |
prod005 |
44 |
1 |
USD |
test@test.com |
processing |
0 |
492481058 |
user049245 |
2022-04-12T10:23:37Z |
prod999 |
1.00 |
50 |
EUR |
user04925@test.com |
shipped |
10 |
860340254 |
user01499 |
2022-01-30 |
prod002244 |
15.15 |
3 |
AUD |
delivered |
||
860340255 |
user2201 |
2022-03-15 |
prod1101 |
22.99 |
2 |
CAD |
delivered |
Sample File Structure: Using the multiple identities system
| transactionId | attribute:emailAddress | attribute:sfcrmLeadId | purchaseDate | productId | price | quantity | attribute:currency | attribute:shipStatus | attribute:quantityReturned |
139502841 |
test@test.com |
2022-04-12 |
prod001 |
100.12 |
2 |
USD |
processing |
0 |
|
139502841 |
test@test.com |
2022-04-12 |
prod001923 |
15.09 |
1 |
USD |
processing |
0 |
|
139502841 |
test@test.com |
2022-04-12 |
prod005 |
44 |
1 |
USD |
processing |
0 |
|
492481058 |
user04925@test.com |
02941850249856 |
2022-04-12T10:23:37Z |
prod999 |
1.00 |
50 |
EUR |
shipped |
10 |
860340254 |
561716831115090 |
2022-01-30 |
prod002244 |
15.15 |
3 |
AUD |
delivered |
||
860340255 |
981361079810570 |
2022-03-15 |
prod1101 |
22.99 |
2 |
CAD |
delivered |
