🚲 Cyclistic Bike-Share Analysis

Google Data Analytics Capstone Project

---

📌 Project Overview

This project is part of the Google Data Analytics Professional Certificate Capstone.

The objective is to analyze Cyclistic (Divvy) bike-share data and identify behavioral differences between:

• Casual riders
• Annual members

The goal is to generate actionable insights to help convert casual riders into annual members.

---

📊 Dataset

• Dataset Used: July 2020 Divvy Trip Data
• Download Link:
  https://divvy-tripdata.s3.amazonaws.com/202007-divvy-tripdata.zip

⚠️ Due to file size limitations, the raw dataset is not included in this repository.

---

🛠 Tools & Technologies

• Google Sheets – Data cleaning, Pivot Tables, Visualization
• BigQuery (SQL) – Aggregation and query-based analysis
• Python (Pandas) – Data preprocessing and validation

---

📂 Project Structure

Cyclistic-project/
│
├── sql/
│   └── cyclistic_queries.sql
│
├── python/
│   └── python.ipynb
│
├── visuals/
│   ├── avg_ride_duration_by_user_type.png
│   ├── ride_count_by_weekday.png
│   └── time_of_day_usage.png
│
└── README.md

---

🧹 Data Cleaning Steps

• Removed null values
• Removed records where ride_length = 0
• Converted ride duration into proper time format
• Created derived columns:
  • Day of Week
  • Time of Day (Morning, Afternoon, Evening, Night)

Cleaned dataset and pivot tables available here:
👉 [https://docs.google.com/spreadsheets/d/1YhSgkbri5ox_12BcBMrfc4_Cm1w04lXFLUrugLcJh9s/edit?usp=sharing]

---

📈 Key Insights

1️⃣ Average Ride Duration

• Casual riders ride significantly longer (~1 hour)
• Members ride shorter trips (~18 minutes)
• Indicates leisure-oriented behavior among casual riders

---

2️⃣ Ride Count by Weekday

• Casual riders are more active on weekends
• Members show consistent weekday usage
• Suggests members primarily use bikes for commuting

---

3️⃣ Time of Day Usage

• Members peak in morning and evening hours
• Casual riders peak in afternoon and evening
• Further supports commuting vs leisure behavior pattern

---

🎯 Business Recommendations

• Introduce weekend membership conversion offers
• Offer targeted discounts after long-duration casual rides
• Run marketing campaigns during peak leisure hours

---

🔁 Reproducibility Instructions

To reproduce this project:

1. Download dataset from the link above
2. Place CSV file inside a data/ folder
3. Execute SQL queries in BigQuery
4. Run python.ipynb for preprocessing and validation

---

📌 Author

Shivam Kumar
Aspiring Data Analyst | SQL | Power BI | Python | Excel