This project analyzes historical Cyclistic bike-share trip data to compare how casual riders and annual members use the service. The goal is to identify behavioral patterns that could help inform marketing strategies aimed at increasing annual memberships.
How do casual riders and annual members differ in their bike-share usage, and what patterns could support member conversion strategies?
- SQL: data cleaning, transformation, and analysis
- R: analysis support, validation, and summary work
- R Markdown: reporting and project presentation
This project uses a dual workflow in SQL and R. The main cleaning and analysis process was completed in SQL, then reproduced in R to confirm the approach and demonstrate the same analytical logic across two environments.
Location: Analysis/Scripts/SQL/01_cleaning/
This folder contains the quarterly cleaning scripts used to standardize the raw trip data:
Main tasks:
- standardized column names and formats
- cleaned inconsistent or incomplete records
- prepared each quarter for merging
Location: Analysis/Scripts/SQL/02_merge/merge_cleaned_tables.sql
This script combines the cleaned quarterly datasets into a single table for analysis.
Location: Analysis/Scripts/SQL/03_statistical_analysis/
This folder contains SQL queries used to analyze rider behavior:
The analysis focuses on:
- ride duration
- member versus casual usage
- time-of-day behavior
- monthly ride trends
Location: Analysis/Scripts/R/cyclistic_workflow.R
This script supports the analysis by reproducing the workflow in R and summarizing key patterns.
- Casual riders generally take longer rides than annual members.
- Casual rider activity is higher on weekends and during warmer periods.
- Members use the service more consistently on weekdays.
- Usage patterns suggest different purposes for each rider type.
- Technical Report (PDF): Cyclistic Analysis Report v1.0 (hosted as a GitHub Release asset to keep repo size small)
- Executive Summary (Markdown):
Reports/Cyclistic_Markdown.md
- Dataset: Cyclistic Bike-Share Data
- Period covered: 2019 Q1 through 2020 Q1 (15 months of trip data)
The analysis uses publicly available Cyclistic (Divvy) trip data. Raw source files are not included in this repository due to size constraints. The SQL scripts assume the original data has already been loaded into a database environment.
This analysis is based on trip history data only. It does not include demographic information or direct evidence of rider motivation, so interpretations about commuting or leisure behavior should be treated as informed usage patterns rather than confirmed intent.
The analysis, the SQL/R code and the conclusions were done by hand, without AI coding tools.