#MyProject
#MyProject
Devise a Strategy For a Bike Share Company To Increase Number of Annual Members
Devise a Strategy For a Bike Share Company To Increase Number of Annual Members
Embedded Files
"Analyze the data about customers(casual and annual riders) to find trends and patterns in their behavior in order to come up with a plan to get more casual riders to signup for annual membership package."
"Analyze the data about customers(casual and annual riders) to find trends and patterns in their behavior in order to come up with a plan to get more casual riders to signup for annual membership package."
The project was completed following the six steps of data analysis cycle:
The project was completed following the six steps of data analysis cycle:
🔹Ask
🔹Prepare
🔹Process
🔹Analyze
🔹Share
🔹Act
DELIVERABLES
DELIVERABLES
1. A clear statement of the business task
2. A description of all data sources used
3. Documentation of any cleaning or manipulation of data
4. A summary of my analysis
5. Supporting visualizations and key findings
6. My top three recommendations based on the analysis
Business Task
We want to increase the number of our annual members and in order to do that, we want to target our casual user base. We need to find the differences between how annual and casual riders use the bikes differently. Once we have figured that out, can pinpoint what makes casual users switch.
The Stakeholders
The key stakeholders for this project are:
Director of marketing
Marketing analytics team
Executive team
Data Sources Used
I downloaded the customer trips data from the company's data server and stored appropriately giving a lot of attention to how the files are named and organized. The data was organized into 2 different forlders of .xls and .csv files. Both of these contained Microsoft Excel files. These different formats are needed when doing further analysis. Within the folders there were 12 additional folders containing the data for all the 12 months of 2021.
The data was saved in a place it could not be accessed by anyone that is not a stakeholder in the project. This was done to protect the customers' data as it contained details about their personal information.
The data was saved in a place it could not be accessed by anyone that is not a stakeholder in the project. This was done to protect the customers' data as it contained details about their personal information.
Processing The Data
The data was then cleaned in Excel and any mistakes, typos, multiple entries were looked for and removed. Since I have a trip start and end time for each trip available but not the duration of the ride, it was calculated by subtracting the start time from the end time and was put in a column named "ride_length". This will be used for future analysis. Another column was created named "day_of_week". in this column the day that the cycle was booked was placed. It was calculated using the 'weekday' command on the "start_time" column. The names of the columns in the files are as follows:
The data was then cleaned in Excel and any mistakes, typos, multiple entries were looked for and removed. Since I have a trip start and end time for each trip available but not the duration of the ride, it was calculated by subtracting the start time from the end time and was put in a column named "ride_length". This will be used for future analysis. Another column was created named "day_of_week". in this column the day that the cycle was booked was placed. It was calculated using the 'weekday' command on the "start_time" column. The names of the columns in the files are as follows:
ride_id
rideable_type (type of bike)
started_at
ended_at
start_station_name
start_station_id
end_station_name
end_station_id
start_lat (latitude)
start_lng (longitude)
end_lat
end_lng
member_casual (type of member)
ride_length
day_of_week
The data was further cleaned and many small corrections were made until it was ready for analysis.
The data was further cleaned and many small corrections were made until it was ready for analysis.
The Analysis
In order to start the analysis, all the 12 months of data was combined into 1 single file for the whole year. Where relevant, the columns were made consistent and a descriptive analysis of the data was conducted. The tool of choice selected for analysis was SQL and Microsoft Excel. The SQL related work was done in BigQuery. A few calculations were done in SQL to get a better sense of the data I'm working with. Once the file was uploaded into BigQuery, the following code was run in order to get some summary statistics.
In order to start the analysis, all the 12 months of data was combined into 1 single file for the whole year. Where relevant, the columns were made consistent and a descriptive analysis of the data was conducted. The tool of choice selected for analysis was SQL and Microsoft Excel. The SQL related work was done in BigQuery. A few calculations were done in SQL to get a better sense of the data I'm working with. Once the file was uploaded into BigQuery, the following code was run in order to get some summary statistics.
>To calculate maximum ride_length
>To calculate maximum ride_length
SELECT MAX(ride_length)
FROM `case-study-2021.case_study_2021.Feb_2021`
>To find the mode of day_of_week i.e. which day has the most rides
>To find the mode of day_of_week i.e. which day has the most rides
SELECT day_of_week
FROM `case-study-2021.case_study_2021.Feb_2021`
GROUP BY day_of_week
ORDER BY COUNT(day_of_week) DESC
LIMIT 1
>To calculate the average of ride_length I used Excel formula
>To calculate the average of ride_length I used Excel formula
=AVERAGE(N:N)
(column N has the ride_length data)
>To calculate the average ride_length for Casual riders & members seperately I used this excel formula
>To calculate the average ride_length for Casual riders & members seperately I used this excel formula
=AVERAGEIF(M:M,"member",N:N)
&
&
=AVERAGEIF(M:M,"casual",N:N)
(column M has member_casual data)
I also worked with Pivot tables in Excel. To find the average ride_length for users sorted by days of week, we use these conditions in pivot table:
I also worked with Pivot tables in Excel. To find the average ride_length for users sorted by days of week, we use these conditions in pivot table:
-> Columns = day_of_week
-> Columns = day_of_week
-> Rows = member_casual
-> Rows = member_casual
-> Values = Average of ride_length
-> Values = Average of ride_length
By adding count of trip_id to values, I also found the number of rides for users by day_of_week.
By adding count of trip_id to values, I also found the number of rides for users by day_of_week.
By conducting the same descriptive analysis of different months throughout the year I found the difference in how the customers use bikes throughout the different seasons.
By conducting the same descriptive analysis of different months throughout the year I found the difference in how the customers use bikes throughout the different seasons.
Sharing My Findings Through Visualizations
In order to share my findings with the stakeholders, i had to convert the data in the summary files to easy to understand, simple, accessible visualizations. I chose to make these visualization in Tableau & Excel.
In order to share my findings with the stakeholders, i had to convert the data in the summary files to easy to understand, simple, accessible visualizations. I chose to make these visualization in Tableau & Excel.
In order to understand how the number of casual riders and annual members changes on different days of the week, I created a plot in Tableau by the name "Difference between cycle usage throughout the week: Member vs. Casual" which can be seen here(https://public.tableau.com/app/profile/samran.maqbool).
In order to understand how the number of casual riders and annual members changes on different days of the week, I created a plot in Tableau by the name "Difference between cycle usage throughout the week: Member vs. Casual" which can be seen here(https://public.tableau.com/app/profile/samran.maqbool).
Looking at the plot, it is clear that number of annual members is far greater than that of casual riders. Saturday and Sunday are the busiest days for casual riders while for members the least number of trips are recorded on Sunday. It means that casual riders mostly use bikes for leisure/fun rides or are tourists going around the city on weekends while members mostly use it on weekdays for travelling to and from their places of work.
Looking at the plot, it is clear that number of annual members is far greater than that of casual riders. Saturday and Sunday are the busiest days for casual riders while for members the least number of trips are recorded on Sunday. It means that casual riders mostly use bikes for leisure/fun rides or are tourists going around the city on weekends while members mostly use it on weekdays for travelling to and from their places of work.
Next, i created an Excel file that had the number of casual and annual members sorted by month. I imported this file into Tableau so that I could visualize how the number of different riders changes throughout the months. A visualization by the name of "Ridership Throughout the Year" was created which can be seen here(https://public.tableau.com/app/profile/samran.maqbool).
Next, i created an Excel file that had the number of casual and annual members sorted by month. I imported this file into Tableau so that I could visualize how the number of different riders changes throughout the months. A visualization by the name of "Ridership Throughout the Year" was created which can be seen here(https://public.tableau.com/app/profile/samran.maqbool).
This visualization shows that the number of casual riders is the smallest at the beginning of the year i.e. just a few thousand and gradually increases in the coming months until July where it hits a peak monthly ridership of more than four hundred thousand. After that it starts to decline gradually in the coming months until the end of the year.
The story is a little different for annual members. The numbers are the smallest at the beginning of the year like casual riders. After that the numbers go up every month until hitting the peak around Aug-Sep period and then ridership decreases till December. It is important to note that June, July, August are the only months in which number of casual riders is greater than annual members. It means that although the overall number of annual member rides is increasing in the mid year months, the percentage of total monthly rides is decreasing. In order to confirm that, I created the following chart in Microsoft Excel.
This chart makes things much more clearer. Even though both type of riders have the smallest number of rides at the beginning of year, annual member rides constitute ~80% of all rides throughout the month. In the coming months percentage of casual riders increases and annual members' percentage decreases reaching the lowest of ~45% in July. This is in line with the previous findings. After July, annual members' percentage increases till year end.
Now that the analysis is completed, I can draw conclusions and create a set of recommendations for the marketing analytics team.
Recommendations
My top 3 recommendations for the marketing analytics team are:
Create discounted membership plans in the months from June to September for targeting casual riders.
Launch marketing campaigns with stats showing how annual membership is cheaper than casual rides in mid year.
Target the casual riders through social media by geolocating the ones closer to site of any festival, parks, theme parks and tourist attractions because that's where most casual riders would be going on weekends.
Page updated
Google Sites
Report abuse