Shuttle Revenue Tracker
The Problem
Shuttle earns commission from multiple payment partners, each sending statements in different formats — CSV, PDF, XLSX — with different column structures and terminology. Reconciling these manually each month was tedious and error-prone.
What I Built
An automated pipeline that handles the entire process:
- Fetches commission statement attachments from Gmail automatically using OAuth2
- Parses CSV, XLSX, and PDF statements using partner-specific YAML configurations
- Normalises data across all partners to a common schema (policy number, commission amount, transaction type, currency)
- Generates a multi-sheet Excel workbook:
- All Records
- Summary by Partner (totals, counts, averages)
- Breakdown by Transaction Type
- Individual sheets per partner
- Creates an interactive HTML dashboard with Plotly charts
Partners Integrated
- Checkout.com — PDF statement parsing
- Trust Payments — CSV processing
- Twilio Marketplace — CSV processing
- GoCardless — email body extraction
Tech Stack
- Python with pandas, pdfplumber, openpyxl
- Gmail API with OAuth2 authentication
- Plotly.js for interactive dashboards
- YAML-based partner configuration for extensibility
- Monthly cron (1st of each month at 9am)
Design Decisions
Plugin-based parser architecture with a base parser class and CSV/PDF implementations. Adding a new partner means creating a YAML config file — no code changes required.