Strategy Recommendations for Cyclistic Bike Share: A Google Data Analytics Capstone Project.

INTRODUCTION

The Cyclistic bike share case study is a capstone project for the Google Data Analytics Professional Certificate. Cyclistic Bike-Share is a fictional company based in Chicago, seeking to achieve greater success by maximizing a category of its customer base. Outlined below are: the company brief, role, processes, analysis, and recommendations geared towards achieving the business ask.

Tools used: Microsoft Excel, Microsoft SQL Server Management Studio, Modern CSV, Tableau Public Desktop

Links

SQL Documentation on my GitHub

Tableau Interactive Dashboard here

Link to raw dataset here

1. Company Background

Cyclistic- A bike-share program that features more than 5,800 bicycles and 600 docking stations. It 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. Most riders opt for traditional bikes; about 8% of riders use assistive options. Cyclistic users are more likely to ride for leisure, but about 30% use them to commute to work each day. ?

In 2016, Cyclistic launched a successful bike-share offering. Since then, the program has grown to a fleet of 5,824 bicycles that are geo-tracked 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 at any time.

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. 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.

1.1 Stakeholders

Cyclistic: A bike-share company with more than 5,800 bicycles and 600 docking stations.

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.

2. Task

As a junior data analyst working in the marketing analyst team at Cyclistic. 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.

Recap: My role is identifying how annual members and casual riders use Cyclistic bikes differently. ?

3. Data Collection

Cyclistic Bike-Share historic trip data exists and can be accessed here, courtesy of Motivate International Inc, I downloaded the trip data for the months of March 2022 to February 2023, since I am looking to identify how riders used Cyclistic bikes in the past year.

4. Data Cleaning in Microsoft Excel

After downloading the trip data from March 2022- February 2023 in CSV format, I imported and cleaned them individually in Microsoft Excel. First by filtering and deleting duplicate values in ride_id.

Blanks found in the start_station and end_station columns were also deleted.

Missing values in start_station_name, end_station_name, start_station_id, and end_station_id were also deleted.

5. Data Transformation

To enhance comprehension, column names rideable_type, and member_casual were changed to bike_type and user_type respectively.

A new column ride_length was created, computed as the difference between ended_at and start_at, and formatted as (HH:MM: SS). A day_of_week column was also inserted, computed as =WEEKDAY(“Start_day”, 1) 1 representing Sunday, the first day of the week.

5.1 Deletion

Columns start_station_id, end_station_id, start_lat, start_long, end_lat, and end_long were deleted.

5.2 Data Consistency

Ride duration, indicated as ride_length less than 1 minute (0:01:00) was filtered and deleted, the same was done to ride length over 24 hours(24:00:00).

5.3 Data Validation

Checked for Null values and misspelled words. Data types were also checked and confirmed to be consistent in every individual spreadsheet file. I also checked for duplicates in ride_id columns.

6. Descriptive Analysis

For every month I checked for the total number of bike trips, the average ride duration(ride_length), the maximum ride duration(another way I check for ride duration below 1 minute and above 24hrs), and finally the mode. My findings are documented in the table below.

7. Data Transformation with SQL

I created a database “Bike_Trips” on Microsoft SQL Server Management Studio (SSMS) and imported each spreadsheet from March 2022 to February 2023 in chronological order into the newly created database.

To understand how Cyclistic customers rode in the past year, I combined the monthly trips to a year, thus another table “Cyclistic_bike_trips” was created using SELECT INTO AND UNION ALL queries.

With the new table created, it lacked variables like the season, month, hour, day of the week, and time of the day, so I created yet another table “new_cyclistic_trips”, using the DATEPART function to get the Hour each bike ride started, Month function for the month each bike ride started and CASE statements for day_of_week, season and time_of_day.

SELECT * INTO Bike_Trips.dbo.[new_cyclistic_trips

FROM?

(SELECT ride_id,

bike_type,

user_type,

started_at,?

ended_at,

day_of_week,?

DATEPART("HOUR", started_at) AS hour,??

DATEDIFF(MINUTE, started_at,ended_at) AS ride_duration,

?

CASE?

????WHEN MONTH(Started_at) = 1 THEN 'January'

??????????????? WHEN MONTH(Started_at) = 2 THEN 'February'

??????????????? WHEN MONTH(Started_at) = 3 THEN 'March'

??????????????? WHEN MONTH(Started_at) = 4 THEN 'April'

??????????????? WHEN MONTH(Started_at) = 5 THEN 'May'

??????????????? WHEN MONTH(Started_at) = 6 THEN 'June'

??????????????? WHEN MONTH(Started_at) = 7 THEN 'July'

??????????????? WHEN MONTH(Started_at) = 8 THEN 'August'

??????????????? WHEN MONTH(Started_at) = 9 THEN 'September'

??????????????? WHEN MONTH(Started_at) = 10 THEN 'October'

??????????????? WHEN MONTH(Started_at) = 11 THEN 'November'

??????????????? WHEN MONTH(Started_at) = 12 THEN 'December'

END AS month,

????

CASE

?? WHEN(day_of_week) = 1 THEN 'Sunday'

?? WHEN(day_of_week) = 2 THEN 'Monday'

?? WHEN(day_of_week) = 3 THEN 'Tuesday'

?? WHEN(day_of_week) = 4 THEN 'Wednesday'

?? WHEN(day_of_week) = 5 THEN 'Thursday'

?? WHEN(day_of_week) = 6 THEN 'Friday'

?? WHEN(day_of_week) = 7 THEN 'Saturday'

?? END AS day,

CASE?

?????WHEN Month(started_at) IN (12,1,2) THEN 'Winter'

??????????????? WHEN Month(started_at) IN (3,4,5) THEN 'Spring'

??????????????? WHEN Month(started_at) IN (6,7,8) THEN 'Summer'

??????????????? WHEN Month(started_at) IN (9,10,11) THEN? 'Autumn'

??????????????? END AS season,

CASE?

????WHEN DATEPART("HOUR", started_at) IN (0,1,2,3,4,5) THEN 'Early Morning'

??????????????? WHEN DATEPART("HOUR", started_at) IN (6,7,8,9,10,11) THEN 'Morning'

??????????????? WHEN DATEPART("HOUR", started_at) IN (12,13,14,15,16) THEN 'Afternoon'

??????????????? WHEN DATEPART("HOUR", started_at) IN (17,18,19,20,21,22,23) THEN 'Evening'

??????????????? END AS time_of_day

FROM Bike_Trips.dbo.[cyclistic_bike_trips]

) AS rides]        

A new column "ride_duration" was also included in the newly created table using the DATEDIFF function, DATEDIFF(MINUTE, started_at, ended_at), since I couldn’t average the initial ride_length column created in Microsoft Excel without error.

7.1 Deletion

After creating the last table "new_cyclistic_trips", I deleted the individual tables from March 2022 to February 2023, using DROP TABLE statement.

7.2 Definition of Terms.

Ride_id - Unique alphanumeric characters assigned for every bike ride.

Bike_type - Refers to the type of bike used for each trip, there are three bike types - classic, electric, and docked.

User_type - Refers to the two categories of bike users or riders as I used interchangeably throughout this report, member and casual users.

Started_at - Refers to the time each bike ride started, comprising year, month, day, hour, minute, and seconds.

Ended_at - Refers to the time each bike ride ended, comprising year, month, day, hour, minute, and seconds.

Day_of_week and Day - The day the bike trip started.

Hour- Refers to the hour the bike ride began.

Ride_duration/Ride_length - Duration of the bike trip from when it started until it ended, calculated in minutes.

Month - Refers to the month of the year the bike ride began.

Season - The four seasons in a year: Winter, Spring, Summer, and Autumn.

Time_of_day -?The parts of the day: Early Morning, Morning, Afternoon, and Night.

?

8. Data Visualization and Analysis.

Tableau interactive dashboard here

I extracted the data from the "new_cyclistic_trips" table as a text file using SQL Server Import and Export wizard.

8.1 Share of Bike Rides by Users

From the 0 hours of March 1st, 2022 to 23hr:59mins:00secs of February 28th, 2023, a total of 4,416,102 bike rides were recorded, divided across two riders category (member and casual). Of the aforementioned bike rides, 2,651,331 bike rides were recorded by members, while casual riders made the remaining 1,764,771, representing 60.04% and 39.96% of all rides respectively. Thus, making member riders the most users of Cyclistic bike-share service.

8.2 Rides by Bike Type

Classic bike was the most used bike by both user types, 59.5% of all trips were on a classic bike, followed by electric bikes at 36.7%. However, docked bikes were used exclusively by casual riders, accounting for just 3.96% of all rides recorded.

Furthermore, of the 59.5% rides on classic bikes, member riders accounted for 39.2%, while casual riders made up the remaining 20.16%. Member riders also dominated electric bike usage at 20.78%, compared to 15.85% for casual users.?

8.3?Rides by Month

The summer months of June, July, and August saw the greatest number of bike rides for both user types, accounting for 41.6% of all bike trips for the period under review. On a user type base, member riders accounted for 22% of all rides during the summer, reaching peak ridership in the month of August(August alone accounted for 7.4% of all bike trips). 7.5% during the winter months of December, January, and February, 13.6% for the spring months of March, April, and May, finally 16.8% for the autumn months of September, October, and November.

In comparison, casual riders accounted for 19.4% of bike rides during the summer, reaching peak ridership in the month of July, constituting 6.9% of all bike rides. 2.1% during the winter, 8.5% during the spring, and 9.9% during the autumn season. Furthermore, the winter season was significantly less busy, accounting collectively for just 9.6% of all bike trips, but casual riders saw the most decline in ridership during the winter season, from a peak 19.4% of all summer rides to just 1.74% during the winter season.

8.4 Bike Trips during the Week

Overall, Saturday saw the most number of bike trips, both members and casual riders rode 705,712 times, 114,022 times more than on Monday which recorded the least number of rides. Individually, the most bike rides recorded for member riders was on Tuesday with 425,834(16% of all rides), Sunday with 303,578 rides was the least, 11% percentage-wise. For casual riders, Saturday was the busiest day with a total of 365,899(20.7%) trips recorded, while Tuesday recorded the least number of rides at 199,962(11.3%).

However, the weekend (Friday, Saturday, and Sunday) was particularly busy for casual riders, 52% of casual rides were on the weekends, compared to 48% on weekdays (Monday, Tuesday, Wednesday, and Thursday). For members it’s the opposite, the weekends accounted for 38% of bike trips, while 62% of bike trips were on weekdays. Collectively, the weekends especially Saturday and Sunday saw a higher average ride duration than any other day of the week, 20 minutes 43 seconds on Saturday and 20 minutes 39 seconds on Sunday.

8.5 Hourly Rides

The evenings (17:00-23:00) remained the busiest time of the day for both user types, followed by the afternoon (12:00-16:00). Though, 17:00(5 PM) turned out the most active hour of the day with a record 456,774 rides, representing 10.34% of all rides. ?

However, members rode significantly more in the morning compared to casual riders, the hours of 6:00 am to 10:00 am were busy for member riders, casual riders on the other hand, got active from 10:00 am until the peak of 5 pm.

8.6 Average Ride Duration

On average for the year under review, Cyclistic bike share riders rode for 17minutes:06seconds. Casual riders rode much longer on average for 23minutes:53seconds, while member riders rode much less on average for 12minutes:35seconds. Down to the hour, the average ride duration per hour for casual riders was also much longer than member riders, 28minutes:28seconds at 11 am for casual riders, compared to member riders' 13minutes:22seconds at 5 pm.

From the diagram above, the trajectories for average ride duration for both categories of riders share a similar pattern, a dip in ridership in the morning and then a peak towards noon. However, for member riders, the average ride duration per hour held steady with a minor drop in seconds from 0 hour, before a slightly significant drop at 5 am, from 6 am the recovery began until its peak at 5 pm. Casual riders on the hand, started off 0 hour on a high, before a significant drop at 7 am, its recovery into the 20minutes bracket didn’t start until 9 am, before peaking at 11 am.

9. Strategy Recommendation.

From all the analysis above, casual riders possess an identified pattern of usage, hence the recommendations below will be based on those patterns.

9.1 Seasonal Membership: The beauty of the subscription business model is the ease of offer customization, Cyclistic currently offers just three options (single-ride pass, full-day pass, and annual membership). However, casual riders were most active during the summer months, this presents an opportunity for Cyclistic to offer a membership option that captures the seasonality of this ride pattern. For example A three-month summer membership or a six-month membership plan from May to October.?

?9.1.1 Casual riders were also very active over the weekends, which presents an opportunity to design a weekend membership plan, and promotional rides that reflects this pattern of usage.

9.2 Loyalty Programs: A well-executed customer-centric reward program is essential in motivating and converting customers still on the fence, as well as retaining existing customers. In the case of Cyclistic, the duration and frequency of rides can be converted to redeemable points for purchases or ride discounts in the future. This clearly benefits casual riders as they rode much longer than member riders, and they did so on a single-ride pass or full-day pass, a membership subscription simply makes longer rides much cheaper.

9.3 Influencer Marketing: This involves a company like Cyclistic collaborating with an online influencer to market its product and service. Simply put, marketing with the intention to influence. In this case, finding an influencer(s) with the reputation and reach to collaborate with, his or her role will be to promote the peaks of a Cyclistic membership through social media posts, engage users in discussions, and make informative videos. The Influencer Marketing Benchmark Report 2023 revealed a $5.2 Return on Investment(ROI) for every $1 spent on influencer marketing for businesses, while some companies gain up to $18 earned media value (EMV) for every influencer dollar spent, although 25% either lose money or just break even.

?

9.4 Better Service: Cyclistic probably isn’t the only bike share company in Chicago, competition is a component of the modern business environment, however, one way to stay ahead of the competition is to offer a much better experience than others. Improved customer relationship management and friendly employees, the ease to find information about products and services are all components a better customer experience. Matter of fact, poor service according to a Customer Experience Impact Report by Oracle remains a major reason why 89% of customers switch over to the competition. Hence, its safe to assume that the allure of a much better service by Cyclistic makes a compelling case to casual riders to switch to a much longer plan.

?

10. Benefits of the Strategies to Cyclistic.

10.1 Consistent Revenue: An increase in the number of member riders ensures a predictable and fixed revenue for the company, helping it make much more accurate financial plans.

10.2 Brand Loyalty: Having customers locked in membership subscriptions in the current Cyclistic business model helps increase customer lifetime value(LTV), which naturally reduces the churn rate. It also creates an ecosystem for the company, an opportunity for upselling or cross-selling other products and services (merchandise, biking gears, unique biking experience, etc.). Furthermore, the loyalty of customers helps with word-of-mouth marketing, which reduces the cost of customer acquisition.

?

11. Value Proposition to Casual Riders.

11.1 Ease: The convenience and ease of annual, seasonal one-off payments, reward points, better biking experience and reduced costs that come with customized membership plans far outweigh the costs and inconvenience of multiple payments, card charges, and higher fees for bike rides on non-membership plans. The convenience of hopping on a bike, ride to your destination, drop off the bike at the end station and continue with your journey without worrying about payments is simply a luxury.

Conclusion?

The strategies recommended above are based on the patterns identified in the analysis and application of similar marketing strategies that proved successful in the past. Variables such as age bracket, income level, etc,will be very helpful in better understanding casual riders and designing a Cyclistic Bike-Share experience.

Reference:

Felicity Ozioma Nwanekwu (2022). How MTN XtraCool Made MTN Extra Cool.Source: https://www.dhirubhai.net/pulse/how-mtn-xtracool-made-extra-cool-felicity-ozioma-nwanekwu/?trk=pulse-article

Sumup. Subscription- What is a subscription? Source: https://www.sumup.com/en-gb/invoices/dictionary/subscription/

Halona Black (updated:2023). 16+ Customer Loyalty Programs of 2023 + How they work. Source: https://www.zendesk.com/blog/loyalty-rewards/

SuperOffice (updated: 2023). Customer Churn. 12 Ways to Stop Churn Immediately. Source: https://www.superoffice.com/blog/reduce-customer-churn/

Oracle (2012). 2011 Customer Experience Impact Report. Source: https://www.oracle.com/us/products/applications/cust-exp-impact-report-epss-1560493.pdf

The State of Influencer Marketing 2023: Benchmark Report. Source: https://influencermarketinghub.com/influencer-marketing-benchmark-report/

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?

?