Saturday, May 14, 2022

Reconciliation reports - Hotel Center Help [gg-hotelprices-en]

Reconciliation reports

If you use the commission billing model for your ads, you must reconcile your account with Google at regular intervals. There are two aspects to this:

  • Paying your invoice (you and your TAM coordinate how you do this).
  • Uploading a CSV file (your "reconciliation report") to Google which tells us how much each invoice should be. You upload this report monthly, weekly, or every two weeks.
Important: To avoid double-charging when uploading a new reconciliation file, please ensure that it does not contain any guest stays that have already been reconciled in a previous file.

The reconciliation report lists the bookings from which commissions should be collected. It is typically auto-generated by the hotel or its technical partners.

Be sure you are sending accurate "from" and "to" conversion rates, as inverted rates will make your report incorrect. Your commission multiplied by {commission currency to billing currency conversion rate} should be equal to {commission amount in billing currency}.

Upload a reconciliation report

You can use the Hotel Center to upload a reconciliation report. Your reconciliation reports must conform to the syntax described in Create a reconciliation report.

To upload your reconciliation report using Hotel Center:

  1. In your browser, open the Hotel Center.
  2. Select Account > Commissions. The commissions reconciliation tool displays:
  3. Drag the reconciliation report into the box, or click the box to browse files on your computer. The Hotel Center validates the format of your reconciliation report.
  4. Review the summary report and correct any errors.
    • If your file is missing information, you will see an error message that includes the corrections that need to be made.
  5. Continue to validate your file until all errors are resolved.
  6. Click Confirm to send your file for final reconciliation.
Note: You can also upload a reconciliation report by using the Reconciliation Reports API.
Create a reconciliation report

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.

Validate a reconciliation report with API

Validate a reconciliation report with API

To validate, upload, and get reconciliation reports, see the Travel Partner API.

Example of an upload/validate request

{

"fileName": "booking_report.csv",

"contents":"Hotel ID,Hotel Name,Hotel Address,Hotel City,Hotel State/Region,Hotel PostalCode,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 and Spa\",123 Foo Driveway,Boston,MA,02472,US,+11234567890,2thHRTY,2016-01-04,2016-02-01,2016-02-03,1,4,213.88,USD,1,Stayed,21.39,USD,1.0,2016-06-07,Invoice Required\n\n211,\"Mabels Gabels\",45678 Bar Street,London,,KT13 0PU,GB,+440203456123,z452121A,2016-02-04,2016-02-01,2016-02-03,1,3,414.21,GBP,1.249,Stayed,64.43,USD,1.0,2016-06-07,Invoice Required\n\n311,\"No-Tell Motels\",66 Acacia Avenue,Geneva,,1211,CH,+412241820000,42,2016-03-04,2016-02-01,2016-02-03,1,2,451.15,CHF,1.05,Stayed,37.41,EUR,0.99889,2016-06-07,Invoice Required"

}

Tip

If you have guest stays that were not captured by the gtag.js (e.g. it occurred in-app) but you are able to attribute them to Google Ads, you can still include those guest stays in your reconciliation report. This will effectively allow the bidder to assume a lower cancellation rate than it otherwise would, ensuring that you are bidding competitively by accounting for conversions not counted by the gTag. Note that you will be billed for all reported guest stays included in the reconciliation report.

Reporting policy

If Google is not satisfied with the accuracy of your reporting, we may, with at least 7 days' notice and on reasonable grounds, request that you provide documents or records required in order for Google to determine whether your reports or payments made were accurate at the time. If you fail to provide such documents or records, or if Google is not satisfied that they demonstrate the accuracy of your reporting, Google may stop displaying ads for your hotel.

If the reconciliation report is overdue by more than 15 days, Google will automatically stop serving ads for Commission per stay campaigns until the file is uploaded. Once the latest file is uploaded, ads will begin to serve within 3 days.

Frequently asked questions

This section contains frequently asked questions about reconciliation reports.

How are commission (per stay) campaigns billed?

Commission (per stay) campaigns are billed on the 18th of every month based on reported guest stays in the advertiser's uploaded reconciliation report.

What can I do if I accidentally left out a guest stay for a monthly reconciliation report?

You can include the guest stay in your next monthly reconciliation report.

For example, let's say you uploaded a reconciliation report for the month of March on April 15th and accidentally left out a guest stay that completed on March 31st. In this case, you can include the March 31st guest stay in the report for the month of April (to be uploaded between May 1st - May 17th).

If something was uploaded incorrectly, you can contact us to address the issue.

Related link

No comments:

Post a Comment

Search This Blog

Review flagged email messages [MS]

Review flagged email messages Outlook for Microsoft 365 Outlook 2021 ...