ANALYSIS OF BOLT IN THE FIRST WEEK OF LAUNCH USING MICROSOFT EXCEL.

bolt car service

Introduction

In a world filled with insecurities, Bolt provides a safe and secure alternative to public transport. If you’re in the mood for more comfort and privacy, you’ll get your own backseat space. Bolt is an all-in-one mobility app.

With Bolt, you can get more things done thereby increasing productivity and maximizing every minute of the day. From the backseat, you can sort through emails, make calls, or even take the chance to do your makeup for my ladies and men.

Additionally, bolt conducts background checks on all prospective drivers and vehicles and has a wide selection of safety features on the app, meaning you never have to doubt your safety.

This analysis aims to examine the supply and demand of bolt service over a week in a single city a few days after launch.

UNDERSTANDING THE DATA STRUCTURE

The data set is a 1-week demand and supply of bolt service. it contains two tables, the searches_raw and activity_raw tables both containing 169 rows with the former containing 4 columns and later, 9 columns.

The searches_raw dataset contains information on the date, people saw 0 cars(unique), people saw 1 car (unique), and coverage ratio (unique)orders, The columns include;

Date — date + hour for which the row of data is presented.

People saw 0 cars (unique) — number of users who did not see a car.

People saw +1 car (unique) — number of users who saw a car.

Coverage Ratio (unique) — % of users who saw a car.

An overview of the dataset

searches_raw dataset

The activity_raw dataset contains information on the date, active drivers, online(h), booking(h), waiting for booking(h), busy(h), hours per active driver, rides per online hour, and finished rides. The columns include;

Date — date and hour for which the row of data is presented.

Active drivers — number of active drivers (any level of activity) available during the period.

Online (h) — total supply hours that were available during the period.

Has booking (h) — total hours during which drivers had a client booking (any state).

Waiting for booking (h) — total hours drivers spent waiting for booking.

Busy (h) — total hours in which drivers were not available to take orders (coffee breaks, etc.).

Hours per active driver — the average number of hours each driver was online during period.

Rides per online hour — aka RPH — average. finished trips per online hour during the period.

Finished Rides — number of finished trips during the period

Activity_raw dataset

Data Cleaning and Preparation Steps:

The raw dataset required some cleaning and preprocessing to ensure its suitability for analysis. The following steps were performed using excel.

1. Data Loading: The raw dataset was already in Excel.
2. Handling Missing Values: checked to confirm there were no missing values.
3. Creating more relevant Data: Create more columns or data to contribute to the analysis. for example, created columns like new date, weekday, and hours. Below is the overview of added columns.

overview of the newly added columns

5. Data Type Conversion: Also ensured that the data types of each column were appropriate for analysis.

6. Data Validation: The data was validated to ensure that the quality met the analysis requirements.

The dataset was refined and made ready for insightful analysis and insights were generated using Excel by following these data cleaning and preparation steps. The cleaning procedure makes sure that the analysis’s findings are accurate and dependable. I went straight into answering the questions given after this.

For each hour of the week, calculate the total number of unique searches with 100% coverage.

Using the pivot table, the days of the week with 100% coverage are Monday, Tuesday, and Thursday.

result of 100% coverage ratio

Create a pivot table to illustrate the distribution of the total number of unique searches against Hours and Weekdays (24x7 grid).

The analysis showed that the greener the cells, the lower the coverage ratio, and hours between 3 pm to 5 am which are less green/more white have higher coverage. The higher coverage in those hours could be because it is the window of closing time for most workplaces.

Result of 24/7 grid

Illustrate in a chart the demand and supply throughout the week.

Using a line chart, the analysis showed that Thursday and Tuesday had the highest number of drivers(supply) with numbers 1562 and 1477 respectively. The day with the highest number of passengers(demand) is Friday (375). This may be because it’s the beginning of weekend and people are either going home after work or going out for different events.

pivot table for demand and supply

result of the line chat

In the form of a 24x7 grid (hour-weekday grid), visualize a coverage heatmap. This one will be sent to drivers to show when is the best time to go online (low coverage usually suggests higher demand) and make sure the format is appropriate for them.

Using a pivot table and conditional formatting, I visualized the coverage ratio by days of the week and hours of the day.

result of heatmap visualization

Find the 5-hour period (consecutive hours) with the highest demand and calculate how much we can spend on driver incentives, given that we want to re-invest all the revenue received in this timeframe. Assume that Finished Rides have an average value of €10 and our commission is 20%.

Firstly, using a pivot table, I brought out the hours of the day and the sum of finished rides.

result of the hours/finished rides table

Secondly, I filtered out the 5-period consecutive hours with the highest demand(finished rides). I then calculated the sum of revenue since we were given the average value of finished rides to be 10 pounds.

Subsequently, I calculated the commission, and since we were given 20%, I used that which is the sum of revenue multiplied by 0.2(20/100). Lastly, I then deduced the net revenue by subtracting the commission from the total revenue.

result of 5 consecutive hours with the highest demand

Estimate the number of weekly trips we could have done (finished) with maximum Coverage Ratio.

This analysis showed the total number of estimated weekly trips. To calculate the estimated weekly trips, I needed the coverage ratio and the people that saw cars.

Friday had the highest number of estimated weekly trips with a total number of 85,284 and Monday was the least with a total number of 56,300.

result of estimated weekly trips

Find the 5 most utilized hours of the week (not necessarily consecutive), and calculate the sum of trips finished in these 5 hours.

Using the pivot table, I showed the non-consecutive 5 hours that were used the most which are 19(7 pm), 23(11 pm), 18(6 pm), 09(9 am), and 17(5 pm).

result of 5 most utilized hours

result of 5 most utilized hours

Recommendations

• The drivers should be given incentives like discounts, during peak hours so they can earn more which will in turn motivate them to work during those hours.
• To improve customer service and waiting time, analyze and detect hours with low supply and high demand and assign more drivers to those hours.
• Keep an open line of communication with your drivers to understand their needs and preferences. Use feedback to improve incentive programs and ensure that drivers feel valued and motivated.
• Put in place higher pricing (surge pricing) during peak hours to balance demand and supply. The heatmap analysis will help identify the specific hours when multipliers should be applied. These multipliers should be communicated clearly to both drivers(supply) and riders(demand).
• Analyze the hours with the highest utilization rates and aim to replicate these conditions during other times. For example, if specific hours consistently show high driver utilization, study these periods to understand what factors contribute to this efficiency and apply similar strategies during other hours.
• The transport demand(riders) and supply(drivers) flow changes often. Frequently check the data and adjust your strategies accordingly. Use the data analysis to continuously refine the balance between supply and demand, pricing, and driver incentives.

Limitations

It is important to note that this analysis was performed using data from just one week after launch which may not accurately show the demand and supply patterns. This analysis also does not account for other factors such as market conditions, seasonal variations, driver availability, and customer sensitivity.

Conclusion

In conclusion, the insights gained from this analysis can be used to optimize service quality, ensure a better balance between supply and demand, and maximize both driver and customer satisfaction, eventually leading to a more well-organized profitable operation.

Thank you for reading.

My name is Chinelo and my goal is to provide valuable insights that can enhance business growth, keep the motivation to learn more burning, and connect with like-minded people in this field.

NB: I am open to a job role.

Connect with me on Twitter and LinkedIn.

Don’t forget to follow me here and check out my Medium profile for more interesting projects.