High-Volume Behavioral Trend Mapping: Large-Scale SQL Analysis in Google BigQuery
Project Name
Cyclistic_2024
Problem
Limited understanding of behavioral differences between casual riders and members.
Objective
To assist the marketing team in developing a campaign that converts casual riders (non-subscribers) into Cyclistic members (subscribers).
Data Collection
Big Query public database
Part 1
Yearly
Seasonal
Monthly
Method used
Part 2
Weekday
Daily Timeframe
Results
Members ride 76% more frequently than casual riders, while casual riders’ average ride length is 75% longer than members.
In colder months, members take 400% more rides than casual riders; in warmer months, the gap decreases to 37.4%.
The average ride length difference is 18% in January but increases to 77% in May, with casual riders consistently riding longer.
​
The ride frequency gap between members and casual riders is 133% on weekdays, but narrows to 15% on weekends.
​
Overall patterns indicate that casual riders are primarily leisure-oriented, whereas members tend to use bicycles for utilitarian purposes (e.g., commuting).
​
Tools
SQL - Excel
Project Slides
