AnalyticDB:Fungsi funnel dan retensi

Cara kerja

Fungsi ini memproses event setiap pengguna secara kronologis:

1. Fungsi memindai event untuk menemukan kemunculan pertama dari kondisi awal dalam urutan Anda. Saat ditemukan, jendela geser dimulai.

2. Selanjutnya, fungsi mencari setiap kondisi berikutnya secara berurutan dalam jendela tersebut. Setiap kecocokan meningkatkan penghitung sebesar satu.

3. Jika urutan terputus atau jendela berakhir sebelum semua kondisi cocok, penghitung berhenti. Fungsi mengembalikan nilai penghitung tertinggi yang dicapai.

Contoh:

• Daftar event [c1, c2, c3], data pengguna [c1, c2, c3, c4] → mengembalikan 3 (urutan lengkap cocok)

• Daftar event [c1, c2, c3], data pengguna [c4, c3, c2, c1] → mengembalikan 1 (hanya c1 yang cocok dari awal)

• Daftar event [c1, c2, c3], data pengguna [c4, c3] → mengembalikan 0 (kondisi pertama tidak pernah cocok)

Sintaks

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

Parameter

Contoh

Contoh berikut menganalisis jalur konversi — telusuri → tambah ke favorit → tambah ke keranjang → beli — dari 2017-11-25 00:00:00 hingga 2017-11-26 00:00:00. Jendela waktu geser adalah 30 menit (1.800 detik), dan timestamp dalam format Unix (1511539200 hingga 1511625600).

Kueri dijalankan dalam dua tahap: kueri dalam menghitung kedalaman funnel per pengguna, dan kueri luar menghitung jumlah pengguna yang mencapai setiap kedalaman.

SELECT
  funnel,
  count(1)
FROM (
  SELECT
    uid,
    window_funnel(
      cast(1800 as integer),  -- Jendela geser 30 menit
      "default",
      ts,
      event = 'pv',           -- Langkah 1: Telusuri
      event = 'fav',          -- Langkah 2: Tambah ke favorit
      event = 'cart',         -- Langkah 3: Tambah ke keranjang
      event = 'buy'           -- Langkah 4: Beli
    ) AS funnel
  FROM user_behavior
  WHERE ts > 1511539200
    AND ts < 1511625600
  GROUP BY uid
)
GROUP BY funnel;

Hasil contoh:

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

Nilai 4 berarti pengguna menyelesaikan keempat langkah. Nilai 1 berarti hanya langkah pertama (telusuri) yang cocok.

Sintaks

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

Parameter

Contoh

Contoh berikut mengukur retensi 7 hari mulai 25 November 2017. sum(r[1]) menghitung pengguna yang aktif pada hari ke-1; sum(r[2]) hingga sum(r[7]) menghitung jumlah pengguna yang kembali pada setiap hari berikutnya.

Tahap 1 — hitung array retensi per pengguna:

SELECT
  uid,
  retention(
    ds = '2017-11-25' AND event = 'pv',  -- Hari 1: aktif (garis dasar)
    ds = '2017-11-25',                   -- Pemeriksaan retensi hari 1
    ds = '2017-11-26',                   -- Hari 2
    ds = '2017-11-27',                   -- Hari 3
    ds = '2017-11-28',                   -- Hari 4
    ds = '2017-11-29',                   -- Hari 5
    ds = '2017-11-30'                    -- Hari 6
  ) AS r
FROM user_behavior_date
GROUP BY uid

Tahap 2 — agregasi lintas semua pengguna:

SELECT
  sum(r[1]),   -- Pengguna aktif pada hari 1
  sum(r[2]),   -- Dipertahankan pada hari 1
  sum(r[3]),   -- Dipertahankan pada hari 2
  sum(r[4]),   -- Dipertahankan pada hari 3
  sum(r[5]),   -- Dipertahankan pada hari 4
  sum(r[6]),   -- Dipertahankan pada hari 5
  sum(r[7])    -- Dipertahankan pada hari 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
);

Hasil contoh:

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

Sintaks

-- Retensi per pengguna
retention_range_count(is_first, is_active, dt, intervals, outputFormat)

-- Agregasi lintas pengguna
retention_range_sum(retention_range_count_result)

Parameter

retention_range_count:

Nilai outputFormat:

retention_range_sum:

Contoh

Contoh berikut menghitung retensi hari ke-1 dan hari ke-2 untuk pengguna yang login pada 1–2 Mei 2022, berdasarkan aktivitas dari 1–4 Mei 2022. Event aktivasi adalah login dan event retensi adalah pay.

Langkah 1. Buat tabel uji dan masukkan 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");

Data contoh:

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

Langkah 2. Hitung status retensi per pengguna.

SELECT
  uid,
  r
FROM (
  SELECT
    uid,
    retention_range_count(
      event = 'login',   -- Event aktivasi
      event = 'pay',     -- Event retensi
      ds,
      array(1, 2)        -- Lacak retensi hari ke-1 dan hari ke-2
    ) AS r
  FROM event
  GROUP BY uid
) AS t
ORDER BY uid;

Hasil contoh:

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

Setiap array berisi [tanggal_mulai_sebagai_hari, retensi_hari1, retensi_hari2]. Misalnya, user3 login pada 2 Mei (hari ke-738642) dan melakukan pembayaran pada 3 Mei (hari ke-1) serta 4 Mei (hari ke-2).

Langkah 3. Agregasi untuk mendapatkan laju retensi harian lintas semua pengguna.

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

Hasil contoh:

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

Pada 1 Mei, dua pengguna login (user1 dan user2). Hanya user1 yang membayar pada 2 Mei (hari ke-1 setelah aktivasi), sehingga retensi hari ke-1 untuk 1 Mei adalah 0,5. Tidak ada pengguna yang membayar pada 3 Mei (hari ke-2 setelah aktivasi), sehingga retensi hari ke-2 untuk 1 Mei adalah 0,0. Pada 2 Mei, dua pengguna login (user1 dan user3). Keduanya membayar dalam dua hari berikutnya, sehingga retensi hari ke-1 dan hari ke-2 keduanya 0,5 untuk pengguna yang diaktifkan pada 2 Mei.