Case Study: How does a bike-share navigate speedy success?

To view the powerpoint click here.
To view the SQL scripts click here.

Background on Cyclistic

  • Bikeshare Company launched in 2016
  • Currently has fleet of 5,824 bikes and 692 stations throughout Chicagoland
  • Flexible riding plans: single-ride passes, full-day passes, and annual memberships
  • Finance has determined that annual members are much more profitable than casual riders
  • Director of marketing believes maximizing the number of annual members is key to future growth
  • Design marketing strategies aimed at converting casual riders into annual members

Business Questions to address

  • Three questions will guide the future marketing program:
    • How do annual members 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?
  • This study will be focusing on the first task

Prepare Data for Analysis

  • Obtained data made available by Motivate International Inc. at
  • Downloaded last 12 months of data from February 2023 to January 2024
  • Features of the data:
    • ride_id
    • rideable_type(electric, docked)
    • started_at
    • ended_at
    • start_station_name
    • start_station_id
    • end_station_name
    • end_station_id
    • start_lat
    • start_lng
    • end_lat
    • end_lng
    • member_casual

Process Data for Analysis

  • Used SQL for processing the data since there’s a little over 5 million records for the one year period (SQL scripts link)
  • Pulled each table and put all the tables together into one table within SQL
  • Calculated ride length in minutes for each record and month, day, and year using the ride start timestamp
  • Cleaning and Verification:
    • Several ride length times(2.5%) were less than or equal to zero since start time was earlier then end time; these data points were removed
    • Ensure that there is no NULL data for the member/casual classifiction

Analysis by Day of the Week

  • Members ride more frequently than casual members on all days of the week (about 193k more rides)
  • Difference is lesser on the weekends(52k more)
  • Casual riders ride most frequently on the weekends

Analysis by Day of the Week

  • Average riding time for casual riders is higher than members on all days of the week (16 minutes on average)
  • Highest riding averages for both members and casual riders are on the weekends

Analysis by Month

  • There’s an increase in ridership of members and casual riders in the summer and fall months(Jun.-Nov.)
  • There is significantly small number of rides by casual riders in the winter months (Dec.-Feb.)

Analysis by Month

  • Casual riders have a higher average ride time in the summer months (Apr.-Aug.)
  • Casual riders ride approximately 14 more minutes on average longer than members

Final Analysis Recommendations

  • Casual riders ride most frequently on:
    • Weekends
    • Summer and fall months
  • Casual ride minutes do not vary significantly by day or month of the year
  • Average time casual riders ride is much more than members