Skip to main content

Gap Fill

GapFillSpec estimates sale and delivery dates for vehicles caught in a known collection gap — a date range over which the upstream source stopped collecting, or produced data known to be unreliable. Ranges are inclusive of both endpoints.

It applies to inventory data where first_seen is the vehicle's lot arrival date and last_seen is the last date it was observed on lot.

The two affected populations​

Two indicator columns select the records to correct, and both are added to the output.

  • last_in_gap — last_seen falls within a gap, so the sale date is unknown. last_seen is replaced with first_seen + agg(last_seen - first_seen) over clean records in the same group_by segment.
  • first_in_gap — first_seen is the day after a gap ends and last_seen is not the partition date, so the vehicle was delivered during the gap. first_seen is replaced with last_seen - agg(last_seen - first_seen) over the same segment.

A vehicle that sold inside the gap is last observed on the day before the gap opened, and cannot be told apart from one that genuinely sold that day. That ambiguity is accepted: the estimate is what the correction buys.

The reference population​

The aggregation excludes last_in_gap records, first_in_gap records and still-on-lot vehicles, since a still-on-lot vehicle's observed duration is incomplete and would bias the estimate upward. Rows outside group_filter count as clean records and may contribute to it.

Estimates are clamped to the gap boundary. A last_in_gap estimate is held between the observed last_seen and the gap end date; a first_in_gap estimate between the gap start date and the observed first_seen. A record whose group has no clean reference rows keeps its original dates, and its indicator column still marks it.