Cyclistic Bike-Share (Data Analytics Capstone)
My another capstone project for the Google Data Analytics Professional Certificate. This case study serves as a culmination of the program, applying end-to-end data analysis to solve a real-world business challenge using the six-phase methodology: Ask, Prepare, Process, Analyze, Share, and Act.
Stepping into the role of a Data Analyst on Cyclistic’s marketing team, my primary objective was to analyze customer usage patterns to understand how annual subscribers and casual riders (single-ride and daily pass users) interact with the service differently. These insights directly inform a targeted marketing strategy designed to convert casual riders into long-term annual members.
About the Company
Cyclistic is a premier bike-share program featuring a fleet of over 5,800 bicycles and 600 docking stations. Cyclistic sets itself apart through inclusivity, offering not only standard two-wheeled bikes but also reclining bikes, hand tricycles, and cargo bikes to accommodate riders with disabilities and varying physical needs.
Business Task
Analyze historical ride data to uncover how annual members and casual riders utilize Cyclistic bikes differently. The ultimate goal is to deliver data-driven insights that inform targeted marketing strategies aimed at converting casual riders into long-term annual subscribers.
Key Stakeholders
✓ Lily Moreno (Director of Marketing): Responsible for leading marketing initiatives and campaign development across email, social media, and digital channels.
✓ MarketingAnalytics Team: A dedicated team of analysts responsible for gathering, processing, analyzing, and presenting data to guide overall marketing strategy.
✓ Executive Team: A detail-oriented leadership team responsible for reviewing analysis results and approving final marketing proposals.
Data Integrity & Credibility
Cyclistic is a fictional entity created for this case study. The underlying data is authentic public bike-share data made available by Motivate International Inc. under an open-data license agreement. The datasets are appropriate, timely, and scrubbed of personally identifiable information (PII) to ensure user privacy and maintain data integrity.
Data Cleaning and Analysis in SQL
Here are the steps that I followed during this phase:
✓ Combined all the tables into one data table
✓ Check for null and duplicates
✓ Cleaned the data
✓ Additional columns and data transformation
✓ Extract data for analysis
Data Cleaning Process
Tables represting 12 .csv files have been uploaded to the tripdata_2025 dataset. The following SQL query is implemented to combine data from all the tables into a single table called 'all_tripdata'
To check the number of null values in each column, the following SQL query is run.
CREATE TABLE IF NOT EXISTS
`tripdata_2025.all_tripdata` AS
(SELECT *
FROM `tripdata_2025.tripdata_2025_01`
UNION ALL
SELECT *
FROM `tripdata_2025.tripdata_2025_02`
UNION ALL
SELECT * FROM `tripdata_2025.tripdata_2025_03`
UNION ALL
SELECT * FROM `tripdata_2025.tripdata_2025_04`
UNION ALL
SELECT * FROM `tripdata_2025.tripdata_2025_05`
UNION ALL
SELECT * FROM `tripdata_2025.tripdata_2025_06`
UNION ALL
SELECT * FROM `tripdata_2025.tripdata_2025_07`
UNION ALL
SELECT * FROM `tripdata_2025.tripdata_2025_08`
UNION ALL
SELECT * FROM `tripdata_2025.tripdata_2025_09`
UNION ALL
SELECT * FROM `tripdata_2025.tripdata_2025_10`
UNION ALL
SELECT * FROM `tripdata_2025.tripdata_2025_11`
UNION ALL
SELECT * FROM `tripdata_2025.tripdata_2025_12`
)
SELECT
COUNT(*) - COUNT(ride_id) AS missing_ride_id,
COUNT(*) - COUNT(rideable_type) AS missing_rideable_type,
COUNT(*) - COUNT(started_at) AS missing_started_at,
COUNT(*) - COUNT(ended_at) AS missing_ended_at,
COUNT(*) - COUNT(start_station_name) AS missing_start_station_name,
COUNT(*) - COUNT(end_station_name) AS missing_end_station_name,
COUNT(*) - COUNT(start_station_id) AS missing_start_station_id,
COUNT(*) - COUNT(end_station_id) AS missing_end_station_id,
COUNT(*) - COUNT(start_lat) AS missing_start_lat,
COUNT(*) - COUNT(end_lat) AS missing_end_lat,
COUNT(*) - COUNT(start_lng) AS missing_start_lng,
COUNT(*) - COUNT(end_lng) AS missing_end_lng,
COUNT(*) - COUNT(member_casual) AS missing_member_casual
FROM
`tripdata_2025.all_tripdata`
From this, we saw that there are null values in start_station_name, end_station_name, end_station_id, start_station_id, end_lat and end_lng. So to clean the data, we run the following SQL query and create a new 'cleaned_tripdata' table.
CREATE TABLE IF NOT EXISTS
`tripdata_2025. cleaned_tripdata` AS
(SELECT *
FROM `tripdata_2025.all_tripdata`
WHERE start_station_name IS NOT NULL
AND end_station_name IS NOT NULL
AND end_station_id IS NOT NULL
AND start_station_id IS NOT NULL
AND end_lat IS NOT NULL
AND end_lng IS NOT NULL)
We need to check how many rides are for more than 1 day ~ 24 hours.
SELECT
COUNT(*) AS trips_more_than_24_hours
FROM
`tripdata_2025. cleaned_tripdata`
WHERE
TIMESTAMP_DIFF (ended_at, started_at, HOUR) >= 24
Total 133 trips are equal or more than 24 hours. We also need to check how many rides are less than 1 minute ~ 60 seconds.
SELECT
COUNT(*) AS trips_less_than_1_minute
FROM `tripdata_2025. cleaned_tripdata`
WHERE
TIMESTAMP_DIFF(ended_at, started_at, SECOND) <= 60
Total 88381 trips are less than 1 minutes. To analyze the data with more proper data insights, we need to delete these data rows.
CREATE TABLE IF NOT EXISTS
`tripdata_2025. final_tripdata` AS
(SELECT *
FROM `tripdata_2025. cleaned_tripdata`
WHERE TIMESTAMP_DIFF(ended_at, started_at, HOUR) < 24
AND TIMESTAMP_DIFF (ended_at, started_at, SECOND) > 60)
Data Analysis
Number of trips per rider type
SELECT
member_casual,
COUNT(*) AS num_of_trips
FROM
`tripdata_2025.final_tripdata`
GROUP BY
member_casual
ORDER BY
member_casual
Number of trips per bike type per rider type
SELECT
member_casual,
rideable_type,
COUNT(*) AS num_of_trips
FROM
`tripdata_2025.final_tripdata`
GROUP BY
rideable_type, member_casual
ORDER BY
member_casual, num_of_trips
Average ride length per rider type
SELECT
member_casual,
AVG(TIMESTAMP_DIFF(ended_at, started_at, MINUTE)) AS avg_ride_time
FROM
`tripdata_2025.final_tripdata`
GROUP BY
member_casual
Average ride length per month per rider type
WITH cleaned AS
(SELECT *,
EXTRACT(MONTH FROM started_at) AS month
FROM `tripdata_2025.cleaned_tripdata`)
SELECT
member_casual, month,
AVG(TIMESTAMP_DIFF(ended_at, started_at, MINUTE)) AS avg_ride_time
FROM
cleaned
GROUP BY
member_casual, month
ORDER BY
member_casual, month
Average ride length per hour per rider type
WITH cleaned AS
(SELECT *,
EXTRACT(HOUR FROM started_at) AS hour
FROM `tripdata_2025.cleaned_tripdata`)
SELECT
member_casual, hour,
AVG(TIMESTAMP_DIFF(ended_at, started_at, MINUTE)) AS avg_ride_time
FROM
cleaned
GROUP BY
member_casual, hour
ORDER BY
member_casual, hour
No. of trips per month per rider type
WITH cleaned AS
(SELECT *,
EXTRACT(MONTH FROM started_at) AS month
FROM `tripdata_2025.cleaned_tripdata`)
SELECT
member_casual, month,
COUNT(*) AS num_of_trips
FROM
cleaned
GROUP BY
member_casual, month
ORDER BY
member_casual, month
No. of trips per day per rider type
WITH cleaned AS
(SELECT *,
EXTRACT(DAYOFWEEK FROM started_at) AS day
FROM
`tripdata_2025.cleaned_tripdata`)
SELECT
member_casual, day,
COUNT(*) AS num_of_trips
FROM
cleaned
GROUP BY
member_casual, day
ORDER BY
member_casual, day
No. of trips per hour per rider type
WITH cleaned AS
(SELECT *,
EXTRACT(HOUR FROM started_at) AS hour
FROM
`tripdata_2025.cleaned_tripdata`)
SELECT
member_casual, hour,
COUNT(*) AS num_of_trips
FROM
cleaned
GROUP BY
member_casual, hour
ORDER BY
member_casual, hour
Most popular start station per rider type
SELECT
member_casual, start_station_name,
AVG(start_lat) AS avg_start_lat,
AVG(start_lng) AS avg_start_lng,
COUNT(*) AS num_of_trips
FROM `tripdata_2025.cleaned_tripdata`
GROUP BY
member_casual, start_station_name
ORDER BY
member_casual, num_of_trips DESC
Most popular end station per rider type
SELECT
member_casual, end_station_name,
AVG(end_lat) AS avg_end_lat,
AVG(end_lng) AS avg_end_lng,
COUNT(*) AS num_of_trips
FROM
`tripdata_2025.cleaned_tripdata`
GROUP BY
member_casual, end_station_name
ORDER BY
member_casual, num_of_trips DESC
Tableau Dashboard for visualization
See this dashboard live : Click here
Key Conclusions
✓ Annual members make up 64.53% of total trips, providing a strong, reliable core of recurring ridership.
✓ Members primarily use the service for daily commuting, evidenced by higher weekday trip counts and shorter ride durations (~12 minutes). Casual riders use the service for leisure, showing strong weekend activity and longer ride times (~23 minutes).
✓ Overall ridership surges significantly during the summer months (June–August) due to favorable weather, with casual rider volume showing the highest sensitivity to seasonal changes.
Recommendations
✓ Launch digital and email marketing campaigns specifically geared toward casual riders, emphasizing the cost savings, speed, and convenience of an annual subscription over single-ride passes.
✓ Place physical and digital advertisements at top casual start stations—particularly those near coastal areas, parks, and tourist hotspots, featuring scan-to-subscribe QR codes for instant sign-ups.
✓ Capitalize on peak summer traffic by offering limited-time conversion promotions (e.g., discounted first-year annual rates or "Summer Special" member perks) when casual usage is highest.
✓ Promote the reliability and availability of classic bikes, the top choice for both user groups, by offering exclusive member perks, such as primary access or rewards points for frequent commuters.