This section describes how to create a reconciliation report so that you can upload it using the Hotel Center.
Basic requirements
Each reconciliation CSV file must contain:
- 1 header row with column names
- Zero or more booking details rows (one for each booking)
- (Optional) One row that specifies service fees
The CSV file format that we use for the reconciliation report conforms to RFC 4180. The following general rules apply:
- UTF-8 encoding.
- Byte order marks are allowed (but not necessary).
- Numbers should use a period "." as a decimal separator rather than a comma ",". For example, ten Euros should be formatted as "10.00" and not "10,00".
- Strings that contain commas, quotes, or carriage returns/line feeds must be enclosed in "double quotes".
- Each line must end with a CRLF.
Header row
The header row defines the column names and must contain either 23 or 28 fields in total:
- 23 fields: If you do not include service fees.
- 28 fields: If you include service fees.
You can include the optional service fees row if you charge fees that are related to collecting commissions from your hotels. This is typically a small fee for each booking for the administration of the Commissions program (usually calculated as a small charge or an extra percentage). The fields related to service fees are optional, and are not processed by Google.
The following example header row shows all 28 fields with the names exactly as they must appear:
Hotel ID,Hotel Name,Hotel Address,Hotel City,Hotel State/Region,Hotel Postal
Code,Hotel Country Code,Hotel Phone Number,Booking Reference,Booking Date and
Time,Check-in Date,Check-out Date,Number of Rooms,Number of Guests,Booking
Revenue,Booking Revenue Currency,Booking Revenue Currency to Billing Currency
Conversion Rate,Booking Status,Commission,Commission Currency,Commission Currency
to Billing Currency Conversion Rate,Payment Date,Payment Status
Optional fields:
Service Fee, Service Fee Currency, Service Fee Currency to Billing Currency Conversion Rate, Total Payment, Billing Currency
In your CSV file, the column names must be in the order that they appear in the previous example.
Booking detail rows
Booking rows contain information about each confirmed booking. This information typically indicates when a booking has been consumed, but can also indicate that a booking was cancelled or was not commissionable for other reasons. You do not pay commissions on cancelled or no-show bookings.
Each booking details row in the CSV file must contain exactly the same number of columns as the header row.
TIP: All fields are required unless otherwise noted. For an optional field that you do not enter data in, include the comma but no content.
The following table describes the columns in each booking details row:
Column |
Description |
Hotel ID |
A unique ID that you use for the booked hotel. This ID must match the same ID that you used in your Hotel List Feed. For example, "661378545442". |
Hotel Name |
The name of the booked hotel. For example, "Capybara Motel". |
Hotel Address |
The street address of the booked hotel. For example, "123 Electric Avenue". |
Hotel City |
The city in which the booked hotel is located. For example, "Cambridge". |
Hotel State/Region |
The state or region in which the booked hotel is located. For example, "MA".
This field is optional.
|
Hotel Postal Code |
A flexible postal code at which the booked hotel is located. For example, "02472-1234". |
Hotel Country Code |
The two-letter country code for the country in which the booked hotel is located. For example, "US". |
Hotel Phone Number |
Phone number of booked hotel. The format of this field is flexible. For example, "+1-617-867-5309". |
Booking Reference |
A unique string identifier for this booking. For example, "108BDYKNI". |
Booking Date and Time |
The ISO 8601-formatted date and time at which the booking occurred, in the hotel's local time, including the timezone. For example, "2016-07-07T19:43:21+08:00:". |
Check-in Date |
The date of check-in, in the hotel’s local time. Use the YYYY-MM-DD format, where YYYY is the 4-digit year, MM is the month, and DD is the day. For example, "2016-09-01". |
Check-out Date |
The date of check-out, in the hotel’s local time. Use the YYYY-MM-DD format, where YYYY is the 4-digit year, MM is the month, and DD is the day. For example, "2016-09-01". |
Number of Rooms |
The number of rooms in this booking. For example, "1". |
Number of Guests |
The number of guests in this booking. For example, "2". |
Booking Revenue |
The revenue collected from this booking. This value can be the gross or net revenue and should reflect the conversion value. For example, "1066.42". |
Booking Revenue Currency |
The three-letter currency code of the currency used for the "Booking Revenue" field. For example, "EUR". |
Booking Revenue Currency to Billing Currency Conversion Rate |
The conversion rate from the currency used in the “Booking Revenue Currency” field to the currency used in the “Billing Currency” field. This value should reflect the conversion rate that was used for the booking. For example, "1.113".
When the Booking Revenue Currency and the Billing Currency are the same, set this field to 1 .
|
Booking Status |
The status of the booking. Can be one of the following values:
- "Stayed": The booking was completed and guests stayed as expected.
- "Cancelled": The guests cancelled the booking prior to the check-in date. If you set Booking Status to "Cancelled", then set Commission to 0.
- "No-show": The guests did not stay and did not cancel the booking ahead of time. If you set Booking Status to "No-show", then set Commission to 0.
- "Non-commissionable": This booking should not include a commission. If you set Booking Status to "Non-commissionable", then set Commission to 0.
|
Commission |
The amount being paid to Google for this booking. For cancelled, no-show, or non-commissionable bookings, set the value of this field to "0". For example, "3.42". |
Commission Currency |
The three-letter currency code of the currency used in the “Commission” field. For example "USD".
|
Commission Currency to Billing Currency Conversion Rate |
The conversion rate from the currency used in the Commission Currency field to the currency used in the optional Billing Currency field. The value of this field should be the conversion rate used for the commission. For example, "0.88942".
When the Commission Currency and Billing Currency are the same, set this field to 1.
|
Payment Date |
Leave this field blank. |
Payment Status |
Set this to "Invoice Required". |
(Optional) Service Fee Fields
The fields described in this section are optional.
|
Service Fee |
The amount of the special service fee. For example, "1.23". |
Service Fee Currency |
The three-letter currency code for the currency in which the optional service fee is paid in. For example, "EUR". |
Service Fee Currency to Billing Currency Conversion Rate |
The conversion rate of the service fee to billing currency. For example, "0.88998".
When the Service Fee Currency and the Billing Currency are the same, set this field to 1.
|
Total Payment |
The total amount of the payment, including the optional service fee. For example, "1420.39". |
Billing Currency |
The three-letter currency code representing the currency in which the payment, including the service fee, was made. For example, "USD". |
Example CSV file
The following example CSV file shows the header row and 3 booking rows:
Hotel ID,Hotel Name,Hotel Address,Hotel City,Hotel State/Region,Hotel Postal
Code,Hotel Country Code,Hotel Phone Number,Booking Reference,Booking Date and
Time,Check-in Date,Check-out Date,Number of Rooms,Number of Guests,Booking
Revenue,Booking Revenue Currency,Booking Revenue Currency to Billing Currency
Conversion Rate,Booking Status,Commission,Commission Currency,Commission Currency
to Billing Currency Conversion Rate,Payment Date,Payment Status
111,"Capybara Hotel & Spa",123 Foo Driveway,Boston,MA,02472,US,+11234567890,2thHRTY,2016-01-04T12:12:12-07:00,2016-01-01,2016-01-03,1,4,213.88,USD,1,Stayed,21.39,USD,1.0,2016-06-07,Invoice Required
211,"Mabel's Gabels",45678 Bar Street,London,,KT13 0PU,GB,+440203456123,z452121A,2016-01-04T12:12:12-07:00,2016-02-01,2016-02-03,1,3,414.21,GBP,1.57,Stayed,64.43,USD,1.0,2016-06-07,Invoice Required
311,"No-Tell Motels",66 Acacia Avenue,Geneva,,1211,CH,+412241820000,42,2016-01-04T12:12:12-07:00,2016-03-01,2016-03-03,1,2,451.15,CHF,1.05,Stayed,37.41,EUR,0.99889,,Invoice Required
A comma is required to delimit all fields, even if there is no value in that field (if it is optional).
Note in this example, because the service fee information is included (as the last row), all other rows use commas to indicate empty values for those columns. If there were no service fee row, then the example could exclude the trailing commas.