1. Scenerio

• Launched in 2016, Cyclistic has expanded to 5,824 geotracked bikes across 692 Chicago stations, offering flexible point-to-point rentals. Its pricing includes single-ride/day passes (casual riders) and annual memberships (members).
• Finance data reveals annual members are significantly more profitable than casual riders. While flexible pricing attracts users, Marketing Director Lily Moreno sees greater potential in converting existing casual riders—who already use and trust Cyclistic—into members.
• As a Junior Data Analyst on Cyclistic’s marketing team, your mission is to uncover behavioral differences between casual riders and annual members of this Chicago-based bike-share program. With leadership prioritizing membership growth, your data-driven insights will directly shape conversion strategies—but executive approval hinges on rigorous analysis and persuasive visual storytelling.

Business Task

Analyze usage patterns between annual members and casual riders to identify key behavioral differences that inform conversion strategies.

2. Data Sources

• The analysis leverages 13 months of Cyclistic's ride-share data (April 2024-April 2025), comprising over 5.8 million trips across 692 Chicago stations.
• This dataset, made available through Motivate International Inc.'s public data program, under this license , stored in standardized CSV formats for analysis.

Data includes:

• ride_id
• rideable_type: classic, docked, electric
• started_at: trip start day and time
• ended_at: trip end day and time
• start_station_name
• start_station_id
• end_station_name
• end_station_id
• start_lat : start station latitude
• start_lng : start station longitude
• end_lat : end station latitude
• end_lng : end station longitude

3. Data Cleaning & Preparation

Upload files to Google BigQuery

• To efficiently analyze the large dataset (approximately 6 million records), I implemented a scalable solution using Google BigQuery and SQL for data cleaning, manipulation, and analysis.
• Due to file size constraints (some exceeding 100MB), I first preprocessed the data by dividing these files into smaller segments using Python, as shown in the script below.

Union all tables

• After uploading all files to Google BigQuery, I consolidated the datasets using UNION ALL to enable comprehensive analysis.
• The following query merges all source tables into a temporary table combine_tables using UNION ALL:

Add columns

• To analyze trip durations, I derived a new ride_duration_in_mins metric by computing the time difference between ended_at and started_at timestamps, converted to minutes.
• The new wk_days column (1-7 representing Sunday-Saturday) revealed meaningful usage patterns across different weekdays.
• Finally, I created a quarter field to enable seasonal trend analysis.
• Save result into a temporary table name add_extra_col

Data Cleaning

• I used case-when to transform wk_days from number (1-7) to (Sun-Sat)
• Check and filter null value
• Save result into a temporary table name clean_table