readme

# Inteligência de Operações e Logística · E-commerce Olist

Este projeto apresenta uma investigação analítica profunda sobre o desempenho comercial, a eficiência logística e os fatores determinantes da satisfação do cliente na plataforma Olist. O objetivo central é identificar anomalias operacionais que impactam a experiência do consumidor e mapear a estrutura de faturamento do negócio utilizando modelagem de dados transacionais em **PostgreSQL**.

---

## 🛠️ 1. Governança e Auditoria de Dados (Data Quality)

Antes do início das análises estratégicas, foi realizada uma auditoria completa de integridade na base de dados para mapear registros inconsistentes, nulos ou que pudessem causar viés nos indicadores de desempenho (KPIs).

### Mapeamento de Lacunas e Inconsistências

* **Avaliações de Clientes (`order_reviews`)**: Identificou-se que 89.386 avaliações possuem valores nulos nas colunas de comentários textuais (`review_comment_title` e/ou `review_comment_message`). O comportamento foi classificado dentro da normalidade operacional, dado que o preenchimento dessas mensagens é opcional para o consumidor.
* **Ciclo de Vida dos Pedidos (`orders`)**: Foram localizados 2.980 pedidos com dados ausentes em colunas de controle temporal absoluto (como data de entrega ou data de postagem). Mapeou-se ainda 161 compras com a data de aprovação de pagamento nula. Além disso, identificou-se 1.183 transações onde o produto foi despachado para a transportadora, mas o registro de recebimento pelo cliente permaneceu vazio.
* **Inconsistências Graves de Status**: Identificou-se 141 pedidos com status de cancelados que possuíam dados logísticos preenchidos e 5 criados decorrentes de erros de sistema. Adicionalmente, detectou-se 14 transações registradas como entregues que apresentavam dados de rastreamento gravemente corrompidos pelo sistema.
* **Dimensões e Cadastro de Produtos (`products`)**: Identificou-se 611 produtos com dados nulos simultâneos em categorias e dimensões físicas (peso e tamanho).

### Regra de Ouro de Negócio (Tratamento de Dados)

Para mitigar distorções graves causadas por transações incompletas ou canceladas (que inflariam o faturamento e comprometeriam as médias de prazos reais), estabeleceu-se a diretriz técnica de considerar **estritamente pedidos com status consumado de entrega (`order_status = 'delivered'`)**.

Essa limpeza filtrou o banco de dados original e consolidou um ecossistema de **96.478 registros válidos** (97% do volume histórico total). Esta base purificada foi centralizada em uma visualização unificada (`vw_pedidos`) que abasteceu todas as etapas seguintes, garantindo que as métricas estivessem sempre livres de duplicidades ou lixo transacional. Os produtos com dimensões físicas nulas foram mantidos para análises de vendas e faturamento comercial, mas eliminados de futuros estudos focados na volumetria de frete.

---

## 📈 2. Desempenho Comercial e Infraestrutura Financeira

### Cenário Histórico Consolidado (Linha de Base)

A extração de dados tratados na View permitiu estabelecer o panorama real do modelo de negócio da Olist no período compreendido entre **04/09/2016 e 17/10/2018**:
* **Volume Histórico de Clientes Únicos (Base Filtrada)**: 94.086
* **Volume Histórico de Vendedores Ativos**: 3.095
* **Faturamento Bruto Acumulado**: R$ 13.221.498,11
* **Nota Média Geral de Satisfação**: 4.25

**Diagnóstico Factual de Recorrência**: Ao cruzar a base de clientes com o volume de pedidos entregues, identificou-se que a plataforma possui uma média crítica de apenas **1.025 pedidos por cliente único**. Esse dado revela uma falha grave de retenção no modelo de negócios da empresa: a plataforma atrai novos consumidores, mas falha em fazê-los retornar para uma segunda compra.

### Evolução Temporal e Tração de Mercado

O faturamento anual demonstrou uma expansão acelerada, comprovando a rápida escala da plataforma na linha do tempo:
* **Ano de 2016**: R$ 40.470,98 (Fase de início das operações)
* **Ano de 2017**: R$ 5.962.902,01 (Fase de forte tração de mercado)
* **Ano de 2018**: R$ 7.218.125,12 (Pico de consolidação histórica)

### Análise de Dependência de Meios de Pagamento

Para entender a segurança da captura de receita, analisou-se o faturamento e a volumetria total transacionada por método financeiro:
* **Cartão de Crédito**: Canaliza **74,03% de todo o faturamento da plataforma** (74.586 vendas), consolidando-se como o motor financeiro absoluto do negócio.
* **Boleto Bancário**: Representa **19,05%** do volume transacionado (19.191 vendas).
* **Voucher (Vale-Compra)**: Corresponde a **5,45%** (5.493 vendas).
* **Cartão de Débito**: Possui representatividade marginal de **1.47%** (1.486 vendas).

**Diagnóstico Financeiro**: O fluxo de checkout é altamente centralizado no uso de cartões de crédito. Uma falha de integração técnica ou uma alteração nas taxas dessas operadoras comprometeria instantaneamente quase 3/4 da receita da empresa. Como a etapa de pagamentos é amplamente dominada por um canal eficiente, conclui-se que o problema de falta de retenção de clientes não está no momento da compra, mas sim na experiência do pós-venda.

---

## 🗺️ 3. O Funil de Insatisfação Regional e o Paradoxo Logístico

### Distribuição Geográfica de Eficiência Operacional
Ao quebrar os volumes de venda, notas médias e prazos de entrega pelo estado de destino do cliente, isolou-se onde estavam concentradas as piores experiências de compra (considerando estados com mais de 500 pedidos para relevância estatística):

1. **SP**: 40.501 pedidos | Nota: **4.13** | Postagem: 3.1 dias | Viagem: 5.6 dias | Total: 8.70 dias de espera.
2. **RJ**: 12.350 pedidos | Nota: **3.97** | Postagem: 3.3 dias | Viagem: 11.9 dias | Total: 15.24 dias de espera.
3. **MG**: 11.354 pedidos | Nota: **4.09** | Postagem: 3.2 dias | Viagem: 8.7 dias | Total: 11.94 dias de espera.
4. **RS**: 5.345 pedidos | Nota: **4.05** | Postagem: 3.2 dias | Viagem: 12.0 dias | Total: 15.25 dias de espera.
5. **PR**: 4.923 pedidos | Nota: **4.10** | Postagem: 3.2 dias | Viagem: 8.7 dias | Total: 11.94 dias de espera.
6. **SC**: 3.546 pedidos | Nota: **4.00** | Postagem: 3.3 dias | Viagem: 11.6 dias | Total: 14.90 dias de espera.
7. **BA**: 3.256 pedidos | Nota: **3.81** | Postagem: 3.3 dias | Viagem: 16.0 dias | Total: 19.28 dias de espera.
8. **DF**: 2.080 pedidos | Nota: **4.01** | Postagem: 3.2 dias | Viagem: 9.7 dias | Total: 12.90 dias de espera.
9. **ES**: 1.995 pedidos | Nota: **3.99** | Postagem: 3.4 dias | Viagem: 12.4 dias | Total: 15.72 dias de espera.
10. **GO**: 1.957 pedidos | Nota: **3.99** | Postagem: 3.2 dias | Viagem: 12.4 dias | Total: 15.54 dias de espera.
11. **PE**: 1.593 pedidos | Nota: **3.96** | Postagem: 3.3 dias | Viagem: 15.1 dias | Total: 18.40 dias de espera.
12. **CE**: 1.279 pedidos | Nota: **3.81** | Postagem: 3.3 dias | Viagem: 17.9 dias | Total: 21.20 dias de entrega.
13. **MA**: 717 pedidos | Nota: **3.71** | Postagem: 3.6 dias | Viagem: 17.9 dias | Total: 21.51 dias de entrega.
14. **MS**: 701 pedidos | Nota: **4.05** | Postagem: 3.1 dias | Viagem: 12.4 dias | Total: 15.54 dias de entrega.
15. **PB**: 517 pedidos | Nota: **4.00** | Postagem: 3.5 dias | Viagem: 16.9 dias | Total: 20.39 dias de entrega.

### O Paradoxo Logístico e o Contexto Regional Fluminense

O cruzamento desses dados geográficos quebrou uma premissa óbvia de mercado. A análise provou que **o consumidor não penaliza a empresa pelo tempo longo de espera absoluto**, mas sim pela **quebra da margem de segurança estimada e fricções locais de entrega**.

* **A Evidência**: Clientes do Rio Grande do Sul (RS) e do Rio de Janeiro (RJ) enfrentam praticamente o mesmo tempo físico de espera (**15.25 dias no RS vs 15.24 dias no RJ**). No entanto, a nota do RS permanece saudável em **4.05**, enquanto a nota do RJ desaba para **3.97**.
* **A Explicação Matemática**: O sistema calcula uma margem de segurança de dias estimados para ambas as regiões. No entanto, o fluxo de transporte rodoviário interestadual para o Rio de Janeiro apresenta severa instabilidade de previsibilidade. No RS, apenas **6.1%** dos pedidos ultrapassam o limite de segurança. Já o Rio de Janeiro **dobra a taxa de falha logística oficial, registrando 12.1% de atrasos reais**, onde a entrega estoura a data máxima prometida.
* **O Fator de Risco Local (RJ)**: Para além da quebra matemática do prazo, o cenário logístico do estado do Rio de Janeiro é historicamente impactado por fatores exógenos de segurança pública, como restrições de tráfego em áreas de risco e altos índices de roubo de carga. Essas ocorrências frequentemente forçam as transportadoras e os Correios a reterem as mercadorias em Centros de Entrega Encomendas (CEEs), obrigando o cliente final a se deslocar fisicamente para retirar o produto em uma agência — uma fricção crítica de pós-venda que penaliza diretamente a nota de satisfação, independentemente do esforço de postagem do vendedor.


### O Peso Crítico do Prazo Estourado na Nota

Para mensurar a gravidade desse estouro de prazo, os pedidos foram divididos em faixas de atraso. Mapeou-se que o volume absoluto de atrasos na Olist é baixo (~8% do total do site). Todavia, o impacto desse grupo minoritário é devastador para a reputação da marca:
* **Pedidos entregues No Prazo**: Mantêm uma nota média alta de **4.31**.
* **Pedidos com Atraso Acima de 7 dias**: A nota média despenca para **1.54** e a taxa de detratores críticos (clientes que avaliam a experiência com notas 1 ou 2) explode, atingindo **78.3%** das avaliações.

---

## 📦 4. Mapeamento de Risco por Categoria de Produto

Para verificar se a insatisfação geral estava concentrada em problemas crônicos de tipos específicos de mercadorias (como produtos complexos ou pesados), o portfólio comercial da Olist foi segmentado. Para garantir a consistência estatística, foram consideradas apenas as categorias com volume igual ou superior a 100 pedidos na história da plataforma.

### O Indicador de Criticidade Comercial

Criou-se uma regra de análise no banco de dados para cruzar os dois eixos de maior impacto para o negócio: o **Rank de Faturamento** (importância financeira) e a **Nota Média de Satisfação** (experiência do cliente). O objetivo foi isolar produtos com o status de **Alto Faturamento e Baixa Avaliação (Status Crítico)**, que trazem muito dinheiro para o caixa, mas danificam a reputação da marca.

O cruzamento de dados isolou as 5 categorias de maior urgência operacional para a empresa, ordenadas por representatividade financeira (*Marketshare*):

1. **Cama, Mesa e Banho (`bed_bath_table`)**
   * **Volume e Rank**: 9.272 pedidos (1º lugar em volume do site)
   * **Faturamento e Share**: R\$ 1.023.434,76 (3º lugar geral · 7,93% de marketshare)
   * **Métricas**: Tempo de espera médio de 12.8 dias e 7,0% de atrasos reais.
   * **Avaliação**: Nota média de **3.92** ➔ **STATUS: CRÍTICO**.

2. **Acessórios de Informática (`computers_accessories`)**
   * **Volume e Rank**: 6.530 pedidos (4º lugar em volume do site)
   * **Faturamento e Share**: R\$ 888.724,61 (5º lugar geral · 6,88% de marketshare)
   * **Métricas**: Tempo de espera médio de 13.2 dias e 6,5% de atrasos reais.
   * **Avaliação**: Nota média de **3.99** ➔ **STATUS: CRÍTICO**.

3. **Móveis de Decoração (`furniture_decor`)**
   * **Volume e Rank**: 6.307 pedidos (5º lugar em volume do site)
   * **Faturamento e Share**: R\$ 711.927,69 (6º lugar geral · 5,51% de marketshare)
   * **Métricas**: Tempo de espera médio de 12.8 dias e 7,0% de atrasos reais.
   * **Avaliação**: Nota média de **3.95** ➔ **STATUS: CRÍTICO**.

4. **Telefonia (`telephony`)**
   * **Volume e Rank**: 4.093 pedidos (8º lugar em volume do site)
   * **Faturamento e Share**: R\$ 309.860,23 (14º lugar geral · 2,40% de marketshare)
   * **Métricas**: Tempo de espera médio de 12.8 dias e 7,0% de atrasos reais.
   * **Avaliação**: Nota média de **3.99** ➔ **STATUS: CRÍTICO**.

5. **Móveis de Escritório (`office_furniture`)**
   * **Volume e Rank**: 1.254 pedidos (19º lugar em volume do site)
   * **Faturamento e Share**: R\$ 268.154,31 (15º lugar geral · 2,08% de marketshare)
   * **Métricas**: Tempo de espera médio de **20.8 dias** e 8,0% de atrasos reais.
   * **Avaliação**: Nota média de **3.51** (A pior nota absoluta de todo o e-commerce) ➔ **STATUS: CRÍTICO**.

**Diagnóstico Estrutural de Portfólio**: Os dados provam que a insatisfação do cliente está diretamente concentrada nos produtos de maior faturamento da empresa. É fundamental notar que os tempos de espera médios listados acima (como os 12.8 dias de Cama, Mesa e Banho) são **médias nacionais agregadas**. Como veremos adiante, esses números mascaram realidades muito mais severas de rotas interestaduais específicas, onde o tempo de transporte rodoviário isolado de um único trajeto (como SP para o RJ) consome quase a totalidade do prazo médio do país inteiro.


---

## ⚖️ 5. Isolamento da Causa Raiz: De quem é a Culpa?

Para dar o diagnóstico definitivo sobre quem está gerando as notas baixas e os atrasos, a investigação realizou dois testes estatísticos controlados, separando os dados por taxas e fatias proporcionais.

### A Pirâmide de Poder dos Vendedores (Princípio de Pareto)
No modelo de negócios descentralizado da Olist, o estoque fica na mão de lojistas parceiros. Aplicando uma rampa de ranking percentual baseada no faturamento real acumulado de cada loja, descobriu-se o nível de vulnerabilidade operacional da marca:
* **O Top 1% (Apenas 30 vendedores)**: Sozinhos, acumulam **R$ 3,42 milhões** em vendas.
* **O Top 10% (267 vendedores)**: Acumulam **R$ 5,44 milhões**.

**Constatação de Pareto**: Somando os dois grupos do topo, **apenas 10% dos vendedores (297 lojas) controlam R$ 8,87 milhões do faturamento, o que representa 67,2% de toda a receita real da Olist**. O sucesso financeiro e a reputação da marca dependem diretamente da eficiência operacional desse pequeno grupo de elite.

### Teste da Hipótese 1: Big Players de SP × Categorias Problemáticas
Para testar se os grandes fornecedores do topo de São Paulo sabotavam as notas devido à ineficiência interna de expedição (Cenário A) ou se o problema era um gargalo geral da categoria do produto (Cenário B), dividiu-se o mercado de forma comparativa nas categorias problemáticas.

Os dados reais revelaram que os dois cenários coexistem de forma isolada dependendo do produto:

* **Móveis de Escritório (`office_furniture`) · Confirmação do Cenário A (Culpa do Big Player)**:
  * **Big Players SP**: 1.140 pedidos | Nota: **3.48** | Dias para Postar: **11.4** | Notas Ruins (1 e 2): **26.0%**
  * **Resto do Mercado**: 114 pedidos | Nota: **3.84** | Dias para Postar: **5.1** | Notas Ruins (1 e 2): **19.7%**
  * *Veredito Factual*: Enquanto o resto do mercado despacha os móveis em 5.1 dias, os Big Players de SP levam **mais de 11 dias úteis** travando o produto na expedição interna, gerando uma taxa abusiva de 26% de notas vermelhas. A ineficiência é estritamente de processo operacional interno das grandes contas de SP.

* **Cama, Mesa e Banho (`bed_bath_table`) · Confirmação do Cenário B (Gargalo Geral do Produto)**:
  * **Big Players SP**: 7.507 pedidos | Nota: **3.93** | Dias para Postar: **3.0** | Notas Ruins (1 e 2): **18.0%**
  * **Resto do Mercado**: 1.819 pedidos | Nota: **3.91** | Postagem: **3.7** | Notas Ruins (1 e 2): **18.6%**
  * *Veredito Factual*: As notas e as taxas de erro estão perfeitamente empatadas. Os grandes de SP são eficientes e postam rápido (3 dias). O problema dessa categoria afeta a plataforma por igual e está atrelado ao transporte na estrada, e não ao tamanho da loja.

* **Acessórios de Informática (`computers_accessories`) e Decoração (`furniture_decor`) · Sinal de Alerta**:
  * Em informática, os grandes de SP registram nota menor (**3.89 vs 4.03**) e maior taxa de notas ruins (**19.5% vs 15.8%**) do que os lojistas menores, evidenciando problemas de qualidade ou quebra de expectativas. Em decoração, a tendência se repete (**3.92 vs 4.00**).

### Teste da Hipótese 2: A Origem Proporcional das Notas Ruins no RJ
Para testar se as notas baixas do Rio de Janeiro eram causadas por problemas interestaduais de transporte ou por falhas na malha de entrega interna (last-mile) do RJ, isolou-se a taxa de notas ruins que o cliente carioca deu para cada estado de origem do vendedor:

* **Origem SP para RJ**: 8.188 pedidos | Nota: **3.79** | Notas Ruins (1 e 2): **22.8%** | Dias de Viagem: **12.5 dias**
* **Origem PR para RJ**: 978 pedidos | Nota: **3.82** | Notas Ruins (1 e 2): **22.3%** | Dias de Viagem: **13.4 dias**
* **Origem SC para RJ**: 474 pedidos | Nota: **3.93** | Notas Ruins (1 e 2): **19.0%** | Dias de Viagem: **12.8 dias**
* **Origem MG para RJ**: 1.145 pedidos | Nota: **4.07** | Notas Ruins (1 e 2): **15.5%** | Dias de Viagem: **10.9 dias**
* **Origem RJ para RJ (Local)**: 961 pedidos | Nota: **4.30** | Notas Ruins (1 e 2): **11.9%** | Dias de Viagem: **3.6 dias**

**O Veredito Factual**: O problema de satisfação do Rio de Janeiro **não é a entrega de última milha (last-mile) interna**. Quando o vendedor é do próprio Rio, a viagem dura apenas 3.6 dias e a taxa de notas vermelhas cai para a metade (11.9%), gerando uma nota alta de 4.30.

O colapso de notas acontece na **fronteira interestadual rodoviária**. Os vendedores de outros estados postam rápido (SP posta em 3.5 dias), mas o caminhão que sai de SP gasta **12.5 dias apenas viajando** e o do Paraná gasta **13.4 dias**. O banco prova que quanto mais tempo o transporte passa na rodovia para chegar ao Rio, maior é o volume de atrasos e a taxa proporcional de notas 1 e 2 que o estado de origem recebe, penalizando severamente o fluxo interestadual.

---

## 🎯 6. Conclusões Gerais e Recomendações Estratégicas

A investigação exploratória avançada no banco de dados isolou que a baixíssima taxa de recompra (1.03) na Olist não é motivada por uma crise sistêmica generalizada, mas sim pela soma de dois gargalos operacionais muito bem delimitados e comprovados pelas tabelas analíticas:

### 1. Ineficiência Geográfica Externa (O Caso do Rio de Janeiro)
* **O Fato**: Os lojistas de fora postam rápido para o Rio de Janeiro (média de 3.5 dias). O colapso na satisfação decorre puramente da etapa rodoviária interestadual, que consome de 12.5 a 13.4 dias de deslocamento, estourando as datas limites estimadas e gerando até 22.8% de avaliações críticas (1 e 2). Quando o produto já nasce no próprio estado (vendedor RJ), a viagem cai para 3.6 dias e a nota sobe para 4.30.
* **Recomendação Estratégica**: A Olist deve recalibrar as margens de segurança das datas estimadas de entrega para o Rio de Janeiro para conter a quebra de expectativa do consumidor e, prioritariamente, negociar malhas expressas dedicadas ou parcerias com transportadoras regionais fluminenses para reduzir o tempo rodoviário de trâmite interestadual.

### 2. Ineficiência Operacional de Processo (O Caso de Móveis e a Elite de SP)
* **O Fato**: O Princípio de Pareto provou que 67,2% do faturamento da empresa está centralizado em apenas 10% dos lojistas parceiros. O cruzamento controlado de categorias isolou que, no segmento de Móveis de Escritório (`office_furniture`), a elite de grandes fornecedores de São Paulo opera de forma gravemente ineficiente. Enquanto o mercado comum despacha móveis em 5.1 dias, esses grandes players travam os produtos por mais de 11 dias úteis em seus galpões internos antes de realizar a postagem.
* **Recomendação Estratégica**: Como esse pequeno grupo de lojistas movimenta a maior parte do caixa do site, a diretoria da Olist deve instaurar um comitê imediato de auditoria de processos e aplicar penalidades contratuais baseadas em metas rígidas sobre o tempo interno de expedição desses grandes parceiros de São Paulo, eliminando o atraso na largada do fluxo.

Com este diagnóstico cirúrgico, o projeto comprova que a inteligência de dados é capaz de desarmar vieses de volume bruto, isolar os verdadeiros culpados e direcionar os investimentos da empresa exatamente para onde os gargalos acontecem.