AnalyticDB:Funnel and retention functions

How it works

The function processes each user's events in chronological order:

1. It scans events to find the first occurrence of the initial condition in your sequence. When found, the sliding window starts.

2. It then looks for each subsequent condition in order within the window. Each match advances the counter by one.

3. If the sequence is interrupted or the window expires before all conditions match, the counter stops. The function returns the highest counter value reached.

Examples:

• Event list [c1, c2, c3], user data [c1, c2, c3, c4] → returns 3 (full sequence matched)

• Event list [c1, c2, c3], user data [c4, c3, c2, c1] → returns 1 (only c1 matched from start)

• Event list [c1, c2, c3], user data [c4, c3] → returns 0 (first condition never matched)

Syntax

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

Parameters

Example

The following example analyzes the conversion path — browse → add to favorites → add to cart → purchase — from 2017-11-25 00:00:00 to 2017-11-26 00:00:00. The sliding time window is 30 minutes (1,800 seconds), and timestamps are in Unix format (1511539200 to 1511625600).

The query runs in two stages: the inner query computes a funnel depth per user, and the outer query counts how many users reached each depth.

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;

Sample result:

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

A value of 4 means the user completed all four steps. A value of 1 means only the first step (browse) was matched.

Syntax

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

Parameters

Example

The following example measures 7-day retention starting November 25, 2017. sum(r[1]) counts users who were active on day 1; sum(r[2]) through sum(r[7]) count how many of those returned on each subsequent day.

Stage 1 — compute per-user retention arrays:

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

Stage 2 — aggregate across all users:

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

Sample result:

+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 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)

Syntax

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

-- Aggregate across users
retention_range_sum(retention_range_count_result)

Parameters

retention_range_count:

outputFormat values:

retention_range_sum:

Example

The following example computes day-1 and day-2 retention for users who logged in on May 1–2, 2022, based on activity from May 1–4, 2022. The activation event is login and the retention event is pay.

Step 1. Create a test table and insert data.

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");

Sample data:

+-------+-------+------------+
| 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 |
+-------+-------+------------+

Step 2. Compute per-user retention status.

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;

Sample result:

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

Each inner array contains [start_date_as_days, day1_retained, day2_retained]. For example, user3 logged in on May 2 (day number 738642) and made a payment on both May 3 (day 1) and May 4 (day 2).

Step 3. Aggregate to get daily retention rates across all users.

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

Sample result:

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

On May 1, two users logged in (user1 and user2). Only user1 paid on May 2 (day 1 after activation), so day-1 retention for May 1 is 0.5. Neither user paid on May 3 (day 2 after activation), so day-2 retention for May 1 is 0.0. On May 2, two users logged in (user1 and user3). Both paid within the next two days, so both day-1 and day-2 retention are 0.5 for users who activated on May 2.