Hello, my name is Tobiloba Babajide, and this is my Google Data Analytics capstone project. This project is the first case study from the final course of the eight-course certificate program. This is the first case study I carried out after fully delving into the field of data analytics, so I hope you enjoy it!
In this case study, I work as a junior data analyst at the Chicago-based bike-sharing startup Cyclistic's marketing analyst team. Let me give you a short background on the company:
Cyclistic introduced a popular bike-share program in 2016. The initiative has expanded since then to include a fleet of 5,824 bicycles that are geo-tracked and locked into a system of 692 stations throughout Chicago. The bikes may at any time be released from one station and brought back to any other station in the network.
Up to this point, Cyclistic's marketing approach focused on raising public awareness and appealing to a wide range of consumer groups. Currently, the company operates with three payment plans:
single-ride passes;
full-day passes;
annual membership
The company's future prosperity, in the opinion of the marketing director, hinges on increasing the number of yearly subscriptions, as the company's financial analysts have come to the conclusion that annual members are more profitable than casual riders. My team is interested in learning how annual members and casual riders utilize Cyclistic bikes differently and will create a new marketing plan to turn casual riders into annual members based on these insights.
At the end of this case study, we will:
have a better understanding of how casual riders and annual members ride differently.
provide recommendations on how the casual riders can be converted to annual members.
In order to do this, we will follow the 6-step data analysis process:
Ask
Prepare
Process
Analyze
Share
Act
Let us begin the journey!
ASK
In this step, I identified the business task, i.e., the problem that had to be solved. The important questions for this business task are:
1. How do annual members and casual riders bike differently?
2. How do we convert casual riders into annual members?
At the end of this analysis, these questions would have been answered and recommendations will be provided to the marketing team on what areas to focus on in order to be able to turn casual riders into annual members.
PREPARE
In order to conduct this analysis, cyclistic travel data from the past 12 months (June 2021 to May 2022) were used.
The data used for this case study was provided by Motivate International, Inc. The data was made public for use by BikeShare (Lyft Bikes and Scooters) LLC, which operates the City of Chicago’s Divvy bicycle-sharing service.
PROCESS
The zip files containing 12 months of data were downloaded from the Motivate International, Inc. database. These files were then extracted and stored as Comma Separated Value (CSV) files in a local database. The CSV files were then converted into Microsoft Excel files and saved in a folder for cleaning.
Each Excel file contained 13 columns, namely:
ride_id: the id for each ride;
rideable_type: the type of bicycle used;
started_at: the start time for the trip;
ended_at: the end time for the trip;
start_station_name: the name of the station where the trip started;
start_station_id: the id of the station where the trip started;
end_station_name: the name of the station where the trip ended;
end_station_id: the id of the station where the trip ended;
start_lat: the latitude of the start station;
start_lng: the longitude of the start station;
end_lat: the latitude for the end station;
end_lng: the longitude for the end station;
member_casual: the kind of user (either an annual member or a casual rider)
I created a filter for the columns to further investigate the data. In doing this, I noticed that there were several empty cells in several rows in the following four columns:
· start_station_name
· start_station_id
· end_station_name
· end_station_id
Since missing data could lead to inaccurate analysis, I cleaned the data by deleting rows in which there were missing column data, and I did this for the 12 xls data files for the 12 months.
ANALYZE
While investigating the data, I noticed that most of the current users of the bike-share program are annual members, but on average, casual users tend to take longer rides than annual members:
To further analyse this data, two new columns were created:
ride_length: In this column, I calculated the length for each ride by finding the difference between the started_at and the ended_at columns by using the formula “=D2 – C2”.
day_of_week: In this column, I found the day of the week that each ride was taken from the started_at column by using the formula “=WEEKDAY(C2, 1)”
The two new columns are shown along with the filtered data sorted according to "start_station_name":
Using these new columns, I performed several analyses through the use of pivot tables. The pivot tables created for June 2021 are shown below:
These pivot tables were used to identify the relationship between:
The type of user (casual riders and annual members) and the average length of their rides
The type of user (casual riders and annual members) and the total number of rides
The total number of rides for each rideable type for each type of user.
SHARE
The analysis involved several steps and was a laborious procedure that demanded patience and unwavering commitment. Due to the size of the data set used for the research, hours were spent cleaning and structuring the data, but it was ultimately worthwhile.
From the analyses performed, the following relationships and trends were identified:
1. Over the last 12 months, casual riders of the rideshare have taken longer rides than annual members:
2. Over the last 12 months, annual members took more rides than casual users of the rideshare. Also, there was a significant drop in the total number of rides for both types of users between November 2021 and March 2022 (fall and winter seasons):
3. On average, casual users take more rides during the weekends, while annual members take more rides during the weekdays:
4. The average ride length throughout the week is evenly distributed for both casual riders and annual members, with casual users having a higher average ride length:
5. The classic bike is the most popularly used bike among both types of users, but the docked bike is only used by casual members:
ACT
Based on the insights that the analyses above provide, the following recommendations are to be made to the marketing analytics team and the executive team:
Annual members of the rideshare program can be charged less on weekends through the use of discounts for long weekend trips.
Instead of charging the annual members per distance traveled, charges can be made per trip during the weekdays.
For the classic bike, cheaper charges can be introduced for annual members.
During the summer and spring seasons, better deals can be provided for annual members.
I believe that with these recommendations, the goals set by the marketing analytics team will be accomplished and the company will see considerable growth.
The full dashboard is available below. Thank you for your time.