WINDOW_FUNNEL (Lakehouse v1)

Semblable à windowFunnel dans ClickHouse (les deux ont été créées par le même auteur), cette fonction recherche des chaînes d’événements dans une fenêtre temporelle glissante et calcule le nombre maximal d’événements de la chaîne.

La fonction suit l’algorithme suivant :

La fonction recherche les données qui déclenchent la première condition de la chaîne et fixe le compteur d’événements à 1. C’est à ce moment que la fenêtre glissante démarre.
Si les événements de la chaîne se produisent séquentiellement dans la fenêtre, le compteur est incrémenté. Si la séquence d’événements est interrompue, le compteur n’est pas incrémenté.
Si les données comportent plusieurs chaînes d’événements à des stades d’achèvement différents, la fonction ne renvoie que la taille de la chaîne la plus longue.

Syntaxe SQL

WINDOW_FUNNEL( <window> )( <timestamp>, <cond1>, <cond2>, ..., <condN> )

Arguments

<timestamp> : nom de la colonne contenant l’horodatage. Types de données pris en charge : types entiers et types date-heure.
<cond> : conditions ou données décrivant la chaîne d’événements. Doit être de type de données Boolean.

Paramètres

<window> : durée de la fenêtre glissante, c’est-à-dire l’intervalle de temps entre la première et la dernière condition. L’unité de window dépend de timestamp lui-même et varie. Elle est déterminée à l’aide de l’expression timestamp of cond1 <= timestamp of cond2 <= ... <= timestamp of condN <= timestamp of cond1 + window.

Valeur renvoyée

Le nombre maximal de conditions consécutives déclenchées de la chaîne dans la fenêtre temporelle glissante. Toutes les chaînes de la sélection sont analysées.

Type : UInt8.

Exemple

Déterminez si une période donnée suffit à l’utilisateur pour SÉLECTIONNER un téléphone et l’acheter deux fois dans la boutique en ligne.

Définissez la chaîne d’événements suivante :

L’utilisateur s’est connecté à son compte dans la boutique (event_name = 'login').
L’utilisateur arrive sur la page (event_name = 'visit').
L’utilisateur ajoute un article au panier (event_name = 'cart').
L’utilisateur finalise l’achat (event_name = 'purchase').

CREATE TABLE events(user_id BIGINT, event_name VARCHAR, event_timestamp TIMESTAMP);

INSERT INTO events VALUES(100123, 'login', '2022-05-14 10:01:00');
INSERT INTO events VALUES(100123, 'visit', '2022-05-14 10:02:00');
INSERT INTO events VALUES(100123, 'cart', '2022-05-14 10:04:00');
INSERT INTO events VALUES(100123, 'purchase', '2022-05-14 10:10:00');

INSERT INTO events VALUES(100125, 'login', '2022-05-15 11:00:00');
INSERT INTO events VALUES(100125, 'visit', '2022-05-15 11:01:00');
INSERT INTO events VALUES(100125, 'cart', '2022-05-15 11:02:00');

INSERT INTO events VALUES(100126, 'login', '2022-05-15 12:00:00');
INSERT INTO events VALUES(100126, 'visit', '2022-05-15 12:01:00');

Table d’entrée :

┌─────────┬────────────┬────────────────────────────┐
│ user_id │ event_name │ event_timestamp            │
├─────────┼────────────┼────────────────────────────┤
│  100123 │ login      │ 2022-05-14 10:01:00.000000 │
│  100123 │ visit      │ 2022-05-14 10:02:00.000000 │
│  100123 │ cart       │ 2022-05-14 10:04:00.000000 │
│  100123 │ purchase   │ 2022-05-14 10:10:00.000000 │
│  100125 │ login      │ 2022-05-15 11:00:00.000000 │
│  100125 │ visit      │ 2022-05-15 11:01:00.000000 │
│  100125 │ cart       │ 2022-05-15 11:02:00.000000 │
│  100126 │ login      │ 2022-05-15 12:00:00.000000 │
│  100126 │ visit      │ 2022-05-15 12:01:00.000000 │
└─────────┴────────────┴────────────────────────────┘

Déterminez jusqu’où l’utilisateur user_id peut aller dans la chaîne sur une fenêtre glissante d’une heure.

Requête :

SELECT
    level,
    count() AS count
FROM
(
    SELECT
        user_id,
        window_funnel(3600000000)(event_timestamp, event_name = 'login', event_name = 'visit', event_name = 'cart', event_name = 'purchase') AS level
    FROM events
    GROUP BY user_id
)
GROUP BY level ORDER BY level ASC;

Résultat :

┌───────┬───────┐
│ level │ count │
├───────┼───────┤
│     2 │     1 │
│     3 │     1 │
│     4 │     1 │
└───────┴───────┘

L’utilisateur 100126 a le niveau 2 (login -> visit).
L’utilisateur 100125 a le niveau 3 (login -> visit -> cart).
L’utilisateur 100123 a le niveau 4 (login -> visit -> cart -> purchase).