API Tiering Process

Every six months, the Strategic Partnerships Operations team starts the API Tiering process. This process is managed in a master Google sheet, which will be updated yearly.

Process to update the master Google sheet:

• Check if you have access to the API Bookings & In-System Affiliate Bookings report. If you don’t, reach out to your Manager or Strategic Partnership Operations to request access.

• Go to the API Bookings & In-System Affiliate Bookings Report.

  • On the right, select the following Booking Date toggle and set the date range for the last 12 months.

  

• The report will refresh. You can now click on Download.

• Choose Crosstab as the file format. This type will open in CSV.
• Open the file in the master Google sheet.

• Select all data on the spreadsheet and pivot the data in a new sheet.

  • Part 1: Create a pivot (SP – Related should include all except the blank) with the following attributes. Add it to a new sheet, named Multi-currency Leads.
    • SP – Related Leads
    • SUM of #In-System Affiliate Bookings
    • SUM of #API Bookings
    • SUM of Total Paid
  • Part 2: Create a pivot (SP – Related should be blank) with the following attributes. Add it to a new sheet, named No Related Leads.
    • Affiliate Shortnames
    • SUM of #In-System Affiliate Bookings
    • SUM of #API Bookings
    • SUM of Total Paid

  • Add new columns to the two new sheets in the same manner previous bi-annual Tiering was done (Total Paid and API Usage).

    • The Points (Column F and I) formula should be copied over from previous bi-annual Tiering.
    • Final Points will be a sum of column F and I.

    • Final Tier: copy the formula from previous bi-annual Tiering. The tiering will be based on the below table:

      Tier 1	0 – 8 points
      Tier 2	9 – 18 points
      Tier 3	19 – 38 points
      Tier 4	39 points and above

Example of a company where total paid is under $250,000:

Tours4fun (External API) has a Total Paid of $44,767.20, which is under 250,000.

Total Paid: Tier 1 (4 points)

Example of a company where total paid is above $250,000:

Tiqetsusd (External API) | Data Extracted from Tableau:

We need this step to determine which API Partners have not generated a booking over the last 12 months and to verify this information on the FH Dashboard.

Process to complete the comparison:

• Report from Close:

  • Search Close using this Query and export the data.
  • Once the report is ready, import the report to a new sheet in the master Google sheet. Rename the sheet Close Data.

  • Move the custom.FH Shortname custom field to column C and insert two new columns:

    i. Column D – Tableau vs. Close

    ii. Column E – New Tier

  • Pivot the data from the Tableau Report to have the Affiliate shortnames in rows. Copy all the Affiliate shortnames to a new column as values.

  • Do a V-lookup for the custom.FH Shortname custom field against the Affiliate Shortname from the Tableau Report in column D.
  • Filter column D to see the shortnames that returned #N/A. Complete a spot-check of these shortnames on the Dashboard to see if any bookings were completed in the last 12 months.
  • If no bookings were generated in the last 12 months, the API Partner will be a Tier 1. If a booking was generated, an investigation must be completed.
  • For all API Partners with a match, complete a V-lookup on API Tiering from the Multi-currency Leads and No Related Leads sheets. Add this information to column E.
  • Filter column E to see if there any #N/A, then investigate the reasons. If the reasons are common, include them in the notes column.

The Channel API (also known internally as the Reseller API) enables seamless integrations between FareHarbor and online travel agencies (OTAs) such as Expedia and TripAdvisor Experiences (Viator). This guide explains how tiering works and how to determine a partner’s tier based on their total paid bookings over the last 12 months. How Tiering Works

Channel Managers API tiering is based on the Total Paid value across the past 12 months. This value determines the business tier for each partner, which is used internally to assess and manage OTA performance and engagement levels.

• Tableau Report – Link Here
• Close Query: Link Here

How to Calculate Tiers

• Open the Channel Manager Bookings Report in Tableau
• On the right panel, toggle Booking Date and set it to the past 12 months.
• Export the Bookings by Channel Manager report:
• Click Download
• Select Crosstab format (opens as a CSV)
• Open the downloaded CSV in the master Google Sheet.
• Add a new column called Final Tier.
• Use the following formula to calculate the tier: =INDEX(‘Total Paid Index’!$B$21:$B$28, MATCH(B2, ‘Total Paid Index’!$A$2:$A$9, 1)
• Once tiers are calculated, compare the new tier to the previous period to identify any changes.
• Once tier changes have been reviewed and confirmed, the next step is to proceed to Phase 3.

Communication to SP Managers – API connections

Once the tiering process is complete, the SP Operations Team will send the SP Managers – API Connections a message with a link to a clean Google sheet, which will compare the old tiering to the new tiering. The following criteria should be used:

• If the API Partner tiered up, indicate with green.
• If the API Partner tiered down, indicate with red.
• If the API Partner remained the same, indicate with gray.

Close updates

The Strategic Partnerships Operations team will import a Google sheet with the API Partner’s shortname and tier. Only the main API Partner leads will be updated with the tier custom field. The multiple currency leads will not be updated. Here are the steps to update Close:

1. Open a new Google sheet and rename it to the month of their API Tiering. For instance, “API Tiering – October 2023” (see image below).
2. Rename column A to custom.FH Shortname.

3. Rename column B to custom.SP – API Partner Tier.

   

• From the File dropdown, download the spreadsheet as a CSV.
• Open Close and upload the CSV.
• Click Next and pay attention to the following:
  • If a match is found, select Replace Lead Information in Close.
  • If a lead doesn’t already exist, select Skip the Lead.
• Finally, click on Start Importing.

• Pulls availability
• Pushes bookings
• Can cancel or rebook through the API
• Honors our rate limit (approximately 3100 requests per 5 minutes)
• Routine refresh periods as laid out by the Partnerships team
  • Make requests to populate or update their cache with general availability data for the current and next couple of months (or further into the future) at least twice a month.
  • Make requests to update their cache with availability data for the current week (and perhaps next week or next few weeks) at least three times a week.
  • Make requests to update their cache with availability data for the current day (and perhaps next day or next few days) several times a day.

• Includes Tier 1 criteria
• Able to accommodate per group AND per person pricing on their platform
• Displays customer type minimums and maximums (not just availability capacity)

• Includes all Tiers 1 and 2 criteria
• Connects to FareHarbor Custom Fields
• Connects to Transportation Module
• Note: Tier 4 consists of mostly Channel Partners, for which these do not always apply.