HK Last-Mile Delivery Intelligence
A BI analytics dashboard built directly on the Lalamove Sandbox API. 450 HMAC-signed quotations across 50 HK corridors × 3 vehicle classes × 3 time buckets — aggregated in Python + pandas, with SQL equivalents for Hive / MySQL / DORIS warehouses.
Fleet-weighted cost per kilometre, by vehicle class
SUM(price) / SUM(km) across all quotations — not AVG(price/km), which would be biased upward by short trips amortising the base fare over tiny distances.
Zone-to-zone corridor matrix · average distance (km)
Pickup zone on the left, drop-off zone across the top. Cells coloured by average distance — the honest geography signal the sandbox exposes, independent of its flat pricing.
Brighter lines = longer hauls · more distance = lower HK$/km
| Hong Kong Island (drop-off) HKI | Kowloon (drop-off) KLN | New Territories (drop-off) NT | |
|---|---|---|---|
| Hong Kong Island(pickup) HKI | 4.9 km n=90 · HK$86.67 avg | 7.71 km n=63 · HK$86.67 avg | 25 km n=45 · HK$86.67 avg |
| Kowloon(pickup) KLN | — | 3.7 km n=90 · HK$86.67 avg | 12.2 km n=45 · HK$86.67 avg |
| New Territories(pickup) NT | — | — | 16.62 km n=117 · HK$83.59 avg |
Corridor efficiency · HK$ per km
Least-efficient routes are short intra-district hops (base fare dominates). Most-efficient are long cross-territory hauls. In production, this view is how an ops team identifies where pricing needs surge adjustment vs. where volume growth would print margin.
Most efficient · lowest HK$/km
| Corridor | HK$/km | Avg km |
|---|---|---|
| Fanling->Airport | HK$2.02 | 43 |
| TuenMun->TseungKwanO | HK$2.06 | 42 |
| Aberdeen->Airport | HK$2.06 | 42 |
| ShaTin->Airport | HK$2.22 | 36 |
| Central->Airport | HK$2.34 | 37 |
| TinShuiWai->Airport | HK$2.86 | 28 |
| YuenLong->Fanling | HK$4.56 | 19 |
| KowloonBay->TaiPo | HK$4.56 | 19 |
Least efficient · highest HK$/km
| Corridor | HK$/km | Avg km |
|---|---|---|
| MongKok->PrinceEdward | HK$86.67 | 1 |
| Jordan->TsimShaTsui | HK$86.67 | 1 |
| TseungKwanO->HangHau | HK$80 | 1 |
| Central->WanChai | HK$43.33 | 2 |
| KwunTong->LamTin | HK$43.33 | 2 |
| LaiChiKok->MongKok | HK$28.89 | 3 |
| SheungWan->Admiralty | HK$28.89 | 3 |
| NorthPoint->QuarryBay | HK$28.89 | 3 |
How this dashboard is built — and what the sandbox honestly exposes
Built for the Senior BI Engineer interview at Lalamove HK. Every number above comes from real Lalamove Sandbox API responses — no mock data.
Pipeline
scripts/fetchQuotations.js— HMAC-SHA256 signer built from spec, rate-limited at 44 rpm (under the 50 QPM account cap).analysis/analyze.py— pandas aggregation, authoritative BI layer. Mirrored inscripts/analyze.jsfor Node-only CI.queries/*.sql— the same aggregations in Hive / MySQL / DORIS-compatible SQL, with schema + partition + rollup notes per file.- Astro 5 static build, committed JSON, no runtime API calls from the browser. Secrets never ship.
What the sandbox does expose truthfully
- Distances — real routing between HK coordinate pairs.
- Service-type availability — confirmed HK fleet: Motorcycle, Van, Truck 550 (plus Courier / Walker / Pooled Order).
- Base-fare structure — flat base + fixed surcharge per vehicle class.
What the sandbox does not simulate
- Time-of-day surge. Morning / midday / evening peak quotes are identical — no demand model behind them. The by_hour aggregation is emitted for completeness but not charted; a surge visualisation on flat data would be dishonest.
- Distance-scaled pricing. Base fare amortises heavily across long hauls, which produces the dramatic HK$/km spread in the zone matrix — a real insight, but driven by the pricing model's flat floor rather than a real "cost curve" you'd see in production data.
How the production version would differ
- Replace the one-shot fetcher with a streaming ingest from the
ORDER_AMOUNT_CHANGEDandORDER_STATUS_CHANGEDwebhooks. - Land events in a partitioned fact table (daily partition key) — the schema already assumed in
queries/avg_price_by_district.sql. - Build a DORIS rollup on
(dt, pickup_district, dropoff_district, vehicle)to serve the dashboard in sub-100 ms. - Charts update: peak-hour chart becomes real, and the HK$/km curve reflects demand surge rather than base-fare amortisation.