Bike Share Analysis using Python and Power BI (Part 2)

In the second part will focus on the problems that need to be answered to solve business problems. Let’s remember again that the marketing director assumes that the company will profit if the number of annual members increases. So as a junior data analyst, you need to look for patterns that occur in customers, how is the difference between using bicycles for regular customers and annual members?

My Dashboard Divvy using Power BI

Here I will answer questions using python instead of power bi, because my power bi account is free and cannot be shared. before going into the first question I will explain again the library that I use in data visualization that uses plotly, for output by default I display the png format so that when it loads it is not too heavy.

import plotly.express as px
import plotly.io as pio
pio.renderers.default = "png"

Preparation is complete, let’s extract the data that has been cleaned. I will shorten Phase 4–5 (Analyze and Share) into one sub to make it more concise and easy to understand. Share itself is a phase where the analysis results can be interpreted through visualization, in this analysis Power BI is the tool I use.

1. The comparison of the last year is the number of annual members vs regular members

During the last year, starting from October 2021 to September 2022, annual members still have active users with a total of 2.6 million users compared to regular users which are only 1.8 million. However, if you look at it again from the side of the duration of renting a bicycle for regular members, it turns out that it is longer in the duration of the rental. Because the purpose of this business problem is to make regular member become annual members, let’s explore further why regular customers use the average bicycle for quite a long time?

2. Total trips and average trips based on the type of bike used

I will use the column for the type of bicycle used, with this it will be seen in more detail which bicycle is used with the longest average trip, especially for regular users (non-subscription).

as can be seen that the classic bike is the highest used type of bike from both regular members and annual members. Inversely different on the duration of the rental where the problem in the first question can be answered here because the regular type of customer has an additional type of bicycle, namely a docked bike. Docked bike implies a bike that’s planned to be bolted or secured from unauthorized utilize by being bolted or secured to a dock, rack, sharing station, or other protest that’s given beneath the program.

3. Total trips and average trips by hour

let’s look at the pattern based on the time the rental starts from the total trip, the average trip, and also the result of the type of bicycle used.

The pattern shown by the start of the rental in hourly format from the started_at column can be seen that the number of users above 165 thousand is between 15.00–19.00 with the highest peak at 17.00. these hours are the hours when office employees are renting bicycles to go home from work. The average rental hour duration is from 10:00 to 15:00 used by regular customers, which is a high average because it is represented by the type of docked bicycle.

4. Total trips and average trips by day

the result of the pattern from the third question still feels vague because the pattern is not very visible on the docked bike, the last thing I want to try is grouping by day.

Doesn’t change the fact that the classic bike type is the most popular but there is a slight difference between regular customers and annual members. Regular customers with active users above 200k only available on Saturdays, while annual members are quite stable every day getting 200k active users only on classic bicycle types. The thing that can be seen here is that on weekends there are quite a lot of regular customers who rent. But on the other hand, an interesting fact is obtained on the type of docked bicycle that the average length of rental is very stable every day, even though it has a small number of users but it can help to increase the duration of the rental.

5. Total trips from and to station by user type

then what if viewed from the station? Where is the busiest rental start and return station?

If we look at the average rental duration, we agree that the docked type is the longest rental bike. The bicycle dock type location is heavily used from Dusable Harbor station to Wells St & Elm St at 6pm for 678.4 hours in the last year and this is huge. Streeter Dr & Grand Ave is a busy station from rental to bike return stations. DuSable Lake Shore Dr & Monroe St and DuSable Lake Shore Dr & North Blvd are the second and third busy stations. from these three stations can be used for a campaign to increase the number of membership.

6. Act

Entering the last phase, which is the act of discussing the results and recommendations of an analyst to answer the problems that occur. Insights and analysis results are expected to help the marketing director decide what steps are appropriate for the strategy to be implemented. After the five questions are answered, I will give 3 recommendations that can be done to increase the number of annual members from regular members:

• Seasonal campaigns need to be done in May to September. By using inbound marketing techniques that utilize superior content from the benefits obtained from annual members to attract upgradability from regular members.
• Launched a new docked bicycle type product at the annual member. The data shows that although docked users are only available to regular members, a campaign needs to be carried out to make the docked user transition to become annual members which will be effective enough to increase the number of annual members.
• On weekends the number of active users of regular members increases quite a lot compared to normal days. use email marketing to share interesting promos specifically for regular members who often rent and return bicycles at busy stations such as Streeter Dr & Grand Ave, DuSable Lake Shore Dr & Monroe St and DuSable Lake Shore Dr & North Blvd.

This is the end of the first project of the data analytics course I got from google. Thank you for reading the results of my project analysis, criticism and suggestions are very welcome to help me develop even more ^^. I hope in the future I can write again the project I made or give tutorials about data, business, or design? let’s see hehehe, I’m very happy to be able to share information.

You can visit the complete source code via github, to download the Power BI interactive dashboard file at this link. Find me through via Instagram and LinkedIn ^^.