Como resolver um problema do time de operações usando SQL?
Tags: datascience, analytics
Published: 2022-02-16 21:16:32

**Introdução**
O segundo desafio do curso de data analytics da [Tera](https://somostera.com/) foi assinado pelo [QuintoAndar](https://www.quintoandar.com.br/), e a proposta era a de resolver um problema cotidiano do time de operações. Para isso, deveríamos utilizar PostgreSQL, e também o DBeaver (vou falar mais sobre o software adiante).
O problema em questão dizia respeito à queda no volume de contratos assinados. O intuito era se imaginar como parte do time de dados do quinto andar, e oferecer suporte ao time de operações, para uma melhor tomada de decisão e tirar alguns insights a partir dos dados.

**Compreensão do problema**
A primeira etapa foi a de identificar o problema. O time de operações quer entender por que o volume de contratos assinados está diminuindo. Mas, como é o fluxo dos contratos na empresa? Como começa e quais são as etapas?

**Experiência de usuário**
Eu nunca aluguei um apartamento pelo quinto andar, então meu primeiro passo foi baixar o app e agendar uma visita. Consegui rapidamente encontrar um apartamento disponível para visitar, e fiquei atenta a todas as etapas que aconteceriam após a minha visita. Ter essa experiência de usuário, passar por todas as etapas, avaliar o atendimento e a visita, e chegar na parte de fazer uma oferta pelo imóvel me auxiliou no entendimento no fluxo de informações.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lv234iw5d4tl5oghif2z.png)
 
Descobri que antes mesmo de fazer a visita que é possível fazer uma proposta para o imóvel, a chamada Proposta Express.
Depois de realizar a visita, acompanhada por uma corretora de imóveis, começaram as avaliações: sobre o imóvel em si e também sobre o atendimento.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qms4rsx0e6fisf4ok27y.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8tvczl14m0vv8470hfdu.png)
 
 ![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ik0y9brw7ww0jnvabhbj.png)
 Compreender o fluxo de um cliente que pretende alugar um apartamento foi muito importante e elucidativo.

**Destaques após a realização da visita:**

- Ao fim da avaliação, o inquilino pode fazer uma proposta para o imóvel;
- As avaliações se referem a visita, ao corretor e ao imóvel visitado;
- Alguns critérios da avaliação são semelhantes as colunas da tabela tenant_review: conservação do imóvel, preço, mobília.
- Outra conexão percebida entre a realização da visita e o banco de dados é a coluna client_offers, que é a oferta feita ao final da visita.
- Foi possível identificar a etapa inicial do funil de contratos.

A seguir vou apresentar o banco de dados disponibilizado para a elaboração do desafio, com a apresentação das colunas e da estrutura.

**O dataset**
Para a realização do desafio, foi disponibilizada uma ferramenta de SGBD (sistema de gerenciamento de banco de dados) chamada [DBeaver](https://dbeaver.com/).

> O DBeaver é um open source, que suporta uma vasta opção de sistemas de gestão de bases de dados, como: MySQL, PostgreSQL, SQL Server, Oracle, MariaDB dentre outros.

Neste projeto, deveríamos usar PostgreSQL, e contava com as seguintes tabelas:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fxibd6ao76mizo3aldpz.png)

**Dicionário das colunas:
tenant_reviews ou tenant_reviews_clean:** notas que os inquilinos dão para os imóveis após a visita.
visit_date: data agendada para a visita (ano-mês-dia);
day: dia que a visita aconteceu (diferentes formatos de data);
time: hora do dia em que ocorreu a visita;
house_id: identificação dos imóveis;
neighbourhood: bairro do imóvel;
visitor: visitante/inquilino;
agent: nome do corretor;
review: avaliação de 1–5;
building: características em relação à construção do imóvel;
conservation: conservação do imóvel;
size: tamanho do imóvel;
price: preço do imóvel;
location: localização do imóvel;
did_not_like_the_location: localização errada (sim ou não);
expected_a_better_preserved_property: melhor estado de conservação do imóvel;
bigger_in_photos: tamamnho do imóvel maior do que nas fotos;
different_from_the_listing: diferença do que foi listado para o imóvel;
others: comentários abertos. 

**visits_contracts:** volumes de visitas que viraram e que não viraram contratos de aluguel.
visits: quantidade de visitas realizadas;
tenants_that_signed: quantidade de visitas que assinaram contrato;
tenants_that_did_not_sign: quantidade de visitas que não assinaram.

**visits_offers_contracts:** volumes para cada etapa do funil ao longo do tempo.
month: mês (janeiro a agosto);
visits: quantidade de visitas agendadas em cada mês;
tenant_that_visited: quantidade de inquilinos que fizeram visita;
visited_houses: quantidade de imóveis visitados;
offers: quantidade de ofertas recebidas (fazer uma proposta?);
tenants_that_made_offers:quantidade de inquilinos que fizeram oferta;
contracts: quantidade de contratos assinados.

Algumas considerações:
- Não há nenhuma coluna igual entre todas as tabelas;
- O que significa a coluna visits da tabela visits_contracts?
- Qual a diferença entre as colunas offers e tenants_made_offerts?

**Diagnóstico**
Depois de compreender as tabelas, e realizar algumas querys, foi possível identificar que:
A quantidade de visitas realizadas aumentou de janeiro a agosto. Porém, quanto mais visitas realizadas, menos ofertas são feitas.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ncwy12ut1btq2o6v8q4s.png)
A quantidade de visitas realizadas aumentou 16% de janeiro a agosto, enquanto a quantidade de ofertas diminuiu em 12% com o passar dos meses.
Já com relação a quantidade de contratos, houve uma diminuição de 48% de janeiro a agosto. E a quantidade de inquilinos que fizeram ofertas diminuiu 38%.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/du9m3qnqhipa5nb83qzx.png)

Comparação entre visitas e ofertas realizadas nos meses de janeiro a agosto:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c5h3b7o8qdgr1m9wl02b.png)

Mesmo com o aumento na quantidade de visitas, o número de contratos assinados não aumentou. Surgiram então questionamentos como: por que não tiveram mais contratos assinados se a quantidade de visitas aumentou?
E também: é preciso aumentar a quantidade de visitas ou melhorar a qualidade para que sejam mais efetivas? Qual a nota da avaliação daqueles inquilinos que fizeram a visita e assinaram contrato? 

**Algumas hipóteses:**

- Clientes não encontram o imóvel adequado nas primeiras visitas e desistem de tentar alugar.
- Novos clientes chegam, aumentando a quantidade de visitas mas não são fidelizados e não assinam contrato.

Algumas outras informações poderiam ter ajudado a resolver a questão, mas não estavam disponíveis: ID dos inquilinos; dados da tabela visits_offers_contracts desagrupados (estavam agrupados por mês).

**Recomendações**
Por fim, um acionável que poderia auxiliar na resolução do problema seria aumentar as chances de sucesso nas primeiras visitas. E, para fazer isso, poderia direcionar para o melhor corretor fazer as primeiras visitas e também melhorar o filtro de busca dos imóveis.

**Referências**
https://www.w3schools.com/sql/sql_ref_keywords.asp
https://www.postgresqltutorial.com/postgresql-subquery/
https://www.postgresqltutorial.com/postgresql-cte/
https://www.postgresql.org/docs/9.1/queries-with.html