Cyclistic Bike-Hire

Data Analysis

Assets:

SQL Code │ Tableau Visualisation

Cyclistic — The Background:

Cyclistic is a bike-share company and challenger brand based in Chicago.

After launching in 2016, Cyclistic’s offering 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 others within the network.

The model operates similarly to other bike hiring initiatives throughout the world, such as Santander Cycles in London, UK, and Valenbisi in Valencia, Spain.

Cyclistic sets itself apart by offering reclining bikes, hand tricycles, and cargo bikes. This makes bike-share more inclusive to people with disabilities and riders who can’t use a standard two-wheeled bike.

Cyclistic users are more likely to ride for leisure, but about 30% use the bikes to commute to work each day.

Until now, Cyclistic’s marketing strategy has relied heavily on building general awareness about the company’s brand and appealing to broad consumer segments.

One approach that helped make this possible was the flexibility of pricing plans, which include single-ride passes, full-day passes, and annual memberships.

Customers

Cyclistic have two main types of customers who use their bike hire network on a regular basis.

• Customers that purchase single-ride or full-day passes are referred to as casual riders.
• Customers who purchase annual bike hire memberships are Cyclistic members.

Cyclistic’s Working Hypothesis

Cyclistic’s finance analysts have concluded that annual members are much more profitable than casual riders.

Although the pricing flexibility helps the company to attract more customers, Lily Moreno, Director of Marketing, Cyclistic, believes that maximising the number of annual members will be key to commercial growth in the future.

Rather than creating another marketing campaign that targets all types of customers, Moreno believes there is a solid opportunity to devise a streamlined campaign to convert casual riders into paid-up annual members.

This is seen as ‘low-hanging-fruit’ for the company because casual riders are already aware of the Cyclistic program, the key benefits, and many have already chosen Cyclistic for their unique mobility requirements.

Ask

Lily Moreno and the Cyclistic marketing team are eager to kick-on and devise a new marketing campaign to convert the casual riders across on to annual memberships.

But before this can happen, the company needs to better understand how casual riders and annual members use Cyclistic bikes differently.

Cyclistic’s detail-oriented executive team will eventually decide whether to approve the new marketing campaign based on the outcomes of this analysis.

That’s where I was approached to conduct in-depth data analysis to answer the following key business questions:

1. How do annual members and casual riders use Cyclistic bikes differently?
2. Why would casual riders buy Cyclistic annual memberships?
3. How can Cyclistic use digital media to influence casual riders to become members?

Prepare:

After understanding the various business questions and the asks from my stakeholders, the next stage of the process was to prepare the raw data.

I accessed Cyclistic’s historical trip data from the past 12 months (Feb 2024 – Jan 2025) and downloaded it from a cloud storage server.

The data consisted of 12 separate CSV files. Each file held one month’s trip data. The CSV files were very large and contained 5,867,348 rows of bike trip data collectively.

The data was made available to me by Motivate International Inc. under license.

I saved and stored these 12 CSV files locally using an appropriate file naming convention and folder structure.

Process:

Cleaning the data proved to be the most challenging stage of the project.

That’s because the Cyclistic annual dataset was enormous!

Across the 12 monthly CSV files, prior to data cleansing and transformation, there were 5,867,348 original rows of bike trip data.

My plan had been to quickly clean each of the monthly CSV files using Google Sheets or Excel, before joining the monthly files together into a single annual table using R or SQL.

But, with some files containing well over 800K rows of data, I decided against using Google Sheets or Microsoft Excel due to memory and performance concerns. Excel has a maximum limit of 1,048,576 rows that can be displayed within a sheet.

I needed something more powerful!

I decided to change direction and use RStudio to cleanse the data and make the table joins. But because I was using Posit Cloud’s free subscription package, this meant that the huge dataset again became a problem, especially when it came to uploading, accessing, and working with the files.

My stakeholders suggested using just a quarter of bike trip data from the previous year, while using R, but I felt it was important to get the full year picture to answer my business questions.

So, I switched tactics for a third time and eventually used SQL for processing via Microsoft SQL Server and SSMS (SQL Server Management Studio 20).

To clean and transform the Cyclistic data, prior to analysis, I performed the following steps:

• Created a database in SSMS called Cyclistic.
• Loaded in my 12 monthly CSV files as individual tables.
• Generated a common data schema to work with.
• Queried each of the 12 tables for NULL values and deleted any rows where NULLS were present.
• Renamed some of the original table columns into more user-friendly alternatives.
• Created and populated six new columns to assist with the analysis process, including:
  • ride_length
  • day_of_the_week
  • time_of_day
  • season_of_year
  • month_of_year
  • hour_of_day
• Queried ride lengths containing negative values (i.e., end_time greater than the start_time) and deleted these from the tables.
• Trimmed the string columns in my tables to remove any extra spaces at the beginning or end of the strings.
• Queried ride lengths under a minute (37,509 trips). This could be where customers re-docked their bikes and abandoned their rides. Deleted these rows from the dataset.
• Queried rides over three hours to understand the amount of rows affected (11,625). This could be where customers experienced difficulties returning their bikes, or didn’t adhere to the bike hire rules and regulations. These rows were retained.
• There were 12,819 rows of data that had a duplicate trip_id, which could have been due to bike users not docking their bikes correctly at the start or end of their journey. These rows were retained for the purposes of the analysis.

During processing, I deleted a total of 1,696,192 rows (or 28.91% of the dataset) which included the rows with NULL values and rides below one minute.

Removing 1,696,192 rows from the original dataset of 5,867,348, left 4,171,156 rows of data to analyse across the entire year.

The SQL code from my project can be viewed here.

Analyse

There were 4,171,156 rides made throughout the year (Feb 2024 – January 2025).

• 1,508,197 rides (36.16%) were made by Casual Riders
• 2,662,959 rides (63.84%) were made by Members

The largest proportion of rides were attributable to Cyclistic members.

Bike type

The classic bike was the most popular type of bike across both sets of customers.

Casual:

• Classic Bike: 963,178 rides (23.09%)
• Electric Bike: 519,740 rides (12.46%)
• Electric Scooter: 25,279 rides (0.61%)

Members:

• Classic Bike: 1,735,863 rides (41.62%)
• Electric Bike: 905,419 rides (21.71%)
• Electric Scooter: 21,677 rides (0.52%)

Average Ride Length

The average ride length across all 4,171,156 rides, including both customer types, was 17 minutes.

Casual riders spent 24 minutes on average in the saddle per ride.

Members made quicker trips. Their average ride time was only 13 minutes. Half the time.

Days of the Week

The weekend was the most popular time for bike hires to be made by casual riders.

Saturday was the most popular day of the week. Casual riders made 315,539 trips (7.56% of the total trips) on Saturdays throughout the year. Sunday was the second most popular day of the week with casual riders. They made 263,537 trips on Sundays (6.32% of the total trips) throughout the year.

Tuesday was the least popular day of the week with casual riders, with 161,116 journeys made (3.86% of the total trips) over the year.

In complete contrast, Cyclistic members were least likely to hire bikes over the weekend, with 331,806 trips made on Saturdays (7.95% of the total trips) and 293,138 journeys made on Sundays – which was the least popular day of the week (7.03% of the total trips).

Weekdays were the most popular time for Cyclistic members to hire a bike, suggesting that many members used their bikes for commuting purposes. Wednesday was the most popular day for bike hires by members with 439,306 trips made across the year (10.53% of the total trips).

Time of the day

The majority of bike journeys were made during the afternoon, by both types of customers, with a total of 1,891,951 trips (45.36% of the total rides) being made between midday and 6pm.

The second most popular time of day for bike hires was during the morning between 6am and midday. A total of 1,119,114 trips (26.83% of the total rides) were made by both casual riders and Cyclistic members.

865,034 rides (20.74% of the total rides) took place during the evening (6pm to 9pm), while night was the least popular time of day for bike hires to be made with 295,057 trips (7.07% of the total rides) made between 10pm to 6am.

5pm was the most popular hour of the day where bike hires were made across all customer types. 436,125 trips were started at 5pm across the entire year, accounting for 10.46% of the total trips made. 4pm and 6pm were the second and third most popular times of day, with 390,114 (9.35%) and 336,481 trips (8.07%) made respectively across the year by members and casual riders.

3am was the least busy hour of the day for bike hires across all customer types, with 8,867 trips made throughout the year at that particular time (0.21% of the total trips made).

The top three hiring hours of the day by customer type were 5pm, 4pm, and 6pm for Cyclistic members and 5pm, 4pm, and 3pm for casual riders.

Seasonality

The summer was the busiest time of the year for bike hires across both customer types. 1,566,879 rides (37.56% of the total rides across the year) took place throughout the summer months of June, July and August.

Fall (September, October, and November) was the second most popular season with 1,225,149 rides made (29.37% of the total rides) across this period.

The cooler seasons of the year were the least popular times that casual riders and members used the bike hire service. In Spring (March, April, May), 953,632 rides took place (22.86% of the total rides).

Winter (December, January, February) was the least popular season of the year. 10.20% of rides took place over this period, with 425,496 journeys made by both customer types.

Months of the year

August was the most popular month for bike hires, with 537,956 trips made by all types of customers, accounting for 12.90% of the total trips made.

July and September were the second and third most active months across all customers with 537,678 and 533,403 trips made respectively.

January was the least active month of the year with 100,594 trips made (2.41% of the total trips made).

July (230,176 trips or 5.52% of total trips), August (226,827 trips or 5.44% of total trips) and September (214,299 trips or 5.14% of total trips) were the most active months for casual riders.

September (319,104 trips or 7.65% of total trips), August (311,129 trips or 7.46% of total trips), and July (307,502 trips or 7.37% of total trips) were the top three busiest months for Cyclistic members.

January was the least active month for both customer types.

Stations

The most popular docking stations, across all customers, to begin a trip were:

• Streeter Dr & Grand Ave (61,902 trips)
• DuSable Lake Shore Dr & Monroe St (41,068 trips)
• DuSable Lake Shore Dr & North Blvd (36,373 trips)
• Michigan Ave & Oak St (36,051 trips)
• Kingsbury St & Kinzie St (35,820 trips)
• Clark St & Elm St (31,738 trips)
• Clinton St & Washington Blvd (30,902 trips)
• Millennium Park (30,286 trips)
• Clinton St & Madison St (29,456 trips)
• Wells St & Concord Ln (27,930 trips)

The most popular docking stations to end a trip, across all customers, were:

• Streeter Dr & Grand Ave (63,371 trips)
• DuSable Lake Shore Dr & North Blvd (40,028)
• DuSable Lake Shore Dr & Monroe St (39,774 trips)
• Michigan Ave & Oak St (36,287 trips)
• Kingsbury St & Kinzie St (35,674 trips)
• Clark St & Elm St (31,215 trips)
• Clinton St & Washington Blvd (31,052 trips)
• Millennium Park (30,852 trips)
• Clinton St & Madison St (29,960 trips)
• Wells St & Concord Ln (28,368 trips)

Casual riders used the following stations the most to begin their trips:

• Streeter Dr & Grand Ave (47,966 trips)
• DuSable Lake Shore Dr & Monroe St (31,897 trips)
• Michigan Ave & Oak St (23,149 trips)
• DuSable Lake Shore Dr & North Blvd (21,181 trips)
• Millennium Park (20,654 trips)
• Shedd Aquarium (19,968 trips)
• Dusable Harbor (17,166 trips)
• Theater on the Lake (15,281 trips)
• Michigan Ave & 8th St (12,468 trips)
• Adler Planetarium (12,137 trips)

Casual riders used the following stations the most to end their trips:

• Streeter Dr & Grand Ave (52,040 trips)
• DuSable Lake Shore Dr & Monroe St (29,796 trips)
• DuSable Lake Shore Dr & North Blvd (24,993 trips)
• Michigan Ave & Oak St (24,035 trips)
• Millennium Park (22,718 trips)
• Shedd Aquarium (18,161 trips)
• Theater on the Lake (16,779 trips)
• Dusable Harbor (15,559 trips)
• Michigan Ave & 8th St (11,566 trips)
• Indiana Ave & Roosevelt Rd (10,642 trips)

Share

To document the various insights I uncovered during my data analysis, I created an accompanying dashboard using Tableau.

The dashboard was produced to help answer the key business question:

• How do annual members and casual riders use Cyclistic bikes differently?

My dashboard gave stakeholders real-time views of key metrics. Lily Moreno, the Marketing team, and the SMT could now all see the data.

I used a series of different charts and followed best practices to build the dashboard.

Act

Based on my findings, I made the following recommendations to Lily Moreno and the Cyclistic Marketing team:

1. Bike rentals peak on weekends among casual riders. The commercial team could investigate raising weekend rates for single ride or full day passes. This would motivate casual riders to buy a membership package because it offers better long-term value. Another method could be to create a new weekend membership model with discounted rates.
2. The marketing team can reach casual riders through email, social media, or via the Cyclistic app. They can share a ‘Savings Calculator.’ This tool would show how much riders have spent on single rides or day passes over a period of time. It would also show the potential savings they could have made by purchasing a membership.
3. The marketing team could look to target their new marketing campaign across the top ten start and end stations used by casual riders. This could include setting up QR code scanners, video walls, and promotional posters and billboards. Members of the Sales team could even be present, on the ground, at the most popular stations between 3-5pm (peak time for casual riders) to talk to customers and encourage them to sign-up for a membership. The Logistics team could also ensure enough bikes are available at peak times within the most popular bike stations.
4. The marketing team should prepare their new campaign throughout the winter and spring months before launching to casual riders throughout May – September. This will help to captialise on the most popular months of the year.
5. The marketing team could look to incorporate gamification into the Cyclistic app, to reward casual riders for clocking up their miles in the saddle. We know that casual riders tend to make longer journeys, so based on an agreed amount of activity, casual riders who clock up this benchmark could unlock rewards, such as a one-month free membership pass. This would expose them, on a temporary basis, to the Cyclistic membership on a ‘try before you buy’ basis. The app could then offer a further special discount for an annual membership if the casual riders sign up before an agreed deadline.

Assets:

SQL Code │ Tableau Visualisation

Inspiration

While building my dashboard, for the Hours/Day section I took huge inspiration from Naresh Suglani.