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

Interaction Studio supports ingesting booking data to associate hotel reservations with individual users. Bookings may be updated by sending records with the same bookingId and all associated records. Files should be sorted by bookingId, but Interaction Studio also provides the ability to sort if needed. 

This Article Explains

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

Sections in this Article

Requirements and Schema

Overview

Each booking is made up of a bookingId that allows key booking information such as productId, check-in/out, price, property, party size and more to be applied to the booking. The bookingId is also leveraged in the Booking Add-On ETL to apply additional add-on line-items to the booking. All records of a booking in the Booking ETL must have the same user identifier and purchaseDate values. If there are different customer identifiers listed against the same bookingId, a duplicate booking will be created. A matching bookingId does not trigger a user merge. Every record in a transaction must have a productId, price, and quantity. 

As mentioned in the overview, files should be sorted by bookingId. File sorting can be either performed prior to delivery to Interaction Studio (client responsibility) or sorting can be enabled for the Booking ETL in the Gear Configuration screen by marking the Booking 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.


Tracking a Booking and Booking Add-On via the Web SDK

The sitemap can be configured to track a booking transaction (simply leverage the order object where the order ID would map to the booking Id), but booking metadata like check-in/out date, adults, children, etc. can only be updated via the booking ETL. To update the meta-data on a booking transaction captured via the web SDK, make sure that the booking ID in the ETL matches the one that was captured on the site. You are not able to update a booking that is brought in via ETL through the sitemap. In order to associate add-ons to a booking via the web SDK, the add-ons must be purchased on the same order as the booking as line items for that booking. If add-ons are purchased separately from the original booking, they can only be tied to the parent booking ID via the booking add-on ETL.


Bookings With Multiple Line-Items

For scenarios where multiple rooms are tied to a single bookingId, you would simply have the same bookingId appear on multiple lines. Rows with matching bookingId's are treated as a single booking transaction. The user identifier, booking date, property, & check-in/out date and time need match for all line items associated to a single booking. 


Updating a Booking

If you would like to update a booking with new information (eg. if Check-In Time was not provided at the initial booking, but is now available), the entire contents of the booking must be present in the file, meaning any and all line items of the booking must be included, even if they are not changing. Any records previously stored for the booking in Interaction Studio will be removed if a file is received that has a bookingId which reflects a previously recorded booking.


Booking Status

Booking Status is the current state of a booking. Booking States are customizable to match how a client considers the state of the booking. In order for these values to be referenced by the booking status Hotel Rule, an admin needs to navigate to the configuration screen of the Hotel Gear and enter the corresponding states. After entering the states, they will become available in a drop-down in the booking status rule and look for individuals with a matching booking status (Additional information on configuring booking states is available here). To update the Status of a booking, all line items associated to the booking need to have the updated attribute:bookingStatus column set. Similar to updating a booking with new information, the entire contents of the booking must be present in the file even if the only value that is changing is the Booking Status.



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. 

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 Booking ETL expects each booking to potentially have multiple line items associated with it. By default, the Interaction Studio parser expects that booking files are sorted by bookingId. If the file needs to be sorted, the Booking ETL can be sorted by an Admin in the Gear Configuration screen and marking the box to enable bookingETLSortBeforeGrouping. This will result in the file being sorted prior to loading the data into Interaction Studio.


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


File 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 Interaction Studio 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



bookingIdRequired: The bookingId field is required for every record in the file. This represents a unique identifier for an individual booking. All line items from a booking must share the same booking ID. It is required that files be sorted by bookingId so that all records in a file that share a bookingId are represented in consecutive rows.860340254255String
bookingDateRequired: ISO 8601 Date time string for when the booking occurred. The first record read for a booking defines the date which is set on the overall order. All dates are stored in UTC time only.

2020-10-15

2020-01-09T11:24:59Z

1023Date
productIdRequired: Represents the product in the catalog which is being booked 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
attribute:propertyRequired: The property name that is associated with the booking. This property name will match the property catalog object that is stored against the catalog item being booked in the product catalog.Downtown Main Hotel255String
attribute:bookedCheckInDateRequired: ISO 8601 Date time string for the scheduled check-in date of the booking. Multiple line items for the same booking must have the same check-in date.

2020-10-15

2020-01-09T11:24:59Z

1023Date
attribute:bookedCheckOutDateRequired: ISO 8601 Date time string for the scheduled check-out date of the booking. Multiple line items for the same booking must have the same check-out date.

2020-10-15

2020-01-09T11:24:59Z

1023Date
attribute:adultsRequired: The number of adults on the booking.1, 21023Integer
System Fields



attribute:checkInTimeISO 8601 Date time string for the scheduled check-in time of the booking. If no value is provided, the system will assign the required check-in date value until a check-in time becomes available and is assigned at a later date via a subsequent Booking ETL job. Notes on updating a booking are provided at the top of this document. Multiple line items for the same booking must have the same check-in time.

2020-10-15

2020-01-09T11:24:59Z

1023Date
attribute:checkOutTimeISO 8601 Date time string for the scheduled check-out time of the booking. If no value is provided, the system will assign the required check-out date value until a check-in time becomes available and is assigned at a later date via a subsequent Booking ETL job. Notes on updating a booking are provided at the top of this document. Multiple line items for the same booking must have the same check-out time.

2020-10-15

2020-01-09T11:24:59Z

1023Date
attribute:childrenThe number of children on the booking. If no value is present, this will default to 0.2, 31023Integer
attribute:bookingStatusThe current state of the booking. These states are customizable to match how a client considers the state of the booking. In order for these values to be referenced by the booking status Hotel Rule, an admin needs to navigate to the configuration screen of the Hotel Gear and enter the corresponding states. After entering the states, they will become available in a drop-down in the booking status rule and look for individuals with a matching booking status. The value in the ETL and in the gear configuration screen are NOT case sensitive. Additional information on how to set up the booking states in the Hotel Gear is available here.Reserved, Cancelled, In-House, No Show255String
attribute:currency

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 Setup screen for that dataset. Must be in ISO 4217 format, 3 Uppercase letters.

USD, AUD, EUR3String

Sample File

Sample File Download booking-2020-10-16_00-00-00.csv (For clients that are NOT leveraging Interaction Studio's multiple identities system)

bookingIduserIdbookingDateproductIdattribute:propertypricequantityattribute:bookedCheckinDateattribute:bookedCheckOutDateattribute:checkInTimeattribute:checkOutTimeattribute:adultsattribute:childrenattribute:bookingStatus
ABCDE12345user1039252020-10-15hotelOrRoomRateId1hotelProperty1299.9912020-10-202020-10-212020-10-20T16:30:00Z2020-10-21T10:00:00Z20reserved
ANIEE23456user0492452020-10-15hotelOrRoomRateId2hotelProperty2649.9912020-10-222020-10-252020-10-22T16:30:00Z2020-10-25T10:00:00Z22reserved
OBOKJ70937user014992020-10-15hotelOrRoomRateId5hotelProperty350012020-10-272020-10-282020-10-27T12:30:00Z2020-10-28T10:00:00Z10changed
BNKUV18475user22012020-10-15hotelOrRoomRateId5hotelProperty138712020-10-292020-10-302020-10-29T16:30:00Z2020-10-30T10:00:00Z10checked-in
BMCBN46783user239052020-10-16hotelOrRoomRateId2hotelProperty2299.9912020-10-292020-10-302020-10-29T16:30:00Z2020-10-30T10:00:00Z40cancelled


Sample File Download booking-2020-10-17_00-00-00.csv (For clients leveraging Interaction Studio's multiple identities system)

bookingIdattribute:emailAddressattribute:sfcrmLeadIdbookingDateproductIdattribute:propertypricequantityattribute:bookedCheckinDateattribute:bookedCheckOutDateattribute:checkInTimeattribute:checkOutTimeattribute:adultsattribute:childrenattribute:bookingStatus
ABCDE12345jdoe1@test.com
2020-10-15hotelOrRoomRateId1hotelProperty1299.9912020-10-202020-10-212020-10-20T16:30:00Z2020-10-21T10:00:00Z20reserved
ANIEE23456jdoe2@test.com
2020-10-15hotelOrRoomRateId2hotelProperty2649.9912020-10-222020-10-252020-10-22T16:30:00Z2020-10-25T10:00:00Z22reserved
OBOKJ70937jdoe3@test.com
2020-10-15hotelOrRoomRateId5hotelProperty350012020-10-272020-10-282020-10-27T12:30:00Z2020-10-28T10:00:00Z10changed
BNKUV18475
029418502498562020-10-15hotelOrRoomRateId5hotelProperty138712020-10-292020-10-302020-10-29T16:30:00Z2020-10-30T10:00:00Z10checked-in
BMCBN46783
5617168311150902020-10-16hotelOrRoomRateId2hotelProperty2299.9912020-10-292020-10-302020-10-29T16:30:00Z2020-10-30T10:00:00Z40cancelled