Case Study: How Does a Bike-Share Navigate Speedy Success?
Alex Alvarado
2024-07-01
Introduction
This case study is part of the Google Data Analytics Professional Certificate. In order to answer business questions, this Google Certificate outlines the data analysis process: Ask, Prepare, Process, Analyze, Share, and Act.
This case study will showcase my data analyzing skills using the steps briefly mentioned above, using the R programming tool to collect, prepare, clean, analyze, and create visuals to tell a story of what the data is telling us. The case study is based on a hypothetical marketing analyst team working for a bike-share company (Cyclistic) in Chicago. The data is sourced from Motivate International Inc under agreement.
Case Study Challenge
Cyclistic wants me to thoroughly analyze how annual members and casual riders use Cyclistic bikes differently in order to design a data-driven marketing strategy aimed at converting casual riders into annual members.
Ask
Key Objective: To clearly define the business problem or question you aim to solve.
Cyclistics was founded in 2016, it rapidly became a successful small company because it offered a great bike-share service. As of now, the company has 5,824 bicycles with a network of 692 stations across Chicago.
Cyclistic aims to market to broad consumer segments, providing flexibility in their pricing plans:
- Single – Ride Passes
- Full – Day Passes
- Annual memberships
Cyclistics uses “Casual” to describe the riders that prefer the single-ride or full-day passes. They use “Members” to describe riders who subscribe to annual memberships.
Cyclistic’s finance analysts have confirmed that annual members are much more profitable than casual riders. The director of the marketing team and my manager, Lily Moreno, has set a clear goal: convert casual riders into annual members. As part of the Data Analytics team, Lily has assigned me the responsibility of determining how annual members and casual riders use Cyclistic bikes differently by using data to tell a compelling story.
Deliverables: Clear statement of the business task: our goal is to analyze how annual members and casual riders use Cyclistic bikes differently to inform marketing strategies aimed at converting casual riders into annual members.
Prepare
Key Objective: Gather, organize, and ensure the quality of the data needed for analysis.
The data used in this case study has been made available by Motivate International Inc. under this license. The datasets are formatted as ‘csv’ files and can be downloaded here.
# Load Tidiverse Package
library(tidyverse)
library(knitr)
library(scales)
# Importing and renaming Datasets
data1 <- read_csv("Downloads/data - case study - bikes/202306-divvy-tripdata.csv")
data2 <- read_csv("Downloads/data - case study - bikes/202307-divvy-tripdata.csv")
data3 <- read_csv("Downloads/data - case study - bikes/202308-divvy-tripdata.csv")
data4 <- read_csv("Downloads/data - case study - bikes/202309-divvy-tripdata.csv")
data5 <- read_csv("Downloads/data - case study - bikes/202310-divvy-tripdata.csv")
data6 <- read_csv("Downloads/data - case study - bikes/202311-divvy-tripdata.csv")
data7 <- read_csv("Downloads/data - case study - bikes/202312-divvy-tripdata.csv")
data8 <- read_csv("Downloads/data - case study - bikes/202401-divvy-tripdata.csv")
data9 <- read_csv("Downloads/data - case study - bikes/202402-divvy-tripdata.csv")
data10 <- read_csv("Downloads/data - case study - bikes/202403-divvy-tripdata.csv")
data11 <- read_csv("Downloads/data - case study - bikes/202404-divvy-tripdata.csv")
data12 <- read_csv("Downloads/data - case study - bikes/202405-divvy-tripdata.csv")Now that the previous 12 months of Cyclistic trip data has been
collected, downloaded, extracted from zip files, stored and renamed, we
can understand the data better by using a couple functions in R.
colnames() and glimpse() are useful functions
in R, with colnames() allowing you to see and change column
names in a dataset, and glimpse() providing a quick and
concise overview of your dataset. After reviewing the column names in
the two datasets below, I confirmed they match perfectly, allowing us to
join them into one file.
Inspect Data
colnames(data1)## [1] "ride_id" "rideable_type" "started_at"
## [4] "ended_at" "start_station_name" "start_station_id"
## [7] "end_station_name" "end_station_id" "start_lat"
## [10] "start_lng" "end_lat" "end_lng"
## [13] "member_casual"colnames(data2)## [1] "ride_id" "rideable_type" "started_at"
## [4] "ended_at" "start_station_name" "start_station_id"
## [7] "end_station_name" "end_station_id" "start_lat"
## [10] "start_lng" "end_lat" "end_lng"
## [13] "member_casual"glimpse(data1)## Rows: 719,618
## Columns: 13
## $ ride_id <chr> "6F1682AC40EB6F71", "622A1686D64948EB", "3C88859D92…
## $ rideable_type <chr> "electric_bike", "electric_bike", "electric_bike", …
## $ started_at <dttm> 2023-06-05 13:34:12, 2023-06-05 01:30:22, 2023-06-…
## $ ended_at <dttm> 2023-06-05 14:31:56, 2023-06-05 01:33:06, 2023-06-…
## $ start_station_name <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ start_station_id <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ end_station_name <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ end_station_id <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,…
## $ start_lat <dbl> 41.91, 41.94, 41.95, 41.99, 41.98, 41.99, 41.88, 41…
## $ start_lng <dbl> -87.69, -87.65, -87.68, -87.65, -87.66, -87.68, -87…
## $ end_lat <dbl> 41.91, 41.94, 41.92, 41.98, 41.99, 41.94, 41.88, 41…
## $ end_lng <dbl> -87.70, -87.65, -87.63, -87.66, -87.65, -87.65, -87…
## $ member_casual <chr> "member", "member", "member", "member", "member", "…The str() function is useful because it allows inspection of the data frames to look for inconsistencies, ensuring they stack together correctly when the datasets are joined.
str(data1)## spc_tbl_ [719,618 × 13] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
## $ ride_id : chr [1:719618] "6F1682AC40EB6F71" "622A1686D64948EB" "3C88859D926253B4" "EAD8A5E0259DEC88" ...
## $ rideable_type : chr [1:719618] "electric_bike" "electric_bike" "electric_bike" "electric_bike" ...
## $ started_at : POSIXct[1:719618], format: "2023-06-05 13:34:12" "2023-06-05 01:30:22" ...
## $ ended_at : POSIXct[1:719618], format: "2023-06-05 14:31:56" "2023-06-05 01:33:06" ...
## $ start_station_name: chr [1:719618] NA NA NA NA ...
## $ start_station_id : chr [1:719618] NA NA NA NA ...
## $ end_station_name : chr [1:719618] NA NA NA NA ...
## $ end_station_id : chr [1:719618] NA NA NA NA ...
## $ start_lat : num [1:719618] 41.9 41.9 42 42 42 ...
## $ start_lng : num [1:719618] -87.7 -87.7 -87.7 -87.7 -87.7 ...
## $ end_lat : num [1:719618] 41.9 41.9 41.9 42 42 ...
## $ end_lng : num [1:719618] -87.7 -87.7 -87.6 -87.7 -87.7 ...
## $ member_casual : chr [1:719618] "member" "member" "member" "member" ...
## - attr(*, "spec")=
## .. cols(
## .. ride_id = col_character(),
## .. rideable_type = col_character(),
## .. started_at = col_datetime(format = ""),
## .. ended_at = col_datetime(format = ""),
## .. start_station_name = col_character(),
## .. start_station_id = col_character(),
## .. end_station_name = col_character(),
## .. end_station_id = col_character(),
## .. start_lat = col_double(),
## .. start_lng = col_double(),
## .. end_lat = col_double(),
## .. end_lng = col_double(),
## .. member_casual = col_character()
## .. )
## - attr(*, "problems")=<externalptr>str(data2)## spc_tbl_ [767,650 × 13] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
## $ ride_id : chr [1:767650] "9340B064F0AEE130" "D1460EE3CE0D8AF8" "DF41BE31B895A25E" "9624A293749EF703" ...
## $ rideable_type : chr [1:767650] "electric_bike" "classic_bike" "classic_bike" "electric_bike" ...
## $ started_at : POSIXct[1:767650], format: "2023-07-23 20:06:14" "2023-07-23 17:05:07" ...
## $ ended_at : POSIXct[1:767650], format: "2023-07-23 20:22:44" "2023-07-23 17:18:37" ...
## $ start_station_name: chr [1:767650] "Kedzie Ave & 110th St" "Western Ave & Walton St" "Western Ave & Walton St" "Racine Ave & Randolph St" ...
## $ start_station_id : chr [1:767650] "20204" "KA1504000103" "KA1504000103" "13155" ...
## $ end_station_name : chr [1:767650] "Public Rack - Racine Ave & 109th Pl" "Milwaukee Ave & Grand Ave" "Damen Ave & Pierce Ave" "Clinton St & Madison St" ...
## $ end_station_id : chr [1:767650] "877" "13033" "TA1305000041" "TA1305000032" ...
## $ start_lat : num [1:767650] 41.7 41.9 41.9 41.9 42 ...
## $ start_lng : num [1:767650] -87.7 -87.7 -87.7 -87.7 -87.7 ...
## $ end_lat : num [1:767650] 41.7 41.9 41.9 41.9 42 ...
## $ end_lng : num [1:767650] -87.7 -87.6 -87.7 -87.6 -87.6 ...
## $ member_casual : chr [1:767650] "member" "member" "member" "member" ...
## - attr(*, "spec")=
## .. cols(
## .. ride_id = col_character(),
## .. rideable_type = col_character(),
## .. started_at = col_datetime(format = ""),
## .. ended_at = col_datetime(format = ""),
## .. start_station_name = col_character(),
## .. start_station_id = col_character(),
## .. end_station_name = col_character(),
## .. end_station_id = col_character(),
## .. start_lat = col_double(),
## .. start_lng = col_double(),
## .. end_lat = col_double(),
## .. end_lng = col_double(),
## .. member_casual = col_character()
## .. )
## - attr(*, "problems")=<externalptr>These functions helped me understand the data better and confirmed that we have the necessary data to answer our stakeholders’ questions regarding the differences between Cyclistic casual customers and annual members. Sorting and filtering datasets using the “member_casual” column will provide insights and trends on the differences between casual and annual members. We can compare trip lengths, locations, and usage patterns of the Cyclistic service throughout the year to create strategic marketing campaigns aimed at gaining more annual members. Deliverables Data source description:The data for this analysis consists of the last 12 months of Cyclistic trip data, provided by Motivate International Inc. It consists of 12 csv files, from here. The data is reliable, current, and comprehensive making it suitable for our analysis.
Process
Key Objective:Transform the raw data into a clean and usable format for analysis. Validating the data by identifying and correcting errors, handling missing data, and ensuring that the data is consistent and properly formatted.
To start off, we took all of the 12 different datasets and combined them into one data frame.
#Bind the different datasets together
all_trips <- bind_rows(data1, data2, data3, data4, data5, data6, data7, data8, data9, data10, data11, data12)Then, I cleaned, aggregated, filtered, and mutated the data in preparation for my analysis.
# Created “ride_length” column, and calculated in minutes
all_trips <- all_trips %>% mutate(ride_length =as.numeric(difftime(ended_at, started_at, units = "mins")))
# Removed rows where rides were negative
all_trips <- all_trips[!(all_trips$ride_length < 1),]
# Remove rows where rides were above 1 day
all_trips <- all_trips[!(all_trips$ride_length > 1440),]# Created "day of the week" column using function "wday" from lubridate package
all_trips <- all_trips %>%
mutate(day_of_week = wday(started_at, label = TRUE))
# Created a "month" column
all_trips$month <- format(as.Date(all_trips$started_at), "%b")
# Created a "Year" column
all_trips$year <- format(as.Date(all_trips$started_at), "%Y")
# Renamed Columns
names(all_trips) [2] <- 'bike'
names(all_trips) [13] <- 'user'
# Ordering day_of_week column
all_trips$day_of_week <- ordered(all_trips$day_of_week, levels = c("Monday", "Tuesday", "Wednesday",
"Thursday", "Friday", "Saturday",
"Sunday"))
# Ordering month column
all_trips$month <- ordered(all_trips$month, levels = c("Jan", "Feb", "Mar", "Apr", "May", "Jun",
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))
# Removed NAs
all_trips_cleaned <- all_trips %>%
drop_na(start_station_name, start_station_id, end_station_name, end_station_id)Deliverables: Documentation of any cleaning or manipulation of data: Combined the data from 12 datasets into a single dataframe, calculated the ride length, created new time-related columns (day of the week, month, and year), filtered out invalid data, dropped rows with any missing values, renamed columns for clarity, and ordered the categorical time data for better analysis.
Analyze
Key Objective: Uncover meaningful insights and patterns within the data that can inform decision- making and drive actionable recommendations. This involves using statistical techniques and data visualization methods to understand trends, relationships, and anomalies in the dataset.
# Differences Between Members and Casual Riders
ride_length_summary <- all_trips_cleaned %>%
group_by(user) %>%
summarise(mean_ride_length = mean(ride_length, na.rm = TRUE), median_ride_length = median(ride_length, na.rm = TRUE))
print(ride_length_summary)## # A tibble: 2 × 3
## user mean_ride_length median_ride_length
## <chr> <dbl> <dbl>
## 1 casual 23.8 13.3
## 2 member 12.6 8.95Casual riders have an average ride length that is almost double that of members. The mediam ride length for a casual rider is approximately 13 minutes, while for a member, it is around 9 minutes.
| user | weekday | number_of_rides | average_duration |
|---|---|---|---|
| casual | Sun | 248659 | 27.60636 |
| casual | Mon | 175079 | 23.52134 |
| casual | Tue | 168580 | 21.14473 |
| casual | Wed | 173416 | 20.53573 |
| casual | Thu | 186681 | 20.27274 |
| casual | Fri | 223017 | 23.11098 |
| casual | Sat | 314526 | 26.98178 |
| member | Sun | 297532 | 14.14713 |
| member | Mon | 389953 | 12.11098 |
| member | Tue | 428323 | 12.13869 |
| member | Wed | 442027 | 12.10430 |
| member | Thu | 445900 | 11.96121 |
| member | Fri | 395387 | 12.43387 |
| member | Sat | 348863 | 14.24662 |
This analysis provides an overview of ride frequency and duration by day, answering: “Which days are most popular for rides?”
# Bike Type Used by Casual and Annual Members
bike_type_usage <- all_trips_cleaned %>%
group_by(user, bike) %>%
summarise(count = n(), .groups = 'drop') %>%
arrange(user, bike)
# Display the table
knitr::kable(bike_type_usage, caption = "Bike Type Usage")| user | bike | count |
|---|---|---|
| casual | classic_bike | 920574 |
| casual | docked_bike | 47519 |
| casual | electric_bike | 521865 |
| member | classic_bike | 1859041 |
| member | electric_bike | 888944 |
This analysis shows which type of bike casual and annual members used for their rides. Casuals have access to docked bikes, while members don’t.
# Ride Count by Hour
ride_count_by_hours <- all_trips_cleaned %>%
mutate(hour = hour(started_at)) %>%
group_by(user, hour) %>%
summarise(number_of_rides = n(), .groups = 'drop') %>%
arrange(user, hour)
# Display the table
knitr::kable(ride_count_by_hours, caption = "Ride Count By Hour")| user | hour | number_of_rides |
|---|---|---|
| casual | 0 | 23534 |
| casual | 1 | 15032 |
| casual | 2 | 8728 |
| casual | 3 | 4513 |
| casual | 4 | 3394 |
| casual | 5 | 7970 |
| casual | 6 | 20686 |
| casual | 7 | 37428 |
| casual | 8 | 51694 |
| casual | 9 | 52065 |
| casual | 10 | 66715 |
| casual | 11 | 84290 |
| casual | 12 | 100099 |
| casual | 13 | 103260 |
| casual | 14 | 106817 |
| casual | 15 | 117490 |
| casual | 16 | 135396 |
| casual | 17 | 145346 |
| casual | 18 | 121607 |
| casual | 19 | 88665 |
| casual | 20 | 63686 |
| casual | 21 | 52098 |
| casual | 22 | 46365 |
| casual | 23 | 33080 |
| member | 0 | 22375 |
| member | 1 | 12642 |
| member | 2 | 6817 |
| member | 3 | 4468 |
| member | 4 | 5831 |
| member | 5 | 26440 |
| member | 6 | 83126 |
| member | 7 | 156395 |
| member | 8 | 192225 |
| member | 9 | 126247 |
| member | 10 | 112062 |
| member | 11 | 133063 |
| member | 12 | 151579 |
| member | 13 | 150076 |
| member | 14 | 151344 |
| member | 15 | 186558 |
| member | 16 | 255481 |
| member | 17 | 298077 |
| member | 18 | 228003 |
| member | 19 | 157833 |
| member | 20 | 109553 |
| member | 21 | 82368 |
| member | 22 | 59071 |
| member | 23 | 36351 |
This analysis shows how many rides occur per hour between casuals and members. 3-6pm are the hours in which rides peaked for both type of riders.
ride_count_by_months <- all_trips_cleaned %>%
mutate(month = month(started_at, label = TRUE)) %>%
group_by(user, month) %>%
summarise(number_of_rides = n(), .groups = 'drop') %>%
arrange(user, month)
# Display the table
knitr::kable(ride_count_by_months, caption = "Ride Count By Month")| user | month | number_of_rides |
|---|---|---|
| casual | Jan | 17370 |
| casual | Feb | 37613 |
| casual | Mar | 61799 |
| casual | Apr | 92234 |
| casual | May | 164351 |
| casual | Jun | 215923 |
| casual | Jul | 240949 |
| casual | Aug | 230213 |
| casual | Sep | 193977 |
| casual | Oct | 128313 |
| casual | Nov | 71061 |
| casual | Dec | 36155 |
| member | Jan | 93540 |
| member | Feb | 144465 |
| member | Mar | 164708 |
| member | Apr | 200336 |
| member | May | 270047 |
| member | Jun | 308794 |
| member | Jul | 321967 |
| member | Aug | 344300 |
| member | Sep | 304187 |
| member | Oct | 268639 |
| member | Nov | 199129 |
| member | Dec | 127873 |
This analysis shows how many rides occurred for each type of member, June - Sep are the peak months for both type of members. Deliverables findings from the above analysis: Casual riders have longer ride lengths on average than members. Casual riders tend to ride more on weekends, whereas members have a higher number of rides mid-week. These insights can help shape targeted marketing strategies to convert casual riders into annual members.
Share
Key objective: Creating visualizations and summaries to communicate insights effectively with the stakeholders.
# Plot Mean and Median Ride Length by User Type
ggplot(ride_length_summary) +
geom_col(aes(x = user, y = mean_ride_length, fill = "Mean"), position = "dodge") +
geom_col(aes(x = user, y = median_ride_length, fill = "Median"), position = "dodge") +
labs(
title = "Mean and Median Ride Length by User Type",
x = "User Type",
y = "Ride Length (seconds)"
) +
scale_fill_manual(name = "Statistic", values = c("Mean" = "blue", "Median" = "red")) +
theme_minimal()
# Number of Rides by Rider Type and Weekday:
ggplot(rides_by_day_of_week, aes(x = weekday, y = number_of_rides, fill = user)) +
geom_col(position = "dodge") +
labs(
title = "Number of Rides by Rider Type and Weekday",
x = "Day of the Week",
y = "Number of Rides",
fill = "User Type"
) +
theme_minimal() + scale_y_continuous(labels = scales::comma) 
# Average Ride Duration by Rider Type and Weekday
avg_ride_duration_ggplot <- all_trips_cleaned %>%
mutate(weekday = wday(started_at, label = TRUE)) %>%
group_by(user, weekday) %>%
summarise(number_of_rides = n(), average_duration = mean(ride_length)) %>%
arrange(user, weekday) %>%
ggplot(aes(x = weekday, y = average_duration, fill = user)) +
geom_col(position = "dodge") +
labs(title = "Average Ride Duration by Rider Type and Weekday", x = "Weekday", y = "Average Duration (minutes)")+
theme_minimal()## `summarise()` has grouped output by 'user'. You can override using the
## `.groups` argument.print(avg_ride_duration_ggplot)
# Ride Count by Hour
ggplot(ride_count_by_hours, aes(x = hour, y = number_of_rides, color = user)) +
geom_line() +
labs(
title = "Ride Count by Hour",
x = "Hour of the Day",
y = "Number of Rides"
) +
theme_minimal() +
scale_color_manual(values = c("casual" = "red", "member" = "blue")) + scale_y_continuous(labels = scales::comma) 
# Ride Count by Month
ggplot(ride_count_by_months, aes(x = month, y = number_of_rides, color = user, group = user)) +
geom_line() +
geom_point() +
labs(
title = "Ride Count by Month",
x = "Month",
y = "Number of Rides"
) +
theme_minimal() +
scale_color_manual(values = c("casual" = "red", "member" = "blue")) + scale_y_continuous(labels = scales::comma) 
ggplot(bike_type_usage, aes(x = bike, y = count, fill = user)) +
geom_col(position = "dodge") +
labs(
title = "Bike Type Usage by User Type",
x = "Bike Type",
y = "Number of Rides",
fill = "User Type"
) +
theme_minimal()
Deliverables Supporting visualizations &
key findingsDetailed plots and summaries showing the
differences in ride lengths, patterns by weekday and month, and usage
distribution between casual and annual members.
Act
Key objective: To implement decisions and actions based on insights gained from the data analysis.
Recommendations for Cyclistic Based on the detailed analysis of how annual members and casual riders use Cyclistic bikes differently, I have identified several key insights and actionable recommendations to help convert casual riders into annual members.
- Targeted Marketing Campaigns
Insight:
-Casual riders have an average ride length almost double that of members. -Casual riders peak on weekends, while members peak mid-week (Wednesday and Thursday).
Recommendation:
-Weekend Promotions: Implement special weekend promotions targeted at casual riders, offering discounts on annual memberships if they sign up during the weekend. -Mid-week Benefits: Highlight the benefits of being an annual member with exclusive mid-week perks, such as discounted rides on Wednesday and Thursday.
- Incentivize Longer Rides
Insight:
-Casual riders prefer longer rides compared to members.
Recommendation:
-Extended Ride Packages: Offer extended ride packages to casual riders that provide a taste of the benefits annual members enjoy, such as additional ride time or reduced rates for longer rides. -Ride Challenges: Create monthly ride challenges that encourage casual riders to increase their ride frequency and duration, with the chance to win an annual membership.
- Customize Bike Type Options
Insight:
-Casual riders have the option to use electric, classic, and docked bikes, with classic bikes being the most popular. Members, however, only have access to electric or classic bikes.
Recommendation:
-Expand Bike Options for Members: Allow annual members to also use docked bikes to provide more flexibility and appeal. -Highlight Bike Benefits: Market the benefits of using different bike types (e.g., electric bikes for faster commutes) to casual riders and how these are included in the annual membership.
- Peak Time Promotions
Insight:
-Peak usage times for both casual riders and members are from 3-6 PM.
Recommendation:
-Happy Hour Rides: Introduce “happy hour” rides from 3-6 PM where casual riders can experience the advantages of membership at a discounted rate. -After-Work Specials: Promote after-work specials that encourage casual riders to become annual members by offering exclusive deals during these peak hours.
- Seasonal Campaigns
Insight:
Peak months for both members and casual riders are June to August.
Recommendation:
-Summer Membership Drive: Launch a summer membership drive with incentives such as reduced rates for annual memberships, limited-time offers, and bonus rides for new members who sign up during these peak months. -Seasonal Events: Organize seasonal events and rides for members and casual riders, emphasizing community and the additional benefits of becoming an annual member.
By implementing these targeted marketing strategies, Cyclistic can effectively convert more casual riders into annual members, ultimately increasing profitability and customer loyalty.
Thank you for taking the time to read my case study!