Case 1 Part 3 - Richard Crabb - Analysis Of Cancellations At Indocabs PDF

Preview

Summary

Richard Crabb...

Description

Analysis of Cancellations at IndoCabs, a Cab Portal Company Evan Leitch, Zach Saphner, Micah Christofferson TEAM NUMBER: 131 February 2017

Executive Summary In this report, we analyzed the problem with cancellations at IndoCabs. We examined multiple types of information such as travel type, booking method, and booking windows in order to determine when a cancellation is most likely to occur. By analyzing the data, it is clear that most of the business for IndoCabs comes from shortdistance, point-to-point trips. Although there is a wide range of trip durations, a large number of trips are an hour or less. Very few of the long-distance bookings are cancelled and many of the point-to-point bookings are cancelled compared to the overall average. The booking method is another area of concern. The cancellation percentage is 19.67% for mobile bookings compared to 12.58% with online bookings. Mobile bookings would be a channel to think about shutting down since it accounts for a small part of the business and a high percentage of cancelations. A lower percentage of bookings are cancelled on Saturday compared to any other day. Besides Saturday, the rest of the week is similar in percentages of bookings cancelled. As the number of bookings increases, the percentage of cancellations also increases. Also, from 6pm to 7pm is when 21% of the bookings are cancelled. This is a specific area to pay attention to. The booking window in relationship to bookings cancelled is another important statistic. Most booking windows are less than a day. Additionally, a large number of cancellations come from trips with a short booking window. More specifically, a booking window of less than 6 hours accounts for more than 50% of the total number of cancellations. This is the issue. Currently the issue with cancellations stems from point-to-point trips. This is likely due to a combination of the shorter booking window and shorter trip duration for point-to-point trips compared to the average trip. Shorter trips create less revenue and therefore less profit compared to a longer trip. Part of the cancellation could be due to low prices on point-to-point trips and could be solved with higher prices for point-to-point trips providing an incentive for drivers to not cancel on these trips.

1

Analysis A Look at Trip Durations There is a large disparity, particularly in the trip duration, between the shortest trips and the longest trips. While the range for trip duration is a massive 207 hours, the median was only 1.33 hours. The low median relative to the range proves that many of trips were well under 1.33 hours in duration and only a few trips were days in length. Also, a small median for booking window of 0.41 days shows that most trips were booked within a day. The median (measure of central tendency) and the range (measure of variation) work together to prove that outliers exist and most of the trips were short in length and had a small booking window. The average wouldn't be useful here due to outliers as shown with the large range The relative outliers of 100 hour or more trips were sparse, but clearly had a large impact on data calculations. This is supported by the interquartile range which shows us that the middle 50% of the data was within a range of 1.56 hours giving more evidence of a majority of the trips being very short in duration due to a low median. Similar with the booking window interquartile range of 0.72 days, a small interquartile range proves that many of the booking windows were small. The interquartile range relative to the range proves that much of the data was concentrated within a smaller range. Variance was not chosen here since the variance is not a useful measure by itself.

Median Range

Trip Duration (Hours) 1.33 207.40

Booking Window (Days) 0.41 32.23

Interquartile Range

1.56

0.72

The Magnitude of the Cancellation Problem at IndoCabs Our sample size was a total of 2002 bookings after the data was cleaned. Between the three travel types, long distance, point-to-point, and hourly rental, the point-to-point has the greatest percentage of car cancellations and long distance has the smallest percentage of car cancellations. As a whole, 8.74% of total bookings are cancelled. The overall average cancellation percentage is higher than both the long distance and hourly rental travel type cancellation percentages.

Travel Type

Number of Bookings

Number of 2

Percent Cancelled

Long Distance Point-to-Point Hourly Rental

Cancellations 2 154 19

84 1580 338

2.38% 9.75% 5.62%

The chart above shows the high number of bookings of point-to-point travel type relative to other types of travel types that also has the highest cancellation percentage is the root cause of the 8.74 overall cancellation percentage. There is a negative correlation between the average duration in hours and percent of trips cancelled. The longer the trip, the lower the percent cancelled. 78.92% of total trips are from point-to-point (shorter trip) which has the highest cancellation percentage brings up the overall average of total bookings cancelled. It also is expected that short distance (point-to-point) would have the most number of bookings due to it being cheaper and more common. We expected this pattern since shorter trips give less revenue and therefore would be more likely to be cancelled. Drivers would be more likely to cancel when profit on a trip is lower. Longer trips likely involve a higher profit per trip and therefore would be less likely to be cancelled compared to shorter trips. Between online and mobile booking, there is a larger percentage of mobile bookings that are cancelled compared to online bookings. This difference is meaningful since the cancellation percentage is 19.67% with mobile compared to 12.58% with online. Mobile bookings would be a channel to think about shutting down. It only accounts for 6% of the total bookings yet has a cancellation percentage of 19.67%. With such a high cancellation percentage, taking the mobile channel out of production would decrease the overall cancellation percentage by 0.7% and bring the 8.74 cancellation percentage down to 8.03%. The online booking channel should not be shut down since it has a high percentage of total number of bookings. It should not be expanded either as booking channel has a higher than average cancellation percentage. Other channels that IndoCabs receives bookings through could be worth expanding. These other channels not given in the data should have a lower average of bookings cancelled.

3

Percentage of Cancellations by Day of Week 12.00

Percentage Cancelled

10.00 8.00 6.00 4.00 2.00 0.00

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

Day of the Week

The cancellations are relatively dispersed by the day of the week. This pattern is not exactly expected. Saturday has an unusually low cancellation percentage and Sunday has an unusually high cancellation percentage. Saturday is a day to pay attention to due to the 6.06% cancellation percentage. This is significantly lower than the rest. The average number of bookings cancelled by point-to-point travel type is 7.00% on Saturday which is lower than the overall average of point-to-point cancellation percentage of 9.75%. Saturday has a lower cancellation percentage across all travel types and also has the greatest number of bookings out of any day. Sunday has the highest cancellation percentage and therefore would be useful to have a surge pricing on these days to reduce the abnormally high cancellation percentage.

The Relationship between Booking Windows, Cancellations, and Trip Timing An R-squared value of 38.89% is an acceptable value. This value describes how well the data fits the regression line. This means that 38.89% of the variation in the data can be explained by a linear relationship. Above 50% for an R-squared value would be desirable but with human behavior, this value is acceptable. There is a strong positive correlation between the number of bookings and the number of cancellations (0.6236). It makes perfect sense that as the number of bookings increases that the number of cancellations also increases and the data supports this conclusion.

4

Number of Bookings vs. Number of Cancellations Correlation 30

Number of Cancellations

25 20 15 R² = 0.39 10 5 0

0

20

40

60

80

100

120

140

160

Number of Bookings

These three line charts have a similar shape and seem to align with the average person’s workday with a large number of bookings in the mornings between 6am and 10am and again from 6pm and 10pm with a sizeable dip in the middle of the day. The most interesting feature of these line charts is the jump in percentage of cancelations from 3pm to 5pm. This jump is large and does not line up exactly with a comparable increase in bookings. The most useful line chart is the “Percentage of Cancellations by Hour” since the data can be compared hour by hour to see when the greatest chance of a cancellation occurring is. This shows that from 6pm to 7pm is a time to pay attention to as 1 in 5 trips are cancelled.

Number of Bookings by Hour 160

Number of Bookings

140 120 100 80 60 40 20 0

0

1

2

3

4

5

6

7

8

9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Hour

5

Number of Cancellations by Hour 30

Number of Bookings Cancelled

25 20 15 10 5 0

0

1

2

3

4

5

6

7

8

9

10 11 12 13 14 15 16 17 18 19 20 21 22 23

Hour

Percentage of Cancellations by Hour Percentage of Bookings Cancelled

25

20

15

10

5

0 0

1

2

3

4

5

6

7

8

9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Hour

6

The histogram with the smaller bin sizes (with increments of 0.25) is far more informative. Rather than showing just a booking window of a day, it breaks down the day into specific quarters of each day to give a detailed time of when most bookings occur. 36% of the bookings occur within 6 hours of the trip.

Frequency (Percentage)

Distribution of Booking Window in Days 90 80 70 60 50 40 30 20 10 0

1

2

3

4

5

6

7

More

Booking Window (Days)

Frequency (Percentage)

Distribution of Booking Window in Days 40 35 30 25 20 15 10 5 0

25 .5 75 0. 0 0.

1 25 .5 75 1. 1 1.

2 25 .5 75 2. 2 2.

3 25 .5 75 3. 3 3.

4 25 .5 75 4. 4 4.

Booking Window (Days)

7

5 25 .5 75 5. 5 5.

6 25 .5 75 6. 6 6.

7 re o M

Basedont hedat ac ol l ec t edandr epr es ent edi nt hesehi st ogr amst hev as tmaj or i t yof book i ngsar es amedaybook i ngsandi nt ur nt hev as tmaj or i t yofc anc el at i onsar es ame dayc anc el l at i ons .Themos ti nt er es t i ngpai rofhi st ogr amsar et het hi r dandf our t ht hat c ompar et henoncanc el l edt r i psandt hec anc el l edt r i psovert hes amet i mes pan. Thes eshowt hatt heshor t ert hebook i ngwi ndowt hemor ebook i ngst hatoc curandt he mor ecancel at i onst hatoc c ur .

Frequency (Percentage)

Distribution of Bookings Not Cancelled (Days) 90 80 70 60 50 40 30 20 10 0

1

2

3

4

5

6

7

More

Booking Window (Days)

Distribution of Bookings Cancelled (Days) 90

Frequency (Percentage)

80 70 60 50 40 30 20 10 0

1

2

3

4

5

Booking Window (Days)

8

6

7

More

Frequency (Percentage)

Distribution of Bookings Non-Cancelled (Days) 40 35 30 25 20 15 10 5 0

25 .5 75 0. 0 0.

1 .25 1.5 75 1 1.

2 .25 2.5 75 2 2.

3 .25 3.5 75 3 3.

4 .25 4.5 75 4 4.

5 .25 5.5 75 5 5.

6 25 6.5 .75 6 6.

7 re o M

Booking Window (Days)

Distribution of Bookings Cancelled (Days) Frequency (Percentage)

60 50 40 30 20 10 0

0.250.50.75 1 1.251.51.75 2 2.252.52.75 3 3.253.53.75 4 4.254.54.75 5 5.255.55.75 6 6.256.56.75 7 More

Booking Window (Days)

Elevator Charts These charts tell the story that cancellations are mostly occurring from point-to-point travels at later parts of the day. Point-to-Point travel type accounts for most of the business, but also has a higher than average cancellation percentage. This proves that point-to-point travel types are the main travel type to look at. Travel Type

Number of Bookings

Number of Percent Cancelled Cancellations Long Distance 84 2 2.38% Point-to-Point 1580 154 9.75% Hourly Rental 338 19 5.62% This graph shows that over 20% of the trips were cancelled from 6pm to 7pm. This is significant because it is a major spike in the cancellation percentage by hour and could be resolved with surge pricing to increase revenues and decrease the odds of being cancelled.

9

Percentage of Cancellations by Hour Percentage of Bookings Cancelled

25

20

15

10

5

0

0

1

2

3

4

5

6

7

8

9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Hour

Number of Bookings vs. Percentage of Cancellations

Percentage Cancelled

25 20 15 10 5 0

0

20

40

60

80

100

120

140

160

Number of Bookings

There is a positive correlation 0.4015 between number of bookings and percentage cancelled showing that a greater percentage of bookings are cancelled when more bookings are made. These 3 charts show that point-to-point travels account for most of the cancellations and when more bookings occur, that increases the odds of it being cancelled, and most trips are cancelled from hours 6pm to 7pm

Notes on Data Preparation Before doing any real work on our IndoCabs data set, it was essential to remove any duplicate or erroneous entries. To delete the entries, we simply used Excel's duplicate removal feature. 10

According to Excel, there were no duplicate entries to be removed. For our data set, we were only to analyze entries from January 1st, 2013 or later. In order to remove the entries from before 2013, we organized the data from oldest to newest, and simply deleted all of the entries with dates from before 2013. We then deleted other erroneous entries, such as entries with empty fields that should not have been empty. Once we cleaned up the data, we created some new variables such as "trip duration" and "trip duration in hours". These preparation steps made it possible for us to effectively analyze the data. I think that we should have tried to look for outliers in the data and removed them; however, the assignment did not call for that.

11...