Case study: How does a bike-share navigate speedy success?

Coursera: Google Data Analytics Professional Certificate - Capstone Project

Here’s my strategy for tackling the first case study in the capstone course of the Google Data Analytics Professional Certificate.

Scenario

The marketing director thinks that the company’s future success relies on maximizing annual membership numbers. Therefore, we need to understand how casual riders and annual members use Cyclistic bikes differently.

From these insights, a new marketing strategy will convert casual riders into annual members. But first, Cyclistic executives must approve our recommendations, so we need to provide compelling data insights and professional data visualizations to back them up.

---

Characters and teams

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 your 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 group of data analysts who collect, analyze, and report data to guide Cyclistic’s marketing strategy. You joined this 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.

---

About the company

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 anytime.

So far, Cyclistic’s marketing strategy focused on creating awareness and attracting a wide range of consumers.

One approach that helped make these things possible was the flexibility of its pricing plans:

• single-ride passes

• full-day passes

• 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 executive team needs to better understand:

• How annual members and casual riders differ

• Why casual riders would buy a membership

• 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.

---

The Task

Three questions will 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 us the first question to answer:

How do annual members and casual riders use Cyclistic bikes differently?

We need to produce a report with the following deliverables:

1. A clear statement of the business task

2. A description of all data sources used

3. Documentation of any cleaning or manipulation of data

4. A summary of your analysis

5. Supporting visualizations and key findings

6. Your top three recommendations based on your analysis

---

In order to answer the business questions, we follow the six steps of the data analysis process.

Ask

To recap, Lily Moreno believes there is a solid opportunity to convert casual riders into members.

There are two reasons for her beliefs:

• casual riders are already aware of the Cyclistic program;

• casual riders have chosen Cyclistic for their mobility needs.

Her goal is to design a marketing strategy aimed at converting casual riders into annual members. To present this strategy to the executive team we need to understand how annual members and casual riders differ.

We note that the executive team is detail-oriented, so this analysis must produce compelling insights.

Statement of the business task:

How do annual members and casual riders use Cyclistic bikes differently?

Prepare and Process

To analyze and identify trends, we use Cyclistic’s historical trip data saved here.

The Divvy Bikes website, which we can find at divvybikes.com, provides historical trip data for public use; we are welcome to explore and utilize this data. Here are the details:

• Data Availability: Divvy’s trip data is available for download, mapping, animation, or any other creative use.

• Data Content: Each trip in the dataset is anonymized

• Data Processing: The dataset has been processed to remove trips taken by staff during system service and inspection, as well as trips shorter than 60 seconds (which might be false starts or users re-docking bikes to ensure security).

• License Agreement: The data is provided according to the Divvy Data License Agreement and is released on a monthly schedule.

Upon reading the license, I have concluded that data is Reliable, Original, Comprehensive, Current and Cited.

Although it was suggested we should download the previous twelve months of trip data (April 2023 to March 2024), I downloaded all the files for I was interested in checking whether there is a growth in bike usage.

The files are all compressed to zip files. I have chosen to download them all in a dedicated folder of my computer and extract the CSV files, so I can examine their structure.

My strategy was to:

• Make the headings of all CSV files uniform.
  
  Files covering the period 2020 to 2024 have an extra column: rideable_type, whereas the files for the period 2013 to 2019 have two extra columns: gender and birthday.
  
  In addition, the files for the period 2013 to 2019 offer the same structure, but sometimes different headings; I had to manually change the headings of each file to conform to the following heading: { “trip_id”, “starttime”, “stoptime”, “bikeid”, “tripduration”, “from_station_id”, “from_station_name”, “to_station_id”, “to_station_name”, “usertype”, “gender”, “birthday” }. A special note for the “usertype” field: two records - “Customer” and “Subscriber”
  
  The files of the second period, 2020 to 2014, present an uniform structure. A special note for the “member_casual” field: two records: “casual” and “member”.

• Upload them all on Google Cloud Storage in a dedicated bucket called divvy_trips. Google created a project for me, “careful-alloy-419717”.
  
  It is essential to ensure that all files in this bucket have the same heading; if not, the next step (upload them as append in BigQuery would fail).

• Create a dataset in Google BigQuery with the same name, divvy_trips, and two tables:

  • divvy_trips_2013_2019

  • divvy_trips_2020_2024

Structure of the 2013_2019 table:

Structure of the 2020_2024:

Note how different the schemas are.

Once the two tables are created, the next step was to summarize them; otherwise the size of the dataset (in excess of 21 millions rows) would have made it impossible to analyse in R.

SELECT
  EXTRACT( DATE FROM starttime) AS date,
  EXTRACT( HOUR FROM starttime) AS hour,
  usertype,
  AVG( tripduration )           AS avg_trip_duration,
  MIN( tripduration )           AS min_trip_duration,
  MAX( tripduration )           AS max_trip_duration,
  COUNT( DISTINCT trip_id )     AS total_records
FROM careful-alloy-419717.divvy_trips.2013_2019
GROUP BY
  EXTRACT( DATE FROM starttime),
  EXTRACT( HOUR FROM starttime),
  usertype;

And for the second table:

WITH orig_data AS (
  SELECT
    ride_id,
    EXTRACT( DATE FROM started_at )                         AS date,
    EXTRACT( HOUR FROM started_at )                         AS hour,
    IF( member_casual = 'member', 'Subscriber', 'Customer') AS usertype,
    TIMESTAMP_DIFF( ended_at, started_at, SECOND)           AS tripduration
  FROM careful-alloy-419717.divvy_trips.2020_2024
)

SELECT 
  date,
  hour,
  usertype,
  AVG( tripduration )           AS avg_trip_duration,
  MIN( tripduration )           AS min_trip_duration,
  MAX( tripduration )           AS max_trip_duration,
  COUNT( DISTINCT ride_id )     AS total_records
FROM orig_data
GROUP BY
  date,
  hour,
  usertype;

I have saved the results of both queries as two BiqQuery tables with a view to consolidate them into one. Both tables have now a similar schema, with only one exception: the dataset 2013_2019 does not contain the “rideable_type” field.

The UNION query is simple:

SELECT 
  '' AS rideable_type,
  *
FROM careful-alloy-419717.divvy_trips.divvy_trips_2013_2019

UNION ALL

SELECT *
FROM careful-alloy-419717.divvy_trips.divvy_trips_2020_2024;

I have saved the query as a Google BigQuery table and noted the result of summation: 263254 records:

A final query on the combined table allowed me to save the result to a CSV file in my Google Drive:

Analyze

I have decided to analyze the dataset in R.

A cursory structure command reveals its composition:

library(tidyverse)
library(lubridate)
library(dplyr)
# force all conflicts to become errors
library(conflicted)
conflicts_prefer(dplyr::filter)

#Import CSV file
divvy_trips <- read.csv("C:\\Temp\\divvy_trips_aggregate_all.csv")
# Transform as tibble
divvy_trips <- tibble(divvy_trips)

#Examine tibble
str(divvy_trips)

## tibble [263,254 × 8] (S3: tbl_df/tbl/data.frame)
##  $ rideable_type    : chr [1:263254] "" "" "" "" ...
##  $ date             : chr [1:263254] "10 07 2016" "30 06 2013" "30 06 2019" "02 12 2018" ...
##  $ hour             : int [1:263254] 0 0 0 0 0 0 0 0 0 0 ...
##  $ usertype         : chr [1:263254] "Customer" "Customer" "Customer" "Subscriber" ...
##  $ avg_trip_duration: num [1:263254] 1984 1320 1971 1552 2096 ...
##  $ min_trip_duration: int [1:263254] 210 248 190 184 194 118 224 242 76 249 ...
##  $ max_trip_duration: int [1:263254] 72047 4936 13731 39590 11205 2029 6308 40533 2181 26151 ...
##  $ total_records    : int [1:263254] 93 16 140 44 150 51 120 114 70 67 ...

head(divvy_trips)

## # A tibble: 6 × 8
##   rideable_type date        hour usertype   avg_trip_duration min_trip_duration
##   <chr>         <chr>      <int> <chr>                  <dbl>             <int>
## 1 ""            10 07 2016     0 Customer               1984.               210
## 2 ""            30 06 2013     0 Customer               1320.               248
## 3 ""            30 06 2019     0 Customer               1971.               190
## 4 ""            02 12 2018     0 Subscriber             1552.               184
## 5 ""            21 07 2019     0 Customer               2096.               194
## 6 ""            21 04 2019     0 Subscriber              574.               118
## # ℹ 2 more variables: max_trip_duration <int>, total_records <int>

Note again that rideable_type has empty records for period ending 31^st Dec 2019.

Let us therefore analyse this dataset separately, for both periods, before 2020 and after.

Likewise, the “date” field is a character type; I need it to be a date type.

# Convert "date" field as date
divvy_trips$date <- as.Date(divvy_trips$date, format = "%d %m %Y")

In addition, I wanted to add four more fields, useful for summary:

• year

• end_of_month

• week_day (as integer, from 1 - Sunday to 7 - Saturday) and

• week_day_type (if week_day in {1, 7} then “weekend” else “weekday”

# Add four new columns and remove part years
divvy_trips <- divvy_trips %>%
  # create year
  mutate(year = year(date)) %>%
  # create end of month
  mutate(end_of_month = ceiling_date(date, "month") - days(1)) %>%
  # create week_day (as integer)
  mutate(week_day = weekdays(date, abbreviate = TRUE)
  ) %>%
  mutate(
    week_day = match(week_day, c("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"))
  ) %>%
  # create week_day_type
  mutate(
    week_day_type = case_when( week_day %in% c(1, 7) ~ "weekend", TRUE ~ "weekday")
  ) %>%
  filter(date >= as.Date("2014-01-01") & date <= as.Date("2019-12-31"))

# Examine tibble
str(divvy_trips)

## tibble [98,176 × 12] (S3: tbl_df/tbl/data.frame)
##  $ rideable_type    : chr [1:98176] "" "" "" "" ...
##  $ date             : Date[1:98176], format: "2016-07-10" "2019-06-30" ...
##  $ hour             : int [1:98176] 0 0 0 0 0 0 0 0 0 0 ...
##  $ usertype         : chr [1:98176] "Customer" "Customer" "Subscriber" "Customer" ...
##  $ avg_trip_duration: num [1:98176] 1984 1971 1552 2096 574 ...
##  $ min_trip_duration: int [1:98176] 210 190 184 194 118 224 242 76 249 135 ...
##  $ max_trip_duration: int [1:98176] 72047 13731 39590 11205 2029 6308 40533 2181 26151 2002 ...
##  $ total_records    : int [1:98176] 93 140 44 150 51 120 114 70 67 73 ...
##  $ year             : num [1:98176] 2016 2019 2018 2019 2019 ...
##  $ end_of_month     : Date[1:98176], format: "2016-07-31" "2019-06-30" ...
##  $ week_day         : int [1:98176] 1 1 1 1 1 1 1 1 1 1 ...
##  $ week_day_type    : chr [1:98176] "weekend" "weekend" "weekend" "weekend" ...

head(divvy_trips)

## # A tibble: 6 × 12
##   rideable_type date        hour usertype   avg_trip_duration min_trip_duration
##   <chr>         <date>     <int> <chr>                  <dbl>             <int>
## 1 ""            2016-07-10     0 Customer               1984.               210
## 2 ""            2019-06-30     0 Customer               1971.               190
## 3 ""            2018-12-02     0 Subscriber             1552.               184
## 4 ""            2019-07-21     0 Customer               2096.               194
## 5 ""            2019-04-21     0 Subscriber              574.               118
## 6 ""            2015-08-23     0 Customer               1561.               224
## # ℹ 6 more variables: max_trip_duration <int>, total_records <int>, year <dbl>,
## #   end_of_month <date>, week_day <int>, week_day_type <chr>

Let’s create a pie chart, based on total_records, to show the proportion in usertype:

# Create a piechart on total records
pc_usertype <- divvy_trips %>%
  group_by(usertype) %>%
  summarise(count = sum(total_records)) %>%
  mutate(percentage = count / sum(count))

# Build a pie chart
percentage <- pc_usertype$percentage
labels <- pc_usertype$usertype
piepercent <- round(100 * percentage, 1)

# Check if the sum of percentages is 100
if (sum(piepercent) != 100) {
  stop("Sum of percentages must be 100.")
}

# Create the pie chart
pie(piepercent, 
    labels = paste(labels, " (", piepercent, "%)"),  # Add labels and percentages
    main = "Before 2020: Proportion of riders in total records",
    col = rainbow(length(labels))
)

And another pie chart, but this time based on average of ride duration (seconds):

# Create a piechart on average riding duration (seconds)
pc_usertype <- divvy_trips %>%
  group_by(usertype) %>%
  summarise(avg = mean(avg_trip_duration)) %>%
  mutate(percentage = avg / sum(avg))

# Build a pie chart
percentage <- pc_usertype$percentage
labels <- pc_usertype$usertype
piepercent <- round(100 * percentage, 1)

# Check if the sum of percentages is 100
if (sum(piepercent) != 100) {
  stop("Sum of percentages must be 100.")
}

# Create the pie chart
pie(piepercent, 
    labels = paste(labels, " (", piepercent, "%)"),  # Add labels and percentages
    main = "Before 2020: Proportion of riders in average riding time",
    col = rainbow(length(labels))
)

The first pie chart shows the proportion on usage on both types of customers: paid subscribers ride more than trice than casual customers.

However, the second pie chart shows a completely different picture; casual customers enjoy riding an average riding time more than four times above the paid subscribers.

And, if we want to see the same proportion in week_day_type, first on total rides:

# Create pie chart on total records
pie_chart <- divvy_trips %>%
  group_by(usertype, week_day_type) %>%
  summarise(count = sum(total_records)) %>%
  mutate(percentage = count / sum(count))

# Build the percentages
percentage <- pie_chart$percentage
labels <- paste(pie_chart$usertype, pie_chart$week_day_type)
piepercent <- round(100 * percentage, 1)

# Rename the levels of the factor variable
levels(pie_chart$week_day_type) <- c("weekday", "weekend")

# Create the pie chart
ggplot(pie_chart, aes(x = "", y = percentage, fill = week_day_type)) +
  geom_bar(width = 1, stat = "identity") +
  coord_polar("y", start = 0) +
  facet_grid(. ~ usertype) +
  geom_text(aes(label = scales::percent(percentage)), 
            position = position_stack(vjust = 0.5)) +
  theme_void() +
  theme(
    strip.text.x = element_text(size = 12, face = "bold"),
    legend.title = element_blank(),
    legend.position = "bottom"
    ) +
  labs(
    title = "User type distribution by week day type",
    subtitle = "Before 2020: Total records",
    caption = 
      "Condering total rides, both rider types prefer weekdays (more so paid   subscribers)"
    ) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

And the same chart, but this time on average riding time:

pie_chart <- divvy_trips %>%
  group_by(usertype, week_day_type) %>%
  summarise(count = mean(avg_trip_duration)) %>%
  mutate(percentage = count / sum(count))

# Build the percentages
percentage <- pie_chart$percentage
labels <- paste(pie_chart$usertype, pie_chart$week_day_type)
piepercent <- round(100 * percentage, 1)

# Rename the levels of the factor variable
levels(pie_chart$week_day_type) <- c("weekday", "weekend")

# Create a pie chart
ggplot(pie_chart, aes(x = "", y = percentage, fill = week_day_type)) +
  geom_bar(width = 1, stat = "identity") +
  coord_polar("y", start = 0) +
  facet_grid(. ~ usertype) +
  geom_text(aes(label = scales::percent(percentage)), 
            position = position_stack(vjust = 0.5)) +
  theme_void() +
  theme(
    strip.text.x = element_text(size = 12, face = "bold"),
    legend.title = element_blank(),
    legend.position = "bottom"
    ) +
  labs(
    title = "User type distribution by week day type",
    subtitle = "Before 2020: Average ride duration in seconds",
    caption = "Close distribution, both rider types prefer weekends"
    ) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

We have proved that the majority is made by paid customers, riding in weekdays.

Let’s see whether there is any growth over years:

# Prepare the tibble for linechart; group the dataframe on end_of_month and usertype
lc_endofmonth <- divvy_trips %>%
  group_by(end_of_month, usertype) %>%
  summarise(count = sum(total_records))
  
# Examination
glimpse(lc_endofmonth)

## Rows: 144
## Columns: 3
## Groups: end_of_month [72]
## $ end_of_month <date> 2014-01-31, 2014-01-31, 2014-02-28, 2014-02-28, 2014-03-…
## $ usertype     <chr> "Customer", "Subscriber", "Customer", "Subscriber", "Cust…
## $ count        <int> 1060, 24016, 1203, 26884, 11830, 57478, 51250, 107226, 11…

# The linechart with trends
ggplot(data=lc_endofmonth) + 
  geom_line(
    mapping=aes(x=end_of_month, y=count, color=usertype)
  ) +
  geom_smooth(
    mapping=aes(x=end_of_month, y=count, color=usertype),
    # use linear regression to fit the line
    method="lm",
    # don't include a confidence interval
    se=FALSE,
    # solid line
    linetype="solid",
    # line size
    size=1.5
  ) +
  # ensure y axis shows values properly formatted
  scale_y_continuous(n.breaks=10, labels = scales::comma) +
  labs(
    title = 
      "Total rides (total_records) by user types",
    subtitle = 
      "Data from Jan 2014 to Dec 2023 (data aggregated on end_of_month)",
    caption = 
      "Seasonality evident, but trends show stagnation in casual customers and growth in paid subscribers.",
    color="User type"
  ) + 
  # remove redundant axis labels
  theme(axis.title = element_blank()) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

If, however, we plot the data on average rising duration (seconds), the situation is different:

lc_year <- divvy_trips %>%
  group_by(end_of_month, usertype) %>%
  summarise(average = mean(avg_trip_duration))

# The linechart with trends
ggplot(data=lc_year) + 
  geom_line(
    mapping=aes(x=end_of_month, y=average, color=usertype)
  ) +
  geom_smooth(
    mapping=aes(x=end_of_month, y=average, color=usertype),
    # use linear regression to fit the line
    method="lm",
    # don't include a confidence interval
    se=FALSE,
    # solid line
    linetype="solid",
    # line size
    size=1.5
  ) +
  # ensure y axis shows values properly formatted
  scale_y_continuous(n.breaks=10, labels = scales::comma) +
  labs(
    title = 
      "Average duration rides (seconds) by user types",
    subtitle= 
      "Data from Jan 2014 to Dec 2023 (data aggregated on end_of_month)",
    caption = 
      "Slight increase in casual customers; plateau in paid subscribers",
    color="User type"
  ) + 
  # remove redundant axis labels
  theme(axis.title = element_blank()) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

We already know when users ride most, but we don’t have in years:

# Prepare the tibble for linechart; group the dataframe on end_of_month, weekday_type and usertype
lc_endofmonth_weekdaytype <- divvy_trips %>%
  group_by(end_of_month, week_day_type, usertype) %>%
  summarise(count = sum(total_records))

# Build a linechart to see evolution on rides on usertype and week_day_type
ggplot(data=lc_endofmonth_weekdaytype) + 
  geom_smooth(
    mapping=aes(x=end_of_month, y=count, color=week_day_type), 
    method="gam", formula=y~s(x)
  ) + 
  # remove dual y axis and force it for comparability
  facet_wrap(~usertype, scales = "fixed") + 
  scale_y_continuous(n.breaks=10, labels = scales::comma, limits = c(0, NA)) +
  labs(
    title = 
      "Total rides by user types",
    subtitle = 
      "Data from Jan 2014 to Dec 2023 (data aggregated on years)",
    caption = 
      "It is confirmed that both types of customers prefer to ride on weekdays.
       However, casual customers seem indifferent.",
    color="Weekday type"
  ) + 
  # remove redundant axis labels
  theme(axis.title = element_blank()) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

And the same analysis, but this time on average duration rides:

# Prepare the tibble for linechart; group the dataframe on end_of_month, weekday_type and usertype
lc_endofmonth_weekdaytype <- divvy_trips %>%
  group_by(end_of_month, week_day_type, usertype) %>%
  summarise(average = mean(avg_trip_duration))

# Build a linechart to see evolution on rides on usertype and week_day_type
ggplot(data=lc_endofmonth_weekdaytype) + 
  geom_smooth(
    mapping=aes(x=end_of_month, y=average, color=week_day_type), 
    method="gam", formula=y~s(x)
  ) + 
  # romove dual y axis and force it for comparability
  facet_wrap(~usertype, scales = "fixed") + 
  scale_y_continuous(n.breaks=10, labels = scales::comma, limits = c(0, NA)) +
  labs(
    title = 
      "Total rides by user types",
    subtitle = 
      "Data from Jan 2014 to Dec 2023 (data aggregated on years)",
    caption = 
      "Casual customers ride on average more in weekends.
       Paying subscribers are indifferent.
       The situation reversed.",
    color="Weekday type"
  ) + 
  # remove redundant axis labels
  theme(axis.title = element_blank()) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

Let’s check the distribution on days:

# Prepare histogram_days
histogram_data_days <- divvy_trips %>%
  group_by(usertype, end_of_month, week_day) %>%
  summarise(count = sum(total_records))
  
# Convert 'week_day' to factor for categorical x-axis
histogram_data_days$week_day <- factor(histogram_data_days$week_day, levels = 1:7)

# Create histogram
ggplot(histogram_data_days, aes(x = week_day, y = count)) +
  geom_bar(stat = "identity", fill = "steelblue") +
  facet_wrap(~usertype, scales = "fixed") +
  scale_x_discrete(
    breaks = 1:7, 
    labels = c("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
    ) +
  scale_y_continuous(labels = function(x) format(x, scientific = FALSE, big.mark = ",")) +
  labs(
    title = 
      "Histogram of total records by weekday for both type of users",
    subtitle = "Before 2020",
    caption = 
      "Paid subscribers: High usage on weekdays.
      Casual customers: High usage on weekends."
  ) +
  # remove redundant axis labels
  theme(axis.title = element_blank()) +
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

And on hours; my hypothesis is paid customers use the bikes for commuting:

# Prepare histogram_hour
histogram_data_hours <- divvy_trips %>%
  group_by(usertype, end_of_month, hour) %>%
  summarise(count = sum(total_records))
  
# Convert 'hour' to factor for categorical x-axis
histogram_data_hours$hour <- factor(histogram_data_hours$hour, levels = 0:23)

# Create histogram
ggplot(histogram_data_hours, aes(x = hour, y = count)) +
  geom_bar(stat = "identity", fill = "steelblue") +
  facet_wrap(~usertype, scales = "fixed") +
  scale_x_discrete(breaks = 0:23) +
  scale_y_continuous(labels = function(x) format(x, scientific = FALSE, big.mark = ",")) +
  labs(
       title = 
         "Histogram of total records by hour for both type of users",
       subtitle = "Before 2020",
       caption = 
        "Paid subscribers: High usage in mornings and late afternoon.
         Casual customers: Higher usage in the afternoons."
  ) +
  # remove redundant axis labels
  theme(axis.title = element_blank()) +
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

In conclusion, for the period 1^st Jan 2014 to 31^st Dec 2019:

• Paid subscribers take more rides than casual customers.

• Casual customers, on the other hand, ride longer than paid subscribers.
  It is confirmed that casual customers ride for leisure, whereas paid subscribers cycle to commute

• Casual customers show stagnation, whereas paid subscribers show growth.

Now, let’s do the same analysis, but this time for the period starting 1^st Jan 2020. For the sake of brevity, we will remove comments until the end (captions are sufficient).

library(tidyverse)
library(lubridate)
library(dplyr)
# force all conflicts to become errors
library(conflicted)
conflicts_prefer(dplyr::filter)

#Import CSV file
divvy_trips <- read.csv("C:\\Temp\\divvy_trips_aggregate_all.csv")
# Transform as tibble
divvy_trips <- tibble(divvy_trips)

# Convert "date" field as date
divvy_trips$date <- as.Date(divvy_trips$date, format = "%d %m %Y")

# Add four new columns and remove part years
divvy_trips <- divvy_trips %>%
  filter(date >= as.Date("2020-01-01") & date <= as.Date("2023-12-31")) %>%
  # create year
  mutate(year = year(date)) %>%
  # create end of month
  mutate(end_of_month = ceiling_date(date, "month") - days(1)) %>%
  # create week_day (as integer)
  mutate(week_day = weekdays(date, abbreviate = TRUE)
  ) %>%
  mutate(
    week_day = match(week_day, c("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"))
  ) %>%
  # create week_day_type
  mutate(
    week_day_type = case_when(week_day %in% c(1, 7) ~ "weekend", TRUE ~ "weekday")
  ) %>%
  # select only elements of interest
  select(
    rideable_type, hour, usertype, total_records, 
    avg_trip_duration, end_of_month, week_day, week_day_type, year
    )

##########################################################################

# Create a piechart on total records
pc_usertype <- divvy_trips %>%
  group_by(usertype) %>%
  summarise(count = sum(total_records)) %>%
  mutate(percentage = count / sum(count))

# Build a pie chart
percentage <- pc_usertype$percentage
labels <- pc_usertype$usertype
piepercent <- round(100 * percentage, 1)

# Check if the sum of percentages is 100
if (sum(piepercent) != 100) {
  stop("Sum of percentages must be 100.")
}

# Create the pie chart
pie(piepercent, 
    labels = paste(labels, " (", piepercent, "%)"),  # Add labels and percentages
    main = "After 2020: Proportion of riders in total records",
    col = rainbow(length(labels))
)

#########################################################################

# Create pie chart on total records
pie_chart_total_records <- divvy_trips %>%
  group_by(usertype, week_day_type) %>%
  summarise(count = sum(total_records)) %>%
  mutate(percentage = count / sum(count))

# Build the percentages
percentage <- pie_chart_total_records$percentage
labels <- paste(pie_chart_total_records$usertype, pie_chart_total_records$week_day_type)
piepercent <- round(100 * percentage, 1)

# Rename the levels of the factor variable
levels(pie_chart_total_records$week_day_type) <- c("weekday", "weekend")

# Create the pie chart
ggplot(pie_chart_total_records, aes(x = "", y = percentage, fill = week_day_type)) +
  geom_bar(width = 1, stat = "identity") +
  coord_polar("y", start = 0) +
  facet_grid(. ~ usertype) +
  geom_text(aes(label = scales::percent(percentage)), 
            position = position_stack(vjust = 0.5)) +
  theme_void() +
  theme(
    strip.text.x = element_text(size = 12, face = "bold"),
    legend.title = element_blank(),
    legend.position = "bottom"
  ) +
  labs(
    title = "User type distribution by week day type",
    subtitle = "After 2020: Total records",
    caption = 
      "Condering total rides, both rider types prefer weekdays (more so paid subscribers).
      In addition, compared to the previous period (before 2020), customers ride more on weekdays."
  ) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

##########################################################################

pie_chart_average <- divvy_trips %>%
  group_by(usertype, week_day_type) %>%
  summarise(count = mean(avg_trip_duration)) %>%
  mutate(percentage = count / sum(count))

# Build the percentages
percentage <- pie_chart_average$percentage
labels <- paste(pie_chart_average$usertype, pie_chart_average$week_day_type)
piepercent <- round(100 * percentage, 1)

# Rename the levels of the factor variable
levels(pie_chart_average$week_day_type) <- c("weekday", "weekend")

# Create a pie chart
ggplot(pie_chart_average, aes(x = "", y = percentage, fill = week_day_type)) +
  geom_bar(width = 1, stat = "identity") +
  coord_polar("y", start = 0) +
  facet_grid(. ~ usertype) +
  geom_text(aes(label = scales::percent(percentage)), 
            position = position_stack(vjust = 0.5)) +
  theme_void() +
  theme(
    strip.text.x = element_text(size = 12, face = "bold"),
    legend.title = element_blank(),
    legend.position = "bottom"
  ) +
  labs(
    title = "User type distribution by week day type",
    subtitle = "After 2020: Average ride duration in seconds",
    caption = 
      "Not much difference in casual customers.
      Subscribers ride loner in weekends."
  ) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

##########################################################################

# Prepare the tibble for linechart; group the dataframe on end_of_month and usertype
lc_endofmonth <- divvy_trips %>%
  group_by(end_of_month, usertype) %>%
  summarise(count = sum(total_records))

# The linechart with trends
ggplot(data=lc_endofmonth) + 
  geom_line(
    mapping=aes(x=end_of_month, y=count, color=usertype)
  ) +
  geom_smooth(
    mapping=aes(x=end_of_month, y=count, color=usertype),
    # use linear regression to fit the line
    method="lm",
    # don't include a confidence interval
    se=FALSE,
    # solid line
    linetype="solid",
    # line size
    size=1.5
  ) +
  # ensure y axis shows values properly formatted
  scale_y_continuous(n.breaks=10, labels = scales::comma) +
  labs(
    title = 
      "Total rides (total_records) by user types",
    subtitle = 
      "Data from Jan 2020 to Dec 2023 (data aggregated on end_of_month)",
    caption = 
      "Trends show progress in both rider types.",
    color="User type"
  ) + 
  # remove redundant axis labels
  theme(axis.title = element_blank()) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

##########################################################################

lc_year <- divvy_trips %>%
  group_by(end_of_month, usertype) %>%
  summarise(average = mean(avg_trip_duration))

# The linechart with trends
ggplot(data=lc_year) + 
  geom_line(
    mapping=aes(x=end_of_month, y=average, color=usertype)
  ) +
  geom_smooth(
    mapping=aes(x=end_of_month, y=average, color=usertype),
    # use linear regression to fit the line
    method="lm",
    # don't include a confidence interval
    se=FALSE,
    # solid line
    linetype="solid",
    # line size
    size=1.5
  ) +
  # ensure y axis shows values properly formatted
  scale_y_continuous(n.breaks=10, labels = scales::comma) +
  labs(
    title = 
      "Average duration rides (seconds) by user types",
    subtitle= 
      "Data from Jan 2020 to Dec 2023 (data aggregated on end_of_month)",
    caption = 
      "Marked decrease in casual customers.
      Plateau in paid subscribers.",
    color="User type"
  ) + 
  # remove redundant axis labels
  theme(axis.title = element_blank()) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

##########################################################################

# Prepare the tibble for linechart; group the dataframe on end_of_month, weekday_type and usertype
lc_endofmonth_weekdaytype <- divvy_trips %>%
  group_by(end_of_month, week_day_type, usertype) %>%
  summarise(count = sum(total_records))

# Build a linechart to see evolution on rides on usertype and week_day_type
ggplot(data=lc_endofmonth_weekdaytype) + 
  geom_smooth(
    mapping=aes(x=end_of_month, y=count, color=week_day_type), 
    method="gam", formula=y~s(x)
  ) + 
  # remove dual y axis and force it for comparability
  facet_wrap(~usertype, scales = "fixed") + 
  scale_y_continuous(n.breaks=10, labels = scales::comma, limits = c(0, NA)) +
  labs(
    title = 
      "Total rides by user types",
    subtitle = 
      "Data from Jan 2020 to Dec 2023 (data aggregated on years)",
    caption = 
      "It is confirmed that both types of customers prefer to ride on weekdays.",
    color="Weekday type"
  ) + 
  # remove redundant axis labels
  theme(axis.title = element_blank()) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

##########################################################################

# Prepare the tibble for linechart; group the dataframe on end_of_month, weekday_type and usertype
lc_endofmonth_weekdaytype <- divvy_trips %>%
  group_by(end_of_month, week_day_type, usertype) %>%
  summarise(average = mean(avg_trip_duration))

# Build a linechart to see evolution on rides on usertype and week_day_type
ggplot(data=lc_endofmonth_weekdaytype) + 
  geom_smooth(
    mapping=aes(x=end_of_month, y=average, color=week_day_type), 
    method="gam", formula=y~s(x)
  ) + 
  # romove dual y axis and force it for comparability
  facet_wrap(~usertype, scales = "fixed") + 
  scale_y_continuous(n.breaks=10, labels = scales::comma, limits = c(0, NA)) +
  labs(
    title = 
      "Average duration rides (seconds) by user types",
    subtitle = 
      "Data from Jan 2020 to Dec 2023 (data aggregated on years)",
    caption = 
      "Casual customers ride on average more in weekends.
       Paying subscribers are indifferent.
       Notice decrease in 2020, due to COVID.
       The situation is reversed.",
    color="Weekday type"
  ) + 
  # remove redundant axis labels
  theme(axis.title = element_blank()) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

##########################################################################

# Prepare histogram_days
histogram_data_days <- divvy_trips %>%
  group_by(usertype, end_of_month, week_day) %>%
  summarise(count = sum(total_records))

# Convert 'week_day' to factor for categorical x-axis
histogram_data_days$week_day <- factor(histogram_data_days$week_day, levels = 1:7)

# Create histogram
ggplot(histogram_data_days, aes(x = week_day, y = count)) +
  geom_bar(stat = "identity", fill = "steelblue") +
  facet_wrap(~usertype, scales = "fixed") +
  scale_x_discrete(
    breaks = 1:7, 
    labels = c("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
  ) +
  scale_y_continuous(labels = function(x) format(x, scientific = FALSE, big.mark = ",")) +
  labs(
    title = 
      "Histogram of total records by weekday for both type of users",
    subtitle = "After 2020",
    caption = 
      "Paid subscribers: High usage on weekdays.
      Casual customers: High usage on weekends.
      Not significantly different than the previous period (before 2020)."
  ) +
  # remove redundant axis labels
  theme(axis.title = element_blank()) +
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

##########################################################################

# Prepare histogram_hour
histogram_data_hours <- divvy_trips %>%
  group_by(usertype, end_of_month, hour) %>%
  summarise(count = sum(total_records))

# Convert 'hour' to factor for categorical x-axis
histogram_data_hours$hour <- factor(histogram_data_hours$hour, levels = 0:23)

# Create histogram
ggplot(histogram_data_hours, aes(x = hour, y = count)) +
  geom_bar(stat = "identity", fill = "steelblue") +
  facet_wrap(~usertype, scales = "fixed") +
  scale_x_discrete(breaks = 0:23) +
  scale_y_continuous(labels = function(x) format(x, scientific = FALSE, big.mark = ",")) +
  labs(
    title = 
      "Histogram of total records by hour for both type of users",
    subtitle = "After 2020",
    caption = 
      "Paid subscribers: High usage in mornings and late afternoon.
      Casual customers: Higher usage in the afternoons.
      Not significantly different than the previous period (before 2020)."
  ) +
  # remove redundant axis labels
  theme(axis.title = element_blank()) +
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

In conclusion, for the period 1^st Jan 2020 to 31^st Dec 2023:

• Paid subscribers take more rides than casual customers.

• Paid subscribers ride longer on average than casual subscribers.

• When analysed on total rides, both user types show progress.

• But on average duration rides (seconds), casual customers show regress, whereas paid subscribers show stagnation.

Now, let’s concentrate on type of bikes.

# Rideable type analysis - average 

# Prepare the tibble for linechart; group the dataframe on rideable_type, end_of_month and usertype
lc_endofmonth_rideabletypetype <- divvy_trips %>%
  group_by(end_of_month, rideable_type, usertype) %>%
  summarise(average = mean(avg_trip_duration))

# Build a linechart to see evolution on rides on usertype and week_day_type
ggplot(data=lc_endofmonth_rideabletypetype) + 
  geom_line(
    mapping=aes(x=end_of_month, y=average, color=rideable_type)) + 
  # remove dual y axis and force it for comparability
  facet_wrap(~usertype, scales = "fixed") + 
  scale_y_continuous(n.breaks=10, labels = scales::comma, limits = c(0, NA)) +
  labs(
    title = 
      "Average duration rides by type of bikes and user types",
    subtitle = 
      "Data from Jan 2020 to Dec 2023 (data aggregated on end of month)",
    caption = 
      "Indifference in paid subscribers.
       Casual customers prefer docked bikes.Electric bikes not regarded.",
    color="Types of bikes"
  ) + 
  # remove redundant axis labels
  theme(axis.title = element_blank()) + 
  # align caption left hand-side
  theme(plot.caption = element_text(hjust = 0))

Share

To answer Lily Moreno’s question on how do annual members and casual riders use Cyclistic bikes differently:

1. Casual customers prefer riding for pleasure, more in weekends, but it is clear that after 2020, they ride more in weekdays:
   
   
   
   
   

2. Proportion changed - casual customers show more interest in riding their bikes after 2020 than before. Again, casual customers ride more in weekdays than before 2020:

   
   
   
   
   
   
   
   
   

3. Point of concern: reduction in average duration trips for casual customers:
   
   
   
   

   4. Casual customers seem to enjoy riding docket bikes:

   

Act

Despite COVID, casual customers ride more times after 2020, although less in duration. I recommend further analysis to determine the cause. To further understand their preferences, I also recommend that a survey be sent to them.