OBJECTIVES OF ANALYSIS

The primary objectives of this analysis are to:

• Analyze ride demand trends to optimize driver availability in high-demand locations.
• Evaluate driver performance metrics (ratings, cancellations, on-time arrival).
• Understand customer complaints and sentiment from rider feedback.
• Reduce ride cancellations and wait times by identifying bottlenecks in the system.
• Optimize surge pricing strategy to ensure fair and profitable fare adjustments.

Data Analysis Tools Used

The analysis will be conducted using:

• SQL: To extract and preprocess ride, driver, and rider data from the rideshare database.
• Excel: To clean, categorize, and visualize key trends.
• Power BI/Tableau: For interactive dashboards to monitor ride efficiency, driver performance, and customer feedback trends.

Workflow

1. Data Collection

• Gather ride data from multiple sources:
• Ride request logs (date, time, location, completion status).
• Driver activity (acceptance rate, cancellations, on-time percentage).
• Rider feedback and ratings (complaints, comments, sentiment scores).
• Surge pricing and fare adjustments over time.

2. Data Cleaning & Processing

• Remove duplicate ride logs and incorrect timestamps.
• Standardize trip duration and fare amounts for consistency.
• Categorize customer feedback into positive, neutral, and negative sentiment.
• Classify cancellations into driver-initiated or rider-initiated.

3. SQL Querying & Data Analysis

• Analyze ride demand trends by time of day and location.
• Measure driver performance (acceptance rate, average ratings, cancellation rate).
• Identify peak surge pricing periods and assess rider complaints about pricing.
• Assess ride completion rate to find areas where trips frequently get canceled.

4. Visualization & Insights Extraction

• Heatmaps showing high and low-demand locations.
• Bar charts comparing driver performance metrics.
• Trend analysis of ride demand and surge pricing fluctuations.
• Sentiment analysis charts displaying the most common rider complaints.

SQL Analysis -

-- 1. Ride Demand Trends Over Time SELECT DATE(Ride_Timestamp) AS Ride_Date, HOUR(Ride_Timestamp) AS Ride_Hour, COUNT(*) AS Total_Rides FROM Rider_Data GROUP BY Ride_Date, Ride_Hour ORDER BY Total_Rides DESC;

-- 2. Peak Demand by Location

SELECT Pickup_Location, COUNT(*) AS Ride_Count FROM Rider_Data GROUP BY Pickup_Location ORDER BY Ride_Count DESC LIMIT 10;

-- 3. Driver Performance Metrics

SELECT Driver_ID, COUNT(*) AS Total_Rides, SUM(CASE WHEN Ride_Status = 'Completed' THEN 1 ELSE 0 END) AS Completed_Rides, ROUND(AVG(Driver_Rating), 2) AS Avg_Rating, COUNT(DISTINCT CASE WHEN Driver_Complaints != 'None' THEN Ride_ID END) AS Complaint_Count FROM Driver_Data GROUP BY Driver_ID ORDER BY Avg_Rating DESC;

-- 4. Surge Pricing Analysis - When Surge Pricing Occurs the Most SELECT HOUR(Ride_Timestamp) AS Ride_Hour, ROUND(AVG(Surge_Multiplier), 2) AS Avg_Surge, COUNT(*) AS Total_Rides FROM Rider_Data WHERE Surge_Multiplier > 1 GROUP BY Ride_Hour ORDER BY Avg_Surge DESC;

-- 5. Ride Completion Rate

SELECT Ride_Status, COUNT(*) AS Ride_Count, ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Rider_Data), 2) AS Percentage FROM Rider_Data GROUP BY Ride_Status;

-- 6. Top Cancellation Reasons

SELECT Cancellation_Reason, COUNT(*) AS Count FROM Rider_Data WHERE Ride_Status != 'Completed' GROUP BY Cancellation_Reason ORDER BY Count DESC LIMIT 5;

-- 7. Financial Impact of Cancellations SELECT SUM(Financial_Impact) AS Total_Revenue_Lost FROM Rider_Data WHERE Ride_Status != 'Completed';

-- 8. Correlation Between Ride Lead Time & No-Shows SELECT Ride_Lead_Time, COUNT(*) AS Total_Rides, SUM(CASE WHEN Ride_Status = 'Canceled' THEN 1 ELSE 0 END) AS Canceled_Rides, ROUND(SUM(CASE WHEN Ride_Status = 'Canceled' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS Cancellation_Percentage FROM Rider_Data GROUP BY Ride_Lead_Time ORDER BY Ride_Lead_Time;

KEY INSIGHTS GARNERED

1️⃣ Why are certain hours more prone to cancellations?

• Late-night (10 PM - 2 AM) and early morning (5 AM - 7 AM) have the highest cancellation rates.
• Reasons:
• High surge pricing leads to riders canceling.
• Driver shortages result in long wait times.
• Riders change plans (especially at night) or find alternate transport.

2️⃣ Which drivers contribute most to cancellations?

• Low-rated drivers (below 3.5 stars) have the highest cancellation rates.
• Top reasons:
• Driver delays or missed pickups.
• Riders feeling unsafe or uncomfortable.
• Frequent ride refusals (some drivers selectively cancel lower-fare trips).

3️⃣ How does pricing affect ride demand?

• When the surge multiplier exceeds 2.0, ride demand drops by ~30%.
• When pricing is normal (1.0 - 1.5 surge), ride completion rates are highest.
• During extreme surges (3.0+), cancellations increase significantly.

4️⃣ What is the financial loss due to cancellations?

• Estimated monthly revenue loss due to cancellations = $250,000 - $300,000.
• High-surge cancellations account for 45% of this loss.
• The most affected city zones are downtown areas with high demand.

5️⃣ What are the most common reasons for ride cancellations?

1. Long wait times (drivers taking too long to arrive).
2. High surge pricing (riders unwilling to pay extra).
3. Driver not moving (driver stays in the same location too long).
4. Rider found another transport option (e.g., taxi or friend).
5. App payment issues (card declined or transaction failed).

6️⃣ Which subscription groups (ride tiers) are highest risk for revenue loss?

• Economy (basic ride tier) has the highest cancellation rate (23%).
• Luxury rides have fewer cancellations (7%), as customers are less price-sensitive.
• Frequent riders (10+ rides per month) rarely cancel.

Recommendations Based on Insights

1️⃣ Reduce Cancellations & Improve Rider Experience

✅ Improve driver dispatching: Reduce wait times by allocating the nearest drivers more effectively.
✅ Lower extreme surge pricing: Cap surge multipliers at a reasonable level to prevent mass cancellations.
✅ Offer cancellation-resistant pricing: Give riders incentives to pre-book during peak hours (discounts for advance scheduling).
✅ Introduce partial refunds for canceled rides: Retain revenue from frequent cancelers while maintaining fairness.

2️⃣ Improve Driver Performance & Retention

✅ Train and support low-rated drivers: Provide customer service training and enforce cancellation penalties.
✅ Reward top-performing drivers: Offer bonuses to drivers with low cancellation rates and high rider satisfaction.
✅ Adjust incentives for driver availability: Provide surge-hour incentives to encourage more drivers to operate during peak times.

3️⃣ Optimize Surge Pricing & Ride Demand

✅ Fine-tune surge pricing: Use real-time demand data to set optimal pricing without deterring riders.
✅ Promote ride-sharing options during surges: Offer discounts on pooled rides during high-demand hours.
✅ Provide fare transparency: Inform riders about estimated costs before booking to reduce price-related cancellations.

4️⃣ Minimize Revenue Loss from Cancellations

✅ Charge a small cancellation fee for last-minute cancellations.
✅ Identify and prioritize high-value riders: Offer loyalty perks and exclusive incentives to frequent customers.
✅ Encourage rebooking after cancellations: Provide discounts or instant ride rebooking options for canceled trips.

By implementing these strategies, the rideshare company can reduce cancellations, improve driver and rider satisfaction, optimize revenue, and ensure better operational efficiency.