Contents
Overview
Google provides key datasets for Trains & Buses partners via BigQuery, offering more in-depth analysis capabilities than the TAC dashboards alone. Currently, two main datasets are available:
- Transport Quality: This dataset contains the same data on price and link accuracy samples found in the TAC Quality Dashboard. It allows analysis of data quality over a longer period.
- Transport Stop Mapping: This dataset provides detailed results on how the stops submitted by partners (via the TAC interface) are matched to Google Maps features. This is essential for ensuring your locations are correctly represented.
Accessing these datasets through BigQuery has the following key advantages:
- Extended Data Retention: BigQuery datasets typically offer a longer history (e.g., the last 90 days for Quality samples) compared to the views in the TAC dashboards.
- Programmatic Access: Ability to set up custom alerts, and the flexibility to explore the data with your own dashboards.
Access
To request access to the Transport BigQuery datasets, please reach out to transport-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:
Prerequisite: You must have access to your Google Cloud project before beginning the following instructions. If you can not access your Google Cloud project, please contact your Google Cloud representative.
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 transport quality dataset.
Step 5: Go to Google BigQuery and you will see a table named ft_transport_quality. This is a link to the table.
Step 6: Click on the ft_transport_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_transport_quality` LIMIT 1000;
NOTE: Replace '<project-name> & <dataset> value accordingly.
Transport Quality Dataset
Data Model
|
Name |
Type |
Description |
|
timestamp |
Timestamp |
The time at which the sample was taken. |
|
partner_id |
String |
Your identifier. |
|
date |
String |
The day in which the sample was taken (America/Los_Angeles). |
|
is_valid_partner_link |
Bool |
Whether the link was valid. |
| origin_country |
String |
The “ISO 3166-1 alpha-2“ country code for the trip’s origin. |
|
destination_country |
String |
The “ISO 3166-1 alpha-2” country code for the trip’s destination. |
|
origin_stop_id |
String |
The ‘origin’ stop upload via feed. |
|
destination_stop_id |
String |
The ‘destination’ stop upload via feed. |
|
user_country |
String |
The country code (2-letter ISO 3166-1 e.g 'US') of the user. |
|
route |
String |
Concatenation of origin names and destination names (e.g. “Barcelona-Sants -> Seville Santa Justa Train Station”). |
|
segments |
Integer |
The total number of segments (a segment represents the portion of an itinerary that is traveled on a specific vehicle). |
|
segments_per_slice |
Integer |
The maximum number of segments per slice in the whole solution. |
|
integration_id |
String |
Your integration identifier. |
|
partner_price |
String |
The price + currency found on the partner website. |
|
google_price |
String |
Sampled price + currency (from price cache or live API). |
|
partner_live_api_price |
String |
Price from the live partner API call. |
|
price_diff_usd |
Float |
The absolute value of the price difference in USD (always represented as positive). |
|
time_to_departure_hours |
Float |
The number of hours until departure. |
|
url |
String |
The link to the trip in Google Search. |
|
partner_url |
String |
The link to the trip on your website. |
|
price_diff_percentage |
Float |
The price difference percentage (always represented as positive number). |
|
environment |
String |
How the sample was taken (e.g. 'Transport Unpriced Crawler', 'Transport Prod Crawler','Transport Qual Crawler'). |
|
is_domestic |
Bool |
Whether this is a domestic trip (all segments belong to the same country). |
|
price_compare |
String |
A STRING containing either ‘Price Drop’, ‘Price Jump’ or ‘Equal’ or 'Close'. It can also contain the term ‘Error’. |
|
error_type |
String |
The class of errors that his particular error belongs to (e.g. SOLUTION_NOT_FOUND). |
|
error_message |
String |
The error explanation (e.g. “Shallow page returned”). |
|
ranking |
Integer |
A measure of recency of the sample by error type (lower ranking is more recent). |
|
departure_timestamp |
Integer | Timestamp of the departure. |
|
arrival_timestamp |
Integer | Timestamp of the arrival. |
| departure_local_time_string |
String |
Local time of the departure (24h format, e.g “17:35”). |
| arrival_local_time_string | String | Local time of the arrival (24h format, e.g “17:35”). |
|
departure_date |
String |
Departure date in format YYYY-MM-DD in the station timezone. |
|
arrival_date |
String |
Arrival date in format YYYY-MM-DD in the station timezone. |
|
origin_name |
String | The name of the trip’s origin (e.g. ‘Venice Mestre’). |
| destination_name | String | The name of the trip’s destination (e.g. ‘Verona Porta Nuova’). |
|
shallow_partner_price |
String |
Price from the landing page (of a shallow link). |
|
live_partner_price |
String |
Price from the live partner API call. |
Metric Calculation
Below are some GoogleSQL examples for BigQuery to help you analyze your Transport Quality data. These queries demonstrate how to calculate key performance indicators such as link error rates and price discrepancy percentages. Please replace <namespace> with your own table name.
Key Performance Indicators
Calculate 'itinerary not found' and 'price discrepancy'
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(*) AS samples
FROM `<namespace>.ft_transport_quality`
GROUP BY environment;
Daily Error Rates
Track overall link errors and price discrepancies over time.
SELECT
date,
COUNT(*) AS total_samples,
AVG(IF(is_valid_partner_link, 0, 1)) AS link_error_percentage,
SAFE_DIVIDE(
COUNTIF(is_valid_partner_link AND price_diff_percentage > 0.02),
COUNTIF(is_valid_partner_link)
) AS price_discrepancy_percentage
FROM `<namespace>.ft_transport_quality`
GROUP BY date
ORDER BY date DESC;
Top Error Types
Identify the most common reasons for link failures.
SELECT
error_type,
COUNT(*) AS error_count,
COUNT(*) / SUM(COUNT(*)) OVER () AS total_errors_percentage
FROM `<namespace>.ft_transport_quality`
WHERE NOT is_valid_partner_link
GROUP BY error_type
ORDER BY error_count DESC;
Most Problematic Routes
Find specific routes (based on stop IDs) with the highest error/discrepancy frequencies.
SELECT
origin_stop_id,
destination_stop_id,
COUNT(*) AS total_samples,
AVG(IF(is_valid_partner_link, 0, 1)) AS link_error_percentage,
COUNTIF(NOT is_valid_partner_link) AS link_errors,
SAFE_DIVIDE(
COUNTIF(is_valid_partner_link AND price_diff_percentage > 0.02),
COUNTIF(is_valid_partner_link)) AS price_discrepancy_percentage,
COUNTIF(is_valid_partner_link AND price_diff_percentage > 0.02) AS price_discrepancies
FROM `<namespace>.ft_transport_quality`
GROUP BY
origin_stop_id,
destination_stop_id
HAVING link_errors > 50 AND link_error_percentage > 0.8
ORDER BY
link_errors DESC,
link_error_percentage DESC
-- -- Alternatively, we can use price discrepancies instead of link errors
-- HAVING price_discrepancies > 50 AND price_discrepancy_percentage > 0.8
-- ORDER BY
-- price_discrepancies DESC,
-- price_discrepancy_percentage DESC
LIMIT 100;
Transport Stop Mapping Dataset
Data Model
| Field | Type | Description |
|---|---|---|
|
partner_id |
String | Your identifier. |
| stop_file_uploaded_date | String | The date the partner's stop file was uploaded (YYYY-MM-DD). Note: Field presence depends on upstream data. |
| integration_id | String | Identifier for the specific data integration or feed, as configured in the Partner Center (e.g., "eu_bus", "us_trains"). |
| stop_id | String | The unique ID of the stop as provided by the partner in their feed. |
| stop_name | String | The name of the stop as provided by the partner. |
| stop_lat | Float | The latitude of the stop location as provided by the partner. |
| stop_lon | Float | The longitude of the stop location as provided by the partner. |
| match_type | String |
Describes the outcome or method of the matching process. Common values include: |
| superseded_by | Array | This column indicates whether a particular stop mapping record has been replaced by another stop_id. There can be more than one stop_id |
| is_matched | Integer |
Flag indicating if the partner stop was successfully matched to a Google Maps feature (1 for Yes, 0 for No). |
Metric Calculation
Below are some GoogleSQL examples for BigQuery to help you analyze your stop mapping data. These queries focus on the most current mapping for each stop by filtering out superseded records. Please replace <namespace> with your own table name.
Overall Match Rate
Calculate the percentage of your stops that are currently matched to a Google Maps feature.
SELECT
COUNTIF(is_matched = 1) AS matched_stops,
COUNT(*) AS total_current_stops,
SAFE_DIVIDE(COUNTIF(is_matched = 1), COUNT(*)) AS match_rate_percentage
FROM `<namespace>.ft_transport_stop_mapping`
WHERE ARRAY_LENGTH(superseded_by) = 0;
Match Rate per Integration ID
Break down the match rate by each integration_id you provide.
SELECT
integration_id,
COUNTIF(is_matched = 1) AS matched_stops,
COUNT(*) AS total_current_stops,
SAFE_DIVIDE(COUNTIF(is_matched = 1), COUNT(*)) AS match_rate_percentage
FROM `<namespace>.ft_transport_stop_mapping`
WHERE ARRAY_LENGTH(superseded_by) = 0
GROUP BY integration_id
ORDER BY integration_id;
List Unmatched Stops for Review
Get a list of all currently unmatched stops, which you can export to investigate and correct.
SELECT
integration_id,
stop_id,
stop_name,
stop_lat,
stop_lon,
match_type
FROM `<namespace>.ft_transport_stop_mapping`
WHERE is_matched = 0 AND ARRAY_LENGTH(superseded_by) = 0
ORDER BY integration_id, stop_name;
Count of Stops by Match Type
Understand the distribution of match outcomes for your stops.
SELECT
match_type,
COUNT(*) AS number_of_stops
FROM `<namespace>.ft_transport_stop_mapping`
GROUP BY match_type
ORDER BY number_of_stops DESC;