US Airline Fare & Route-Market Analytics (BTS DB1B)

Historical US airline fare economics and route-market analytics — average and median fares, carrier market share and concentration (HHI), nonstop share and market distance — computed straight from the US Department of Transportation's official DB1B Origin & Destination Survey, a 10% random sample of all US airline tickets. Keyless, no scraping, no API key.

This is a 10% HISTORICAL sample — NOT live booking prices

Read this before anything else, because it defines what the product is and is not:

• Historical, not live. DB1B is filed quarterly and published with a ~1-2 quarter lag. This actor answers "what did people actually pay on this route last quarter, and who flew it" — not "what does a ticket cost to book today". If you need live booking prices, this is the wrong tool (and a different market entirely).
• A 10% sample. DB1B is a 10% random sample of tickets. Every passenger and volume figure this actor reports is a sample count; estimated_market_passengers is the sample multiplied by 10, and every row carries is_10pct_sample: true and sample_multiplier: 10 so a sample count is never mistaken for a market total. Fares are actual prorated per-passenger dollar amounts (not sampled away), so a passenger-weighted average over the sample is a sound estimate of the market's average fare.
• Why nobody else on the Store sells this. Every "airline fare" actor on the Store scrapes live booking sites (Google Flights, Skiplagged, etc.) — a different product for a different buyer. The historical, cause-of-market, fare-distribution rollup from the government's own O&D survey is uncontested.

Who this is for

• Route planners & network analysts: which carriers fly a market, their passenger share, the average/median fare, and how concentrated (HHI) the market is — before proposing a new route or a schedule change.
• Airline competitive-intelligence teams: benchmark a competitor's fares and share on any city pair, quarter by quarter.
• Aviation & management consultants: market-sizing (estimated passengers), fare trends and concentration for diligence and strategy decks, from the authoritative federal source.
• Airport authorities & economic-development offices: measure fares, carrier mix and nonstop connectivity for your airport's markets to support air-service development cases.
• Fare-trend journalists & researchers: "how much did LAX-JFK cost last quarter", "which carrier dominates DEN", "average fare on connecting vs nonstop markets" — one run, official numbers, with the fare distribution.
• AI agents (MCP): a clean, typed, chainable fare/route-market tool over a multi-million-row quarterly federal dataset that agents cannot practically download and parse themselves (each quarter is a ~100 MB zip holding a ~1 GB CSV of 7M+ rows).

What one run costs — read this first

Pricing is pay-per-result: one dataset row = one (group x quarter) rollup. At $10 per 1,000 rows a row costs $0.01.

Run	Typical rows	Typical cost
One route, one quarter (origins:["LAX"], dests:["JFK"])	1	~$0.01
One airport's routes, one quarter (origins:["LAX"])	~300-600	~$3-6 (cap with maxResults)
One carrier's routes, one quarter (carriers:["WN"], groupBy:"carrier_route")	~600	~$6 (capped)
City-market pairs for one metro, one quarter (groupBy:"market")	~200-400	~$2-4
Default prefill (LAX origin routes, 2025-Q2, maxResults 500)	up to 500	up to $5.00

maxResults (default 500) is a hard billing cap enforced in one audited code path for every grouping mode. When it bites, rows are dropped smallest-groups-first and every emitted row carries results_truncated: true — a capped answer is never passed off as complete. A run that cannot answer (bad input, BTS outage, missing quarter without allowPartial) fails loudly and bills nothing.

A single run is capped at 4 quarters (each quarter is a ~100 MB, ~7-million-row download from BTS). Split longer histories across runs. An unfiltered, all-routes run over a full quarter aggregates tens of thousands of markets and is memory-heavy — an origins/dests/carriers filter keeps it light and cheap.

Example input

{
  "sinceQuarter": "2025-Q1",
  "untilQuarter": "2025-Q1",
  "origins": ["LAX"],
  "groupBy": "route",
  "minPassengers": 10,
  "maxResults": 500
}

Or a single city pair, all carriers, grouped by carrier:

{
  "sinceQuarter": "2024-Q3",
  "untilQuarter": "2025-Q2",
  "origins": ["DEN"],
  "dests": ["ORD"],
  "groupBy": "carrier_route",
  "minPassengers": 10,
  "maxResults": 100
}

Leave sinceQuarter/untilQuarter blank and the run defaults to the two most recent published quarters — discovered live: DB1B publishes with a ~1-2 quarter lag, and the actor probes backward from the current quarter until a file answers. The quarter it lands on is reported on every row as latest_quarter_available.

Grouping modes

groupBy	One row per	Use it for
route (default)	directional origin-dest x quarter, across carriers	"How is LAX-JFK overall — fare, share, HHI?"
carrier_route	ticketing carrier x origin-dest x quarter	Carrier-level fare benchmarking on a route
origin_airport	origin airport x quarter (all outbound markets)	Airport-level fares and connectivity
market	origin city-market x dest city-market x quarter	Metro-to-metro analysis (all NYC airports as one)

Markets are directional in DB1B, so LAX-JFK and JFK-LAX are separate rows. Carrier market-share and HHI fields are meaningful for route, origin_airport and market (which span carriers) and trivial for carrier_route (one carrier per group).

Output fields (the important ones)

Every row is one (group x quarter). All fields are nullable and documented in the dataset schema; the key ones:

• Identity: group_by, quarter, year, quarter_num, carrier, origin, dest, route, origin_city_market_id, dest_city_market_id, city_market, carriers_included
• Volume (10% sample): sample_ticket_count, sample_passengers, estimated_market_passengers (x10), is_10pct_sample, sample_multiplier
• Fares (cleaned, passenger-weighted): avg_fare, median_fare, fare_p25, fare_p75, fare_min, fare_max, fare_basis, fare_sample_ticket_count / fare_sample_passengers (the clean denominator), bulk_fare_ticket_count, zero_or_missing_fare_ticket_count
• Route structure: nonstop_sample_passengers, connecting_sample_passengers, nonstop_pax_share_pct, avg_market_distance_miles
• Carrier concentration: carrier_count, top_carrier, top_carrier_share_pct, market_hhi, market_concentration (DOJ/FTC band)
• Provenance & completeness: quarters_requested, quarters_fetched, quarters_missing, data_complete, quarter_rows_scanned, groups_suppressed_below_min_passengers, results_truncated, latest_quarter_available, data_source (the exact BTS zip URL), data_vintage (the file's HTTP Last-Modified — BTS revises files in place), retrieved_at

How the numbers stay honest

This dataset has traps, and the actor is built around them rather than on top of them:

• Fares are cleaned, and the cleaning is disclosed on every row. A fare enters the fare statistics (avg_fare/median_fare/fare_p25/fare_p75/ fare_min/fare_max) only if it is not a bulk fare (BulkFare != 1) and is a positive dollar amount (MktFare > 0). Bulk fares (negotiated/tour tickets) and $0/blank fares (frequent-flyer, non-revenue) are counted in volume but excluded from every fare statistic — a naive Number(x) || 0 would fabricate $0 fares and drag every average toward zero. bulk_fare_ticket_count and zero_or_missing_fare_ticket_count disclose exactly what was excluded, and fare_sample_passengers is the passenger weight actually behind the averages.
• Averages are passenger-weighted, and median is the robust measure. DB1B has legitimately tiny fares (measured min $0.15) and rare very high fares (max $30,656) that skew a mean; avg_fare is passenger-weighted, but median_fare and the p25/p75 quartiles (from a $5-resolution passenger-weighted histogram) are the robust central measures, and fare_min/fare_max are published exactly so the distribution is visible. Tails are not silently trimmed.
• Passenger counts are sample counts, and the estimate is labelled. DB1B is a 10% sample; estimated_market_passengers is sample_passengers x 10 and every row says is_10pct_sample: true. A sample count is never presented as absolute traffic.
• Market share is by TICKETING carrier. Share and HHI are attributed to the carrier that sold the ticket (TkCarrier); the operating carrier differs on ~27% of rows (codeshare/regional), so this is the honest "who sells the market" measure. Foreign carriers on US-endpoint itineraries and the DB1B -- placeholder appear as their own codes — never guessed or merged.
• A live drift gate runs before anything billable, per quarter: the real header must contain every column this actor reads (a renamed column fails the run, never emits nulls); the file must hold >=300,000 rows, >=15 ticketing carriers and >=100 origin airports; at least one pinned mega-hub (ATL/ORD/DFW/DEN/LAX) must appear as an origin; the file's own Year/Quarter columns must match the quarter requested (a stale or mislabeled file fails instead of billing); a negative-control carrier code must match zero rows; and the overall clean passenger-weighted mean fare must fall in a sane $40-$1500 band (a cents-vs-dollars units error or the wrong fare column fails here).
• A missing quarter never silently vanishes. If BTS hasn't published a requested quarter (or the download fails), the run FAILS by default with the per-quarter status. Opt in with allowPartial: true and every row then reports quarters_missing and data_complete: false.
• Cheap trap coverage: the BTS 404 page is HTML at a real 404; a maintenance page served at HTTP 200 is detected by byte shape (a zip must start with PK) and treated as an outage, never parsed. The quarter is probed with a ranged GET, not HEAD (federal hosts have answered 404 to HEAD on URLs they serve to GET). The CSV entry is selected by extension.

Use as an MCP tool

This actor works as a chainable MCP tool via https://mcp.apify.com — the input schema is fully described, quarter strings are validated with actionable error messages (an agent sending "2025-05", "Q2 2025" or "2025-Q9" gets a named reason, not a silent empty dataset), and a filter that matches nothing fails with the list of carrier codes actually present in the window. Billing is unchanged when called as a tool; a run that cannot answer fails without billing.

Example agent prompt: "Using the BTS DB1B fare & route-market tool, find the average and median fare and each carrier's market share on Denver-Chicago for the last two published quarters, grouped by carrier_route."

FAQ

How fresh is the data? DB1B is quarterly and lags ~1-2 quarters. The run discovers the latest published quarter live and reports it on every row as latest_quarter_available. BTS also revises files in place — data_vintage carries each file's Last-Modified.

Is this live booking prices? No. It is the historical DB1B fare survey — what passengers actually paid, filed quarterly. It is the right tool for fare economics and market analysis, the wrong tool for booking a flight.

Why is this a "sample"? DB1B is DOT's 10% random sample of all US airline tickets. Volume figures are sample counts; multiply by 10 (done for you in estimated_market_passengers) for a market estimate. Fares are real dollar amounts, so fare averages are sound estimates.

Which carriers are covered? All carriers appearing in the survey — ~37 ticketing-carrier codes in a recent quarter, including foreign carriers on itineraries with a US endpoint. Share/fares use the ticketing carrier.

Why do bulk and $0 fares not count in the average? Bulk fares are negotiated tour-operator tickets and $0 fares are frequent-flyer/non-revenue — neither represents a published fare a traveler would pay, so they are excluded from fare statistics but still counted in volume. Every row discloses how many were excluded.

Can I get years of history? Yes, 4 quarters per run (each quarter is a ~100 MB federal download). Loop runs across windows; the DB1B O&D survey goes back to 1993-Q1.

What does the HHI mean? The Herfindahl-Hirschman Index of ticketing-carrier passenger shares in the market (0-10,000). market_concentration bins it into the DOJ/FTC bands: unconcentrated (<1500), moderately concentrated (1500-2500), highly concentrated (>2500). A monopoly route reads ~10,000.

Data source

US DOT Bureau of Transportation Statistics, TranStats Airline Origin & Destination Survey (DB1B), DB1BMarket quarterly PREZIP bulk files at transtats.bts.gov/PREZIP/ — public domain, keyless. Each output row names the exact source zip (data_source) and its Last-Modified (data_vintage).