Case Study: How Does a Bike-Share Navigate Speedy Success?

Quick Links

• Portfolio
• Github [COMING SOON]
• Data Viz (Tableau)

Scenario

I am a junior data analyst working on the marketing analyst team at Cyclistic, a bike-share company in Chicago. The director of marketing believes the company’s future success depends on maximizing the number of annual memberships. Therefore, my job is to understand how casual riders and annual members use Cyclistic bikes differently. I will present my findings to stakeholders to determine effective marketing strategies to convert casual riders into paid members.

About Cyclistic

In 2016, Cyclistic launched a successful bike-share offering. Since then, the program 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 at any time.

Until now, Cyclistic’s marketing strategy relied on building general awareness and appealing to broad consumer segments. One approach that helped make these things possible was the flexibility of its pricing plans: single-ride passes, full-day passes, and annual memberships. Customers who purchase single-ride or full-day passes are referred to as casual riders. Customers who purchase annual memberships are Cyclistic members.

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, Moreno 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, Moreno believes there is a solid opportunity to convert casual riders into members. She notes that casual riders are already aware of the Cyclistic program and have chosen Cyclistic for their mobility needs.

Moreno has set a clear goal: Design marketing strategies aimed at converting casual riders into annual members. In order to do that, however, the team needs to better understand how annual members and casual riders differ, why casual riders would buy a membership, and how digital media could affect their marketing tactics. Moreno and her team are interested in analyzing the Cyclistic historical bike trip data to identify trends.

Key Stakeholders

• Cyclistic: A bike-share program that features more than 5,800 bicycles and 600 docking stations. Cyclistic sets itself apart by also offering reclining bikes, hand tricycles, and cargo bikes, making bike-share more inclusive to people with disabilities and riders who can’t use a standard two-wheeled bike. The majority of riders opt for traditional bikes; about 8% of riders use the assistive options. Cyclistic users are more likely to ride for leisure, but about 30% use the bikes to commute to work each day.
• Lily Moreno: The director of marketing and my manager. Moreno is responsible for the development of campaigns and initiatives to promote the bike-share program. These may include email, social media, and other channels.
• Cyclistic marketing analytics team: A team of data analysts who are responsible for collecting, analyzing, and reporting data that helps guide Cyclistic marketing strategy. I joined the team six months ago and have been busy learning about Cyclistic’s mission and business goals, as well as how you, as a junior data analyst, can help Cyclistic achieve them.
• Cyclistic executive team: The notoriously detail-oriented executive team will decide whether to approve the recommended marketing program.

Ask

There are three questions we need to answer for this analysis:

1. How do annual members and casual riders use Cyclistic bikes differently?
2. Why would casual riders buy Cyclistic annual memberships?
3. How can Cyclistic use digital media to influence casual riders to become members?

For now, I will answer the first question of how annual members and casual riders use Cyclistic bikes differently.

Prepare

The first thing I did was download 12 monthly files from January 2023-December 2023 from the divvy-tripdata index to analyze historical data and identify trends. The data is available due to this license. There is a limitation due to data privacy issues that prohibits certain information, such as personal identification information (PII) of riders or members, such as credit card numbers. The files were downloaded to a special folder for the raw data and eventual clean data.

Dataset observations:

The datasets are stored as downloadable .csv files. Opening the dataset in Excel, I observed that the dataset contains 13 columns, which include the ride ID, the type of bike used, start and end dates and times, station names and IDs, as well as the member type.

The files had missing blank cells in the station_name and station_id columns, as well as sometimes in the longitude and latitude columns.

Does the data follow ROCCC?

The data does follow ROCCC:

Reliable: The data is reliable because it was obtained firsthand.

Original: The data is original because it is provided by the company as downloadable files.

Comprehensive: The data is comprehensive because of the large amount of data they contain.

Current: The data is current as it is from the most current year.

Cited: The data is cited on the download page.

The data is comprehensive in that the datasets have a lot of information to pull from and analyze. The data is also current, being from 2023.

Process

Part 1 (Microsoft Excel)

I opened each file month by month in Microsoft Excel before starting my data cleaning process, which is as follows:

• I checked each individual file for duplicates, with Excel removing any duplicate values found.

• I applied uniform formatting to the dataset by changing text alignment, adjusting column width, and adding a bold font style to the header row.
• I split the date and time in the started_at and ended_at columns into two separate columns to extract the date and time separately. I named these new columns started_at_time and ended_at_time respectively.

• I created a new column with the name ride_length to calculate the duration of the rides for casual users and members by subtracting the ending time from the beginning time. I used the Time> 37:30:55 format for this column.
• I created another column and used the WEEKDAY function to determine which day of the week the date fell on with a numbering system of 1 through 7 with 1= Sunday and 7= Saturday.
• I deleted the columns start_lat, start_lng, end_lat, and end_lng as they were not needed for my final analysis.
• I filtered and deleted table rows where ride_length was a negative number or contained 00:00:00.
• I highlighted and deleted blank rows using Ctr+G and selected "blank" for the table.

Part 2 (Google BigQuery)

Once I had finished my initial cleaning in Microsoft Excel, I exported all the cleaned data as- .csv files to a folder I created ahead of time in my capstone project folder. I then uploaded the folder with the cleaned data to a Google Storage Bucket before uploading each table into Google BigQuery.

Once I combined the files into one table, I further cleaned the data by checking and deleting any null values I missed in my original sweep through in Excel.

Analyze

Once I had cleaned the data to a point where I was satisfied in Google Big Query, I then analyzed parts of the data. To start my analysis, I totaled the number of members and casual riders.

Then, I analyzed how casual riders and members use the bikes by days of the week. I analyzed which day of the week was the busiest for casual riders vs. which days were busiest for members.

Next, I analyzed which months casual riders and members used the most bikes.

After that, I analyzed the average ride length for casual riders vs. annual members.

Share (Tableau)

I decided to use Tableau Public to create my dashboard.

From my visualization, I was able to analyze the habits of members and casual riders. From my analysis, I observed these behaviors in casual members:

• Casual riders preferred classic bikes over electric bikes
• The most busy month for casual riders is July, while January is the least busy month
• Saturday is the busiest day for casual riders
• The highest average hours for casual riders is on Thursday

The behaviors of members pointed to:

• Members like classic bikes over electric bikes
• August is the month with the most trips, while February has the least amount of rides
• Thursday is the best day for members
• Kingsbury St. & Kinzie St. is the top station for members

(note: this forum post helped me with converting time duration.)

Act

Based on my analysis, I think Cyclistic should advertise closer to July and appeal to weekend casual riders to turn them into members. Cyclistic should offer casual riders more classic bike options than electric bikes.