GUIDED PROJECT INTERMEDIATE DIAGNOSTIC

Sales funnel analysis why is revenue underperforming?

You're the analyst at a UK fashion e-commerce brand. Revenue is missing target and the team needs a diagnosis. You'll build the funnel end-to-end in SQL, find the real drop-offs, and ship a Looker Studio dashboard plus a stakeholder recommendation.

TOOLS & TECHNIQUES

SQL BigQuery Looker Studio Funnel modeling Cohort analysis Recommendation framing

▶ Start chapter 1

Google Meta

I've personally interviewed hundreds of data candidates.

Pasquale Bruno Founder · datascienceportfol.io Read bio

⏱

8–10 hours

self-paced

◉

9 chapters

video + written

◱

Real-shaped data

Bella & Oak · 25k sessions

✦

Built with Claude

Ch 8 (optional): AI commentary

→

1-click publish

to your portfolio

WHAT YOU'LL WALK AWAY WITH

Real, portfolio-ready artifacts, not a certificate.

✓

Funnel diagnostic writeup

✓

Looker Studio dashboard

✓

Stakeholder recommendation

✓

Portfolio case study page

THE SCENARIO

Diagnose the funnel. Defend the call.

**Bella & Oak** is a UK fashion e-commerce brand (synthetic, real-shaped data, 25k sessions). Revenue is below target and the leadership team wants to know **why** before they pick the next quarter's bets.

You'll model the funnel (Browse → Add to Cart → Checkout → Purchase), segment drop-offs by **device, customer type, channel and product category**, build a **Looker Studio dashboard** the team can run themselves, and write a one-page recommendation.

BY THE END, YOU'LL BE ABLE TO

✓

Model a multi-step funnel in SQL and spot statistically meaningful drop-offs

✓

Segment funnel drop-off by device, customer type, channel and product category

✓

Build a Looker Studio dashboard the business can run themselves

✓

Frame a fuzzy business problem into an answerable analytical question

✓

Defend a recommendation under pressure, with charts, numbers and assumptions

PROJECT CURRICULUM

9 chapters · ~8–10 hours

CLAUDE API NEW 21 videos 8 written lessons

Project brief & setup

video + setup

45 min +

● Walk through the brief: a Monday-morning CMO message: £2.3M spent, revenue 24% below target

● Meet the dataset: Bella & Oak · 25k sessions, 8k customers, 6 campaigns, Q3 2024

● Set up BigQuery (`p2_funnel` dataset) and a public GitHub repo with GitHub Desktop

● Understand `funnel_stage`: it stores the deepest stage reached, not a current state

Understand the data

video + SQL exploration

45 min +

● Confirm row counts, distinct values, and the Q3 date range (92 days, evenly spread)

● Understand the two deliberate NULLs: `order_value` (non-purchase sessions) and `campaign_id` (unattributed traffic)

● Sessions-per-customer distribution and the summary snapshot: 935 purchases, 3.7% conversion, £81,995 revenue

● £82k revenue against £2.3M spend: the number that anchors the whole project

Build the funnel

video + SQL

55 min +

● Build the core funnel query with COUNTIF: count sessions that reached each stage, not just stopped there

● Window functions: `LAG` and `SUM() OVER ()` for stage-to-stage drop-off rates

● The headline finding: 77.8% of sessions never add anything to cart

● Confirm conversion is flat across July/August/September: a structural problem, not a bad month

Drop-offs by device and customer type

video + SQL

1h 5m +

● `PARTITION BY` to break the funnel down per device and per customer type

● The project's biggest finding: mobile converts at 2.0% vs desktop at 6.2%, concentrated at Checkout → Purchase

● Returning customers convert 2x new customers; combine both cuts to confirm device is the dominant variable

● First JOIN in the project: age group analysis finds 18-24s converting at half the rate of everyone else

Drop-offs by channel and product category

video + SQL

1h 0m +

● `LEFT JOIN` + `COALESCE` to fold in campaign channel and handle unattributed sessions

● Channel efficiency: Email returns £0.23 per £1 spent, Paid Social returns £0.02 on an £800k budget

● Paid Social's problem is traffic quality (Browse → Cart), not checkout, so a different fix than mobile's

● Outerwear's highest AOV (£149) comes with its lowest conversion (2.4%): high-price hesitation, not checkout failure

Looker Studio dashboard

video + dashboard

1h 30m +

● Four questions before opening the tool: audience, dashboard type, one message, level of detail

● Four BigQuery views power eight charts across two Looker Studio pages

● Page 1: the funnel exists and is flat. Page 2: who's causing it (device, age group, channel)

● Share by link and screenshot the finished dashboard into the repo

Stakeholder deck & recommendation

video + deck

55 min +

● The golden rule: lead with the recommendation on slide 2, not buried on slide 6

● Six slides: situation, answer, three findings, action plan with owners and timelines

● Frame every number as a ratio a human can feel: '3x less likely', not '2.0% vs 6.2%'

● Write the 200-word standalone email version of the same recommendation

Ship it

video + ship

40 min +

● Write a README that answers what/why/how-to-run in under 400 words

● Clean, commented queries folder with a `views/` subfolder for the 4 BigQuery views

● Draft a LinkedIn post that leads with numbers, not 'excited to share'

● Publish the project: GitHub, live dashboard link, portfolio update

AI commentary layer (optional) 🧠 CLAUDE API

video + code

45 min +

● Coming soon: content in Notion.

▶ 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 Looker Studio Funnel modeling Cohort analysis Recommendation framing Drop-off analysis Window functions (LAG, PARTITION BY) Chart design Executive writing

PREREQUISITES

Before you start

Basic SQL (SELECT, JOIN, GROUP BY, CASE) REQUIRED

Free Google Cloud / BigQuery + Looker Studio account (free) REQUIRED

Familiarity with B2C e-commerce business model HELPFUL

Some pandas if you want to do the optional AI chapter 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.

AI commentary layer

Claude reads your funnel cuts and drafts a paragraph for each one, in your voice, your data

Optional chapter

The funnel + dashboard work on their own; the AI layer is a polish step on top

Reusable pattern

Same approach plugs into any cohort analysis you do later

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 to pay for BigQuery? +

No, the free tier is more than enough. Looker Studio is free too.

What if I'm rusty on SQL? +

We build up from SELECT/JOIN/GROUP BY. By chapter 3 you'll be comfortable with funnel-shaped queries.

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, funnel diagnosis is one of the most-asked analyst case-study formats.

CONTINUE YOUR PATH