AnalyticDB：Fungsi funnel dan retensi

Set data uji

Pada topik ini, data perilaku Taobao dari Tianchi Lab digunakan untuk menguji fungsi funnel dan retensi. Untuk informasi selengkapnya, lihat Data Perilaku Pengguna dari Taobao untuk Rekomendasi.

Tabel berikut menjelaskan empat jenis perilaku pengguna yang tersedia dalam set data uji.

Sebelum pengujian, unggah set data uji ke Object Storage Service (OSS), lalu impor data dari OSS ke AnalyticDB for MySQL.

1. Unggah set data uji ke OSS. Untuk informasi selengkapnya, lihat Unggah objek.

2. Gunakan tabel eksternal OSS untuk mengimpor data ke AnalyticDB for MySQL. Contoh berikut menunjukkan operasi yang diperlukan:

   1. Buat tabel eksternal OSS.

      CREATE TABLE `user_behavior_oss` (
        `user_id` string,
        `item_id` string,
        `cate_id` string,
        `event` string,
         `ts` bigint) ENGINE = 'oss'
      TABLE_PROPERTIES = '{
      "endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
      "accessid":"******",
      "accesskey":"*******",
      "url":"oss://<bucket-name>/user_behavior/",
      "delimiter":","
      }'

      Untuk informasi selengkapnya, lihat Sintaks tabel eksternal OSS.

   2. Buat tabel uji AnalyticDB for MySQL.

      CREATE TABLE user_behavior(
        uid string,
        event string,
        ts string
      )

   3. Impor data dari tabel eksternal OSS ke tabel uji.

      SUBMIT JOB INSERT OVERWRITE user_behavior SELECT user_id , event, ts FROM user_behavior_oss;

window_funnel

Fungsi window_funnel digunakan untuk mencari daftar event dalam jendela waktu geser dan menghitung jumlah maksimum event berturut-turut. Fungsi ini memulai penghitungan dari event pertama dalam daftar yang ditentukan, memeriksa event secara berurutan, lalu mengembalikan jumlah maksimum event berturut-turut.

• Jika Anda menentukan daftar event yang berisi c1, c2, dan c3 serta data pengguna berisi c1, c2, c3, dan c4, fungsi akan mengembalikan nilai 3.

• Jika Anda menentukan daftar event yang berisi c1, c2, dan c3 serta data pengguna berisi c4, c3, c2, dan c1, fungsi akan mengembalikan nilai 1.

• Jika Anda menentukan daftar event yang berisi c1, c2, dan c3 serta data pengguna berisi c4 dan c3, fungsi akan mengembalikan nilai 0.

Sintaks

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

Parameter

select uid, window_funnel(cast(1800 as integer),"default", TIMESTAMPDIFF('second','2017-11-25 00:00:00.000',ts),event = 'pv',event = 'fav',event = 'cart',event = 'buy') as funnel from user_behavior where ts > '2017-11-25 00:00:00.000' and ts < '2017-11-26 00:00:000.000'

Contoh

Sebagai contoh, Anda ingin menganalisis status konversi dari 2017-11-25 00:00:00 hingga 2017-11-26 00:00:00 berdasarkan jalur konversi berikut: melihat komoditas > menambahkan komoditas ke favorit > menambahkan komoditas ke keranjang belanja > membeli komoditas. Pernyataan SQL terkait melibatkan kondisi-kondisi berikut:

• Ukuran jendela waktu geser adalah 30 menit, setara dengan 1.800 detik.

• Waktu kueri adalah dari 2017-11-25 00:00:00 hingga 2017-11-26 00:00:00, setara dengan 1511539200 hingga 1511625600 dalam format timestamp.

• Langkah-langkah event dilakukan dalam urutan berikut: melihat komoditas > menambahkan komoditas ke favorit > menambahkan komoditas ke keranjang belanja > membeli komoditas.

Jalankan pernyataan SQL berikut:

SELECT funnel, count(1) FROM (SELECT uid, window_funnel(cast(1800 as integer),"default", ts, event='pv',  event='fav', event='cart', event='buy') AS funnel FROM user_behavior WHERE ts > 1511539200 AND ts < 1511625600 group by uid) GROUP BY funnel;

Hasil contoh:

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

retention

Fungsi retention menggunakan serangkaian kondisi sebagai parameter untuk menganalisis apakah suatu event memenuhi kondisi tersebut.

Sintaks

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

Parameter

Contoh

Sebagai contoh, Anda ingin menganalisis status retensi pengguna per 25 November 2017. Pernyataan SQL terkait melibatkan kondisi-kondisi berikut:

• Tanggal mulai analisis adalah 2017-11-25.

• Jumlah pengguna aktif pada hari pertama direpresentasikan oleh sum(r[1]). Jumlah pengguna yang dipertahankan dari hari kedua hingga hari ketujuh direpresentasikan oleh sum(r[2]), sum(r[3])...sum(r[7]).

Jalankan pernyataan SQL berikut:

SELECT sum(r[1]),sum(r[2]),sum(r[3]),sum(r[4]),sum(r[5]),sum(r[6]),sum(r[7]) 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)

retention_range_count dan retention_range_sum

Fungsi analisis retensi (retention_range_count dan retention_range_sum) dapat digunakan untuk menghasilkan grafik visual dan menganalisis retensi pengguna dalam skenario analisis pertumbuhan pengguna. Fungsi retention_range_count mencatat status retensi setiap pengguna. Nilai kembali fungsi ini berupa larik dua dimensi dan dapat berfungsi sebagai parameter masukan untuk fungsi retention_range_sum. Fungsi retention_range_sum merangkum laju retensi harian semua pengguna.

Sintaks

• retention_range_count

  retention_range_count(is_first, is_active, dt, intervals, outputFormat)

• retention_range_sum

  retention_range_sum(retention_range_count_result)

Parameter

Contoh

Sebagai contoh, Anda ingin mengkueri status retensi pada 1 Mei 2022 dan 2 Mei 2022 berdasarkan data pengguna dari 1 Mei 2022 hingga 4 Mei 2022. Event aktivasi adalah login, dan event retensi adalah pay.

1. Buat tabel uji dan masukkan data ke dalam tabel.

   1. Buat tabel.

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

   2. Masukkan data ke dalam tabel.

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

2. Kueri status retensi setiap pengguna.

   SELECT
     uid,
     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;

   Hasil contoh:

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

3. Kueri laju retensi harian 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 |
   +------------+--------------+--------------+