The Funnel Analysis Pattern That Makes Data Scientists’ Queries Churn

Build Your Funnel Upside Down

How to stitch multi-table funnel data accurately —A step-by-step pattern for bottom-up stitching, parameterized windows, and production-ready gold tables

Funnel analysis sounds simple: track users from discovery to conversion. In practice, you’re joining across multiple event tables with hundreds of millions of rows, and every new funnel variant means rewriting SQL from scratch.

The fix isn’t better SQL. It’s join order.

By the end of this post you will know how to:

— Reduce table scans by 99% using bottom-up stitching

— Avoid data leakage by anchoring date windows on every CTE

— Eliminate inflated counts by choosing first, last, or all conversions explicitly

— Structure queries that run weekly and backfill cleanly

— no rewrites — Write idempotently to a gold table safe for repeated pipeline runs

— Layer a materialized view on top of the gold table for fast dashboard queries

Here is what we are building:

events.feature_events ─┐

events.product_events─┼─→ Scheduled job (weekly, backfillable)

core.users. ─┘

↓

gold.feature_funnel_weekly

↓

Materialized view (auto-refresh)

↓

gold.feature_funnel_daily (dashboards, stakeholder queries)

The problem with top-down

Most people start from the biggest table and filter down. For a typical funnel across three event sources, that means scanning 500M+ rows before you find the 200K users you actually care about — across the full lifetime of the table.

Step  Table           Rows scanned
───────────────────────────────────
1     All events      500M+
2     Interactions    200M+
3     Conversions     200K

Total: 800M+ rows to answer a 200K-row question.

The fix: start from the bottom

Flip the join order. Start with the smallest table — the users who reached the conversion event — and join upward.

Step  Table           Rows processed
──────────────────────────────────────
1     Conversions     200K  ← start here
2     Interactions    180K  matched
3     All events      195K  matched

Total: ~575K rows. Same answer. 99% less scanning.

Why it works: you’re carrying a small set of user keys at each join. The query optimizer broadcasts these instead of shuffling hundreds of millions of rows across your cluster.

Applying this to a real funnel

Say you’re tracking the user journey to a conversion event. Your data lives across two tables: events.feature_events and events.product_events.

Don’t do this:

-- Top-down: scans everything first
SELECT * FROM events.feature_events       -- 500M+ rows, full table
JOIN events.product_events USING (user_id)
WHERE event = 'buy_now_click'

Step 1: parameterize your window — never use CURRENT_DATE

⚠️ Critical with backward stitching: each upstream table has no inherited date context from the previous join. Without an explicit filter on every CTE, you scan the full history of every table. And since this runs as a weekly scheduled job — not a one-time query — hardcoding CURRENT_DATE makes backfilling impossible.

Get Rajeshwari Raghuraman’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Pass the window as parameters so the same query powers both the weekly refresh and any historical backfill:

-- Parameters set by your scheduler (Airflow, dbt, cron, etc.)
SET run_date      = '2024-02-05';  -- week ending date, controlled by scheduler
SET lookback_days = 7;             -- always 7 for weekly runs

-- To backfill 6 months: loop run_date over historical week-ending dates.
-- Keep lookback_days = 7 on every iteration — do NOT widen it.
-- Move the window backward by stepping run_date, not by expanding lookback_days.

Weekly run:   run_date steps forward by 7 days each Monday
              2024-01-08 → 2024-01-15 → 2024-01-22 → ...

Backfill run: scheduler loops run_date over past N weeks
              2023-08-07 → 2023-08-14 → ... → 2024-02-05
              (lookback_days stays at 7 on every loop iteration)

Step 2: decide whose conversion you want

Before writing a single line of SQL, answer this: for users who converted multiple times, which event do you want?

Choice              Use when
──────────────────────────────────────────────────
First conversion    Onboarding funnels, first-touch
Last conversion     Most recent engagement analysis
All conversions     Volume and frequency analysis

This choice changes your SQL. The default (all rows) is rarely what you want and silently inflates funnel counts.

Step 3: write the query for your defined use case

Our use case: did a user land in the feature within their first 4 weeks of account creation? We want their first conversion only.

Two windows to keep distinct:

Scan window (run_date — lookback_days to run_date) — bounds what you read from each table. Must be at least as wide as the onboarding window. Here we use run_date — 35 days (28-day onboarding + 7-day buffer) so landing events early in a user’s onboarding are never excluded by the scan.

Onboarding window (signup_ts + 28 days) — the business rule for qualifying users. Independent of the scan window.

-- Bottom-up: first conversion, parameterized for weekly runs and backfill
WITH first_converters AS (
  -- anchor: one row per user, earliest conversion only
  -- scan window covers the full 28-day onboarding window + buffer
  SELECT
    user_id,
    MIN(event_ts) AS first_conversion_ts
  FROM events.feature_events
  WHERE event      = 'buy_now_click'
    AND event_date BETWEEN DATEADD(day, -35, $run_date) AND $run_date
  GROUP BY user_id
),
new_users AS (
  -- restrict to users whose first conversion fell within 4 weeks of signup
  SELECT
    fc.user_id,
    fc.first_conversion_ts,
    u.created_at AS signup_ts
  FROM first_converters fc
  JOIN core.users u USING (user_id)
  WHERE fc.first_conversion_ts <= u.created_at + INTERVAL '28 days'
),
engaged AS (
  -- upstream join: matched users only, within onboarding window
  SELECT p.user_id, p.event, p.event_ts
  FROM events.product_events p
  JOIN new_users n USING (user_id)
  WHERE p.event_date BETWEEN DATEADD(day, -35, $run_date) AND $run_date
    AND p.event_ts <= n.signup_ts + INTERVAL '28 days'
),
discovered AS (
  -- top of funnel: feature landing must precede conversion
  SELECT f.user_id, f.event, f.event_ts
  FROM events.feature_events f
  JOIN new_users n USING (user_id)
  WHERE f.event      = 'feature_landing'
    AND f.event_date BETWEEN DATEADD(day, -35, $run_date) AND $run_date
    AND f.event_ts   < n.first_conversion_ts   -- enforces event ordering
    AND f.event_ts  <= n.signup_ts + INTERVAL '28 days'
)
SELECT
  n.user_id,
  n.signup_ts,
  d.event_ts  AS landing_ts,
  e.event_ts  AS engaged_ts,
  n.first_conversion_ts,
  $run_date   AS pipeline_run_date  -- audit: the scheduler date, not the event date
FROM new_users n
JOIN discovered d USING (user_id)
JOIN engaged    e USING (user_id)

Four things this pattern enforces that naive funnel SQL misses:

• Parameterized window — same query runs weekly and backfills; no hardcoded dates
• Scan window wider than onboarding window — 35 days covers the full 28-day rule with buffer
• First conversion only (MIN(event_ts)) — one row per user, no inflated counts
• Event ordering enforced (event_ts <) — upstream events must precede conversion

Step 4: write to your gold table idempotently

Since this runs on a schedule, the output table must handle reruns safely. Always delete the current window before inserting so reruns don’t double-count:

-- Idempotent write: delete then reinsert for the current run window
DELETE FROM gold.feature_funnel_weekly
WHERE pipeline_run_date = $run_date;

INSERT INTO gold.feature_funnel_weekly
SELECT
  n.user_id,
  n.signup_ts,
  d.event_ts  AS landing_ts,
  e.event_ts  AS engaged_ts,
  n.first_conversion_ts,
  $run_date   AS pipeline_run_date
FROM new_users n
JOIN discovered d USING (user_id)
JOIN engaged    e USING (user_id);

⚠️ pipeline_run_date is the scheduler date for that run — not the event date. On a backfill, rows from August 2023 will correctly carry pipeline_run_date = ‘2023–08–07’ only if the scheduler passes the historical date. Always verify your scheduler is passing the correct historical run_date and not today’s date when backfilling.

The output: materialized view on top of the gold table

gold.feature_funnel_weekly is your stitched, parameterized output — built by the scheduled job above. On top of it, define a materialized view for daily aggregation. This is what dashboards and stakeholders query — never the raw event sources, never the stitching layer directly.

CREATE MATERIALIZED VIEW gold.feature_funnel_daily AS
SELECT
  CAST(w.first_conversion_ts AS DATE)  AS event_date,
  u.platform,
  COUNT(DISTINCT CASE WHEN w.landing_ts  IS NOT NULL THEN w.user_id END) AS landed,
  COUNT(DISTINCT CASE WHEN w.engaged_ts  IS NOT NULL THEN w.user_id END) AS engaged,
  COUNT(DISTINCT w.user_id)                                               AS converted,
  ROUND(COUNT(DISTINCT w.user_id) /
    NULLIF(COUNT(DISTINCT CASE WHEN w.landing_ts IS NOT NULL
                               THEN w.user_id END), 0), 4)               AS conversion_rate
FROM gold.feature_funnel_weekly w
JOIN core.users u USING (user_id)
GROUP BY event_date, u.platform;

-- Refresh after every weekly job run
-- Schedule this immediately after the gold.feature_funnel_weekly insert completes
REFRESH MATERIALIZED VIEW gold.feature_funnel_daily;

⚠️ Always refresh the MV after the scheduled job completes — not on a separate independent schedule. If the MV refreshes before the weekly job finishes writing, dashboards will show stale or partial data for that run window. In Airflow, chain it as a downstream task:

weekly_funnel_job  →  refresh_funnel_daily_mv  →  notify_stakeholders

Why a materialized view here and not another scheduled job:

gold.feature_funnel_weekly  →  needs a scheduled job
                                parameterized ($run_date, $lookback_days)
                                backfillable, idempotent, complex joins

gold.feature_funnel_daily   →  materialized view is the right fit
                                single source (gold table only, no joins across raw tables)
                                simple aggregation — no runtime parameters needed
                                auto-refreshes as gold table is updated

Checklist before you build

Question                                       Good answer
────────────────────────────────────────────────────────────────────
Which table has the fewest rows?               Start joins there
Joining top-down across large tables?          Flip to bottom-up
Date filter on every CTE?                      Yes — on every single one
Scan window wide enough for business window?   scan window ≥ business rule window
Using CURRENT_DATE in a scheduled job?         Replace with $run_date parameter
First, last, or all conversions?               Decide before writing SQL
Upstream events ordered before conversion?     Enforce with event_ts <
User GUIDs in your gold table?                 Remove — aggregate only
Gold table write idempotent?                   DELETE + reinsert on run_date

Tags: Data Engineering · Funnel Analysis · SQL · AnalyticsThe join order is the architecture. Get it right once and every funnel variant gets accurate and Data Scientist dont need to wait for the queries to churn indefinetely- Its a win-win.