Jeremy Bosse Project Manager - Google Data Analytics Capstone Project

Business Scenario

Cyclistic, a Chicago-based Bike Share innovator, invites riders to explore the city on public bikes through single-ride passes, full-day passes, or exclusive annual memberships. Recognizing the untapped potential in annual subscriptions, our data analytics director is steering the company toward profitability. To propel this shift, our marketing analytics team is delving into user behavior, crafting a strategic campaign to transition casual riders into dedicated annual subscribers seamlessly.

Business Questions

Three questions are being used to guide the marketing program:

1. How do annual members and casual riders use Cyclistic bikes differently?

2. Why would casual riders buy Cyclistic annual memberships?

3. How can Cyclistic use digital media to influence casual riders to become members?

Business Task

Develop a marketing strategy to maximize membership conversion from a casual rider to an annual subscriber by identifying variances in rider activity and an effective digital marketing medium to advertise to customers.

Data Preparation

I utilize the Divvy System Public Dataset, comprising data from the last 12 months, securely stored on my personal computer. This extensive dataset, structured longitudinally with 13 fields and over 4,000 rows, includes attributes such as ride_id, rideable_type, started_at, and more. Acquired through a Data License Agreement with Lyft Bikes and Scooters, LLC, and officially provided by the City of Chicago, this data source maintains consistency and impartiality. Adhering strictly to licensing, privacy, security, and accessibility protocols as a public access user, I employ this dataset for analytical purposes within non-commercial contexts. It is a valuable resource for examining variations in the utilization of Cyclistic bikes between annual and casual members, given its rich information on usage dates and times, location data, rideable types, and membership categories.

Opting for BigQuery for data analysis is a natural choice due to my familiarity with this Database Management System (DBMS), particularly given the substantial size of the datasets. As for data visualization and comprehensive analysis, the rich capabilities of Tableau make it the preferred tool, offering robust features to enhance the interpretation and presentation of insights.

Data Processing in EXCEL:

- Formatted the 'started_at' and 'ended_at' to 'mm/dd/yyyy hh:mm:ss' for a customized presentation.
- Standardized the format of the 'ride_id,' 'rideable_type,' 'start_station_name,' 'start_station_id,' 'end_station_name,' 'end_station_id,' 'start_lat,' 'start_lng,' 'end_lat,' 'end_lng,' 'member_casual' fields to 'General.'
- Introduced a 'ride_length' column, formatted to 'hh:mm:ss,' capturing the duration of each ride.
- Established a 'month' column for temporal categorization.
- Implemented a 'day_of_week' column for enhanced time-related insights.
- Incorporated a 'year' column to capture the temporal dimension of the dataset.

Data Processing

Initial Observations

Upon scrutinizing the data tables, noteworthy parallels and subtle distinctions emerged in the activity patterns of members and casual users throughout the year. It's apparent that both cohorts exhibit heightened activity during the spring and summer months, coinciding with longer average ride lengths, likely influenced by warmer temperatures and increased leisure time availability. While shared preferences include a predilection for afternoon rides over evening rides, discernible behavioral divergences surface in terms of mid-week versus weekend riding preferences between the two groups.

Full Year Analysis

Exploratory Data Analysis

Casual riders consistently maintain a higher average ride length than members throughout the year, peaking for both groups between April and July. Additionally, there is a shared increase in average ride length observed on weekends, specifically between Saturday and Sunday.

Query & Query Output

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/24/2023
DESCRIPTION: This statement creates a table of average monthly ride lengths by membership type using an aggregate function, a LEFT JOIN clause, a GROUP BY statement, and an ORDER BY keyword.
*/

SELECT
F.month,
F.year,
A.casual_avg,
B.member_avg,
AVG(F.ride_length) AS total_avg
FROM
`bike_share_rides.full_year` AS F
LEFT JOIN
`bike_share_rides.casual_rider_avg` AS A
ON
F.month = A.month
LEFT JOIN
`bike_share_rides.member_rider_avg` AS B
ON
A.month = B.month
GROUP BY
F.month,
F.year,
A.casual_avg,
B.member_avg
ORDER BY
F.year ASC,
F.month ASC;

month   year   casual_avg   member_avg   total_avg
4   2022   0-0 0 0:23:42.701456291   0-0 0 0:11:20.368975460   0-0 0 0:15:33.146613189
5   2022   0-0 0 0:25:52.694737067   0-0 0 0:13:3.474643313   0-0 0 0:18:43.236327235
6   2022   0-0 0 0:23:55.966749222   0-0 0 0:13:39.227788218   0-0 0 0:18:35.127658633
7   2022   0-0 0 0:23:33.936068819   0-0 0 0:13:25.939047879   0-0 0 0:18:25.736440340
8   2022   0-0 0 0:22:4.131877286   0-0 0 0:13:5.058421545   0-0 0 0:17:11.245522109
9   2022   0-0 0 0:20:35.033741835   0-0 0 0:12:38.057246512   0-0 0 0:15:59.839666063
10   2022   0-0 0 0:18:58.412339464   0-0 0 0:11:32.818043255   0-0 0 0:14:19.503254988
11   2022   0-0 0 0:16:4.147706631   0-0 0 0:10:51.872904067   0-0 0 0:12:25.036387972
12   2022   0-0 0 0:14:0.844255357   0-0 0 0:10:20.761649818   0-0 0 0:11:15.107427697
1   2023   0-0 0 0:14:16.594056188   0-0 0 0:10:4.459542360   0-0 0 0:10:57.467133646
2   2023   0-0 0 0:16:37.874209596   0-0 0 0:10:28.210692677   0-0 0 0:11:51.707394299
3   2023   0-0 0 0:16:1.089001784   0-0 0 0:10:12.021651389   0-0 0 0:11:35.957425834

Both groups prefer riding bikes between Saturday and Sunday on the weekends. Casual Rider activity mid-week drops significantly more than that of Member riders, indicated by the curvature of the trendline.

Query & Query Output

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/24/2023
DESCRIPTION: This statement creates a table with average ride lengths per day within the full year of data using a CREATE TABLE statement, an aggregate function, and a GROUP BY clause.
*/

CREATE TABLE `bike_share_rides.daily_casual_avg_fy`
AS
SELECT
day_of_week,
AVG(ride_length) AS daily_casual_avg_fy
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'casual'
GROUP BY
day_of_week;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/24/2023
DESCRIPTION: This statement creates a table with average ride lengths per day within the full year of data using a CREATE TABLE statement, an aggregate function, and a GROUP BY clause.
*/

CREATE TABLE `bike_share_rides.daily_member_avg_fy`
AS
SELECT
day_of_week,
AVG(ride_length) AS daily_member_avg_fy
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'member'
GROUP BY
day_of_week;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/24/2023
DESCRIPTION: This statement creates a table of average ride lengths by day, month, and membership type using an aggregate function, a LEFT JOIN clause, a GROUP BY statement, and an ORDER BY keyword.
*/

SELECT
F.day_of_week,
C.daily_casual_avg_fy AS casual,
M.daily_member_avg_fy AS member,
AVG(ride_length) AS daily_member_casual_avg
FROM
`bike_share_rides.full_year` AS F
LEFT JOIN
`bike_share_rides.daily_casual_avg_fy` AS C
ON
F.day_of_week = C.day_of_week
LEFT JOIN
`bike_share_rides.daily_member_avg_fy` AS M
ON
C.day_of_week = M.day_of_week
GROUP BY
day_of_week,
C.daily_casual_avg_fy,
M.daily_member_avg_fy
ORDER BY
day_of_week;

day_of_week casual member   daily_member_casual_avg
1   0-0 0 0:25:11.584917046   0-0 0 0:13:29.810395125   0-0 0 0:19:16.891142838
2   0-0 0 0:22:6.529036025   0-0 0 0:11:42.823648742   0-0 0 0:15:28.997341695
3   0-0 0 0:19:36.026296006   0-0 0 0:11:36.894500435   0-0 0 0:14:16.293785996
4   0-0 0 0:18:34.750207834   0-0 0 0:11:36.437039681   0-0 0 0:13:57.077683813
5   0-0 0 0:19:33.198922178   0-0 0 0:11:48.287998838   0-0 0 0:14:36.247404377
6   0-0 0 0:20:42.724356248   0-0 0 0:12:0.435235804   0-0 0 0:15:34.797038741
7   0-0 0 0:24:43.264173513   0-0 0 0:13:38.372609344   0-0 0 0:19:17.784396542

Both Groups prefer to ride bikes between Spring and Fall. Member riders show higher sustained activity throughout the year. Members ride significantly more often than Casual riders during the Fall and Winter.

Query & Query Output

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/25/2023
DESCRIPTION: This statement creates a table showing casual riders' total monthly ride count using an aggregate function and a GROUP BY clause.
*/

CREATE TABLE bike_share_rides.total_monthly_rides_casual AS

SELECT
month,
COUNT(ride_id) AS monthly_casual_rides
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'casual'
GROUP BY
month;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/25/2023
DESCRIPTION: This statement creates a table showing member riders' total monthly ride count using an aggregate function and a GROUP BY clause.
*/

CREATE TABLE bike_share_rides.total_monthly_rides_member AS

SELECT
month,
COUNT(ride_id) AS monthly_member_rides
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'member'
GROUP BY
month;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/25/2023
DESCRIPTION: This statement creates a table showing member and casual riders' total monthly ride count in order of month and year using a LEFT JOIN keyword, an aggregate function, a GROUP BY clause, and an ORDER BY keyword.
*/

SELECT
F.month,
F.year,
C.monthly_casual_rides,
M.monthly_member_rides,
COUNT(ride_id) AS monthly_total_rides
FROM
`bike_share_rides.full_year` AS F
LEFT JOIN
`bike_share_rides.total_monthly_rides_casual` AS C
ON
F.month = C.month
LEFT JOIN
`bike_share_rides.total_monthly_rides_member` AS M
ON
C.month = M.month
GROUP BY
F.month,
F.year,
C.monthly_casual_rides,
M.monthly_member_rides
ORDER BY
year ASC,
month ASC;

month   year   monthly_casual_rides   monthly_member_rides   monthly_total_rides
4   2022   126417   244832   371249
5   2022   280414   354443   634857
6   2022   369044   400148   769192
7   2022   406046   417426   823472
8   2022   358917   427000   785917
9   2022   296694   404636   701330
10   2022   208988   349693   558681
11   2022   100747   236947   337694
12   2022   44894   136912   181806
1   2023   40008   150293   190301
2   2023   43016   147428   190444
3   2023   62201   196477   258678

Member riders ride more frequently than casual riders mid-week; casual riders ride more frequently during the weekend.

Query & Query Output

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/25/2023
DESCRIPTION: This statement creates a table showing casual riders' total daily ride count in order of the day, using an aggregate function, a GROUP BY clause, and an ORDER BY keyword.
*/

CREATE TABLE `bike_share_rides.daily_casual_ride_count_fy`
AS
SELECT
day_of_week,
COUNT(ride_id) AS daily_casual_ride_count_fy
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'casual'
GROUP BY
day_of_week
ORDER BY
day_of_week ASC;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/25/2023
DESCRIPTION: This statement creates a table showing member riders' total daily ride count in order of the day, using an aggregate function, a GROUP BY clause, and an ORDER BY keyword.
*/

CREATE TABLE `bike_share_rides.daily_member_ride_count_fy`
AS
SELECT
day_of_week,
COUNT(ride_id) AS daily_member_ride_count_fy
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'member'
GROUP BY
day_of_week
ORDER BY
day_of_week ASC;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/25/2023
DESCRIPTION: This statement creates a table showing casual and member riders' total daily ride count in order of the day, using an aggregate function, a GROUP BY clause, and an ORDER BY keyword.
*/

SELECT
F.day_of_week,
C.daily_casual_ride_count_fy AS casual_total,
M.daily_member_ride_count_fy AS member_total,
COUNT(ride_id) AS total_daily_count
FROM
`bike_share_rides.full_year` AS F
LEFT JOIN
`bike_share_rides.daily_casual_ride_count_fy` AS C
ON
F.day_of_week = C.day_of_week
LEFT JOIN
`bike_share_rides.daily_member_ride_count_fy` AS M
ON
C.day_of_week = M.day_of_week
GROUP BY
F.day_of_week,
C.daily_casual_ride_count_fy,
M.daily_member_ride_count_fy
ORDER BY
F.day_of_week;

day_of_week   casual_total   member_total   total_daily_count
1   389433   397975   787408
2   275692   484567   760259
3   271372   544334   815706
4   276662   546225   822887
5   311740   551155   862895
6   341009   489854   830863
7   471478   452125   923603

Members show more activity during the day between 4:00 AM and 11:00 PM. Member activity peaks during rush hours between 6:00 AM and 8:00 AM and 4:00 PM and 7:00 PM.

Query & Query Output

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/28/2023
DESCRIPTION: Using an EXTRACT function, a COUNT function, a WHERE clause, a GROUP BY clause, and an ORDER BY keyword, this statement creates a table showing a count of casual rides in order of the hour of the day.
*/

CREATE TABLE bike_share_rides.hourly_rider_traffic_casual AS
SELECT
extract(HOUR FROM started_at) AS hour_of_day,
COUNT(ride_id) AS casual_rider_traffic
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'casual'
GROUP BY
hour_of_day
ORDER BY
hour_of_day

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/28/2023
DESCRIPTION: Using an EXTRACT function, a COUNT function, a WHERE clause, a GROUP BY clause, and an ORDER BY keyword, this statement creates a table showing a count of member rides in order of the hour of the day.
*/

CREATE TABLE bike_share_rides.hourly_rider_traffic_member AS
SELECT
extract(HOUR FROM started_at) AS hour_of_day,
COUNT(ride_id) AS member_rider_traffic
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'member'
GROUP BY
hour_of_day
ORDER BY
hour_of_day

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/28/2023
DESCRIPTION: Using an EXTRACT function, a COUNT function, a GROUP BY clause, and an ORDER BY keyword, this statement creates a table showing a count of total rides in order of the hour of the day.
*/

CREATE TABLE bike_share_rides.hourly_rider_traffic_total AS
SELECT
extract(HOUR FROM started_at) AS hour_of_day,
COUNT(ride_id) AS total_hourly_rider_traffic
FROM
`bike_share_rides.full_year`
GROUP BY
hour_of_day
ORDER BY
hour_of_day

/*
CREATED BY: Jeremy Bosse
CREATED ON: 6/28/2023
DESCRIPTION: Using a LEFT JOIN keyword, a GROUP BY clause, and an ORDER BY keyword, this statement creates a table showing a count of casual rides per hour, a count of member rides per hour, and a count of total rides per hour in order of the hour of the day.
*/

SELECT
F.hour_of_day,
C.casual_rider_traffic,
M.member_rider_traffic,
F.total_hourly_rider_traffic
FROM
`bike_share_rides.hourly_rider_traffic_total` AS F
LEFT JOIN
`bike_share_rides.hourly_rider_traffic_casual` AS C
ON
F.hour_of_day = C.hour_of_day
LEFT JOIN
`bike_share_rides.hourly_rider_traffic_member` AS M
ON
C.hour_of_day = M.hour_of_day
GROUP BY
F.hour_of_day,
F.total_hourly_rider_traffic,
C.casual_rider_traffic,
M.member_rider_traffic
ORDER BY
F.hour_of_day

hour_of_day   casual_rider_traffic   member_rider_traffic   total_hourly_rider_traffic
0   46673   37129   83802
1   30146   22692   52838
2   18750   13246   31996
3   11105   8090   19195
4   7716   9137   16853
5   12834   32791   45625
6   30789   96387   127176
7   53499   179918   233417
8   72001   215787   287788
9   73766   151008   224774
10   93952   141086   235038
11   122230   167891   290121
12   144424   192861   337285
13   150779   192047   342826
14   160479   191089   351568
15   178857   230387   409244
16   199424   303726   503150
17   221099   363340   584439
18   197290   292886   490176
19   151781   211932   363713
20   112410   148801   261211
21   96241   116782   213023
22   86575   88907   175482
23   64566   58315   122881

Both groups prefer to ride bikes during the afternoon. Member riders show more activity throughout the day.

Query & Query Output

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION:
*/

CREATE TABLE bike_share_rides.member_casual_use_by_hour AS
SELECT
extract(HOUR FROM started_at) AS hour_of_day,
member_casual
FROM
`bike_share_rides.full_year`

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a SUM function wrapped around a CASE statement, this query produces a table of a count of casual rides in the morning, a count of casual rides in the afternoon, a count of member rides in the morning, and a count of member rides in the afternoon. In addition, this statement features a COUNT function which produces a count of total rides.
*/

SELECT
SUM(CASE WHEN hour_of_day BETWEEN 0 AND 11 AND member_casual = 'casual' THEN 1 ELSE 0 END) AS casual_morning_rides,
SUM(CASE WHEN hour_of_day BETWEEN 12 AND 23 AND member_casual = 'casual' THEN 1 ELSE 0 END) AS casual_afternoon_rides,
SUM(CASE WHEN hour_of_day BETWEEN 0 AND 11 AND member_casual = 'member' THEN 1 ELSE 0 END) AS member_morning_rides,
SUM(CASE WHEN hour_of_day BETWEEN 12 AND 23 AND member_casual = 'member' THEN 1 ELSE 0 END) AS member_afternoon_rides,
COUNT(member_casual) AS total_rides
FROM
`bike_share_rides.member_casual_use_by_hour`

casual_morning_rides casual_afternoon_rides member_morning_rides member_afternoon_rides total_rides
573461 1763925 1075162 2391073 5803621

Member riders show higher activity across all rideable types except "Docked Bike," where members show no activity.

Query & Query Output

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a WHERE clause, and a GROUP BY clause, this statement creates a table with a count of casual rides by rideable type.
*/

CREATE TABLE bike_share_rides.casual_rideable_type AS
SELECT
rideable_type,
COUNT(ride_id) AS casual_users
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'casual'
GROUP BY
rideable_type;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a WHERE clause, and a GROUP BY clause, this statement creates a table with a count of member rides by rideable type.
*/

CREATE TABLE bike_share_rides.member_rideable_type AS
SELECT
rideable_type,
COUNT(ride_id) AS member_users
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'member'
GROUP BY
rideable_type;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a LEFT JOIN keyword, and a GROUP BY clause, this statement creates a table with a count of casual rides by rideable type, member rides by rideable type, and total rides by rideable type.
*/

SELECT
F.rideable_type,
C.casual_users,
M.member_users,
COUNT(ride_id) AS rides_per_bike
FROM
`bike_share_rides.full_year` AS F
LEFT JOIN
`bike_share_rides.casual_rideable_type` AS C
ON
F.rideable_type = C.rideable_type
LEFT JOIN
`bike_share_rides.member_rideable_type` AS M
ON
C.rideable_type = M.rideable_type
GROUP BY
rideable_type,
C.casual_users,
M.member_users;

rideable_type   casual_users   member_users   rides_per_bike
electric_bike   1273764   1716578   2990342
classic_bike   889875   1749657   2639532
docked_bike   173747       173747

Casual riders consistently show more activity between Spring and Fall. Activity drops significantly during the winter months. Member riders did not use docked bikes.

Query & Query Output

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a WHERE clause, a GROUP BY clause, and an ORDER BY keyword, this statement creates a table with a count of casual riders using the docked bike in order of the month and year.
*/

CREATE TABLE bike_share_rides.docked_bike_casual AS
SELECT
month,
year,
rideable_type,
COUNT(ride_id) AS rideable_type_trend_casual
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'casual'
AND
rideable_type = 'docked_bike'
GROUP BY
month,
year,
rideable_type
ORDER BY
year ASC,
month ASC;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a WHERE clause, a GROUP BY clause, and an ORDER BY keyword, this statement creates a table with a count of member riders using the docked bike in order of the month and year.
*/

CREATE TABLE bike_share_rides.docked_bike_member AS
SELECT
month,
year,
rideable_type,
COUNT(ride_id) AS rideable_type_trend_member
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'member'
AND
rideable_type = 'docked_bike'
GROUP BY
month,
year,
rideable_type
ORDER BY
year ASC,
month ASC;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a LEFT JOIN keyword, a WHERE clause, a GROUP BY clause, and an ORDER BY keyword, this table results in a count of casual riders, a count of member riders, and total riders who used a docked bike in order of the month and year.
*/

SELECT
F.rideable_type,
F.month,
F.year,
C.rideable_type_trend_casual AS casual_use,
M.rideable_type_trend_member AS member_use,
COUNT(ride_id) AS total_use
FROM
`bike_share_rides.full_year` AS F
LEFT JOIN
`bike_share_rides.docked_bike_casual` AS C
ON
F.month = C.month
AND
F.year = C.year
LEFT JOIN
`bike_share_rides.docked_bike_member` AS M
ON
C.month = M.month
AND
C.year = M.year
WHERE
F.rideable_type = 'docked_bike'
GROUP BY
F.rideable_type,
F.month,
F.year,
C.rideable_type_trend_casual,
M.rideable_type_trend_member
ORDER BY
F.year ASC,
F.month ASC

rideable_type   month   year   casual_use   member_use   total_use
docked_bike   4   2022   12116       12116
docked_bike   5   2022   26409       26409
docked_bike   6   2022   30640       30640
docked_bike   7   2022   31055       31055
docked_bike   8   2022   26323       26323
docked_bike   9   2022   19826       19826
docked_bike   10   2022   12614       12614
docked_bike   11   2022   5886       5886
docked_bike   12   2022   1925       1925
docked_bike   1   2023   1738       1738
docked_bike   2   2023   2195       2195
docked_bike   3   2023   3020       3020

Member riders show higher preference than Casual riders who also use the Classic bike.

Query & Query Output

/*

CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a WHERE clause, a GROUP BY clause, and an ORDER BY keyword, this statement creates a table with a count of casual riders using the classic bike in order of the month and year.
*/

CREATE TABLE bike_share_rides.classic_bike_casual AS
SELECT
month,
year,
rideable_type,
COUNT(ride_id) AS rideable_type_trend_casual
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'casual'
AND
rideable_type = 'classic_bike'
GROUP BY
month,
year,
rideable_type
ORDER BY
year ASC,
month ASC;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a WHERE clause, a GROUP BY clause, and an ORDER BY keyword, this statement creates a table with a count of member riders using the classic bike in order of the month and year.
*/

CREATE TABLE bike_share_rides.classic_bike_member AS
SELECT
month,
year,
rideable_type,
COUNT(ride_id) AS rideable_type_trend_member
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'member'
AND
rideable_type = 'classic_bike'
GROUP BY
month,
year,
rideable_type
ORDER BY
year ASC,
month ASC;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a LEFT JOIN keyword, a WHERE clause, a GROUP BY clause, and an ORDER BY keyword, this table results in a count of casual riders, a count of member riders, and total riders who used a classic bike in order of the month and year.
*/

SELECT
F.rideable_type,
F.month,
F.year,
C.rideable_type_trend_casual AS casual_use,
M.rideable_type_trend_member AS member_use,
COUNT(ride_id) AS total_use
FROM
`bike_share_rides.full_year` AS F
LEFT JOIN
`bike_share_rides.classic_bike_casual` AS C
ON
F.month = C.month
AND
F.year = C.year
LEFT JOIN
`bike_share_rides.classic_bike_member` AS M
ON
C.month = M.month
AND
C.year = M.year
WHERE
F.rideable_type = 'classic_bike'
GROUP BY
F.rideable_type,
F.month,
F.year,
C.rideable_type_trend_casual,
M.rideable_type_trend_member
ORDER BY
F.year ASC,
F.month ASC

rideable_type   month   year   casual_use   member_use   total_use
classic_bike   4   2022   47543   119169   166712
classic_bike   5   2022   126075   197971   324046
classic_bike   6   2022   169996   236664   406660
classic_bike   7   2022   156089   217074   373163
classic_bike   8   2022   128634   215415   344049
classic_bike   9   2022   105375   200766   306141
classic_bike   10   2022   61568   151991   213559
classic_bike   11   2022   33044   111543   144587
classic_bike   12   2022   12652   60698   73350
classic_bike   1   2023   13909   76385   90294
classic_bike   2   2023   15534   74354   89888
classic_bike   3   2023   19456   87627   107083

Electric bikes attract more casual riders in peak summer, while members exhibit increased activity compared to casual riders during summer to winter. This shift in group activity is likely influenced by the heightened demand for electric bikes in warmer weather.

Query & Query Output

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a WHERE clause, a GROUP BY clause, and an ORDER BY keyword, this statement creates a table with a count of casual riders using the electric bike in order of the month and year.
*/

CREATE TABLE bike_share_rides.electric_bike_casual AS
SELECT
month,
year,
rideable_type,
COUNT(ride_id) AS rideable_type_trend_casual
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'casual'
AND
rideable_type = 'electric_bike'
GROUP BY
month,
year,
rideable_type
ORDER BY
year ASC,
month ASC;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a WHERE clause, a GROUP BY clause, and an ORDER BY keyword, this statement creates a table with a count of member riders using the electric bike in order of the month and year.
*/

CREATE TABLE bike_share_rides.electric_bike_member AS
SELECT
month,
year,
rideable_type,
COUNT(ride_id) AS rideable_type_trend_member
FROM
`bike_share_rides.full_year`
WHERE
member_casual = 'member'
AND
rideable_type = 'electric_bike'
GROUP BY
month,
year,
rideable_type
ORDER BY
year ASC,
month ASC;

/*
CREATED BY: Jeremy Bosse
CREATED ON: 7/3/2023
DESCRIPTION: Using a COUNT function, a LEFT JOIN keyword, a WHERE clause, a GROUP BY clause, and an ORDER BY keyword, this table results in a count of casual riders, a count of member riders, and total riders who used an electric bike in order of the month and year.
*/

SELECT
F.rideable_type,
F.month,
F.year,
C.rideable_type_trend_casual AS casual_use,
M.rideable_type_trend_member AS member_use,
COUNT(ride_id) AS total_use
FROM
`bike_share_rides.full_year` AS F
LEFT JOIN
`bike_share_rides.electric_bike_casual` AS C
ON
F.month = C.month
AND
F.year = C.year
LEFT JOIN
`bike_share_rides.electric_bike_member` AS M
ON
C.month = M.month
AND
C.year = M.year
WHERE
F.rideable_type = 'electric_bike'
GROUP BY
F.rideable_type,
F.month,
F.year,
C.rideable_type_trend_casual,
M.rideable_type_trend_member
ORDER BY
F.year ASC,
F.month ASC

rideable_type   month   year   casual_use   member_use   total_use
electric_bike   4   2022   66758   125663   192421
electric_bike   5   2022   127930   156472   284402
electric_bike   6   2022   168408   163484   331892
electric_bike   7   2022   218902   200352   419254
electric_bike   8   2022   203960   211585   415545
electric_bike   9   2022   171493   203870   375363
electric_bike   10   2022   134806   197702   332508
electric_bike   11   2022   61817   125404   187221
electric_bike   12   2022   30317   76214   106531
electric_bike   1   2023   24361   73908   98269
electric_bike   2   2023   25287   73074   98361
electric_bike   3   2023   39725   108850   148575

Analysis Summary

Through my analysis, I have deduced that casual riders exhibit distinct preferences when utilizing Cyclistic bikes compared to member riders. Specifically, casual riders engage in longer rides on average during the spring and summer, favor weekends more than their member counterparts, demonstrate peak rider activity earlier in the summer, show a preference for afternoon rides, and exhibit a higher preference for electric bikes over other rideable types.

Recommendations

- Advertise to casual users promotional incentives during their peak season between Spring and Summer, and encourage enrollment into an annual subscription.

- Advertise to Casual riders promotional incentives for enrolling in an annual subscription between Saturday and Sunday.

- Advertise to Casual riders promotional incentives for enrolling in an annual subscription in the afternoon between 4:00 PM and 7:00 PM daily.

- Advertise to Casual riders promotional incentives for enrolling in an annual subscription whenever an electric bike is used.

- Utilize the email addresses of casual riders who sign up for a single or full-day pass using a landing page during ride activation.

- Create an email campaign with email addresses obtained through email capture to convert casual riders to member riders.

You may also like