Cyclistic Case Study using MySQL
I just completed the Google Analytics Certificate course on Coursera. And by the end of the course they give us the capstone project to help us create our portfolios in data analytics. They even provide us the steps how to start analyzing. One of the capstone projects is Cyclistic company, and my role is junior data analyst that will help the marketing team to design the new strategy.
About The Company
Cyclistic is a bike-share offering company. In 2016, Cylistic has grown to a fleet of 5.824 bicycles that are geotracked and locked into a network of 692 stations across Chicago. The bikes can be unlocked from one station and returned to any other station in the system anytime. Cyclistic offers three pricing plans: single-ride passes, full-day passes, and annual membership. Single-ride passes and full-day passes are referred to as casual riders. And the annual memberships are referred to as member riders.
Problem: Cyclistic’s marketing strategy still relied on building general awareness and appealing to broad consumer segments.
Solution: Help Cyclistic’s marketing strategy to be targetted on particular customers that will convert casual riders to member riders.
Analytics goal:
HOW member and casual riders use Cyclistic bikes differently?
WHY would casual riders buy Cyclistic annual memberships?
HOW can Cyclistic use digital media to influence casual riders to become members?
The data contains Cyclistic’s customers historical data from April 2020 to March 2021. I got the data from Google Analytics Capstone Projects. But recently people upload it on Kaggle. Here’s one of them: https:/www.kaggle.com/mihelic/cyclistic
I use MySQL to merge and clean the data, here is my code: SQL
To visualize the data I use Tableau, here is the link: Tableau
Here are the steps that I did to analyze the data,
- The number of rides by day
Number of rides have been increasing in Sunday and Saturday. While the number of rides in Monday until Friday is around 400k and above.
2. Number of member and casual riders
The number of member riders is more than casual riders. The differences is around 600K.
3. Average of ride length by day of week
The Average of ride length for member riders are slightly different each day. The highest ride length is on Saturday. Therefore, the highest average of ride length for casual riders in Sunday, it is 47 minutes long. Casual riders likely to ride a bike in Sunday.
4. Total Average of ride length for member and casual riders
The average ride length of casual riders is longer than member riders. Casual riders tend to use Cyclistic longer than member riders, although the number of riders for member is more than casual riders.
We will see when casual riders spend the most time by dividing the day to the part of time. I divided the day to for parts, there are Morning is between 06:00 A.M to 11:59 AM, Afternoon is between 12:00 PM to 04:59 PM, Evening is between 05:00 PM to 07:59 AM, and Night is between 08:00 PM to 05:59 AM.
5. The number of member casual rides by part of the day
The casual riders tend to use Cyclistic in the afternoon and in the evening. But the member riders are using Cyclistic in the Afternoon and in the morning. And from here we will see the number of member casual rides by part of the day on each day.
6. The number of member casual rides by part of the day on each day
A lot of casual riders use Cyclistic in the Sunday and Saturday afternoon, and many of them use it at night more than member riders. The member riders using cyclistic have slightly differences in the Morning and in the evening at weekdays. This shows the casual riders are actively using cyclistic on the weekend, and member riders on the weekdays.
7. Type of bike that used the most by member casual riders
Docked bike is the most popular for both member and casual riders. The second place for the most popular type of bike is electric bike, and the last place is classic bike.
8. The most popular months
The most popular months are July, August, and September. Because July and August are summer season in Chicago, and in September the weather is transitioning from summer to autumn. That is the best weather to go on a bike. The least months are December, January and February because those are the winter season in Chicago.
9. The top 10 start and end station for casual riders
The top 10 of the start and end stations are exactly the same but it ranks different.
The summary:
The casual riders take rides longer than member riders. Most of casual riders use Cyclistic on the weekend in the afternoon, while the member riders often use Cyclistic on the weekdays from morning until evening they only have slightly differences number of ride.
To convert casual riders to member riders we can give them discount for afternoon rides on the weekend because the most popular times to ride for casual members are on Sunday afternoon and Saturday afternoon.
We can promote and influence casual riders starting in July because the most popular months to ride are July until September for both casual and member riders. By using advertisement on social media, we can target the casual riders based on the top 10 start and end stations. We can influence them by giving them discount for afternoon ride if they convert to member riders, the optimal time to promote the ads is the early month of July.
Thanks for reading! Feel free to write your thoughts in the comment section below!:)
