# Customer Loyalty v1 Rollout\n\n## Confirmation\nNo existing Customer Loyalty projects found (no 'projects' table).\n\n## Data Summary\n- **Airport Shuttle (ID=1)**: 1442 customers, 2222 trips, avg $146/trip, total ~$325K revenue, monthly: Feb $56K → Apr $143K (growing).\n- **Texas Shuttle (ID=2)**: 371 customers, 596 trips, avg $205/trip, total ~$122K revenue, monthly: Feb $98K → Apr $19K.\n- Repeat rate: 0% (data quality issue: customer_id often NULL in reservations; customers table metrics 0).\n- Fleet data sparse (0 unique vehicles detected).\n- LTV proxy: ~$225 (1), ~$330 (2).\n\n## Stats Analysis\nHistorical revenue short (3 months), growing for Phoenix.\nTarget: +20% LTV via loyalty → monitor repeat trips post-launch.\n\n## v1 Design (JSON)\n```json\n{\n \"tiers\": [\n {\n \"name\": \"Bronze\",\n \"min_spend\": 0,\n \"min_trips\": 0,\n \"rewards\": [\n \"Welcome: 10% off next ride (promo LOYAL10)\",\n \"Birthday SMS discount\"\n ]\n },\n {\n \"name\": \"Silver\",\n \"min_spend\": 300,\n \"min_trips\": 3,\n \"rewards\": [\n \"15% off rides\",\n \"Priority dispatch\",\n \"Free upgrade (if avail)\"\n ]\n },\n {\n \"name\": \"Gold\",\n \"min_spend\": 1000,\n \"min_trips\": 10,\n \"rewards\": [\n \"20% off all rides\",\n \"1 free ride/quarter\",\n \"Dedicated support line\"\n ]\n }\n ],\n \"campaigns\": {\n \"sms\": {\n \"provider\": \"Twilio\",\n \"templates\": [\n \"Welcome: Hi {name}, join Loyalty! Use LOYAL10 for 10% off. Reply STOP to optout.\",\n \"Tier Up: Congrats {name}! You're Silver. Enjoy 15% off next ride.\"\n ],\n \"triggers\": [\"first_trip\", \"tier_change\", \"inactive_30d\"]\n },\n \"email\": {\n \"provider\": \"SendGrid\",\n \"templates\": [\n \"Loyalty Dashboard: Track your tier, upcoming rewards.\",\n \"Referral: Refer a friend, both get $20 credit.\"\n ],\n \"triggers\": [\"signup\", \"reward_earned\", \"anniversary\"]\n }\n },\n \"target\": {\n \"ltv_uplift\": \"20%\",\n \"kpis\": [\"repeat_rate >30%\", \"avg_trips/cust >2\"]\n }\n}\n```\n\n## Rollout Plan (Onboarding Track)\n1. **Data Migration/Clean**: Link reservations to customers via email/phone. Add loyalty_tier jsonb to customers (SQL: ALTER TABLE customers ADD COLUMN loyalty_tier jsonb DEFAULT '{}'; UPDATE customers SET loyalty_tier = jsonb_build_object('tier','Bronze','points',0); ).\n2. **Integration**: Automate Anything workflows for tier calc (cron daily), Twilio/SendGrid triggers on booking/completion.\n3. **Admin Setup**: Dashboard in Passenger Transportation Pro: loyalty overview, segment customers.\n4. **First Value**: Test campaign on 100 customers → measure open/ redemption rates.\n5. **Go-Live**: Phased: 10% customers week1, full week4.\n6. **Training**: Video: Tier management, campaign setup. Live session for ops.\n7. **Track**: Feature adoption (loyalty bookings %), drop-offs.\n\n## SQL Rollout Starter\n```sql\n-- Add columns\nALTER TABLE customers ADD COLUMN IF NOT EXISTS loyalty_points integer DEFAULT 0;\nALTER TABLE customers ADD COLUMN IF NOT EXISTS loyalty_tier text DEFAULT 'Bronze';\nALTER TABLE reservations ADD COLUMN IF NOT EXISTS loyalty_promo_applied boolean DEFAULT false;\n\n-- Seed Bronze\nUPDATE customers SET loyalty_tier = 'Bronze', loyalty_points = 0 WHERE company_id IN (1,2);\n\n-- View\nCREATE VIEW customer_loyalty AS SELECT c.*, r.total_amount as last_trip_value FROM customers c LEFT JOIN (SELECT customer_id, MAX(created_at) max_date, total_amount FROM reservations GROUP BY customer_id) r ON c.id::int = r.customer_id;\n```\n\n## Next Steps\n- Delegate to engineering for DB changes.\n- Proactive check-in: Week1 metrics.\nSuccess: First aha - 5% redemption rate.