Insights Dataset

On this page you will learn how the data is stored in Insights Dataset.

Overview

Data is stored in multiple tables depending on the kind of information that wants to be queried:

  • Insights: contains information from searches, quotes, bookings and revenue aggregated per buyer, seller, search date, check in date, nights, market, number of rooms, quantity of paxes and hotel by day|month
  • Insights Agg: contains information similar to Insights but with a bigger aggregation. The fields check_in, adults, children and infants have disappeared.
  • Insights Destination: contains information from searches, quotes, bookings and revenue aggregated per buyer, seller, search date, check in date, nights, market, number of rooms, quantity of paxes and location (country + Administrative Zone 1 + Administrative Zone 2 + City) by day|month.
  • Insights Destination Agg: contains information similar to Insights Destination but with a bigger aggregation. The fields check_in, adults, children and infants have disappeared.
  • Insights Hotel Destination: contains combined information of Insights Agg and Insights Destination Agg, in the same table you have metrics by hotel code and its destination, also with cancellation metrics. The fields pax_type, nights, market and num_rooms and username have disappeared.
  • Effective Bookings: contains specific information about bookings and their current status (OK or Cancelled).
  • Portfolio: contains information to know which hotels over supplier’s portfolio are being queried by the clients. This can be helpful to know which percentage over portfolio is mapped by the client.
  • Portfolio Stats: contains information to measure the evolution of the daily_portfolio_(buyer|seller) table. This can be helpful to know if you are getting better results in your portfolio optimization.

Also, some table has a different level of aggregation:

  • Daily (agregation stored every day)
  • Monthly (aggregation stored in the first day of the month)

This dataset is updated daily.


Tables

Descriptions of each table and their correspondent fields are found below:

insights_(daily|monthly)_(buyer|seller)

This table contains info from searches, quotes, bookings and revenue aggregated per buyer, seller, search date, check in date, nights, market, number of rooms, quantity of paxes and hotel by day|month.

fields

  • client_owner (STRING). Organization’s name of the client.
  • provider_owner (STRING). Organization’s name of the provider.
  • client_id (STRING). Client unique ID.
  • provider_id (STRING). Provider unique ID.
  • username (STRING). User ID in provider’s system.
  • cache (BOOLEAN). It indicates if the source of request was the client or Speed-X to load info in cache (0=Client, 1=Speed-X).
  • search_date (TIMESTAMP). Search date in UTC (format: YYYY-MM-DD hh:mm:ss).
  • check_in (TIMESTAMP). Check in date in UTC (format: YYYY-MM-DD hh:mm:ss).
  • nights (NUMBER). Length of stay.
  • market (STRING). Source market of paxes (ISO 3166-1 alpha-2).
  • num_rooms (NUMBER). Quantity of rooms.
  • adults (NUMBER). Quantity of paxes older than 12 years (12 is not included).
  • children (NUMBER). Quantity of paxes between 2 and 12 (both included).
  • infants (NUMBER). Quantity of paxes younger than 2 years (2 is not included).
  • search_ok (NUMBER). Quantity of searches that returned any available hotel for above key (search_date, check_in…).
  • search_nok (NUMBER). Quantity of searches that didn’t return any available hotels for above key.
  • quote_ok (NUMBER). Quantity of quotes that returned a correct result for above key.
  • quote_nok (NUMBER). Quantity of quotes that returned a wrong result for above key.
  • booking_ok_net (NUMBER). Quantity of confirmed bookings for above key where we have the net amount in EUR.
  • booking_ok_unknown (NUMBER). Quantity of confirmed bookings for above key where we have the amount in EUR but it may have applied commissions.
  • booking_nok (NUMBER). Quantity of failed bookings for above key.
  • min_amount_booking_net (NUMBER). Net amount of cheapest booking for above key.
  • max_amount_booking_net (NUMBER). Net amount of most expensive booking for above key.
  • tot_amount_booking_net (NUMBER). Total net amount of confirmed bookings for above key.
  • min_amount_booking_unknown (NUMBER). Amount of cheapest booking for above key when we don’t know if any commission is applied.
  • max_amount_booking_unknown (NUMBER). Amount of most expensive booking for above key when we don’t know if any commission is applied.
  • tot_amount_booking_unknown (NUMBER). Total amount of confirmed bookings for above key when we don’t know if any commission is applied.
  • hotels (STRUCT). Repeated field that contains info about hotels for above key.
    • code (STRING). Hotel unique ID.
    • search_ok_net (NUMBER). Quantity of searches with available options for above key and hotel with net price available.
    • search_ok_unknown (NUMBER). Quantity of searches with available options for above key and hotel with a price where we don’t know if any commission is applied.
    • search_nok (NUMBER). Quantity of searches without available options for above key and hotel.
    • min_amount_search_net (NUMBER). Net amount of cheapest option for that hotel.
    • max_amount_search_net (NUMBER). Net amount of most expensive option for that hotel.
    • tot_amount_search_net (NUMBER). Total net amount of searches for that hotel.
    • min_amount_search_unknown (NUMBER). Amount of cheapest option for that hotel (Commission not available).
    • max_amount_search_unknown (NUMBER). Amount of most expensive option for that hotel (Commission not available).
    • tot_amount_search_unknown (NUMBER). Total amount of searches for that hotel (Commission not available).
    • quote_ok_net (NUMBER). Quantity of quotes for above key and hotel with net price available.
    • quote_ok_unknown (NUMBER). Quantity of quotes for above key and hotel with a price where we don’t know if any commission is applied.
    • quote_nok (NUMBER). Quantity of failed quotes for above key and hotel.
    • min_amount_quote_net (NUMBER). Net amount of cheapest quote for that hotel.
    • max_amount_quote_net (NUMBER). Net amount of most expensive quote for that hotel.
    • tot_amount_quote_net (NUMBER). Total net amount of quotes for that hotel.
    • min_amount_quote_unknown (NUMBER). Amount of cheapest quote for that hotel (Commission not available).
    • max_amount_quote_unknown (NUMBER). Amount of most expensive quote for that hotel (Commission not available).
    • tot_amount_quote_unknown (NUMBER). Total amount of quotes for that hotel (Commission not available).
    • booking_ok_net (NUMBER). Quantity of bookings for above key and hotel with net price available.
    • booking_ok_unknown (NUMBER). Quantity of bookings for above key and hotel with a price where we don’t know if any commission is applied.
    • booking_nok (NUMBER). Quantity of failed bookings for above key and hotel.
    • min_amount_booking_net (NUMBER). Net amount of cheapest booking for that hotel.
    • max_amount_booking_net (NUMBER). Net amount of most expensive booking for that hotel.
    • tot_amount_booking_net (NUMBER). Total net amount of booking for that hotel.
    • min_amount_booking_unknown (NUMBER). Amount of cheapest booking for that hotel (Commission not available).
    • max_amount_booking_unknown (NUMBER). Amount of most expensive booking for that hotel (Commission not available).
    • tot_amount_booking_unknown (NUMBER). Total amount of booking for that hotel (Commission not available).

Preview

client_id provider_id cache search_date check_in nights nationality num_rooms adults children infants search_ok search_nok quote_ok quote_nok booking_ok_net booking_ok_unknow booking_nok min_amount_booking_net max_amount_booking_net tot_amount_booking_net min_amount_booking_unknown max_amount_booking_unknown tot_amount_booking_unknown hotels.code hotels.search_ok_net hotels.search_ok_unknown hotels.search_nok hotels.quote_ok_net hotels.quote_ok_unknown hotels.quote_nok hotels.booking_ok_net hotels.booking_ok_unknown hotels.booking_nok hotels.min_amount_search_net hotels.max_amount_search_net hotels.tot_amount_search_net hotels.min_amount_search_unknown hotels.max_amount_search_unknown hotels.tot_amount_search_unknown hotels.min_amount_quote_net hotels.max_amount_quote_net hotels.tot_amount_quote_net hotels.min_amount_quote_unknown hotels.max_amount_quote_unknown hotels.tot_amount_quote_unknown hotels.min_amount_booking_net hotels.max_amount_booking_net hotels.tot_amount_booking_net hotels.min_amount_booking_unknown hotels.max_amount_booking_unknown hotels.tot_amount_booking_unknown
client A provider X false 2019-01-13 00:00:00 UTC 2019-02-13 00:00:00 UTC 4 SE 1 1 null null 13 11 null null null null null null null null null null null 641443 13 null null null null null null null null null null null null null null null null null null null null null null null null null null
551568 null 11 null null null null null null null null null null null null null null null null null null null null null null null null null
client B provider Y false 2019-01-13 00:00:00 UTC 2019-01-18 00:00:00 UTC 2 CN 1 2 null null 6 1 3 null 1 null null 398.84 398.84 398.84 null null null 299 null null 1 null null null null null null null null null null null null null null null null null null null null null null null null
927 5 null null 3 null null 1 null null 198.84 598.84 2393.04 null null null 198.84 598.84 1196.52 null null null 398.84 398.84 398.84 null null null
203 null 1 null null null null null null null null null null 2548.26 2548.26 2548.26 null null null null null null null null null null null null

insights_agg_(daily|monthly)_(buyer|seller)

This table contains info similar to insights_daily_(buyer|seller) but with a bigger aggregation. The fields check_in, adults, children and infants have disappeared.

fields

  • client_owner (STRING). Organization’s name of the client.
  • provider_owner (STRING). Organization’s name of the provider.
  • client_id (STRING). Client unique ID.
  • provider_id (STRING). Provider unique ID.
  • username (STRING). User ID in provider’s system.
  • cache (BOOLEAN). It indicates if the source of request was the client or Speed-X to load info in cache (0=Client, 1=Speed-X).
  • search_date (TIMESTAMP). Search date in UTC (format: YYYY-MM-DD hh:mm:ss).
  • booking_window (NUMBER). It is an enumeration to describe how far away is the check-in date.
    • 1 = Last second (0-1 day).
    • 2 = Last minute (2-3 days).
    • 3 = About 1 week (4-7 days).
    • 4 = About 2 weeks (8-14 days).
    • 5 = About 1 month (15-30 days)
    • 6 = About 2 months (31-60 days).
    • 7 = About 3 months (61-90 days).
    • 8 = More than 3 months (90+ days).
  • nights (NUMBER). Length of stay.
  • market (STRING). Source market of paxes (ISO 3166-1 alpha-2).
  • num_rooms (NUMBER). Quantity of rooms.
  • pax_type (NUMBER). It is an enumeration to describe a combination of paxes (adults + children + infants).
    • 1 = Solo (Adults = 1, children = 0 and infants = 0).
    • 2 = Couple (Adults = 2, children = 0 and infants = 0).
    • 3 = Family (Adults = 2, children + infants > 0).
    • 4 = Group (Adults > 2).
    • 5 = Other.
  • search_ok (NUMBER). Quantity of searches that returned any available hotel for above key (search_date, check_in…).
  • search_nok (NUMBER). Quantity of searches that didn’t return any available hotels for above key.
  • quote_ok (NUMBER). Quantity of quotes that returned a correct result for above key.
  • quote_nok (NUMBER). Quantity of quotes that returned a wrong result for above key.
  • booking_ok_net (NUMBER). Quantity of confirmed bookings for above key where we have the net amount in EUR.
  • booking_ok_unknown (NUMBER). Quantity of confirmed bookings for above key where we have the amount in EUR but it may have applied commissions.
  • booking_nok (NUMBER). Quantity of failed bookings for above key.
  • min_amount_booking_net (NUMBER). Net amount of cheapest booking for above key.
  • max_amount_booking_net (NUMBER). Net amount of most expensive booking for above key.
  • tot_amount_booking_net (NUMBER). Total net amount of confirmed bookings for above key.
  • min_amount_booking_unknown (NUMBER). Amount of cheapest booking for above key when we don’t know if any commission is applied.
  • max_amount_booking_unknown (NUMBER). Amount of most expensive booking for above key when we don’t know if any commission is applied.
  • tot_amount_booking_unknown (NUMBER). Total amount of confirmed bookings for above key when we don’t know if any commission is applied.
  • hotels (STRUCT). Repeated field that contains info about hotels for above key.
    • code (STRING). Hotel unique ID.
    • search_ok_net (NUMBER). Quantity of searches with available options for above key and hotel with net price available.
    • search_ok_unknown (NUMBER). Quantity of searches with available options for above key and hotel with a price where we don’t know if any commission is applied.
    • search_nok (NUMBER). Quantity of searches without available options for above key and hotel.
    • min_amount_search_net (NUMBER). Net amount of cheapest option for that hotel.
    • max_amount_search_net (NUMBER). Net amount of most expensive option for that hotel.
    • tot_amount_search_net (NUMBER). Total net amount of searches for that hotel.
    • min_amount_search_unknown (NUMBER). Amount of cheapest option for that hotel (Commission not available).
    • max_amount_search_unknown (NUMBER). Amount of most expensive option for that hotel (Commission not available).
    • tot_amount_search_unknown (NUMBER). Total amount of searches for that hotel (Commission not available).
    • quote_ok_net (NUMBER). Quantity of quotes for above key and hotel with net price available.
    • quote_ok_unknown (NUMBER). Quantity of quotes for above key and hotel with a price where we don’t know if any commission is applied.
    • quote_nok (NUMBER). Quantity of failed quotes for above key and hotel.
    • min_amount_quote_net (NUMBER). Net amount of cheapest quote for that hotel.
    • max_amount_quote_net (NUMBER). Net amount of most expensive quote for that hotel.
    • tot_amount_quote_net (NUMBER). Total net amount of quotes for that hotel.
    • min_amount_quote_unknown (NUMBER). Amount of cheapest quote for that hotel (Commission not available).
    • max_amount_quote_unknown (NUMBER). Amount of most expensive quote for that hotel (Commission not available).
    • tot_amount_quote_unknown (NUMBER). Total amount of quotes for that hotel (Commission not available).
    • booking_ok_net (NUMBER). Quantity of bookings for above key and hotel with net price available.
    • booking_ok_unknown (NUMBER). Quantity of bookings for above key and hotel with a price where we don’t know if any commission is applied.
    • booking_nok (NUMBER). Quantity of failed bookings for above key and hotel.
    • min_amount_booking_net (NUMBER). Net amount of cheapest booking for that hotel.
    • max_amount_booking_net (NUMBER). Net amount of most expensive booking for that hotel.
    • tot_amount_booking_net (NUMBER). Total net amount of booking for that hotel.
    • min_amount_booking_unknown (NUMBER). Amount of cheapest booking for that hotel (Commission not available).
    • max_amount_booking_unknown (NUMBER). Amount of most expensive booking for that hotel (Commission not available).
    • tot_amount_booking_unknown (NUMBER). Total amount of booking for that hotel (Commission not available).

Preview

client_id provider_id cache search_date booking_window nights nationality num_rooms pax_type search_ok search_nok quote_ok quote_nok booking_ok_net booking_ok_unknow booking_nok min_amount_booking_net max_amount_booking_net tot_amount_booking_net min_amount_booking_unknown max_amount_booking_unknown tot_amount_booking_unknown hotels.code hotels.search_ok_net hotels.search_ok_unknown hotels.search_nok hotels.quote_ok_net hotels.quote_ok_unknown hotels.quote_nok hotels.booking_ok_net hotels.booking_ok_unknown hotels.booking_nok hotels.min_amount_search_net hotels.max_amount_search_net hotels.tot_amount_search_net hotels.min_amount_search_unknown hotels.max_amount_search_unknown hotels.tot_amount_search_unknown hotels.min_amount_quote_net hotels.max_amount_quote_net hotels.tot_amount_quote_net hotels.min_amount_quote_unknown hotels.max_amount_quote_unknown hotels.tot_amount_quote_unknown hotels.min_amount_booking_net hotels.max_amount_booking_net hotels.tot_amount_booking_net hotels.min_amount_booking_unknown hotels.max_amount_booking_unknown hotels.tot_amount_booking_unknown
client A provider X false 2019-01-13 00:00:00 UTC 3 4 SE 1 1 13 11 null null null null null null null null null null null 641443 13 null null null null null null null null null null null null null null null null null null null null null null null null null null
551568 null 11 null null null null null null null null null null null null null null null null null null null null null null null null null
client B provider Y false 2019-01-13 00:00:00 UTC 3 2 CN 1 2 6 1 3 null 1 null null 398.84 398.84 398.84 null null null 299 null null 1 null null null null null null null null null null null null null null null null null null null null null null null null
927 5 null null 3 null null 1 null null 198.84 598.84 2393.04 null null null 198.84 598.84 1196.52 null null null 398.84 398.84 398.84 null null null
203 null 1 null null null null null null null null null null 2548.26 2548.26 2548.26 null null null null null null null null null null null null

insights_(daily|monthly)_destination_(buyer|seller)

This table contains info from searches, quotes, bookings and revenue aggregated per buyer, seller, search date, check in date, nights, market, number of rooms, quantity of paxes and location (country + Administrative Zone 1 + Administrative Zone 2 + City) by day|month.

fields

  • client_owner (STRING). Organization’s name of the client.
  • provider_owner (STRING). Organization’s name of the provider.
  • client_id (STRING). Client unique ID.
  • provider_id (STRING). Provider unique ID.
  • username (STRING). User ID in provider’s system.
  • cache (BOOLEAN). It indicates if the source of request was the client or Speed-X to load info in cache (0=Client, 1=Speed-X).
  • search_date (TIMESTAMP). Search date in UTC (format: YYYY-MM-DD hh:mm:ss).
  • check_in (TIMESTAMP). Check in date in UTC (format: YYYY-MM-DD hh:mm:ss).
  • nights (NUMBER). Length of stay.
  • market (STRING). Source market of paxes (ISO 3166-1 alpha-2).
  • num_rooms (NUMBER). Quantity of rooms.
  • adults (NUMBER). Quantity of paxes older than 12 years (12 is not included).
  • children (NUMBER). Quantity of paxes between 2 and 12 (both included).
  • infants (NUMBER). Quantity of paxes younger than 2 years (2 is not included).
  • country (STRING). Country ISO2 code. E.g. ES (Spain)
  • destinations (STRUCT). Repeated field that contains info about destinations for above key.
    • zone_1 (STRING). Administrative first level zone. E.g. Comunidad de Madrid
    • zone_2 (STRING). Administrative second level zone E.g. Madrid city
    • city (STRING). City name. If the city is big enough this level shows the neighbourghood. E.G. Chamberí
    • search_ok_net (NUMBER). Quantity of searches with available options for above key and hotel with net price available.
    • search_ok_unknown (NUMBER). Quantity of searches with available options for above key and hotel with a price where we don’t know if any commission is applied.
    • search_nok (NUMBER). Quantity of searches without available options for above key and hotel.
    • min_amount_search_net (NUMBER). Net amount of cheapest option for that hotel.
    • max_amount_search_net (NUMBER). Net amount of most expensive option for that hotel.
    • tot_amount_search_net (NUMBER). Total net amount of searches for that hotel.
    • min_amount_search_unknown (NUMBER). Amount of cheapest option for that hotel (Commission not available).
    • max_amount_search_unknown (NUMBER). Amount of most expensive option for that hotel (Commission not available).
    • tot_amount_search_unknown (NUMBER). Total amount of searches for that hotel (Commission not available).
    • quote_ok_net (NUMBER). Quantity of quotes for above key and hotel with net price available.
    • quote_ok_unknown (NUMBER). Quantity of quotes for above key and hotel with a price where we don’t know if any commission is applied.
    • quote_nok (NUMBER). Quantity of failed quotes for above key and hotel.
    • min_amount_quote_net (NUMBER). Net amount of cheapest quote for that hotel.
    • max_amount_quote_net (NUMBER). Net amount of most expensive quote for that hotel.
    • tot_amount_quote_net (NUMBER). Total net amount of quotes for that hotel.
    • min_amount_quote_unknown (NUMBER). Amount of cheapest quote for that hotel (Commission not available).
    • max_amount_quote_unknown (NUMBER). Amount of most expensive quote for that hotel (Commission not available).
    • tot_amount_quote_unknown (NUMBER). Total amount of quotes for that hotel (Commission not available).
    • booking_ok_net (NUMBER). Quantity of bookings for above key and hotel with net price available.
    • booking_ok_unknown (NUMBER). Quantity of bookings for above key and hotel with a price where we don’t know if any commission is applied.
    • booking_nok (NUMBER). Quantity of failed bookings for above key and hotel.
    • min_amount_booking_net (NUMBER). Net amount of cheapest booking for that hotel.
    • max_amount_booking_net (NUMBER). Net amount of most expensive booking for that hotel.
    • tot_amount_booking_net (NUMBER). Total net amount of booking for that hotel.
    • min_amount_booking_unknown (NUMBER). Amount of cheapest booking for that hotel (Commission not available).
    • max_amount_booking_unknown (NUMBER). Amount of most expensive booking for that hotel (Commission not available).
    • tot_amount_booking_unknown (NUMBER). Total amount of booking for that hotel (Commission not available).

Preview

client_id provider_id provider_owner client_owner cache search_date check_in nights market num_rooms adults children infants destinations.country destinations.zone_1 destinations.zone_2 destinations.city destinations.search_ok_net destinations.search_ok_unknown destinations.search_nok destinations.quote_ok_net destinations.quote_ok_unknown destinations.quote_nok destinations.booking_ok_net destinations.booking_ok_unknown destinations.booking_nok destinations.min_amount_search_net destinations.max_amount_search_net destinations.tot_amount_search_net destinations.min_amount_search_unknown destinations.max_amount_search_unknown destinations.tot_amount_search_unknown destinations.min_amount_quote_net destinations.max_amount_quote_net destinations.tot_amount_quote_net destinations.min_amount_quote_unknown destinations.max_amount_quote_unknown destinations.tot_amount_quote_unknown destinations.min_amount_booking_net destinations.max_amount_booking_net destinations.tot_amount_booking_net destinations.min_amount_booking_unknown destinations.max_amount_booking_unknown destinations.tot_amount_booking_unknown
client A provider X Org Name 1 Org Name 1 false 2019-06-17 00:00:00 UTC 2019-06-19 00:00:00 UTC 1 ES 1 2 ES Comunidad de Madrid Provincia de Madrid Opera 508 8 641 2 1 37.87 3982.2 79735.36264325699 100.0 162.4 1049.6 212.77 212.77 425.54 212.77 212.77 212.77
client B Provider Y Org Name 2 Org Name 2 false 2019-06-17 00:00:00 UTC 2019-07-07 00:00:00 UTC 2 ES 1 2 ES Comunidad de Madrid Provincia de Madrid Bilbao 86 82 3 1 212.81982704823037 1372.92 41923.90368994989 275.23 275.23 825.69 275.23 275.23 275.23

insights_agg_(daily|monthly)_destination_(buyer|seller)

This table contains info similar to insights_daily_destination_(buyer|seller) but with a bigger aggregation. The fields check_in, adults, children and infants have disappeared.

fields

  • client_owner (STRING). Organization’s name of the client.
  • provider_owner (STRING). Organization’s name of the provider.
  • client_id (STRING). Client unique ID.
  • provider_id (STRING). Provider unique ID.
  • username (STRING). User ID in provider’s system.
  • cache (BOOLEAN). It indicates if the source of request was the client or Speed-X to load info in cache (0=Client, 1=Speed-X).
  • search_date (TIMESTAMP). Search date in UTC (format: YYYY-MM-DD hh:mm:ss).
  • booking_window (NUMBER). It is an enumeration to describe how far away is the check-in date.
    • 1 = Last second (0-1 day).
    • 2 = Last minute (2-3 days).
    • 3 = About 1 week (4-7 days).
    • 4 = About 2 weeks (8-14 days).
    • 5 = About 1 month (15-30 days)
    • 6 = About 2 months (31-60 days).
    • 7 = About 3 months (61-90 days).
    • 8 = More than 3 months (90+ days).
  • nights (NUMBER). Length of stay.
  • market (STRING). Source market of paxes (ISO 3166-1 alpha-2).
  • num_rooms (NUMBER). Quantity of rooms.
  • pax_type (NUMBER). It is an enumeration to describe a combination of paxes (adults + children + infants).
    • 1 = Solo (Adults = 1, children = 0 and infants = 0).
    • 2 = Couple (Adults = 2, children = 0 and infants = 0).
    • 3 = Family (Adults = 2, children + infants > 0).
    • 4 = Group (Adults > 2).
    • 5 = Other.
  • country (STRING). Country ISO2 code. E.g. ES (Spain)
  • destinations (STRUCT). Repeated field that contains info about destinations for above key.
    • zone_1 (STRING). Administrative first level zone. E.g. Comunidad de Madrid
    • zone_2 (STRING). Administrative second level zone E.g. Madrid city
    • city (STRING). City name. If the city is big enough this level shows the neighbourghood. E.G. Chamberí
    • search_ok_net (NUMBER). Quantity of searches with available options for above key and hotel with net price available.
    • search_ok_unknown (NUMBER). Quantity of searches with available options for above key and hotel with a price where we don’t know if any commission is applied.
    • search_nok (NUMBER). Quantity of searches without available options for above key and hotel.
    • min_amount_search_net (NUMBER). Net amount of cheapest option for that hotel.
    • max_amount_search_net (NUMBER). Net amount of most expensive option for that hotel.
    • tot_amount_search_net (NUMBER). Total net amount of searches for that hotel.
    • min_amount_search_unknown (NUMBER). Amount of cheapest option for that hotel (Commission not available).
    • max_amount_search_unknown (NUMBER). Amount of most expensive option for that hotel (Commission not available).
    • tot_amount_search_unknown (NUMBER). Total amount of searches for that hotel (Commission not available).
    • quote_ok_net (NUMBER). Quantity of quotes for above key and hotel with net price available.
    • quote_ok_unknown (NUMBER). Quantity of quotes for above key and hotel with a price where we don’t know if any commission is applied.
    • quote_nok (NUMBER). Quantity of failed quotes for above key and hotel.
    • min_amount_quote_net (NUMBER). Net amount of cheapest quote for that hotel.
    • max_amount_quote_net (NUMBER). Net amount of most expensive quote for that hotel.
    • tot_amount_quote_net (NUMBER). Total net amount of quotes for that hotel.
    • min_amount_quote_unknown (NUMBER). Amount of cheapest quote for that hotel (Commission not available).
    • max_amount_quote_unknown (NUMBER). Amount of most expensive quote for that hotel (Commission not available).
    • tot_amount_quote_unknown (NUMBER). Total amount of quotes for that hotel (Commission not available).
    • booking_ok_net (NUMBER). Quantity of bookings for above key and hotel with net price available.
    • booking_ok_unknown (NUMBER). Quantity of bookings for above key and hotel with a price where we don’t know if any commission is applied.
    • booking_nok (NUMBER). Quantity of failed bookings for above key and hotel.
    • min_amount_booking_net (NUMBER). Net amount of cheapest booking for that hotel.
    • max_amount_booking_net (NUMBER). Net amount of most expensive booking for that hotel.
    • tot_amount_booking_net (NUMBER). Total net amount of booking for that hotel.
    • min_amount_booking_unknown (NUMBER). Amount of cheapest booking for that hotel (Commission not available).
    • max_amount_booking_unknown (NUMBER). Amount of most expensive booking for that hotel (Commission not available).
    • tot_amount_booking_unknown (NUMBER). Total amount of booking for that hotel (Commission not available).

Preview

client_id provider_id provider_owner client_owner cache search_date booking_window nights market num_rooms pax_type destinations.country destinations.zone_1 destinations.zone_2 destinations.city destinations.search_ok_net destinations.search_ok_unknown destinations.search_nok destinations.quote_ok_net destinations.quote_ok_unknown destinations.quote_nok destinations.booking_ok_net destinations.booking_ok_unknown destinations.booking_nok destinations.min_amount_search_net destinations.max_amount_search_net destinations.tot_amount_search_net destinations.min_amount_search_unknown destinations.max_amount_search_unknown destinations.tot_amount_search_unknown destinations.min_amount_quote_net destinations.max_amount_quote_net destinations.tot_amount_quote_net destinations.min_amount_quote_unknown destinations.max_amount_quote_unknown destinations.tot_amount_quote_unknown destinations.min_amount_booking_net destinations.max_amount_booking_net destinations.tot_amount_booking_net destinations.min_amount_booking_unknown destinations.max_amount_booking_unknown destinations.tot_amount_booking_unknown
client A provider X Org Name 1 Org Name 1 false 2019-06-17 00:00:00 UTC 5 2 ES 1 2 ES Comunidad de Madrid Provincia de Madrid Opera 188 3 127 3 1 83.340515 1372.92 79190.58911099887 163.2 413.07836319871626 800.2783631987163 275.23 275.23 825.69 275.23 275.23 275.23
client A provider Y Org Name 2 Org Name 2 false 2019-06-17 00:00:00 UTC 4 8 CO 1 1 ES Comunidad de Madrid Provincia de Madrid Chueca 81 92 1 1 148.14701791922977 1712.7577783721138 61912.03550860302 165.32049567620578 165.32049567620578 165.32049567620578 148.14700919818134 148.14700919818134 148.14700919818134
client B provider X Org Name 3 Org Name 3 false 2019-06-17 00:00:00 UTC 8 7 CO 1 1 ES Comunidad de Madrid Provincia de Madrid Bilbao 30 10 27 1 1 89.69198537933495 2519.0036551662656 26465.12476598021 1000.0445751983597 1176.4999554248018 11059.106713024874 111.74110724792726 111.74110724792726 111.74110724792726 100.13417054461978 100.13417054461978 100.13417054461978

insights_hotel_destination_(daily|monthly)_(buyer|seller)

This table contains combined information of Insights Agg and Insights Destination Agg, in the same table you have metrics by hotel code and its destination, also with cancellation metrics. The fields pax_type, nights, market and num_rooms and username have disappeared.

fields

  • client_owner (STRING). Organization’s name of the client.
  • provider_owner (STRING). Organization’s name of the provider.
  • client_id (STRING). Client unique ID.
  • provider_id (STRING). Provider unique ID.
  • search_date (TIMESTAMP). Search date in UTC (format: YYYY-MM-DD hh:mm:ss).
  • booking_window (NUMBER). It is an enumeration to describe how far away is the check-in date.
    • 1 = Last second (0-1 day).
    • 2 = Last minute (2-3 days).
    • 3 = About 1 week (4-7 days).
    • 4 = About 2 weeks (8-14 days).
    • 5 = About 1 month (15-30 days)
    • 6 = About 2 months (31-60 days).
    • 7 = About 3 months (61-90 days).
    • 8 = More than 3 months (90+ days).
  • country (STRING). Country ISO2 code. E.g. ES (Spain)
  • zone_1 (STRING). Administrative first level zone. E.g. Comunidad de Madrid
  • zone_2 (STRING). Administrative second level zone E.g. Madrid city
  • search_ok (NUMBER). Quantity of searches that returned any available hotel for above key (search_date, booking_window…).
  • search_nok (NUMBER). Quantity of searches that didn’t return any available hotels for above key.
  • quote_ok (NUMBER). Quantity of quotes that returned a correct result for above key.
  • quote_nok (NUMBER). Quantity of quotes that returned a wrong result for above key.
  • booking_ok_net (NUMBER). Quantity of confirmed bookings for above key where we have the net amount in EUR.
  • booking_ok_unknown (NUMBER). Quantity of confirmed bookings for above key where we have the amount in EUR but it may have applied commissions.
  • booking_nok (NUMBER). Quantity of failed bookings for above key.
  • cancel_ok_net (NUMBER). Quantity of cancellations that returned a correct result for above key where we have the net amount in EUR.
  • cancel_ok_unknown (NUMBER). Quantity of cancellations that returned a correct result for above key where we have the amount in EUR but it may have applied commissions.
  • cancel_nok (NUMBER). Quantity of failed cancellations for above key.
  • tot_amount_booking_net (NUMBER). Total net amount of confirmed bookings for above key.
  • tot_amount_booking_unknown (NUMBER). Total amount of confirmed bookings for above key when we don’t know if any commission is applied.
  • tot_amount_cancel_net (NUMBER). Total net amount of bookings cancelled for above key.
  • tot_amount_cancel_unknown (NUMBER). Total amount of bookings cancelled for above key when we don’t know if any commission is applied.
  • hotels (STRUCT). Repeated field that contains info about hotels for above key.
    • code (STRING). Hotel unique ID.
    • name (STRING). Hotel name.
    • city (STRING). City name of the hotel. If the city is big enough this level shows the neighbourghood. E.G. Chamberí
    • search_ok (NUMBER). Quantity of searches with available options for above key and hotel.
    • search_nok (NUMBER). Quantity of searches without available options for above key and hotel..
    • quote_ok (NUMBER). Quantity of quotes for above key and hotel.
    • quote_nok (NUMBER). Quantity of failed quotes for above key and hotel.
    • booking_ok_net (NUMBER). Quantity of bookings for above key and hotel with net price available.
    • booking_ok_unknown (NUMBER). Quantity of bookings for above key and hotel with a price where we don’t know if any commission is applied.
    • booking_nok (NUMBER). Quantity of failed bookings for above key and hotel.
    • cancel_ok_net (NUMBER). Quantity of cancellations for above key and hotel with net price available.
    • cancel_ok_unknown (NUMBER). Quantity of cancellations for above key and hotel with a price where we don’t know if any commission is applied.
    • cancel_nok (NUMBER). Quantity of failed cancellations for above key and hotel.
    • tot_amount_booking_net (NUMBER). Total net amount of booking for that hotel.
    • tot_amount_booking_unknown (NUMBER). Total amount of booking for that hotel (Commission not available).
    • tot_amount_cancel_net (NUMBER). Total net amount of bookings cancelled for that hotel.
    • tot_amount_cancel_unknown (NUMBER). Total amount of bookings cancelled for that hotel (Commission not available).

Preview

client_owner client_id provider_owner provider_id search_date booking_window country zone_1 zone_2 search_ok search_nok quote_ok quote_nok booking_ok_net booking_ok_unknow booking_nok cancel_ok_net cancel_ok_unknow booking_nok tot_amount_booking_net tot_amount_booking_unknown tot_amount_cancel_net tot_amount\cancel_unknown hotels.code hotels.name hotels.city hotels.search_ok hotels.search_nok hotels.quote_ok hotels.quote_nok hotels.booking_ok_net hotels.booking_ok_unknown hotels.booking_nok hotels.cancel_ok_net hotels.cancel_ok_unknown hotels.cancel_nok hotels.tot_amount_booking_net hotels.tot_amount_booking_unknown hotels.tot_amount\cancel_net hotels.tot_amount\cancel_unknown
org buyer A client A org seller X provider X 2019-01-13 00:00:00 UTC 3 ES Comunidad de Madrid Provincia de Madrid 13 11 null null null null null null null null null null null null 641443 Hotel name 1 Opera 13 null null null null null null null null null null null null null
551568 Hotel name 2 Bilbao null null null null null null null null null null null null null null
org buyer B client B org seller Y provider Y 2019-01-13 00:00:00 UTC 3 ES Comunidad de Madrid Provincia de Madrid 6 1 3 null 1 null null 1 null null 398.84 null 398.84 null 299 Hotel name 3 Opera null 1 null null 1 null null 1 null null 398.84 null 398.84 null

effective_bookings_(buyer|seller)

This table contains specific information about bookings done and their current status (Ok or cancelled).

fields

  • session_id (STRING). Search date in UTC (format: YYYY-MM-DD hh:mm:ss).
  • client_id (STRING). Client unique ID.
  • provider_id (STRING). Provider unique ID.
  • client_owner (STRING). Organization’s name of the client.
  • provider_owner (STRING). Organization’s name of the provider.
  • username (STRING). User ID in provider’s system.
  • booking_date (TIMESTAMP). Booking date in UTC (format: YYYY-MM-DD hh:mm:ss).
  • checkin_date (TIMESTAMP). Checkin date in UTC (format: YYYY-MM-DD hh:mm:ss).
  • amount (NUMBER). Amount of booking in EUR.
  • commission (NUMBER). Possible values for the commission:
    • -1 = not specified
    • 0 = net price
    • X = Percentatge of the commission applied to the amount.
  • cancel_date (TIMESTAMP). Cancel date in UTC (format: YYYY-MM-DD hh:mm:ss) if the booking was cancelled else will be empty.
  • market (STRING). Source market of paxes (ISO 3166-1 alpha-2).
  • market_name (STRING). Source market’s name.
  • num_room (NUMBER). Quantity of rooms booked.
  • nights (NUMBER). Length of stay.
  • country (STRING). Country of destination (ISO 3166-1 alpha-2).
  • zone_1 (STRING). Main zone of destination.
  • zone_2 (STRING). Secondary zone of destination.
  • city (STRING). Last level of destination.
  • hotel_code (STRING). Hotel’s code in provider’s portfolio.
  • hotel_name (STRING). Hotel’s name in provider’s portfolio.
  • adults (NUMBER). Quantity of paxes older than 12 years (12 is not included).
  • children (NUMBER). Quantity of paxes between 2 and 12 (both included).
  • infants (NUMBER). Quantity of paxes younger than 2 years (2 is not included).

Preview

session_id client_id provider_id client_owner provider_owner booking_date checkin_date amount cancel_date market market_name num_room nights country country_name zone_1 zone_2 city hotel_code hotel_name username commission adults children infants
123456789012 client 1 provider 1 Org Client 1 Org Provider 1 2019-06-18 16:32:37 UTC 2019-06-20 12:00:00 UTC 71.54 null CN China 1 1 IT Italy Lazio Citta metropolitana di Roma Capitale Rome 18943 hotel 1 user 1 0.0 1 0 0

daily_portfolio_(buyer|seller)

This table is used to know which hotels over supplier’s portfolio are being queried by the clients. This can be helpful to know which percentage over portfolio is mapped by the client.

fields

  • client_owner (STRING). Organization’s name of the client.
  • provider_id (STRING). Provider unique ID.
  • hotel (STRING). Hotel unique ID.
  • hotel_name_portfolio (STRING). Hotel name.
  • country (STRING). Hotel Country Code (ISO Alpha-2 code).
  • zone_1 (STRING). Main zone of destination.
  • zone_2 (STRING). Secondary zone of destination.
  • city (STRING). Last level of destination.
  • status. It is an enumeration to describe the status of the hotel.
    • 0 = Hotel code queried but it doesn’t appear in provider’s portfolio. It can be due to a wrong mapping in client’s system.
    • 1 = Hotel code not queried but it appears in provider’s portfolio. It can be due to a mismapping in the client’s system.
    • 2 = Hotel code queried and it appears in provider’s portfolio.
    • 3 = Hotel code queried but don’t appears in TravelgateX system. It can be due to a out-of-date provider hotels information.
  • search_ok (NUMBER). Quantity of searches that returned any available hotel for above key (search_date, check_in, client_id, provider_id, username, hotel, country, zone_1,zone_2, city and status).
  • search_nok (NUMBER). Quantity of searches that didn’t return any available hotels for above key.
  • quote_ok (NUMBER). Quantity of quotes that returned a correct result for above key.
  • quote_nok (NUMBER). Quantity of quotes that returned a wrong result for above key.
  • booking_ok (NUMBER). Quantity of confirmed bookings.
  • booking_nok (NUMBER). Quantity of failed bookings for above key.
  • cancel_ok (NUMBER). Quantity of confirmed cancellations.
  • cancel_nok (NUMBER). Quantity of failed cancellations for above key.
  • revenue_net (NUMBER). Total net amount of confirmed bookings for above key.
  • revenue_unknown (NUMBER). Total amount of confirmed bookings for above key when we don’t know if any commission is applied.

Preview

client_owner provider_id hotel_name country zone_1 zone_2 city hotel_code status search_ok search_nok quote_ok quote_nok booking_ok booking_nok cancel_ok cancel_nok revenue_net revenue_unknown
Org Client 1 Provider 1 Residence Bologna CZ Hlavni mesto Praha Praha 1 Stare Mesto 336629 2 9805 3896 10 1 1 354.1
Org Client 2 Provider 2 Hotel Guaparo Suites VE Estado Carabobo Urbanizacion Guaparo 814595 1
Org Client 3 Provider 3 2363330 0 4

daily_portfolio_stats_(buyer|seller)

This table is used to measure the evolution of the daily_portfolio_(buyer|seller) table. This can be helpful to know if you are getting better results in your portfolio optimization.

fields

  • client_owner (STRING). Organization’s name of the client.
  • provider_id (STRING). Provider unique ID.
  • status. It is an enumeration to describe the status of the hotel.
    • 0 = Hotel code queried but it doesn’t appear in provider’s portfolio. It can be due to a wrong mapping in client’s system.
    • 1 = Hotel code not queried but it appears in provider’s portfolio. It can be due to a mismapping in the client’s system.
    • 2 = Hotel code queried and it appears in provider’s portfolio.
    • 3 = Hotel code queried but don’t appears in TravelgateX system. It can be due to a out-of-date provider hotels information.
  • hits (NUMBER). Quantity of hotels for above key (client_id, provider_id and status).

Preview

client_owner provider_id status hits
Client 1 Provider 1 1 146453
Client 2 Provider 2 0 150200
Client 3 Provider 3 2 14644046