Como resolver um problema do time de operações usando SQL?

Introdução
O segundo desafio do curso de data analytics da Tera foi assinado pelo QuintoAndar, 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.

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.

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.

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:

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.

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%.

Comparação entre visitas e ofertas realizadas nos meses de janeiro a agosto:

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