“The Sunday Explorer vs. The Monday Commuter:” Closing the Membership Gap

Author

Miguel Santos

1 Executive Summary

Analysis of 5.4 million Chicago’s city bike trips reveals a sharp divide: Members use Cyclistic as a consistent mobility solution for weekday commuting (avg. 12 mins.), while Casuals act as “Explorers”, taking leisure trips along the lakeside on weekends (avg. 20 mins.).
While Members provide stable revenue, Casuals represent a high-engagement segment that currently views the service as a luxury rather than a daily necessity.

To increase memberships and sustainable revenue streams, our marketing recommendations are built on 2 key ideas:

  • The “Casual” Mindset Shift:

This idea consists of shifting the marketing narrative from “weekend recreation” to “weekday mobility solution”. The campaign positions biking as a faster, cheaper, and better lifestyle choice for daily commuting in Chicago. Riders benefit from predictable travel times by avoiding peak-hour traffic, lower transportation costs with no gas or maintenance, and meaningful lifestyle advantages—including reduced environmental impact, mental decompression, and built-in daily exercise. Together, these benefits reframe biking from a recreational option into a smart, sustainable choice for everyday urban mobility.

  • The “Explorer” Pass:

This is a proposed five-month membership (May-September) designed to engage Chicago’s summer “Explorers”. By creating a membership with a value proposition aligned with “The Explorer” needs, which encourages consistent ridership, enables direct communication, and generates valuable behavioral insights, we are creating a stronger pathway to convert seasonal users into annual members over the long-term.

Together, The “Casual” Mindset Shift and “The Explorer Pass” form a cohesive strategy to unlock growth. By reframing biking as a reliable, cost-effective, and lifestyle-enhancing mobility solution, and pairing that message with a seasonally tailored membership, we meet riders where they are and guide them toward long-term value.
The message will be amplified through trusted local digital influencers, radio, and podcasts, this approach builds relevance, normalizes daily use, and creates a scalable pathway to grow memberships and sustainable revenue.

2 The Business Task

The primary objective is to drive long-term revenue growth by converting casual riders into annual members.
Financial modeling has identified annual members as the most profitable customer segment.
This analysis aims to identify the specific behavioral triggers that can be leveraged in a targeted marketing campaign to maximize conversion rates.

2.1 Key Questions

To achieve this objective, the analysis will answer three main questions:

  • How do annual members and casual riders use Cyclistic bikes differently?
  • What value propositions would convert casual riders into annual members?
  • How can Cyclistic use digital media to increase this conversion rate?

3 Data Methodology & Cleaning

Dataset made available using this license link.

Click here for the full data cleaning steps and scripts using BigQuery.

In order to analyse riders behavior, we gathered the most recent data (Jan-Nov of 2025) from Divvy (Chicago’s public bike-share system), provided in this link.
We then proceeded to combine all 11 individual monthly tables into a single one “divyy_2025_raw”, using BigQuery.

The raw combined table “divyy_2025_raw” has 5,412,460 rows and 13 columns, which is a very large dataset, hence why we chose BigQuery for the data cleaning process, and then exported the final table to RStudio for visualizations and reporting.

In BigQuery, we added some new columns to expand our potential for quality insights:

  • Ride Duration (ride_length): Quantifies user engagement to identify the primary utility of the service (e.g., commuting vs. leisure).
  • Duration Segmentation (ride_length_group): Categorizes trip patterns to identify high-value user segments and optimize bike turnover rates.
  • Temporal Distribution (day_of_week): Maps peak activity periods to align marketing efforts with high-traffic days.
  • Hourly Demand (hour_of_day): Isolates specific peak-usage windows to differentiate between professional commuter cycles and leisure trips.

The final table “divyy_2025_cleaned” has the following schema:

Table 1: Data Schema and Variable Descriptions
Field Name Data Type Constraint Description
ride_id STRING NULLABLE Primary key for each unique trip
rideable_type STRING NULLABLE Bike category (Classic, Electric)
started_at TIMESTAMP NULLABLE Trip commencement timestamp
ended_at TIMESTAMP NULLABLE Trip conclusion timestamp
start_station_name STRING NULLABLE Name of origin station
start_station_id STRING NULLABLE Unique ID of origin station
end_station_name STRING NULLABLE Name of destination station
end_station_id STRING NULLABLE Unique ID of destination station
start_lat FLOAT NULLABLE Latitude coordinate of origin
start_lng FLOAT NULLABLE Longitude coordinate of origin
end_lat FLOAT NULLABLE Latitude coordinate of destination
end_lng FLOAT NULLABLE Longitude coordinate of destination
member_casual STRING NULLABLE Classification of user (Member or Casual)
ride_length FLOAT NULLABLE Calculated trip duration (minutes)
ride_length_group STRING NULLABLE Categorical grouping of ride durations
day_of_week INTEGER NULLABLE The specific day the trip occurred (1 - 7, Sunday - Saturday)
hour_of_day INTEGER NULLABLE The integer hour portion of (started_at) timestamp, ranging from 0 (12:00 AM) to 23 (11:00 PM).

3.1 Events worth mentioning during the Data Cleaning Process

3.1.1 nulls:

We found:
start_station_name -> 1,157,747 NULLS (21.4% of total)
start_station_id -> 1,157,747 NULLS (21.4% of total)
end_station_name -> 1,213,260 NULLS (22.4% of total)
end_station_id -> 1,213,260 NULLS (22.4% of total)
end_lat -> 5,413 NULLS (0.01% of total)
end_lng -> 5,413 NULLS (0.01% of total)

Action:
Keep the nulls, there is a lot of useful data to be extracted from other fields.

3.1.2 ride_length

3.1.2.1 Negative ride_length values

We found:

29 trips with negative values (an impossibility), all during 2025-11-02, between 1:00 am and 2:00 am. In the United States, on the first Sunday of November (2025-11-02), at 2:00 am the clock jumps back to 1:00 am (Daylight Saving Time), generating a situation where a trip can start at 1:55 am and end at 2:03 am, but it will show as ending at 1:03 am.

Action:

Keep the 29 rows, adding 1 hour back where ended_at < started_at on 2025-11-02.

3.1.2.2 Oddly short ride_length values (< 1min)

We found:

143,411 trips with ride_length < 1:00 minute (likely false starts and other issues).
Possible explanations: * False Starts: A user unlocks a bike, realizes the seat is broken or a tire is flat, and docks it immediately. * Station Issues: A user tries to dock a bike, but the dock doesn’t “catch” correctly, so they try again 30 seconds later. * User Error: A user changes their mind or accidentally triggers a rental via the app.

Action:

Removed the 143,411 rows, since none of these are meant to be “real” trips.

3.1.2.3 Strange long trips (> 6 hours)

We found:

5,423 rows with trips over 6 hours, with start_station_name but no end_station_name.
Possible explanation: Long trips with start_station_name but no end_station_name are likely “Lost,” “Stolen,” or “Abandoned” bike scenarios.

Action:

Deleted the 5,423 rows, since they are likely not representative of real trips.

5 Recommendations for the Marketing Campaign

In order to better tailor our recommendations, we summarized the profile for each rider on the table below.

“Table 2: The Explorer vs. ”The Commuter – A profile of ridership patterns.”

Given the fact that these two profiles (Commuter vs. Explorer) are totally different, except for the fact that they both prefer electric bikes, our marketing recommendations are built upon these 2 keys pillars:

  • The “Casual” Mindset Shift

  • The “Explorer” Membership

5.1 The “Casual” Mindset Shift

5.1.1 The Setup:

The data clearly shows that Casual Riders aren’t just “unregistered members”, they use bikes with a different purpose entirely. Our core business is to provide an accessible mobility solution, 64% of our trips (3,374,869) are currently made by members and they already see the value on that daily value proposition.
We believe our marketing campaign should focus on shifting, not only our current casual members perception of the bikes from a weekend luxury to a weekday solution, but also a big part of the commuting population of Chicago.

5.1.2 The Target:

Casual Riders are the main target but we see value in including the whole of Chicago’s city daily commuting population.

5.1.3 The Idea:

The idea is to show the daily commuters of Chicago the benefits of using bikes for their daily mobility needs, such as:

  • Faster: By being able to avoid peak-hour traffic congestion, making travel time more predictable;

  • Cheaper: By highlighting the no-maintenance, no gas and low cost transportation;

  • Lifestyle: By highlighting the low environmental carbon footprint, mental decompression and added exercise as a bonus, in an ever increasing sedentary and screen-centric world.

5.1.4 The Goal:

The objective is to drive a large-scale modal shift in Chicago’s transit landscape by converting the daily commuting population into a recurring annual membership base.
By positioning Cyclistic as the city’s primary professional mobility solution, we aim to capture a dominant share of the daily transit market and secure long-term revenue growth.

5.2 The “Explorer” Pass

5.2.1 The Setup:

Besides highlighting the benefits of bike’s everyday usage and bringing casual riders (and other commuters) closer to the available membership value proposition, we also suggest creating a new membership, aligned with “The Explorer” profile. The data shows a significant winter trough for casual riders, a full year commitment may be the primary deterrent for this group.

5.2.2 The Target:

Casual Riders and other potential “Summer City Explorers”.

5.2.3 The Idea:

A membership designed specifically around the needs and usage patterns of this segment. By engaging this audience over a five-month peak season (May-September), we can normalize recurring use, leverage valuable insights and build direct communication, creating a strong foundation to convert Summer season users (members) to an annual plan down the line.

Core Value Proposition:

  • Flexibility: No rigid peak-hour rules, extended ride times.
  • Longer trips: Longer ride windows before overage fees.
  • Leisure timing: Optimized for weekends & afternoons.
  • Summer-focused: Five-month peak season.

5.2.4 The Goal:

“The Explorer Pass” aims to capture high-volume seasonal demand by removing the friction of a year-long commitment, which currently acts as a barrier for casual riders.
By providing a tailored, five-month entry point, Cyclistic can formalize the “Explorer” habit, gain direct access to user data for targeted retention, and create a structured pathway to convert seasonal users into loyal annual members.

5.3 Media Channels:

5.3.1 Traditional

  • Lakeside / Landmark outdoor locations – Hot spots: DuSable Lake Shore Dr & Monroe St and Navy Pier stations.

5.3.2 Digital

Influencer Marketing:

  • @chicago.by.ren – Focuses on discovering hyper-local neighborhood gems, community events, and the daily experience of living like a true Chicago “local”.

  • @bored_in_chicago – Dedicated to unearthing unique city curiosities, transit hacks, and budget-friendly ways to navigate and enjoy Chicago without a car.

  • @meganhomme – Provides a polished, professional aesthetic focused on urban lifestyle, “day-in-the-life” office routines, and efficient travel tips for the young professional.

Radio Stations:

  • WBBM Newsradio: The #1 choice for Chicago commuters. It is an all-news format that drivers tune into specifically for transit updates every ten minutes. An ad here during the 8:00 AM or 5:00 PM block reaches the exact person frustrated for being stuck in traffic.

  • WBEZ Chicago: The local NPR affiliate. Listeners are highly engaged with programs like Morning Edition and All Things Considered.

  • 101.9 THE MIX (WTMX): A top-rated music and variety station. Its morning show is a staple for a broad demographic of Chicago office workers who want entertainment while idling in traffic.

Podcasts:

  • The Block Club Chicago Podcast: High-trust, hyper-local reporting.

  • Curious City (WBEZ): A “cult favorite” that explores the mysteries and infrastructure of Chicago. Listeners are often curious about urban living and city hacks.

  • In the Loop (WBEZ): A daily news podcast that “unpacks the headlines” for Chicagoans. It’s designed specifically for the morning commute duration (~20-30 minutes).

6 Appendix: SQL Queries

In this section, we included the SQL code used to join, clean and aggregate the Divyy 2025 dataset before importing it into RStudio.

We combined all 2025 months (Jan-Nov) into one single table called “divyy_2025_raw” for EDA using BigQuery (Google Cloud Storage buckets) Bucket Screenshot

6.1 SQL Code

-- 1. CREATING TABLE & COLUMNS --

-- Creating the new staged table --

CREATE OR REPLACE TABLE `capstone-483020.divyy_tripdata.divyy_2025_staged`
AS SELECT *
FROM `capstone-483020.divyy_tripdata.divyy_2025_raw`;

-- Creating the new cleaned table, data is already in the right format --

CREATE OR REPLACE TABLE `capstone-483020.divyy_tripdata.divyy_2025_cleaned` AS
SELECT *
FROM `capstone-483020.divyy_tripdata.divyy_2025_staged`;

-- Adding ride_length column --

ALTER TABLE `capstone-483020.divyy_tripdata.divyy_2025_cleaned`
ADD COLUMN ride_length FLOAT64;

-- Populating ride_length column --

UPDATE `capstone-483020.divyy_tripdata.divyy_2025_cleaned`
SET ride_length = CAST(TIMESTAMP_DIFF(ended_at, started_at, SECOND) AS FLOAT64) / 60 
WHERE TRUE;

-- Adding ride_length_group column --

ALTER TABLE `capstone-483020.divyy_tripdata.divyy_2025_cleaned`
ADD COLUMN ride_length_group STRING;

UPDATE `capstone-483020.divyy_tripdata.divyy_2025_cleaned`
SET ride_length_group =
  CASE
    WHEN ride_length < 6 THEN "Short Commute"
    WHEN ride_length >= 6 AND ride_length < 30 THEN "Standard Trip"
    WHEN ride_length >= 30 AND ride_length < 120 THEN "Extended Ride"
  ELSE "Day Trip" 
  END 
WHERE TRUE;

-- Adding day_of_week column --

ALTER TABLE `capstone-483020.divyy_tripdata.divyy_2025_cleaned`
ADD COLUMN day_of_week INT64;

-- Populating day_of_week column --

UPDATE `capstone-483020.divyy_tripdata.divyy_2025_cleaned`
SET day_of_week = EXTRACT(DAYOFWEEK FROM started_at)
WHERE TRUE;

-- Adding hour_of_day column --

ALTER TABLE `capstone-483020.divyy_tripdata.divyy_2025_cleaned`
ADD COLUMN hour_of_day INT64;

-- Populating hour_of_day column --

UPDATE `capstone-483020.divyy_tripdata.divyy_2025_cleaned`\
SET hour_of_day = EXTRACT(HOUR FROM started_at)\
WHERE TRUE;

-- 2. EDA & DATA CLEANING --

-- Exploring ride_id duplicates --

SELECT ride_id, COUNT(*) as duplicate_count
FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned`
GROUP BY ride_id
HAVING COUNT(*) > 1;

-- Exploring null values --

SELECT   
COUNTIF(ride_id IS NULL) AS null_ride_ids,
COUNTIF(rideable_type IS NULL) AS null_rideable_type,
COUNTIF(started_at IS NULL) AS null_started_at,
COUNTIF(ended_at IS NULL) AS null_ended_at,
COUNTIF(start_station_name IS NULL) AS null_start_station_name,
COUNTIF(start_station_id IS NULL) AS null_start_station_id,
COUNTIF(end_station_name IS NULL) AS null_end_station_name,
COUNTIF(end_station_id IS NULL) AS null_end_station_id,
COUNTIF(start_lat IS NULL) AS null_start_lat,
COUNTIF(start_lng IS NULL) AS null_start_lng,
COUNTIF(end_lat IS NULL) AS null_end_lat,
COUNTIF(end_lng IS NULL) AS null_end_lng,
COUNTIF(member_casual IS NULL) AS null_member_casual

FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned`;

-- Exploring the date fields --

SELECT   
MIN(started_at) as min_started, 
MAX(started_at) as max_started, 
MIN(ended_at) as min_ended, 
MAX(ended_at) as max_ended

FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned`;

-- Exploring the rideable_type field --

SELECT rideable_type, COUNT(*) as num_trips

FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned`\
GROUP BY rideable_type;

-- Exploring the member_casual field --

SELECT member_casual, COUNT(*) as num_trips

FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned` 
GROUP BY member_casual;

-- Exploring ride_length --

SELECT 
  MIN(ride_length) as min_ride_length, 
  AVG(ride_length) as avg_ride_length, 
  MAX(ride_length) as min_ride_length 
FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned`;

-- Exploring ride_length negative values --

SELECT 
  started_at, 
  ended_at, 
  ride_length   
FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned` 
WHERE ride_length < 0;

-- Adding 1 hour back to the 29 trips with negative ride_length --

UPDATE `capstone-483020.divyy_tripdata.divyy_2025_cleaned` 
SET ended_at = TIMESTAMP_ADD(ended_at, INTERVAL 1 HOUR) 
WHERE ended_at < started_at;

-- Re-run ride_length script to update values --

UPDATE `capstone-483020.divyy_tripdata.divyy_2025_cleaned` 
SET ride_length = TIMESTAMP_DIFF(ended_at, started_at, SECOND)/60 
WHERE TRUE;

-- Exploring ride_length oddly short trips --

SELECT * 
FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned` 
WHERE ride_length < 1;

-- Creating a cleaned table where ride_length > 1:00 minute --

DELETE FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned`   
WHERE ride_length < 1;

-- Checking the new ride_length MIN, AVG, MAX --

SELECT 
  MIN(ride_length) as min_ride_length, 
  AVG(ride_length) as avg_ride_length, 
  MAX(ride_length) as max_ride_length

FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned`;

-- Exploring ride_length extremely high values --

SELECT * 
FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned` 
WHERE ride_length >= 480 AND end_station_name IS NULL;

-- Removing ride_length trips over 6 hours and empty end_station_name --

DELETE FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned` 
WHERE ride_length >= 480 AND end_station_name IS NULL;

-- Exploring ride_length_group --

SELECT 
  ride_length_group, 
  COUNT(*) AS total_rides, 
  MIN(ride_length) AS min_in_group, 
  MAX(ride_length) AS max_in_group

FROM `capstone-483020.divyy_tripdata.divyy_2025_cleaned` 
GROUP BY ride_length_group 
ORDER BY min_in_group;