An end-to-end data analytics project analyzing 5.5M+ Chicago taxi trips (2022) to uncover demand patterns, fare behavior, and tipping trends using Google BigQuery and Looker Studio.
How can a taxi aggregator optimize driver allocation and pricing strategy by understanding when demand peaks, what drives higher fares, and what influences passengers to tip?
Page 1: Demand & Operations
Page 2: Fare & Tip Behavior
- Source: Google BigQuery Public Dataset
- Table:
bigquery-public-data.chicago_taxi_trips.taxi_trips - Scope: 2022 (filtered for clean records)
- Clean records analyzed: 5,581,779 trips
| Tool | Purpose |
|---|---|
| Google BigQuery | Data storage, SQL analysis, Views |
| SQL | Data cleaning, aggregation, window functions |
| Looker Studio | Interactive dashboard, live BigQuery connection |
| GitHub | Version control, project documentation |
chicago-taxi-analytics/ │
├── Queries/ # All 9 SQL queries saved as BigQuery views
├── Dashboard/ # Dashboard screenshots
└── README.md
- How did taxi demand trend across 2022?
- Which hours and days see the highest trip volume?
- Which companies dominate Chicago's taxi market?
- How does trip distance affect fare and profitability?
- Do card payment passengers tip significantly more than cash passengers?
- Which distance bucket is most profitable per minute for drivers?
- Peak month: June recorded the highest trip volume in 2022 with 557K+ trips
- Peak hours: Weekday trips peak between 7–10 AM and 4–7 PM
- Market leader: Flash Cab dominates with 1.5M+ trips, 3x the next competitor
- Tip behavior: Credit card passengers tip on average $5 vs $0 for cash passengers
- Profitability: Short trips (0–1 miles) generate the highest fare per minute at $25/min
- Avg fare: $22.43 per trip across 5.5M clean records
- Filtered out NULL fares, tips, and zero-mile trips for data quality
- Used CTEs for readable query structure
- Used SAFE_DIVIDE to avoid division-by-zero errors
- Used EXTRACT for time-based aggregations
- Saved all queries as BigQuery Views for live Looker Studio connection
- Applied CASE WHEN bucketing for distance segmentation
- Open Google BigQuery Sandbox (free)
- Copy any query from the
queries/folder - Replace
your_projectwith your GCP project ID - Run the query, results match the dashboard views
Gurpreet Kaur