Google Capstone Project: ‘Cyclistic’ Case Study

6 min readMay 6, 2023

This Project is part of the final course in the ‘Google Data Analytics Professional Certificate’. It follows a real-world scenario where I as a data analyst had to follow the steps of the Data Analysis Process and share my findings.

Scenario

You are a junior data analyst working in the marketing analyst team at Cyclistic, a bike-share company in Chicago. The director of marketing believes the company’s future success depends on maximizing the number of annual memberships. Therefore, your team wants to understand how casual riders and annual members use Cyclistic bikes differently. From these insights, your team will design a new marketing strategy to convert casual riders into annual members. But first, Cyclistic executives must approve your recommendations, so they must be backed up with compelling data insights and professional data visualizations.

Characters and teams

● Cyclistic: A bike-share program that features more than 5,800 bicycles and 600 docking stations. Cyclistic sets itself apart by also offering reclining bikes, hand tricycles, and cargo bikes, making bike-share more inclusive to people with disabilities and riders who can’t use a standard two-wheeled bike. The majority of riders opt for traditional bikes; about 8% of riders use the assistive options. Cyclistic users are more likely to ride for leisure, but about 30% use them to commute to work each day.

● Lily Moreno: The director of marketing and your manager. Moreno is responsible for the development of campaigns and initiatives to promote the bike-share program. These may include email, social media, and other channels.

● Cyclistic marketing analytics team: A team of data analysts who are responsible for collecting, analyzing, and reporting data that helps guide Cyclistic marketing strategy. You joined this team six months ago and have been busy learning about Cyclistic’s mission and business goals — as well as how you, as a junior data analyst, can help Cyclistic achieve them.

● Cyclistic executive team: The notoriously detail-oriented executive team will decide whether to approve the recommended marketing program.

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.

Moreno has set a clear goal: Design marketing strategies aimed at converting casual riders into annual members. In order to do that, however, 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. Moreno and her team are interested in analyzing the Cyclistic historical bike trip data to identify trends.

Ask

Three questions will guide the future marketing program:

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

Main Stakeholder, Moreno, has tasked me with answering the first question.

Tools Used:

Microsoft Excel: Used to get initial understanding of the data with some changes to the datasets.
Google BigQuery: Used for the main part of the Analysis, for data manipulation and to get final results.
Tableau: Used for the visualization of data

Data

This is a first-party data collected directly from the company’s sources. Data is not biased, is complete, reliable. I will be using data collected in a period from April 2020 to March 2021.

Here are the original dataset and license.

Initial look at the Data

Saved all 12 files to an subfolder, ensuring original file integrity
Proceeded to check the data for inconsistencies, there were some in the ride_id field, since there was no info on it the field was left as is
Removed columns start_lat, start_long, end_lat, end_long since they will not be used in this analysis
Created column trip_duration which calculated the duration in minutes. Function used: =CONVERT(D2-C2, “day”, “mn”), where 01:30sec = 1.5
Removed rows from trip_duration which had negative or 0 values
Created column weekday which extracted day from the started_at column. Function used: =TEXT(WEEKDAY(C2,1),”dddd”)

Having done that for all 12 files, data was ready to be moved to BigQuery for analysis. I will include an example of my code in this reading, for my complete SQL code visit my GitHub.

BigQuery Analysis and Tableau visualizations

After merging all tables into one, cleaning the data of unwanted null values and extracting the month from started_at column, the dataset was ready for analysis. Final dataset had 3473442 rows and 10 columns.

SQL used to get number of trips and average trip duration by member_type

SELECT COUNT(*) AS number_of_trips,  member_type 
FROM `capstone-385619._script2fed36877decab554777bc3d3c01cc24dd9889df.final_data`
WHERE trip_duration <= 1440
GROUP BY member_type

SELECT ROUND(AVG(trip_duration),0)AS average_trip, member_type
FROM `capstone-385619._script2fed36877decab554777bc3d3c01cc24dd9889df.final_data`
WHERE trip_duration <= 1440
GROUP BY member_type

In this viz we can see that there is a big difference in number of trips taken by different member types. Similarly the difference for average duration is also big were casual riders are riding the bike longer.

This graph shows that the ‘Members’ are having more trips during the workdays, with the ‘Casual’ riders slowly increasing over the workdays, and having their most trips on the weekends, even overtaking ‘Members’ on Saturdays.

Having the smaller number of trips on average, the ‘Casual’ riders tend to stay on bikes more than ‘Members’.

We will now examine these differences by Months

Differences in Number of Trips between Months and Member Types

In the ‘Summer Spikes’ visualization we can see the some ups and downs for both member types until the month of May where their numbers start increasing and reaching their peeks at the month of August, from where the numbers go down with the temperatures.

The Monthly Trip Duration difference shows the same trends seen in the Daily Duration.

This Stacked Bar Chart shows that the difference in bike choice for both groups is massive and that the Classic bike is the preferred mode of transport.

Recommendations

since the most riders opt for classic bikes, special advertisements should be placed at or around stations which mostly have classic bikes
since the casual riders ride bikes longer on average, maybe a different approach to pricing might incentivize them to switch to annual memberships
furthermore, annual members could be given special discount, prices based upon their months of being a member, or even be given a day or 3 days free based upon their loyalty, which might get the casual riders to switch
implementing a strategy where a certain number of bikes per station could be ‘reserved’ by members could motivate casual riders to switch. That would especially be effective at the summer months where the number of rides are the highest
since the casual riders are using bikes for leisure, teaming up with some sport brands or getting athletes on adverts might increase the number of members