Case Study-Design Cyclistic’s Marketing Strategy
The cleaned Dataset to the Case Study is available below: https://www.kaggle.com/datasets/julianachaudhari/case-study-cyclistic
Case Study-Design Cyclistic’s Marketing Strategy
The cleaned Dataset to the Case Study is available below: https://www.kaggle.com/datasets/julianachaudhari/case-study-cyclistic
How does a bike-share speedy success
Background Cyclistic is focusing on success depending on maximizing annual bike subscriptions. The company decides to design a marketing strategy on conversion of casual riders to annual riders. The data analytics team is given the task to analyse how do annual members and casual riders use Cyclistic bikes differently. This will lead to Cyclistic bikes Executive team and Marketing Director making data driven decision for increased subscriptions of annual riders and increased profitability. Study Target Audience — Two wheeler bikers both leisure and and people riding to work. People with disabilities and riding 3-wheeler bikes. In this case study, we will be using the Google analytics Data analysis phases of Ask, Prepare, Process, Analyze, Share and Act
Ask phase
PROBLEM DEFINITION The company has asked the following below:
- How do annual members and casual riders use Cyclistic bikes differently?
- Why would casual riders buy Cyclistic annual memberships?
- How can Cyclistic use digital media to influence casual riders to become members?
Based on the above given information we have to analyse and report the following: 1. Analyse causes for casual riders to become Cyclistic annual members 2. Find out which location or trip stations can be targeted to increase the sales. 3. What can be inputs to design advertising strategy to increase annual riders.
Key Stakeholders -Marketing Director, Data Analytics team and Executive team. Bike riders are the focus audience and end user customer of Cyclistic.
DATA AND METRICS
Cyclistic have shared the historical trip data of 2019 and 2020 of bikers. The metrics will help analyze and identify trends of the trip stations, bike members etc. This analysis will help the executive team to make data driven marketing strategy decisions to increase annual subscriptions and future success.
Prepare phase The data has been made available by ( Motivate International Inc. under this license.) This is public data that will be used to explore how different customer types are using Cyclistic bikes. Data security and privacy issues have been addressed so we can’t use riders’ personally identifiable information. This means that you won’t be able to connect pass purchases to credit card numbers to determine if casual riders live in the Cyclistic service area or if they have purchased multiple single passes.
The data is reliable and original. Data used for analysis is of the year 2019 and 2020 which is not current and analysis results may vary. However, the results may vary, but can be best prepared with data available.
We have collected data, downloaded and organized in .CSV files and organized below
CSV file structure of the data
Process phase The following tools is used for the analysis: R programming through RStudio on cloud (Posit) is used as the data is huge and has integrated development design to collaborate data. We will be using Tableau for visualizations. STEP 1. After we import the data, we use the library below to analyse, clean and aggregate data.
# Load Packages
library(tidyverse)
library(lubridate)
library(tidyr)
library(skimr)
library(janitor)
library(data.table)
library(ggplot2)
library(tidygeocoder)
> install.packages (“tidyverse”)
── Attaching core tidyverse packages ──────────────────────────── tidyverse 2.0.0 ──
✔ forcats 1.0.1 ✔ readr 2.1.6
✔ ggplot2 4.0.1 ✔ stringr 1.6.0
✔ lubridate 1.9.4 ✔ tibble 3.3.0
✔ purrr 1.2.0 ✔ tidyr 1.3.1
── Conflicts ────────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag() masks stats::lag()
ℹ Use the conflicted package to force all conflicts to become errors
> detach("package:tidyverse", unload = TRUE)
> library(tidyverse)
── Conflicts ────────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag() masks stats::lag()
ℹ Use the conflicted package to force all conflicts to become errors
Restarting R session...> install.packages("lubridate")
Installing package into ‘/cloud/lib/x86_64-pc-linux-gnu-library/4.5’
(as ‘lib’ is unspecified)
trying URL 'http://rspm/default/__linux__/focal/latest/src/contrib/lubridate_1.9.4.tar.gz'
Content type 'application/x-gzip' length 990322 bytes (967 KB)
==================================================
downloaded 967 KB
* installing *binary* package ‘lubridate’ ...
* DONE (lubridate)
The downloaded source packages are in
‘/tmp/Rtmpz7iaoA/downloaded_packages’
> install.packages("janitor")
Installing package into ‘/cloud/lib/x86_64-pc-linux-gnu-library/4.5’
(as ‘lib’ is unspecified)trying URL 'http://rspm/default/__linux__/focal/latest/src/contrib/janitor_2.2.1.tar.gz'
Content type 'application/x-gzip' length 285327 bytes (278 KB)
==================================================
downloaded 278 KB
* installing *binary* package ‘janitor’ ...
* DONE (janitor)
The downloaded source packages are in
‘/tmp/Rtmpz7iaoA/downloaded_packages’
> library(janitor)
Attaching package: ‘janitor’
The following objects are masked from ‘package:stats’:
chisq.test, fisher.
Now packages are loaded, we will aggregate all the data in our Divvy Trips folder.
We first import the data in Posit cloud.
Global Environment files in R
STEP 2-In this phase, we wrangle the data and combine it into one file. Using the following function gives the column names of the table Divvy_Trips_2019_Q1 which shows the table specification.
1.
#Data check
Colnames(Divvy_Trips_2019_Q1)
Colnames(Divvy_Trips_2020_Q1)
[1] "trip_id" [2]"start_time" [3]"end_time" [4] "bikeid"
[5] "tripduration" [6] "from_station_id" [7]"from_station_name" [8]"to_station_id"
[9] "to_station_name" [10]"usertype" [11] "gender" [12] "birthyear"
[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"
#While the names are not in the same order, they DO need to match perfectly before we can use a command to join them into one file2. As the columns names in both datasets match in our next step we will
rename the column names in 2019 Q1 dataset even though the order of the
column names do not match.
# Rename columns to make them consistent with q1_2020 (as this will be the supposed going-forward table design for Divvy)
(Divvy_Trips_2019_Q1 <- rename(Divvy_Trips_2019_Q1
,ride_id = trip_id
,rideable_type = bikeid
,started_at = start_time
,ended_at = end_time
,start_station_name = from_station_name
,start_station_id = from_station_id
,end_station_name = to_station_name
,end_station_id = to_station_id
,member_casual = usertype
))
# A tibble: 365,069 × 12
ride_id started_at ended_at rideable_type tripduration start_station_id
<dbl> <chr> <chr> <dbl> <dbl> <dbl>
1 21742443 2019-01-01 0:04:37 2019-01-… 2167 390 199
2 21742444 2019-01-01 0:08:13 2019-01-… 4386 441 44
3 21742445 2019-01-01 0:13:23 2019-01-… 1524 829 15
4 21742446 2019-01-01 0:13:45 2019-01-… 252 1783 123
5 21742447 2019-01-01 0:14:52 2019-01-… 1170 364 173
6 21742448 2019-01-01 0:15:33 2019-01-… 2437 216 98
7 21742449 2019-01-01 0:16:06 2019-01-… 2708 177 98
8 21742450 2019-01-01 0:18:41 2019-01-… 2796 100 211
9 21742451 2019-01-01 0:18:43 2019-01-… 6205 1727 150
10 21742452 2019-01-01 0:19:18 2019-01-… 3939 336 268
# ℹ 365,059 more rows
# ℹ 6 more variables: start_station_name <chr>, end_station_id <dbl>,
# end_station_name <chr>, member_casual <chr>, gender <chr>, birthyear <dbl>
# ℹ Use `print(n = ...)` to see more rows
> print(x=365069)
[1] 365069
3.
# Inspect the dataframes and look for incongruencies
str(Divvy_Trips_2019_Q1)
str(Divvy_Trips_2020_Q1)> str(Divvy_Trips_2019_Q1)
spc_tbl_ [365,069 × 12] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
$ ride_id : num [1:365069] 21742443 21742444 21742445 21742446 21742447 ...
$ started_at : chr [1:365069] "2019-01-01 0:04:37" "2019-01-01 0:08:13" "2019-01-01 0:13:23" "2019-01-01 0:13:45" ...
$ ended_at : chr [1:365069] "2019-01-01 0:11:07" "2019-01-01 0:15:34" "2019-01-01 0:27:12" "2019-01-01 0:43:28" ...
$ rideable_type : num [1:365069] 2167 4386 1524 252 1170 ...
$ tripduration : num [1:365069] 390 441 829 1783 364 ...
$ start_station_id : num [1:365069] 199 44 15 123 173 98 98 211 150 268 ...
$ start_station_name: chr [1:365069] "Wabash Ave & Grand Ave" "State St & Randolph St" "Racine Ave & 18th St" "California Ave & Milwaukee Ave" ...
$ end_station_id : num [1:365069] 84 624 644 176 35 49 49 142 148 141 ...
$ end_station_name : chr [1:365069] "Milwaukee Ave & Grand Ave" "Dearborn St & Van Buren St (*)" "Western Ave & Fillmore St (*)" "Clark St & Elm St" ...
$ member_casual : chr [1:365069] "Subscriber" "Subscriber" "Subscriber" "Subscriber" ...
$ gender : chr [1:365069] "Male" "Female" "Female" "Male" ...
$ birthyear : num [1:365069] 1989 1990 1994 1993 1994 ...
- attr(*, "spec")=
.. cols(
.. trip_id = col_double(),
.. start_time = col_character(),
.. end_time = col_character(),
.. bikeid = col_double(),
.. tripduration = col_number(),
.. from_station_id = col_double(),
.. from_station_name = col_character(),
.. to_station_id = col_double(),
.. to_station_name = col_character(),
.. usertype = col_character(),
.. gender = col_character(),
.. birthyear = col_double()
.. )
- attr(*, "problems")=<externalptr>
> str(Divvy_Trips_2020_Q1)
spc_tbl_ [426,887 × 13] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
$ ride_id : chr [1:426887] "EACB19130B0CDA4A" "8FED874C809DC021" "789F3C21E472CA96" "C9A388DAC6ABF313" ...
$ rideable_type : chr [1:426887] "docked_bike" "docked_bike" "docked_bike" "docked_bike" ...
$ started_at : chr [1:426887] "2020-01-21 20:06:59" "2020-01-30 14:22:39" "2020-01-09 19:29:26" "2020-01-06 16:17:07" ...
$ ended_at : chr [1:426887] "2020-01-21 20:14:30" "2020-01-30 14:26:22" "2020-01-09 19:32:17" "2020-01-06 16:25:56" ...
$ start_station_name: chr [1:426887] "Western Ave & Leland Ave" "Clark St & Montrose Ave" "Broadway & Belmont Ave" "Clark St & Randolph St" ...
$ start_station_id : num [1:426887] 239 234 296 51 66 212 96 96 212 38 ...
$ end_station_name : chr [1:426887] "Clark St & Leland Ave" "Southport Ave & Irving Park Rd" "Wilton Ave & Belmont Ave" "Fairbanks Ct & Grand Ave" ...
$ end_station_id : num [1:426887] 326 318 117 24 212 96 212 212 96 100 ...
$ start_lat : num [1:426887] 42 42 41.9 41.9 41.9 ...
$ start_lng : num [1:426887] -87.7 -87.7 -87.6 -87.6 -87.6 ...
$ end_lat : num [1:426887] 42 42 41.9 41.9 41.9 ...
$ end_lng : num [1:426887] -87.7 -87.7 -87.7 -87.6 -87.6 ...
$ member_casual : chr [1:426887] "member" "member" "member" "member" ...
- attr(*, "spec")=
.. cols(
.. ride_id = col_character(),
.. rideable_type = col_character(),
.. started_at = col_character(),
.. ended_at = col_character(),
.. start_station_name = col_character(),
.. start_station_id = col_double(),
.. end_station_name = col_character(),
.. end_station_id = col_double(),
.. start_lat = col_double(),
.. start_lng = col_double(),
.. end_lat = col_double(),
.. end_lng = col_double(),
.. member_casual = col_character()
.. )
- attr(*, "problems")=<externalptr>
4.
# Rename the dataset names as it is very long using below function
> q1_2019 <- Divvy_Trips_2019_Q1
> q1_2020 <- Divvy_Trips_2020_Q1
> View(q1_2019)
> View(q1_2020)
5.
# Convert ride_id and rideable_type to character so that they can stack correctly and merge with second dataset
q1_2019 <- mutate(q1_2019, ride_id = as.character(ride_id)
,rideable_type = as.character(rideable_type))
6. We combine both datasets into one dataframe for further analysis
# Stack individual quarter's data frames into one big data frame
all_trips <- bind_rows(q1_2019, q1_2020)#, q3_2019)#, q4_2019, q1_2020)
7. We can drop columns which are not required in the analysis thus making it consistent
# Remove lat, long, birthyear, and gender fields as this data was dropped beginning in 2020
all_trips <- all_trips %>%
select(-c(start_lat, start_lng, end_lat, end_lng, birthyear, gender, "tripduration"))
We now have 9 columns with 791956 rows
STEP 3- We now clean up and add data to prepare for analysis
# Inspect the new table that has been created
colnames(all_trips) #List of column names
[1] "ride_id" "started_at" "ended_at" "rideable_type"
[5] "start_station_id" "start_station_name" "end_station_id" "end_station_name"
[9] "member_casual"
nrow(all_trips) #How many rows are in data frame?
[1] 791956
dim(all_trips) #Dimensions of the data frame?
[1] 791956 9
#See the first 6 rows of data frame.
head(all_trips)
# A tibble: 6 × 9
ride_id started_at ended_at rideable_type start_station_id start_station_name end_station_id
<chr> <chr> <chr> <chr> <dbl> <chr> <dbl>
1 21742443 2019-01-01… 2019-01… 2167 199 Wabash Ave & Gran… 84
2 21742444 2019-01-01… 2019-01… 4386 44 State St & Randol… 624
3 21742445 2019-01-01… 2019-01… 1524 15 Racine Ave & 18th… 644
4 21742446 2019-01-01… 2019-01… 252 123 California Ave & … 176
5 21742447 2019-01-01… 2019-01… 1170 173 Mies van der Rohe… 35
6 21742448 2019-01-01… 2019-01… 2437 98 LaSalle St & Wash… 49
# ℹ 2 more variables: end_station_name <chr>, member_casual <chr>
Also tail(all_trips)
# A tibble: 6 × 9
ride_id started_at ended_at rideable_type start_station_id start_station_name end_station_id
<chr> <chr> <chr> <chr> <dbl> <chr> <dbl>
1 6F4D221B… 2020-03-1… 2020-03… docked_bike 675 HQ QR 675
2 ADDAA33C… 2020-03-1… 2020-03… docked_bike 675 HQ QR 675
3 82B10FA3… 2020-03-0… 2020-03… docked_bike 161 Rush St & Superio… 240
4 AA0D5AAA… 2020-03-0… 2020-03… docked_bike 141 Clark St & Lincol… 210
5 3296360A… 2020-03-0… 2020-03… docked_bike 672 Franklin St & Ill… 264
6 064EC769… 2020-03-0… 2020-03… docked_bike 110 Dearborn St & Eri… 85
# ℹ 2 more variables: end_station_name <chr>, member_casual <chr>
#See list of columns and data types (numeric, character, etc)
str(all_trips)
tibble [791,956 × 9] (S3: tbl_df/tbl/data.frame)
$ ride_id : chr [1:791956] "21742443" "21742444" "21742445" "21742446" ...
$ started_at : chr [1:791956] "2019-01-01 0:04:37" "2019-01-01 0:08:13" "2019-01-01 0:13:23" "2019-01-01 0:13:45" ...
$ ended_at : chr [1:791956] "2019-01-01 0:11:07" "2019-01-01 0:15:34" "2019-01-01 0:27:12" "2019-01-01 0:43:28" ...
$ rideable_type : chr [1:791956] "2167" "4386" "1524" "252" ...
$ start_station_id : num [1:791956] 199 44 15 123 173 98 98 211 150 268 ...
$ start_station_name: chr [1:791956] "Wabash Ave & Grand Ave" "State St & Randolph St" "Racine Ave & 18th St" "California Ave & Milwaukee Ave" ...
$ end_station_id : num [1:791956] 84 624 644 176 35 49 49 142 148 141 ...
$ end_station_name : chr [1:791956] "Milwaukee Ave & Grand Ave" "Dearborn St & Van Buren St (*)" "Western Ave & Fillmore St (*)" "Clark St & Elm St" ...
$ member_casual : chr [1:791956] "Subscriber" "Subscriber" "Subscriber" "Subscriber" ...
#Statistical summary of data. Mainly for numerics
summary(all_trips)
ride_id started_at ended_at rideable_type start_station_id
Length:791956 Length:791956 Length:791956 Length:791956 Min. : 2.0
Class :character Class :character Class :character Class :character 1st Qu.: 77.0
Mode :character Mode :character Mode :character Mode :character Median :174.0
Mean :204.4
3rd Qu.:291.0
Max. :675.0
start_station_name end_station_id end_station_name member_casual
Length:791956 Min. : 2.0 Length:791956 Length:791956
Class :character 1st Qu.: 77.0 Class :character Class :character
Mode :character Median :174.0 Mode :character Mode :character
Mean :204.4
3rd Qu.:291.0
Max. :675.0
NA's :1We need to go further to clean the data for consistency, and reliability. We will now check the dataset if they have “NA” values, duplicates or blank values and decide whether to drop them or substitute them.
#Count rows with “NA” values
colSums(is.na(all_trips))
ride_id started_at ended_at rideable_type start_station_id
0 0 0 0 0
start_station_name end_station_id end_station_name member_casual
0 1 1 0
#Remove missing
all_trips <- all_trips[complete.cases(all_trips), ]
# Save and export the cleaned file before dropping any rows using
> write.csv(all_trips, file = "all_trips.csv", row.names = FALSE)To further refine and clean the data, it is necessary to remove empty, “NA”, and missing values. This can be achieved through the use of functions such as drop_na(), remove_missing().
#Remove NA
all_trips <- drop_na(all_trips)
all_trips <- remove_missing(all_trips) # There are a few problems we will need to fix:(1) In the “member_casual” column, there are two names for members (“member” and “Subscriber”) and two names for casual riders (“Customer” and “casual”). We will need to consolidate that from four to two labels.
# In the "member_casual" column, replace "Subscriber" with "member" and "Customer" with "casual"
# Before 2020, Divvy used different labels for these two types of riders ... we will want to make our dataframe consistent with their current nomenclature
# N.B.: "Level" is a special property of a column that is retained even if a subset does not contain any values from a specific level
# Begin by seeing how many observations fall under each usertype
table(all_trips$member_casual)
casual Customer member Subscriber
48479 23163 378407 341906
# Reassign to the desired values (we will go with the current 2020 labels)
all_trips <- all_trips %>%
mutate(member_casual = recode(member_casual
,"Subscriber" = "member"
,"Customer" = "casual"))
# Check to make sure the proper number of observations were reassigned
table(all_trips$member_casual)
casual member
71642 720313(2) The data can only be aggregated at the ride-level, which is too granular. We will want to add some additional columns of data — such as day, month, year — that provide additional opportunities to aggregate the data.
# Add columns that list the date, month, day, and year of each ride
# This will allow us to aggregate ride data for each month, day, or year ... before completing these operations we could only aggregate at the ride level
# https://www.statmethods.net/input/dates.html more on date formats in R found at that link
#The default format is yyyy-mm-dd
all_trips$date <- as.Date(all_trips$started_at)
all_trips$month <- format(as.Date(all_trips$date), "%m")
all_trips$day <- format(as.Date(all_trips$date), "%d")
all_trips$year <- format(as.Date(all_trips$date), "%Y")
all_trips$day_of_week <- format(as.Date(all_trips$date), "%A")(3) We will want to add a calculated field for length of ride since the 2020Q1 data did not have the “tripduration” column. We will add “ride_length” to the entire dataframe for consistency.
# Add a "ride_length" calculation to all_trips (in seconds)
# https://stat.ethz.ch/R-manual/R-devel/library/base/html/difftime.html
all_trips$ride_length <- difftime(all_trips$ended_at,all_trips$started_at)
# Result of “ride_length”column created with time difference in seconds
# Inspect the structure of the columns
str(all_trips)
tibble [791,955 × 15] (S3: tbl_df/tbl/data.frame)
$ ride_id : chr [1:791955] "21742443" "21742444" "21742445" "21742446" ...
$ started_at : chr [1:791955] "2019-01-01 0:04:37" "2019-01-01 0:08:13" "2019-01-01 0:13:23" "2019-01-01 0:13:45" ...
$ ended_at : chr [1:791955] "2019-01-01 0:11:07" "2019-01-01 0:15:34" "2019-01-01 0:27:12" "2019-01-01 0:43:28" ...
$ rideable_type : chr [1:791955] "2167" "4386" "1524" "252" ...
$ start_station_id : num [1:791955] 199 44 15 123 173 98 98 211 150 268 ...
$ start_station_name: chr [1:791955] "Wabash Ave & Grand Ave" "State St & Randolph St" "Racine Ave & 18th St" "California Ave & Milwaukee Ave" ...
$ end_station_id : num [1:791955] 84 624 644 176 35 49 49 142 148 141 ...
$ end_station_name : chr [1:791955] "Milwaukee Ave & Grand Ave" "Dearborn St & Van Buren St (*)" "Western Ave & Fillmore St (*)" "Clark St & Elm St" ...
$ member_casual : chr [1:791955] "member" "member" "member" "member" ...
$ date : Date[1:791955], format: "2019-01-01" "2019-01-01" "2019-01-01" ...
$ month : chr [1:791955] "01" "01" "01" "01" ...
$ day : chr [1:791955] "01" "01" "01" "01" ...
$ year : chr [1:791955] "2019" "2019" "2019" "2019" ...
$ day_of_week : chr [1:791955] "Tuesday" "Tuesday" "Tuesday" "Tuesday" ...
$ ride_length : 'difftime' num [1:791955] 390 441 829 1783 ...
..- attr(*, "units")= chr "secs"
# Convert "ride_length" from Factor to numeric so we can run calculations on the data
> is.factor(all_trips$ride_length)
[1] FALSE
> all_trips$ride_length <- as.numeric(as.character(all_trips$ride_length))
# “secs” which is in character removed in ride_length column
> is.numeric(all_trips$ride_length) # to verify column is numeric
[1] TRUE(4) There are some rides where tripduration shows up as negative, including several hundred rides where Divvy took bikes out of circulation for Quality Control reasons. We will want to delete these rides.
# Remove "bad" data
# The dataframe includes a few hundred entries when bikes were taken out of docks and checked for quality by Divvy or ride_length was negative
# We will create a new version of the dataframe (v2) since data is being removed
# https://www.datasciencemadesimple.com/delete-or-drop-rows-in-r-with-conditions-2/
all_trips_v2 <- all_trips[!(all_trips$start_station_name == "HQ QR" | all_trips$ride_length<0),]
New version of the dataset
Analyze Phase
STEP 4: Conduct Descriptive Analysis
# Descriptive analysis on ride_length (all figures in seconds)
mean(all_trips_v2$ride_length) #straight average (total ride length / rides)
[1] 1189.459
median(all_trips_v2$ride_length) #midpoint number in the ascending array of ride lengths
[1] 539
max(all_trips_v2$ride_length) #longest ride
[1] 10632022
min(all_trips_v2$ride_length) #shortest ride
[1] 1
# You can condense the four lines above to one line using summary() on the specific attribute
summary(all_trips_v2$ride_length)
Min. 1st Qu. Median Mean 3rd Qu. Max.
1 331 539 1190 912 106Let us now compare members and casual users for Cyclistic as the mean ride length, median, the max and minimum ride length for both the variables
# Compare members and casual users
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = mean)
1 casual 5372.7839
2 member 795.2523
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = median)
all_trips_v2$member_casual all_trips_v2$ride_length
1 casual 1393
2 member 508
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = max)
all_trips_v2$member_casual all_trips_v2$ride_length
1 casual 10632022
2 member 6096428
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = min)
all_trips_v2$member_casual all_trips_v2$ride_length
1 casual 2
2 member 1
# See the average ride time taken each day for members vs casual users
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual + all_trips_v2$day_of_week, FUN = mean)
all_trips_v2$member_casual all_trips_v2$day_of_week all_trips_v2$ride_length
1 casual Friday 6090.7373
2 member Friday 796.7338
3 casual Monday 4752.0504
4 member Monday 822.3112
5 casual Saturday 4950.7708
6 member Saturday 974.0730
7 casual Sunday 5061.3044
8 member Sunday 972.9383
9 casual Thursday 8451.6669
10 member Thursday 707.2093
11 casual Tuesday 4561.8039
12 member Tuesday 769.4416
13 casual Wednesday 4480.3724
14 member Wednesday 711.9838
# Notice that the days of the week are out of order. Let's fix that.
all_trips_v2$day_of_week <- ordered(all_trips_v2$day_of_week, levels=c("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"))
all_trips_v2$member_casual all_trips_v2$day_of_week all_trips_v2$ride_length
1 casual Sunday 5061.3044
2 member Sunday 972.9383
3 casual Monday 4752.0504
4 member Monday 822.3112
5 casual Tuesday 4561.8039
6 member Tuesday 769.4416
7 casual Wednesday 4480.3724
8 member Wednesday 711.9838
9 casual Thursday 8451.6669
10 member Thursday 707.2093
11 casual Friday 6090.7373
12 member Friday 796.7338
13 casual Saturday 4950.7708
14 member Saturday 974.0730We will now analyze ridership data by type and weekday, using the entire code below
# analyze ridership data by type and weekday
all_trips_v2 %>% mutate(weekday = wday(started_at, label = TRUE)) %>% #creates weekday field using wday()
group_by(member_casual, weekday) %>%
#groups by usertype and weekday
summarise(number_of_rides = n()
#calculates the number of rides and average duration
,average_duration = mean(ride_length)) %>%
# calculates the average duration
arrange(member_casual, weekday)
#sorts
`summarise()` has grouped output by 'member_casual'. You can override using the `.groups`
argument.
# A tibble: 14 × 4
# Groups: member_casual [2]
member_casual weekday number_of_rides average_duration
<chr> <ord> <int> <dbl>
1 casual Sun 18652 5061.
2 casual Mon 5591 4752.
3 casual Tue 7311 4562.
4 casual Wed 7690 4480.
5 casual Thu 7147 8452.
6 casual Fri 8013 6091.
7 casual Sat 13473 4951.
8 member Sun 60197 973.
9 member Mon 110430 822.
10 member Tue 127974 769.
11 member Wed 121902 712.
12 member Thu 125228 707.
13 member Fri 115168 797.
14 member Sat 59413 974.We also need to consider riders- casual and member that have started maximum from which location and ended at which station.
Now we analyze the location from when max and min riders — casual and member have taken and max end station.
#We show below number of ride variables in start_station_name
n_distinct(all_trips_v2$start_station_name)
[1] 635
#We show below number of ride variables in end_station_name
n_distinct(all_trips_v2$end_station_name)
[1] 636
# to find number of times a location used in start_station_name and create new dataframe
location_start_station <- all_trips_v2 %>% count(start_station_name, sort=TRUE)
start_station_name n
1 Canal St & Adams St 14155
2 Clinton St & Washington Blvd 13640
3 Clinton St & Madison St 13362
4 Columbus Dr & Randolph St 9080
5 Kingsbury St & Kinzie St 9021
6 Canal St & Madison St 8208
7 Michigan Ave & Washington St 7525
8 Franklin St & Monroe St 7227
9 Larrabee St & Kingsbury St 6708
10 Clinton St & Lake St 6674
11 LaSalle St & Jackson Blvd 6426
12 Dearborn St & Monroe St 6332
13 Daley Center Plaza 6251
14 Michigan Ave & Lake St 6113
15 Dearborn St & Erie St 5618
16 Desplaines St & Kinzie St 5521
17 Franklin St & Jackson Blvd 5495
18 Wabash Ave & Grand Ave 5322
19 Wells St & Huron St 5222
20 St. Clair St & Erie St 5182
21 Wells St & Hubbard St 5127
22 Clark St & Elm St 5059
23 Orleans St & Merchandise Mart Plaza 5048
24 Morgan St & Lake St 4891
25 Kingsbury St & Erie St 4876
26 Wacker Dr & Washington St 4839
27 Wabash Ave & Roosevelt Rd 4634
28 State St & Kinzie St 4632
29 Streeter Dr & Grand Ave 4533
30 State St & Randolph St 4521
31 Franklin St & Lake St 4460
32 Clark St & Randolph St 4435
33 Wells St & Concord Ln 4393
34 Clark St & Lake St 4223
35 Fairbanks Ct & Grand Ave 4145
36 Sheffield Ave & Fullerton Ave 4139
37 Ravenswood Ave & Lawrence Ave 4094
38 Ashland Ave & Division St 4090
39 Damen Ave & Pierce Ave 4042
40 Indiana Ave & Roosevelt Rd 3993
41 LaSalle St & Illinois St 3954
42 Desplaines St & Jackson Blvd 3931
43 University Ave & 57th St 3892
44 Lake Shore Dr & Monroe St 3811
45 Millennium Park 3796
46 HQ QR 3766
47 McClurg Ct & Illinois St 3751
48 McClurg Ct & Erie St 3746
49 Clark St & Ida B Wells Dr 3677
50 Marshfield Ave & Cortland St 3638
51 Milwaukee Ave & Grand Ave 3566
52 Franklin St & Chicago Ave 3549
53 Green St & Madison St 3507
54 Wilton Ave & Belmont Ave 3486
55 Sedgwick St & Huron St 3485
56 LaSalle St & Washington St 3484
57 Stetson Ave & South Water St 3472
58 Cityfront Plaza Dr & Pioneer Ct 3445
59 Dearborn St & Adams St 3395
60 Dearborn Pkwy & Delaware Pl 3376
61 Wells St & Elm St 3358
62 Peoria St & Jackson Blvd 3342
63 Broadway & Barry Ave 3336
64 Clark St & Armitage Ave 3305
65 Wabash Ave & Adams St 3300
66 Morgan St & Polk St 3224
67 Sheridan Rd & Irving Park Rd 3159
68 Rush St & Hubbard St 3145
69 Rush St & Superior St 3127
70 Wilton Ave & Diversey Pkwy 3074
71 Federal St & Polk St 3021
72 Wabash Ave & 9th St 3008
73 Shedd Aquarium 2899
74 Loomis St & Lexington St 2880
75 Aberdeen St & Jackson Blvd 2877
76 Clark St & Wrightwood Ave 2814
77 Michigan Ave & Oak St 2813
78 Mies van der Rohe Way & Chicago Ave 2809
79 Wolcott Ave & Polk St 2804
80 Canal St & Jackson Blvd 2751
81 Bissell St & Armitage Ave 2736
82 Wells St & Evergreen Ave 2722
83 Clinton St & Jackson Blvd (*) 2720
84 Ellis Ave & 55th St 2719
85 Clark St & Schiller St 2716
86 State St & Van Buren St 2691
87 Desplaines St & Randolph St 2676
88 Sedgwick St & North Ave 2666
89 Green St & Randolph St 2607
90 Aberdeen St & Monroe St 2596
91 Theater on the Lake 2585
92 Ellis Ave & 60th St 2577
93 Michigan Ave & Jackson Blvd 2559
94 Lincoln Ave & Fullerton Ave 2524
95 Southport Ave & Roscoe St 2520
96 Michigan Ave & Madison St 2499
97 Clark St & Lincoln Ave 2447
98 Clark St & Chicago Ave 2441
99 Mies van der Rohe Way & Chestnut St 2429
100 Wells St & Polk St 2421
max.print----omitted 536 rows
# to find number of times a location used in end_station_name and create new data frame
location_end_station <- all_trips_v2 %>% count(end_station_name, sort = TRUE)
end_station_name n
1 Canal St & Adams St 15067
2 Clinton St & Washington Blvd 14865
3 Clinton St & Madison St 13713
4 Kingsbury St & Kinzie St 8991
5 Michigan Ave & Washington St 8639
6 Canal St & Madison St 8581
7 Clinton St & Lake St 6943
8 Franklin St & Monroe St 6519
9 Daley Center Plaza 6458
10 LaSalle St & Jackson Blvd 6409
11 Dearborn St & Monroe St 6296
12 Michigan Ave & Lake St 6244
13 Larrabee St & Kingsbury St 6179
14 St. Clair St & Erie St 6108
15 Dearborn St & Erie St 5899
16 Clark St & Elm St 5514
17 Streeter Dr & Grand Ave 5416
18 Columbus Dr & Randolph St 5408
19 State St & Kinzie St 5316
20 Wells St & Hubbard St 5297
21 Wabash Ave & Grand Ave 5272
22 Morgan St & Lake St 5256
23 Wabash Ave & Roosevelt Rd 5203
24 Franklin St & Jackson Blvd 5110
25 Desplaines St & Kinzie St 4846
26 Wells St & Huron St 4841
27 Millennium Park 4588
28 Clark St & Randolph St 4545
29 State St & Randolph St 4527
30 Wacker Dr & Washington St 4456
31 Wells St & Concord Ln 4378
32 Kingsbury St & Erie St 4298
33 Fairbanks Ct & Grand Ave 4295
34 Orleans St & Merchandise Mart Plaza 4294
35 Franklin St & Lake St 4283
36 Damen Ave & Pierce Ave 4177
37 LaSalle St & Illinois St 4075
38 Sheffield Ave & Fullerton Ave 4035
39 Ashland Ave & Division St 4008
40 University Ave & 57th St 3956
41 Ravenswood Ave & Lawrence Ave 3942
42 Clark St & Lake St 3911
43 Desplaines St & Jackson Blvd 3865
44 Green St & Madison St 3848
45 Broadway & Barry Ave 3813
46 Indiana Ave & Roosevelt Rd 3793
47 HQ QR 3766
48 Clark St & Ida B Wells Dr 3762
49 McClurg Ct & Illinois St 3712
50 Dearborn Pkwy & Delaware Pl 3673
51 McClurg Ct & Erie St 3657
52 Marshfield Ave & Cortland St 3521
53 LaSalle St & Washington St 3513
54 Milwaukee Ave & Grand Ave 3454
55 Sedgwick St & Huron St 3430
56 Franklin St & Chicago Ave 3399
57 Wilton Ave & Belmont Ave 3332
58 Dearborn St & Adams St 3299
59 Wells St & Elm St 3283
60 Clark St & Armitage Ave 3267
61 Lake Shore Dr & Monroe St 3259
62 Michigan Ave & Oak St 3227
63 Peoria St & Jackson Blvd 3166
64 Cityfront Plaza Dr & Pioneer Ct 3156
65 Wabash Ave & Adams St 3156
66 Morgan St & Polk St 3151
67 Clinton St & Jackson Blvd (*) 3137
68 Rush St & Hubbard St 3123
69 Rush St & Superior St 3098
70 Wilton Ave & Diversey Pkwy 3049
71 Canal St & Jackson Blvd 3044
72 Sheridan Rd & Irving Park Rd 3042
73 Green St & Randolph St 2989
74 Wabash Ave & 9th St 2982
75 Federal St & Polk St 2950
76 Aberdeen St & Jackson Blvd 2863
77 Clark St & Wrightwood Ave 2848
78 Bissell St & Armitage Ave 2805
79 Theater on the Lake 2794
80 Mies van der Rohe Way & Chicago Ave 2762
81 Ellis Ave & 55th St 2704
82 Wolcott Ave & Polk St 2698
83 Michigan Ave & Jackson Blvd 2689
84 Lincoln Ave & Fullerton Ave 2648
85 Southport Ave & Roscoe St 2647
86 Loomis St & Lexington St 2592
87 Michigan Ave & Madison St 2589
88 Aberdeen St & Monroe St 2570
89 Wells St & Evergreen Ave 2549
90 Ellis Ave & 60th St 2532
91 Sheffield Ave & Kingsbury St 2523
92 State St & Van Buren St 2509
93 Kimbark Ave & 53rd St 2501
94 Clark St & Lincoln Ave 2495
95 Shedd Aquarium 2481
96 Wabash Ave & Wacker Pl 2468
97 Desplaines St & Randolph St 2461
98 Broadway & Waveland Ave 2451
99 Sheffield Ave & Wellington Ave 2446
100 Clark St & Schiller St 2379
[ reached 'max' / getOption("max.print") -- omitted 536 rows ]
location_start_station <- all_trips_v2 %>% count(end_station_name, sort = TRUE)
The location for start and end station is in the state of Chicago.
# We add Chicago to the start and end station name columns using the below code:
total_trip_start_station$start_station_name <- paste(total_trip_start_station$start_station_name, "Chicago", sep = ", ")
member_casual start_station_name total rides
casual 2112 W Peterson Ave, Chicago 12
casual 63rd St Beach, Chicago 52
casual 900 W Harrison St, Chicago 143
etc…..
total_trip_end_station$end_station_name <- paste(total_trip_end_station$end_station_name, "Chicago", sep = ", ")
member_casual start_station_name total rides
casual 2112 W Peterson Ave, Chicago 14
casual 63rd St Beach, Chicago 47
casual 900 W Harrison St, Chicago 110Share Phase
We will have to answer the key problems, the key stakeholder-Marketing and executive committee will use to make strategic decision to increase riders and sales.
# Lets visualize comparison of members and casual users
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = mean)
1 casual 5372.7839
2 member 795.2523
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = median)
all_trips_v2$member_casual all_trips_v2$ride_length
1 casual 1393
2 member 508
aggregate(all_trips_v2$ride_length~all_trips_v2$member_casual, FUN = max)
all_trips_v2$member_casual all_trips_v2$ride_length
1 casual 10632022
2 member 6096428
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual, FUN = min)
all_trips_v2$member_casual all_trips_v2$ride_length
1 casual 2
2 member 1
#Lets visualize ride duration statistics by user type
ride_stats <- all_trips_v2 %>%
group_by(member_casual) %>%
summarize (
mean_length = mean(ride_length),
median_length = median(ride_length),
max_length = max(ride_length),
min_length = min(ride_length))
ride_stats <- data.frame(
user_type = c("casual", "member", "casual", "member", "casual", "member", "casual", "member"),
metric = c("Average", "Average", "Median", "Median", "Max", "Max", "Min", "Min"),
value = c(5372.7839, 795.2523, 1393, 508, 10632022, 6096428, 2, 1))
ggplot(ride_stats, aes(x = user_type, y = value, fill = user_type)) +
geom_col() +
facet_wrap(~metric, scales = "free_y") +
labs(title = "Ride Duration Statistics by User Type",
subtitle = "Note: Y-axes differ across panels to accommodate scale differences",
x = "User Type",
y = "Seconds",
fill = "User Type") +
theme_minimal() +
scale_fill_brewer(palette = "Paired")
Ride Duration by User type-Casual or Member
# See the average ride time taken each day for members vs casual users
aggregate(all_trips_v2$ride_length ~ all_trips_v2$member_casual + all_trips_v2$day_of_week, FUN = mean)
all_trips_v2$member_casual all_trips_v2$day_of_week all_trips_v2$ride_length
1 casual Friday 6090.7373
2 member Friday 796.7338
3 casual Monday 4752.0504
4 member Monday 822.3112
5 casual Saturday 4950.7708
6 member Saturday 974.0730
7 casual Sunday 5061.3044
8 member Sunday 972.9383
9 casual Thursday 8451.6669
10 member Thursday 707.2093
11 casual Tuesday 4561.8039
12 member Tuesday 769.4416
13 casual Wednesday 4480.3724
14 member Wednesday 711.9838
# Let's create a sample data frame like your aggregate output
agg_data <- data.frame(
member_casual = rep(c("casual", "member"), 7),
day_of_week = rep(c("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"), each = 2),
ride_length = c(5061.3044, 972.9383, 4752.0504, 822.3112, 4561.8039, 769.4416, 4480.3724, 711.9838, 8451.6669, 707.2093, 6090.7373, 796.7338, 4950.7708, 974.0730)
)
# Make sure day_of_week is a factor in the correct order for plotting
days_order <- c("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
agg_data$day_of_week <- factor(agg_data$day_of_week, levels = days_order)
# --- 2. Create the ggplot ---
ggplot(agg_data, aes(x = day_of_week, y = ride_length, fill = member_casual)) +
geom_col(position = "dodge") + # 'dodge' places bars side-by-side
labs(
title = "Average Ride Length by Day of Week & Member Type",
x = "Day of Week",
y = "Average Ride Length (seconds)", # Adjust units if needed
fill = "Member Type"
) +
theme_minimal() + scale_fill_brewer(palette = "Paired") +# A clean theme
theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels
Average ride time taken each day for members vs casual users
# Let's visualize the number of rides by rider type
all_trips_v2 %>%
mutate(weekday = wday(started_at, label = TRUE)) %>%
group_by(member_casual, weekday) %>%
summarise(number_of_rides = n()
, average_duration = mean(ride_length)) %>%
arrange(member_casual, weekday) %>%
ggplot(aes(x = weekday, y = number_of_rides, fill = member_casual)) +
geom_col(position = "dodge")
Number of Rides each day by Rider type
# Let's create a visualization for average duration
all_trips_v2 %>%
mutate(weekday = wday(started_at, label = TRUE)) %>%
group_by(member_casual, weekday) %>%
summarise(number_of_rides = n()
,average_duration = mean(ride_length)) %>%
arrange(member_casual, weekday) %>%
ggplot(aes(x = weekday, y = average_duration, fill = member_casual)) +
geom_col(position = "dodge")
Average duration of ride per day by Rider type
# Lets now visualize total rides taken by manual and casual riders from start and end station. For this, we first plot the latitude and longitude for each place.
Use the latitude and longitude from the original database to verify the locations. As we don’t have data of latitude and longitude of some locations in Q1 2019, we find them using R Studio.
We plot the location on the map, we now find the latitude and longitude for each start and end station. We thus create 2 columns for start and end trip dataframes.
For locations that don’t have latitude and longitude-Q1 2019. We first install “tidygeocoder”
geo_trip_start_station <- total_trip_start_station %>% geocode(address = start_station_name, method = "OSM", lat = latitude, long = longitude)
member_casual start_station_name total_rides latitude longitude
casual 2112 W Peterson Ave, Chicago 12 41.99096 -87.68284
casual 63rd St Beach, Chicago 52 41.78249 -87.57450
casual 900 W Harrison St, Chicago 143 41.87476 -87.64981
etc….
geo_trip_start_station <- total_trip_start_station %>% geocode(address = start_station_name, method = "OSM", lat = latitude, long = longitude)
member_casual start_station_name total_rides latitude longitude
casual 2112 W Peterson Ave, Chicago 14 41.99096 -87.68284
casual 63rd St Beach, Chicago 47 41.78249 -87.57450
casual 900 W Harrison St, Chicago 110 41.87476 -87.64981
etc….Create a visualization to plot total rides for start station on Tableau
Upload the start station total ride.csv with latitude and longitude. We get the below visual also showing total rides for both member and casual riders at the start station. Using Tableau created visualization for total rides start station Q1 2019 & 2020 and total rides end station Q1 2019 & 2020
Total Start Station rides from Q1 2019 & 2020 for Member and Casual riders
Total End Station rides from Q1 & 2020 for Member and Casual riders
From the above visualization, we can conclude that casual riders use bike for recreational purposes and start and end from hotels, parks, aquariums, harbors and member riders use bike for work, business centers, banks, schools, universities, institutes, theatres, residential areas, hospitals.
Summary
-
Average duration of rides is more for casual riders than member riders, i.e casual riders use bikes for longer duration and member riders use bikes for shorter duration.
-
Number of casual riders are more during weekends than weekdays as bikes used for recreational purposes. While number of member riders are more during the weekday
We also noticed that casual riders use bikes for recreational purposes and start and end hotels, parks, aquariums, harbors and member riders use bike for work, business centers, banks, schools, universities, institutes, theatres, residential areas, hospitals.
ACT
As per our analysis, we suggest the following recommendations:
-
We can create marketing campaigns and advertising for casual riders at other recreational areas to motivate the casual riders to become members.
-
Marketing advertising at residential areas can also increase bike riders especially during spring and summers when casual riders are at peak.
-
Create marketing campaigns at business centers, universities, schools, hospital on weekday to increase member riders’ base as we see that member riders are more even though for short ride duration.
-
We can provide incentive and discount to casual riders during weekend that can convert to more members
-
Having entire year data, we can further analyze and report marketing campaigns which ca be focused monthly, quarterly or half yearly for a particular location to increase member riders.
Secondary Case study Sources:
Coursera-Using RScript, John Rama, Visualization type for location-Somia Nasir
메타데이터
- post_id
- 90f2b6663d6b
- slug
- case-study-design-cyclistics-marketing-strategy-90f2b6663d6b
- url
- https://medium.com/@juliana_dsouza2001/case-study-design-cyclistics-marketing-strategy-90f2b6663d6b
- canonical_url
- https://medium.com/@juliana_dsouza2001/case-study-design-cyclistics-marketing-strategy-90f2b6663d6b
- author_url
- https://medium.com/@juliana_dsouza2001
- status
- ok
- fetched_at
- 2026-06-09 15:37:30
