Build a baseline that respects time.

1. Set up your experiment

Imagine the task is to estimate at trip start whether a trip will last more than 20 minutes. For this deliberately simple baseline, use only the UTC hour and day of week at the start of the trip. The recorded duration supplies the label later; it must not be a feature. This toy model is for practicing the workflow, not for operational decisions.

In a billing-enabled project, create a dedicated dataset named fieldnotes_lab in the US multi-region to match the public dataset. Ensure your account can run BigQuery jobs and create models in the dataset. In each query replace YOUR_PROJECT with your project ID. Choose unique model names if you want to preserve earlier runs; CREATE OR REPLACE overwrites a model of the same name.

2. Inspect the time windows

First confirm that each period has usable records and both labels. January 2023 is training, February is validation, and March is the final test. A time split prevents later trips from being used to train the earlier-period model, but it does not automatically remove repeated drivers or other dependencies.

SELECT
  FORMAT_TIMESTAMP('%Y-%m', trip_start_timestamp, 'UTC') AS month,
  COUNT(*) AS trips,
  COUNTIF(trip_seconds > 1200) AS long_trips,
  SAFE_DIVIDE(COUNTIF(trip_seconds > 1200), COUNT(*)) AS positive_rate
FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
WHERE trip_start_timestamp >= TIMESTAMP('2023-01-01')
  AND trip_start_timestamp < TIMESTAMP('2023-04-01')
  AND trip_seconds IS NOT NULL AND trip_seconds > 0
GROUP BY month
ORDER BY month;

If a period has no data or a missing class, stop and investigate the dataset and time windows. Do not assume a result from the numbers in this article.

3. Train the January baseline

The label is derived from completed-trip duration, but duration itself is omitted from the model inputs. NO_SPLIT trains on the supplied January rows; the later windows are evaluated explicitly. Hour and day are available at trip start, subject to the timestamp and timezone assumptions of this exercise.

CREATE OR REPLACE MODEL `YOUR_PROJECT.fieldnotes_lab.taxi_duration_baseline`
OPTIONS (
  MODEL_TYPE = 'LOGISTIC_REG',
  INPUT_LABEL_COLS = ['is_long_trip'],
  DATA_SPLIT_METHOD = 'NO_SPLIT'
) AS
SELECT
  EXTRACT(HOUR FROM trip_start_timestamp AT TIME ZONE 'UTC') AS start_hour_utc,
  EXTRACT(DAYOFWEEK FROM trip_start_timestamp AT TIME ZONE 'UTC') AS start_day_utc,
  CAST(trip_seconds > 1200 AS INT64) AS is_long_trip
FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
WHERE trip_start_timestamp >= TIMESTAMP('2023-01-01')
  AND trip_start_timestamp < TIMESTAMP('2023-02-01')
  AND trip_seconds IS NOT NULL AND trip_seconds > 0;

4. Evaluate on February

Run the following evaluation once the model is ready. Review precision, recall, accuracy, F1, and ROC AUC in context. Since the positive class rate can differ between windows, compare it with the data inspection results. Accuracy alone can be misleading for an imbalanced label.

SELECT * FROM ML.EVALUATE(
  MODEL `YOUR_PROJECT.fieldnotes_lab.taxi_duration_baseline`,
  (
    SELECT
      EXTRACT(HOUR FROM trip_start_timestamp AT TIME ZONE 'UTC') AS start_hour_utc,
      EXTRACT(DAYOFWEEK FROM trip_start_timestamp AT TIME ZONE 'UTC') AS start_day_utc,
      CAST(trip_seconds > 1200 AS INT64) AS is_long_trip
    FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
    WHERE trip_start_timestamp >= TIMESTAMP('2023-02-01')
      AND trip_start_timestamp < TIMESTAMP('2023-03-01')
      AND trip_seconds IS NOT NULL AND trip_seconds > 0
  )
);

Use February for development decisions. For a simple benchmark, compare the model’s accuracy with the majority-class accuracy derived from February’s positive rate. If you change features or select a threshold, repeat validation on February and record each decision. Once you have frozen the approach, run the same evaluation query with the March window, from 2023-03-01 inclusive to 2023-04-01 exclusive. Treat March as a final test rather than repeatedly tuning against it.

5. Review limitations and clean up

To remove the model created by this lab, run the following command after substituting your project ID. Delete the dedicated dataset only if you are certain it contains no other work.

DROP MODEL IF EXISTS `YOUR_PROJECT.fieldnotes_lab.taxi_duration_baseline`;

Continue with the official BigQuery ML classification tutorial, model evaluation overview, and public taxi dataset example.