Google Data Analytics capstone project

-Studied patterns and trends in a large bike rental data set to find ways to increase subscription purchases of single-use renters

-Joined, cleaned, and analyzed data sets in SQL Server and Excel to search for evidence to support the hypothesis that a new market could be captured

-Modeled data in Tableau to further explore the data set, then developed a slide presentation with the data explaining the analysis process and presenting actionable findings

Transcript

1. CYCLISTIC HOW DO ANNUAL SUBSCRIPTION MEMBERS AND CASUAL RIDERS USE CYCLISTIC BIKES DIFFERENTLY? David Bacile
2. ANALYSIS SUMMARY • This analysis is performed with the intention of locating Cyclistic bike usage trends between one-time and subscriber users. A marketing team has requested data- driven recommendations on how to maximize the number of annual membership purchases. • After analysis, trends were found linking casual user bike trips to entertainment destinations during the weekends and warmer months. • Annual subscription members trend towards year-long use. This use is focused on weekday, downtown only rides that seem correlated to work commutes. • The suggested solution to this business problem is to create an all-new set of subscription packages that cater to the financial needs of casual riders. • Data is calculated on an open source 2019 dataset containing ~3.8 million bike rides. For clarification in the following slides, a customer refers to a Cyclistic non-subscriber rental, and a subscriber refers to a rental from a member of the yearly Cyclistic subscription service. 2022 2
3. • When graphing bike usage by user type and day of week, customer bike usage is seen more prominent on weekends and subscription bike usage is focused on weekdays. • The average trip duration across the data set is 57 minutes for customers and 14 minutes for subscribers. So, subscribers use their rented bikes for 75% less average time than customers. • The hypothesized reason for this is that subscribers mainly live downtown and use their bikes to commute back and forth to work. 2022 3 ANALYSIS PROCESS
4. • To test this idea, I want to visualize user trips on a heat map. Unfortunately, the dataset is missing trip geolocation info, providing only the cross- streets for each bike trip’s start and ending. • So, unique street intersections are derived from the cross-street data, and 640 unique values are found. • These unique intersections can now be uploaded to a 3rd party website and transformed into gps coordinates. • The coordinates that were incorrectly located by the site are found and manually fixed. Then, the latitude and longitudinal data are joined back to the original data set in SQL. 2022 4 www.gpsvisualizer.com
5. • Now, with this geo location data, a heat map can be plotted on top of Chicago. • For this visual I use the coordinates related to where the bikes end their trips, as I’m less interested in where users choose to start their rides. • After filtering down to only customer rides, we find the areas of high activity focused specifically around the circled leisure destinations. Customer Rides 2022 5 67,585 55,888 23,691 23,278 18,803 Navy Pier Maggie Daley/Millenium Park Oak St. Beach Lincoln Park Theater on the Lake Top 5 Customer Bike Destinations by Trip
6. • When instead plotting a heat map of only subscription users, bike destinations are found consolidated around the River North and Loop neighborhoods of downtown Chicago. • This indicates that on average, subscription riders don’t use their membership to bike to leisure areas in Chicago. • (This heat map’s density is more pronounced than the customer map. This is because there’s more data points for subscribers. In the 2019 data set there’s 880k customer rides compared to 2.9m subscriber rides). 2022 6 Subscriber Rides
7. • I want more evidence that subscribers mainly use their bikes for work commutes, so I plot their use on a graph. • I filter out weekends here (they were all dips in the graph) to more clearly display bike usage dipping heavily on work holidays. For example, there’s a roughly 65% decrease in subscriber ridership over Memorial Day. • Other dips in this graph often correlate to inclement weather, such as this thunderstorm on Jul. 18th. This is relevant because it means that much of the variation in the graph can be explained. 2022 7 www.wunderground.com
8. • When instead plotting a customer-only graph across the year, the peaks are largely inverse to the dips on the subscriber graph. • Instead of smoothing this graph, I’ve left in all days of the week to display that the peaks in this graph correlate to weekends. I’ve noted one such point to the right. • We notice high usage on holiday’s like Independence and Labor day in the warm summer months of 2019, but low average usage otherwise across the colder months of November through April. • This indicates that non-subscription based customers prefer to use Cyclistic bikes for leisure activities. 2022 8 Saturday, Aug. 3, 2019
9. BUSINESS STRATEGY • The data suggests subscribers largely use their bikes for work commutes and customers largely use their bikes for leisure activities. There’s no clear overlap in the demographics. In other words, there are few customers that seem to use their bikes to commute to work, and few subscribers proportionally that bike to leisure areas. • So, advertising to customers with the intention of encouraging them to buy a subscription for their work commute would likely be an unproductive use of advertising dollars. Cyclistic has already captured this market. • But, that’s not to say that customers don’t have predictable bike use patterns that can’t be catered to with all-new subscription packages. • Because customers largely use their bikes on weekends, a discounted yearly subscription called a “Weekend Tier” could be introduced. • Similarly, customers that prefer to bike only during warmer months could be offered a “Summer Tier” subscription option. 2022 9
10. WRAP UP 2022 10 • I’m surprised to find how strongly the two Cyclistic user types differed in bike usage. Although I was hoping to find evidence that there were uncaptured downtown work commuters in the customer subset of the data, the information I instead found was equally valuable. • The fact that customers routinely use their bikes for leisure activities in very specific geographic areas means there’s a targetable marketing opportunity present in this data. Cyclistic customers may become subscription members if the company is able to display to them a savings benefit in the form of tailored subscription packages.