📌 Один из способов запроса воронки

В работе часто приходится анализировать какие-нибудь воронки или последовательности с условиями. Например юзер сделал то-то, потом вот это, а потом то. Есть миллион разных способов как собрать такой лог, но мне в последнее время нравится вот такой, можно сказать, сниппет, с использованием множественных cte. Он простой, кастомизируемый и достаточно надёжный в плане точности.

Вот как он может выглядеть на примере расчёта проходимости через авторизацию в приложении через смс. Основан на фронтовых ивентах, но это вообще не важно.

🔵В этом примере есть экран, страница или любой другой индикатор что юзер попал на страницу логина,

🔵потом жмякнул по авторизации через смс

🔵и успешно залогинился, попав на главную.

Логика простая, собираем отдельно каждый шаг, а потом склеиваем поюзерно с соблюдением таймингов — второй шаг после первого, третий за вторым и т.д. Я ещё добавил фильтр что это всё в один день. Но тут можно что угодно навешивать. Вообще можно независимо кастомить условия на любом шаге.

В итоге будет таблица, где NULL в датах это потери, т.е. юзер дропнулся. Дальше можно уже как угодно это агрегировать и делать что там было нужно.

Забирайте, пользуйтесь 🙂

# Собираем данные по первому шагу
with login as (
  select user_id,
         timestamp as login_screen_dt
  from db
  where date(timestamp) >= current_date - 30
    and event = 'screen_view'
    and screen_name = 'login'
),

# По второму
sms_screen as (
  select user_id,
         timestamp as sms_screen_dt
  from db
  where date(timestamp) >= current_date - 30
    and event = 'screen_view'
    and screen_name = 'sms'
),

# По третьему
home_page as (
  select user_id,
         timestamp as home_page_dt
  from db
  where date(timestamp) >= current_date - 30
    and event = 'screen_view'
    and screen_name = 'home_page'
)

# Склеиваем это всё
select ls.*,
       ss.sms_screen_dt,
       hp.home_page_dt
from login ls
  left join sms_screen ss on ls.user_id = ss.user_id
                         and ls.login_screen_dt <= ss.sms_screen_dt
                         and date(ls.login_screen_dt) = date(ss.sms_screen_dt)
  left join home_page hp on ss.user_id = hp.user_id
                        and ss.sms_screen_dt <= hp.home_page_dt
                        and date(ss.sms_screen_dt) = date(hp.home_page_dt)

#сниппеты