Project Details

Cyclistic is a bike-share company operating a fleet of over 5800 bicycles and 692 stations in Chicago. Cyclistic's distinguishing feature is the flexibility it offers to customers. Apart from standard bikes, Cyclistic also rents reclining bikes, hand tricycles and cargo bikes to make bike-share more inclusive. Customers can also choose from among three of its plans: single-ride passes, full-day passes and annual memberships. Single-ride or full-day pass buyers are referred to as casual customers. Customers who purchase annual memberships are called Cyclistic members.

Annual membership sales are much more profitable than casual riders for Cyclistic. This case study was done with the goal of identifying market strategies aimed at converting casual riders into annual members. A detailed description of the project can be found here.

Dataset Description

• Rides data for the period from October 2020 to September 2022 was downloaded from this link.

• The dataset consists of 24 files with each file containing the details of every single ride for one month. The details of the ride include ride id, type of rideable, type of rental(casual or member),start time, end time, start station name and its id, end station name and its id, latitude and longitude of start and end locations.

Data Processing

• A python program was written to consolidate the entire dataset in one file to facilitate importing into SQL.

• The dataset was then queried for the count and sum of durations of trips on any given day grouped by rental type(member or casual) and rideable type.

• Since a few entries with negative duration were found, all trips with duration less than or equal to zero were omitted from the analysis. Similarly, all trips with duration greater than 24 hours were also excluded as irrelevant. 13,318 (0.12%) out of a total of 10,964,496 records were thus excluded in this way.

• The results of this query were output into a summary file. A python program to execute all of the above steps (in place of sql) can be found here.

• The number indicating the count of rides is used as a proxy for the number of members or casual customers.

• The duration of rides is used as a proxy for revenue from these rides.

• This summary was imported into Power BI and used in the rest of the analysis.

• A calculated field was created to filter the data into weeks so as to be able to view the data on a week-by-week basis if the analysis required it.

Visulisation and analysis

A number of visualisations were generated as part of the analysis process. The full workbook can be accessed here. The following conclusions were reached as a result of the case study

1.) Cyclistic has been relatively successful in increasing its membership with the bar graph of number of rides by members showing a healthy bump year to year. This increase will become more notable in light of the next point. Existing marketing strategies and price tiers need to continue.

2.) The number of passes purchased by casual customers has dropped in the third quarter of 2022 compared to a year ago. Some of this could be explained by the fact that 2021 saw a renewed interest in health and outdoor activities plausibly giving rise to a sudden increase in bike rentals. However, this base effect cannot account for the entire drop. In particular, a decrease of 20% in September 2022 from the previous year is concerning. Marketing strategies focused on increasing brand awareness among the general public such as ads on radio stations in Chicago and/or Facebook and YouTube ads localised to Chicago should be considered. First time customers can be offered one or more free rides initially in non-peak months from March to May.

3.) In any week, the duration of rides per rental is significantly higher for casual customers as compared to members. Casual customers can be sent messages via the app containing details like the number of miles or hours they travelled during the month, the charges they paid, and a calculation of their savings if they had purchased a membership.

4.) Casual customers also rent electric bikes in a greater proportion than members. Special tiers on electric bikes can be offered to casual customers who regularly rent electric bikes if they purchase a membership.