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

This project is a capstone project from Google data analytics course.

Cycle Rental Service. Source: Google

Introduction

Imagine being a part of a dynamic team at Cyclistic, a prominent bike-share company in Chicago, as a junior data analyst. Your team’s primary focus is to maximize the number of annual memberships, which the director of marketing believes is vital for the company’s future success. To achieve this, your team aims to gain insights into how casual riders and annual members utilize Cyclistic bikes differently. Armed with these valuable insights, you will collaborate to design a new marketing strategy aimed at converting casual riders into annual members. However, to win the executives’ approval, your recommendations must be supported by compelling data insights and professional data visualizations.

What is Cyclistic?

Cyclistic launched its successful bike-share program in 2016 and has since expanded its fleet to 5,824 bicycles across 692 stations throughout Chicago. The bikes are equipped with geotracking capabilities and can be unlocked from one station and returned to any other station within the system. Cyclistic’s marketing strategy has so far focused on building general awareness and appealing to broad consumer segments. The company offers pricing plans that include single-ride passes, full-day passes, and annual memberships. Customers who purchase single-ride or full-day passes are categorized as casual riders, while those who opt for annual memberships become Cyclistic members. The finance analysts at Cyclistic have identified that annual members are significantly more profitable than casual riders. With this knowledge, the company aims to convert casual riders into annual members to drive future growth.

What needs to be Figured Out?

In this portfolio project, the dataset provided by Google serves as the foundation for answering a crucial question: How do annual members and casual riders utilize Cyclistic bikes differently? By exploring and analyzing this data, we can uncover meaningful insights that will inform the creation of targeted marketing strategies.

The Project Workflow

To complete this project, I followed a structured workflow that leveraged various tools and techniques:

1. Data Cleaning in Excel: Initially, I used Excel to clean the dataset and prepare it for further analysis.
2. Data Loading to SQL: After cleaning the data, I loaded it into a SQL database to conduct the analysis.
3. Analysis and Findings: Using SQL queries, I performed insightful analysis on the dataset, extracting key findings that shed light on the differences between annual members and casual riders.
4. Exporting Results to Excel: To present the findings effectively, I exported the results from SQL back to Excel for data visualization purposes.

SQL Code Snippets

Throughout the project, I utilized SQL queries to extract valuable information from the Cyclistic dataset. Here are some examples of the SQL code used:

-----Combining all the months into one table
--WITH yeartable AS(
--SELECT * FROM January
--UNION ALL
--SELECT * FROM February
--UNION ALL
--SELECT * FROM March
--UNION ALL
--SELECT * FROM April
--UNION ALL
--SELECT * FROM May
--UNION ALL
--SELECT * FROM June
--UNION ALL
--SELECT * FROM July
--UNION ALL
--SELECT * FROM August
--UNION ALL
--SELECT * FROM September
--UNION ALL
--SELECT * FROM October
--UNION ALL
--SELECT * FROM November
--UNION ALL
--SELECT * FROM December)

--------printing the whole table
--SELECT * FROM yeartable
--ORDER BY start_date;

------ count of casual and member riders
--SELECT member_casual, COUNT(*) as count
--FROM yeartable
--GROUP BY member_casual;

------- count of electric, classic and docked bike
--SELECT rideable_type, 
--  COUNT(CASE WHEN member_casual = 'member' THEN 1 END) AS members,
--  COUNT(CASE WHEN member_casual = 'casual' THEN 1 END) AS casual
--FROM yeartable
--WHERE member_casual IN ('member', 'casual')
--GROUP BY rideable_type;

------daily riders of casual and member
--SELECT CONVERT(varchar, start_date)AS dates,
--  COUNT(CASE WHEN member_casual = 'member' THEN 1 END) AS members,
--  COUNT(CASE WHEN member_casual = 'casual' THEN 1 END) AS casual
--FROM yeartable
--WHERE member_casual IN ('member', 'casual') 
--GROUP BY start_date;


------weekly riders of casual and member
--SELECT day_of_the_week,
--       COUNT(CASE WHEN member_casual = 'member' THEN 1 END) AS members,
--       COUNT(CASE WHEN member_casual = 'casual' THEN 1 END) AS casual
--FROM yeartable
--WHERE member_casual IN ('member', 'casual')
--GROUP BY day_of_the_week
--ORDER BY day_of_the_week;

-------monthly riders of casual and member 
--SELECT end_month,
--       COUNT(CASE WHEN member_casual = 'member' THEN 1 END) AS members,
--       COUNT(CASE WHEN member_casual = 'casual' THEN 1 END) AS casual
--FROM yeartable
--WHERE member_casual IN ('member', 'casual')
--GROUP BY end_month
--ORDER BY end_month;

--SELECT end_month, COUNT(member_casual)
--FROM yeartable
--GROUP BY end_month;

------average ride time
--SELECT 
--    MONTH(start_date) AS Month,
--    AVG(CASE WHEN member_casual = 'member' THEN DATEDIFF(minute, started_at_timestamp, ended_at_timestamp) END) AS Member_Avg_Ride_Length,
--    AVG(CASE WHEN member_casual = 'casual' THEN DATEDIFF(minute, started_at_timestamp, ended_at_timestamp) END) AS Casual_Avg_Ride_Length
--FROM yeartable
--WHERE member_casual IN ('member', 'casual')
--GROUP BY MONTH(start_date)
--ORDER BY MONTH(start_date);

-- Count the total number of rides taken by each user type on each day of the week and hour of the day
--SELECT member_casual, MONTH(start_date) AS months, start_time, COUNT(*) AS [Total Rides]
--FROM yeartable
--GROUP BY member_casual, start_date, start_time
--ORDER BY member_casual, start_date, start_time;

My Findings

Upon conducting the analysis of the Cyclistic bike trip data, I uncovered several key insights:

1. Member riders significantly outnumber casual riders, highlighting the importance of targeting this user segment for conversion into annual members.
2. Classic bikes are the preferred choice of riders, surpassing electric and docked bikes in popularity.
3. Thursday emerges as the busiest day of the week in terms of bike usage.
4. Members tend to spend more time per session compared to casual riders, suggesting a deeper level of engagement with the service.
5. August stands out as the busiest month in 2022, indicating a potential opportunity to capitalize on the higher demand during this period.

These findings lay the groundwork for developing data-driven marketing strategies that focus on converting casual riders into loyal annual members. By understanding the preferences and behaviors of each user segment, Cyclistic can tailor its messaging and incentives to effectively target casual riders and motivate them to become dedicated members.

In conclusion, the Cyclistic Bike Share Analysis case study demonstrates the power of data analytics in providing actionable insights for enhancing marketing strategies. Through thorough analysis and visualization of the Cyclistic dataset, we can pave the way for increased annual memberships and sustained growth for the company.