Skip to lesson
Exit
Capstone: Mock Loop1 / 2

2 min lesson

Task 1 - stage conversion by segment, without double-counting

Explain what the example in "Task 1 - stage conversion by segment, without double-counting" is doing and why it matters.

Step 1 of 2

Task 1 - stage conversion by segment, without double-counting

Collapse to one row per person before you count and count distinct people who ever reached each stage, not raw stage events. Make the NULL segment its own visible bucket instead of letting it vanish.

person-level stage conversion by segment; dedup people, bucket NULLs, count distinct reach-a-stage
WITH person AS (                       -- collapse duplicate leads to one person
  SELECT
    LOWER(TRIM(person_email))            AS person,
    MAX(lead_id)                         AS canonical_lead_id,
    COALESCE(MIN(segment), 'unknown')    AS segment   -- NULL segment is a real bucket, not a silent drop
  FROM leads
  WHERE person_email IS NOT NULL
  GROUP BY LOWER(TRIM(person_email))
),
reached AS (                            -- did this person EVER reach each stage
  SELECT
    p.person,
    p.segment,
    BOOL_OR(e.stage = 'captured')  AS hit_captured,
    BOOL_OR(e.stage = 'qualified') AS hit_qualified,
    BOOL_OR(e.stage = 'meeting')   AS hit_meeting,
    BOOL_OR(e.stage = 'won')       AS hit_won
  FROM person p
  JOIN leads l   ON l.lead_id = p.canonical_lead_id
  JOIN stage_events e ON e.lead_id IN (   -- all lead_ids belonging to this person
        SELECT lead_id FROM leads WHERE LOWER(TRIM(person_email)) = p.person)
  GROUP BY p.person, p.segment
)
SELECT
  segment,
  COUNT(*) FILTER (WHERE hit_captured)  AS captured,
  COUNT(*) FILTER (WHERE hit_qualified) AS qualified,
  COUNT(*) FILTER (WHERE hit_meeting)   AS meeting,
  COUNT(*) FILTER (WHERE hit_won)       AS won,
  ROUND(100.0 * COUNT(*) FILTER (WHERE hit_qualified)
              / NULLIF(COUNT(*) FILTER (WHERE hit_captured), 0), 1) AS cap_to_qual_pct
FROM reached
GROUP BY segment
ORDER BY captured DESC;
Say it like this

“I dedup to a person on normalized email before counting, because the same human captured twice would otherwise inflate every stage. I count distinct people who ever reached a stage with BOOL_OR, not raw stage_events, because a lead can re-enter a stage and that would overstate the funnel. And I'm bucketing NULL segment as 'unknown' rather than dropping it, so the self-serve signups stay in the denominator instead of quietly disappearing.”