Case Study: How Does a Bike-Share Navigate Speedy Success?
ABOUT THE COMPANY
In 2016, Cyclistic launched a successful bike-share offering. Since then, the program has grown to a fleet of 5,824 bicycles that are geotracked and locked into a network of 692 stations across Chicago. The bikes can be unlocked from one station and returned to any other station in the system anytime.
Until now, Cyclistic’s marketing strategy relied on building general awareness and appealing to broad consumer segments. One approach that helped make these things possible was the flexibility of its pricing plans: single-ride passes, full-day passes, and annual memberships. Customers who purchase single-ride or full-day passes are referred to as casual riders. Customers who purchase annual memberships are Cyclistic members.
Cyclistic’s finance analysts have concluded that annual members are much more profitable than casual riders. Although the pricing flexibility helps Cyclistic attract more customers, Moreno believes that maximizing the number of annual members will be key to future growth. Rather than creating a marketing campaign that targets all-new customers, Moreno believes there is a very good chance to convert casual riders into members. She notes that casual riders are already aware of the Cyclistic program and have chosen Cyclistic for their mobility needs.
BUSINESS TASK: How do annual members and casual riders use Cyclistic bikes differently?
STAKEHOLDERS
Lily Moreno: The director of marketing and your manager.
Cyclistic marketing analytics team: A team of data analysts who are responsible for collecting, analyzing, and reporting data that helps guide Cyclistic marketing strategy.
Cyclistic executive team: The detail-oriented executive team
DATA SOURCES USED
Cyclistic’s historical trip data will be analyzed to identify trends. The data was made available by Motivate International Inc. 2019 data was downloaded and it was stored by quarters i.e. 2019_Q1, 2019_Q2, 2019_Q3, and 2019_Q4
PROCESS OF ANALYSIS USING MS SQL
The structure of the data in use
A database named 'portfolio' was created and the data to be used was imported into MS SQL server . Temporary table was created to house the combination of the 4 quarters data to be brought in.
Data was inserted into the Temp table.
How many user types do we have?
How many subscribers do we have?
How many customers do we have?
How many total bike riders do we have?
What is the average trip duration of the user type in minutes?
What is the monthly average trip duration?
What is the monthly average trip duration of customers in minutes?
What is the monthly average trip duration of subscribers in minutes?
Creating a Temp table for a subset of the data
How many customers rode a bike during the week?
How many subscribers rode a bike during the week?
Creating a Temp table for a subset of the data
What is the age range of the user type?
What is the average age of the user type?
How many customers rides on a monthly basis?
How many subscribers rides on a monthly basis?