Cyclistic Bikeshare Analysis — Google Capstone Project

Introduction

Cyclistic is a successful bike-share company based in Chicago. They launched in 2016, and since then, the program has grown to a fleet of over 5800 bicycles that are geotracked and locked into a network of more than 600 docking stations across the city.

Business Goal: The company’s current goal is to design marketing strategies aimed at converting casual riders into annual members. To achieve that, the marketing analyst team needs to better understand how annual members and casual riders differ, why casual riders would buy a membership, and how digital media could affect their marketing tactics.

Business Task: I will be assuming the position of ‘Jr. Data Analyst’, and to help guide the future marketing program, the director of marketing has asked for insights on the following question: “How do annual members and casual riders use Cyclistic bikes differently?”

The resulting findings will help draw up strategies for achieving the business goal.

Data Collection

To answer this question, the historical bike trip data for all 12 months in 2022 was retrieved from this source. The data, collected by Cyclistic company and licensed by Lyft Bikes and Scooters, LLC., contains all the relevant information necessary for this analysis, including customer type and start and end time for each trip. It is separated by month and stored in CSV format.

Data Cleaning and Processing

Microsoft Excel

The cleaning process started with exploring the monthly data for each month using Microsoft Excel. This tool made it easy to get an overview and a general understanding of the data. I performed some data-cleaning procedures in Excel before exporting the data for processing.

After executing these data cleaning processes, the data for each month was exported in .csv format for further processing in SQL.

SQL

For the next data processing stage, my tool of choice was SQL (specifically MySQL), as this tool allows for managing large amounts of data, which can be challenging with Excel. Extra data cleaning steps were carried out before proceeding to analyze the data. Please check here for the full SQL code.

Data Analysis

After the data cleaning and processing phase, I analyzed the data and visualized the key findings using Tableau. With the help of this tool, the data was efficiently aggregated and visualized to help identify key trends and relationships from the data. These visualizations are represented in a comprehensive Tableau dashboard, which can be accessed here. Some of the findings are outlined below:

Annual Members vs. Casual Riders

The cleaned and processed data provided valuable insights into the usage patterns of annual members and casual riders. In the 12 months of 2022, a total of about 4.4 million rides were recorded. Cyclistic members accounted for about 2.61 million rides, which is 60% of all the rides recorded in 2022. Casual riders recorded about 1.76 million rides, amounting to about 40% of the total rides. Overall, cyclistic members engaged in more rides than casual riders.

Average Ride Length By User Type

The average ride duration for all the rides taken in 2022 was about 17 minutes. Further analysis was carried out to determine the average ride by user type.

Cyclistic members rode for an average of 12.45 minutes in the entire year. On the other hand, casual riders had an average of 23.75 minutes of bike activity, almost twice the average length of cyclistic members. Despite the lower number of rides by casual riders (40% of total rides), they had significantly longer average ride durations compared to annual members.

Ride Distribution by Weekday

Overall, Saturday was the busiest day of the week, with a total of about 700 thousand rides recorded. However, when analyzed by user type, the data tells different stories for casual riders and Cyclistic members.

Cyclistic members took more rides during the weekdays and had fewer rides on the weekends. The chart above shows that cyclistic members’ usage peaked in the middle of the week (Tuesday, Wednesday, and Thursday), and significantly fewer rides were recorded on the weekend.

In contrast, casual riders took more rides on the weekends, with Saturday clearly topping the charts. Saturday and Sunday recorded over 300 thousand casual rides, followed by a sharp drop on Monday and even less on Tuesday.

Average Ride Length By User Type

The data analysis revealed that annual members and casual riders tend to have longer rides on Sundays and Saturdays. However, ride durations decrease during the middle of the week before increasing again on Fridays. This creates a u-shape trend in ride durations for both groups, although it is more pronounced for casual riders who rode almost twice as long on each given day.

Ride Distribution by Month

The busiest month of the year was July, while January had the least amount of rides. In general, summer months (June, July, and August) accounted for the most bike-share activity, with about 43% of all rides, while winter months (December, January, and February) recorded the least number of rides, with just about 7% of the total rides.

The number of rides increased as the winter months gave way to the spring season and peaked in the summer before declining again in the fall. This trend was fairly consistent when rides were analyzed by user type.

Summary & Conclusion

• Annual members accounted for 60% of the total rides in 2022, while casual riders comprised 40%.
• Despite the fewer rides, casual riders had significantly longer average (about 2x) ride durations than annual members.
• Analyzing ride frequency by weekday revealed interesting patterns, with higher ride volumes occurring on weekdays compared to weekends for cyclistic members and vice versa for casual riders.
• The summer months recorded the highest number of rides, and the winter season had the lowest number.

Business Recommendations

The insights obtained from the data analysis phase can help inform marketing strategies to target and convert casual riders into annual members, as well as optimize resources and tailor promotions for more effective results:

• Casual riders could be encouraged to become cyclistic members by offering first-time discounts for new members and extra discounts on the weekends, as casual riders tend to ride more on weekends.
• Since both casual riders and annual members take more rides on the weekend, special summer packages can be made available cyclistic members. This might encourage casual riders to consider a membership to benefit from the offer.
• From the analysis report, casual riders generally engage in longer rides. Cyclistic can introduce member discounts for longer rides.

Thanks for Reading!

Check out the Tableau dashboard here.
Connect on LinkedIn | Tableau | GitHub | Kaggle