Capstone project - Google Data Analytics

Info  

● Company: Cyclistic, a successful bike-share company, working in Chicago. 

● Department: Director of Marketing, Lily Moreno, Marketing team. 

● Data: Historical data about bike rides. My data is focusing on April 2022. Data is public data and can  be downloaded here 

Questions: 

1. How annual members and casual members differ? 

2. Why casual riders would buy a membership? 

3. How digital media could affect their marketing tactics? 

● Plan: clean and analyze data by using Google Sheets/Excel. Data visualization Tableau.

Data Cleaning and Analyzing. Google Sheets/Excel 

1. Checking data and columns 

2. Added new columns to make data easier to  

read 

2.1 Separate started_at to two columns.  

Start_date and start_time.

Data Cleaning and Analyzing. Google Sheets/Excel 

2.2 Doing same steps as earlier to ended_at column. Separate to two columns. 

2.3 Creating new column which calculates ride_length. (=end_time - start_time)  2.4 Check if the ride_time column has same start_date and end_date, TRUE OR FALSE

Data Cleaning and Analyzing. Google Sheets/Excel 

2.5 Create new column to get weekdays

Data Cleaning and Analyzing. Google Sheets/Excel  1. Hiding some rows which are not important to this analyze 

2. Calculating average ride_time 

3. Calculating rides to weekdays to see the difference between days 4. Counting total number members and casuals in dataset 

5. Comparing weekdays rides between members and casual

Data Cleaning and Analyzing. Google Sheets/Excel  

Conclusion: 

● We have cleaned data 

● Created new rows  

● We can compare members and casuals riders 

● We can compare weekdays 

● We have average ride time 

Next we focus on Data Visualization to present the data to stakeholders

Data Visualization - Total 

● First lets create picture of total rides in the period ● We can clearly see most popular dates and total rides in this  

period

Data Visualization - Casuals 

● Focus on casual riders and how they behave

Data Visualization - Members 

● Focus on Members and how they behave

Data Visualization - Members vs. Casuals

Findings 

● We can see that the most popular days are weekends 

● Overall, many members already 279,303 which is about 65% of the all users ● There was 147,285 casual rides in the period which is about 35% of the all users ● Members use bikes more regularly 

● Casuals peeks in weekends 

● Difference is big in weekdays (MON-THU) riders but small in weekends(FRI-SUN) 

Future suggestions 

● Casual riders like to use bike in weekends by offering weekend or month pass we  would get more options for casual riders which have not yet purchased full  membership 

● Marketing campaigns to casual riders. Tactical marketing and specific offer for casual  riders e.g. 3-month pass 

● Courage people to use more bikes by sending inspiring data to users (how much have  they used bike, how many kilometres, how many calories burned and so on) 

● For further investigations and more detailed data about casual riders we could create  survey which will tell us more detail why they haven't purchase membership and  when they decide to use bikes etc. 

● It it also important to keep members (65% of rides) happy we could make different  survey for members to understand better they needs and how they chose to buy  membership