Cyclistic Bike-Share Case Study
Using Google Sheets
Project Summary:
This case study was provided by the Google Professional Certificates in Data Analytics program to be utilized as a portfolio project. The Cyclistic Bike-Share is a fictional bicycle company, and all data provided is fictional and licensed to be shared. Cyclistic is looking to increase profits by turning casual bike riders (who do not have memberships), into membership riders. The marketing team needs help to decide how to increase membership conversions. In this case study, I used Google Sheets to analyze data collected from the month of January to inform the marketing team of the casual vs. membership rider trends by creating pivot tables and visual charts.
Purpose/ Business Task:
Using data collected in January 2022 for the fictional company of Cyclistic Bike-Share, analyze trends in casual and membership riders to identify how they use the program differently. Use this data to create three solutions for turning casual riders in members for the marketing team.
Process:
Ask:
After reviewing the case study, I established the business task outlined above.
Prepare:
This data is credible and licensed to use by a third party company. Data was extracted and stored in my Google Drive for ease of access. The data was organized by trip ID, seconds of travel, start and stop stations, and if the rider was casual or a member. Before beginning the cleaning process, I sorted the data by ride length in ascending order.
Process:
Data Cleaning Changelog:
Data was imported to Google Sheets.
Data was checked for duplicates (no duplicates were found).
Additional attribute, “ride_length”, was created to determine duration of bike rides.
Additional feature, “day_of_week”, was created to determine which day of the week the ride began.
All columns were checked for proper formatting.
Column “member_casual” was filtered to ensure only member or casual were inputted.
Attribute “ride_length” was filtered to only show rides longer than 50 seconds and less than 25 hours.
No deletions or other additions were made to the data. The original raw data remains unchanged and can be accessed at any time.
Analyze/ Share:
Using the cleaned data, I created pivot tables and visual charts to analyze. Tables were created for the attributes of ride length, number of riders, bicycle preference, and riders per day of the week. View the pivot tables below:
Summary of Analysis:
Number of Rides by Casual Riders in January: 18505, 17.8% of Rides
Number of Rides by Member Riders in January: 85250, 82.2% of Rides
Casual riders were the only riders to use docked bicycles.
Casual riders rode electric bikes more often, while member riders rode classic bikes more often.
Casual riders preferred to ride more on Saturdays, while Member riders rode more often on Mondays, Tuesday, and Thursdays and rode less on the weekends.
Casual riders had a higher average ride length time than members.
Act:
Top recommendations to convert Casual riders to Members based on my findings:
Casual riders prefer to use bikes on Saturdays, potentially as a leisure activity, while Members use bikes more often during the week, potentially for work commutes. Advertise to Casual riders the benefits of using Cyclistic as their daily mode of transportation during the week rather than just a leisure activity.
Members tend to ride for shorter periods of time more frequently, while casual riders tend to ride for longer periods of time less frequently. The data observed for casual riders could be a result of wanting to get the most value for their money each time they pay for a ride. Advertise to Casual riders the benefits of becoming a Member rider to have unlimited access to bikes to shorten any commute and save more money than if they continue to pay for individual rides.
Member riders preferred to use classic bikes while Casual riders preferred to use electric bikes. Advertise to Casual riders the health benefits of using a classic bike as not only a way to commute, but also another form of exercise, and that a Cyclistic membership for both commuting and working out would be cheaper than a car and gym membership.