How Can a Bike Rental Company Pedal Its Way to Quick Success?

Introduction

This report is part of the capstone project for the Google Data Analytics Professional Certificate. For this case study, I analyzed a dataset provided during the course using the following tools:

• Microsoft Excel
• PostgreSQL
• Tableau Public
• RMarkdown

The data is available under a public license.

Context

The dataset captures bike ride information from users of a bike-sharing app in Chicago during 2021. Originally sourced from Divvy, the data has been adapted for a fictional company called Cyclistic. Here's a quick overview of the dataset:

• Publicly available
• Up-to-date
• Free of personal user information

Goal of the Analysis

The main goal was to identify how casual users differ from subscription members in their riding behavior. The bigger picture? Convert more casual riders into long-term subscribers. Based on the data insights, this report proposes practical recommendations to achieve that.

Data Wrangling

To get started, I imported the monthly CSV files into Excel to check for completeness and missing values in the key columns.

Next, I calculated average and maximum ride durations by user type. That's when I stumbled upon some... epic outliers. For example, in February —typically the slowest month— casual riders had an average ride time of 50 minutes and 45 seconds. But two users appeared to be on a tour-de-force ride that lasted nearly 21 days. Either they forgot to end their ride, encountered a software glitch, or went on the slowest sightseeing trip ever.

Excluding these outliers, the average February ride duration drops to 44 minutes and 9 seconds.

I chose not to remove these long-haul rides for three main reasons:

1. Even without them, the differences in ride duration among casual users are relatively small.
2. Without a clear explanation, these might just be part of the normal range of user behavior.
3. In the summer, outliers become so common that it's hard to define what's normal anymore, even when calculating standard deviations.

To get a clearer picture, I focused on average ride durations and number of rides per month and weekday by user type. I used PostgreSQL for data aggregation and Tableau Public for visualization.

SQL Query Example:

SELECT
  member_casual,
  AVG(ended_at - started_at) AS avg_ride_length,
  COUNT(*) AS num_of_rides,
  TO_CHAR(started_at, 'Month') AS "month",
  TO_CHAR(started_at, 'Day') AS "day"
FROM
  data_2021 d
GROUP BY
  member_casual,
  TO_CHAR(started_at, 'Month'),
  TO_CHAR(started_at, 'Day');

Analysis Results

Key findings from the data:

1. Weekend Warriors vs. Weekday Commuters
   • Casual users ride more on weekends.
   • Members ride more during weekdays.

2. Casual Riders Take Their Time
   • Casual users have longer average ride durations every day of the week.

3. Sunshine = Saddle Time
   • Both groups prefer biking in the warmer months.

These insights suggest that casual riders are more likely to use the app for leisure, while members use it for more practical reasons—like commuting.

Missing Data

A deeper analysis would benefit from additional fields —most notably, a unique user ID. This would help identify casual users who ride like members (e.g., frequent, short weekday rides) and might be ripe for subscription offers.

Recommendations

Based on the findings, here's how Cyclistic can turn one-time riders into loyal members:

1. Weekend-Only Subscription Plan
   Offer a discounted subscription valid from Friday to Sunday —perfect for leisure riders.

2. Tourism Meets Tech
   Since casual riders use the app for fun, integrate tourism-friendly features like suggested routes, scavenger hunts, and sightseeing tips.

3. Seasonal and Targeted Ads
   Run ad campaigns in spring, targeting users Friday through Sunday, right when leisure use peaks.

4. Spring Freebie Promo
   Launch a free one-month trial in March or April to hook users just before peak biking season.

Conclusion

Cyclistic has a solid base of casual users. However, many of them don't benefit from the current all-year subscription model. By designing a plan tailored to their habits —focused on weekends and warmer months— the company could turn fleeting users into loyal customers. In other words, with the right pedal-powered strategy, casual riders might just stick around for the long ride.