Анализ воронки на чистом SQL: от визита до покупки

CREATE TABLE user_events (
  user_id UInt64,
  event_type String,  -- 'visit', 'catalog', 'product_view', 'add_to_cart', 'purchase'
  event_time DateTime,
  session_id String,
  page_url String,
  product_id Nullable(String),
  revenue Nullable(Float64)
) ENGINE = MergeTree()
ORDER BY (user_id, event_time);

SELECT
  'visit' AS step,
  1 AS step_order,
  COUNT(DISTINCT user_id) AS users
FROM user_events
WHERE event_type = 'visit'
  AND toDate(event_time) BETWEEN '2026-03-01' AND '2026-03-15'

UNION ALL

SELECT 'catalog', 2,
  COUNT(DISTINCT user_id)
FROM user_events
WHERE event_type = 'catalog'
  AND toDate(event_time) BETWEEN '2026-03-01' AND '2026-03-15'

UNION ALL

SELECT 'product_view', 3,
  COUNT(DISTINCT user_id)
FROM user_events
WHERE event_type = 'product_view'
  AND toDate(event_time) BETWEEN '2026-03-01' AND '2026-03-15'

UNION ALL

SELECT 'add_to_cart', 4,
  COUNT(DISTINCT user_id)
FROM user_events
WHERE event_type = 'add_to_cart'
  AND toDate(event_time) BETWEEN '2026-03-01' AND '2026-03-15'

UNION ALL

SELECT 'purchase', 5,
  COUNT(DISTINCT user_id)
FROM user_events
WHERE event_type = 'purchase'
  AND toDate(event_time) BETWEEN '2026-03-01' AND '2026-03-15'

ORDER BY step_order;

WITH funnel AS (
  SELECT
    step,
    step_order,
    users,
    lagInFrame(users) OVER (ORDER BY step_order) AS prev_step_users,
    first_value(users) OVER (ORDER BY step_order) AS total_visitors
  FROM (
    SELECT
      event_type AS step,
      multiIf(
        event_type = 'visit', 1,
        event_type = 'catalog', 2,
        event_type = 'product_view', 3,
        event_type = 'add_to_cart', 4,
        event_type = 'purchase', 5,
        0
      ) AS step_order,
      COUNT(DISTINCT user_id) AS users
    FROM user_events
    WHERE toDate(event_time) BETWEEN '2026-03-01' AND '2026-03-15'
      AND event_type IN ('visit', 'catalog', 'product_view', 'add_to_cart', 'purchase')
    GROUP BY event_type
  )
)
SELECT
  step,
  users,
  round(users / total_visitors * 100, 2) AS pct_of_total,
  if(prev_step_users > 0,
    round(users / prev_step_users * 100, 2),
    100
  ) AS step_conversion,
  if(prev_step_users > 0,
    prev_step_users - users,
    0
  ) AS dropoff
FROM funnel
ORDER BY step_order;

SELECT
  level,
  COUNT(*) AS users
FROM (
  SELECT
    user_id,
    windowFunnel(86400)(
      event_time,
      event_type = 'visit',
      event_type = 'catalog',
      event_type = 'product_view',
      event_type = 'add_to_cart',
      event_type = 'purchase'
    ) AS level
  FROM user_events
  WHERE toDate(event_time) BETWEEN '2026-03-01' AND '2026-03-15'
  GROUP BY user_id
)
GROUP BY level
ORDER BY level;

WITH user_steps AS (
  SELECT
    user_id,
    event_type,
    event_time,
    multiIf(
      event_type = 'visit', 1,
      event_type = 'catalog', 2,
      event_type = 'product_view', 3,
      event_type = 'add_to_cart', 4,
      event_type = 'purchase', 5,
      0
    ) AS step_order
  FROM user_events
  WHERE toDate(event_time) BETWEEN '2026-03-01' AND '2026-03-15'
),
step_times AS (
  SELECT
    user_id,
    event_type AS step,
    step_order,
    event_time,
    lagInFrame(event_time) OVER (
      PARTITION BY user_id ORDER BY step_order, event_time
    ) AS prev_step_time
  FROM user_steps
)
SELECT
  step,
  round(avg(dateDiff('minute', prev_step_time, event_time)), 1) AS avg_minutes,
  quantile(0.5)(dateDiff('minute', prev_step_time, event_time)) AS median_minutes,
  quantile(0.9)(dateDiff('minute', prev_step_time, event_time)) AS p90_minutes
FROM step_times
WHERE prev_step_time IS NOT NULL
  AND step_order > 1
GROUP BY step, step_order
ORDER BY step_order;

SELECT
  utm_source,
  countDistinctIf(user_id, event_type = 'visit') AS visits,
  countDistinctIf(user_id, event_type = 'add_to_cart') AS carts,
  countDistinctIf(user_id, event_type = 'purchase') AS purchases,
  round(
    countDistinctIf(user_id, event_type = 'purchase')
    / countDistinctIf(user_id, event_type = 'visit') * 100, 2
  ) AS total_cr,
  round(
    countDistinctIf(user_id, event_type = 'purchase')
    / countDistinctIf(user_id, event_type = 'add_to_cart') * 100, 2
  ) AS cart_to_purchase_cr
FROM user_events
WHERE toDate(event_time) BETWEEN '2026-03-01' AND '2026-03-15'
GROUP BY utm_source
HAVING visits > 100
ORDER BY total_cr DESC;