Cyclistic: Google Data Analytics Case Study

This case study is a part of the Google Data Analytics Professional Certification. In this case, I am working as a junior data analyst in the marketing team for Cyclistic, a bike-share company based in Chicago. And my task is to analyze user behavior, find similarities and differences, determine trends, and provide recommendations on how these insights can help the marketing team develop strategies that would convert casual riders into annual members.

The dataset provided for this project consists of multiple ‘.csv’ files representing each month from January 2020 to August 2021. Each table consists of 13 columns with a various number of records depending on each month’s rides.

In order to prepare and clean this dataset, I used SQL to address the following issues:

• multiple null values across tables,
• inconsistent naming of columns,
• inconsistent format of variables.

I also added two new columns to calculate the duration of each ride and the day of the week when each ride started.

After fixing several issues during the cleaning process, we can now begin analyzing the data to see the patterns that differ between the behavior of casual users and annual members. To this end, I used Tableau software to better visualize the data.

First, we want to get some general information about our customers behavior, such us how their bike usage varies over time.

Our first finding was that bike usage varies largely over the year.

Bike usage spikes in the summer peaking in August, and largely decreases as temperatures drop. This result indicates the existence of a significant seasonal effect on bike usage.

(This interpretation has taken into consideration the nature of California’s climate: a Mediterranean climate, with hotter, drier weather in summer and cooler, wetter weather in winter.)

After seeing the evolution of our customers behavior over the months. The next step is to verify if the days of the week also has an impact on bike usage.

Yes, it certainly has. Casual users are more likely to use the service during weekends, while members use it very consistently during the week.

These trends may indicate more leisure-oriented bike use by casual users, as opposed to more commuting use by members.

We will continue down this path and examine the impact that different hours of the day might have on bike usage for each of our costumers’ types.

There’s a clear difference here, members bike usage is significantly higher than casuals in the morning between 6am and 9am, and in the evening between 4pm and 6pm.

In order to properly measure the differences in the behavior of our users, we need to compare the percentage of hourly rides (number of rides in a specific hour / total rides of the day), not just their numbers.

When comparing the percentages, it turns out that the only significant difference between our users’ behavior exist in the morning. Members’ bike usage between 7am and 9am accounts for more than 41% of their total daily usage, while it accounts for only 27% in the case of casuals.

This graph emphasizes our recent assumption of considering commuting as the primary purpose of members’ bike use.

These findings led us to further question the reliability of this categorization:

o Members = use bikes for commuting

o Casuals = use bikes for leisure

Looking at the average trip duration, we found that casuals cyclists’ trips are more than twice as long as members’ trips.

The longer trips of casuals’ riders support the idea that they primarily use bikes for leisure and touristic purposes.

Recommandations :

The goal of the marketing campaign is to get casual cyclists to sign up for an annual membership.

Based on the results of this analysis and in order to properly present my recommendations, I will employ the key elements involved in marketing: the four Ps — product, price, place, promotion — which are often referred to as the marketing mix.

By considering all these elements we will be able to approach a holistic marketing strategy.

Product:

Casual riders are highly active on weekends.

• Develop a special weekend membership (create a new kind of members).

Price:

• Increase the price for the non-subscription service to encourage the transition of casuals’ riders to this new type of membership.

Place:

Casual riders mostly use bike-sharing for leisure and tourism purposes.

• The campaign should focus on airports, hotels, museums, and other touristic places.

Promotion:

The bike usage reaches its peak in the summer.

• Summer is the best time to run the campaign.