Cyclistic Bike-Share

As a data analyst, I want to understand how casual riders and annual members use bikes differently. From these insights, the team will design a new marketing strategy to convert casual riders into annual members.

I used Microsoft SQL Server and Microsoft Power BI to clean, transform, and analyze the data in this project.

Scenario

Cyclistic: A bike-share program with over 6,000 bicycles and 600 docking stations in Chicago. The bikes can be unlocked from one station and returned to any other station in the system. 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.

Bike Types: Cyclistic offers three types of bikes.

1. Classic Bike

2. Electric Bike (Original and Next-gen)

3. Docked (Scooters)

The marketing director believes the company’s future success depends on maximizing the number of annual memberships. Therefore, it is important to understand how casual riders and annual members use Cyclistic bikes differently. From these insights, the team will design a new marketing strategy to convert casual riders into annual members.

The recommendations must be backed up with compelling data insights and professional data visualizations.

Ride Pricing

Ride Type	Single Ride	Day Pass	Membership
	$1 + $0.17/min	$16.50/day	$130.90/year
Classic Bike	$1 unlock + $0.17/min	3 hours free, then $0.17/min	45 min free, then $0.17/min
Docked (Scooter)	$1 unlock + $0.42/min	Free unlocks + $0.42/min	Free unlokcs + $0.27/min
E-Bike	$1 unlock + $0.42/min	Free unlocks + $0.42/min	Free unlocks + $0.17/min

Data

Cyclistic is a fictional company; however, the data is from a real bike-sharing company, Divvy, in Chicago.

Historical trip data is available for public use.

This data is provided according to the Divvy Data License Agreement and released monthly.

Each trip is anonymized and includes:

Trip start day and time
Trip end day and time
Trip start station
Trip end station
Rider type (Member, Single Ride, and Day Pass)

The data has been processed to remove trips taken by staff as they service and inspect the system; and any trips below 60 seconds in length (potentially false starts or users trying to re-dock a bike to ensure it was secure).

The data about the identity of the customers and financial transactions is removed from the public dataset.

The data is available in zip files. To understand recent trends I decided to use the trip data from the year 2020 onwards.

After downloading all zip files, I extracted them in separate folders per year. There is one file for each month but sometimes we have it quarterly or mixed, as you can see for the year 2020.

Microsoft SQL Server

I created a database in Microsoft SQL Server and imported each CSV file into its separate table. Once all data is imported for a year, then I merged them into one table for that year. This way I have the data for the whole year available in one table.

Once I had all data imported from the year 2020 till October 2023, I then again merged it all into one table. The total number of rows reach 16 million.

Cyclistic Database

Import Flat File

Insert data

Cyclistic Database

1/6

Microsoft Power BI - Desktop

Click on the below image to view it enlarged.

Trip Data - Cleaned

Table with all records from the year 2020 till October 2023 is imported in Power BI. Unwanted columns are removed. Rows with blank values in 'ride_id' column are also removed.
Calculated columns such as 'trip duration', 'weekday', 'Hour', 'Season' added to learn patterns.

Proportions

The information we get from these charts are:

1. Out of total rides, 59% are by the members and 41% are by the casual users. This shows that casual users are almost half of the total users. The contribution to profits from members is higher than the casual users. The more casual users become members the more beneficial it will be for the overall profits.

2. Classic bikes are most favorite, 42% followed by electric bikes which are 38% of the total rides. It is good for the company because classic bikes are cheaper to purchase and maintain as well.

3. We can see that members are using more classic bikes and then electric bikes. On the other hand, casual user's favourite is electric bike, but classic bike's usage is almost equal to electric bike.

Microsoft Power BI - Desktop

The charts below show the comparison between the usage of casual riders and members.

The top chart shows the number of rides and the below chart shows the trip duration.

Click on the image to view it enlarged with insights.

Rides by Users - Annual Trend

The first thing which is obvious is that casual users ride more than the members, but members are riding for longer period.

Probably this is why users decided to become members because it is economically viable. It seems they need to ride every day due to the nature of their work.

Casual users, on the other hand riding it either for leisure or fitness.

Rides by Users - Seasonal Trends

The effect of the weather becomes evident if we compare the seasons.

In Chicago, The Spring months are March, April and May.
The Summer months are June, July and August.
The Autum months are September, October and November.
The Winter months are December, January and February.

The winter months are obviously least productive. Spring and then summer months are most busy.

Cyclistic should offer a special package during autumn months to encourage riders to use the bikes especially during daytime to cover the losses in winter months.

Rides by Users - Week Days Trend

When we further drill down to the week days it becomes clear that the members are using the bikes mostly during the working days and less during the weekends.

Casual users are riding less during the working days but mostly during the weekends and we can see sudden increase from Friday onwards.

It is obvious that the members are using the rides for their office/work related requirements and casual users are for leisure only.

The duration of the trips for members remains same throughout the week.

However, the casual users are riding for longer period during weekend starting from Friday onwards.

Rides by Users - Hourly Trend

At hourly level we see an interesting pattern.

The members are using the rides mostly during the office hours; there is a jump during office starting hours and then again during office closing hours.

The Casual users are riding throughout the day, gradually increasing until it reaches at maximum at evening time.

The duration of rides, for members are mostly after the office starting hours (seems like for job related) and then it reaches max level during closing hours.

For casual riders, the trip duration is mostly in the afternoon and evening hours.

Microsoft Power BI - Desktop

Click on the below image to view it enlarged.

Yasir Niaz

Cyclistic Bike-Share

Scenario

Ride Pricing

Data

Microsoft SQL Server

Microsoft Power BI - Desktop

Microsoft Power BI - Desktop

Microsoft Power BI - Desktop