Cyclistic

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.

Main Objective:

The main objective of the company is maximising the number of annual memberships.

Business Task:

To make marketing strategies to convert casual riders into annual members.

Goal:

To understand how casual riders and annual members use Cyclistic bikes differently.

Our process will be based on six steps which are,

1. Ask

2. Prepare

3. Process

4. Analyse

5. Share

6. Act

Ask

The director of the company Cyclistic has set a clear goal for us, which is, to design marketing strategies aimed at converting casual riders into annual members.

The questions which will guide future marketing program are as follows,

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?

The problem we are trying to solve is to find out how casual riders and annual members use bikes differently.

The key stakeholder in this scenario is Lily Moreno

Prepare

Data Source:-

For this project i have used public data from https://divvy-tripdata.s3.amazonaws.com/index.html

Cyclistic is a fictional company, so I have considered this public data as Cyclistic’s historical data which has records of people who have taken rides on bikes.

Licence Agreement:-

There is no private public information in this data which can be used by anyone for malice. The data has been made available under this licence https://ride.divvybikes.com/data-license-agreement

About the Data:-

The data on the website was organised badly. For data of month Feb, after downloading the files we come to know that data is actually of month Jan.

The names of data have been all mixed up by the provider.

For this project I have considered 7 months of data. I have used Google sheets to have a quick preview of the data. I have noticed lots of null values in the data.

Attributes of the data are as follows,

1. ride_id

2. rideable_type

3. started_at

4. ended_at

5. start_station_name

6. start_station_id

7. end_station_name

8. end_station_id

9. start_lat

10. start_lng

11. end_lat

12. end_lng

13. member_casual

Process

Tools used:-

1. SQL Big query:- For data cleaning, sorting, data manipulation and data analysis. Since the size of data is big, MS Excel or Google Sheets won't be able to process data so they were not used.

2. Tableau:- For visualisation for stakeholders.

3. Google Docs:- For writing reports and documenting the case study.

Data Cleaning and Sorting:-

After downloading the data, I have seen that all the downloaded files have the wrong name by the provider. I had to rename all the downloaded CSV files then upload them to SQL Bigquery. After uploading all 7 months of data my first step was to aggregate all the different tables into one for better cleaning, sorting and analysis.

I have combined all the data into one single table by name data2021.

To check for the null values I have used the following query for every column.

Fortunately, there are no null values in columns which we will be using for analysis.

Next step is to check for duplicate rows in data. I have done that by using the following query in SQL. There were no duplicate rows found in the data.

Next we will look if the data type is correct for all columns. Using Schema we can confirm that there was no problem with data type in any column.

Now the data is cleaned and ready to analyse

Analyze

In this phase, we are gonna run queries in SQL BigQuery to analyse data and find trends and relationships in the data.

First I found out how many total riders, casual riders, annual riders, total bikes, classic bikes and electric bikes there are in the data. For that I have used the following query.

Then the next thing I did was, find which type of bike casual members use more.

For that i have used the following query in SQL to analyse the data.

Now to find the difference between which group spent more time riding bikes. For that I have used TIMESTAMP function in the following SQL query.

Average of time spent by casual riders and Annual members riding bike are as follows.

With this we have completed analysing the data and we move towards the next step, Share phase, where we will share our findings.

Share

We were able to find the differences between casual riders and annual members on how they used bikes differently. The following visualisations tell us why they are different when it comes to using bikes.

Total number of casual riders vs Members:-

First let's find if there are more casual riders or annual members who are using the Cyclistic bikes.

According to the 7 months of data, there are 1,168,868 total members and 682,465 total casual riders. We can see that there are more members than casual riders who are using the Cyclistic bikes. With this in mind we can proceed further.

Time spent riding bike:-

We will now look at which group spends more time riding bikes.

Though there are more members than casual, we can see that casual riders spend more time riding bikes than annual members. The average time spent by a casual rider is 33.64 minutes and the average time spent by members riding a bike is 12.79 minutes.

Type of bike preferred by casual riders and members:-

The following visual points out which type of bike is preferred more by casual riders and annual members.

From the above visuals we can see that the most bikes used by riders are classic bikes.

Annual Members:- 70% of classic bikes and 61% of electric bikes are used by annual members.

Casual Riders:- 29% of classic bikes and 38% of electric bikes are used by casual riders.

We can see that casual riders prefer to ride electric bikes more than classic bikes.

Act

Conclusion:- We can see that there are more members than casuals and members prefer riding classic bikes more than electric bikes. But the case is opposite for casual riders; Casual riders prefer riding electric bikes more than classic bikes. Also, casual riders spend more time riding bikes than members.

Applying Insights:- Based on the above analysis and insights, if we want to convert more casuals into members then we have to make offers and discounts on electric bikes for members only. This will make more casual riders buy membership.