google data analytics capstone project reddit

R news and tutorials contributed by hundreds of R bloggers

A hiring manager’s review of the google data analytics professional certificate syllabus.

Posted on November 30, 2021 by Oscar in R bloggers | 0 Comments

[social4i size="small" align="align-left"] --> [This article was first published on R programming – Oscar Baruffa , and kindly contributed to R-bloggers ]. (You can report issue about the content on this page here ) Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

30 November 2021

“Is the Google Data Analytics Professional Certificate any good?”

Whether you’re a job seeker looking to boost your career or a hiring manager eager to find skilled staff, this is a pertinent question.

In this review, I assess the certificate based on the syllabus, and not the course content itself i.e. I have not completed any of the course work :). I’m interested in what I can learn about the course as someone who might hire an analyst with these credentials, or want to recommend it to someone entering the broad field of Data Science.

I was pleasantly surprised. The course material looks comprehensive, provides nuance and introduces about all one could expect one course to deliver at a reasonable price and time commitment. My only wish is that it covered how to get oneself unstuck when facing any challenges with software and coding.

Where we are now: a job market paradox

The job market for new Data Analysts (and to some degree Data Scientists) seems very paradoxical to me. My perception is that:

• There’s an ever increasing demand for data skills that I think will be around for a very long time indeed.
• It’s quite challenging to fill these roles and retain people.
• New data analysts have trouble finding work.

This is not backed up by extensive data or research on my part, it’s just my experience and that of others from the past few years.

I understand there’s a few things that need to be in place for this supply of data professionals to meet the demand for them, both from the people looking for work and from those doing the hiring. One thing that can help is a way to train Data Analysts with the skills they need and provide a certification that employers can recognise.

Enter the Google Data Analytics Professional Certificate

Google launched it’s Data Analytics Professional Certificate in March of 2021. When I saw the run up to it I was quite intrigued. When Google does something related to data it’s worth a look in my opinion. A quick glance of the syllabus revealed that the main tools they cover are spreadsheets, Tableau and R (all my skills, so I already felt a bit buoyed by that) and SQL (not my skillset but a staple for many Data Analyst roles). The course also goes beyond the technical skills and looks more holistically at how to think with an analytical mindset, work with others and communicate. At a high level, these are signals that the Certification has good potential.

Since it’s launch, I’ve seen many positive reviews of it by students. There are also many people in forums such as the r/DataAnalysis subreddit that ask whether the course is worth it, whether they’ll land a job, or at the very least develop the skills they’ll need for data analysis.

The Certification is quite extensive “with over 180 hours of instruction and hundreds of practice-based assessments” spread across 8 courses. It has a capstone project. At the time of writing, it boasts over 450 000 enrolments and over 30 000 ratings with an average of 4.8 stars out of 5.

Enrolment and completion

At the time of writing (December 2021) the completion rate seems to me fairly low, if we use the number of ratings of each of the 8 courses as a proxy for how many students complete and rate the course. I haven’t taken a Coursera course before, but I assume they are like most platforms and quite aggressively ask for student ratings.

The course boasts 456k enrolments, but Course 1 of 8 only has 22k ratings – that’s less than 5% “conversion”. It makes sense that there’s an initial large drop-off because its free to enrol but after just 7 days one needs to start paying $39 per month for subscription, so the actual commitment to continue with Course 1 is a more true reflection of real enrolment.

Showing the number of enrolments is a double-edged sword. It signals a lot of “social proof” and students and hiring managers might think “Wow, so many people took this, it must be good”. On the other hand, I can also imagine it’s a real psychological blow for new entrants who may think “Oh no, the job market is saturated”. This is especially damaging if they’ve heard that the field is saturated and getting this Certification is meant to help you stand out in the job market, in addition to obtaining the requisite skills for that first role. As we’ll see however, it’s very unlikely that many people hold this Certification.

So, Course 1 has 22k ratings and then we see a steep fall in ratings to 7k for Course 2 and all the way down to 1.7k for Course 8. The final course has just 7% of the initial courses rating. There’ll be many reasons for this, but the difference did strike me as quite substantial. My takeaway is “don’t worry, there’s not a saturation of Google Data Analytics Professional Certifications.” And what would a saturation be anyway? If we assume that Data Analysts create value, then hundreds of thousands of people trained in data analysis would be a huge boost to the economy! But I digress…

The context of my review

The course is developed by Google, one of the largest tech companies in the world. I assume what they teach and ask for is very relevant for them and other big tech firms – however I have no experience working in those organisations

My interest is the more general situation where many hundreds of thousands (or millions) of companies are. A few hundred to a few thousand employees and the organisation needs people with skills to work with data.

My assessment on whether this is a good or bad Certification centres around questions like:

• How much of the full data/analysis pipeline are they exposed to?
• How much of their own analytical thinking is developed?
• Do they learn how to learn?

I think we can’t expect too much from any course that’s teaching these skills, especially to someone just entering it (unless the fees are exorbitant!) . The field of Data Science, and the role of Data Analyst – is so broad and diverse that it’ll be easy to provide criticism if the material doesn’t fit your view of what’s important. We must acknowledge that even working professionals, decades into their career – can be on completely different paths and largely disagree on what an introductory course should cover.

If Google is able to produce a course that most people can say does give some value to newcomers, then I think they’ll have done well.

Syllabus overview

The Professional Certificate comprises 8 courses. For this review I’ve mostly looked at the course overview and then dove a bit deeper into each courses’ syllabus.

1. Foundations: Data, Data, Everywhere : Introduces concepts and terminology, key terms and awareness of tools and skillsets. What a day in the role of a Data Analyst looks like, how it fits into the broader data ecosystem and what job opportunities there are.

2. Ask Questions to Make Data-Driven Decisions: Teaches about how to think about analyses, how to ask questions effectively and how decisions are made using the results of analyses. This course also introduces the use of spreadsheets in analysis.

3. Prepare Data for Exploration: Builds upon previous material for spreadsheet skills and introduces SQL. Covers how analysts decide what data to use, differences between structured and unstructured data, data types and formats. Open data, ethics and privacy are touched upon. Learn how databases access, filter and sort the data they contain. Also covers how to organise data and store it securely. This course uses BigQuery for the database instruction.

4. Process Data from Dirty to Clean : Covers data cleaning techniques using both spreadsheets and SQL, introduces SQL queries and functions. Teaches how to deliver a good data cleaning report, and how to check data integrity.

5. Analyze Data to Answer Questions: Further use of spreadsheets and SQL, but now utilising more complex formulas and queries. How to prep data for analysis, how to aggregate data.

6. Share Data Through the Art of Visualization: Introduces visualisation, Tableau, how to make dashboards and effective presentations. How to consider the limitations of your analysis and how to deal with audience questions. They use the free version of Tableau which is great as there’s no hidden expense for students.

7. Data Analysis with R Programming: Introduces the use of R, RStudio and R packages. Covers the full pipeline from cleaning to organising, analysing and visualizing data. Looks to be a Tidyverse-centric approach and touches on RMarkdown too. R and RStudio are open source and free to use, so again its great that students don’t have to pay for additional software. Interestingly they also cover the Python vs R debate, so I might enrol just to see what they say! PS I love Python too don’t worry ;).

8. Google Data Analytics Capstone: Complete a Case Study: This is an optional module, includes identifying and developing a case study, job hunting instruction, interview training and how to build a portfolio.

Structure of each course

Diving a bit deeper into the syllabi of the courses, I can see quite a good mix of videos, exercises, quizzes and readings. Videos tend to be very short at just a few minutes each, whereas time allocated for readings are usually longer at around 10 to 20 minutes a piece. There’s also checklists, learning logs, course challenges and self-reflections.

Courses are peppered with introductions into different types of analytic roles, presumably presented by people with those roles. I like that this will broaden students’ horizons when job hunting.

The content ratings of each one also seem generally very high.

Verdict: Very good but with just one missed opportunity

Overall this looks like quite a comprehensive introduction to data analytics. It covers a lot of ground for someone new to the role. Provided the content is at least of ok quality, I think there’s enough here that the skills developed will be useful for that first job and the student will be equipped with enough awareness of other tools to figure out which one to use where, or at least have awareness of what their colleagues are talking about. It’s fantastic that the course covers how to think like an analyst, ask questions and interact with stakeholders. This is what gives it that extra credibility for me versus a certification that focussed solely on the technical tooling.

While I was reviewing this and especially when looking at more detail, I found that I was becoming more and more impressed by the topics covered. The course covers not just the basics, but seeks to add nuance, caveats and gotchas as well. The type of things you might discuss with a new colleague over a casual chat – there seems to be a bit of mentorship built into the material.

The only thing that appears to be missing is teaching students how to become unstuck when they face a technical problem. This would warrant a whole course in my opinion, or at least a big chunk of one of the existing ones. There are so many tools and technologies out there, and I fear that new entrants to the field feel overwhelmed by the amount of stuff on the ever growing “to learn ” list, even though in practice you are likely to use just a handful of things a lot and the rest very little or not at all – and which you use depends entirely on the specific job and task at hand. I think there’s still a lot of stigma around “googling the solution” for many people, but ANY tech professional will tell you this is something they do all the time. How to format your question, knowing what to google in the first place, evaluating potential solutions, using the process of elimination to slowly get unstuck and move forward – super super important skills. A new Data Analyst will develop these skills over time, but its really a missed opportunity to equip students with a skill to empower themselves this way.

There’s no coverage of statistics in any form that I could see. Now, what’s needed for a data analyst position is debatable (even my own stats knowledge is pretty limited) but I would hope that the difference between a mean and median is quite a basic one to cover – but I’ll reserve too much comment here – surely in 180 hours of material it’ll be mentioned somewhere :).

In conclusion

I think this is a valuable addition to the ecosystem and has the potential to be a good onramp for many people to enter the industry. The cost is by no means negligible for many people, but I think at $39/m provides fantastic value just considering the breadth of topics this covers.

I’m looking forward to seeing this develop and hopefully it means we’ll soon have a whole lot more people in the world with the skills to analyse data!

Don’t miss any updates, sign up below!

The post A Hiring Manager’s review of the Google Data Analytics Professional Certificate syllabus appeared first on Oscar Baruffa .

To leave a comment for the author, please follow the link and comment on their blog: R programming – Oscar Baruffa . R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job . Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Copyright © 2022 | MH Corporate basic by MH Themes

Never miss an update! Subscribe to R-bloggers to receive e-mails with the latest R posts. (You will not see this message again.)

• Oct 18, 2021
• 10 min read

Google Data Analytics Capstone Project

Updated: Jul 5, 2023

I worked on the Google Data Analytics Capstone Project, Track 1, Case Study 1. I will be diving into the background, my full process of cleaning, analyzing and visualizing the data, along with my final suggestions and summary of the data.

Quick Links :

Tableau Dashboard | Github R Code for Analysis | Github R Code for Tableau Visualization | LinkedIn Post

Below is a table of contents in case you want to go to a specific section.

Table of Contents:

Microsoft excel.

Finished Project

Summary of Data

Business Suggestions

What I Learned

Cyclistic is a bike sharing program which features more than 5,800 bikes and 600 docking stations. It offers reclining bikes, hand tricycles, and cargo bikes, making it more inclusive to people with disabilities and riders who can't use a standard two-wheeled bike. It was founded in 2016 and has grown tremendously into a fleet of bicycles that are geotracked and locked into a network of 692 stations across Chicago. The bikes can be unlocked from one station and returned to any other station in the system anytime.

Previously, Cyclistic's marketing strategy tried to build the general awareness and appeal to broad consumers. It has flexible pricing plans: single-ride passes, full-day passes, and annual memberships. Those who purchase single-ride or full-day passes are referred to as casual riders while those who purchase annual memberships are Cyclistic members .

My Role : In this scenario I am a junior data analyst at Cyclistic and my team has been tasked with the overall goal (see below) of designing marketing strategies

Overall Goal : Design marketing strategies aimed at converting casual riders into annual members.

Business Question : "How do annual members and casual riders use Cyclistic bikes differently?"

Below I will describe step-by-step the process I used to for this project. If you want to skip ahead to the business suggestions move onto the section "Insights".

Overview : I first analyzed the data separately (each month) in Excel, then used R to analyze the data as a whole (one year). Finally I created a dashboard in Tableau and used Figma to support the design elements.

I initially wanted to gather and analyze my data in Excel because it was the tool I was most familiar with and I could get a general understanding of the data quicker. I did not combine all of the spreadsheets into one because that would've taken more processing power than my computer had.

I began downloading the data from divvy-tripdata , and turning the .csv files into excel spreadsheets. I downloaded the most recent year of data which was at the time of starting my project:

August 2020

September 2020

October 2020

November 2020

December 2020

January 2021

February 2021

Added two columns to all of the months:

ride_length calculated the total ride length for each trip using the start_at column which was: ending time minus starting time.

day_of_week calculated the day of the week for each trip using the start_at column date.

Went over the business task and the information I had at hand and how that could be used to figure out how members and casual riders use the bike service differently

Came up with metrics to look at such as :

total number of rides per hour, per day of the month, per season, per day of the week, and for different bike types

Average ride length between members and casual

For every month in Excel created pivot tables and charts to go with the analysis on (this took the longest):

Total Rides per Weekday - calculated the total rides for members and casual and separated it by day of the week; used a cluster column chart

Average Ride Length - calculated the average ride length for members and casual and separated it by day of the week; used a cluster column chart

Total Rides per Hour - calculated the total rides for members and casual separated by the time of the day (24hr); used a line comparison chart

Total Rides per Day - calculated the total rides for members and casual separated by the day of the month; used a line comparison chart

Total Rides per Bike Type - calculated the total rides for members and casual separated by Bike type; used stacked column chart

I also created a Google docs Notes list where I wrote down the exact steps for each month (had a checklist) and included my insights for each month

Time Spent:

535 minutes or just under 9 hours to complete.

I originally wanted to use SQL but the files were too big to upload and I couldn't figure out how to utilize Google Cloud Platform. Instead I used R to analyze the data because it could handle all of the information quicker than Excel, and I wanted to work on my R skills. Below is my general process in R, I didn't include my mistakes/missteps or errors for the sake of brevity.

View my full code on my Github for this capstone project here .

Load all of the libraries I used: tidyverse, lubridate, hms, data.table

Uploaded all of the original data from the data source divytrip into R using read_csv function to upload all individual csv files and save them in separate data frames. For august 2020 data I saved it into aug08_df, september 2020 to sep09_df and so on.

Merged the 12 months of data together using rbind to create a one year view

Created a new data frame called cyclistic_date that would contain all of my new columns

Created new columns for:

Ride Length - did this by subtracting end_at time from start_at time

Day of the Week

Time - convert the time to HH:MM:SS format

Season - Spring, Summer, Winter or Fall

Time of Day - Night, Morning, Afternoon or Evening

Cleaned the data by:

Removing duplicate rows

Remove rows with NA values (blank rows)

Remove where ride_length is 0 or negative (ride_length should be a positive number)

Remove unnecessary columns: ride_id, start_station_id, end_station_id, start_lat, start_long, end_lat, end_lng

Calculated Total Rides for:

Total number of rides which was just the row count = 4,152,139

Member type - casual riders vs. annual members

Type of Bike - classic vs docked vs electric; separated by member type and total rides for each bike type

Hour - separated by member type and total rides for each hour in a day

Time of Day - separated by member type and total rides for each time of day (morning, afternoon, evening, night)

Day of the Week - separated by member type and total rides for each day of the week

Day of the Month - separated by member type and total rides for each day of the month

Month - separated by member type and total rides for each month

Season - separated by member type and total rides for each season (spring, summer, fall, winter)

Calculated Average Ride Length for:

Total average ride length

Type of Bike - separated by member type and average ride length for each bike type

Hour - separated by member type and average ride length for each hour in a day

Time of Day - separated by member type and average ride length for each time of day (morning, afternoon, evening, night)

Day of the Week - separated by member type and average ride length for each day of the week

Day of the Month - separated by member type and average ride length for each day of the month

Month - separated by member type and average ride length for each month

Season - separated by member type and average ride lengths for each season (spring, summer, fall, winter)

Then using all of this data I created my own summary in my case notes and took note of the: total rides for each variable, average ride lengths for each variable, and the difference between members versus casual riders. I originally wanted to create a report using R Markdown as well but for the sake of time (I had already spent over 20 hours on the project so far), I decided to skip this step, and write this article instead.

1045 minutes or about 17 and a half hours to complete.

While I learned the basics of Tableau in the Google Course I wanted more practice with visualizing data and creating dashboards.

To view my completed dashboard click here .

I created a separate R code (you can view it here on Github) that made some changes for specifically the Tableau portion.

For ride length I rounded the digits by 1, meaning my numbers were 29.8 or 12.5.

Revised how I created my "month" column. I used mutate() to create a column that had the month in ___ format and not number format. So instead of 01 it would say "January"

Cleaned the data: removed rows with NA values, removed duplicate rows, removed where ride_length was 0 or negative and removed unnecessary columns like: ride_id, start_station_id, end_station_id, start_lat, start_long, end_lat, end_lng

Created a new dataframe with this information so I could test the difference between the original data frame (cyclistic_date) that I used for my analysis and the data frame I would use for Tableau (cyclistic_tableau).

In this new data frame I removed more columns to make calculations quicker in Tableau. I removed: start_station_name, end_station_name, time, started_at, ended_at

Downloaded this data frame into a .csv file which I uploaded to Tableau

Created graphs similar to those I created in Excel but added a few:

Total Rides by Bike Type

Ride Length by Weekday

Total Rides by Weekday

Total rides by hour, total rides by month.

Then I created a basic dashboard with all of that information, a prototype for me to view while I was creating the final dashboard ( Figure 1 below).

Created a prototype mockup in Figma

Created a final version of the mockup in Figma

Edited Dashboard in Tableau to reflect design in Figma

Edited graphs in Tableau

Made bar graphs round

Added annotations

Highlights to specific important notes

Got rid of labels for visual purposes

Combined Figma and Tableau (used dashboard created in Figma as the background for my Tableau Dashboard) to create a final prototype ( Figure 2 below)

Made minor edits to design elements and created final dashboard ( Figure 3 - Cyclistic Dashboard V1 )

On April 24, 2023 I decided to update my dashboard (See Finished Project , image Final Dashboard - Cyclistic Dashboard V2 ). All of the analysis is the same. The only changes have been to the dashboard. Which include:

Adding horizontal grid lines to a few of the charts

Updating the tool tips.

Making all of the top metric values (e.g. Total Rides, Average Ride Length, etc.) interactive in Tableau instead of in Figma.

765 minutes or almost 13 hours to complete.

Tableau Prototype

Below was my first draft of the dashboard only using Tableau.

Prototype using Figma Background

Combined Figma and Tableau (used dashboard created in Figma as the background for my Tableau Dashboard) to create a final prototype.

Final Dashboard V1

Made minor edits to design elements and created final dashboard. This was the original final dashboard.

I am including the other tools I used.

Figma to create my background and help develop the dashboard aesthetics.

Google Docs helped me keep track of all of my documents for this project like:

Date Log - I wrote down what I did that day related to my project

Resources - A list of resources I frequently used

Case Notes - Notes for the case study including the final insights, what I was looking for, and anything else having to do with the case

Evernote to draft this article before I uploaded it here.

FINISHED PROJECT

Here is my finished project: Google Capstone Project (V2) . You can view the links to my R code on Github used for analysis here and the code for Tableau here .

Note: This is V2 with a few minor changes to the dashboard. Including:

SUMMARY OF DATA

Those who purchase single-ride or full-day passes are referred to as casual riders while those who purchase annual memberships are Cyclistic members .

Total Rides by User Type

Average Ride Length per User Type

Average Ride per Weekday

Members had more rides with 2,328,763 total rides or 56% and casual riders had 1,823,376 total rides or 43%.

Total Rides per Bike Type

Both casual riders and members used the classic bike the most with 1,777,593 rides or 43% of total rides, followed by docked bikes with 1,545,936 rides or 37% of total rides, and lastly with electric bikes at 828,610 rides or 20% of total rides.

Average Ride Length by User Type

The total average ride length was 24 minutes. For casual riders it was longer at 27 minutes while members was 14 minutes.

Average Ride Length per Weekday

For the average ride length per weekday both casual riders and members had an increase in the average ride length on the weekends. For both Sunday was the longest at 31 minutes.

Saturday was the most popular weekday combining casual riders and member rides with 784,239 rides or 19% of total rides. But for member rides only Wednesday was the most popular day with 356,060 rides, 5,407 rides more than Saturday.

5PM or 17:00 was the busiest hour for both members and casual riders with 426,685 rides or 10% of the total rides. Typically rides began increasing in the morning at 6AM and rose until 5PM then dropped afterwards. The afternoon was the busiest for both rider types with 1,905,797 rides or 45% of total rides. 4AM was the least popular hour.

July was the busiest month combining casual riders and member rides at 691,476 rides or 16% of total rides. While summer was the most popular season for both at 1,903,446 rides or 46% of total rides. Looking at just members August is actually the busiest month with 323,140 rides, 816 rides more than July. Winter is the least popular season and February is the least popular month.

Final Summary

The most popular bike among with riders was the classic.

Busiest time was afternoon and the peak time was at 5PM for both casual riders and members.

Busiest weekday was Saturday, casual riders used the service the most on the weekends.

Busiest season was Summer for both types of riders.

Most rides by User Type was members but casual riders weren't far behind.

The average ride length was 24 minutes but casual riders on average rode 23 minutes longer than members.

BUSINESS SUGGESTIONS

This was the hardest part for me for the whole project. I have never provided suggestions for a business nor worked in marketing. Any feedback here would be appreciated.

These are my suggestions for the marketing team to convert casual riders to annual members:

Personalize discounts and show perks in the membership program based on their preferences and riding habits.

Emphasize the benefits of memberships, including discounts during busy times of the year like during Summer, or on the weekends.

Have existing members to share their stories about how using Cyclistic's system has changed their life, to create a sense of community, offer a discount if they do so this will help encourage new riders to join the program.

WHAT I LEARNED

Below is what I learned/practiced from over 40 hours spent on this project:

Pivot Tables in Microsoft Excel

Practice using R for data analysis and cleaning specifically using the tidyverse package for data analysis

Graphs in Tableau, edited visual elements along with creating different charts and filters.

Design elements of an effective dashboard

Combining the design feature of Figma with the functionality of Tableau

R portion of my project I found Itamar's case study on Kaggle using R as well, a helpful resource.

Tableau portion I used Navneet Singh's Tableau Dashboard as inspiration.

• Data Analytics
• Portfolio Projects

Recent Posts

The Power of Collaborative Projects in Data Analytics

Deep Work Dashboard 2023

Behind the Scenes: Creating a SQL Course

Wow Kelly. This really is impressive. Glad to see you are really into this process. You have definitely found your niche!!

Search code, repositories, users, issues, pull requests...

Provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications

SomiaNasir/Google-Data-Analytics-Capstone-Cyclistic-Case-Study

Folders and files, repository files navigation, google data analytics capstone: cyclistic case study.

Course: Google Data Analytics Capstone: Complete a Case Study

Introduction

In this case study, I will perform many real-world tasks of a junior data analyst at a fictional company, Cyclistic. In order to answer the key business questions, I will follow the steps of the data analysis process: Ask , Prepare , Process , Analyze , Share , and Act .

Quick links:

Data Source: divvy_tripdata [accessed on 04/03/23]

SQL Queries: 01. Data Combining 02. Data Exploration 03. Data Cleaning 04. Data Analysis

Data Visualizations: Tableau

A bike-share program that features more than 5,800 bicycles and 600 docking stations. Cyclistic sets itself apart by also offering reclining bikes, hand tricycles, and cargo bikes, making bike-share more inclusive to people with disabilities and riders who can’t use a standard two-wheeled bike. The majority of riders opt for traditional bikes; about 8% of riders use the assistive options. Cyclistic users are more likely to ride for leisure, but about 30% use them to commute to work each day.

Until now, Cyclistic’s marketing strategy relied on building general awareness and appealing to broad consumer segments. One approach that helped make these things possible was the flexibility of its pricing plans: single-ride passes, full-day passes, and annual memberships. Customers who purchase single-ride or full-day passes are referred to as casual riders. Customers who purchase annual memberships are Cyclistic members.

Cyclistic’s finance analysts have concluded that annual members are much more profitable than casual riders. Although the pricing flexibility helps Cyclistic attract more customers, Moreno (the director of marketing and my manager) believes that maximizing the number of annual members will be key to future growth. Rather than creating a marketing campaign that targets all-new customers, Moreno believes there is a very good chance to convert casual riders into members. She notes that casual riders are already aware of the Cyclistic program and have chosen Cyclistic for their mobility needs.

Moreno has set a clear goal: Design marketing strategies aimed at converting casual riders into annual members. In order to do that, however, the marketing analyst team needs to better understand how annual members and casual riders differ, why casual riders would buy a membership, and how digital media could affect their marketing tactics. Moreno and her team are interested in analyzing the Cyclistic historical bike trip data to identify trends.

I am assuming to be a junior data analyst working in the marketing analyst team at Cyclistic, a bike-share company in Chicago. The director of marketing believes the company’s future success depends on maximizing the number of annual memberships. Therefore, my team wants to understand how casual riders and annual members use Cyclistic bikes differently. From these insights, my team will design a new marketing strategy to convert casual riders into annual members. But first, Cyclistic executives must approve our recommendations, so they must be backed up with compelling data insights and professional data visualizations.

Business Task

Devise marketing strategies to convert casual riders to members.

Analysis Questions

Three questions will guide the future marketing program:

• How do annual members and casual riders use Cyclistic bikes differently?
• Why would casual riders buy Cyclistic annual memberships?
• How can Cyclistic use digital media to influence casual riders to become members?

Moreno has assigned me the first question to answer: How do annual members and casual riders use Cyclistic bikes differently?

Data Source

I will use Cyclistic’s historical trip data to analyze and identify trends from Jan 2022 to Dec 2022 which can be downloaded from divvy_tripdata . The data has been made available by Motivate International Inc. under this license .

This is public data that can be used to explore how different customer types are using Cyclistic bikes. But note that data-privacy issues prohibit from using riders’ personally identifiable information. This means that we won’t be able to connect pass purchases to credit card numbers to determine if casual riders live in the Cyclistic service area or if they have purchased multiple single passes.

Data Organization

There are 12 files with naming convention of YYYYMM-divvy-tripdata and each file includes information for one month, such as the ride id, bike type, start time, end time, start station, end station, start location, end location, and whether the rider is a member or not. The corresponding column names are ride_id, rideable_type, started_at, ended_at, start_station_name, start_station_id, end_station_name, end_station_id, start_lat, start_lng, end_lat, end_lng and member_casual.

BigQuery is used to combine the various datasets into one dataset and clean it. Reason: A worksheet can only have 1,048,576 rows in Microsoft Excel because of its inability to manage large amounts of data. Because the Cyclistic dataset has more than 5.6 million rows, it is essential to use a platform like BigQuery that supports huge volumes of data.

Combining the Data

SQL Query: Data Combining 12 csv files are uploaded as tables in the dataset '2022_tripdata'. Another table named "combined_data" is created, containing 5,667,717 rows of data for the entire year.

Data Exploration

SQL Query: Data Exploration Before cleaning the data, I am familiarizing myself with the data to find the inconsistencies.

Observations:

The table below shows the all column names and their data types. The ride_id column is our primary key.

The following table shows number of null values in each column.

Note that some columns have same number of missing values. This may be due to missing information in the same row i.e. station's name and id for the same station and latitude and longitude for the same ending station.

As ride_id has no null values, let's use it to check for duplicates.

There are no duplicate rows in the data.

All ride_id values have length of 16 so no need to clean it.

There are 3 unique types of bikes( rideable_type ) in our data.

The started_at and ended_at shows start and end time of the trip in YYYY-MM-DD hh:mm:ss UTC format. New column ride_length can be created to find the total trip duration. There are 5360 trips which has duration longer than a day and 122283 trips having less than a minute duration or having end time earlier than start time so need to remove them. Other columns day_of_week and month can also be helpful in analysis of trips at different times in a year.

Total of 833064 rows have both start_station_name and start_station_id missing which needs to be removed.

Total of 892742 rows have both end_station_name and end_station_id missing which needs to be removed.

Total of 5858 rows have both end_lat and end_lng missing which needs to be removed.

member_casual column has 2 uniqued values as member or casual rider.

Columns that need to be removed are start_station_id and end_station_id as they do not add value to analysis of our current problem. Longitude and latitude location columns may not be used in analysis but can be used to visualise a map.

Data Cleaning

SQL Query: Data Cleaning

• All the rows having missing values are deleted.
• 3 more columns ride_length for duration of the trip, day_of_week and month are added.
• Trips with duration less than a minute and longer than a day are excluded.
• Total 1,375,912 rows are removed in this step.

Analyze and Share

SQL Query: Data Analysis Data Visualization: Tableau The data is stored appropriately and is now prepared for analysis. I queried multiple relevant tables for the analysis and visualized them in Tableau. The analysis question is: How do annual members and casual riders use Cyclistic bikes differently?

First of all, member and casual riders are compared by the type of bikes they are using.

The members make 59.7% of the total while remaining 40.3% constitutes casual riders. Each bike type chart shows percentage from the total. Most used bike is classic bike followed by the electric bike. Docked bikes are used the least by only casual riders.

Next the number of trips distributed by the months, days of the week and hours of the day are examined.

Months: When it comes to monthly trips, both casual and members exhibit comparable behavior, with more trips in the spring and summer and fewer in the winter. The gap between casuals and members is closest in the month of july in summmer. Days of Week: When the days of the week are compared, it is discovered that casual riders make more journeys on the weekends while members show a decline over the weekend in contrast to the other days of the week. Hours of the Day: The members shows 2 peaks throughout the day in terms of number of trips. One is early in the morning at around 6 am to 8 am and other is in the evening at around 4 pm to 8 pm while number of trips for casual riders increase consistently over the day till evening and then decrease afterwards.

We can infer from the previous observations that member may be using bikes for commuting to and from the work in the week days while casual riders are using bikes throughout the day, more frequently over the weekends for leisure purposes. Both are most active in summer and spring.

Ride duration of the trips are compared to find the differences in the behavior of casual and member riders.

Take note that casual riders tend to cycle longer than members do on average. The length of the average journey for members doesn't change throughout the year, week, or day. However, there are variations in how long casual riders cycle. In the spring and summer, on weekends, and from 10 am to 2 pm during the day, they travel greater distances. Between five and eight in the morning, they have brief trips.

These findings lead to the conclusion that casual commuters travel longer (approximately 2x more) but less frequently than members. They make longer journeys on weekends and during the day outside of commuting hours and in spring and summer season, so they might be doing so for recreation purposes.

To further understand the differences in casual and member riders, locations of starting and ending stations can be analysed. Stations with the most trips are considered using filters to draw out the following conclusions.

Casual riders have frequently started their trips from the stations in vicinity of museums, parks, beach, harbor points and aquarium while members have begun their journeys from stations close to universities, residential areas, restaurants, hospitals, grocery stores, theatre, schools, banks, factories, train stations, parks and plazas.

Similar trend can be observed in ending station locations. Casual riders end their journay near parks, museums and other recreational sites whereas members end their trips close to universities, residential and commmercial areas. So this proves that casual riders use bikes for leisure activities while members extensively rely on them for daily commute.

After identifying the differences between casual and member riders, marketing strategies to target casual riders can be developed to persuade them to become members. Recommendations:

• Marketing campaigns might be conducted in spring and summer at tourist/recreational locations popular among casual riders.
• Casual riders are most active on weekends and during the summer and spring, thus they may be offered seasonal or weekend-only memberships.
• Casual riders use their bikes for longer durations than members. Offering discounts for longer rides may incentivize casual riders and entice members to ride for longer periods of time.

Capstone Project - Google Data Analytics Certification

Leah williams.

This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see http://rmarkdown.rstudio.com .

Google Data Analytics Certificate - Capstone Project

This project contains code and report for the Google Data Analytics Capstone Project as part of the certificate program.

The results have been documented in the Analysis Cyclistic 2019 Riders R Markdown Notebook.

Project Overview

In this project the trip data of the Cyclistic bike-share program has been analyzed to perform customer conversion. Cyclistic has casual leisure riders and annual membership riders, the goal is to gain more annual members.

This project has two steps:

Determine how casual riders and annual member riders use Cyclistic bikes.

Identify strategies to convert casual riders into annual members.

Data Description

The data has been provided by Coursera through the resource site. The dataset contains four (4) files containing trip data by the bike user type for each quarter to make up a calendar year of trip data for the year 2019.

Technical Overview

The project has been divided into steps which include:

• Data Exploration and Cleaning

• Data Analysis

• Create a Presentation of the Deliverable

• Create a Portfolio Site

• Add the Capstone Project Resource Links to Resume

• Submission to Kaggle

Requirements

All the project requirements are provided Coursera.

Guiding Questions

• What is the problem you are trying to solve?

• How can your insights drive business decisions?

• Identify the business task.

• Identify final conclusions based on analysis findings.

Dependencies

The main dependency for this project is the package tidyverse.

Collect Data

Upload dataset files.

Wrangle Data: Organize

Inspect the column names.

Rename the columns.

Inspect the data frame.

Bind rows to combine files.

Clean and Adjust Data

Inspect the table created.

Format rider types.

Format date times.

Perform Calculations.

Format ride_length.

Clean Data and Create New Data Frame.

Analyze Data

Analyze ride_length.

Analyze rider_types.

Analyze average duration.

Analyze average duration by weekday and rider type.

Analyze weekday use.

Process Data

Check for Data Errors.

Visualize Data

Visualize Data for Rides by Rider Type.

Visualize Data for Average Duration.

Create a file.

Share Findings

Portfolio Site

Cyclystic 2019 Riders https://sites.google.com/view/leahsprojects

• Top Courses
• Online Degrees
• Find your New Career
• Join for Free

Learner Reviews & Feedback for Google Data Analytics Capstone: Complete a Case Study by Google

About the course, top reviews.

May 12, 2023

Excellent course. Highly recommended. Teaches the latest technologies and provides real work experiences. Already leveraging some of the things I have learned in my current role as a business analyst.

Nov 10, 2022

An elevator pitch gives potential employers a quick, high-level understanding of your professional experience. What are the key considerations when creating an elevator pitch? Select all that apply.

1 - 25 of 2,272 Reviews for Google Data Analytics Capstone: Complete a Case Study

May 27, 2021

No jobs. In Week 4 of Course 8, there is the Googlecerts Coursera Job Platform. Some may call it Consortium. It's linked under "Practice Quiz: End of Cert. Checklist." I did a search for "data analyst." All the jobs seemed to require previous relevant exp. or education. Most req. bachelor's or higher. All the Google postings I checked required a degree. Mods are now censoring comments from people who've said this. I therefore do not recommend Google or Coursera until such time as they're willing to address concerns about online ads for this specialization and the alleged lack of entry-level jobs.

By Andrew T

May 3, 2021

wish they could make a video of full tutorial on the two given case studies and guide us step by step. they should make it compulsory to submit the project and let other students grade it and give feedback

By Nathan W

Sep 14, 2021

I appreciate the enthusiasm of this course's presenter. However, all the material around the case studies, specifically the first one, leaves a lot to be desired.

To start with, each piece of this material feels like it was written by a different person—and it probably was. This creates a significant problem, in that all the of the material is disjointed and doesn't correctly guide the student through the case study. Sometimes two steps will duplicate a piece of the process, and other times one step will assume something was done earlier when it wasn't. There is an almost complete lack of quality control and editing with respect to stitching together the disparate pieces and making sure that a student can follow them through correctly from start to finish in the order they were presented.

To add a further wrinkle, the first case study specifies in the provided guide that the student should be retrieving the last 12 months of data from the database. However, the R script portion was written when the most recent data was Q1 2020, and it shows. As of the time of writing this (I started the case study at the end of August 2021), the 12 most recent months of data comprised several *million* rows in Excel, and more than a gigabyte of data in total. This amount of data cannot possibly be processed in the free version of RStudio or in a single Excel workbook; it might be doable in SQL, but that requires downloading a SQL platform to work with, since Google didn't see fit to supply us with appropriate access to one (like BigQuery) for the sake of completing this case study.

The course guide should have specified to use the exact same months of data that were used in the R script document for the Analysis phase of the case study. This would allow the student to follow that document from start to finish if there was any confusion about how to code the suggested analysis steps. (As a side note here, why on earth is this script document written in a Google Doc? This makes it practically unreadable. It should be an RMD file that could be followed along with, just like in Course 7.)

All told, the main point of this course turned out to be a disaster when I had really been looking forward to it. I was able to learn quite a bit by trying to troubleshoot all the errors that kept arising, such as the as.numeric() function given in the script document turning my whole ride_length column into NAs, but I learned the most by just trying to clean the data myself before I realized that the case study guide offered specific steps of how to clean and analyze the data. So, thank you, Google, for providing the basics in previous courses so that I could still teach myself something when your case study course turned into a train wreck.

By Katelin K

Jul 16, 2021

I decided not to do the project as it was too unclear, and the datasets were too large. Furthermore, if you chose the R route, you were given the script to use, which seemed too advanced for the amount of R we were taught in the certificate.

I did find the interview videos helpful.

Aug 26, 2021

The previous courses in this program do not adequately prepare students for this project, and there is no help to be found from when things go wrong. I powered through the other seven parts of this without incident, which should have triggered suspicion on my part that the instruction was not enough, but I legitimately thought I was just picking it up that easily.

Apr 24, 2021

This Capstone could be better and more guided. Case studies given are either too complicated or have very less data.

Jan 14, 2022

Sadly, a waste of time and money. Too much time was spent on the basics and too little time spent on technical know-how like SQL and R. The videos were boring, the assignments were too few and too pointless, and at the end as I struggled through the case study I realized how ill-prepared I was by this course. To help me finish the case study I started some free Codecademy lessons and learned more there in a few days than this entire course. I thought at least the job board availability at the end of the course would be worth it, but that is a disappointment as well. Unless you live in NYC or Silicon Valley, there is little opportunity to be found there. Perhaps this is a good course for those who need to brush up on their skills, but for true novices it's a big disappointment.

Aug 20, 2021

I was not expecting too much from this class due to the price, so this review is not based on the content, but rather the flow and presentation. For a good 60-70% of the course, you are bounced back and forth between Excel commands, SQL commands, testimonials from Google employees, and data analysis best practices. My notebook looks like 14 people were trying to take notes all at the same time. Trying to go back through my notes to find one particular command or concept was frustrating. Once the course starting covering R, it was a little better but still continued to be peppered with extra videos and distractions. Some of the videos were actually really informative, but how and when they were placed were not helpful to the learning process at all. Also, having a final/capstone project that is not reviewed or graded devalues the capstone project massively. I guess you get what you pay for.

By Stephanie

May 15, 2021

Informative course; excellent case studies and some real world applications, however, would be great if there was a downloadable template so students can take notes while reading/watching(not just transcripts but the slides & screenshots on instructor's screen with space to write notes/highlight ideas) This would help with traditional hardcopy people, kinesthetic learners, or those with neurodevelopmental disorders.

Jul 27, 2021

Instead of giving step by step process on a written pdf, you should show it in video step by step so we can perform it by following the process as when we get stuck we don t have anyone to ask what to do I cant ask a pdf copy what I can do if I get stuck.

By anatole c

Jun 24, 2022

95% of material is waste of time. 5% actually learning how to analyze/code/clean real datasets. Mountains of motivational bullshit that takes up time/energy and makes courses monotenous. really dissapointed. simply continued to get the progfessional certificate. wiould recommend others self study. while certificate may sound good on the resume you will be 100% unable to do the actual job after this course

By Daniel O

Jun 29, 2022

Unlike all previous courses within the Data Analytics Certificate, this one is very disappointing. There's a big gap between the skills one can earn throughout the courses and their applicability to the final Capstone project. We have learned general data analytics topics, specific tools, processes, etc. But we were studying them without a chance to look at the big picture - meaning, we were learning every skill in its own sandbox without adding them to the real analytics process. There's no chance to go through the data analysis from start to end similar to the capstone project, but with the supervision of the instructor. Thus, when entering the tasks listed under the capstone, you just don't know what to do. Yes, you know Excel, Google Sheets, SQL, R, and visualization tools, but you don't know where to begin with, how to put everything in order, what you should look at first, second, etc. In other words, there's a definitely missing course or part: a fully guided capstone project. The one in which the instructor(s) guides the learner throughout the project explaining the entire analytic process step by step, allowing learners to make attempts, and providing brief feedback.

This is why I personally have not completed the Capstone although I marked it as completed. I was simply lost in what I needed to begin with, what was next, and so on. Basically, the Capstone is useless for those who have no previous practical experience.

By Drashya P

Jun 2, 2021

fake interview video and useless tips very very disappointed with last course

Apr 21, 2021

Great course overall. Just wish the capstone part was a little more hands-on, and less optional. Helps with accountability.

Jul 20, 2022

This course soured my entire experience with the rest of the specialization.

The instruction on the actual case study is lacking at best, abysmal at worst. Even on the guided questions I was lost with very little direction, and the direction I did get seemed like it was for something else entirely.

What they should have done was give you two optional courses, one for the guided case study and one for the free case study. From there it can allow the instructor to go into more depth about what you needed to do.

What it actually did was give you two modules in the same week, both with vague checklists, and instructions that don't take the actual data you're working with into account.

For example: the bicycle dataset. They give you YEARS worth of data, going all the way back to 2004, and only want you to use about 12 months worth of it. They want you to use google sheets, R studio, or SQL for this. Well google sheets literally cannot handle that amount of data and you will frequently run out of RAM in R Studio UNLESS you upgrade to a full account, because the dataset is just so large. And uploading it to BigQuery literally required me to upload it to google drive first, and then give it a URI because the files were so massive. But one of the steps REQUIRES you to import it into google sheets.

The issue is that this course doesn't make you do case studies beforehand, it gives you bits and pieces of each part of a case study before throwing you out into sea and making you put all of it together yourself with no rhyme or reason. And it doesn't help you can clearly tell that each course was designed by someone different, which I was willing to put up with until this course assumed so much of the past few courses.

Is this course worth it? Yes, you still learn valuable insights on R, Tableau, SQL, Spreadsheets. But don't expect this to be your sole ticket into becoming a data analyst. Manage your expectations before starting.

By ramprakash y

Apr 16, 2021

it is really great course for Data Analyst

By Nadine L K

Mar 4, 2022

Extremely difficult task to undertake if you are a beginner in Data analytics. The 7 preceding courses guide you step by step through the basics, but the skill level required to complete the capstone project is truly overwhelming and leaves you floundering . Hugely dissatisfied ,especially after feeling so motivated and scoring 99% in the previous courses

Dec 9, 2021

The resources and guidance through the process of doing a Capstone Project were excellent. However, I was VERY disappointed in the help available to find a job! After I created my profile in the Coursera Job Platform and searched for Data Analyst jobs in Houston, TX, I found exactly zero! You led me to believe that you had a coalition of potential employers ready to consider new Google Data Analytics Certificate holders. That is one of the main reasons that I signed up for this program. Too bad it was not true. I know that there are plenty of analyst jobs available through the usual job search platforms like Indeed. Why are you not tapped into that market? Anyway, overall I don't regret earning the certificate. I am just disappointed at the lack of help leveraging it into a real job offer.

By Mohd. W S

May 8, 2021

Creating Portfolio is something I was missing even though I have 8 years of work experience as Data Analytics & Visualization Specialist, this remains a big take away for me.

By Lam C V D

Apr 15, 2021

Love the data analytics portfolio project, first time introduced by Google for showcasing case studies and interview preparation.

Apr 23, 2021

So helpful for Junior Data Analyst job search preparation!

Dec 28, 2021

Very little guidance about the case study, very disappointing

By The L O K

Jul 28, 2021

this is an amazing beginner friendly course for aspiring data analyst to learn the fundamentals. Coursea and google however should improve this online course by including more practice quizzes that involved coding, creating syntax, explaining errors not just in video form but in real scenario form. Overall i believe i am equip with basic knowledge to apply for a position as an entry level data analyst.

Aug 19, 2021

This is good for an overview of the different phases of analytics projects. However, when it came time to dive deeper into the substance, it felt like there was not any rhyme or reason to what concepts were taught or what order they were taught in. I do not feel prepared to engage in analysis, although I do feel that I better understand the process and what gaps I need to study more to fill in.

By Mohamad F B R

Jun 1, 2021

Really enjoyed the capstone project! This project allows learners to hone their hands-on skills which are taught throughout the course. Thank you Google for this opportunity