Como analisei dados de aluguel de DVD com o SQL

Okoh Anita Blocked Unblock Seguir Seguindo 20 de dezembro de 2018

Introdução

Recentemente, concluí alguns treinamentos em Data Foundation, facilitados pela Escola de Ciência de Dados da Bertelsmann (em parceria com a Udacity). Para um projeto pessoal, decidi analisar o banco de dados de uma empresa de aluguel de DVDs que chamaremos de Rent A Film . Vamos dar uma olhada em um estudo de caso detalhando meu processo e saída.

Conjunto de dados

Comecei dando uma olhada no banco de dados. O banco de dados DvdRental tem 15 tabelas. Abaixo estão as diferentes tabelas e uma breve descrição delas.

  • actor – contém dados dos atores, incluindo nome e sobrenome.
  • filme – contém dados de filmes como título, ano de lançamento, duração, classificação etc.
  • film_actor – contém as relações entre filmes e atores.
  • categoria – contém dados das categorias do filme.
  • film_category – contendo as relações entre filmes e categorias.
  • store – contém os dados da loja, incluindo a equipe e o endereço do gerente.
  • inventário – armazena dados de inventário.
  • aluguel – armazena dados de aluguel.
  • pagamento – armazena os pagamentos do cliente.
  • staff – armazena os dados da equipe.
  • cliente – armazena os dados do cliente.
  • endereço – armazena dados de endereço para funcionários e clientes
  • cidade – armazena os nomes da cidade.
  • country – armazena os nomes dos países.

Nota: Eu analisei este banco de dados usando o PostgreSQL. Você pode obter detalhes para instalar o PostgreSQL aqui e baixar o banco de dados de aluguel de DVD aqui .

Objetivo e Objetivos

Neste projeto, pretendo responder às seguintes perguntas:

  1. Quais são os principais e menos alugados (em demanda) gêneros e quais são suas vendas totais?
  2. Podemos saber quantos usuários distintos alugaram cada gênero?
  3. Qual é a taxa média de locação para cada gênero? (do maior para o menor)
  4. Quantos filmes alugados foram devolvidos tarde, cedo e pontualmente?
  5. Em que países a Rent A Film está presente e qual é a base de clientes em cada país? Quais são as vendas totais em cada país? (do mais para o menor)
  6. Quem são os 5 principais clientes por vendas totais e podemos obter os seus dados apenas no caso de a Rent A Film querer recompensá-los?

Antes de iniciar as análises, primeiro tentei entender o ERM (Entity Relationship Model) desse banco de dados, também conhecido como Schema. Aqui está o esquema abaixo:

ESQUEMA DE ALUGUEL DE DVD

Você pode ver meu código no meu perfil do GitHub aqui .

Análise

Para responder à primeira pergunta: Quais são os gêneros principais e menos alugados (em demanda) e quais são suas vendas totais?” , Primeiro identifiquei com as tabelas que eu precisaria ingressar, que são:

Categoria> film_Category> film> inventory> rental> customer> payment

Abaixo está a consulta que usei para extrair para responder à pergunta:

Intuições

Da tabela acima, podemos desenhar três principais insights:

  • Rent A Film tem 16 gêneros disponíveis
  • A categoria esportiva parece ser o gênero mais alugado em termos do número de vezes que é alugado, e também tem as maiores vendas totais em termos de dinheiro.
  • A categoria de música é o gênero menos alugado em termos do número de vezes que é alugado e tem o menor total de vendas em termos de dinheiro

Pergunta 2: Podemos saber quantos usuários distintos alugaram cada gênero? Em suma, sim nós podemos.

As tabelas a serem unidas são as seguintes:

Categoria> film_Category> film> inventory> rental> cliente

Abaixo está minha consulta para esta pergunta:

Intuições

Eu queria saber quantos clientes diferentes alugavam cada um dos gêneros. Uma coisa fascinante da consulta é que, embora o gênero musical tenha o menor registro total alugado, ele não tem o menor número de clientes distintos que alugaram o gênero. O gênero de viagem tem esse registro.

Dando um passo para trás e conectando os insights derivados das questões 1 e 2, podemos dizer que o gênero de viagem foi alugado mais vezes do que o gênero musical.

E, claro, o gênero esportivo tem o maior número de clientes distintos que alugaram o gênero.

Pergunta 3: Qual é a taxa média de locação para cada gênero? (do maior para o menor)

As tabelas a serem unidas são as seguintes:

Categoria> film_Category> film

Abaixo está minha consulta para esta pergunta:

Intuições

Fui em frente para ver se o número de vezes que uma categoria foi alugada tem alguma coisa a ver com a taxa média de aluguel de cada gênero. A partir da tabela acima, podemos concluir facilmente que a taxa média de aluguel pode não ser um fator.

Embora o gênero de jogo tenha a menor média de aluguel, é um dos cinco gêneros mais alugados. Surpreendentemente, o gênero da música não é o mais caro – a ação é, mesmo que o gênero de ação seja um dos gêneros mais alugados.

Podemos dizer com segurança que a maioria dos clientes é amante de filmes relacionados ao esporte e está menos interessada em filmes musicais.

Pergunta 4: Quantos filmes alugados foram devolvidos tarde, cedo e no horário?

As tabelas a serem unidas são as seguintes:

filme> inventário> aluguel

Intuições

O status de retorno dos filmes é indiscutivelmente um dos aspectos mais importantes a serem monitorados em um negócio de aluguel de DVDs. Da consulta acima, 48% dos filmes são retornados antes da data de vencimento, enquanto 41% dos filmes são devolvidos com atraso e 11% chegam na hora marcada.

Poderia haver uma série de fatores por que isso poderia estar acontecendo, como a distância de envio desses filmes em lojas que poderiam estar totalmente fora do controle dos clientes e assim por diante. Precisamos nos aprofundar nos dados para obter a essência do problema.

No entanto, é sensato observar que uma porcentagem significativa de filmes é devolvida com atraso. A introdução de uma multa por atraso na chegada pode ser uma fonte extra de renda e, por sua vez, desencorajar retornos atrasados.

Mas tal decisão só pode fazer sentido se soubermos porque o problema está ocorrendo.

Pergunta 5: Em que países a Rent A Film está presente e qual é a base de clientes em cada país? Quais são as vendas totais em cada país? (De mais a menos)

As tabelas a serem unidas são as seguintes:

País> Cidade> Endereço> cliente> pagamento

Veja a consulta abaixo:

Intuições

A Rent A Film está presente em 108 países, com a Índia tendo a maior base de clientes de 60 clientes e as maiores vendas totais em termos de dinheiro. O Afeganistão tem o menor total de vendas em termos de dinheiro, embora não seja o único país com a menor base de clientes de 1 cliente

Pergunta 6: Quem são os 5 principais clientes por vendas totais e podemos obter os detalhes apenas no caso de a Rent A Film os recompensar?

As tabelas a serem unidas são as seguintes:

País> Cidade> Endereço> cliente> pagamento

Veja a consulta abaixo:

Intuições

Supondo que desejamos recompensar ou enviar presentes físicos aos principais clientes, a tabela acima mostra seus nomes completos, endereços, e-mail, etc.

Essas informações podem ser enviadas para a equipe de marketing da empresa, a fim de usar seus conhecimentos de domínio para decidir como recompensá-los.

Conclusão

Neste projeto, analisamos dados de uma empresa de aluguel de DVDs que decidimos chamar de "Rent A Film" para encontrar insights sobre os clientes e suas preferências. Nós temos 3 principais conclusões:

  1. A empresa tem clientes amantes de esportes e seria aconselhável estocar mais filmes relacionados ao esporte para aumentar as vendas totais em comparação com os filmes relacionados à música. Seria uma boa ideia aumentar a taxa média de aluguel de filmes do gênero esportivo, já que não é um fator importante no aluguel para os clientes. Isso, por sua vez, aumenta a receita total. No entanto, mais análises precisam ser feitas para concluir sobre isso.
  2. Existe potencial para obter uma fonte extra de receita através de uma taxa de devolução de filmes atrasados.
  3. A Rent A Film está presente em 108 países, sendo a Índia o maior mercado em pessoas e receitas. Além disso, 20% dos países em que participam contribuem para 80% da base total de clientes.

PS Como eu, qualquer um pode aprender a ser um analista de dados e se você quiser ser notificado sobre meu próximo projeto ou atualizações sobre meu aprendizado, sinta-se à vontade para se inscrever no meu boletim informativo

Texto original em inglês.