---
# System prepended metadata

title: 'Google Data Analytics Capstone - Case 1 '

---

July 09,2023
Chin Jia Pei
### Background
It's a practice data analytics project for the Google Data Analytics Professional Certification where I will be working on a fictional company called Cyclistic and analyzing business quetions. 

The following content follow the steps of the data analysis process as below: 
*Ask - Prepare - Process - Analyze - Share - Act*



---


### Ask
Three questions will guide the future 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 statement: To increase the annual member audience base



---


### Prepare
Download 12 months of Cyclistic's historical trip data and save it into the desktop folder.

Data source: https://divvy-tripdata.s3.amazonaws.com/index.html

*Note: The datasets have a different name because Cyclistic is a fictional company. For the purposes of this case study, the datasets are appropriate and will enable you to answer the business questions. The data has been made available by Motivate International Inc. under this [license](https://ride.divvybikes.com/data-license-agreement).*

![](https://hackmd.io/_uploads/r1QIdpwKh.png)

---


### Process
Tools to be used in this project: 
- Data cleaning: Microsoft Excel Power Query /  Visualisation: Microsft Power BI. 

- Imported 12 months of raw data into Excel via Power Query. Power Query able to combine multiple databases (.csv.file)


- Data cleaning - Remove error and blank row and check the row count
![](https://hackmd.io/_uploads/H1baHY_dn.png)

- Edit the column type > Text/Number

- Add new column to calculate trip_duration, day of week and month 
![](https://hackmd.io/_uploads/HkgXQpPKn.png)

- Remove all trip durations that are less than 0 minutes, convert the numerical values of day of week and month into text. eg. 6 - Saturday in day of week column.
![](https://hackmd.io/_uploads/H1Dv7TvKh.png)
- Check the overall data consistency by creating a pivot table and make necessary changes accordingly.

- There's a total of 5 million rows from 12 months of raw data. After cleaning the data, there are a total of 3 million rows of data ready for analysis.
*Before*
![](https://hackmd.io/_uploads/S1297Y_dn.png)
*After*
![](https://hackmd.io/_uploads/BJTnE6vKn.png)
  

---

### Analyze & Share
![](https://hackmd.io/_uploads/rJlS36wth.png)
Tool: Microsoft Power BI

Key Finding: 
- A total of 3.03M trackable users ride Cyclistic bike from Jun 2022 to May 2023) with an average ride of 13.2mins.
- Annual membership rider is 36% higher than casual rider, tending to ride electric bike than classic bike.
- Casual riders tend to have longer average trip durations than annual members, possibly due to the availability of full-day passes.
- During the winter season (Dec - Feb), user counts for the cyclistic service are typically lower due to inclement weather conditions, whereas spring and summer seasons observe a significant increase in user activity.



|| Member | Casual|
| -------- | -------- | -------- |
|Total Users   | 1.75M     | 1.28M     
Avg.Trip Duration|11.48mins|15.67mins     
Bike Types|Electric & Classic|Classic & Docked     
Highest count by months|Aug, Jul, Sept|Jul, Aug, Jun     
Least count by months|Dec - Feb| Dec - Feb
Highest count by days|Wed, Thurs, Tues|Sat, Sun, Fri     
Data Visualisation| ![](https://hackmd.io/_uploads/H1BKhaPth.png)| ![](https://hackmd.io/_uploads/Hyr3navKh.png)




---


### ACT
- Business KPI: To maximize the number of annual membership by 15% in 12 months.
- Market Standard:10.09% https://www.statista.com/outlook/mmo/shared-mobility/shared-rides/bike-sharing/worldwide


Based on the finding above, I propose launching two new annual subscription plans that align with the preferences of casual users in order to increase the conversion rate to annual membership. 

1. Annual membership (weekends) - Subscibe the weekend annual membership to enjoy the first 15mins free ride on every weekend. 
2. Flexi seasonality annual membership: Subscribe the flexi membership to enjoy seasonal benefits and privileges, especially Jun - Aug. 



Designing a customer success framework based on consumer needs to increase the motivation to be part of the Cyclistic community eg. Unlock the *membership benefit at no cost for a month!* allows users to experience the perks of annual membership. 

On top of that, we should update/review the annual membership value-add features to increase the stickiness of brand loyalty. eg. brand partnership - ride XXkm and get a free tacos from us!

Media Touchpoint:
| Awareness | Consideration | Intent |
| -------- | -------- | -------- |
|  Social media (Engagement/Reach)|Social media (App promotion/Traffic)| Social Media 
Programmatic digital OOH in proximity locations| KOL & KOC marketing| STB and Rich Media optimsed towards CPA
Standard display banner| Rich Media| 
Advertorials| Email Marketing

