DATA ANALYTICS ENGINEERING / MARKET INTELLIGENCE
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.
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
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
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
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
85.26%
of observed completed trips belong to Uber + Lyft
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
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.
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
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
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
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
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.
10 / WHAT THE DATA CANNOT ANSWER
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
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.