Excel Project
Cyclistic bike-share
In 2022, Cyclistic, a bike-share company in Chicago, gathered data on bike usage by casual riders and annual members.
​
The marketing analyst team aims to analyze this data to identify differences in usage patterns between the two groups and design strategies to convert casual riders into annual members.
​
Due to Excel limitations on the number of rows it can handle efficiently, I work with a single month's data (January 2022).
Data source: divvy-tripdata
I start formatting data as needed for clarity and ease of analysis. This includes setting appropriate data types, number formats, and date formats.
I remove duplicates, leading and trailing spaces in cells and checking missing data.
Pertinent columns are created to facilitate the analysis process.
​
-
Column E:
-
ride_lenght =D2-C2
-
Datatype set to TIME to show up the output value in the format hh:mm:ss.
-
-
Column F:
-
day_of_week_temp =WEEKDAY(C2, 1)
-
-
Column G:
-
day_of_week =IFS(F2=1, "Sunday", F2=2, "Monday", F2=3, "Tuesday", F2=4, "Wednesday", F2=5, "Thursday", F2=6, "Friday", F2=7, "Saturday")
-
A simple pivot table is created in order to visualize the number of rides on a daily timeframe (January only).
​
​
The dual-line chart presents two key insights:
-
Member rider numbers remain consistent on workdays, implying substantial utilization by commuters.
To validate this, we anticipate observing a consistent ride duration throughout these days.
​
-
Weekend days exhibit a higher presence of casual riders.
A closer examination of timings and locations allows us to uncover aspects of our casual rider demographics.
By identifying patterns like frequenting the same meeting spots, we can encourage these individuals to transition into members.
A bar chart compares average ride durations per day of the week for both user types.
Two distinct insights come to light:
-
Member riders exhibit a consistent ride duration, averaging 12 minutes. This observation potentially affirms their usage pattern for commuting to work.
​
-
Casual riders, on the other hand, tend to opt for longer routes, suggesting a recreational usage in areas like parks and other scenic paths.
A spatial analysis could further validate this trend.
After looking at the differences between casual and member riders, we can come up with smart plans based on data to convince casual riders to join as members.
​
Suggestions:
-
Seasonal Hotspots: Concentrate marketing drives in spring and summer at spots favored by casual riders for leisure and tourism.
-
Timed Access: Tailor memberships for weekends and peak seasons to align with casual riders' preferences for active cycling.
-
Ride Incentives: Introduce ride-duration discounts to captivate casual riders and encourage longer rides, appealing to both casual users and members.