Skip to content

ThalithaThomas/Cyclistic_Bikeshare_Case_Study

BranchesTags

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

2 Commits
README.md
README.md
 
 

Repository files navigation

Cyclistic Bike Share Analysis 🚴‍♀️

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

📊 Interactive Dashboard

Interactive Tableau Dashboard

View Live Tableau Dashboard

🎯 Project Overview

This case study analyzes how casual riders and annual members use Cyclistic bikes differently. The analysis aims to design marketing strategies to convert casual riders into annual members, driving future growth for the bike-share company.

Note: This is a capstone case study project designed to demonstrate data analysis skills for job applications. The scenario and recommendations are part of a structured learning exercise.

🏢 About Cyclistic

  • Location: Chicago
  • Fleet Size: 5,824 bicycles across 692 stations
  • Specialty: Inclusive bike-share with reclining bikes, hand tricycles, and cargo bikes
  • Usage: 70% leisure, 30% commuting
  • Accessibility: 8% of riders use assistive options

📋 ASK - Define the Business Problem

Business Task

Design marketing strategies to convert casual riders into annual members to drive future growth for Cyclistic.

Key Questions

  • Primary Question: How do annual members and casual riders use Cyclistic bikes differently?
  • Supporting Questions:
    • Why would casual riders buy a membership?
    • How can digital media affect marketing tactics?
    • What factors influence casual riders' decision to become members?

Stakeholders

  • Lily Moreno - Director of Marketing (Primary decision maker)
  • Marketing Analytics Team - Analysis execution and insights
  • Executive Team - Strategic approval and resource allocation

Success Criteria

  • Identify clear behavioral differences between user types
  • Develop actionable marketing recommendations
  • Establish measurable conversion strategies

🔍 PREPARE - Data Collection & Understanding

Data Source

  • Dataset: Cyclistic Divvy bike share trip data
  • Time Period: June 2024 - May 2025 (12 months)
  • Original Size: 5,628,847 records
  • Format: 12 monthly CSV files
  • License: Public dataset available for analysis

Data Structure

Key Variables:

  • ride_id - Unique trip identifier
  • started_at / ended_at - Trip timestamps
  • start_station_name / end_station_name - Station information
  • member_casual - User type (member vs casual)
  • rideable_type - Bike type (classic, electric, docked)
  • start_lat / start_lng - Geographic coordinates
  • end_lat / end_lng - Geographic coordinates

Data Limitations

  • Missing station information for some trips
  • Potential data quality issues with duration calculations
  • No demographic information about users
  • Limited to trip-level data (no user profiles)

Data Credibility (ROCCC)

  • Reliable: Official company data from bike-share system
  • Original: First-party data from Cyclistic operations
  • Comprehensive: 12 months of complete trip records
  • Current: Recent data covering full seasonal cycles
  • Cited: Properly documented and sourced

🧹 PROCESS - Data Cleaning & Preparation

Data Consolidation

SQL Implementation:

-- Step 1: Combine 12 monthly datasets
CREATE OR REPLACE TABLE `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data` AS
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202406-divvy-tripdata`
UNION ALL
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202407-divvy-tripdata`
UNION ALL
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202408-divvy-tripdata`
UNION ALL
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202409-divvy-tripdata`
UNION ALL
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202410-divvy-tripdata`
UNION ALL
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202411-divvy-tripdata`
UNION ALL
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202412-divvy-tripdata`
UNION ALL
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202501-divvy-tripdata`
UNION ALL
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202502-divvy-tripdata`
UNION ALL
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202503-divvy-tripdata`
UNION ALL
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202504-divvy-tripdata`
UNION ALL
SELECT * EXCEPT(ride_id) FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.202505-divvy-tripdata`;

Data Quality Control

SQL Implementation:

-- Step 2: Remove records with critical missing values
DELETE FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
WHERE start_station_name IS NULL
   OR end_station_name IS NULL
   OR member_casual IS NULL
   OR started_at IS NULL
   OR ended_at IS NULL;

Removed 1,669,187 records (29.7%) with critical missing values:

  • Missing start_station_name or end_station_name
  • Missing member_casual classification
  • Missing started_at or ended_at timestamps
  • Invalid or corrupted ride IDs

Feature Engineering

SQL Implementation:

-- Step 3: Extract date and time components
CREATE OR REPLACE TABLE `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data` AS
SELECT 
  *,
  DATE(started_at) AS started_date,
  EXTRACT(HOUR FROM started_at) AS started_hour,
  EXTRACT(DAYOFWEEK FROM started_at) AS started_day_of_week,  -- 1=Sunday, 7=Saturday
  EXTRACT(MONTH FROM started_at) AS started_month,
  DATE(ended_at) AS ended_date,
  EXTRACT(HOUR FROM ended_at) AS ended_hour
FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`;

-- Step 4: Add and calculate trip duration
ALTER TABLE `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
ADD COLUMN trip_duration_minutes INT64;

UPDATE `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
SET trip_duration_minutes = DATETIME_DIFF(ended_at, started_at, MINUTE)
WHERE TRUE;

Created new calculated fields:

  • started_date - Trip start date for temporal analysis
  • started_hour - Hour of day (0-23) for usage patterns
  • started_day_of_week - Day of week (1=Sunday, 7=Saturday)
  • started_month - Month of trip for seasonal analysis
  • trip_duration_minutes - Calculated trip duration

Data Validation

SQL Implementation:

-- Step 5: Check for invalid trip durations
SELECT COUNT(*) as negative_duration_trips
FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
WHERE trip_duration_minutes <= 0;

-- Step 6: Remove invalid durations
DELETE FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
WHERE trip_duration_minutes <= 0;

-- Step 7: Remove trips longer than 24 hours
DELETE FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
WHERE trip_duration_minutes > 1440;

Additional cleaning steps:

  • Removed 24,882 records with invalid durations (≤0 minutes)
  • Removed trips longer than 24 hours (likely data errors or maintenance)
  • Validated geographic coordinates within Chicago area
  • Checked for duplicate trip records

Final Clean Dataset: 3,935,778 records (70.1% retention rate)

Tools Used

  • Google BigQuery - Large-scale data processing and SQL queries
  • SQL - Data cleaning, transformation, and validation
  • Git - Version control for query scripts and documentation

🔍 ANALYZE - Exploratory Data Analysis & Insights

Overall Usage Patterns

SQL Analysis:

-- Overall usage comparison between member types
SELECT 
    member_casual,
    COUNT(*) AS total_trips,
    ROUND(AVG(trip_duration_minutes)) AS avg_duration_minutes,
    CONCAT(ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER()), '%') AS percentage_of_trips  
FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data` 
GROUP BY member_casual
ORDER BY total_trips DESC;

Key Findings:

  • Members: 64% of total trips, 12 minutes average duration
  • Casual Riders: 36% of total trips, 23 minutes average duration
  • Key Insight: Casual riders take longer trips but fewer total trips

Temporal Analysis

Hourly Patterns

SQL Analysis:

-- Usage patterns by time of day
SELECT 
    member_casual,
    CASE 
        WHEN started_hour BETWEEN 0 AND 5 THEN "Night (12AM-5AM)"
        WHEN started_hour BETWEEN 6 AND 11 THEN "Morning (6AM-11AM)"
        WHEN started_hour BETWEEN 12 AND 17 THEN "Afternoon (12PM-5PM)"
        WHEN started_hour BETWEEN 18 AND 23 THEN "Evening (6PM-11PM)"
    END as time_period,
    COUNT(*) as trip_count,
    ROUND(AVG(trip_duration_minutes)) as avg_duration_minutes,
    CONCAT(ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(PARTITION BY member_casual)), '%') as percentage_of_trips
FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
GROUP BY member_casual, time_period
ORDER BY 
    CASE time_period
        WHEN "Morning (6AM-11AM)" THEN 1
        WHEN "Afternoon (12PM-5PM)" THEN 2
        WHEN "Evening (6PM-11PM)" THEN 3
        WHEN "Night (12AM-5AM)" THEN 4
    END;

Key Findings:

  • Members: Peak usage during morning (7-9 AM) and evening (5-7 PM) rush hours
  • Casual Riders: Peak usage during afternoon and evening hours (12-6 PM)
  • Insight: Members show commuter behavior, casual riders show leisure patterns

Daily Patterns

SQL Analysis:

-- Usage patterns by day of week
SELECT 
    member_casual,
    CASE started_day_of_week
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday' 
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END as day_of_week,
    COUNT(*) as trip_count,
    ROUND(AVG(trip_duration_minutes)) as avg_duration_minutes,
    CONCAT(ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(PARTITION BY member_casual)), '%') as percentage_of_trips
FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
GROUP BY member_casual, started_day_of_week
ORDER BY member_casual, started_day_of_week;

Key Findings:

  • Members: 77% of trips on weekdays (Monday-Friday)
  • Casual Riders: 62% of trips on weekends (Saturday-Sunday)
  • Insight: Members use bikes for work commuting, casual riders for recreation

Seasonal Patterns

SQL Analysis:

-- Seasonal usage analysis
SELECT 
    member_casual,
    CASE 
        WHEN started_month IN (12, 1, 2) THEN 'Winter'
        WHEN started_month IN (3, 4, 5) THEN 'Spring'
        WHEN started_month IN (6, 7, 8) THEN 'Summer'
        WHEN started_month IN (9, 10, 11) THEN 'Fall'
    END as season,
    COUNT(*) as trip_count,
    ROUND(AVG(trip_duration_minutes)) as avg_duration_minutes,
    CONCAT(ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(PARTITION BY member_casual)), '%') as percentage_of_trips
FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
GROUP BY member_casual, season
ORDER BY member_casual, 
    CASE season
        WHEN 'Winter' THEN 1
        WHEN 'Spring' THEN 2
        WHEN 'Summer' THEN 3
        WHEN 'Fall' THEN 4
    END;

Key Findings:

  • Members: Winter 11%, Spring 22%, Summer 36%, Fall 31%
  • Casual Riders: Winter 5%, Spring 18%, Summer 46%, Fall 31%
  • Insight: Casual riders more sensitive to weather, showing stronger seasonal variation

Geographic Analysis

SQL Analysis:

-- Top starting stations by member type
SELECT 
    member_casual,
    start_station_name,
    start_lng,
    start_lat,
    COUNT(*) as trip_count
FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
GROUP BY member_casual, start_station_name, start_lng, start_lat
QUALIFY ROW_NUMBER() OVER(PARTITION BY member_casual ORDER BY COUNT(*) DESC) <= 10
ORDER BY member_casual, trip_count DESC;

-- Top ending stations by member type  
SELECT 
    member_casual,
    end_station_name,
    end_lng,
    end_lat,
    COUNT(*) as trip_count
FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
GROUP BY member_casual, end_station_name, end_lng, end_lat
QUALIFY ROW_NUMBER() OVER(PARTITION BY member_casual ORDER BY COUNT(*) DESC) <= 10
ORDER BY member_casual, trip_count DESC;

Key Findings:

  • Members: Concentrated around business districts and residential areas
  • Casual Riders: Concentrated around tourist attractions, parks, and recreational areas
  • Insight: Usage patterns reflect different trip purposes

Bike Type Preferences

SQL Analysis:

-- Bike type preferences by member type
SELECT 
  member_casual,
  rideable_type,
  COUNT(*) as trip_count,
  CONCAT(ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(PARTITION BY member_casual)), '%') as pct_within_member_type
FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
GROUP BY member_casual, rideable_type
ORDER BY member_casual, trip_count DESC;

Key Findings:

  • Classic Bikes: Members 61%, Casual 61% (similar preference)
  • Electric Bikes: Members 38%, Casual 37% (similar preference)
  • Docked Bikes: Members 1%, Casual 2% (minimal usage)
  • Insight: Bike type preference not a differentiating factor

Advanced Analysis

Rush Hour Behavior

SQL Analysis:

-- Commuter pattern analysis (Rush hour usage)
SELECT 
  member_casual,
  CASE 
    WHEN started_hour BETWEEN 7 AND 9 THEN 'Morning Rush (7-9 AM)'
    WHEN started_hour BETWEEN 17 AND 19 THEN 'Evening Rush (5-7 PM)'
    ELSE 'Non-Rush Hours'
  END as time_period,
  COUNT(*) as trip_count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(PARTITION BY member_casual), 2) as pct_of_member_trips
FROM `cyclistic-bike-analysis-464315.cyclistic_data_2024_2025.combined_cyclistic_data`
GROUP BY member_casual, 
  CASE 
    WHEN started_hour BETWEEN 7 AND 9 THEN 'Morning Rush (7-9 AM)'
    WHEN started_hour BETWEEN 17 AND 19 THEN 'Evening Rush (5-7 PM)'
    ELSE 'Non-Rush Hours'
  END
ORDER BY member_casual, time_period;

Key Findings:

  • Morning Rush (7-9 AM): 85% members, 15% casual riders
  • Evening Rush (5-7 PM): 78% members, 22% casual riders
  • Non-rush hours: More balanced usage between user types

Duration-Based Insights

Key Findings:

  • Short trips (<10 min): Primarily members (commuter efficiency)
  • Medium trips (10-30 min): Mixed usage
  • Long trips (>30 min): Primarily casual riders (leisure exploration)

📊 SHARE - Data Visualization & Communication

Interactive Tableau Dashboard

View Live Tableau Dashboard

Key Visualizations:

  • User demographics and bike type analysis
  • Temporal usage patterns (hourly, daily, seasonal)
  • Geographic distribution and station popularity
  • Behavioral insights and trip duration analysis

Features:

  • Interactive filtering and drill-down capabilities
  • Comparative analysis between user types
  • Mobile-responsive design

🎯 ACT - Strategic Recommendations

Key Recommendations

  1. Weekend Leisure Targeting: Launch weekend-focused membership campaigns
  2. Seasonal Conversion: Time promotions with summer peak usage
  3. Geographic Targeting: Deploy location-based marketing at tourist stations
  4. Duration-Based Incentives: Create membership tiers for longer rides

Implementation Strategy

  • Phase 1: Foundation & tracking setup
  • Phase 2: Campaign launch & location-based advertising
  • Phase 3: Performance optimization & scaling

Success Metrics

  • Target 5% improvement in casual-to-member conversion rate
  • 25% increase in weekend membership sign-ups
  • 3:1 ROI on seasonal campaigns

🛠️ Technologies Used

  • Data Processing: Google BigQuery, SQL
  • Visualization: Tableau Public
  • Data Volume: 5.6M+ records processed
  • Analysis Period: 12 months of bike-share data

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors