Cyclistic Case Study — Megan Moulos - Data Analyst & UI/UX Designer

A deep-dive into a Chicago bike-sharing company

Note: Cyclistic is a fictional company, this case study was completed as a capstone project for Google.

Cyclistic is a Chicago-based bike-share program that features more than 5,800 bicycles and 600 docking stations. Cyclistic users are more likely to ride for leisure, but about 30% use the bikes to commute to work each day. The bikes can be unlocked from one station and returned to any other station in the system anytime.

Cyclistic customers fall into two categories: those who purchase annual memberships known as members, and customers who purchase single-ride or full-day passes are casual riders.

Cyclistic’s finance analysts have concluded that annual members are much more profitable than casual riders. Although the pricing flexibility helps Cyclistic attract more customers, the marketing team believes that maximizing the number of annual members will be key to future growth. Rather than creating a marketing campaign that targets all new customers, Cyclistic shareholders believe there is a solid opportunity to convert casual riders into members.

Using historical data, how do members and casual riders use bikes differently?

Data sources

The data is open-source and publicly available through Motivate International Inc.:
https://divvy-tripdata.s3.amazonaws.com/index.html
The license agreement can be found here: https://www.divvybikes.com/data-license-agreement
Data privacy issues prohibit me from using riders’ personally identifiable information such as names or credit card numbers.
The case study covers data from 2023.

Data Cleaning & Manipulation

The data is contained in 12 separate CSV files, one for each month of 2023. I used Excel to clean and prepare the data for analysis.

Steps:

Added ride_length column which calculated the length of each ride in hours, minutes, and seconds.
Added ride_length_in_seconds to calculate how many seconds each ride was, to be able to perform calculations.
Added day_of_week column which calculated the day of the week that each ride started.
Removed all trips that lasted less than 1 minute.

Analysis

I began with the data for January 2023. After cleaning the data in Excel, I imported the CSV file into BigQuery. BigQuery allowed me to run SQL queries on the large data set. First, I made sure that the schema matched what I needed for analysis:

There were 184088 total rides in January of 2023. I then checked how many of those rides were by casual riders and how many by members. I also averaged the percent of the total rides from each group.

Member rides make up 79% of all rides in January 2023. I then figured out the most popular day for all riders, and then the most popular day for casual riders only. This could help decide when to place digital advertisements on social media.

The most popular day for riding for all riders is Tuesday, while the least popular is Saturday. Looking closer, I wanted to figure out the most popular day for casual riders, since the company will want to target those riders in particular for advertisement.

The most popular day for casual riders was also Tuesday.

This exploration into the January ride data helped me understand the kind of data I was working with. With this information in mind, I joined the 12 tables into quarters to find out if the popularity of ride days changes over the different seasons.

Q1 - January, February, March
Q2 - April, May, June
Q3 - July, August, September
Q4 - October, November, December

For Quarter 1, casual riders made up 23% of all rides, and the most popular day for casual riders was Sunday. Casual riders preferred electric bikes to any other type. The top 10 start stations are near popular attractions and hotels. I used Tableau to create images to help stakeholders understand the data.

In the second quarter, members only made up 59% of all rides, with casual riders increasing to 41%. There were also more rides overall, up 140% from the first quarter. This change could be because of the warmer weather in the spring and summer months. It could also be attributed to the fact that more people with children vacation over the summer, and therefore there would be more casual riders. The second quarter data also showed that similar starting and ending stations were popular with casual riders. The most popular day for riders was Saturday during April, May, and June.

I continued similar steps for the third and fourth quarters of 2023. My recommendations are presented below.

Recommendations

Based on the historical data analyzed I would provide the following recommendations to the marketing team.

Casual riders were more likely to use the Cyclistic bikes on the weekends. I would try to use social media marketing more heavily on Saturdays and Sundays year-round. Members use the bikes more frequently during the work week.
Casual riders were also more likely to visit stations near popular attractions such as Millenium Park and the Shedd Aquarium. Concentrating print advertisements near these attractions would make them more visible to casual riders.
Casual riders preferred electric bikes over any other type offered. Adding more electric bikes to the offerings may help convince casual riders to purchase an annual membership.
There are more casual riders in the summertime than in any other season. Perhaps another membership option could be created for the summer months to entice casual riders to join for a more long-term option.
All forms of advertising should highlight why becoming a member is a better option.