Introduction

This report was completed as part of the Google Data Analytics Professional Certificate. A hypothetical San Francisco bike share company wants to increase usage and subscribership to their platform. As such, the marketing team wants recommendations for new ad campaigns.

Three questions were raised to help the marketing team strategize for a new campaign to bring in more users:

How do casual riders and subscribers use the bike share differently?
How can the bike share acquire more subscribers?
How can the bike share use ads effectively to promote additional riders?

Solutions

A close range marketing approach may be applicable to promote usage around bike stations that are in close proximity to key tourism spots - such as the cruise and ferry terminals. It would also be an applicable strategy to promote campaigns around multi-modal commuter stations like the Caltrains.
Promotional ads to target weekends customers
The bike share has a disproportionate gender use ratio and could do better at promoting an inclusive product. Niche or gender neutral marketing campaigns on the Caltrain and Ferries targeting women and nonbinary populations would be good place to start. To increase subscribership Divvy could address the gender gap, as only 23% of subscribers are female. They could do this with targeted campaigns for millennial and gen z females and/or make the product more gender inclusive by understanding how women commute differently and then acting on that knowledge.

The data used was open sourced from BigQuery’s publicly available San Francisco bikeshare data ¹.

Cleaning data

The data contained missing values prior to 2018. It seems that the data was missing due to change in data collection prior to 2018. Given that I focused on the most current data where data collection was more thorough. I dropped birthyear < 1940 as these riders are outliers. There is a significant data loss in May and June of all years. I could not find the reasoning why there was no data for these months.

Define trips CTE used in downstream analysis:

with trips as (
  select
    case when subscriber_type = 'nan' then c_subscription_type else subscriber_type end as type
    , start_station_name  
    , end_station_name 
    , start_date
    , end_date
    , duration_sec
    , member_gender as gender
    , member_birth_year as birthyear
    , case when member_birth_year between 1946 and 1964 then'BBoomers'
      when member_birth_year between 1965 and 1980 then 'Gen X'
      when member_birth_year between 1981 and 1996 then 'Millennials'
      when member_birth_year between 1997 and 2010 then 'Gen Z'
      end as generation
  from `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips` 
  where start_date >= '2017-01-01'
    and member_birth_year > 1940
)

Missing data for month of May and June

select
  end_date
from trips
where extract(month from end_date) = 5
  or extract(month from end_date) = 6

Analysis

How do customers and subscribers use the bike share differently?

Visit this interactive and slow to load Tableau map outlining important bike docking stations: https://public.tableau.com/views/SFBikeShare_16699270600370/Sheet4?:language=en-US&:display_count=n&:origin=viz_share_link

Customers:

Ride mostly on weekends
The most popular docks are near the ferry and cruise terminals and show more traffic during the months of April and September. They show more consistent year-round usage with a dip in the winter
Given that the most popular docks are on weekends and near terminals one explanation is that the increase is tourist driven from cruise ships

select
  format_date('%A', start_date) as week_day
  , start_station_name
  , count(*) as num_trips
from trips
where type = 'Customer'
group by week_day, start_station_name
order by count(*) desc

select
  format_date('%B', start_date) as month
  , start_station_name
  , count(*) as cnt_customers
from trips
where type = 'Customer'
group by month, start_station_name
order by cnt_customers desc

Subscribers:

Ride mostly on weekdays
The most popular docks are near the Caltrain terminal
This could be commuter driven

select
  format_date('%B', start_date) as month
  , start_station_name
  , count(*) as cnt_subscribers
from trips
where type = 'Subsriber'
group by month, start_station_name
order by cnt_subscribers desc

select
  format_date('%A', start_date) as week_day
  , start_station_name
  , count(*) as num_trips
from trips
where type = 'Subsriber'
group by week_day, start_station_name
order by num_trips desc

How can the bike share acquire more subscribers and customers?

Addressing gender inequality in cycling:

SF bike share has an opportunity to increase sales by targeting specific demographics like gender. The product is disproportionately catered towards men. Finding ways to increase women usership could be one way to boost sales.
According to the Institution for Transportation and Development Policy, women on average travel differently due to societal roles - in part due to caregiving and domestic duties. Because of these responsibilities, women tend to “trip chain”, wherein they take multi-modal trips and combine multiple stops in one journey in order to complete a range of activities. ²
SF bike share could use this knowledge by making sure bikes have accommodations for mothers and caregivers. One possible recommendation would be to have bikes available with an extra seat in the back for children.

Breakdown of gender usage:

Female	Male	NonDisclosed
23%	76%	1%

select
  round(avg(case when gender = 'Male' then 1 else 0 end),2) as pct_male
  , round(avg(case when gender = 'Female' then 1 else 0 end),2) as pct_female
  , round(avg(case when gender= 'Other' then 1 else 0 end),2) as pct_nondisclosed
from trips
where gender is not null

Gender breaks down about the same by generation:

Generation	Female	Male	NonDisclosed
Baby Boomers	20%	79%	1%
Gen X	19%	79%	2%
Millennials	26%	73%	1%
Gen Z	27%	69%	4%

select
  generation
  , round(avg(case when gender= 'Female'then 1 else 0 end),2) as pct_female
  , round(avg(case when gender= 'Male' then 1 else 0 end),2) as pct_male
  , round(avg(case when gender= 'Other' then 1 else 0 end),2) as pct_other
from trips
where generation is not null
group by generation
order by generation

Knowing that only 23% of users are female we can look at hypothetical growth projections for gender. For A 10% growth Divvy bikes could yield a 2.22% growth (.23 * .10).

10% Growth	25% Growth	50% Growth
2.22%	5.56%	11.11%

, counts as (
  select
    sum(case when type ='Subscriber' and gender = 'Female' then 1 else 0 end) as female_cnt
    ,sum(case when type = 'Subscriber' and gender ='Male' then 1 else 0 end) as male_cnt
  from trips
)

select
    (female_cnt * 0.1) / (female_cnt + male_cnt) as _10prc
  , (female_cnt * 0.25) / (female_cnt + male_cnt) as _25prc
  , (female_cnt * 0.50) / (female_cnt + male_cnt) as _50prc
 from counts

Targeting weekend customers

There is an uptick in Customer use during weekends and a down tick in Subscribers

select
  format_date('%A', start_date) as week_day
  , sum(case when type = 'Subscriber' then 1 else 0 end) as num_subscribers
  , sum( case when type = 'Customer' then 1 else 0 end) as num_customers
from trips
group by week_day
order by num_subscribers desc, num_customers desc

One way to increase usage is by trying to target weekend users to subscribe

Hypothetical growth projections for targeting weekend customers:

10% Growth	25% Growth	50% Growth
1.35%	3.3%	6.7%

, weekend_customers as (
  select
    avg(case when extract(dayofweek from start_date) in (1,7) and type ='Customer' then 1 else 0 end) as weekend_cnt
  , avg(case when extract(dayofweek from start_date) in (2,3,4,5,6) and type ='Customer' then 1 else 0 end) as weekday_cnt
  from trips
)

select
  (weekend_cnt *.10)/(weekend_cnt + weekday_cnt) as _10prc
  , (weekend_cnt *.25)/(weekend_cnt + weekday_cnt) as _25prc
  , (weekend_cnt *.50)/(weekend_cnt + weekday_cnt) as _50prc
from weekend_customers

Tourists

Uptick in usage around the ferry and cruise terminals during the month of August is an indication of tourism.
Embacarcadero at Sansome St is a light rail station close to the pier where cruise ships port.

, monthly_station_trips as (
  select
    extract(month from start_date) as month
    , start_station_name as station
    , sum(case when type ='Customer' then 1 else 0 end) as num_trips
  from trips
  group by month, station
  order by month, station
)

select 
  month 
  , station
  , num_trips
  , row_number() over(partition by month order by num_trips desc) as station_rank
from monthly_station_trips
qualify station_rank = 1
order by month, num_trips desc

Uptick in usage around the ferry and cruise terminals during the month of August is an indication of tourism.

, monthly_station_trips as (
  select
    extract(month from start_date) as month
    , start_station_name as station
    , count(*) as num_trips
  from trips
  group by month, station
  order by month, station
)

select 
  month 
  , station
  , num_trips
  , row_number() over(partition by month order by num_trips desc) as station_rank
from monthly_station_trips
qualify station_rank = 1
order by month, num_trips desc

Tourists might not necessarily ever become subscribers but marketing to this demographic could reach potential customers for day use.

Conclusion

How can the bike share use ads effectively to promote additional riders?

Hypothetical Growth	Weekend Customers	Women
10%	1.35%	2.22%
25%	3.37%	5.56%
50%	6.75%	11.11%

A close range marketing approach may be applicable to promote usage around bike stations that are in close proximity to key tourism spots - such as the cruise and ferry terminals. It would also be an applicable strategy to promote campaigns around multi-modal commuter stations like the Caltrains.
Promotional ads to target weekends customers
The bike share has a disproportionate gender use ratio and could do better at promoting an inclusive product. Niche or gender neutral marketing campaigns on the Caltrain and Ferries targeting women and nonbinary populations would be good place to boost sales. By addressing the gender gap with targeted campaigns for millennial and gen z potential users would help to promote growth with those demographics.

Appendix

The analysis was conducted using open sourced from BigQuery’s publicly available San Francisco bikeshare trips data.
This analysis used the most recent data (2017/2018) as there was a lot of missing data previous to 2017. The data was cleaned and analyzed using big query sql. You can access the analysis here: https://console.cloud.google.com/bigquery?sq=1049850404938:808d49aa51a84fa78c81a9debd0e8473
Tableau visuals :https://public.tableau.com/views/SFBikeShare_16699270600370/Sheet4?
Gender Inequality in Cycling: https://www.itdp.org/2022/07/06/cyclings-gender-gap/