The first steps will to be set up my environment and packages. I will be using the TidyVerse package for this project and loading the data Divvy_trips_2019_Q1 through Q4. Each of the quarters come in their own file and will be assigned to the variables Q1 through Q4 respectively.
library(tidyverse)
Q1 <- read.csv("H:/corsera courses/Analysis projects/Cyclistic_case_study/Case_study_1/Divvy_Trips_2019_Q1.csv")
Q2 <- read.csv("H:/corsera courses/Analysis projects/Cyclistic_case_study/Case_study_1/Divvy_Trips_2019_Q2.csv")
Q3 <- read.csv("H:/corsera courses/Analysis projects/Cyclistic_case_study/Case_study_1/Divvy_Trips_2019_Q3.csv")
Q4 <- read.csv("H:/corsera courses/Analysis projects/Cyclistic_case_study/Case_study_1/Divvy_Trips_2019_Q4.csv")First I changed the column names so they were all the same and created a new data frame to work with so the original wouldn’t be altered by the analysis.
## repeat code for all four orginal data sets
colnames(Q1) <- c("trip_id","start_time",
"end_time","bikeid","trip_duration_seconds",
"from_station_id","from_station_name",
"to_station_id","to_station_name",
"usertype","gender","birthyear")
q1_clean<-Q1
colnames(Q2) <- c("trip_id","start_time","end_time","bikeid", "trip_duration_seconds","from_station_id",
"from_station_name", "to_station_id", "to_station_name",
"usertype", "gender","birthyear")
q2_clean <- Q2
colnames(Q3) <- c("trip_id","start_time","end_time","bikeid", "trip_duration_seconds","from_station_id", "from_station_name", "to_station_id", "to_station_name", "usertype", "gender","birthyear")
q3_clean<-Q3
colnames(Q4) <- c("trip_id","start_time","end_time","bikeid", "trip_duration_seconds","from_station_id",
"from_station_name", "to_station_id", "to_station_name",
"usertype", "gender","birthyear")
q4_clean<-Q4 Second, I converted the start_time and end_time into date and time objects, from character strings.
## repeat code for all four orginal data sets
q1_clean$start_time<-parse_date_time(q1_clean$start_time,"ymd_HMS")
q1_clean$end_time <- parse_date_time(q1_clean$end_time, "ymd_HMS")
q2_clean$start_time <- parse_date_time(q2_clean$start_time, "ymd_HMS")
q2_clean$end_time <- parse_date_time(q2_clean$end_time, "ymd_HMS")
q3_clean$start_time<-parse_date_time(q3_clean$start_time,"ymd_HMS")
q3_clean$end_time<-parse_date_time(q3_clean$end_time,"ymd_HMS")
q4_clean$start_time<-parse_date_time(q4_clean$start_time,"ymd_HMS")
q4_clean$end_time<-parse_date_time(q4_clean$end_time,"ymd_HMS")Third, the birth year column was converted into a date and time format and, the year was extracted, put in a new column and the original column was dropped. This gave me an identical column with the birth year as a date and time object rather than a character object.
q1_clean$birthyear <- parse_date_time(q1_clean$birthyear,
"y")
q1_clean<-q1_clean %>%
mutate(birth_year=year(birthyear),.before = birthyear) %>%
mutate(birthyear=NULL)
q2_clean$birthyear <- parse_date_time(q2_clean$birthyear,
"y")
q2_clean<-q2_clean %>%
mutate(birth_year=year(birthyear),.before = birthyear) %>%
mutate(birthyear=NULL)
q3_clean$birthyear <- parse_date_time(q3_clean$birthyear,
"y")
q3_clean <- q3_clean%>%
mutate(birth_year=year(birthyear),birthyear=NULL)
q4_clean$birthyear <- parse_date_time(q4_clean$birthyear,
"y")
q4_clean <- q4_clean%>%
mutate(birth_year=year(birthyear),birthyear=NULL)Fourth, the trip_duration_seconds column had to be converted to a number from a character so calculations could be performed.
## repeat code for all four orginal data sets
q1_clean$trip_duration_seconds <- parse_number(q1_clean$trip_duration_seconds)
q2_clean$trip_duration_seconds <- parse_number(q2_clean$trip_duration_seconds)
q3_clean$trip_duration_seconds<-parse_number(q3_clean$trip_duration_seconds)
q4_clean$trip_duration_seconds<-parse_number(q4_clean$trip_duration_seconds)Finally I converted the user type column to factor objects in all four data sets at once to make them easier to work with.
## repeat code for all four orginal data sets
q1_clean$usertype<- as.factor(q1_clean$usertype)
q2_clean$usertype<- as.factor(q2_clean$usertype)
q3_clean$usertype<- as.factor(q3_clean$usertype)
q4_clean$usertype<- as.factor(q4_clean$usertype)Once all the data sets were cleaned and formatted they were combined with only the columns relevant to the analysis. The to station name and from station name columns were dropped, along with bike ID. Also a new column, “qtr”, was added to specify which quarter the data came from in the original sets and that column was converted into factor data.
ttl_rds_1<-q1_clean %>%
select(trip_id,usertype,trip_duration_seconds, from_station_id,to_station_id,
gender,birth_year,start_time,end_time)%>%
mutate(qtr= "q1")
ttl_rds_2<-q2_clean %>%
select(trip_id,usertype,trip_duration_seconds,from_station_id,to_station_id,
gender,birth_year,start_time,end_time) %>%
mutate(qtr= "q2")
ttl_rds_3<-q3_clean %>%
select(trip_id,usertype,trip_duration_seconds, from_station_id,to_station_id,
gender,birth_year,start_time,end_time) %>%
mutate(qtr= "q3")
ttl_rds_4<-q4_clean %>%
select(trip_id,usertype,trip_duration_seconds,from_station_id,to_station_id,
gender,birth_year,start_time,end_time) %>%
mutate(qtr= "q4")
rides_by_cust<-rbind(ttl_rds_1,ttl_rds_2,ttl_rds_3,ttl_rds_4)
rides_by_cust$qtr <-as.factor(rides_by_cust$qtr)In order to determine the best timing for the campaign, I looked at total ridership in all four quarters. This graph shows even though subscribers continue using the bikes during the colder months, customers, generally do not. In fact there are so few customers in Q1 and Q4 that running a conversion campaign may not have enough benefit to justify the cost.
rides_by_cust %>%
select(qtr,usertype) %>%
group_by(usertype) %>%
ggplot(aes(x=usertype,))+
geom_bar(fill="#5a99bd")+
theme_bw()+
labs(x="User Type",
y="Number of rides",
title = "Total Rides per User Type")+
scale_y_continuous(breaks = c(10000,100000,200000,300000,400000,500000,
600000,700000,800000,900000,1000000,1100000),
labels = c("10k","100k","200k","300k","400k","500k",
"600k","700k","800k","900k","1m","1.1m"))+
facet_wrap(~qtr)
Since the proportion of customers to subscribers is up dramatically in Q2 & Q3, it is recommended that a further cost benefit analysis be conducted to determine if running the campaign in Q1 and Q4 is viable.
Although the subscribers are making more trips they are shorter. The Customers are responsible for more total time with the bikes, and their average time per trip is about three times that of the subscribers.
## Determine total ride time in hours per user type and per Q2,&Q3
rides_by_cust %>%
select(usertype,trip_duration_seconds,qtr) %>%
filter(qtr == c("q2","q3")) %>%
group_by(qtr,usertype) %>%
summarise(trip_duration_hours=sum(trip_duration_seconds)/60^2, .groups = "keep") %>%
ggplot(mapping = aes(x=usertype,y=trip_duration_hours))+
geom_bar(stat="identity",fill="#5a99bd")+theme_bw()+
labs(x="User Type",y="Ride Duration Hours",title = "Total Ride Time per User Type")+scale_y_continuous(breaks = c(10000,100000,200000,300000),labels = c("10k","100k","200k","300k"))+
facet_wrap(~qtr)+
theme(plot.margin = margin(0, 0, 0, 0, "cm"))
rides_by_cust %>%select(trip_duration_seconds,usertype,qtr,)%>%
filter(qtr==c("q2","q3"))%>%
mutate(trip_duratiion_min=trip_duration_seconds/60) %>%
group_by(qtr,usertype) %>%
summarise(avg_trip_min=mean(trip_duratiion_min),.groups="keep") %>%
ggplot(aes(x=usertype,avg_trip_min))+
geom_bar(stat="identity",fill="#5a99bd")+
theme_bw()+
labs(x="User Type",
y="Average Min Per Ride",
title = "Average Trip Duration Per User Type")+
facet_wrap(~qtr)+
theme(plot.margin = margin(0, 0, 0, 0, "cm"))
Next it should be noted that the customers make more round trips than the subscribers do.
rides_by_cust %>%
select(usertype,from_station_id,to_station_id,qtr,) %>%
filter(from_station_id==to_station_id, qtr==c("q2","q3")) %>%
group_by(qtr,usertype) %>%
count(usertype) %>%
ggplot(aes(x=usertype,y=n))+
geom_bar(stat="identity",fill="#5a99bd")+
theme_bw()+
facet_wrap(~qtr,nrow = 2)+
labs(kx="User Type",
y="Number of Round Trips",
title = "Total Round Trips per User Type",
caption = "Total Round Trips Q2 & Q3\n Customers: 45,919\n Subscribers: 17,904")
In this graph you can see the top 5 routes taken by both customers and subscribers in both Q2 & Q3
##top five trips for Customers in q2
top_5_cust_q2<-rides_by_cust %>%
select(from_station_id,to_station_id,qtr,usertype,) %>%
filter(qtr== c("q2","q3")) %>%
unite(col=trip,
from_station_id,
to_station_id,
sep = "-") %>%
mutate(to_station_id=NULL,from_station_id=NULL) %>%
group_by(qtr,usertype) %>%
count(trip) %>%
filter( qtr=="q2",usertype=="Customer",n>=540) %>%
view
## top 5 trips for Subscribers in q2
top_5_Subs_q2<-rides_by_cust %>%
select(from_station_id,to_station_id,qtr,usertype,) %>%
filter(qtr== c("q2","q3")) %>%
unite(col=trip,
from_station_id,
to_station_id,
sep = "- ") %>%
mutate(to_station_id=NULL,from_station_id=NULL) %>%
group_by(qtr,usertype) %>%
count(trip) %>%
filter( qtr=="q2",usertype=="Subscriber",n>=327) %>%
view()
##create the new data frame with top 5 rides for subs and
##customers
top_trips_q2<-rbind(top_5_cust_q2,top_5_Subs_q2)
## Plot top 5 trips in q2 per user type
top_trips_q2 %>%
ggplot(aes(x=trip,y=n))+
geom_bar(stat="identity",fill="#5a99bd",show.legend = TRUE,)+
theme_bw() +
facet_wrap(~usertype,nrow = 2,scales = "free")+
labs(x= "Station ID Number",
y= "Trip Count",
title = "Top 5 Trips in Q2")+
labs(caption ="Station ID order is from - to")
## Determine the most popular trips in Q3
##top five trips for Customers in q3
top_5_Cust_q3<-rides_by_cust %>%
select(to_station_id,from_station_id,usertype,qtr) %>%
unite(col = trip,
from_station_id,
to_station_id,
sep = "-") %>%
mutate(to_station_id=NULL,from_station_id=NULL) %>%
filter(qtr=="q3") %>%
group_by(qtr,usertype) %>%
count(trip) %>%
filter(usertype=="Customer",n>=1840) %>%
view()
## top 5 trips for Subscribers in q3
top_5_Subs_q3<-rides_by_cust %>%
select(to_station_id,from_station_id,usertype,qtr) %>%
unite(col = trip,
from_station_id,
to_station_id,
sep = "-") %>%
mutate(to_station_id=NULL,from_station_id=NULL) %>%
filter(qtr=="q3") %>%
group_by(qtr,usertype) %>%
count(trip) %>%
filter(usertype=="Subscriber",n>=798) %>%
view()
##create the new data frame with top 5 rides for subs and
##customers
top_trips_q3<-rbind(top_5_Cust_q3,top_5_Subs_q3)
## Plot top 5 trips in q3 per user type
top_trips_q3 %>%
ggplot(aes(x=trip,y=n))+
geom_bar(stat="identity",fill="#5a99bd",show.legend = TRUE,)+
theme_bw() +
facet_wrap(~usertype,nrow = 2,scales = "free")+
labs(x= "Station ID Number",
y= "Trip Count",
title = "Top 5 Trips in Q3")+
labs(caption ="Station ID order is from - to")
In addition to making more round trips, the customers also make a larger portion of their trips on the week ends. Where as the subscribers make their largest portion of trips during the week.
## Total rides per weekday and per user type Q2 & Q3
##Q2
rides_by_cust %>%
select(start_time,usertype,qtr) %>%
mutate(week_day=wday(start_time,label=TRUE)) %>%
filter(qtr== "q2") %>%
group_by(usertype) %>%
count(week_day) %>%
ggplot(aes(x=week_day,y=n,))+
geom_bar(stat="identity",fill="#5a99bd")+
theme_bw()+
labs(x="Day of the Week",
y="Number of Rides",
title = "Rides by Weekday\nQ2")+
facet_wrap(~usertype)
##Q3
rides_by_cust %>%
select(start_time,usertype,qtr) %>%
mutate(week_day=wday(start_time,label=TRUE)) %>%
filter(qtr== "q3") %>%
group_by(usertype) %>%
count(week_day) %>%
ggplot(aes(x=week_day,y=n,))+
geom_bar(stat="identity",fill="#5a99bd")+
theme_bw()+
labs(x="Day of the Week",
y="Number of Rides",
title = "Rides by Weekday\nQ3")+
facet_wrap(~usertype)
The two types of users have quite different behaviors with the bikes. The Subscribers make a lot more trips, they are one way, both their average and total trip time is lower, and they tend to prefer doing their riding during the week. The customers make a lot few trips, they are round trip, they have more hours and their average ride time is higher, and they tend to prefer the weekends.
The customers spend more time on individual rides, they start their rides most on the weekends, and the repeated round trips suggests they are using the bikes more for leisure than work or business. Therefore it is recommended that the features in the subscription plan be adjusted to include packages aimed at befitting round trips and longer duration rides. The ad campaign should target Friday, Saturday, and Sunday, and focus on the stations locations where the most round trips are made.