Аналитика воронки с использованием SQL в Snowflake

Я работаю над созданием запроса для отслеживания жизненного цикла пользователя через платформу через события. Стол EVENTS имеет 3 столбца, DATE_TIME а также EVENT_NAME. Ниже приведен снимок таблицы,

Ниже мой запрос,

      SELECT * FROM EVENTS
MATCH_RECOGNIZE
(   PARTITION BY USER_ID
    ORDER BY DATE_TIME
    MEASURES MIN(IFF(EVENT_NAME = 'registration new', DATE_TIME, NULL)) AS REGISTRATION_NEW_TIMESTAMP,
             MIN(IFF(EVENT_NAME = 'registration pending confirm', DATE_TIME, NULL)) AS REGISTRATION_PENDING_CONFIRM_TIMESTAMP,
             MIN(IFF(EVENT_NAME = 'your business information', DATE_TIME, NULL)) AS YOUR_BUSINESS_INFORMATION_TIMESTAMP,
             MIN(IFF(EVENT_NAME = 'your personal information', DATE_TIME, NULL)) AS YOUR_PERSONAL_INFORMATION_TIMESTAMP,
             MIN(IFF(EVENT_NAME = 'qualified', DATE_TIME, NULL)) AS QUALIFIED_TIMESTAMP
  ONE ROW PER MATCH
  PATTERN(STEP_1 ANYTHING* STEP_5)
  DEFINE
        STEP_1 AS EVENT_NAME = 'registration new',
        STEP_5 AS EVENT_NAME = 'qualified'
)

Мой ожидаемый результат,

Что я получаю прямо сейчас,

Ниже приведены мои требования / предостережения,

  • Отметка времени следующего события должна быть больше или равна отметке времени предыдущего события (чтобы отметки времени были равны или продолжали увеличиваться по воронке). Хороший пример такой логики можно объяснить разницей в текущем и ожидаемом результате, т.е. со значениями в REGISTRATION_PENDING_CONFIRM_TIMESTAMP а также QUALIFIED_TIMESTAMP столбец.
  • Не у всех пользователей есть все эти 5 событий, например, если USER_ID54321 не имеет / пропускает событие 'ваша личная информация', результат должен содержать данные для остальных шагов (прямо сейчас, если пользователь не имеет / пропускает какое-либо событие в воронке, запрос не возвращает данные ). Я чувствую, что это связано с тем, что поиск по шаблону не выполняется, когда событие, определенное как мера, отсутствует в потоке пользователя.

Порядок событий в таблице не согласован, поэтому я определил события по порядку в разделе мер в соответствии с бизнес-логикой / логикой воронки.

1 ответ

Это не полный ответ, но, по крайней мере, я помогаю здесь определить образцы данных (лучше, чем просто снимок экрана) и представить использование CLASSIFIER:

      create or replace temp table events as
select $1 user_id, $2 date_time, $3 event_name
from values(1,'2020-11-26 15:24:00','registration new')
, (1,'2021-04-12 18:00:00','registration new')
, (1,'2020-11-26 15:24:00','registration pending confirm')
, (1,'2021-04-12 18:11:00','registration pending confirm')
, (1,'2021-04-18 15:04:00','your personal information')
, (1,'2021-04-22 13:13:00','your personal information')
, (1,'2021-04-13 10:22:00','qualified')
, (1,'2021-04-22 13:13:00','qualified')
;


SELECT * FROM EVENTS
MATCH_RECOGNIZE
(   PARTITION BY USER_ID
    ORDER BY DATE_TIME
 
    MEASURES  classifier as class, MIN(IFF(CLASSIFIER = 'STEP_1', DATE_TIME, NULL)) AS REGISTRATION_NEW_TIMESTAMP,
             MIN(IFF(CLASSIFIER = 'STEP_2', DATE_TIME, NULL)) AS REGISTRATION_PENDING_CONFIRM_TIMESTAMP,
             MIN(IFF(CLASSIFIER = 'STEP_3', DATE_TIME, NULL)) AS YOUR_BUSINESS_INFORMATION_TIMESTAMP,
             MIN(IFF(CLASSIFIER = 'STEP_4', DATE_TIME, NULL)) AS YOUR_PERSONAL_INFORMATION_TIMESTAMP,
             MIN(IFF(CLASSIFIER = 'STEP_5', DATE_TIME, NULL)) AS QUALIFIED_TIMESTAMP
 
  ONE ROW PER MATCH
 -- all rows per match
  PATTERN((step_1 | step_2 | step_3 | step_4 | step_5 | coincidence)*)--(STEP_2 | XX)* (STEP_3 | XXX)* (STEP_4 | XX)* (STEP_5 | XX)*)
  DEFINE
        STEP_1 AS EVENT_NAME = 'registration new',
        STEP_2 AS LAG(DATE_TIME) < DATE_TIME AND EVENT_NAME = 'registration pending confirm' ,
        STEP_3 AS LAG(DATE_TIME) < DATE_TIME AND EVENT_NAME = 'your business information',
        STEP_4 AS LAG(DATE_TIME) < DATE_TIME AND EVENT_NAME = 'your personal information',
        STEP_5 AS EVENT_NAME = 'qualified'
        , COINCIDENCE AS LAG(DATE_TIME) = DATE_TIME
);
Другие вопросы по тегам