Um estudo com SQL (BigQuery) e Power BI sobre uma rede fictícia de pet shops: os produtos que mais vendem são os que fazem o cliente voltar?
O problema
Numa reunião de resultados, o diretor comercial levantou a dúvida: "Sabemos quais são os produtos mais vendidos. Mas são eles que fazem os clientes voltarem a comprar?"
A rede escolhia promoções e campanhas pelo volume de vendas, sem saber se isso criava relacionamento de longo prazo com o cliente ou só receita pontual.
O que os dados mostraram
Volume, receita e retenção têm líderes diferentes.
A categoria do primeiro produto comprado é o principal preditor do valor do cliente ao longo do tempo. Quem estreia com Ração Premium Filhotes chega a um LTV médio de R$ 1.459, quase o dobro de quem estreia com Areia Sanitária.
Dashboard
De 5.153 clientes analisados, 64,5% voltaram para uma nova compra. Por isso a retenção, e não o volume, é a métrica central para medir o valor real de cada produto.
Como foi construído
Toda a análise foi feita em SQL no BigQuery, a partir da base de vendas da rede, em três passos: identificar a primeira compra de cada cliente, classificar quem voltou a comprar e cruzar produto, categoria e recompra para calcular retenção e LTV médio por segmento.
- Classificação de cliente recorrenteDefine o que é um cliente retido: quem tem mais de uma venda. Todas as métricas seguintes dependem dessa regra.
SELECT
status_cliente,
COUNT(*) AS qtd_clientes
FROM (
SELECT
id_cliente,
CASE
WHEN COUNT(DISTINCT id_venda) > 1 THEN 'Recorrente'
ELSE 'Não Recorrente'
END AS status_cliente
FROM varejopet.conjunto01.vendas
GROUP BY id_cliente
)
GROUP BY status_cliente;
- Retenção por produto da primeira compraÉ a resposta técnica à pergunta do diretor: junta a primeira compra de cada cliente ao seu status de recompra e calcula a taxa por produto. É daqui que sai o achado do Antipulgas Pipeta Gatos.
WITH cliente_resumo AS (
SELECT
id_cliente,
MIN(data_venda) AS data_primeira_compra,
CASE WHEN COUNT(DISTINCT id_venda) > 1 THEN 'Recorrente' ELSE 'Não Recorrente' END AS status_cliente
FROM varejopet.conjunto01.vendas
GROUP BY id_cliente
),
itens_primeira_compra AS (
SELECT
v.id_cliente,
v.produto,
v.categoria,
cr.status_cliente
FROM varejopet.conjunto01.vendas v
INNER JOIN cliente_resumo cr
ON v.id_cliente = cr.id_cliente
AND v.data_venda = cr.data_primeira_compra
)
SELECT
produto,
categoria,
COUNT(DISTINCT id_cliente) AS total_clientes_primeira_compra,
ROUND(SUM(CASE WHEN status_cliente = 'Recorrente' THEN 1 ELSE 0 END) / COUNT(DISTINCT id_cliente) * 100, 2) AS taxa_retencao_pct
FROM itens_primeira_compra
GROUP BY produto, categoria
ORDER BY total_clientes_primeira_compra DESC;
- Retenção por categoriaA mesma lógica, agregada por categoria. Reaproveita as CTEs da query anterior para responder a uma segunda pergunta de negócio.
WITH cliente_resumo AS (
SELECT
id_cliente,
MIN(data_venda) AS data_primeira_compra,
CASE WHEN COUNT(DISTINCT id_venda) > 1 THEN 'Recorrente' ELSE 'Não Recorrente' END AS status_cliente
FROM varejopet.conjunto01.vendas
GROUP BY id_cliente
),
itens_primeira_compra AS (
SELECT
v.id_cliente,
v.categoria,
cr.status_cliente
FROM varejopet.conjunto01.vendas v
INNER JOIN cliente_resumo cr
ON v.id_cliente = cr.id_cliente
AND v.data_venda = cr.data_primeira_compra
)
SELECT
categoria,
COUNT(DISTINCT id_cliente) AS total_clientes_primeira_compra,
ROUND(SUM(CASE WHEN status_cliente = 'Recorrente' THEN 1 ELSE 0 END) / COUNT(DISTINCT id_cliente) * 100, 2) AS taxa_retencao_pct
FROM itens_primeira_compra
GROUP BY categoria
ORDER BY taxa_retencao_pct DESC;
O que isso significa para o negócio
Volume, receita e retenção apontam vencedores diferentes, então cada promoção deve ser avaliada pelo resultado que pretende mover.
Como a primeira compra é o principal preditor do valor do cliente, as ofertas de aquisição podem priorizar as categorias que levam ao maior LTV. E a retenção deve entrar no dashboard padrão, para ser acompanhada todo mês.