GUIDED PROJECT BEGINNER DISCOVERY

A marketplace health analysis why 97% of customers never come back

Monday morning, week one. Your manager hands you the BigQuery schema for a Brazilian e-commerce marketplace and wants a picture of the business (revenue, customers, sellers, delivery, satisfaction) before Friday's exec review.

TOOLS & TECHNIQUES

SQL BigQuery Python Pandas Stakeholder writing Two-call AI architecture

▶ Start chapter 1

Google Meta

I've personally interviewed hundreds of data candidates.

Pasquale Bruno Founder · datascienceportfol.io Read bio

⏱

5–7 hours

self-paced

◉

9 chapters

video + written

◱

Real dataset

Olist · 100k orders, 2016–2018

✦

Built with Claude

Ch 5: interpretation + briefing

→

1-click publish

to your portfolio

WHAT YOU'LL WALK AWAY WITH

Real, portfolio-ready artifacts, not a certificate.

✓

Annotated SQL query library

✓

Python notebook with answers

✓

One-page executive briefing

✓

Portfolio case study page

THE SCENARIO

Real BigQuery, real questions, real exec review.

**Olist** is a Brazilian e-commerce marketplace with **100k orders** across 2016–2018, anonymised and officially released. The data is in BigQuery and your manager just messaged: 'I need a picture of the business (revenue, customers, sellers, delivery, satisfaction) before Friday's exec review.'

You'll write **9 SQL queries** answering 9 specific business questions, layer Claude on top to interpret each result, and end with a **one-page executive briefing** for the VP of Marketplace.

BY THE END, YOU'LL BE ABLE TO

✓

Translate fuzzy business questions into specific SQL queries

✓

Use 3-table joins, date truncation and CASE WHEN to answer revenue, customer, seller and delivery questions

✓

Use Claude API to produce plain-English interpretation of each query result

✓

Synthesise 9 separate analyses into a one-page executive briefing

✓

Ship a polished GitHub repo + LinkedIn post that reads like real analyst work

PROJECT CURRICULUM

9 chapters · ~5–7 hours

CLAUDE API NEW 20 videos 9 written lessons

Project brief & setup

video + setup

30 min +

● Walk through the brief: a Monday-morning request from the VP of Marketplace

● Meet the dataset: Olist · 100k orders · 7 tables (skip order_payments, geolocation)

● Set up BigQuery: create the `olist_ecommerce_1` dataset, upload 6 tables via the UI

● Clean and upload `order_reviews` (drop free-text columns first to avoid CSV errors)

● Verify expected row counts across all 7 tables

● Create the GitHub repo + folder structure (notebooks/ sql/ outputs/)

● Run a connection-check query to confirm Colab → BigQuery is live

Understanding the data

video + SQL exploration

40 min +

● Walk through all 7 tables, column by column

● The big lesson: `customer_id` vs `customer_unique_id` (the 3.5% overcount risk)

● NULL audit on delivery dates: why 2,965 orders have no delivered date

● Order status distribution (97% delivered)

● The four rules every query must respect: revenue includes freight, customer counts use `customer_unique_id`, delivery filters need explicit NULL checks, categories need the Portuguese↔English translation join

Revenue: Q1 & Q2

video + SQL

50 min +

● Q1: Total revenue + monthly trend (FORMAT_DATE, JOIN, GROUP BY date alias)

● Q2: Revenue by product category + AOV (4-table JOIN through the translation lookup)

● Spot the data artefacts: tiny Sept/Dec 2016 (platform launching), Aug 2018 cutoff, Black Friday spike (Nov 2017 = R$1.15M)

● Two ways to be a high-revenue category: high volume + low ticket vs low volume + high ticket

● Save Q1 + Q2 to `sql/olist_queries.sql` with comment headers

Customers & sellers: Q3, Q4 & Q5

video + SQL

1h 0m +

● Q3: Unique customers + order frequency (subquery + COUNT DISTINCT customer_unique_id)

● Q4: Customers and revenue by state (3-table JOIN, ratio calc inside SELECT)

● Q5: Top 10 sellers (clean two-table JOIN + LIMIT)

● The 97% single-order rate, and what it means for a marketplace

● SP dominance: 41% of customers, 9 of the top 10 sellers

● Why northern states show a higher revenue per customer (freight, not affluence)

Delivery & satisfaction: Q6, Q7, Q8 & Q9

video + SQL

1h 10m +

● Q6: Late delivery rate + monthly trend (COUNTIF, NULL handling)

● Q7: On-time delivery by state (clear north/south divide; sample-size caveats)

● Q8: Average review score by order status (delivered 4.16 vs shipped 2.01)

● Q9: The payoff: late vs on-time review score (1.72-star gap on a 5-point scale)

● Why the NULL filters in Q6 and Q9 are not optional

Claude API layer 🧠 CLAUDE API

video + Python + Claude API

40 min +

● Set up the Anthropic SDK + API key in Colab Secrets

● Call 1: Per-question interpretation: one Claude call per query result, plain-English sentence

● Call 2: Aggregate synthesis: a single call producing the one-page executive briefing

● Why a two-call architecture beats one big call (constrained outputs, then synthesis)

● Save the briefing to `outputs/executive_summary.md`

Stakeholder deliverable

written only

30 min +

● The five quality dimensions: specificity, brevity, one implication per finding, a clear priority action, honesty about limits

● Section-by-section checklist against the Claude draft (every key number must be present)

● Final structure: header, five sections, priority action, dataset note

● Commit the final version to GitHub

Ship it

video + ship

40 min +

● Generate the README with Claude (using your real findings as the prompt)

● Push the repo to GitHub: README, notebook (with outputs!), sql/, outputs/

● Upload `olist_queries.sql` to Kaggle as a citable dataset

● Draft the LinkedIn post (lead with the surprising finding; link in first comment)

Frame it for employers

interview prep

25 min +

● Walk-me-through-your-project in 60 seconds: problem first, not tools

● Explain `customer_unique_id` in 30 seconds

● Defend the NULL-filter decision in the delivery queries

● 'Where did you use AI?' The two-call architecture, in your words

● 14 numbers to know cold + 3 follow-up questions to volunteer

▶ Start chapter 1

PREVIOUSLY

Google Meta

YOUR INSTRUCTOR

Pasquale Bruno Founder · datascienceportfol.io

I spent 10+ years in data and product roles at Google and Meta, including hundreds of hiring-panel interviews, so I know exactly what makes a hiring manager keep reading. I design and review every guided project myself, using briefs and datasets modelled on real analyst work, not toy CSVs. If you get stuck, you're emailing me directly, not a support queue.

10+ yrs

IN INDUSTRY

500+

CANDIDATES INTERVIEWED

300+

PORTFOLIOS REVIEWED

See all projects Connect on LinkedIn

SKILLS YOU'LL PRACTICE

Tools & techniques

SQL BigQuery Python Pandas Stakeholder writing Two-call AI architecture 3- and 4-table joins CASE WHEN Date truncation Marketplace metrics

PREREQUISITES

Before you start

Basic SQL (SELECT, JOIN, GROUP BY) REQUIRED

Basic Python (variables, functions, lists, dicts) REQUIRED

Free Google Cloud / BigQuery account (we'll walk through setup) REQUIRED

Familiarity with e-commerce business basics HELPFUL

🧠 NEW · USE AI LIKE A REAL JOB

Ship analysis with the Claude API in the loop.

Real data teams aren't writing SQL one query at a time anymore. A whole chapter teaches you to wire Claude into a real analysis, the way analysts at Stripe, Anthropic and Notion actually work.

Per-query interpretation

One Claude call per query, plain-English commentary you can paste into a notebook

Aggregate exec briefing

Single synthesis call across all 9 results, one-page output for the VP

The two-call architecture

Reusable pattern you'll build on in P4, P-X and beyond

Pricing

One plan gets you every Guided Project.

Free builds your portfolio. Job Ready adds every Guided Project on top, live now and every one we ship next.

Free

$0/forever

For anyone with projects ready to share.

✓ Portfolio website with unlimited projects
✓ Personalised URL
✓ No design nor technical skills needed
✓ All your education and experience info in one place

Start free

Job Ready

$99/ month

For people investing in a data career.

✓ Portfolio website builder PRO
✓ All current + future Guided Projects
✓ Discord support channel for any questions, answered by me
✓ Weekly live office hours with me
✓ 1:1 monthly call with me (first 20 members)
✓ Async portfolio & LinkedIn reviews (first 20 members)
✓ Cancel anytime

Get Job Ready

Not sure if it's right for you?

Book a free 15-minute call with me. We'll talk through where you are and whether a Guided Project makes sense.

Book a call →

HOW WE'RE DIFFERENT

Why this isn't another course.

FEATURE THIS PROJECT DATACAMP COURSERA

Real business scenario, not toy data ✓ ✕ ✕

Portfolio-ready deliverable ✓ ✕ –

1-1 feedback on your writeup ✓ ✕ ✕

Published to your own site ✓ ✕ ✕

Certificate of completion ✓ ✓ ✓

Thousands of micro-lessons ✕ ✓ ✓

FREQUENTLY ASKED

Questions, answered.

Do I need a paid BigQuery account? +

No, Google Cloud's free tier is more than enough for this project. We walk through the setup.

Do I need advanced SQL? +

No, if you can write SELECT with JOIN and GROUP BY, you're ready. We build up from there.

Can I cancel anytime? What about refunds? +

Yes, cancel anytime, no lock-in, right from your account. We don't offer refunds on subscription payments already made, but you'll keep full access through the end of the billing period you've already paid for.

Can I use this in interviews? +

Yes, and we encourage it. The questions map directly onto common analyst case-study rounds.

CONTINUE YOUR PATH