Cyclistic Bike Data Analysis Report
Abraham Saenz Sigala
May 27, 2024
Business Task
Design a marketing strategy from insights gained in order to convert casual riders to annual members.
Dataset
The data set used ranges from January 1, 2019 (Q1) to April 2, 2020 (Q1). It contains a combination of 5 CSVs, totaling 15 columns and 4.2 million rows.
Limitations
Our data lacks identification to differentiate between frequent casual users and tourists, potentially affecting marketing strategies and user behavior patterns. Additionally, null values and inconsistent entries were present but had negligible impact on our analysis post data cleaning. Future data collection should have consistent naming conventions implemented to facilitate and ensure effective insights and company success.
Analysis
1. Insight
Total Rides
# Code: Total rides
total_rides <- bikedata %>%
summarize(total_rides = n()) | total_rides |
|---|
| 4202846 |
Total Rides by User Type
# Code: Total rides by user type
usertype_rides <- bikedata %>%
count(usertype)| usertype | user_rides |
|---|---|
| CASUAL | 918378 |
| MEMBER | 3284468 |
Our data shows that users have used our service for a total of 4,202,846 rides. When we separate this by user types, we see Members make up of 78.15% of rides while Casual users only make up 21.85% of the total.
2. Insight
Total Average/Median Trip Duration in Minutes
# Code: Trip Duration Average/Median
avg_median <- bikedata %>%
summarise(average = round(mean(tripduration), 2), median = median(tripduration)) | average | median |
|---|---|
| 19.03 | 11.5 |
Average/Median Trip Duration by Day of the Week
# Code: Average/Median Trip Duration by Day of the Week
avg_med_tripduration <- bikedata %>%
group_by(day_of_week) %>%
summarize(average = round(mean(tripduration), 2), median = round(median(tripduration), 2))| day_of_week | average | median |
|---|---|---|
| Monday | 17.23 | 10.78 |
| Tuesday | 16.28 | 10.50 |
| Wednesday | 16.35 | 10.58 |
| Thursday | 16.84 | 10.68 |
| Friday | 17.96 | 10.93 |
| Saturday | 26.09 | 15.57 |
| Sunday | 25.42 | 15.15 |
Average/Median Trip Duration by User Type
# Code: Average/Median Trip Duration by User Type
usertype_avg_med <- bikedata %>%
group_by(usertype) %>%
summarise(average = round(mean(tripduration), 2), median = median(tripduration)) | usertype | average | median |
|---|---|---|
| CASUAL | 40.77 | 25.58 |
| MEMBER | 12.95 | 9.65 |
Average Trip Duration by User Type and Day of the Week
# Code: Average Trip Duration by User Type & Day of the Week
avg_user_weekday <- bikedata %>%
filter(usertype %in% c("MEMBER", "CASUAL")) %>%
group_by(day_of_week, usertype) %>%
summarize(average = round(mean(tripduration), 2), .groups = 'drop') %>%
pivot_wider(names_from = c("usertype"), values_from = "average")| day_of_week | CASUAL | MEMBER |
|---|---|---|
| Monday | 39.89 | 12.62 |
| Tuesday | 38.87 | 12.55 |
| Wednesday | 38.18 | 12.62 |
| Thursday | 38.47 | 12.65 |
| Friday | 39.84 | 12.58 |
| Saturday | 43.02 | 14.61 |
| Sunday | 42.95 | 14.35 |
Our data reveals that Casual users have a significantly longer average ride time of 40.77 minutes, compared to Members who ride for an average of 12.95 minutes. Additionally, both Casual users and **Members* exhibit the highest average ride durations on weekends.
3. Insight
Average Age by User Type
# Code: Average Age by User Type
average_age <- bikedata %>%
filter(birthyear > 1950 & birthyear < 2005) %>% # Filtering for people 15 - 70 years old
mutate(age = dataset_year - birthyear) %>%
group_by(usertype) %>%
summarize(average_age = round(mean(age, na.rm = TRUE), 2))| usertype | average_age |
|---|---|
| CASUAL | 31.90 |
| MEMBER | 36.22 |
Most Rides by Age/User Type
# Code: Top 5 Most Rides by Age and User type
maxrides_age <- bikedata %>%
filter(birthyear > 1950 & birthyear < 2005) %>% # Filtering for ages 15-70 years old
mutate(age = dataset_year - birthyear) %>% # Calculate age
group_by(usertype, age) %>% # Group by usertype and age
summarize(num_of_rides = n(), .groups = 'drop') %>%
group_by(usertype) %>%
top_n(5, num_of_rides) %>%
ungroup()| usertype | age | num_of_rides |
|---|---|---|
| CASUAL | 25 | 24334 |
| CASUAL | 26 | 24534 |
| CASUAL | 27 | 23142 |
| CASUAL | 28 | 21236 |
| CASUAL | 31 | 20474 |
| MEMBER | 27 | 162574 |
| MEMBER | 28 | 180573 |
| MEMBER | 29 | 160565 |
| MEMBER | 30 | 163844 |
| MEMBER | 31 | 170150 |
Our data reveals that Casual users and Members total rides start to have a steady drop off at around ages 35 - 40. For Casual users we hit a peak of total rides from ages 25 - 28. For Members our peak total rides range from ages 27 - 31. NOTE: Its important to know that we are filtering for user 15 - 70 years old
4. Insight
Top 5 Most Popular Stations
# Code: Top 5 Most popular stations
max_rows <- 5 # Limit to 5
top_5_stations <- bikedata %>%
group_by(from_station_name) %>%
summarize(total_rides = n()) %>%
arrange(-total_rides) %>%
top_n(max_rows)| from_station_name | total_rides |
|---|---|
| Streeter Dr & Grand Ave | 70424 |
| Canal St & Adams St | 62195 |
| Clinton St & Madison St | 56696 |
| Clinton St & Washington Blvd | 54088 |
| Lake Shore Dr & Monroe St | 52054 |
Our data reveals our most popular stations. Casual users use Streeter Dr & Grand Ave the most (54,612 rides). Members use Canal St & Adams St the most (58,155 rides).
Top 5 Most Popular Stations by User Type
# Code: Top 5 Most popular stations by usertype
max_rows <- 5 # Limit to 5
popular_stations <- bikedata %>%
filter(usertype %in% c("CASUAL", "MEMBER")) %>%
group_by(usertype, from_station_name) %>%
summarize(num_of_rides = n()) %>%
arrange(usertype ,desc(num_of_rides)) %>%
group_by(usertype) %>%
top_n(max_rows)| usertype | from_station_name | num_of_rides |
|---|---|---|
| CASUAL | Streeter Dr & Grand Ave | 54612 |
| CASUAL | Lake Shore Dr & Monroe St | 40811 |
| CASUAL | Millennium Park | 22517 |
| CASUAL | Michigan Ave & Oak St | 22008 |
| CASUAL | Shedd Aquarium | 21611 |
| MEMBER | Canal St & Adams St | 58155 |
| MEMBER | Clinton St & Madison St | 52534 |
| MEMBER | Clinton St & Washington Blvd | 51201 |
| MEMBER | Columbus Dr & Randolph St | 35469 |
| MEMBER | Kingsbury St & Kinzie St | 35142 |
In our plot pictured below, we uncover our top 5 most popular stations for Casual users and Members. This insight allows us to make the correct data driven decisions when it comes to creating marketing strategies.
5. Insight
Total Rides per Month by User Type
# Code: Rides by Months for User Types
monthlyusers <- bikedata %>%
mutate(month = month(start_date, label = TRUE)) %>%
filter(usertype %in% c("MEMBER", "CASUAL")) %>%
group_by(usertype, month) %>%
summarize(num_of_rides = n()) %>%
pivot_wider(values_from = num_of_rides, names_from = usertype)| month | CASUAL | MEMBER |
|---|---|---|
| Jan | 12230 | 232756 |
| Feb | 15305 | 218360 |
| Mar | 43060 | 263044 |
| Apr | 46958 | 215742 |
| May | 79890 | 283404 |
| Jun | 127774 | 342234 |
| Jul | 174459 | 377414 |
| Aug | 185321 | 398686 |
| Sep | 128154 | 359995 |
| Oct | 70387 | 298429 |
| Nov | 18583 | 157040 |
| Dec | 16257 | 137364 |
Our data set reveals that Summer months are among the most popular for our users. Both Members and Casual users see a substantial rise in rides during these months! However, as the colder months approach, we observe a notable decline in ride activity.
Actionable Insights
1. Conclusions
Members constitute a substantial portion of our user base, comprising 78.15% of our total users. It’s important to acknowledge that converting every Casual user may not be feasible, as our data set doesn’t distinguish between locals who use our service without subscribing and tourists. However, we can implement marketing strategies to introduce our service to tourists. Additionally, there’s a significant opportunity to convert a portion of our Casual users and attract new users through incentivized initiatives.
Average trip duration increases substantially during the weekends.
Casual users on average seem to have a longer trip duration compared to Members.
Casual users ages 25 - 31 use our service the most. This upward trend begins at around age 20 and begins to drop off steadily at age 40.
Top 5 Most popular stations for both Casuals and Members are responsible for more than 50,000 rides individually.
Most popular stations for Casual users include: Streeter Dr & Grand Ave, Lake Shore Dr & Monroe St, Millennium Park, Michigan Ave & Oak St and Shedd Aquarium.
The most active months are from June to September for both Members and Casual riders. We do see a drop off during the colder months from November to February.
2. Recommendations
Weekend Promotions for Casual Users:
Strategy: Offer promotional incentives such as discounts at local eateries, cafes, and stores for riders who sign up for memberships during weekends.
Execution: Promote these offers through social media, local hotspots, and in-app notifications starting Friday mornings. Collaborate with local businesses for joint promotions, providing mutual benefits and greater reach.
Student Promotions:
Strategy: Introduce a student pass or referral program to highlight the value of our service and encourage future adoption of annual memberships.
Execution: Partner with universities and student organizations to spread the word. Utilize social media platforms for targeted advertising. Offer initial sign-up incentives, such as free rides during the first month, to encourage trial and adoption. Educate students on the benefits and savings of becoming annual members.
Summer Marketing Strategy:
Strategy: Offer a discounted pass during the first summer month or the first week of each summer month to encourage casual users to become annual members.
Execution: Launch a summer marketing campaign across social media, local radio, and outdoor advertising (posters or billboards). Collaborate with local tourism boards to promote the service to visitors. Send targeted emails to previous casual users and unsubscribed members informing them of summer deals.
Marketing at Popular Stations:
Strategy: Implement marketing strategies such as QR codes, on-site promotions, and attractive signage at the most popular stations to incentivize sign-ups.
Execution: Design eye-catching posters and banners for these stations. Schedule staff presence during peak times, especially weekends and summer months, to provide information on savings and benefits, as well as assistance. Track the effectiveness of these promotions by monitoring membership sign-ups at these locations, allowing for data-driven adjustments and improvements.
Trip Duration Marketing:
Strategy: Offer special benefits for users who ride for a certain amount of time.
Execution: Promote these offers through the app, posters, and other types of media highlighting riding milestones. Partner with businesses to provide discounted benefits or free rides for users who surpass certain riding milestones. Create a rewards program for users who ride for long durations, use the service multiple times a day, or achieve specific milestones.