Cyclistic Bike-Share Case Study

The Google Data Analytics Capstone Project — Case Study 1: “Differentiating Casual Riders and Annual Members — Cyclistic Bike Share Analysis.”

Photo by Alessandra Caretto on Unsplash

Quick Links:

Dataset:

SQL Queries

Power BI Dashboard

Tools Used: Excel, SQL in BigQuery, Power BI

BACKGROUND — About the Company

Cyclistic is a bike-share company based in Chicago. Bikes can be unlocked from one station and returned to another within the system at anytime. The company’s marketing strategy appealed to a broad consumer base by having a flexible pricing plan: 1) single-ride passes, 2) full-day passes, and 3) annual membership. Customers who purchase single-ride and full-day passes are reffered to as Casual Riders. Customers who purchase annual memberships are Cyclistic Members.

Lily Moreno, the director of marketing, has set a goal to design marketing strategies aimed at converting casual riders into annual members to boost revenue and growth. In order to do so, the team needs to better understand the differences in how annual members and casual riders use the service.

The team has come up with three questions to guide the future marketing program:

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?

Moreno has assigned me the first question to answer: How do annual members and casual riders use Cyclistic bikes differently?

In this scenario, I am a junior data analyst on the Marketing Analytics Team. I am assigned to analyze historical bike trip data in order to identify trends. Using those trends, I am tasked to produce compelling data and professional data visualizations with insights and recommendations for marketing strategies to convert casual riders to annual members.

ASK

Business Task:

Identify trends between riders using historical data in order to implement an effective marketing strategy aimed at converting casual riders into annual members.

Key Stakeholders:

• Lily Moreno: Director of Marketing and my manager.
• Cyclistic Marketing Analytics Team: team of data analysts responsible for collecting, analyzing, and reporting data to help guide Cyclistic marketing strategy.
• Cyclistic Executive Team: team that decides whether or not to approve any marketing recommendations.

PREPARE

For the purpose of my analysis, I will be using the Cyclistic trip data from January 2021 — December 2021. The data is stored in 12 separate CSV files, one for each month of the year. The data is made available by Motivate International Inc. under this license.

Each file contains 13 columns. I have listed the columns and a short description of each below:

1. ride_id | id of bike rider
2. rideable_type | different type of bikes: docked, classic, electric
3. started_at | timestamp of when the ride started
4. ended_at | timestamp of when the ride ended
5. start_station_name | name of the station where bike was retrieved
6. start_station_id | specific id where the bike was retrieved
7. end_station_name | name of the station where bike was dropped off
8. end_station_id | specific id where bike was dropped off
9. start_lat | start latitude
10. start_lng | start longitude
11. end_lat | end latitude
12. end_lng | end longitude
13. member_casual | type of rider: member, casual

PROCESS

The entirety of my SQL codes can be viewed via my github, here.

Data Cleaning

I opened each CSV file in Excel:

• Removed all duplicates.
• Removed unnecessary spacing using trim() function in the start and end station names columns.
• Created new ride_length column by subtracting started_at from ended_at.
• Changed time format of ride_length to 37:30:55.
• Created new day_of_week column using the weekday() function with numbers 1–7 to represent Sunday-Saturday respectively.
• Formatted day_of_week as a number with no decimals
• Created ride_month column with numbers 1–12 to represent January-December respectively.

Data Manipulating

I decided to use SQL due to the size of the data.

• uploaded the CSV files as a bucket to be able to upload them as tables into BigQuery under “Original_Tables” in order to easily access them.
• merged all files into one large table titled “alldata” using the UNION ALL function and removed all nulls

• made the following changes for a clearer understanding of column names: “rideable_type” to“bike_type” , “member_casual” to “customer_type”
• got rid of any unnecessary columns ( start_station_id, end station_id, start_lat, start_lng, end_lat, end_lng) by creating a new, more simplified table titled “summary_table”

ANALYZE

Note: All data is grouped by customer type. There is typo within the data tables. “costumer_type” was corrected to “customer type”

1. Total number of riders

2. Number of riders per bike type

3. Number of riders per month

4. Number of riders per day

5. Number of riders per hour

6. Total average ride length in minutes

7. Average ride length per day in minutes

7. Average ride length per month in minutes

8. Average ride length by bike type in minutes.

9. Top 10 bike stations overall

10. Top stations for casual riders

11. Top stations for members

SHARE

My full interactive dashboard using Power BI can be viewed here.

**Note** : The dashboard can be navigated via the menu button located in the top left corner. The dashboard can also be filtered by costumer type, day, and month from the drop down tool within the navigation panel.

Total Rides

Members are the majority with 2.5 million rides taken in 2021, or 55% of the total. Casual riders make up 2.0 million rides and 45% of the total.

Bike Use by Day of the Week:

Saturday was the most popular day overall, with 825K rides. Sunday came in second with 715K rides.

Bike Use By Month:

Most rides were taken in the late summer and early fall, between July and September — with July having the most rides at 692K. The least favored months were during the winter season, with February having only 43K rides.

Bike Use by the Hour:

Hours are shown in the scale of a 24-hour clock starting at 0 = midnight (12AM). The most active time of day was 4–6pm. Members showed increases at 8AM, 12PM, and 5PM — indicating daily commute usage. Casual riders had a steady increase in bike usage from 12PM-5PM, peaking at 5PM.

Ride Length

Casual riders rode longer (32.5 mins) than members (13.2 mins)

Ride Length by Day of the Week:

Both groups took longer rides on weekends, with Sundays seeing the longest (37.6 mins for casual riders, 15.2 mins for members). This indicates that while members use the bikes more consistently weekly, both ride longer on weekends.

Ride Length by Month:

Members maintained consistent ride lengths throughout the year, indicating that their bike usage is for everyday purposes while casual riders use this service more for leisure.

Bike Stations

Seen are the top 10 most visited starting and ending stations. The most visited bike station overall was Streeter Dr. & Grand Ave. for both customer types.

Most Visited Bike Stations for Casual Riders:

Of the top 10 most visited stations, 6 of them were most visited by casual riders. They preferred stations located near tourist attractions and entertainment — such as parks, theaters, and aquariums.

Most Visited Bike Stations for Members:

Members preferred stations located in downtown areas.

Bike Types

Bike Usage per Bike Type:

Classic bikes were preferred among both customer types, making up 70% of the rides. Electric bikes made up 22% of rides. Only casual riders chose docked bikes, making up only 7% of all rides taken.

Ride Length per Bike Type:

Casual riders had longer ride lengths than members. Docked bikes had the longest average duration for casual riders even though they were the least preferred.

ACT

Key Takeaways:

Based on my analysis, here are potential factors prohibiting casual riders from paying for annual memberships:

Bike Types:

• Both customer types preferred classic bikes
• Members showed a higher inclination for electric bikes as compared to casual riders.
• Casual riders had a greater preference for docked bikes compared to members.

Monthly Usage:

• All customer types preferred seasons with warmer months, as expected.
• Casual riders favored the Summer: June-August were the busiest months.
• Members rode most mid-Summer to early fall: July-September.

Daily Usage:

• Members rode consistently throughout the week, peaking on Wednesdays, indicating that members most likely use this service for purposes outside of leisure — like commuting or everyday errands.
• Casual riders rode most frequently on weekends, specifically Saturdays.

Hourly Usage:

• For members, the busiest hours coincided with school and working hours — 8AM, 12PM, and 5PM.
• Casual riders increased in usage steadily throughout the day, peaking at 5PM.

Ride Durations:

• Casual riders rode for longer lengths of time, especially on weekends in warmer months.
• Members had shorter ride lengths but rode at more consistent durations, suggesting that usage is linked to daily routines and commutes.

Bike Stations:

• Members frequented stations that were located downtown — colleges, office buildings, and residential areas.
• Casual riders visited bike stations primarily by large attractions and entertainment such as parks, theaters, and aquariums.

Top 3 Recommendations:

My top 3 recommendations to convert casual riders into annual members are as follows:

1. Exclusive Discounts

Casual riders used Cyclistic Bike-Share at large and in longer durations on weekends. Providing exclusive member-only discounts for weekends and longer rides can encourage casual riders to pay for annual memberships.

2. Locational Promotions

Advertise at bike stations most frequented by casual riders, especially at stations near large tourist attractions and entertainment.

3. Digital Marketing Campaign

Use targeted marketing during peak activity hours, specifically on weekends and during Summer, to highlight the cost-savings advantages of annual membership.