DATA ANALYTICS ENGINEERING / MARKET INTELLIGENCE

843.8 million NYC trips.
One narrower decision.

I built an end-to-end analytics project to test where and when observed trip activity could justify deeper market-entry research for a hypothetical electric ride-hailing service in New York City.

Explore live dashboard ↗ View source code

ROLE

Data Analyst + Analytics Engineer

SCOPE

Individual project · Apr – Jun 2026

DATA

NYC TLC, Jan 2023 – Dec 2025

OUTPUT

Tested marts + interactive dashboard

Personal portfolio study — not affiliated with Green SM or NYC TLC. It analyzes completed-trip activity only; it does not observe requests, driver supply, charging capacity, or unit economics, and never presents completed trips as unmet demand.

Interactive decision-support dashboard built from tested dbt marts and six versioned JSON contracts.

01 / THE ANSWER

The data supports a focused validation phase, not a citywide launch.

NYC trip activity grew across the analysis window, but growth sits inside a market dominated by two platforms. Activity is dense in Manhattan, while several Queens zones combine growth, fare, and airport relevance. Friday and Saturday evenings show the clearest recurring operating window.

My recommendation: investigate a small number of Queens–Manhattan and airport-oriented scenarios, then test driver supply, charging, customer acquisition, regulation, and unit economics before committing fleet capacity.

PROCEED WITH

Corridor-level discovery

DO NOT CLAIM

Unmet demand or profitability

VALIDATE NEXT

Supply, charging, acquisition cost, regulation

02 / DATA SCOPE

Enough data to find patterns. Not enough data to skip judgment.

843,752,700

raw trip rows · 72 monthly Parquet files

839,068,674

valid completed trips after quality rules

263

active pickup zones in scope

36

months · Jan 2023 to Dec 2025

Yellow Taxi (128.2M rows) and High Volume For-Hire Vehicle (715.6M rows) records combined with the TLC taxi-zone lookup — about 18.36 GB locally. Only aggregated dashboard contracts are published; trip-level records never enter Git.

03 / DECISION CONTEXT

Completed trips show activity. They do not show the whole market.

A market-entry team needs more than a citywide trip count: competitive concentration, geographic density, time windows, fare position, and the evidence gaps that could change a fleet decision.

TLC data records completed trips. It does not capture rejected requests, unserved passengers, online drivers, idle vehicles, or charging availability. I treated that boundary as part of the analysis rather than a footnote.

04 / MARKET STRUCTURE

A growing market does not mean accessible market share.

85.26%

of observed completed trips belong to Uber + Lyft

Uber · 522.1M trips · $26.50 avg fare62.23%
Lyft · 193.3M trips · $24.43 avg fare23.04%
Yellow Taxi · 123.7M trips · $20.04 avg fare14.74%

Annual observed trips grew from 270.3M (2023) to 288.9M (2025); operator HHI stayed between 4,504 and 4,769 — persistent concentration.

SO WHAT? A new entrant cannot assume market growth translates into attainable share. Customer acquisition, driver liquidity, reliability, and differentiation need their own evidence.

05 / GEOGRAPHIC SIGNALS

The evidence points to corridors, not a citywide launch.

Manhattan generated 45.95% of valid trip activity, Brooklyn 22.75%, Queens 19.33%. Density alone, however, does not make every Manhattan zone the best place to investigate first.

ZONE (LATEST-YEAR SHORTLIST)

BOROUGH

TRIPS

YOY

AVG FARE

SCORE

Long Island City / Hunters Point

Queens

2.20M

+9.03%

$24.05

80.88

LaGuardia Airport

Queens

6.16M

+2.49%

$56.85

77.90

Flushing

Queens

1.36M

+10.81%

$23.45

76.24

Greenpoint

Brooklyn

2.46M

+7.06%

$24.64

74.77

Financial District North

Manhattan

1.80M

+4.79%

$35.43

73.65

Score = 35% activity percentile + 30% YoY growth percentile + 20% fare percentile + 15% airport relevance percentile. It organizes research priorities — it does not predict profit, unmet demand, or fleet size.

06 / OPERATING PATTERNS

Friday and Saturday evenings provide the clearest test window.

The largest zone-hour aggregates recur between 18:00 and 23:00 on Friday and Saturday — supporting a controlled evening-shift experiment with defined geography and service level. It does not determine fleet size: vehicle requirements still depend on supply, turnaround, charging, deadhead miles and utilization targets.

{{ row.day }}

00:00

06:00

12:00

18:00 → 23:00

Operating-window schematic (brighter = stronger recurring aggregates; amber = Fri/Sat 18:00–23:00 peak window). Exact values live in the dashboard's hourly-demand view.

07 / RECOMMENDATION

Move to the next evidence gate before moving vehicles.

Advance to a narrow validation stage. Compare a Long Island City–Manhattan corridor with a LaGuardia-oriented scenario. Define the operating window, service-level target, and decision thresholds before any pilot.

1

Compare LIC–Manhattan and LaGuardia-oriented scenarios.

2

Acquire request, driver-supply, charging, regulation, and cost inputs.

3

Set utilization, service-level, and contribution-margin thresholds.

4

Run a time-bounded pilot only if those thresholds are credible.

Queens activity & growth → prioritize corridor discovery

Follow-up: customer and driver interviews

Airport relevance & fares → test an airport proposition

Follow-up: permit, queue, toll, charging review

Evening concentration → define a limited operating window

Follow-up: supply and vehicle-cycle assumptions

High concentration → avoid undifferentiated entry

Follow-up: acquisition and retention economics

08 / ANALYTICS ENGINEERING

The dashboard is the last mile of a tested data product.

Business definitions live in dbt models and tests, not chart code. Python downloads the official monthly files with retry and resume. DuckDB reads and transforms local Parquet. dbt standardizes both trip services, builds a star schema and decision marts. Python exports six versioned JSON contracts for the static JavaScript dashboard.

NYC TLC files

2023–2025 · 72 months

→

Python ingestion

retry · resume

→

DuckDB raw views

843.8M trips

→

dbt: stg → int → star schema → marts

types · quality rules · conformed services · decision marts

→

6 JSON contracts

versioned · aggregate-only

→

JS dashboard

Chart.js · GitHub Pages

pytest + dbt tests + Ruff gate every dbt layer · GitHub Actions runs the gates and deploys the dashboard

DATA MODEL

Dimensions: date, service, pickup zone · Fact: one conformed completed-trip record · Marts: market overview, fare positioning, hourly demand, concentration, zone opportunity

KEY DECISIONS

DuckDB over a cloud warehouse (local, reproducible, free) · metric definitions upstream in dbt · aggregate-only publishing · opportunity score with exposed weights · contracts versioned so the dashboard can never drift from tested marts

STACK

Python 3.12+ · DuckDB · SQL · dbt Core · HTML/CSS/JS · Chart.js · pytest · Ruff · GitHub Actions

09 / RELIABILITY

Every headline number traces back to a tested mart.

The full-data build passed 61 of 61 scale-appropriate dbt nodes — covering source types, accepted values, date boundaries, non-null keys, relationships, fact-to-conformed reconciliation, weighted-fare reconciliation, share totals, mart grain, and score bounds. CI adds deterministic fixture builds, pytest, Ruff, JS syntax and contract tests, and Pages validation. The JavaScript layer contains no manually entered KPI constants.

61/61

full-data dbt nodes passed

6

versioned JSON dashboard contracts

0

raw rows committed to Git

CI/CD

GitHub Actions + GitHub Pages

10 / WHAT THE DATA CANNOT ANSWER

The missing data determines whether the opportunity is operable.

Requests, cancellations, ETA

→ separate completed activity from unmet demand

Driver online & idle time

→ estimate supply coverage and utilization

Charging locations & dwell time

→ test EV feasibility by zone and shift

Weather, events, transit disruption

→ explain short-term variation

Insurance, labor, acquisition, energy cost

→ build contribution-margin scenarios

Customer research & incentives

→ test willingness to switch and acquisition cost

Good analysis does not remove uncertainty. It makes the next uncertainty explicit and testable.

11 / REFLECTION — WHAT I'D DO DIFFERENTLY

What this project changed in my analytical practice.

The hardest part was not processing 843.8 million rows. It was keeping the decision claim smaller than the dataset.

I learned to separate observed activity from demand, place metric definitions upstream of the dashboard, expose the assumptions behind a score, and connect every finding to a decision or a missing piece of evidence. The result is both an analytics engineering project and a data analysis case study: reliable enough to inspect, clear enough to question, and honest about what it cannot prove.