AnalyticDB:Funnel and retention functions

Fonctionnement

La fonction traite les événements de chaque utilisateur par ordre chronologique :

1. Elle parcourt les événements pour trouver la première occurrence de la condition initiale de votre séquence. Lorsqu'elle est trouvée, la fenêtre glissante démarre.

2. Elle recherche ensuite chaque condition suivante dans l'ordre, au sein de la fenêtre. Chaque correspondance incrémente le compteur d'une unité.

3. Si la séquence est interrompue ou si la fenêtre expire avant que toutes les conditions ne soient satisfaites, le compteur s'arrête. La fonction renvoie la valeur maximale atteinte par le compteur.

Exemples :

• Liste d'événements [c1, c2, c3], données utilisateur [c1, c2, c3, c4] → renvoie 3 (séquence complète correspondante)

• Liste d'événements [c1, c2, c3], données utilisateur [c4, c3, c2, c1] → renvoie 1 (seule la condition c1 correspond depuis le début)

• Liste d'événements [c1, c2, c3], données utilisateur [c4, c3] → renvoie 0 (la première condition n'a jamais été satisfaite)

Syntaxe

window_funnel(window, mode, timestamp, cond1, cond2, ..., condN)

Paramètres

Exemple

L'exemple suivant analyse le parcours de conversion — consultation → ajout aux favoris → ajout au panier → achat — entre le 25 novembre 2017 à 00:00:00 et le 26 novembre 2017 à 00:00:00. La fenêtre temporelle glissante est de 30 minutes (1 800 secondes) et les horodatages sont au format Unix (de 1511539200 à 1511625600).

La requête s'exécute en deux étapes : la requête interne calcule la profondeur du funnel par utilisateur, tandis que la requête externe compte le nombre d'utilisateurs ayant atteint chaque niveau de profondeur.

SELECT
  funnel,
  count(1)
FROM (
  SELECT
    uid,
    window_funnel(
      cast(1800 as integer),  -- 30-minute sliding window
      "default",
      ts,
      event = 'pv',           -- Step 1: Browse
      event = 'fav',          -- Step 2: Add to favorites
      event = 'cart',         -- Step 3: Add to cart
      event = 'buy'           -- Step 4: Purchase
    ) AS funnel
  FROM user_behavior
  WHERE ts > 1511539200
    AND ts < 1511625600
  GROUP BY uid
)
GROUP BY funnel;

Résultat exemple :

+--------+----------+
| funnel | count(1) |
+--------+----------+
|      0 |    19687 |
|      1 |   596104 |
|      2 |    78458 |
|      3 |    11640 |
|      4 |      746 |
+--------+----------+
5 rows in set (0.64 sec)

Une valeur de 4 signifie que l'utilisateur a effectué les quatre étapes. Une valeur de 1 indique que seule la première étape (consultation) a été satisfaite.

Syntaxe

retention(cond1, cond2, ..., cond32)

Paramètres

Exemple

L'exemple suivant mesure la rétention sur 7 jours à partir du 25 novembre 2017. sum(r[1]) compte les utilisateurs actifs le jour 1 ; sum(r[2]) à sum(r[7]) comptent combien d'entre eux sont revenus chaque jour suivant.

Étape 1 — calcul des tableaux de rétention par utilisateur :

SELECT
  uid,
  retention(
    ds = '2017-11-25' AND event = 'pv',  -- Day 1: active (baseline)
    ds = '2017-11-25',                   -- Day 1 retention check
    ds = '2017-11-26',                   -- Day 2
    ds = '2017-11-27',                   -- Day 3
    ds = '2017-11-28',                   -- Day 4
    ds = '2017-11-29',                   -- Day 5
    ds = '2017-11-30'                    -- Day 6
  ) AS r
FROM user_behavior_date
GROUP BY uid

Étape 2 — agrégation sur l'ensemble des utilisateurs :

SELECT
  sum(r[1]),   -- Active users on day 1
  sum(r[2]),   -- Retained on day 1
  sum(r[3]),   -- Retained on day 2
  sum(r[4]),   -- Retained on day 3
  sum(r[5]),   -- Retained on day 4
  sum(r[6]),   -- Retained on day 5
  sum(r[7])    -- Retained on day 6
FROM (
  SELECT
    retention(
      ds = '2017-11-25' AND event = 'pv',
      ds = '2017-11-25',
      ds = '2017-11-26',
      ds = '2017-11-27',
      ds = '2017-11-28',
      ds = '2017-11-29',
      ds = '2017-11-30'
    ) AS r
  FROM user_behavior_date
  GROUP BY uid
);

Résultat exemple :

+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| sum(r[1]) | sum(r[2]) | sum(r[3]) | sum(r[4]) | sum(r[5]) | sum(r[6]) | sum(r[7]) |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|    686953 |    686953 |    544367 |    529979 |    523516 |    524530 |    528105 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
1 row in set (2.96 sec)

Syntaxe

-- Per-user retention
retention_range_count(is_first, is_active, dt, intervals)
retention_range_count(is_first, is_active, dt, intervals, retentionGranularity, outputFormat)

-- Aggregate across users
retention_range_sum(retention_range_count_result)

Paramètres

retention_range_count :

Valeurs de outputFormat :

retention_range_sum :

Exemple

L'exemple suivant calcule la rétention à J+1 et J+2 pour les utilisateurs qui se sont connectés les 1er et 2 mai 2022, en se basant sur l'activité du 1er au 4 mai 2022. L'événement d'activation est login et l'événement de rétention est pay.

Étape 1. Créez une table de test et insérez des données.

CREATE TABLE event(uid string, event string, ds date);

INSERT INTO event VALUES
  ("user1", "pay",   "2022-05-01"),
  ("user1", "login", "2022-05-01"),
  ("user1", "pay",   "2022-05-02"),
  ("user1", "login", "2022-05-02"),
  ("user2", "login", "2022-05-01"),
  ("user3", "login", "2022-05-02"),
  ("user3", "pay",   "2022-05-03"),
  ("user3", "pay",   "2022-05-04");

Exemple de données :

+-------+-------+------------+
| uid   | event | ds         |
+-------+-------+------------+
| user1 | login | 2022-05-01 |
| user1 | pay   | 2022-05-01 |
| user1 | login | 2022-05-02 |
| user1 | pay   | 2022-05-02 |
| user2 | login | 2022-05-01 |
| user3 | login | 2022-05-02 |
| user3 | pay   | 2022-05-03 |
| user3 | pay   | 2022-05-04 |
+-------+-------+------------+

Étape 2. Calculez le statut de rétention par utilisateur.

SELECT
  uid,
  r
FROM (
  SELECT
    uid,
    retention_range_count(
      event = 'login',   -- Activation event
      event = 'pay',     -- Retention event
      ds,
      array(1, 2)        -- Track day-1 and day-2 retention
    ) AS r
  FROM event
  GROUP BY uid
) AS t
ORDER BY uid;

Exemple de résultat :

+-------+-----------------------------+
| uid   | r                           |
+-------+-----------------------------+
| user1 | [[738642,0,0],[738641,1,0]] |
| user2 | [[738641,0,0]]              |
| user3 | [[738642,1,1]]              |
+-------+-----------------------------+

Chaque tableau interne contient [date_de_début_en_jours, retention_j1, retention_j2]. Par exemple, user3 s'est connecté le 2 mai (numéro de jour 738642) et a effectué un paiement à la fois le 3 mai (jour 1) et le 4 mai (jour 2).

Étape 3. Agrégez les données pour obtenir les taux de rétention quotidiens pour tous les utilisateurs.

SELECT
  from_days(u[1]) AS ds,
  u[3] / u[2] AS retention_d1,
  u[4] / u[2] AS retention_d2
FROM (
  SELECT retention_range_sum(r) AS r
  FROM (
    SELECT
      uid,
      retention_range_count(
        event = 'login',
        event = 'pay',
        ds,
        array(1, 2)
      ) AS r
    FROM event
    GROUP BY uid
  ) AS t
  ORDER BY uid
) AS r,
unnest(r.r) AS t(u);

Exemple de résultat :

+------------+--------------+--------------+
| ds         | retention_d1 | retention_d2 |
+------------+--------------+--------------+
| 2022-05-02 |          0.5 |          0.5 |
| 2022-05-01 |          0.5 |          0.0 |
+------------+--------------+--------------+

Le 1er mai, deux utilisateurs se sont connectés (user1 et user2). Seul user1 a effectué un paiement le 2 mai (jour 1 après l'activation), donc la rétention à J+1 pour le 1er mai est de 0,5. Aucun utilisateur n'a effectué de paiement le 3 mai (jour 2 après l'activation), donc la rétention à J+2 pour le 1er mai est de 0,0. Le 2 mai, deux utilisateurs se sont connectés (user1 et user3). Tous deux ont effectué un paiement dans les deux jours suivants, donc les rétentions à J+1 et J+2 sont de 0,5 pour les utilisateurs activés le 2 mai.