Funnel analysis in AnalyticDB for MySQL - AnalyticDB: Central de Documentação do Alibaba Cloud

Pré-requisitos

Antes de começar, verifique se você tem:

Um cluster do AnalyticDB for MySQL executando a versão secundária 3.1.6.0 ou posterior

Nota

Para visualize e atualize a versão secundária, acesse a seção Configuration Information na página Cluster Information no console do AnalyticDB for MySQL.

Contexto

A análise de funil mede como os usuários avançam por uma sequência definida de etapas — por exemplo, da visualização de um produto até a conclusão de uma compra. Em cada etapa, alguns usuários abandonam o processo; a taxa de conversão indica quantos chegaram ao final. Essa técnica é amplamente utilizada em análises de tráfego e de conversão de metas de produtos.

O AnalyticDB for MySQL oferece suporte às seguintes funções:

Função	Finalidade
`window_funnel`	Conta até onde um usuário progrediu em uma sequência de eventos definida dentro de uma janela de tempo deslizante
`retention`	Verifica se cada usuário atende a um conjunto de condições baseadas em datas e retorna um array binário
`retention_range_count`	Registra o status de retenção por usuário em intervalos especificados e retorna um array bidimensional
`retention_range_sum`	Agrega a saída de `retention_range_count` de todos os usuários para calcular as taxas diárias de retenção

Conjunto de dados de teste

Os exemplos neste tópico usam o conjunto de dados User Behavior Data from Taobao for Recommendation do Tianchi Lab. Esse conjunto contém quatro tipos de comportamento:

Comportamento	Descrição
`pv`	Visualização da página do produto (contado como um clique)
`buy`	Compra
`cart`	Adição ao carrinho de compras
`fav`	Adição aos favoritos

Para carregar o conjunto de dados no AnalyticDB for MySQL, faça upload dele para o Object Storage Service (OSS) e importe-o usando uma tabela externa do OSS.

Faça upload do conjunto de dados para o OSS. Para mais informações, consulte Upload objects.

Crie uma tabela externa do 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":","
}'

Para mais informações, consulte OSS external table syntax.

Crie uma tabela de teste no AnalyticDB for MySQL.

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

Importe os dados da tabela externa do OSS.

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

window_funnel

A função window_funnel busca no histórico de eventos de um usuário uma sequência específica de eventos dentro de uma janela de tempo deslizante. Ela retorna o comprimento do maior prefixo correspondente dessa sequência.

Funcionamento

A função processa os eventos de cada usuário em ordem cronológica:

Varre os eventos para encontrar a primeira ocorrência da condição inicial na sequência. Ao encontrá-la, inicia a janela deslizante.
Em seguida, procura cada condição subsequente em ordem dentro da janela. Cada correspondência incrementa o contador em uma unidade.
Se a sequência for interrompida ou a janela expirar antes que todas as condições sejam atendidas, o contador para. A função retorna o maior valor alcançado pelo contador.

Exemplos:

Lista de eventos [c1, c2, c3], dados do usuário [c1, c2, c3, c4] → retorna 3 (sequência completa correspondida)
Lista de eventos [c1, c2, c3], dados do usuário [c4, c3, c2, c1] → retorna 1 (apenas c1 correspondido desde o início)
Lista de eventos [c1, c2, c3], dados do usuário [c4, c3] → retorna 0 (primeira condição nunca correspondida)

Sintaxe

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

Parâmetros

Parâmetro	Tipo	Descrição
`window`	Integer	Tamanho da janela de tempo deslizante, na mesma unidade da coluna `timestamp`
`mode`	String	Modo de operação. Defina como `"default"`
`timestamp`	BIGINT	Coluna de timestamp. Deve ser do tipo de dados BIGINT. Se sua coluna de timestamp não for BIGINT, use `TIMESTAMPDIFF` para convertê-la. Por exemplo: `TIMESTAMPDIFF('second', '2017-11-25 00:00:00.000', ts)`
`cond1, ..., condN`	Boolean	Condições de evento que definem as etapas do funil, avaliadas em ordem

Exemplo

O exemplo a seguir analisa o caminho de conversão — navegar → adicionar aos favoritos → adicionar ao carrinho → comprar — de 2017-11-25 00:00:00 a 2017-11-26 00:00:00. A janela de tempo deslizante é de 30 minutos (1.800 segundos), e os timestamps estão no formato Unix (1511539200 a 1511625600).

A consulta executa em dois estágios: a consulta interna calcula a profundidade do funil por usuário, e a consulta externa conta quantos usuários atingiram cada profundidade.

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;

Resultado de amostra:

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

Um valor de 4 significa que o usuário completou todas as quatro etapas. Um valor de 1 indica que apenas a primeira etapa (navegação) foi correspondida.

retention

A função retention verifica se um usuário atende a um conjunto de condições — geralmente uma condição por dia — e retorna um array UINT8 onde cada elemento é 1 (condição atendida) ou 0 (condição não atendida).

Sintaxe

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

Parâmetros

Parâmetro	Tipo	Descrição
`cond1, ..., cond32`	UINT8	Condições a serem avaliadas, até 32. Retorna `1` se a condição for atendida, `0` caso contrário

Exemplo

O exemplo a seguir mede a retenção de 7 dias começando em 25 de novembro de 2017. sum(r[1]) conta os usuários ativos no dia 1; de sum(r[2]) até sum(r[7]) conta quantos desses usuários retornaram em cada dia subsequente.

Estágio 1 — calcular arrays de retenção por usuário:

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

Estágio 2 — agregar entre todos os usuários:

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

Resultado de amostra:

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

As funções retention_range_count e retention_range_sum são projetadas para análise de crescimento de usuários. Elas suportam intervalos de retenção flexíveis e produzem saídas adequadas para visualização.

retention_range_count calcula o status de retenção por usuário em intervalos de dias especificados e retorna um array bidimensional.
retention_range_sum recebe a saída de retention_range_count e a agrega entre todos os usuários para produzir taxas diárias de retenção.

Sintaxe

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

-- Aggregate across users
retention_range_sum(retention_range_count_result)

Parâmetros

retention_range_count:

Parâmetro	Tipo	Descrição
`is_first`	Boolean	Indica se esta linha corresponde ao evento de ativação. `true` = este é o comportamento inicial
`is_active`	Boolean	Indica se esta linha corresponde ao evento de retenção. `true` = isso conta como um comportamento retido
`dt`	Date	Data do evento, no formato `date` (por exemplo, `2022-05-01`)
`intervals[]`	Array	Intervalos de retenção a serem rastreados (por exemplo, `array(1, 2)` para retenção no dia 1 e dia 2). Suporta até 15 intervalos
`outputFormat`	String	Formato do valor de retorno. Valores válidos: `normal` (padrão) ou `expand`

Valores de outputFormat:

Valor	Formato de saída
`normal` (padrão)	`[[d1(start date), 1, 0, ...], [d2(start date), 1, 0, ...], ...]` — cada linha é `[start date, interval_1_flag, interval_2_flag, ...]`, onde `1` = retido e `0` = não retido
`expand`	`[[d1(start date), d1+1(retention date)], [d1, d1+2], [d2, d2+1], [d2, d2+3]]`

retention_range_sum:

Parâmetro	Descrição
`retention_range_count_result`	A saída em array bidimensional de `retention_range_count`

Exemplo

O exemplo a seguir calcula a retenção no dia 1 e dia 2 para usuários que fizeram login entre 1 e 2 de maio de 2022, com base na atividade de 1 a 4 de maio de 2022. O evento de ativação é login e o evento de retenção é pay.

Etapa 1. Crie uma tabela de teste e insira dados.

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

Dados de amostra:

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

Etapa 2. Calcule o status de retenção por usuário.

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;

Resultado de amostra:

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

Cada array interno contém [start_date_as_days, day1_retained, day2_retained]. Por exemplo, user3 fez login em 2 de maio (número do dia 738642) e pagou tanto em 3 de maio (dia 1) quanto em 4 de maio (dia 2).

Etapa 3. Agrege para obter as taxas diárias de retenção entre todos os usuários.

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

Resultado de amostra:

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

Em 1º de maio, dois usuários fizeram login (user1 e user2). Apenas o user1 pagou em 2 de maio (dia 1 após a ativação), portanto a retenção no dia 1 para 1º de maio é 0,5. Nenhum usuário pagou em 3 de maio (dia 2 após a ativação), logo a retenção no dia 2 para 1º de maio é 0,0. Em 2 de maio, dois usuários fizeram login (user1 e user3). Ambos pagaram nos dois dias seguintes, então tanto a retenção no dia 1 quanto no dia 2 são 0,5 para os usuários ativados em 2 de maio.

AnalyticDB:Funnel and retention functions

Pré-requisitos

Contexto

Conjunto de dados de teste

window_funnel

Funcionamento

Sintaxe

Parâmetros

Exemplo

retention

Sintaxe

Parâmetros

Exemplo

retention_range_count e retention_range_sum

Sintaxe

Parâmetros

Exemplo

Próximos passos