# Bay Wheels San Francisco Bikeshare Analysis¶

### June 2021¶

This notebook analyzes a publicly available dataset on Google Cloud, bigquery-public-data.san_francisco.bikeshare_trips, from Bay Wheels. Part 1 focuses on understanding commuter trips (what Bay Wheels is most commonly used for). Part 2 (the more extensive part of the notebook) focuses on composing and addressing questions that showcase opportunities for business growth. In total I make four recommendations, articulated below.

In [1]:
import matplotlib.pyplot as plt


# Part 1: What are the 5 most popular trips that you would call "commuter trips"?¶

A commute is defined as "a regular journey of some distance to and from your place of work", and a commuter can be thought of as an individual participating in a commute. With respect to Bay Wheels trips as it relates to the previously stated definition, I require that a "commuter trip" consists of the following properties:

1) The type of subscriber must be a "Subscriber", not a "Customer".
2) The time of the trip must be during a regular morning or evening time (heading to and from work, respectively. The exact times of commutes is determined below).
3) Be at least 60 seconds long (to rule out bad data and to ensure that the trip was of a sufficient distance).
4) The day of the week must be a weekday (further verified below).

Using intuition that subscribers are likely going to be commuters, we can determine the most common days and times that subscribers use Bay Wheels.

In [2]:
%%bigquery commuter_days
SELECT  count(trip_id) AS ts, (EXTRACT (DAYOFWEEK FROM start_date) ) AS tday
FROM bigquery-public-data.san_francisco.bikeshare_trips
WHERE subscriber_type = 'Subscriber' AND duration_sec > 60
GROUP BY tday
ORDER BY ts DESC

Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 227.95query/s]

In [3]:
commuter_days

Out[3]:
ts tday
0 169654 3
1 165521 4
2 160285 5
3 154776 2
4 140041 6
5 31034 7
6 25463 1

It is apparent that "Subscribers" are riding on weekdays rather than weekends. Now let's examine the hours in which they are riding.

In [4]:
%%bigquery commuter_hours
SELECT  count(trip_id) AS ts, (EXTRACT (hour FROM start_date) ) AS thour
FROM bigquery-public-data.san_francisco.bikeshare_trips
WHERE subscriber_type = 'Subscriber' AND duration_sec > 60
GROUP BY thour
ORDER BY ts DESC

Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 571.35query/s]

In [5]:
commuter_hours.head(6)

Out[5]:
ts thour
0 127165 8
1 114906 17
2 89537 9
3 76048 16
4 75790 18
5 64943 7

Note the two main groups of times

• 7 AM, 8 AM, 9 AM
• 4 PM, 5 PM, 6 PM
In [6]:
plt.bar(commuter_hours['thour'], commuter_hours['ts'])
plt.title('Number of Trips vs Hour of Day')
plt.xlabel('Hour of Day')
plt.ylabel("Number of Trips")

Out[6]:
Text(0, 0.5, 'Number of Trips')

### Subscriber Trips Day and Time Findings¶

These findings back intuition that the most common days of the week and times of day for commutes are weekdays during the hours from 7 AM (inclusive) - 10 AM (exclusive) and 4 PM (inclusive) to 7 PM (exclusive).

## Final Query¶

The query below utilizes all four of the previously mentioned criteria, including the exact times of day that are most popular for subscribers to ride during. The five most common start and end stations as well as the number of trips that start and end at each one is output in the dataframe below.

In [7]:
%%bigquery commuter_trips_df

SELECT commute.start_station_id, commute.start_station_name, commute.end_station_id, commute.end_station_name, count(*) as trip_count FROM
(SELECT start_station_id, start_station_name, end_station_id, end_station_name, (EXTRACT (DAYOFWEEK FROM start_date)) AS day_of_week, (EXTRACT (hour FROM start_date)) AS thour
FROM bigquery-public-data.san_francisco.bikeshare_trips
WHERE subscriber_type = 'Subscriber' AND duration_sec > 60) commute
WHERE (day_of_week BETWEEN 2 AND 6) AND ((thour BETWEEN 7 AND 9) OR (thour BETWEEN 16 AND 18))
GROUP BY commute.start_station_name, commute.end_station_name, commute.start_station_id, commute.end_station_id
ORDER BY trip_count DESC LIMIT 5

Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 140.96query/s]

In [8]:
commuter_trips_df

Out[8]:
start_station_id start_station_name end_station_id end_station_name trip_count
0 61 2nd at Townsend 50 Harry Bridges Plaza (Ferry Building) 5034
1 50 Harry Bridges Plaza (Ferry Building) 61 2nd at Townsend 4973
2 69 San Francisco Caltrain 2 (330 Townsend) 65 Townsend at 7th 4958
3 60 Embarcadero at Sansome 74 Steuart at Market 4689
4 51 Embarcadero at Folsom 70 San Francisco Caltrain (Townsend at 4th) 4686

# Part 2: Recommendations to Drive Business Growth¶

There are two categories of recommendations for offers (just below) and a total of four offers (described in detail throughout the remained of this notebook) I suggest based on the data analysis I have completed to this point.

Below, I dive into specific recommendations within each category to increase revenue.

## Category 1: Alter the subscription membership to increase revenue¶

Below we will see that subscribers and customers use Bay Wheels differently. With this in mind, I recommend that we draw a clear distinction in the types of rides available to subscribers and customers, as well as promote an overlap that will encourage subscribers to view the platform for more than just commutes and standard customers to view the platform as more than just a way to enjoy a periodically long bike ride and instead use it for their everyday commute as well.

First, let us examine the length of time rides occur for subscribers vs customers

In [9]:
%%bigquery length_of_ride_df

SELECT subscriber_type, ROUND(AVG(duration_sec)/60, 2) AS minutes
FROM
bigquery-public-data.san_francisco.bikeshare_trips
GROUP BY subscriber_type

Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 618.36query/s]

In [10]:
length_of_ride_df

Out[10]:
subscriber_type minutes
0 Customer 61.98
1 Subscriber 9.71

Additionally, let's examine the number of customers vs subscribers we have using the platform.

In [11]:
%%bigquery subscription_counts_df

SELECT subscriber_type, COUNT(subscriber_type) total
FROM bigquery-public-data.san_francisco.bikeshare_trips
GROUP BY subscriber_type;

Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 748.18query/s]

In [12]:
subscription_counts_df

Out[12]:
subscriber_type total
0 Customer 136809
1 Subscriber 846839

Last, let's identify the days of the week that the subscription type of "customer" uses the platform. We can compare this to previous work, which identified the days of the week that "subscribers" use the platform.

In [13]:
%%bigquery customer_days_df
SELECT  count(trip_id) AS ts, (EXTRACT (DAYOFWEEK FROM start_date) ) AS tday
FROM bigquery-public-data.san_francisco.bikeshare_trips
WHERE subscriber_type = 'Customer' AND duration_sec > 60
GROUP BY tday
ORDER BY ts DESC

Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 865.70query/s]

In [14]:
customer_days_df

Out[14]:
ts tday
0 29242 7
1 25906 1
2 19928 6
3 16610 5
4 15237 4
5 15142 2
6 14737 3

### Recommendation 1: Alter the "Subscriber" Subscription to Only Work on Weekdays and Create Discounted Weekend Rides for These Users¶

As seen in part 1 and backed by the clear examples above that subscribers vs customers use the platform differently, I recommend Bay Wheels draw a clear distinction in membership for what the subscription service offers to its users. Specifically, I recommend that the subscription only work Monday - Friday. The individuals who are subscribers will still have their current needs met, which includes a morning and evening commute, but will allow for further income when they take weekend rides of a significant distance, as the customers do. At the moment, this does not seem to occur very often, as the least popular days of the week for rides for subscribers are Saturday and Sunday. However, for customers, these are the most popular days of the week for rides.

I recommend that Bay Wheels promote weekend rides via advertising on the platform to their subscribers and provide them with a small discount on these rides as well to promote the idea that Bay Wheels is not only a platform for commuting, but is also one that can provide a long, enjoyable weekend trip for an hour, as the customer accounts currently use the platform for. This may also positively alter subscriber opinions of Bay Wheels, as they will not only associate it with work, but also with an enjoyable weekend activity. This change in mindset will increase revenue and customer longevity.

### Recommendation 2: Offer Discounts to the "Customer" Users on a Subscription with Bay Wheels¶

The "customer" base for Bay Wheels is significantly smaller than their "subscriber" base. However, it still makes up approximately 14% of the total users of Bay Wheels, and therefore is a significant source of revenue. This could be further optimized by promoting discounts to these customers for their first month of a subscription. In converting them to a subscriber, this would allow them to likely continue to use the platform on the weekends (the most common days that the "customer" type uses the platform), as well as provide them with a new view that Bay Wheels can serve them almost daily in their work commute. This should increase their reliability on Bay Wheels and lead to an increase in revenue for the company.

## Category 2: Alter Offers in Areas with Supply / Demand Issues¶

We need to ensure that the areas with the highest demand have a supply (bikes available) that meets our user's needs. Specifically I begin by examining stations with peak commuter trips to ensure that during the morning and evening commute bikes are available for riders.

Below, we start off with examining the most popular starting locations for morning commutes (where we need to ensure availability).

### Morning¶

In [15]:
%%bigquery morning_commuter_trips_df

SELECT commute.start_station_id, commute.start_station_name, count(*) as trip_count FROM
(SELECT start_station_id, start_station_name, (EXTRACT (DAYOFWEEK FROM start_date)) AS day_of_week, (EXTRACT (hour FROM start_date)) AS thour
FROM bigquery-public-data.san_francisco.bikeshare_trips
WHERE subscriber_type = 'Subscriber' AND duration_sec > 60) commute
WHERE (day_of_week BETWEEN 2 AND 6) AND (thour BETWEEN 7 AND 9) -- morning trips on weekdays
GROUP BY commute.start_station_name, commute.start_station_id
ORDER BY trip_count DESC LIMIT 5

Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 520.19query/s]

In [16]:
morning_commuter_trips_df

Out[16]:
start_station_id start_station_name trip_count
0 70 San Francisco Caltrain (Townsend at 4th) 38003
1 69 San Francisco Caltrain 2 (330 Townsend) 29866
2 50 Harry Bridges Plaza (Ferry Building) 19198
3 55 Temporary Transbay Terminal (Howard at Beale) 18872
4 74 Steuart at Market 14510

Next, I utilize the above SQL query in a fairly complex series of subqueries to identify the availability of bikes at these popular stations throughout the day to determine if there is ample availability at these stations when they are needed.

In [17]:
%%bigquery bike_availability_morning_df

-- The outer query specifies a focus on the hour and availability of bikes
SELECT popular.thour,
AVG(popular.avg_bikes_available) AS num_available,
AVG(popular.percent_bikes_available) AS percent_available
FROM ( -- This query focuses on obtaining bike availability and dockcount at a given station to determine percent utilized
SELECT ba.thour,
AVG(ba.avg_available) AS avg_bikes_available,
(
AVG(ba.avg_available) / MAX(stat.dockcount) * 100
) AS percent_bikes_available
FROM (
SELECT station_id,
AVG(bikes_available) AS avg_available,
(
EXTRACT (
HOUR
FROM time
)
) AS thour
FROM bigquery-public-data.san_francisco.bikeshare_status
WHERE (EXTRACT (DAYOFWEEK FROM time)) BETWEEN 2 AND 6 -- get bikeshare availability for stations just on weekdays
GROUP BY station_id,
thour
) ba
-- use bikeshare station information for dockcount statistic
JOIN bigquery-public-data.san_francisco.bikeshare_stations stat ON stat.station_id = ba.station_id
GROUP BY ba.thour,
ba.station_id
HAVING ba.station_id IN ( -- Only use the stations that are of relevance (popular for commuting in the morning). This is the query above.
SELECT commute.start_station_id
FROM (
SELECT start_station_id,
start_station_name,
(
EXTRACT (
DAYOFWEEK
FROM start_date
)
) AS day_of_week,
(
EXTRACT (
hour
FROM start_date
)
) AS thour
FROM bigquery-public-data.san_francisco.bikeshare_trips
WHERE subscriber_type = 'Subscriber'
AND duration_sec > 60
) commute
WHERE ( -- weekdays
day_of_week BETWEEN 2 AND 6
)
AND ( -- mornings
thour BETWEEN 7 AND 9
)
GROUP BY commute.start_station_name,
commute.start_station_id
ORDER BY COUNT(*) DESC
LIMIT 5
)
ORDER BY avg_bikes_available DESC
) popular
GROUP BY popular.thour
ORDER BY popular.thour ASC

Query complete after 0.00s: 100%|██████████| 10/10 [00:00<00:00, 3120.07query/s]


Again, for clarity, this table illustrates the number of bikes available and percent of docks filled at the stations that have the most morning commutes.

In [18]:
bike_availability_morning_df

Out[18]:
thour num_available percent_available
0 0 15.228131 68.848268
1 1 15.231087 68.865031
2 2 15.231579 68.871273
3 3 15.228092 68.854741
4 4 15.234188 68.880244
5 5 15.250106 68.960696
6 6 15.026944 67.864735
7 7 13.771963 61.885668
8 8 9.813412 43.758221
9 9 6.103351 27.185420
10 10 6.443437 28.742926
11 11 6.684085 29.789424
12 12 7.092448 31.676778
13 13 7.363005 33.020186
14 14 7.532186 33.906649
15 15 6.817732 30.706087
16 16 7.375507 33.623611
17 17 11.260595 51.296357
18 18 14.083921 63.769402
19 19 14.200883 64.044058
20 20 14.186519 63.963514
21 21 14.720615 66.489775
22 22 14.875774 67.264192
23 23 14.957021 67.639485
In [19]:
plt.bar(bike_availability_morning_df['thour'], bike_availability_morning_df['num_available'])
plt.title('Bike Availability at Popular Morning Stations for Subscribers')
plt.xlabel('Hour of Day')
plt.ylabel("Bikes Available")

Out[19]:
Text(0, 0.5, 'Bikes Available')

We can see that there is a significant dip in the availability at these stations during the day, but at the end of the day, they return back to their previous state (likely users on their return commute) and the stations average approximately 2/3 full overnight and have plenty of bikes available at all times for their riders (an average of at least 6, even after the bikes are taken away by morning commuters).

Let's also examine the popular evening starting stations of subscribers for the station's bike availability throughout the day to see if any adjustments need to be made to supply these very popular stations.

### Evening¶

In [20]:
%%bigquery evening_commuter_trips_df

SELECT commute.start_station_id, commute.start_station_name, count(*) as trip_count FROM
(SELECT start_station_id, start_station_name, (EXTRACT (DAYOFWEEK FROM start_date)) AS day_of_week, (EXTRACT (hour FROM start_date)) AS thour
FROM bigquery-public-data.san_francisco.bikeshare_trips
WHERE subscriber_type = 'Subscriber' AND duration_sec > 60) commute
WHERE (day_of_week BETWEEN 2 AND 6) AND (thour BETWEEN 16 AND 18) -- evening trips on weekdays
GROUP BY commute.start_station_name, commute.start_station_id
ORDER BY trip_count DESC LIMIT 5

Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 910.62query/s]

In [21]:
evening_commuter_trips_df

Out[21]:
start_station_id start_station_name trip_count
0 65 Townsend at 7th 13502
1 70 San Francisco Caltrain (Townsend at 4th) 12826
2 61 2nd at Townsend 12736
3 77 Market at Sansome 10432
4 64 2nd at South Park 10135
In [22]:
%%bigquery bike_availability_evening_df

-- The outer query specifies a focus on the hour and availability of bikes
SELECT popular.thour,
AVG(popular.avg_bikes_available) AS num_available,
AVG(popular.percent_bikes_available) AS percent_available
FROM ( -- This query focuses on obtaining bike availability and dockcount at a given station to determine percent utilized
SELECT ba.thour,
AVG(ba.avg_available) AS avg_bikes_available,
(
AVG(ba.avg_available) / MAX(stat.dockcount) * 100
) AS percent_bikes_available
FROM ( -- get each stations availability by hour
SELECT station_id,
AVG(bikes_available) AS avg_available,
(
EXTRACT (
HOUR
FROM time
)
) AS thour
FROM bigquery-public-data.san_francisco.bikeshare_status
WHERE (EXTRACT (DAYOFWEEK FROM time)) BETWEEN 2 AND 6 -- get bikeshare availability for stations just on weekdays
GROUP BY station_id,
thour
) ba
-- use bikeshare station information for dockcount statistic
JOIN bigquery-public-data.san_francisco.bikeshare_stations stat ON stat.station_id = ba.station_id
GROUP BY ba.thour,
ba.station_id
HAVING ba.station_id IN ( -- Specify an interest in just the stations that are of relevance (popular for commuting in the evening)
SELECT commute.start_station_id
FROM (
SELECT start_station_id,
start_station_name,
(
EXTRACT (
DAYOFWEEK
FROM start_date
)
) AS day_of_week,
(
EXTRACT (
hour
FROM start_date
)
) AS thour
FROM bigquery-public-data.san_francisco.bikeshare_trips
WHERE subscriber_type = 'Subscriber'
AND duration_sec > 60
) commute
WHERE ( -- weekdays
day_of_week BETWEEN 2 AND 6
)
AND ( -- evenings
thour BETWEEN 16 AND 18
)
GROUP BY commute.start_station_name,
commute.start_station_id
ORDER BY COUNT(*) DESC
LIMIT 5
)
ORDER BY avg_bikes_available DESC
) popular
GROUP BY popular.thour
ORDER BY thour ASC

Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 457.14query/s]


This table illustrates the number of bikes available and percent of docks filled at the stations that have the most morning commutes.

In [23]:
bike_availability_evening_df

Out[23]:
thour num_available percent_available
0 0 11.007584 53.748136
1 1 11.020312 53.815902
2 2 11.021364 53.823425
3 3 11.012715 53.762890
4 4 11.017872 53.767621
5 5 11.219127 54.592072
6 6 11.032092 53.313853
7 7 9.435135 45.271648
8 8 8.650125 41.373112
9 9 9.432012 45.828075
10 10 10.065307 49.253869
11 11 9.875486 48.516860
12 12 9.670647 47.470195
13 13 10.015695 48.957432
14 14 10.277094 50.227116
15 15 9.874099 48.503829
16 16 9.706744 47.619328
17 17 9.623310 46.933410
18 18 10.020326 48.101342
19 19 10.378638 49.629140
20 20 10.604578 51.010118
21 21 10.894804 52.844103
22 22 11.052989 53.807587
23 23 11.091599 54.075963
In [24]:
plt.bar(bike_availability_evening_df['thour'], bike_availability_evening_df['num_available'])
plt.title('Bike Availability at Popular Evening Stations for Subscribers')
plt.xlabel('Hour of Day')
plt.ylabel("Bikes Available")

Out[24]:
Text(0, 0.5, 'Bikes Available')

There is surprisingly less of a dip in the availability at these stations in the evening when they are popular to start from. This must be caused by a flux of bikes also appearing in the station at that time as well.

### Recommendation 3: Alter Pricing at Specific Times of Day¶

It is clear that for popular commuter stations, specifically those in the morning, taking out a bike is more popular at certain times of day than others. Bay Wheels could encourage potential subscribers to try a ride to work with a notification on the Lyft application and a discount code. This should help increase the subscriber conversion rate, and it is clear that even though bikes are less available at these stations due to the morning commute, Bay Wheels still has enough in the area to optimize this further. If it is determined that not enough bikes are available and demand is too high (ex. if it is determined that the reason several bikes are consistently available at these stations is because they are broken down / not usable) or the previous changes cause demand to be too high, prices could be increased on "customer" rides at these times to increase revenue and support subscribers. During non-peak subscriber hours where the bikes are largely stagnant at these locations, discount codes could be offered to potential customers in the area through the Lyft application to encourage ridership, such as during lunch hour.

### A Need to Optimize Bikes at Unused Stations¶

Next, I examine stations that are popular and not popular to start a ride from. The query results indicate a vital problem that needs to be addressed: There are some stations that have been around for years, but still only have generated several hundred rides and have on average at least 5 bikes present. Meanwhile, others have generated tens of thousands of rides, and are therefore in much more popular areas and generate a greater amount of revenue.

In [25]:
%%bigquery identify_stagnant_stations

SELECT bs.station_id, si.total_trips, DATE_DIFF(DATE(si.max_end), DATE(si.min_start), DAY) AS days_of_operation, AVG(bikes_available) AS avg_available
FROM bigquery-public-data.san_francisco.bikeshare_status bs
JOIN (SELECT start_station_id, COUNT(*) AS total_trips, MIN(start_date) AS min_start, MAX(end_date) AS max_end FROM bigquery-public-data.san_francisco.bikeshare_trips GROUP BY start_station_id) si ON si.start_station_id = bs.station_id
GROUP BY bs.station_id, si.total_trips, si.min_start, si.max_end
ORDER BY total_trips ASC

Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 529.32query/s]


NOTE "days_of_operation" is calculated from the first trip that started from a station to the latest trip starting from that station. Therefore it is an imperfect metric in what it is being referred to as, but is nonetheless still useful.

In [26]:
identify_stagnant_stations.head(10)

Out[26]:
station_id total_trips days_of_operation avg_available
0 88 20 83 8.940100
1 91 69 26 5.129478
2 89 84 86 5.728563
3 90 173 27 16.644235
4 21 241 994 6.402220
5 24 272 1008 7.317582
6 23 373 1009 7.108827
7 26 463 1036 7.271412
8 83 467 846 6.373241
9 25 931 1034 6.787239
In [27]:
identify_stagnant_stations.tail(10)

Out[27]:
station_id total_trips days_of_operation avg_available
64 67 30209 1098 10.059101
65 65 34894 1098 7.578609
66 77 35142 1098 12.728522
67 74 38531 1098 12.161549
68 55 39200 1098 11.528794
69 61 39936 1098 13.401061
70 60 41137 1098 7.354661
71 50 49062 1098 13.335827
72 69 56100 1098 12.529002
73 70 72683 1098 10.989174
In [30]:
plt.scatter(identify_stagnant_stations['days_of_operation'], identify_stagnant_stations['total_trips'])
plt.title("Station Days in Operation vs Number of Trips from Station")
plt.xlabel("Days in Operation")
plt.ylabel("Number of Trips")

Out[30]:
Text(0, 0.5, 'Number of Trips')

### Recommendation 4: Create a New Offer on Bikes at Stagnant Stations¶

It is clear that there are many stations that have bikes that are not frequently being used. Whenever bikes are not being used, they are not generating revenue for Bay Wheels. While holding back a couple of bikes in less popular areas makes sense to ensure availability at all stations, whenever there is a surplus of bikes (at these less popular stations, I believe more than 4 would count as a surplus considering they are rarely being used), we could notify subscribers and potential customers in the area at a regular interval (once per week) that there is a bike in the area that is discounted for them (ex. 20%) to ride should they drop it off at a new station when they are done.

# Final Thoughts¶

The four recommendations are described above. All of them come from analyzing a fairly old dataset (August 29, 2013 to Agust 31, 2016), which will now include outdated information. Furthermore, the data itself is not clean. For example, it was identified during exploratory data analysis that station 87 was a station that had poor data with very little recorded data. Many of the other, newer stations such as station 91 also contained very few trips, and therefore should be examined differently and with additional caution compared to the stations with greater longevity. Furthermore, some of the data was not clean, such as station 22 having multiple names with just a slight misspelling. These were carefully considered during the analysis process in order to not cause errors or bias in the final results.

The four recommendations are again stated below, but please refer to them above for greater detail on each.

1) Recommendation 1: Alter the "Subscriber" Subscription to Only Work on Weekdays and Create Discounted Weekend Rides for These Users
2) Offer Discounts to the "Customer" Users on a Subscription with Bay Wheels
3) Alter Pricing at Specific Times of Day
4) Create a New Offer on Bikes at Stagnant Stations