Contents
- Overview
- Access
- Quality Dataset
- Booking Page Competitiveness Dataset
- Shopping Page Competitiveness Dataset
Overview
The same Google Flights data that is acessible through TAC's dashboards is also available via BigQuery, such as Quality and Booking Page Competitiveness. Accessing these datasets through BigQuery has advantages, such as:
- More quality samples - Dashboards are limited to the last few days of samples, whereas BigQuery contains the last 90 days.
- Programmatic access allows you to set up your own alerts or explore with your own dashboards.
Access
To setup a Google Flights data feed in Bigquery, please reach out to ota-help@google.com or gfs-help@google.com, expressing interest and mentioning the user emails that should have access to the data via Google Cloud Platform.
Please allow up to 1 week for us to set everything up. Once the data is ready, we will let you know, and you can access it through Analytics Hub with the following steps:
Step 1: Go to the google cloud console Analytics Hub and enable Analytics Hub (If not already enabled).
Step 2: Click on SEARCH LISTINGS
Step 3: Under Filters, select the Private checkbox. On the right panel you will see a private listing. Click on the private listing button.
Step 4: Click on ADD DATASET TO PROJECT. This will create a link for the GFS dataset.
Step 5: Go to Google BigQuery and you will see a table named, for example, "ft_gfs_quality". This is a link to the table.
Step 6: Click on the ft_gfs_quality table and then on the PREVIEW tab. You will see some sample data.
Step 7: You can also use SQL to select this data and can join with other tables using standard SQL Join.
SELECT * FROM `<project-name>.<dataset>.ft_gfs_quality` LIMIT 1000;
NOTE: Replace '<project-name> & <dataset> value accordingly.
Quality Dataset
The data is updated hourly, and the whole dataset is replaced. Learn more about the metrics in the Quality help center article.
Quality Data Model
|
Name |
Type |
Description |
|
sample_id |
Integer |
Primary key of this dataset. |
|
data_refresh_timestamp |
Integer |
Timestamp with the latest date at which the data was updated. |
|
data_version_tag |
String |
A column to accommodate future schema changes, in the format “v1”, “v2”, etc.. |
|
date |
String |
The day in which the sample was taken (America/Los_Angeles). |
|
timestamp |
Timestamp |
The time at which the sample was taken. |
|
booking_codes |
String |
The one/two letters RBD for this trip. |
|
destination_airport |
String |
The airport code for the trip’s destination. |
|
destination_country |
String |
The country code for the trip’s destination. |
|
environment |
String |
How the sample was taken (e.g. GFS Prod Crawler, GFS Price Accuracy Tag). |
|
error_message |
String |
The error explanation (e.g. “Shallow page returned”). |
|
error_type |
String |
The class of errors that his particular error belongs to (e.g. SOLUTION_NOT_FOUND). |
|
fare_basis_code_list |
String |
The fare codes for this trip. |
|
fare_product |
String |
The name of the user facing selected fare product . |
|
gfs_fare_price |
String |
The fare component of the price in GFS. This, plus gfs_fees and gfs_tax is equal to gfs_price. |
|
gfs_fees |
String |
The fees component of the price in GFS. This, plus gfs_fare_price and gfs_tax is equal to gfs_price. |
|
gfs_price |
String |
The price shown in GFS (string starting with the currency code). |
|
gfs_tax |
String |
The tax component of the price in GFS. This, plus gfs_fare_price and gfs_fees is equal to gfs_price. |
|
gfs_url |
String |
The link to the trip in Google Flights. |
|
partner_url |
String |
The link to the trip in the partner website. |
|
selected_cabin_class |
String |
The cabin class selected for the trip (e.g. “Economy”) |
|
is_domestic |
Bool |
Whether this is a domestic trip (all segments belong to the same country). |
|
is_interline |
Bool |
Whether the trip segments are marketed by multiple carriers. |
|
is_multipax |
Bool |
Whether multiple passengers were selected. |
|
marketing_carriers |
String |
Comma separated list of all the marketing carrier codes in the trip. |
|
operating_carriers |
String |
Comma separated list of all the operating carrier codes in the trip. |
|
max_ground_hours |
Decimal |
The maximum connection time in the whole solution. |
|
segments_per_slice |
Integer |
The maximum number of segments per slice in the whole solution. |
|
origin_airport |
String |
The airport code for the trip’s origin. |
|
origin_country |
String |
The country code for the trip’s origin. |
|
partner_price |
String |
The price found on the partner website (string starting with the currency code). |
|
price_compare |
String |
A string containing either ‘Price Drop’, ‘Price Jump’ or ‘Equal or within 2%’. It can also contain the term ‘Error’. |
|
price_diff_percentage |
Decimal |
The price difference percentage (always represented as positive). |
|
price_diff_usd |
Decimal |
The absolute value of the price difference in USD (always represented as positive). |
|
slices |
Integer |
The number of slices in the itinerary. |
|
source |
String |
The pricing source, which is one of: 'Google Flights Pricing', 'Live API', 'Price Through Google (GDS)', 'Cached Price Feed' or 'Price Through Google (web fares)' |
|
hours_to_departure |
Integer |
The number of hours until departure. |
|
segments |
Integer |
The total number of segments |
|
trip_type |
String |
One of ‘One Way’, ‘Round Trip’ or ‘Open Jaw’ |
|
user_country |
String |
The country code (2-letter ISO 3166-1 e.g 'US') of the user. |
|
validating_carrier |
String |
The IATA airline code for the validating carrier. |
|
device_type |
String |
The device type in the request. |
| is_valid_partner_link | Bool | Whether the link was valid. |
Quality Metrics Calculation
Below is an example on how to calculate metrics like 'itinerary not found' and 'price discrepancy'. Note that you can also filter for specific dates, environments, or others. Also, please replace <namespace> with your own table name.
SELECT
environment,
1 - AVG(IF(is_valid_partner_link, 1, 0)) AS itinerary_not_found_percentage,
IF(
SUM(IF(is_valid_partner_link, 1, 0)) > 0,
SUM(IF(is_valid_partner_link AND price_diff_percentage > 0.02, 1, 0))
/ SUM(IF(is_valid_partner_link, 1, 0)),
0) AS price_discrepancy_percentage,
COUNT(1) AS samples
FROM `<namespace>.ft_gfs_quality`
GROUP BY environment;
Booking Page Competitiveness Dataset
The data is updated daily, and contains the last 365 days, similarly to the TAC dashboard. Learn more about the metrics in the Booking Page Competitiveness help center article.
Booking Page Competitiveness Data Model
|
Name |
Type |
Description |
|
partner |
String |
Your identifier in Google Flights' systems. |
|
date |
String |
The day in which the search was taken. Standard ISO 8601. Format YYYY-MM-DD. |
|
date_timestamp |
Integer |
The timestamp in which the search was taken, in nanoseconds since the Unix epoch. |
|
origin_airport_code |
String |
IATA airport code (3 letter code e.g. ‘JFK’) for the trip’s origin. |
|
origin_city_code |
String |
The city code (3 letter code e.g. ‘NYC’) for the trip’s origin. |
|
origin_city |
String |
The city name for the trip’s origin. |
|
origin_region |
String |
The region name for the trip’s origin. |
|
origin_country |
String |
The country name for the trip’s origin. |
|
origin_subcontinent |
String |
The subcontinent name for the trip’s origin. |
|
destination_airport_code |
String |
IATA airport code (3 letter code e.g. ‘JFK’) for the trip’s destination. |
|
destination_city_code |
String |
The city code (3 letter code e.g. ‘NYC’) for the trip’s destination. |
|
destination_region |
String |
The region name for the trip’s destination. |
|
destination_city |
String |
The city name for the trip’s destination. |
|
destination_country |
String |
The country name for the trip’s destination. |
|
destination_subcontinent |
String |
The subcontinent name for the trip’s destination. |
|
user_country |
String |
The country code (2-letter ISO 3166-1 e.g 'US') of the user. |
|
user_country_name |
String |
The country name of the user. |
|
operating_airline |
String |
IATA airline codes (2 letter code e.g. ‘F9’) for the trip, comma-separated |
|
trip_type |
String |
One of ‘One way’, ‘Round trip’, ’Multi-city’ or ‘Open jaw’. |
|
days_until_departure_bucket |
String |
Days bucket between the departure date and date when search has been taken. Possible values '0 to 7 days', '8 to 14 days', '15 to 28 days', '29 to 90 days', '91 to 180 days', '181 or more days'. |
|
client_device_type |
String |
‘Desktop’ or ‘Mobile’. |
|
flight_type |
String |
‘Domestic’ or ‘International’. |
|
price_bucket_check_price |
Double |
The percentage of times when there is no price available on your booking link. Also called “Visit Site for Price”. |
|
price_bucket_check_price_weight |
Double |
The weight of price_bucket_check_price. |
|
price_bucket_only_priced_option |
Double |
The percentage of times when the price of your booking link is the only one for the itinerary. |
|
price_bucket_only_priced_option_weight |
Double |
The weight of price_bucket_only_priced_option. |
|
price_bucket_unique_lowest |
Double |
The percentage of times when there is more than one price and yours is the cheapest option. |
|
price_bucket_unique_lowest_weight |
Double |
The weight of price_bucket_unique_lowest. |
|
price_bucket_lowest |
Double |
The percentage of times when your price is the cheapest option along with other links. |
|
price_bucket_lowest_weight |
Double |
The weight of price_bucket_lowest. |
|
price_bucket_not_lowest |
Double |
The percentage of times when your price is not the cheapest option along with other links. |
|
price_bucket_not_lowest_weight |
Double |
The weight of price_bucket_not_lowest. |
|
average_positive_price_diff |
Double |
The average difference between your price and the cheapest price among other booking providers. |
|
average_positive_price_diff_weight |
Double |
The weight of average_positive_price_diff. |
|
average_negative_price_diff |
Double |
The average difference between your price and the next cheapest price among other booking providers. In this case your price is the cheapest one. |
|
average_negative_price_diff_weight |
Double |
The weight of average_negative_price_diff. |
|
top_impression_share |
Double |
The percentage of times when your booking link is one of the first links that are visible without further clicks on the booking page. |
|
top_impression_share_weight |
Double |
The weight of top_impression_share. |
|
absolute_top_impression_share |
Double |
The percentage of times when your booking link is ranked on the first top position. |
|
absolute_top_impression_share_weight |
Double |
The weight of absolute_top_impression_share. |
|
participation_rate |
Double |
The percentage of times when your booking link was displayed to the user. |
|
participation_rate_weight |
Double |
The weight of participation_rate. |
Booking Page Competitiveness Metrics Calculation
Below is an example of how to calculate metrics using weights. Weights work the same for all the metrics. In this example we will calculate the price_bucket_only_priced_option metric.
By running the query below we will see price_bucket_only_priced_option on the highest level of granularity, by all dimensions:
SELECT
date,
date_timestamp,
origin_airport_code,
origin_city_code,
origin_city,
origin_region,
origin_country,
origin_subcontinent,
destination_airport_code,
destination_city_code,
destination_city,
destination_region,
destination_country,
destination_subcontinent,
user_country,
user_country_name,
operating_airline,
trip_type,
days_until_departure_bucket,
client_device_type,
flight_type,
price_bucket_only_priced_option
FROM `<namespace>.ft_gfs_competitiveness`;
If we would like to see the same metric but aggregated only by date, operating_airline, origin_country and destination_country then we would need to take the sum of multiplication of price_bucket_only_priced_option and price_bucket_only_priced_option_weight and divide it by the sum of price_bucket_only_priced_option_weight.
We can’t divide by 0 so we also need to make sure that the denominator is above 0, if not then the result should be 0.
SELECT
date,
operating_airline,
origin_country,
destination_country,
IF(
SUM(price_bucket_only_priced_option_weight) > 0,
SUM(price_bucket_only_priced_option * price_bucket_only_priced_option_weight)
/ SUM(price_bucket_only_priced_option_weight),
0) AS price_bucket_only_priced_option
FROM `<namespace>.ft_gfs_competitiveness`
GROUP BY date, operating_airline, origin_country, destination_country;
If we would like to see the same metric but aggregated only by date and client_device_type then we do the same calculation and keep only the dimensions that we want to see.
SELECT
date,
client_device_type,
IF(
SUM(price_bucket_only_priced_option_weight) > 0,
SUM(price_bucket_only_priced_option * price_bucket_only_priced_option_weight)
/ SUM(price_bucket_only_priced_option_weight),
0) AS price_bucket_only_priced_option
FROM `<namespace>.ft_gfs_competitiveness`
GROUP BY date, client_device_type;
Shopping Page Competitiveness Dataset
The data is updated daily, and contains the last 365 days, similarly to the TAC dashboard (or from Aug 1st 2024, whichever start date is later). Learn more about the Shopping Competitiveness metrics in the help center article.
Shopping Page Competitiveness Data Model
|
Name |
Type |
Description |
|
partner |
String |
Your partner identifier with Google. |
|
date |
String |
The day in which the search was taken. Standard ISO 8601. Format YYYY-MM-DD. |
|
date_timestamp |
Timestamp |
The timestamp in which the search was taken, in microseconds. |
|
origin |
String |
IATA airport codes (3 letter code e.g. ‘JFK’) for the trip’s origin. If the search expanded to multiple airports, they are separated with "|". |
|
origin_cities |
String |
The city names for the trip’s origin. If the search expanded to multiple cities, they are separated with "|". |
|
origin_countries |
String |
The country names for the trip’s origin. If the search expanded to multiple countries, they are separated with "|". |
|
origin_continents |
String |
The continent names for the trip’s origin. If the search expanded to multiple continents, they are separated with "|". |
|
destination |
String |
IATA airport codes (3 letter code e.g. ‘JFK’) for the trip’s destination. If the search expanded to multiple airports, they are separated with "|". |
|
destination_cities |
String |
The city names for the trip’s destination. If the search expanded to multiple cities, they are separated with "|". |
|
destination_countries |
String |
The country names for the trip’s destination. If the search expanded to multiple countries, they are separated with "|". |
|
destination_continents |
String |
The continent names for the trip’s destination. If the search expanded to multiple continents, they are separated with "|". |
|
is_domestic |
Bool |
TRUE if the trip is domestic, FALSE if the trip is international. |
|
avg_rank |
Double |
The average rank in the shopping queries (top rank is 1 and the highest appearance in the page is the one taken into account). |
|
avg_rank_weight |
Double |
The weight of avg_rank. Useful for aggregations. |
|
avg_in_best_flights_rank |
Double |
The average rank in the shopping query when you appeared in the best flights section. |
|
avg_in_best_flights_rank_weight |
Double |
The weight of avg_in_best_flights_rank. Useful for aggregations. |
|
in_best_flights_when_present |
Double |
The percentage of time that you were in the top flights section. |
|
in_best_flights_when_present_weight |
Double |
The weight of in_best_flights_when_present. Useful for aggregations. |
|
top_rank |
Double |
How often you were in the top 1 rank in the Google Flights Shopping results page. |
|
top_rank_weight |
Double |
The weight of top_rank. Useful for aggregations. |
|
top_3_rank |
Double |
How often you were in the top 3 rank in the Google Flights Shopping results page. |
|
top_3_rank_weight |
Double |
The weight of top_3_rank. Useful for aggregations. |
|
top_5_rank |
Double |
How often you were in the top 5 rank in the Google Flights Shopping results page. |
|
top_5_rank_weight |
Double |
The weight of top_5_rank. Useful for aggregations. |
|
top_10_rank |
Double |
How often you were in the top 10 rank in the Google Flights Shopping results page. |
|
top_10_rank_weight |
Double |
The weight of top_10_rank. Useful for aggregations. |
|
outside_top_10_rank |
Double |
How often you were outside of the top 10 rank in the Google Flights Shopping results page. |
|
outside_top_10_rank_weight |
Double |
The weight of outside_top_10_rank. Useful for aggregations. |
|
participation |
Double |
How often you participated in the results page. |
|
participation_weight |
Double |
The weight of participation. Useful for aggregations. |
|
pct_of_all_outbound_selections |
Double |
How many outbound selections (clicks) went to you. |
|
pct_of_all_outbound_selections_weight |
Double |
The weight of pct_of_all_outbound_selections. Useful for aggregations. |
|
pct_of_outbound_selections_given_partner_was_present |
Double |
How many outbound selections (clicks) went to you, given that you appeared in the results page. |
|
pct_of_outbound_selections_given_partner_was_present_weight |
Double |
The weight of pct_of_outbound_selections_given_partner_was_present. Useful for aggregations. |
|
pct_of_outbound_selections_given_partner_in_best_flights |
Double |
How many outbound selections (clicks) went to you, given that you appeared in the "top flights" section of the results page. |
|
pct_of_outbound_selections_given_partner_in_best_flights_weight |
Double |
The weight of pct_of_outbound_selections_given_partner_in_best_flights. Useful for aggregations. |
Shopping Page Competitiveness Metrics Calculation
Below is an example of how to calculate metrics using the provided weights. Weights work the same way for all metrics. In this example we will calculate the avg_rank.
By running the following query you will see the avg_rank on the highest level of granularity, i.e. by all dimensions: date, date_timestamp, origin, origin_cities, origin_countries, origin_continents, destination, destination_cities, destination_countries, destination_continents, is_domestic.
SELECT
date,
date_timestamp,
origin,
origin_cities,
origin_countries,
origin_continents,
destination,
destination_cities,
destination_countries,
destination_continents,
is_domestic,
avg_rank
FROM `<namespace>.ft_gfs_shopping_competitiveness`
LIMIT 1000;
To compute avg_rank by date, origin, origin_cities, destination and destination_cities only, we take the sum of multiplication of avg_rank and avg_rank_weight and divide it by the sum of avg_rank_weight.
We also make sure that the denominator is above 0.
SELECT
date,
origin,
origin_cities,
destination,
destination_cities,
IF(
SUM(avg_rank_weight) > 0,
SUM(avg_rank * avg_rank_weight)
/ SUM(avg_rank_weight),
0) AS avg_rank
FROM `<namespace>.ft_gfs_shopping_competitiveness`
GROUP BY date, origin, origin_cities, destination, destination_cities;
To compute the same metric but aggregated only by date, we do the same calculation and keep only the dimensions that we want to see:
SELECT
date,
IF(
SUM(avg_rank_weight) > 0,
SUM(avg_rank * avg_rank_weight)
/ SUM(avg_rank_weight),
0) AS avg_rank
FROM `<namespace>.ft_gfs_shopping_competitiveness`
GROUP BY date, is_domestic;