top of page

Cyclistic Bikes Data Analysis

Background 

This analysis is for the Google Data Analytics Certificate. I am assuming the role of a Junior Data Analyst at Cyclistic, a bike-share company based in Chicago. The company's bike subscription numbers have been stagnant, and the director of marketing believes that converting casual riders into annual members is the key to their future success. The aim of the analysis is to understand the differing behaviors between Cyclistic's two main users: annual members and casual riders. Identifying the difference between the two segments will inform marketing strategies that will help promote Cyclistic’s membership plan.


The data will be cleaned and analyzed using SQL and presented using Tableau. I will follow Google’s six-step data analysis process: ask, prepare, process, analyze, share, and act.


Ask

The major question driving the analysis is: “How do annual members and casual riders use Cyclistic bikes differently?” 


Prepare

The data for this analysis was collected from here. I used Quarter 1 data from 2024. Each table was in a separate .csv file, which I combined into a single table using this function in SQL:


  • I checked the length of characters in the ride_ids, which is the primary key. Then, I counted the total amount of rows. I also checked for any null values and duplicates in each row.

  • I also ran the following queries to find out how many stations and member types there are:





Process (Cleaning the Data)

  • To begin the processing stage of the analysis, I created a clean table with no null values.


  • After creating a clean table with no nulls, I added the following rows to make my analysis easier: 

    • Day of the week

    • Month

    • Day 

    • Year 

    • Ride length

    • Time of the day

  • I also only included data that has a valid time length (more than 1 minute and less than 24 hours).


Analyze

Once I finished the processing stage, the data was clean and ready to investigate the question, “How do annual members and casual riders use Cyclistic bikes differently?” To answer this, I broke down the rider composition and identified the difference in each group’s bike preferences and the time spent on the bikes. I analyzed the data using the following queries:


  • Determine the rider composition and bike preferences:


  • Calculate what day(s) of the week the bikes are being used the most/least:


  • Identify the month(s) in the first quarter when bike rides are most popular:


  • Identify the most popular hour each user rides the bikes


Share

Now that the analysis is complete, I will share the insights through visualizations using Tableau. Feel free to check out my dashboard here.


  • Insight #1: Annual members make up 74% of Cyclistic’s user base. The company aims to create a marketing strategy that will increase this figure.


  • Insight #2: Both annual members and casual riders prefer riding classic bikes. 


  • Insight #3: Members ride the bikes the most on weekdays while casual users ride the most on the weekends. This suggests that members might be using the bikes for commute while casual users ride for leisure. 


  • Insight #4: Members use the bikes mostly during work hours (8am-5pm). Casual users prefer mid-day rides (12pm-5pm). This supports the observation that members ride for work commute while casual users ride for leisure.

  • Insight #5: The fact that the bikes are in high demand for both user types at 5pm, means the bike supply is low at that time.


  • Insight #6: Both user groups ride the most in the warmer month of April. 


Act

Key takeaways:

  • Both users prefer classic bikes to electric.

  • Members likely use Cyclistic bikes for work commute while casual riders use the bikes for leisure.

  • There is likely limited availability of bikes for both user types at 5pm as demand is the highest at that time of the day.

  • The winter months (January & February) see the least number of riders. There is a steady increase in the ride trend for casual riders as we progress into the spring season.


Recommendations:

Based on the insights found from the analysis, my recommendations to convert casual riders into members are:

  1. Create a bike assurance system that ensures members have access to bikes during peak ride hours. This will incentivize casual riders to purchase a membership plan to ensure they have access to the bikes at the time of the day when the bike supply is low.

  2. Since demand is lower in the colder months (Jan- Feb), this may be the best time to run targeted promotional deals to maximize subscription rates in the winter.

  3. Create a subscription tier for casual riders who use the bikes mainly for leisure.

bottom of page