Engenharia de Features com SQL: Construindo uma Base Preditiva

🎯 Resultado Final: A Feature Store

A tabela abaixo representa o resultado final do processo de ETL. Cada linha é um cliente único e cada coluna é uma feature construída para modelos de Machine Learning. Para fins de demonstração, são exibidos apenas os 5 primeiros registros, mas a tabela completa contém o perfil de todos os clientes da base de dados. A explicação detalhada de como cada feature foi calculada se encontra nas seções seguintes.

idCliente	qtdeTransacoesVida	qtdeTransacoes56	qtdeTransacoes28	qtdeTransacoes14	qtdeTransacoes7	saldoPontos	DiasUltimaInteracao	qtdePontosPosVida	QtdePontosPos56	QtdePontosPos28	QtdePontosPos14	QtdePontosPos7	qtdePontosNegVida	QtdePontosNeg56	QtdePontosNeg28	QtdePontosNeg14	QtdePontosNeg7	IdadeBase	produtoVida	produto56	produto28	produto14	produto7	dtDia	PeriodoMaisTransacao28	engajamento28Vida
000ff655-fa9f-4baa-a108-47f581ec52a1	484	123	84	24	15	22286	1.01	24286	12300	8400	2400	1500	-2000	0	0	0	0	600.01	ChatMessage	Resgatar Ponei	Resgatar Ponei	Resgatar Ponei	Resgatar Ponei	5	Tarde	0.173553719
001749bd-37b5-4b1e-8111-f9fbba90f530	1	0	0	0	0	50	439.01	50	0	0	0	0	0	0	0	0	0	600.01	Lista de presença	Lista de presença	Lista de presença	Lista de presença	Lista de presença	-1	Sem Informação	0.0
0019bb9e-26d4-4ebf-8727-fc911ea28a92	2	0	0	0	0	2	474.01	2	0	0	0	0	0	0	0	0	0	600.01	ChatMessage	ChatMessage	ChatMessage	ChatMessage	ChatMessage	-1	Sem Informação	0.0
0033b737-8235-4c0f-9801-dc4ca185af00	548	1	0	0	0	2561	50.01	2561	1	0	0	0	0	0	0	0	0	216.01	ChatMessage	ChatMessage	ChatMessage	ChatMessage	ChatMessage	-1	Sem Informação	0.0
0097ab76-4637-4ece-8ebc-ab6abd61d662	5	0	0	0	0	152	593.01	152	0	0	0	0	0	0	0	0	0	600.01	Lista de presença	ChatMessage	ChatMessage	ChatMessage	ChatMessage	-1	Sem Informação	0.0

---

📖 Sobre o Projeto

Este projeto consiste em um robusto processo de ETL (Extração, Transformação e Carga), desenvolvido inteiramente em SQL, com o objetivo de construir uma Feature Store. A tabela final consolida o perfil comportamental de cada usuário a partir de dados transacionais brutos, servindo como uma base otimizada e fundamental para futuros projetos de Machine Learning.

Cada linha da tabela final representa um cliente único, e as colunas (features) são métricas calculadas que descrevem o engajamento, os hábitos e o histórico do usuário em diferentes janelas de tempo.

O principal valor deste projeto está na transformação de dados brutos em insights acionáveis, demonstrando habilidades avançadas em SQL, engenharia de dados e a lógica por trás da criação de features que potencializam modelos preditivos.

---

✨ Features Construídas

A query foi projetada para calcular 10 features principais que compõem o perfil de cada usuário:

1. Quantidade de transações históricas (vida, D7, D14, D28, D56).
2. Dias desde a última transação.
3. Idade do cliente na base.
4. Produto mais utilizado (vida, D7, D14, D28, D56).
5. Saldo de pontos atual.
6. Pontos acumulados positivos (vida, D7, D14, D28, D56).
7. Pontos acumulados negativos (vida, D7, D14, D28, D56).
8. Dia da semana mais ativo (últimos 28 dias).
9. Período do dia mais ativo (últimos 28 dias).
10. Engajamento em D28 versus Vida (proporção de atividade recente).

---

🛠️ Análise Detalhada do Código

A estrutura da query utiliza Common Table Expressions (CTEs) com o comando WITH para segmentar o problema em etapas lógicas, melhorando a legibilidade e a manutenção do código.

Bloco 1: Preparação da Tabela de Transações (tb_transacoes)

WITH tb_transacoes AS (

    SELECT IdTransacao,
           idCliente,
           QtdePontos,
           datetime(substr(DtCriacao, 1, 19)) AS DtCriacao,
           julianday('now') - julianday(substr(DtCriacao, 1, 10)) AS diffDate,
           CAST(strftime('%H', substr(DtCriacao, 1, 19)) AS INTEGER) AS dtHora

    FROM transacoes
),

Explicação

Esta CTE inicial é a base para quase todos os cálculos. Ela lê a tabela transacoes e cria colunas auxiliares cruciais:

• DtCriacao: Padroniza a coluna de data para o formato datetime.
• diffDate: Calcula a diferença em dias entre a data atual ('now') e a data de cada transação. É a coluna chave para filtrar as janelas de tempo (D7, D14, D28, D56).
• dtHora: Extrai apenas a hora de cada transação, essencial para determinar o período do dia (manhã, tarde, noite).

Bloco 2: Preparação da Tabela de Clientes (tb_cliente)

tb_cliente AS (

    SELECT idCliente,
           datetime(substr(DtCriacao, 1, 19)) AS DtCriacao,
           julianday('now') - julianday(substr(DtCriacao, 1, 10)) AS IdadeBase
    
    FROM clientes
),

Explicação

De forma similar à CTE anterior, esta prepara os dados da tabela clientes e calcula a IdadeBase, que representa há quantos dias o cliente se cadastrou na plataforma.

Bloco 3: Sumário das Transações por Cliente (tb_sumario_transacoes)

tb_sumario_transacoes AS (

    SELECT idCliente,

           count(DISTINCT IdTransacao) AS qtdeTransacoesVida,
           count(CASE WHEN diffDate <= 56 THEN IdTransacao END) AS qtdeTransacoes56,
           count(CASE WHEN diffDate <= 28 THEN IdTransacao END) AS qtdeTransacoes28,
           count(CASE WHEN diffDate <= 14 THEN IdTransacao END) AS qtdeTransacoes14,
           count(CASE WHEN diffDate <= 7 THEN IdTransacao END) AS qtdeTransacoes7,

           sum(qtdePontos) AS saldoPontos,

           ROUND(min(diffDate), 2) AS DiasUltimaInteracao,

           sum(CASE WHEN qtdePontos > 0 THEN qtdePontos ELSE 0 END) AS qtdePontosPosVida,
           sum(CASE WHEN qtdePontos > 0 AND diffDate <= 56 THEN qtdePontos ELSE 0 END) AS QtdePontosPos56,
           sum(CASE WHEN qtdePontos > 0 AND diffDate <= 28 THEN qtdePontos ELSE 0 END) AS QtdePontosPos28,
           sum(CASE WHEN qtdePontos > 0 AND diffDate <= 14 THEN qtdePontos ELSE 0 END) AS QtdePontosPos14,
           sum(CASE WHEN qtdePontos > 0 AND diffDate <=  7 THEN qtdePontos ELSE 0 END) AS QtdePontosPos7,

           sum(CASE WHEN qtdePontos < 0 THEN qtdePontos ELSE 0 END) AS qtdePontosNegVida,
           sum(CASE WHEN qtdePontos < 0 AND diffDate <= 56 THEN qtdePontos ELSE 0 END) AS QtdePontosNeg56,
           sum(CASE WHEN qtdePontos < 0 AND diffDate <= 28 THEN qtdePontos ELSE 0 END) AS QtdePontosNeg28,
           sum(CASE WHEN qtdePontos < 0 AND diffDate <= 14 THEN qtdePontos ELSE 0 END) AS QtdePontosNeg14,
           sum(CASE WHEN qtdePontos < 0 AND diffDate <=  7 THEN qtdePontos ELSE 0 END) AS QtdePontosNeg7

    FROM tb_transacoes
    GROUP BY idCliente
),

Explicação

Esta é a CTE principal de agregação, onde a maioria das features é calculada. Ela agrupa os dados por idCliente para gerar um resumo único para cada usuário:

• Quantidade de Transações: Utiliza COUNT(CASE WHEN ...) para contar transações dentro de cada janela de tempo.
• Saldo de Pontos: Um SUM() simples na qtdePontos calcula o saldo total.
• Dias Última Interação: Usa MIN(diffDate) para encontrar a transação mais recente (a menor diferença de dias).
• Pontos Positivos e Negativos: Utiliza SUM(CASE WHEN ...) para somar separadamente os pontos ganhos e os pontos gastos.

Bloco 4: Lógica do Produto Mais Usado

Para encontrar o produto mais usado, o problema foi dividido em três CTEs:

-- CTE 1: Junção de Transações e Produtos
tb_transacao_produto AS (
    SELECT t1.*,
           t2.IdProduto,
           t3.DescNomeProduto,
           t3.DescCategoriaProduto
    FROM tb_transacoes AS t1
    LEFT JOIN transacao_produto AS t2 ON t1.IdTransacao = t2.IdTransacao
    LEFT JOIN produtos AS t3 ON t2.IdProduto = t3.IdProduto
),
-- CTE 2: Contagem de Produtos por Cliente e Janela de Tempo
tb_cliente_produto AS (
SELECT idCliente,
DescNomeProduto,
count(*) AS QtdeVida,
count( CASE WHEN diffDate <= 56 THEN IdTransacao END) AS qtde56,
count( CASE WHEN diffDate <= 28 THEN IdTransacao END) AS qtde28,
count( CASE WHEN diffDate <= 14 THEN IdTransacao END) AS qtde14,
count( CASE WHEN diffDate <= 7 THEN IdTransacao END) AS  qtde7
FROM tb_transacao_produto
GROUP BY idCliente, DescNomeProduto
),
-- CTE 3: Ranqueamento dos Produtos
tb_cliente_produto_rn AS (
SELECT *,
row_number() OVER (PARTITION BY idCliente ORDER BY QtdeVida DESC) AS rnVida,
row_number() OVER (PARTITION BY idCliente ORDER BY Qtde56 DESC) AS rn56,
row_number() OVER (PARTITION BY idCliente ORDER BY Qtde28 DESC) AS rn28,
row_number() OVER (PARTITION BY idCliente ORDER BY Qtde14 DESC) AS rn14,
row_number() OVER (PARTITION BY idCliente ORDER BY Qtde7 DESC) AS rn7
FROM tb_cliente_produto
),

Explicação

• tb_transacao_produto: Enriquece a tabela de transações com o nome do produto correspondente.
• tb_cliente_produto: Agrupa por cliente e produto para contar a frequência de uso em cada janela de tempo.
• tb_cliente_produto_rn: Utiliza a função de janela ROW_NUMBER() para criar um ranking. O produto com ranking 1 (rnVida = 1, rn56 = 1, etc.) é o mais usado em cada período.

Bloco 5: Lógica do Dia e Período Mais Ativo

A mesma técnica de ranqueamento foi aplicada para encontrar o dia da semana e o período do dia mais ativos.

-- Dia da semana mais ativo
tb_cliente_dia AS (
    SELECT IdCliente,
           strftime('%w', DtCriacao) AS dtDia,
           count(*) AS QtdeTransacao
    FROM tb_transacoes
    WHERE diffDate <= 28
    GROUP BY idCliente, dtDia
),
tb_cliente_dia_rn AS (
    SELECT *,
           row_number() OVER (PARTITION BY idCliente ORDER BY QtdeTransacao DESC) AS rnDia
    FROM tb_cliente_dia
),
-- Período do dia mais ativo
tb_cliente_periodo AS (
SELECT
idCliente,
CASE
WHEN dtHora BETWEEN 7 AND 12 THEN 'Manhã'
WHEN dtHora BETWEEN 13 AND 18 THEN 'Tarde'
WHEN dtHora BETWEEN 19 AND 23 THEN 'Noite'
ELSE 'Madrugada'
END AS periodo,
count(*) AS QtdeTransacao
FROM tb_transacoes
WHERE diffDate <= 28
GROUP BY 1, 2
),
tb_cliente_periodo_rn AS (
SELECT *,
row_number() OVER ( PARTITION BY idCliente ORDER BY QtdeTransacao DESC) AS rnPeriodo
FROM tb_cliente_periodo
),

Explicação

• tb_cliente_dia_rn: Filtra os últimos 28 dias, conta as transações por dia da semana e usa ROW_NUMBER() para encontrar o dia com mais atividade (rnDia = 1).
• tb_cliente_periodo_rn: Filtra os últimos 28 dias, classifica as transações em "Manhã", "Tarde", "Noite" ou "Madrugada", e usa ROW_NUMBER() para encontrar o período mais ativo (rnPeriodo = 1).

Bloco 6: Junção Final (tb_join)

tb_join AS (

    SELECT t1.*,
           ROUND(t2.IdadeBase, 2) AS IdadeBase,
           t3.DescNomeProduto AS produtoVida,
           t4.DescNomeProduto AS produto56,
           t5.DescNomeProduto AS produto28,
           t6.DescNomeProduto AS produto14,
           t7.DescNomeProduto AS produto7,
           COALESCE(t8.dtDia, -1) AS dtDia,
           COALESCE(t9.periodo, 'Sem Informação') AS PeriodoMaisTransacao28

    FROM tb_sumario_transacoes AS t1
    LEFT JOIN tb_cliente AS t2 ON t1.idCliente = t2.idCliente
    LEFT JOIN tb_cliente_produto_rn AS t3 ON t1.idCliente = t3.idCliente AND t3.rnVida = 1
    LEFT JOIN tb_cliente_produto_rn AS t4 ON t1.idCliente = t4.idCliente AND t4.rn56 = 1
    LEFT JOIN tb_cliente_produto_rn AS t5 ON t1.idCliente = t5.idCliente AND t5.rn28 = 1
    LEFT JOIN tb_cliente_produto_rn AS t6 ON t1.idCliente = t6.idCliente AND t6.rn14 = 1
    LEFT JOIN tb_cliente_produto_rn AS t7 ON t1.idCliente = t7.idCliente AND t7.rn7 = 1
    LEFT JOIN tb_cliente_dia_rn AS t8 ON t1.idCliente = t8.idCliente AND rnDia = 1
    LEFT JOIN tb_cliente_periodo_rn AS t9 ON t1.idCliente = t9.idCliente AND rnPeriodo = 1
)

Explicação

Esta CTE é a etapa de montagem final. Ela une todos os resultados parciais:

• Inicia com tb_sumario_transacoes como a base.
• Usa LEFT JOIN para adicionar a IdadeBase.
• O passo mais importante é a junção com as tabelas de ranking. A condição AND rn... = 1 garante que apenas a linha correspondente ao item mais frequente (produto, dia ou período) seja unida, mantendo a granularidade de um cliente por linha.
• COALESCE é usado para tratar valores nulos, atribuindo um valor padrão caso não haja dados.

Bloco 7: Consulta Final e Criação da Tabela

CREATE TABLE tb_projeto AS 

SELECT *,
       1.0 * qtdeTransacoes28 / qtdeTransacoesVida AS engajamento28Vida

FROM tb_join

Explicação

Finalmente, a consulta externa seleciona todos os dados da tb_join e calcula a última feature:

• engajamento28Vida: Uma métrica que calcula a proporção de transações recentes (D28) em relação ao total, indicando o nível de engajamento atual do usuário.

O CREATE TABLE tb_projeto AS armazena o resultado completo desta query em uma nova tabela permanente, finalizando o processo ETL.

---

🚀 Tecnologias Utilizadas

• Linguagem: SQL (SQLite Dialect)
• Conceitos: ETL, Engenharia de Features, Funções de Janela (Window Functions), CTEs, Agregação Condicional.