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>, ...
)

Como attr_related funciona

O parâmetro attr_related usa uma máscara de bits binária para especifique quais eventos têm propriedades associadas.

Por exemplo, com quatro eventos (E1, E2, E3, E4):

Quando attr_related=3 (binário 11), os eventos E1 e E2 associam-se por propriedade. Forneça duas expressões <prop> — uma para cada evento. Como E3 e E4 não têm associação, nenhuma expressão <prop> é necessária para eles.

Janela de dia civil

Quando is_relative_window=true, a janela divide-se em dias civis (de 00:00:00 a 23:59:59 de cada dia).

Quando is_relative_window=true , aplicam-se as seguintes restrições:

<window> deve ser um múltiplo inteiro de 86.400.000 (milissegundos por dia).

<step_interval> deve ser 86400 (uma etapa = um dia).

O primeiro dia civil começa no horário do evento e termina às 23:59:59 do mesmo dia. Cada dia subsequente é um dia civil completo. Use este modo para visualize dados diários de funil em análises operacionais refinadas.

{<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');

Exemplo 1: Funil básico sem associação de propriedade

Janela de 3 dias dividida em 3 etapas diárias, rastreando 4 eventos, sem associação de propriedade (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;

Resultado:

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

• id=1111, {4,4,3,2}: O usuário 1111 alcançou o evento 4 (Exit) ao final. Na etapa 1 (2 de jan), atingiu o evento 4; na etapa 2 (3 de jan), o evento 3 (Payment); na etapa 3 (4 de jan), o evento 2 (Logon).

• id=2222, {3,3,0,0}: O usuário 2222 alcançou o evento 3 (Payment) na etapa 1, sem progressão adicional.

Exemplo 2: Funil com associação de propriedade

Defina attr_related=3 (binário 11) para associar os eventos 1 (Registration) e 2 (Logon) pela propriedade province. A conversão entre esses eventos é rastreada apenas quando ambos compartilham o mesmo valor de província.

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;

Resultado:

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

Os resultados mostram o evento mais alto alcançado por cada usuário — e o evento mais alto por etapa — após aplicar a restrição de associação de propriedade.

Exemplo 3: Agregar resultados com funnel_rep

Use funnel_rep para somar os resultados de finder_funnel por usuário em uma visualização no nível de grupo:

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;

Resultado:

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

Exemplo 4: Janela de dia civil

Este exemplo usa uma janela de 3 dias civis (is_relative_window=TRUE) para visualize dados diários de funil.

1. Crie a tabela e insira os dados:

   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. Execute a consulta com uma janela de dia civil:

   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;

   Resultado:

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

   Para id=1111: o primeiro dia civil vai de 2024-01-02 00:00:02 até 23:59:59. Os dias 3 e 4 de janeiro correspondem ao segundo e terceiro dias civis. O dia 5 de janeiro fica fora da janela de 3 dias, portanto o evento Exit não é contabilizado — o usuário 1111 atinge o evento 3 (Payment) como resultado final.