How I'd Attack the Google Data Analytics Capstone Case Study (In 15 Minutes)

The Google Data Analytics Capstone Challenge: 15 Minutes

What would you do if you only had 15 minutes to complete an analysis before executives have to make a decision that's going to affect the future of the company?

It's not super common that you're going to be put in such a quandary, but hey—I've seen crazier things happen.

Here's what I'm going to do: I'm going to attack Track 1, Case Study 1 from the Google Data Analytics Certificate capstone—the Cyclistic Bike Share analysis.

I haven't practiced this. It's just been an idea I've had for a long time, and today is the day.

The Business Question

This is an opportunity to analyze historical bicycle trip data to identify trends. Understanding how casual riders behave differently from riders with paid memberships is important. This analysis will help executives make decisions about marketing programs and strategies to convert casual riders to annual memberships.

So the punch line: Find differences between casual and member riders so we can convert casuals to members.

The Data

I downloaded the last 12 months of data from Divvy (a real bike-share company in Chicago, owned by Lyft). The data is public.

First file I opened: 821,000 rows of data. That's a lot.

Columns include:

• Rideable type (classic bikes, electric bikes, scooters)
• Start/end timestamps
• Start/end station names and IDs
• Latitude and longitude
• Member vs. casual rider

I'm not going to mess with the lat/long or station names. That feels like a distraction from the core question. We're not marketing to street corners—we're marketing to people.

My Approach: Power Query

I'm going to use Power Query to pull all 12 files together and transform the data.

Steps:

1. Remove columns I don't need (station names, lat/long)
2. Keep: rideable type, start time, end time, member/casual
3. Add calculated columns:
   • Month
   • Day of week
   • Hour
   • Ride duration (end minus start, converted to minutes)
4. Create ride time buckets (under 10 min, 10-30 min, 30-60 min, over 60 min)
5. Group by all dimensions and count rows

This collapses millions of rows down to something Excel can handle quickly.

What I Found (In About 5 Minutes of Analysis)

Seasonality

Nice season = more rides. Colder season = fewer rides. Not shocking for Chicago, but there are still 144,000 rides in January. That's a lot of people.

Member vs. Casual Distribution

Members and casuals follow almost identical seasonal patterns. There are just more members than casuals overall.

Day of Week Patterns

This is interesting:

• Members ride more during the week, less on weekends
• Casuals ride more on weekends, less during the week

This suggests members are commuting. Casuals are weekend warriors.

Time of Day Patterns

Even more interesting:

• Members spike at 8 AM and 5 PM (going to and from work)
• Casuals peak around afternoon/dinner time with no morning spike

Members are commuters. Casuals are tourists or recreational riders.

Ride Duration

The overwhelming majority of rides are under 30 minutes. Most are under 10 minutes. These are point A to point B trips, not long recreational outings.

The Strategic Insight

Look at the seasonal data. You've got 5-7 months where ridership is significantly lower. Why would I sign up for a full year if I know I won't use it for six months?

My recommendation: Offer more membership options.

Right now it's annual membership or pay per ride. But look at any metro system—they offer:

• Day passes
• 3-day passes
• Weekend passes
• One-week passes
• One-month passes

Meet people where they are. A tourist isn't going to buy an annual membership, but they might buy a weekend package.

Where to Market

If you're trying to convert casuals:

• Hotels: Offer weekend packages to travelers
• Offices: Partner with businesses to incentivize employee commuting
• Timing: Market right before the nice season starts (March-April) so it's top of mind

Marketing to people in January? They're going to think you're crazy.

The Real Point

The whole intent behind this exercise is to get you reps.

Thinking about data. Working with data. Playing with data. Coming up with a story. Coming up with a narrative.

If you want to make the most out of this:

1. Do it yourself
2. Build it
3. Polish it up
4. Make a video

Publish it. Talk through what you did, why you did it, what you enjoyed, what additional information you'd want.

That's what interviewers want to see—how you think, how you interact with data, how you think about business concepts.

The Bottom Line

I did all of this in Excel. Power Query and pivot tables. No Python, no R, no fancy tools.

The point isn't the tools. The point is:

• Understanding the business question
• Finding the relevant data
• Telling a story that leads to action

That's what makes an analyst.

Common Questions About the Google Data Analytics Capstone

Q: Do I have to use Excel for the capstone, or can I use Python/R?

Use whatever tools you're comfortable with. The point is to demonstrate your analytical thinking, not to show off specific tools. I used Excel because that's what I know best.

Q: How much time should I actually spend on the capstone?

Don't just speed-run it in 15 minutes like I did. Take your time. Polish it. Make it something you're proud to show in interviews. Budget at least a week to do it right.

Q: Should I do Track 1 (pre-made case studies) or Track 2 (my own data)?

If you have something you're passionate about, do Track 2. You'll have a better story to tell in interviews. If not, Track 1 is perfectly fine—just make sure you go deeper than surface-level analysis.