Hologres:finder_funnel

CREATE extension flow_analysis; -- Install the extension.

finder_funnel(
  <window>,
  <start_timestamp>,
  <step_interval>,
  <step_numbers>,
  <num_events>,
  <attr_related>,
  <time_zone>,
  <is_relative_window>,
  <server_timestamp>,
  <client_timestamp>,
  [<prop1>, <prop2>, ...]
  <check_event1>, <check_event2>, ...
)

Fonctionnement de attr_related

attr_related utilise un masque de bits binaire pour spécifier quels événements ont des propriétés associées.

Par exemple, avec quatre événements (E1, E2, E3, E4) :

Lorsque attr_related=3 (binaire 11), les événements E1 et E2 sont associés par propriété. Fournissez deux expressions <prop> — une pour chaque événement. E3 et E4 ne sont pas associés, donc aucune expression <prop> n’est nécessaire pour eux.

Fenêtre de jour calendaire

Lorsque is_relative_window=true, la fenêtre est divisée en jours calendaires (de 00:00:00 à 23:59:59 chaque jour).

Lorsque is_relative_window=true , les contraintes suivantes s’appliquent :

<window> doit être un multiple entier de 86,400,000 (millisecondes par jour).

<step_interval> doit être 86400 (une étape = un jour).

Le premier jour calendaire commence à l’heure de l’événement et se termine à 23:59:59 du même jour. Chaque jour suivant est un jour calendaire complet. Utilisez ce mode pour observer les données d’entonnoir quotidiennes afin d’affiner l’analyse opérationnelle.

{<highest event reached>, <event reached in step 1>, <event reached in step 2>, ...}

CREATE TABLE finder_funnel_test_1(id INT, event_time TIMESTAMP, event TEXT, province TEXT, city TEXT);

INSERT INTO finder_funnel_test_1 VALUES
(1111, '2024-01-02 00:00:00', 'Registration', 'Beijing', 'Beijing'),
(1111, '2024-01-02 00:00:01', 'Logon',        'Beijing', 'Beijing'),
(1111, '2024-01-02 00:00:02', 'Payment',      'Beijing', 'Beijing'),
(1111, '2024-01-02 00:00:03', 'Exit',         'Beijing', 'Beijing'),

(1111, '2024-01-03 00:00:00', 'Registration', 'Beijing', 'Beijing'),
(1111, '2024-01-03 00:00:01', 'Logon',        'Beijing', 'Beijing'),
(1111, '2024-01-03 00:00:02', 'Payment',      'Beijing', 'Beijing'),

(1111, '2024-01-04 00:00:00', 'Registration', 'Beijing', 'Beijing'),
(1111, '2024-01-04 00:00:01', 'Logon',        'Beijing', 'Beijing'),

(2222, '2024-01-02 00:00:00', 'Registration', 'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:00', 'Logon',        'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:01', 'Payment',      'Zhejiang', 'Hangzhou'),
(2222, '2024-01-02 00:00:03', 'Payment',      'Zhejiang', 'Hangzhou');

Exemple 1 : Entonnoir de base sans association de propriété

Une fenêtre de 3 jours divisée en 3 étapes quotidiennes, suivant 4 événements, sans association de propriété (attr_related=0) :

SELECT
    id,
    finder_funnel(
        86400000 * 3,                                           -- 3-day window
        EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT,
        86400,                                                  -- 1-day steps
        3,                                                      -- 3 steps
        4,                                                      -- 4 events
        0,                                                      -- no property association
        'Asia/Shanghai',
        FALSE,
        event_time,
        event_time,
        event = 'Registration',
        event = 'Logon',
        event = 'Payment',
        event = 'Exit'
    ) AS funnel_res
FROM
    finder_funnel_test_1
GROUP BY
    id;

Résultat :

  id  | funnel_res
------+------------
 2222 | {3,3,0,0}
 1111 | {4,4,3,2}
(2 rows)

• id=1111, {4,4,3,2} : L’utilisateur 1111 a finalement atteint l’événement 4 (Exit). À l’étape 1 (2 janvier), il a atteint l’événement 4 ; à l’étape 2 (3 janvier), l’événement 3 (Payment) ; à l’étape 3 (4 janvier), l’événement 2 (Logon).

• id=2222, {3,3,0,0} : L’utilisateur 2222 a atteint l’événement 3 (Payment) à l’étape 1, sans progression ultérieure.

Exemple 2 : Entonnoir avec association de propriété

Définissez attr_related=3 (binaire 11) afin que les événements 1 (Registration) et 2 (Logon) soient associés par la propriété province. La conversion entre ces événements est suivie uniquement lorsque les deux événements partagent la même valeur de province.

SELECT
    id,
    finder_funnel(
        86400000 * 3,
        EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT,
        86400,
        3,
        4,
        3,               -- attr_related=3 (binary 11): events 1 and 2 have associated properties
        'Asia/Shanghai',
        FALSE,
        event_time,
        event_time,
        province,        -- prop for event 1 (Registration)
        province,        -- prop for event 2 (Logon)
        event = 'Registration',
        event = 'Logon',
        event = 'Payment',
        event = 'Exit'
    ) AS funnel_res
FROM
    finder_funnel_test_1
GROUP BY
    id;

Résultat :

  id  | funnel_res
------+------------
 2222 | {3,3,0,0}
 1111 | {4,4,3,2}
(2 rows)

Les résultats affichent l’événement le plus élevé atteint par chaque utilisateur — ainsi que l’événement le plus élevé par étape — après application de la contrainte d’association de propriété.

Exemple 3 : Agrégation des résultats avec funnel_rep

Utilisez funnel_rep pour additionner les résultats finder_funnel par utilisateur en une vue au niveau du groupe :

SELECT
    funnel_rep(3, 4, funnel_res)
FROM (
    SELECT
        id,
        finder_funnel(
            86400000 * 3,
            EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT,
            86400,
            3,
            4,
            3,
            'Asia/Shanghai',
            FALSE,
            event_time,
            event_time,
            province,
            province,
            event = 'Registration',
            event = 'Logon',
            event = 'Payment',
            event = 'Exit'
        ) AS funnel_res
    FROM
        finder_funnel_test_1
    GROUP BY
        id
) a;

Résultat :

                funnel_rep
-------------------------------------------
 {"2,2,2,1","2,2,2,1","1,1,1,0","1,1,0,0"}
(1 row)

Exemple 4 : Fenêtre de jour calendaire

Cet exemple utilise une fenêtre de 3 jours calendaires (is_relative_window=TRUE) pour observer les données d’entonnoir quotidiennes.

1. Créez la table et insérez les données :

   CREATE TABLE finder_funnel_test_3(id INT, event_time TIMESTAMP, event TEXT, province TEXT, city TEXT);
   
   INSERT INTO finder_funnel_test_3 VALUES
   (1111, '2024-01-02 00:00:02', 'Registration', 'Beijing',  'Beijing'),
   (1111, '2024-01-02 00:00:03', 'Logon',        'Beijing',  'Beijing'),
   
   (1111, '2024-01-03 00:00:04', 'Payment',      'Beijing',  'Beijing'),
   
   (1111, '2024-01-05 00:00:01', 'Exit',         'Beijing',  'Beijing'),
   
   (2222, '2024-01-02 00:00:00', 'Registration', 'Zhejiang', 'Hangzhou'),
   (2222, '2024-01-02 00:00:00', 'Logon',        'Zhejiang', 'Hangzhou'),
   (2222, '2024-01-02 00:00:01', 'Payment',      'Zhejiang', 'Hangzhou'),
   (2222, '2024-01-02 00:00:03', 'Payment',      'Zhejiang', 'Hangzhou');

2. Exécutez la requête avec une fenêtre de jour calendaire :

   SELECT
       id,
       finder_funnel(
           86400000 * 3,
           EXTRACT(epoch FROM TIMESTAMP'2024-01-02 00:00:00')::BIGINT,
           86400,
           3,
           4,
           0,
           'Asia/Shanghai',
           TRUE,           -- calendar day mode
           event_time,
           event_time,
           event = 'Registration',
           event = 'Logon',
           event = 'Payment',
           event = 'Exit'
       ) AS funnel_res
   FROM
       finder_funnel_test_3
   GROUP BY
       id;

   Résultat :

    id   | funnel_res
   ------+------------
    1111 | {3,3,0,0}
    2222 | {3,3,0,0}
   (2 rows)

   Pour id=1111 : le premier jour calendaire s’étend de 2024-01-02 00:00:02 à 23:59:59. Le 3 janvier et le 4 janvier constituent les deuxième et troisième jours calendaires. Le 5 janvier tombe en dehors de la fenêtre de 3 jours, donc l’événement Exit n’est pas comptabilisé — l’utilisateur 1111 atteint l’événement 3 (Payment) comme résultat final.