CONTEXTO E PROBLEMA
Antes dos indicadores, uma base consistente.
Um pedido pode conter vários itens, pagamentos e vendedores. Combinar essas informações sem respeitar suas granularidades pode duplicar valores e produzir indicadores incorretos. Desenvolvi um modelo relacional e um processo de carga e validação para transformar arquivos separados em uma base rastreável para análise.
- Como relacionar as entidades sem multiplicar valores financeiros?
- Como comparar o valor dos itens, o frete e os pagamentos registrados?
- Como avaliar a contribuição dos vendedores preservando os limites de atribuição?
DADOS E PREPARAÇÃO
99.441 pedidos, diferentes unidades de análise.
Utilizei o Brazilian E-Commerce Public Dataset by Olist, com compras entre setembro de 2016 e outubro de 2018. A base CORE validada reúne 99.441 pedidos, 112.650 itens, 103.886 registros de pagamento e 3.095 vendedores, além de clientes, produtos, avaliações e informações geográficas.
O processo ELT preserva os arquivos de origem na camada RAW e aplica as transformações no PostgreSQL para construir a CORE. A validação confere volumes, chaves, integridade referencial, nulabilidade, domínios e valores monetários. As exceções de cobertura permanecem identificáveis: há, por exemplo, um pedido entregue com itens e sem pagamento.
METODOLOGIA
Dos requisitos ao consumo em BI.
- Modelagem conceitual, lógica e física. Identificação de entidades, cardinalidades e regras de negócio, seguida da implementação de tabelas, chaves e restrições no PostgreSQL.
- Carga e validação. Ingestão transacional, transformação de RAW para CORE e reconciliação entre origem e destino, com testes automatizados.
- Camada analítica. Pré-agregação de itens e pagamentos por pedido, definição de populações e métricas e criação de views reutilizáveis no schema analytics.
- Power BI. Construção do relatório e do modelo semântico versionados em PBIP, PBIR e TMDL, preservando os contratos de consumo e as granularidades.
DECISÕES DE MODELAGEM
As relações foram definidas a partir da fonte.
A modelagem exigiu distinguir identificadores, validar cardinalidades e reconhecer situações em que um campo aparentemente único não identifica sozinho um registro. Essas decisões orientam tanto a integridade do banco quanto a interpretação das consultas.
- Cliente e histórico de compras
- O modelo preserva o identificador do registro de cliente e o identificador único do consumidor. Essa distinção permite relacionar o cadastro ao pedido e investigar compras recorrentes sem tratar todos os registros como pessoas diferentes.
- Itens e pagamentos
- As chaves combinam o pedido com o identificador do item ou com a sequência do pagamento. Assim, um pedido pode manter seus vários itens e pagamentos como ocorrências distintas, com preços, fretes e valores próprios.
- Avaliações
- O identificador de avaliação isolado não é único na fonte. A chave composta por avaliação e pedido preserva essa característica, evitando impor uma unicidade que os dados não sustentam.
- Prefixos de CEP
- Uma entidade própria conecta os prefixos usados por clientes, vendedores e geolocalização. As coordenadas permanecem em uma relação separada, pois um prefixo pode estar associado a várias observações geográficas.
O modelo conceitual descreve o domínio; o lógico formaliza tabelas, chaves e relacionamentos; o físico implementa essas decisões no PostgreSQL. Essa separação mantém rastreável a passagem entre a regra de negócio e sua implementação.
Examinar chaves e relacionamentos no modelo lógico ↗
ELT E QUALIDADE
Três camadas, responsabilidades explícitas.
- RAW · preservar a origem. Recebe os dados dos CSVs. A carga substitui a anterior, evitando acumular cópias do mesmo dataset e mantendo uma referência para conferir as transformações.
- CORE · consolidar o modelo. Aplica transformações e restrições para materializar as entidades relacionais. A reconciliação de volumes e as verificações de integridade acompanham essa passagem.
- ANALYTICS · estabilizar o consumo. Disponibiliza estruturas reutilizáveis para indicadores e exportações. As regras de cálculo deixam de depender de uma consulta isolada ou de uma interpretação específica no relatório.
A qualidade é verificada em diferentes níveis: estrutura do banco, ingestão, transformação, orquestração e resultado da carga. Os testes automatizados também cobrem os artefatos do Power BI; o GitHub Actions executa verificações de dependências, lint, formatação e testes.
Exceções preservadas para investigação.
No histórico completo, 164 pedidos cancelados possuem pagamentos e não possuem itens. Excluir essas ocorrências silenciosamente reduziria a visibilidade sobre a fonte. O projeto mantém consultas de cobertura e declara quais relações cada indicador exige, separando a população financeira principal dos controles sobre o histórico completo.
Examinar validações do ELT ↗ · Examinar testes automatizados ↗
RESULTADOS E INTERPRETAÇÃO
Indicadores acompanhados de controles.
01 · A junção direta compromete as somas.
O controle entre itens e pagamentos produziu 117.601 linhas na junção direta. Itens se repetem conforme a quantidade de pagamentos, e pagamentos conforme a quantidade de itens. A solução agrega cada relação separadamente por pedido antes de combiná-las.
O resultado técnico é uma regra explícita de consumo: as somas financeiras precisam respeitar a granularidade do pedido.
Examinar consulta de controle ↗02 · Valor bruto e pagamentos não são equivalentes.
No recorte de 96.477 pedidos entregues com itens e pagamentos, os resultados históricos são:
- Valor bruto · itens + frete
- R$ 15.419.630,29
- Pagamento registrado
- R$ 15.422.461,77
- Diferença agregada
- R$ 2.831,48
- Frete / valor bruto
- 14,26%
A conferência individual identificou 96.178 pedidos conciliados dentro da tolerância de R$ 0,01, 260 com pagamento maior que o valor bruto e 39 com valor bruto maior que o pagamento. A maior diferença absoluta observada foi de R$ 182,81.
A diferença agregada não demonstra conciliação de todos os pedidos: valores positivos e negativos podem se compensar. Por isso, a reconciliação permanece disponível por pedido. O valor bruto também não representa lucro ou receita contábil.
03 · A participação varia entre os vendedores.
Entre os 2.970 vendedores ativos em pedidos entregues, os dez maiores representam 13,27% do valor dos itens. Já os 10% maiores concentram 67,11% desse valor no histórico analisado.
A distribuição permite priorizar a análise dos vendedores com maior contribuição financeira. Esses percentuais descrevem a base observada; não medem participação no mercado brasileiro nem atribuem responsabilidade causal por atrasos ou avaliações.
04 · Um pedido pode participar do resultado de mais de um vendedor.
Na base de pedidos entregues com itens, 1.275 pedidos possuem múltiplos vendedores. A atribuição reconciliou 110.197 itens e R$ 13.221.498,11 em valor dos itens, com diferença zero entre a base e os valores atribuídos.
Itens e seus valores podem ser atribuídos diretamente ao vendedor. Já a soma das participações vendedor-pedido não equivale à contagem de pedidos distintos do marketplace. Esse recorte inclui o pedido entregue sem pagamento e, por isso, difere da população financeira de 96.477 pedidos.
05 · Prazo de envio e entrega medem etapas diferentes.
Na granularidade vendedor-pedido, foram observadas 97.819 participações. Entre as observações com informação disponível, 8,97% apresentaram algum item enviado após o limite e 8,02% estavam associadas a pedidos entregues após a estimativa.
Os indicadores ajudam a selecionar situações para investigação operacional. Envio após o limite e atraso da entrega final não são medidas intercambiáveis: a entrega e a avaliação pertencem ao pedido, e a associação com um vendedor não comprova sua responsabilidade pelo resultado.
Fonte dos volumes, controles e resultados: documentação da camada analítica, seções 3, 4 e 6.5 ↗.
EXPLORAÇÃO INTERATIVA
Da visão geral ao detalhe do vendedor.
O relatório Power BI organiza os resultados em cinco páginas: Visão Geral, Financeiro e Reconciliação, Vendedores e Concentração, Operação e Experiência e Detalhe do Vendedor.
O consumo se apoia em quatro views: pedido financeiro, financeiro mensal, vendedor-pedido e desempenho do vendedor. Cada uma tem uma unidade de análise definida, permitindo combinar a visão executiva com a investigação dos registros sem relacionar diretamente fatos de granularidades incompatíveis.
- Visão Geral: síntese dos principais indicadores para iniciar a leitura do negócio.
- Financeiro e Reconciliação: comparação entre componentes do valor bruto e pagamentos registrados.
- Vendedores e Concentração: leitura da contribuição e da distribuição do valor entre vendedores.
- Operação e Experiência: exploração dos indicadores de prazo, entrega e avaliação.
- Detalhe do Vendedor: aprofundamento da análise em um vendedor específico.
O relatório e o modelo semântico são versionados junto ao código. Os formatos PBIP, PBIR e TMDL permitem examinar a estrutura do produto de BI no repositório, complementando a experiência interativa do dashboard.
LIMITAÇÕES E CONCLUSÃO
Consistência também exige delimitar a interpretação.
Os dados são históricos e não representam a operação atual da Olist. Meses com cobertura diária parcial exigem cuidado nas comparações; as consultas mensais calculam crescimento apenas quando o mês atual e o anterior possuem cobertura completa.
Pagamentos, avaliações e entrega pertencem ao pedido e não devem ser automaticamente atribuídos a cada vendedor. Atrasos associados a pedidos com participação de um vendedor não comprovam responsabilidade individual. O dataset também não fornece os custos necessários para calcular lucro.
O projeto conclui o percurso entre requisitos, modelagem, ELT e Power BI com uma base validada, métricas documentadas e controles verificáveis. Sua principal contribuição é permitir que os indicadores sejam examinados junto às regras que os produzem.
TECNOLOGIAS
Ferramentas aplicadas ao projeto.
- SQL
- PostgreSQL
- Python
- pandas
- Power BI
- DAX
- Git
- GitHub Actions